賃金台帳の作成について

質問

ご存知の方、ご指導の程よろしくお願い致します。
使用機種等は、Windows 8.1　Excel 2013 を使用しています。
長文での申し訳ありません。最後まで読んでください。

賃金台帳作成中のエクセルの表で説明を求めるのが筋ですが、OKWaveで表示する方法が判らずWordでは理解し難いでしょうがよろしくお願い致します。

A列 　B列 　C列　 D列 　 E列 　F列 　G列 　 H列　 I列 　 J列　を使用
１行目、月日　曜日　始業時刻　終業時刻　休憩時間　勤務時間　時間内　時間外　時間内支給額　時間外支給額
下記の様に表示されていますが、確認と併せて見て下さい。

２行目　１月１日　木　5:10　17:00　1:00　10:50　8:00　2:50　8,000　16:00
３行目　１月２日　金　5:30　16:00　1:00　 9:30　8:00　1:30　8,000　0,00
４行目　１月３日　土　5:10　16:30　1:00　10:20　8:00　2:20　8,000　16:00
５行目 １月４日　日　5:00　12:00　0:00　7:00　0:00　7:00　0,000　7,700

参考ですが、各列には関数が入っています。
B列(曜日)は、=TEXT(A8,"aaa")
C列(始業時刻)・D列(終業時刻)・E列(休憩時間)は、いずれも手入力です。
(最小で１０分単位です)
F列(勤務時間)は、=IF(OR(C2="",D2=""),"",D2-C2-E2)
G列(時間内)は、=IF(F2="","",IF(F2>$J$44,$J$44,F2))
H列(時間外)は、=IF(OR(F2<=$J$44,F2=""),"",F2-G2)
I列(時間内支給額)は、=IF(G2="","",G2*24*$E$41)
J列(時間外支給額)は、=IF(H2="","",H2*24*$E$41*1.25)

セルが見えませんが、
セルE41(平日の8時間内時給)が、1,000円です。
8時間以上の時間外賃金は、時間内時給1,000*1.25です。
契約時間は、セルJ44に8:00 と入力されています。
特に質問ですが、日曜日の場合のみ何時から何時間勤務をしても、全てが時間外勤務扱いになり、セルE42に時給1,500円を入れています。
カレンダー日曜日に対しセルD42の日に対応するものの合計をもとめます。
関数は、C42=SUMIF($B$8:$B$38,$D$42,$I$8:$I$38) としてあります。
最後に、現在私の関数よりも簡単で推薦されるものがありましたら教えて下さい。

この説明でご理解が頂けるか少し疑問ですが、よろしくお願い致します。

夜分遅く申し訳ありません。

回答No.3

bunjii · ベストアンサー

>セルE35(平日の8時間内時給)が、1,000円です。
>契約時間は、セルJ38に8:00 と入力されています。
>セルE36に時給1,500円を入れています。
>セルD36の日に対応するものの合計
上記のセル位置に変更のときは2行目の数式は次のようになります。
F2=IF(COUNT(C2:D2)=2,D2-C2-E2,0)
G2=IF(B2="日",0,MIN($J$38,F2))
H2=F2-G2
I２=G2*24*$E$35
J2=IF(B2="日",H2*24*$E$36,H2*24*$E$35*1.25)
F2からJ2を選択して下へ32行目までコピーしてください。
日曜日の合計は次のようになります。
C36=SUMIF($B$2:$B$32,$D$36,$J$2:$J$32)
貼付画像は7行目から30行目までを非表示にしてあります。
尚、F2:J32は条件付き書式で値が0のとき文字の色を白にしました。

お礼

おはようございます。

この度の賃金台帳作成に際しましては、２日間にわたりご丁寧な関数等を使い判りやすい説明を頂き、私の希望する月単位の表が出来上がり、本当にありがとうございました。

この表を基に、個人の年間の賃金台帳にリンクさせるべく作業をして行きます。

昨夜(23:20頃)お礼を記載いたしましたが、送信・ベストアンサーで送信した積りでおりましたが、結果的には入力の跡が残って無く、届いていないのではと思い只今再度作成送信したものです。

まだまだ勉強したいことは多くありますが、IF関数の勉強方法・Excelの表で作成したものをOKWaveに添付送信する方法(bunjii様の様に)等です。

感謝の気持ちで何かお渡ししたいと願っています。

電話番号・アドレスの交換はこのシステムでは禁止されていると思います。

感謝の気持ちは山々ですが残念です。

今後一層のご活躍をお祈り致します。
本当にありがとうございました。

回答No.2

bunjii

>最後に、現在私の関数よりも簡単で推薦されるものがありましたら教えて下さい。
F列(勤務時間)は、=IF(OR(C2="",D2=""),"",D2-C2-E2) → =IF(COUNT(C2:D2)=2,D2-C2-E2,0)
始業時刻と終業時刻がシリアル値で入力されていないとき""にすると、そのセルを計算対象にしたとき更にIF関数で条件分岐が必要になりますが、0にすることでIF関数を省けます。
0が表示されると煩わしいときは範囲を選んで条件付き書式で文字の色をセルの塗りつぶしの色と同じにすれば見えなくなります。
G列(時間内)は、=IF(F2="","",IF(F2>$J$44,$J$44,F2)) → =IF(B2="日",0,MIN($J$44,F2))
日曜日のみ時間内勤務が無くすべてが時間外勤務とのことなので、提示の数式に誤りがあるようです。
H列(時間外)は、=IF(OR(F2<=$J$44,F2=""),"",F2-G2) → =F2-G2
I列(時間内支給額)は、=IF(G2="","",G2*24*$E$41) → =G2*24*$E$41
J列(時間外支給額)は、=IF(H2="","",H2*24*$E$41*1.25) → =IF(B2="日",H2*24*$E$42,H2*24*$E$41*1.25)
日曜日の時給のみ$E$42に1500と入力されているとのことなのでIF関数で条件分岐するようにされると良いでしょう。
C42=SUMIF($B$8:$B$38,$D$42,$I$8:$I$38) は日曜日のみの合計のようですが、日曜日は時間外勤務と言うことなのでSUMIF関数の集計範囲は$J$8:$J$38になるのでは？

補足

この度は、早速のご指導誠にありがとうございました。

Wordにて説明をさせて頂きましたが、理解し難いのにありがとうございました。

早速、ご指導に従って作成中の表を修正して見ました。

私が提示しましたセル位置・表示されている時間・時間外支給額等の数値・数式に誤りがあった為と思われます。

申し訳ございませんが、bunjii 様のご指導のものを使用させて頂き、目的の賃金台帳を作成したいと思っております。

引き続き本当にご迷惑をおかけしますが、下記を参考に最後までご指導をお願い致します。

１　日曜日以外(祝日は平日と見なす)は、勤務時間が8時間以上の場合、休憩時間を1:00 手入力し、これ以上の場合は時間内に8:00・時間外に残りの時間数が表示される。関連の支給額のセルも表示される。
２　日曜日は勤務時間が何時間であれ全てが時間外に表示され、時間外支給額が表示される。(休憩時間を入力すれば、時間外及び時間外支給額が変動する)
３　日曜日の勤務は、休日労働時間として、曜日の日セルD42に対応するものの合計を求めます。

セル配置は下記の通りです。

セルE35(平日の8時間内時給)が、1,000円です。
8時間以上の時間外賃金は、時間内時給1,000*1.25です。
契約時間は、セルJ38に8:00 と入力されています。
特に質問ですが、日曜日の場合のみ何時から何時間勤務をしても、全てが時間外勤務扱いになり、セルE36に時給1,500円を入れています。
カレンダー日曜日に対しセルD36の日に対応するものの合計をもとめます。

よろしくお願い致します。