エクセルについて - 条件付きセルのカウント

質問

質問お願いします。
現在私はアルバイト先のシフトの改良（給与計算機能の追加）を行っています。
基本的には条件付きカウントなどを用いて出勤時間を出して計算するだけなのですが
一つできないことがあります。それは日曜日の時給アップ分の計算です。
このシフト表に9,13,17,18,Fなどを入力してシフトを組みます。
そこで日曜日のセルのみの各9,13,17,18,Fを数えてそれぞれの出勤時間を掛けて
その合計を出して時給アップ分を掛けたいと思います。
大事なのはこれをベースにして毎月シフトを組むのですが曜日をずらしたときに
うまくカウントを機能させたいです。実際にシフトを組む店長はエクセルに詳しくないので
簡単に変えれる必要があります。よろしくお願いします。

回答No.1

kmetu · ベストアンサー

たとえば日曜日（一行目に曜日があるとして)の9(2行目にあるとして)なら

=SUMPRODUCT((1:1="日")*(2:2=9))

で数えられます。

お礼

ご回答ありがとうございます。
大変助かりました。

回答No.3

Cupper-2

考え方

全部一度にやろうとしない。
ひとつずつ、手で計算するときと同じ事を作業用の列やシートを作って行う。

難しいと思ったら、こんなふうにすると良いですよ。
作成した Excel のシートを販売でもしない限り、できあがってから考え直しても良いのですからね。

　確認と提案１
時給アップは土日だけですか？休祭日は平日扱いなんでしょうか。
もし休祭日も時給アップなら土日休祭日かを示す行があった方がやりやすいと思います。

　確認と提案２
時間外労働に対する考慮は不要ですか。
もしも時間外作業が生じるのでしたら、別途残業時間の記入方法を考えておきましょう。

　確認と提案３
現在どのような計算式を立てているのでしょうか。
それを完全に無視しても良いですか。

　確認と提案４
土日出勤の時給は一律アップですか。

休祭日は平日扱い。時間外労働は無い。今の計算式は無視してOK。土日は一律アップ。として考えてみます。
A列には店員の名前、B列から日付が記入されているとします。
なお、Sheet2の A1セルには年、A2セルには月 が記入されているとします。
Sheet2の空いているセルに、時給1～5・乗率に相当する数値を記入してそれぞれ範囲名を付ける。
（Sheet2の B2セルに時給の金額と勤務時間を入力（=850*8 とか）そして、左上にB2と表示されている窓に"時給1"と入力。
　これを乗率まで行う。など）

　シート２で値段をそのまま計算します。
　Sheet2 の B3セル に
　　=IF(Sheet1!B3="9",時給1,)
　これが基本。その日の賃金が計算されます。
　　=IF(WEEKDAY(DATE(A1,A2,B1)<6,1,乗率)
　これが平日と土日出勤時の時給の乗率設定
　2つ組み合わせると
　　=IF(Sheet1!B3="9",時給1*IF(WEEKDAY(DATE(A1,A2,B1)<6,1,乗率),)
　になります。
　コピーすることを考えて、絶対参照と相対参照を混ぜます。
　　=IF(Sheet1!B3="9",時給1*IF(WEEKDAY(DATE($A$1,$A$2,B$1)<6,1,乗率),)
　あとは時間毎の組み合わせを加えます。
　　=IF(Sheet1!B3="9",時給1*IF(WEEKDAY(DATE($A$1,$A$2,B$1)<6,1,乗率),
　　IF(Sheet1!B3="12",時給2*IF(WEEKDAY(DATE($A$1,$A$2,B$1)<6,1,乗率),
　　IF(Sheet1!B3="17",時給3*IF(WEEKDAY(DATE($A$1,$A$2,B$1)<6,1,乗率),
　　IF(Sheet1!B3="18",時給4*IF(WEEKDAY(DATE($A$1,$A$2,B$1)<6,1,乗率),
　　IF(Sheet1!B3="F" ,時給5*IF(WEEKDAY(DATE($A$1,$A$2,B$1)<6,1,乗率),
　　0)))))
　※ 便宜上 行を分けていますが、改行せず１行にまとめてください。

　あとは横方向に合計を求め、Sheet1 で計算結果のセルを参照すれば Sheet1 はスッキリします。
　これはできるよね。
　計算が長く難しくなったら、Sheet3 以降で他の時間を別に行うなどして、あとで合計を求めましょう。

ちなみに動作の検証はしていません。
正しく動作しなくても責任は負えませんので、自身でよく考えて修正するなどしてください。

そんなわけで COUNT系の関数を使わずに行う方法でした。
本来は条件がもっと複雑で、こんなやり方をしないと作っている人も分からなくなるくらいです。
ですので 「たかがエクセル」 といって安請け合いしないことを心がけましょう。

お礼

不十分な説明からわざわざ長い説明ありがとうございました。
if文を用いる方法は思いつきませんでしたが残り付けたい機能として
「営業時間短縮における出勤時間の短縮」「年末年始等の特別手当(時給に上乗せ分)」「年末年始等の特別手当(時給に乗率分)」がありますのでそちらで活かせたらと思います。何分機械工学が専攻の学生ですのでぶしつけな質問失礼しました。

回答No.2

mu2011

一例です。
A列を氏名、日付、曜日をB1:AF2で数値、文字列としています。
（１）AG2に=SUMPRODUCT((B$2:AF$2="日")*($B3:$AF3={9;13;17;18;"F"}))を設定して下方向にコピー
（２）後はAG列をSUM関数で集計で如何でしょうか。

お礼

ご回答ありがとうございます。
sumproduct関数を使わせていただくことにしました。
no.1の方と同じご回答でしたのでベストアンサーは
no.1の方にさせていただきますがご厚意大変感謝しております。