複数の休憩時間がある場合の休憩時間の算出方法

Excel で勤務表を作っていますが、
休憩時間が複数ある場合の休憩時間の算出方法が分かりません。

D5 10:00 (出勤時刻)
E5 19:00 (退勤時刻)
F5 休憩時間
H5 =E5-D5-F5 (実稼働時間)

D6 12:00 (出勤時刻)
E6 22:00 (退勤時刻)
F6 休憩時間
H6 =E6-D6-F6 (実稼働時間)

休憩時間1
D38 12:00 (休憩開始時刻)
E38 13:00 (休憩終了時刻)
F38 1:00 (休憩時間1のトータル)

休憩時間2
D39 17:15 (休憩開始時刻)
E39 17:30 (休憩終了時刻)
F39 0:15 (休憩時間2のトータル)

休憩時間2の休憩時間帯が変わってもカスタマイズできるように
(休憩時間3、4、5と実際にはあります)
D38,D39,E38,E39は使わず
F38とF39を使って、F5、F6セルに、休憩時間を算出したいです。

また、13時以降の出社の場合は、12時から13時の休憩時間を含めないようにもしたいです。

どんな関数を用いれば実現可能でしょうか？
アドバイスよろしくお願い致します。

No.3 ベストアンサー 回答者： bunjii 回答日時： 2014/04/26 08:37

>休憩3、休憩4、休憩5、休憩6まである場合は、関数はどう変更すればいいですか？

始めにSUM関数の引数に不備がありましたので訂正します。
序に、出勤時刻と退勤時刻が未入力のとき空欄とするためのIF関数を追加しました。
=IF(COUNT(D5:E5)=2,SUM(MIN(MAX(E5,$D$38),$E$38)-MAX(MIN(D5,$E$38),$D$38),MIN(MAX(E5,$D$39),$E$39)-MAX(MIN(D5,$E$39),$D$39),MIN(MAX(E5,$D$40),$E$40)-MAX(MIN(D5,$E$40),$D$40),MIN(MAX(E5,$D$41),$E$41)-MAX(MIN(D5,$E$41),$D$41),MIN(MAX(E5,$D$42),$E$42)-MAX(MIN(D5,$E$42),$D$42),MIN(MAX(E5,$D$43),$E$43)-MAX(MIN(D5,$E$43),$D$43)),"")
SUM関数の内容を確認してください。
第1引数（休憩1） MIN(MAX(E5,$D$38),$E$38)-MAX(MIN(D5,$E$38),$D$38)
第2引数（休憩2） MIN(MAX(E5,$D$39),$E$39)-MAX(MIN(D5,$E$39),$D$39)
　　　　　・
　　　　　・
　　　　　・
第6引数（休憩6） MIN(MAX(E5,$D$43),$E$43)-MAX(MIN(D5,$E$43),$D$43)
各引数は休憩時間帯を定義した行を参照しますがMAX関数とMIN関数およびセルの列記号は同じパターンになっています。
応用力で指定された休憩時間帯を増減できますので実情に合わせて変更すると良いでしょう。
これはExcelの関数を利用してどのような論理で計算するかの応用問題です。
数学が不得手の方には難しいかも知れません。

この回答へのお礼

理想の結果が確認できました。

最後までお付き合いくださり、
どうもありがとうございました。

お礼日時：2014/04/26 21:58

No.2 回答者： bunjii 回答日時： 2014/04/24 08:20

>休憩時間2の休憩時間帯が変わってもカスタマイズできるように(休憩時間3、4、5と実際にはあります)

>D38,D39,E38,E39は使わずF38とF39を使って、F5、F6セルに、休憩時間を算出したいです。
出勤時刻または退社時刻が休憩時間帯に掛かるときは誤差が生じますので「D38,D39,E38,E39は使わずF38とF39を使って」と言うのは無理でしょう。
次の数式で誤差を生じない計算ができますのでご確認ください。
F5=SUM(MIN(E5,$E$38)-MAX(MIN(D5,$E$38),$D$38),MIN(MAX(E5,$D$39),$E$39)-MAX(D5,$D$39))
D列またはE列が未入力のときF列およびG列を空欄にするときはIF関数で対処してください。
貼付画像はExcel 2013で検証した結果ですが、すべてのバージョンで再現できるはずです。

この回答への補足

休憩3、休憩4、休憩5、休憩6まである場合は、
関数はどう変更すればいいですか？

補足日時：2014/04/25 22:59

No.1 回答者： mshr1962 回答日時： 2014/04/24 00:42

休憩時間=SUMIFS($F$38:$F$39,$D$38:$D$39,">="&$D5,$E$38:$E$39,"<="&$E5

)