EXCELでの給与計算表について

質問

一日に二度も質問すみません
また給与計算表について質問です

画像に載せてるようなデザインで作ろうかと思ってます

・日付
　年度と月分から自動で割り出す
・曜日
　日付から自動で割り出す
・出勤
　出勤時間をhh:mmで記入
・退勤
　上記と同じ
・休憩
　給与に係る分のみ表記
　何時間で何分加算
・時給(A)
　通常時給の時間での労働時間
・時給(B)
　深夜時給の時間での24時までの労働時間
・時給(C)
　上記の残りの0時～5時の労働時間

　※(B)と(C)を合わせて深夜時給22時～5時の労働時間として表記でも構いません

・労働時間
　(A)～(C)の合計労働時間
・日給
　E2:F4の時給とそれぞれの時間を掛けあわせたその日の日給
・D17
　その月の合計勤務日数

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

1日(土)の場合の計算式
F8(休憩)　=　IF((G8+H8+I8)*24>=8,TIME(0,30,0),TIME(0,0,0))
G8(時給A)　=　VALUE(TEXT(MIN("22:00",TIME(E8,,))-MAX("05:00",TIME(D8,,)),"hh:mm;!0;0"))
H8(時給B)　=　VALUE(TEXT(MIN("24:00",TIME(E8,,))-MAX("22:00",TIME(D8,,)),"hh:mm;!0;0"))
I8(時給C)　=　VALUE(TEXT(MIN("05:00",TIME(E8,,))-MAX("00:00",TIME(D8,,)),"hh:mm;!0;0"))
J8(労働時間)　=　G8+H8+I8
K8(日給)　=　(($F$2*G8)+($F$3*H8)+($F$4*I8))*24

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

24時～出勤なら表が作動しますが
23時以前からの24時を跨ぐ出勤なら表が作動しません
色々Googleなどで検索しましたが最終的にここまででした

誰か詳しい方ヒントor答えを教えていただけませんか？

回答No.2

bunjii · ベストアンサー

其々の関数式を次の通り提言します。

>年度と月分から自動で割り出す
=IF(DATE($B$1,$B$2,ROWS(B$7:B7))<=EOMONTH(DATE($B$1,$B$2,1),0),DATE($B$1,$B$2,ROWS(B$7:B7)),"")

>日付から自動で割り出す
=TEXT(B7,"aaa")

>通常時給の時間での労働時間
=MIN("22:00",MAX("5:00",E7*1))-MAX("05:00",MIN("22:00",D7*1))

>深夜時給の時間での24時までの労働時間
=MIN("24:00",MAX("22:00",E7))-MAX("22:00",D7)

>上記の残りの0時～5時の労働時間
=MIN("05:00",E7)-MAX("00:00",MIN("5:00",D7))

>1日(土)の場合の計算式
>F8(休憩)　=　IF((G8+H8+I8)*24>=8,TIME(0,30,0),TIME(0,0,0))
貼付画像では1日（土）は7行目のようです。
従って、提示の計算式は1行ずれています。
また、G、H、I列の時間計算に誤りがあります。
「出勤時間をhh:mmで記入」としていればD列とE列は時刻のシリアル値なので直接計算できます。
MIN("22:00",TIME(E8,,) → E8に入力された時刻はすべて 0 と評価されます。
MAX("05:00",TIME(D8,,)) → D8に入力された時刻はすべて 0 と評価されます。
∴ MIN("22:00",TIME(E8,,))-MAX("05:00",TIME(D8,,) → 0
代わりに次の式を適用しても正しい値を導けない条件もあります。
=MIN("22:00",E8)-MAX("05:00",D8)

>23時以前からの24時を跨ぐ出勤なら表が作動しません
日付を跨ぐ計算は複数行を使う方法と出勤時刻より退勤時刻が小さいとき退勤時刻に整数の1を加える方法で対処します。
何れにしても深夜勤務に係わる22:00～5:00の扱いを論理的に正しい判断を組み入れないと算出時間に誤りが生じます。

補足

とてもわかりやすい回答有難うございます！
1日(土)はこちらのミスで写真が一つ古いのになってました
出0時退12時の場合きちんと表示されます
>日付を跨ぐ計算は複数行を使う方法と出勤時刻より退勤時刻が小さいとき退勤時刻に整数の1を加える方法
こちらを使ってみたところ#ではなくなりましたがやはり正常な値では無いです…
=IF(D8>E8,MIN("22:00",E8+1)-MAX("05:00",D8),MIN("22:00",E8)-MAX("05:00",D8))
式はこれであってますよね?
何度も質問すみません

回答No.4

bunjii

>時給計算のところはどういった式にしたら良いのでしょうか?
24:00を跨いだときの時間計算は判断要素が複雑すぎます。
例えば21:00から翌日の6:00までの勤務では時給Aが21:00～22:00の1時間と翌日の5:00～6:00の1時間を加算しなければなりません。
連続24時間を超える勤務は無いと思いますが完璧に計算するには式の組立に時間が掛かりますので無料の協力は致しかねます。

お礼

分かりました
やはりシフト管理表はそう簡単には作成できないんですね…
市販のソフトかなんかを買って代用します。

何度もお忙しい中回答有難うございました！

回答No.3

bunjii

>こちらを使ってみたところ#ではなくなりましたがやはり正常な値では無いです…
下記の数式はあなたが考えたのでしょうか？
時給Bの時間についての計算ですよね？

=IF(D8>E8,MIN("22:00",E8+1)-MAX("05:00",D8),MIN("22:00",E8)-MAX("05:00",D8))

MIN("22:00",E8+1) → "22:00" → 0.916666667

>式はこれであってますよね?
上記の式でE8は手入力として"24:00"が最大とすればMIN("22:00",E8+1)の結果は"22:00"より大きくなりません。
従って、関数の使い方に誤りがあります。
E8が"2:00"のとき

MIN("22:00",E8+1) → "22:00" → 0.916666667
　　　　　↓
MAX("22:00",E8+1) → "2:00"+1 → "26:00" → 1.083333333

また、MAX("05:00",D8)も見直す必要があります。
D8が"20:00"のとき

MAX("5:00",D8) → "20:00" → 0.833333333
　　　　　↓
MAX("22:00",D8) → "22:00" → 0.916666667

時給Bの時間は22:00以降の時間を勤務計算していると判断しますので24時を超える時は1:00のとき25:00のようにする必要があるのでE8に1を加えます。
5:00より前の時間帯については時給Cで計算していますので時給Bの計算では"5:00"との比較を必要としません。

補足

何度も回答有難うございます
なんかよくわからなくなってきました(TдT)
時給計算のところはどういった式にしたら良いのでしょうか?
Excelに関してはあまり詳しくないので初歩的な式しか組めてませんのでよければ最初から詳しく教えていただけませんか?
回答通りに自分でするとグチャグチャになってきたので…