excel 関数について

質問

「給料計算をしたいと考えている
B2に就業開始時間として数字が記載されている。
C2に休憩開始時間として数字が記載されている。
D2に休憩終了時間として数字が記載されている。
E2に就業終了時間として数字が記載されている。
記載されている数字は、
「1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24」のうちどれか。小数点にも対応してほしい。

・例1
B2に「7」と入力されていたら就業開始時間は「7:00」ということ。C2に「10」と入力されていたら休憩開始時間は「10:00」ということ。D2に「11」と入力されていたら休憩終了時間は「11:00」ということ。E2に「16」と入力されていたら就業終了時間は「16:00」ということ。

・例2
B2に「7.5」と入力されていたら就業開始時間は「7:30」ということ。C2に「10.5」と入力されていたら休憩開始時間は「10:30」ということ。D2に「11.5」と入力されていたら休憩終了時間は「11:30」ということ。E2に「16.5」と入力されていたら就業終了時間は「16:30」ということ。

時給は、以下のようになっている。
11時から14時の間だけ1000円。その他の時間は950円。

C2の時間からD2の時間は休憩時間なのでその間の休憩は0円とする。

上記の計算をした上でF2に解を出力。

回答No.1

luka3 · ベストアンサー

=(MIN(C2,11)-MIN(B2,11))*950+(MEDIAN(11,C2,14)-MEDIAN(11,B2,14))*1000+(MAX(14,C2)-MAX(14,B2))*950+(MIN(E2,11)-MIN(D2,11))*950+(MEDIAN(11,E2,14)-MEDIAN(11,D2,14))*1000+(MAX(14,E2)-MAX(14,D2))*950

やや冗長気味になっていますが、この方が理解しやすいと思います。
やっていることは、ＢからＣまでの労働時間とＤからＥまでの労働時間それぞれに対して、11時より前・11時～14時・14時以降の３区分に対する時間を計算して時給を掛けています。

Ｂ＜Ｃ＜Ｄ＜Ｅ になることが前提で、日付をまたいだ 例えば D=22 E=1 というのは論外です（E=25にすれば計算可能）。

余談ですが、線分同士の交わりの計算ということで、若いときにやってたゲームの「当たり判定」を思い出し懐かしくなりました。

補足

B2に「7.5」と入力されていたら就業開始時間は「7:30」ということ。C2に「10.5」と入力されていたら休憩開始時間は「10:30」ということ。D2に「11.5」と入力されていたら休憩終了時間は「11:30」ということ。E2に「16.5」と入力されていたら就業終了時間は「16:30」ということ。

これなんですけど、小数点でもできるようにして欲しいです

回答No.2

SI299792

B2: 7
C2: 10
D2: 11
E2: 16 の時、
時給1000円が11～14 3時間
時給 950円が 7～10＋15～16 5時間、
計\7750

B2: 7.5
C2: 10.5
D2: 11.5
E2: 16.5の時、
時給1000円が11.5～14 2.5時間
時給 950円が 7.5～10.5＋16～18.5 5.5時間、
計 \7725
ですか❓

普通こういう時は、時給1000円の時間、時給 950円の時間を計算してから答えを出します。その方が時給別の労働時間が解り、検証しやすいです。
F2: =IFERROR(G2*1000+H2*950,"")
表示形式、ユーザー定義｢\#,##0;\-#,##0｣
G2: =IF(B2="","",MAX(MIN(E2,14)-MAX(B2,11),0)-MAX(MIN(D2,16)-MAX(C2,11),0))
H2: =IFERROR(E2-B2-D2+C2-G2,"")

一気に計算したかったら、
F2: =(MAX(MIN(E2,14)-MAX(B2,11),0)-MAX(MIN(D2,14)-MAX(C2,11),0))*1000+(E2-B2-D2+C2-MAX(MIN(E2,14)-MAX(B2,11),0)+MAX(MIN(D2,14)-MAX(C2,11),0))*950
表示形式、ユーザー定義｢\#,##0;\-#,##0;｣
（\ は半角￥にして下さい。）