『勤務時間、給料算出の関数を教えてください』（ミッキー）

　
 単価：2,500円
 残業：3,125円（8h超え）
 深夜：3,750円（22：00〜）


   E　　　F　　G　　H　 　 I　　　J　　　　K
 出勤　退勤　休憩　 残業　深夜　　実働　　金額
 8:00　17:00　1:00　0:00　0:00　　8:00　　20,000
 8:00　19:00　1:00　2:00  0:00　  10:00   26,250
 8:00　23:00　1:00　5:00　1:00　  14:00　 39,375

 *出勤・退勤・休憩のみ手入力

 ※やりたいこと※
 H残業、I深夜、J実働(休憩除く)を関数でだしたい
 K金額を単価表から出したい

 できる限りシンプルな関数で出せると嬉しいです。。
 わかる方よろしくお願いいたしますm(__)m





＜ 使用 Excel：Excel2021、使用 OS：Windows10 ＞
----
残業 = 退勤 - TIMEVALUE("17:00") - 休憩
深夜 = IF(退勤 > TIMEVALUE("22:00"),0,退勤 - TIMEVALUE("22:00"))
実働 = 退勤 - 出勤
金額 = 2500 * 8 + (残業 - 深夜) * 24 * 3125 + 深夜 * 24 * 3750

※8時間以内の勤務、0時以上の勤務はないものとする。
　手入力の勤務時間はシリアル値であること。
（犯人はヤス） 2023/03/29(水) 12:44:35
----
一か所間違い
実働 = 退勤 - 出勤 - 休憩
（犯人はヤス） 2023/03/29(水) 12:46:07
----
更に間違い
深夜 = IF(退勤 < TIMEVALUE("22:00"),0,退勤 - TIMEVALUE("22:00"))
（犯人はヤス） 2023/03/29(水) 12:47:06
----
・深夜からの出勤
・朝5時以降は深夜勤ではない

などの条件が加わると数式が複雑になるので、
シンプルにするためには特殊条件を考慮しない
（ただしその場合は出力値が狂う）ものにした方が良いです。
（火災報知器） 2023/03/29(水) 12:56:47
----
残業 = 退勤 - TIMEVALUE("17:00") - TIMEVALUE(TEXT(休憩,"h:mm"))
深夜 = IF(退勤 > TIMEVALUE("22:00"),0,退勤 - TIMEVALUE("22:00"))
実働 = 退勤 - 出勤 - TIMEVALUE(TEXT(休憩,"h:mm"))
金額 = 2500 * 8 + (残業 - 深夜) * 24 * 3125 + 深夜 * 24 * 3750

※8時間以内の勤務はないものとする。
　手入力の勤務時間はyyyy/m/d h:mmのシリアル値であること。

か？
（犯人はヤス） 2023/03/29(水) 12:58:54
----
さっそくご丁寧にありがとうございます！

お伝え忘れていたのですが、勤怠の入力のない日は空欄にしたく、
=IF(出勤="","",いただいた式)でいれてみたのですが####になってしまうのですがそんなに単純じゃないんですかね、、？

素人すぎてすみません(´；ω；`)
（ミッキー） 2023/03/29(水) 13:26:47
----
 P1:P3 に ↓ が入力されてるとして

	P
1	2,500
2	3,125
3	3,750

 J2 =IF(F2="","",F2-E2-G2)
 I2 =IF(F2="","",MAX(0,F2-"22:00"))
 H2 =IF(F2="","",MAX(0,J2-"8:00"-I2))
 表示形式「時刻」

 K2 =IF(F2="","",((J2-SUM(H2:I2))*$P$1+H2*$P$2+I2*$P$3)*24)
 表示形式「通貨」

 基本的にはこんな感じ　※少なくとも例示通りにはなります

 あとは、例示にはない勤務パターンがあるのかどうか、ですね。
 退勤が深夜0時以降になることがあるとか

 参考まで
（笑） 2023/03/29(水) 13:37:16
----
ありがとうございます！
無事出したいように出ました！

ただ実働時間を最後に集計したく、単純に　=SUM(J1:J50)　としましたが、
一部計算されない行があります。
セルの書式はすべて時刻（13:00形式）になっています。
考えられる他の原因はありますか。。？
（ミッキー） 2023/03/29(水) 15:28:45
----
＞一部計算されない行があります。
計算されない行のデータはどうなっていますか。

（どう） 2023/03/29(水) 15:37:58
----
憶測ですが、集計されてない訳ではなく、集計結果が時間ではなく時刻として表示されているのかと思います。

集計したセルの表示形式を、ユーザー定義の『[h]:mm』に変えてあげてみてはどうでしょうか？
（む） 2023/03/29(水) 15:58:55
----
[h]:mmにしたら計算されました！
みなさまありがとうございます(´；ω；`)
とても助かりました！！
（ミッキー） 2023/03/29(水) 17:00:26
----
 すみません、今さらなんですが勉強のために教えていただきたいです<(_ _)>

 I2 =IF(F2="","",MAX(0,F2-"22:00"))
 MAX関数で最初の数値に0を入れるのは、0と「F2-"22:00"」の結果を比べて最大値（F2-"22:00"の結果）を出すためでしょうか...？
（ミッキー） 2023/03/30(木) 16:01:58
----
 「0」と F2-"22:00" の結果を比べて、大きい方の値を返すということです。

 F2が 23:00 だったら、F2-"22:00" は 1:00 で「0」より大きいので、大きい方の 1:00
 F2が 19:00 だったら、F2-"22:00" はマイナスになって「0」の方が大きいので 0（0:00）

 ついでに確認･･･
 退勤が深夜零時以降になることはない、ということでよかったんですか？

 以上
（笑） 2023/03/30(木) 16:29:02
----
 なるほど・・・そういうことだったんですね！
 ご丁寧にありがとうございます(´；ω；`)

 退勤時間ですが、現時点では0時以降になることはないため、教えていただいた関数で問題ございません！
 ただ今後もし0時を過ぎることがあった際は、またご相談させてくださいm(__)m
（ミッキー） 2023/03/30(木) 16:40:33