エクセルで時給計算をしたいと考えているのですが、関数が複雑でよくわかり

質問

エクセルで時給計算をしたいと考えているのですが、関数が複雑でよくわかりません。
時間帯によって時給が違います。詳しい方教えてください。
宜しくお願いします。

時給単価Ａ　７：００～９：００ or １７：００～２０：００　１０００円／ｈ
時給単価Ｂ　９：００～１７：００　　　　８５０円／ｈ

例）７：３０～１８：００まで勤務した場合、
時給単価Ａ　２．５ｈ
時給単価Ｂ　８ｈ
A（出勤時間） B （退勤時間） C （1000円／ｈの時間数）D（ 850円／ｈの時間数）E（合計勤務時間数）

A1(7:30) B1(18:00) C1(2.5) D1 (8) E1(10.5)

C1とD1に入る式をおしえて頂けますでしょうか？
7：00より前、20：00以降の勤務はありません。
宜しくお願いします。

回答No.3

yubo · ベストアンサー

こんにちは

時間帯を1000円と850円で区別するよりも、朝（C）、昼（D)、夜（E)、と分けるとわかりやすくなると思います。

「朝」は、
出勤時刻が9：00過ぎなら何もしない。
9:00前なら「9:00」と「退勤時刻」を比較して小さい方から、「7:00」と「出勤時刻」を比較して大きい方を差し引く。

=IF(A1>="9:00"*1,"",MIN("9:00",B1)-MAX("7:00",A1))
このままでは、「出勤時刻」と「退勤時刻」が空白でも計算してしまうので、IF(COUNT(A1:B1)<2,"", ）を追加。（この部分は昼と夜にも同様に追加）

結果
=IF(COUNT(A1:B1)<2,"",IF(A1>="9:00"*1,"",MIN("9:00",B1)-MAX("7:00",A1)))

「昼」は、
出勤時刻が17:00過ぎ、または退勤時刻が9:00前なら何もしない。
上記以外なら、「17:00」と「退勤時刻」を比較して小さい方から、「開始時刻」と「9:00」を比較して大きい方を差し引く。

=IF(COUNT(A1:B1)<2,"",IF(OR(A1>="17:00"*1,B1<="9:00"*1),"",MIN("17:00",B1)-MAX("9:00",A1)))

「夜」は、
退勤時刻が17:00前なら何もしない。
17:00過ぎなら「20:00」と「退勤時刻」を比較して小さい方から、「17:00」と「出勤時刻」を比較して大きい方を差し引く。

=IF(COUNT(A1:B1)<2,"",IF(B1<="17:00"*1,"",MIN("20:00",B1)-MAX("17:00",A1)))

後は、朝と夜を足して時給1000円の合計を求める。

一応、入力時刻が時間外（6:45等）になっても対応しておりますが、丸め処理（10分未満切り捨てなど）はしておりません。

お礼

関数はやはり自分には難しかったですが、「結果」だけコピペしたらうまくできました！
これで時給計算の作業が軽減できると思うとうれしくなります。
本当にありがとうございました

回答No.2

ToOrisugaru

セルＩ１に9:00と入力し、名前定義で、「通常開始」と定義します。
セルＪ１に17:00と入力し、名前定義で、「通常終了」と定義します。
セルＧ１に下記関数を入力します。
　　=IF(A1<=通常開始,通常開始-A1,0)
セルＨ１に下記関数を入力します。
　　=IF(B1>=通常終了,B1-通常終了,0)

結果
　セルＣ１に以下の関数を入力します。
　　=G1+H1
　セルＤ１に以下の関数を入力します。
　　=B1-A1-C1

補足

ご回答ありがとうございます。アドバイスの通りの式を使ったらうまくできました！
ありがとうございます。

ただ、７：００～２０：００などの長い勤務の場合ではうまくいきました
が、７：００～８：００や７：００～７：３０などの短い勤務の場合、G1は常に２：００
となります。
正しくはG1に１：００か０：３０となるかと思うのですが、これを改善するにはどうすればよろしいでしょうか？

宜しくお願い致します。

回答No.1

nine999

１つの枠でやろうとすると、それだけ関数は複雑になります。そういう時は手順を分けて考えます。
時間帯が３つに分かれているので、その点はやりやすいと思います。
あと、9:00、17:00は事前にどこかのセルに入力して置いた方が参照しやすくて良いです。（内部では時間ではなく日数で管理しているため）
時給の数字も参照できるようにセルに入れておくと、そこを変更するだけで全ての計算を変更できます。

C1=if(A1<[9:00],([9:00]-a1)*24,0)+if(B1>[17:00],(B1-[17:00])*24,0)
D1=E1-C1
[9:00]と[17:00]は、参照先のセル番号です。

E1はB1-A1じゃなくて、(B1-A1)*24にして置く必要があります。書式も「時刻」ではなく「標準」にすること。