ｴｸｾﾙで休憩時間を除く

ｴｸｾﾙで生産計画を立ているのですが、
①：10：00～10：10
②：12：00～12：45
③：15：00～15：10
④：17：00～17：15
⑤：19：15～19：20
の休憩時間を予定時間から引きたいです。
生産計画は日々変わるのでどういった式を入れたらいいか教えてください。

No.3 回答者： mygoonickname 回答日時： 2022/02/21 15:39

こんにちは。

使い易いかは分かりませんが、例えば下記の様な方法も。
一覧表を用意して、Vlookupで座標を算出、INDIRECT関数で休憩時間を
合計し、最後に勤務時間？から差し引くという様な処理を行っています。

計算式はJ3～M3セルの4か所で、その後は、コピー＆貼り付けて良いかと。
表の時間が、0:00～23:59、開始、終了時間をB列に入れる。
C列、D列は、E列の座標を指定。C列は、ː(コロン)を付けています。
詳細は、添付資料を見て頂いた方が解り易いかと。

No.2 回答者： fujillin 回答日時： 2022/02/21 14:41

こんにちは

直接の回答ではありません。

通常、
＞休憩時間を予定時間から引きたいです。
といった場合は、例えば
・9:00～12:00の3時間に対して、①の時間帯を減じて、2時間50分を算出する
・9:00～12:30の3.5時間に対して、①、②の時間帯を減じて、2時間50分を算出する
ようなことを意味すると思います。

一方で、ご質問の内容は上記とは異なるようですし、セル位置もよくわからない（罫線を非表示にしたうえでセル結合も行っていると思われる）上に、意味不明の別セルを参照している式を見せられても、質問文の情報だけが全てである閲覧者にはまったくわかりません。

質問者様にとってはあたりまえでも、文章に記していない限り、閲覧者には不明な情報となります。
必要な情報を整理して提示した上で（不要な情報は省くことも必要）、なさりたいことを第三者にわかるように質問なさらないと、適切な回答を得るのは難しいと思います。


以下は勝手な妄想で・・・
「開始時刻と実質の所要時間から終了時刻を計算したいが、休憩時間は所要時間から除きたい」
という意味の場合の例をご参考までに。
（意味が違う可能性が高いと思いますが、その場合は、以下は無視してください）

※ 当たるも八卦なので、簡略化してあります。
※ ユーザ定義関数の方がよさそうにも思いますが、無理矢理関数での例です。(笑)
※ 計算量を減らすため、ひとつの「所要時間」は8:00未満と仮定しています。
　（超えるとエラーになります。計算量を増やせば計算は可能です）
※ 生の値でそのまま計算していますので、有効桁の関係で1分程度の誤差を生む可能性があります。
　（個々の計算で、整数化しながら計算すれば誤差は無くなるでしょう）

添付図では、休憩時間をF2：H3と仮定して
B3セルに、
=IF(A3="","",C2)
を入力して下方にフィルコピー
C2セルに、
=IF(A2="","",AGGREGATE(15,6,ROW(OFFSET($A$1,B2*1440,,480))/((ROW(OFFSET($A$1,B2*1440,,480))/1440<=$F$2)+(ROW(OFFSET($A$1,B2*1440,,480))/1440>$H$2))/((ROW(OFFSET($A$1,B2*1440,,480))/1440<=$F$3)+(ROW(OFFSET($A$1,B2*1440,,480))/1440>$H$3)),A2*1440)/1440)
を入力して、下方にフィルコピーしてあります。

開始時刻(B2セル）に時刻を入力し、A2セルから順に所要時間を入力すれば、開始、終了時刻が順に表示されてゆく仕組みです。

No.1 回答者： 銀鱗 回答日時： 2022/02/21 13:24

手作業で行う手順をそのまま数式で作れば良いでしょう。

作業用の列やシートを別途用意すると見た目がすっきりします。

自分ならその休憩時間ごとに区切った作業時間を最後に合計します。
　0:00～10:00
　10:10～12:00
　12:45～15:00
　15:10～17:00
　17:15～19:15
　19:20~24:00
この時間と重複する時間を合計という事になります。

MIN・MAX関数を使うと良いでしょう。

　MIN(終了時間,"12:00")
で、終了時間が12時よりも遅ければ、12:00が値が返ります。

　MAX(開始時間,"10:10")
で、開始時間が10時10分よりも早ければ、10:10が値が返ります。

……ということで、10時10分から12時までの稼働時間は、

　MIN(終了時間,"12:00")-MAX(開始時間,"10:10")

で、求められます。
ただし、終了時間や開始時間がイレギュラーになる事も考慮し、

　MAX(MIN(終了時間,"12:00"),"10:10")-MIN(MAX(開始時間,"10:10"),"12:00")

とすると良いでしょう。
終了時間が10時10分よりも早い場合や、
開始時間が12時よりも遅い場合を考慮しています。

この考え方で、他の区間に対しても同様に計算すればよい。
ややこしいかと思いますが、ちゃんと整理すれば難しいことをやっているわけではありませんので理解できるかと思います。

・・・

この手の問題は、あらゆるパターンを想定してそれに対応する方法を探すのではなく、
今ある運用方法に則って順番に考えるというやり方になります。

「年度末に忙しくなって深夜残業し、日付を跨いで仕事をしちゃったよ。ハハハ」
なんて場合は例外的に手作業で計算すれば良い。
「0：45－8：30」の計算はエラー(#####表示)になりますからね。