エクセルで休憩時間を引いての作業計画予定表の作成の仕方

タイトル通りなのですが、会社には
①10：00～10：10（10分）
②12：00～13：00（60分）
③15：00～15：10（10分）
④17：00～17：15（15分）
⑤19：15～19：20（5分）

からなる休憩が有ります。

作業開始・終了時間は関数で自動で入るようになっているのですが
休憩時間だけを引いた計画表の作成がどう試しても上手くいきません。

今回は添付のようになっていますが、①～④の時間がどこに入るかは
その日の計画になってみないとわかりません。
なので自動で①～④の時間が入ったらその時間分引いて終了時間が
でるようにしたいです。

No.2 回答者： fujillin 回答日時： 2023/07/26 16:11

No1です。

個別に計算するよりも、まとめて計算する方が式が短くなると想定してNo1の回答をしてしまいましたが・・
5種類のケースぐらいだと、個別計算を書き連ねた方が短かったですね。
思い込みが先行してしまい、大変失礼いたしました。

No1の添付図と同じレイアウトで、E2セルに
=D2-C2-MAX(0,MIN(D2,C$12)-MAX(C2,B$12))-MAX(0,MIN(D2,C$13)-MAX(C2,B$13))-MAX(0,MIN(D2,C$14)-MAX(C2,B$14))-MAX(0,MIN(D2,C$15)-MAX(C2,B$15))-MAX(0,MIN(D2,C$16)-MAX(C2,B$16))
を入力して、下方にフィルコピーでも同じ結果が得られます。
（計算内容も同じなので、注意事項も同じです）

※ 休憩時間の種類数が増えてくるとNo1の式の方が短くなります。
（6種類で同じくらい、7種類以上だと短くなりますね）

No.1 回答者： fujillin 回答日時： 2023/07/24 17:27

こんにちは

＞休憩時間だけを引いた計画表の作成がどう試しても上手くいきません。
ご質問内容がイマイチよくわかりませんけれど、指定の開始時刻・終了時刻から、休憩時間を引いた実稼働時間を求められれば良いものと解釈しました。

・事前準備として、どこかに「休憩時間の一覧」を作成しておきます。
（添付図では、仮に、A11:C16をこの表としてあります）
・以下では、開始時刻＞＝終了時刻であるものと仮定しています。
（日付をまたいだ、23:00～1:00のような計算は、以下の式のままではできません。式で対応しようとすると更に長いものになります。
　23:00～25:00のようにすれば、翌日の10:00までならそのままでも可能です。）
（翌日の10:00を超えるような入力がある場合には、休憩時間表に34:00～34:10の項目を追加するなどとしておくことで、可能となります。）
・時刻の値は全てシリアル値（＝エクセルの時間形式の値）であると仮定しています。

添付図では、E2セルに
=D2-C2-SUMPRODUCT((((D2>C$12:C$16)*C$12:C$16+(D2<=C$12:C$16)*D2)>((C2>B$12:B$16)*C2+(C2<=B$12:B$16)*B$12:B$16))*(((D2>C$12:C$16)*C$12:C$16+(D2<=C$12:C$16)*D2)-((C2>B$12:B$16)*C2+(C2<=B$12:B$16)*B$12:B$16)))
の式を入力し、下方にフィルコピーしてあります。


※ このような計算は、まとめてやろうとするとそれなりに面倒で長い式になります。
個別の部分計算を作業列等を用いて計算し、それを目的のセルで合計するなどのような方法をとれば、効率が良くかつメンテナンスも容易になると思います。

この回答へのお礼

ありがとうございます。せっかくですので、この式を使ってみます。

お礼日時：2023/07/25 07:55