【Excel】決められた休憩時間の除外

本日の課題 †

下記画像のように始業時間・退勤時間を入力すると休憩時間が自動的に出力されるようにしたい。休憩時間は複数の時間帯が会社側から指定されている。時間の精度は1分単位とする。

【解決策】休憩時間の羅列を作ってそれを数えて除外する †

別のシートに休憩時間を1分単位で羅列し、それをCOUNTIFを使いって始業〜退勤までの間にいくつ含むかを数えます。1分単位の羅列なので数えた数＝分とする事ができ、これを休憩時間として使います。

休憩時間シートの作成 †

下記は「休憩時間」シートの一例です。所定休憩時間を1分単位で羅列します。休憩時間を羅列する範囲はどこでも良いですが、ここではO列まで使っています。

出勤簿に計算式を入力 †

COUNTIFで先程の休憩時間シートの中にどれだけ休憩時間を含むかを計算します。ここではO列まで使っていますが、もちろんもっと広くしたり、狭くしたりしても大丈夫です。

計算式は一度に全部繋いでも良いのですが、ややこしくなってデバッグが大変そうなので下記の2つに分けました。

始業時間以降に含む休憩時間の数(D列)
退勤時間以降に含む休憩時間の数(E列)
D列とE列の差分(始業〜退勤までに含む休憩時間の数)

D列の計算式(D28)：(※B28は始業時間)
   =COUNTIF($休憩時間.$B$4:$休憩時間.$O$10000,">="&B28)
E列の計算式(E28)：(※C28は退勤時間)
   =COUNTIF($休憩時間.$B$4:$休憩時間.$O$10000,">="&C28)
F列の計算式(F28)：
   =D28-E28

休憩時間の範囲の$O$10000ですが、空欄は無視されるので十分に大きい範囲を指定しています。特に決まりがあるわけではありませんので実用的な範囲であればもっと狭くしても広くしても構いません。

休憩時間シートには1分単位で羅列しているので、見つかった数＝休憩時間(分)とする事ができ、休憩時間を算出できます。

仕上げ †

最後に余分な列を非表示にして完成です。

そういえば †

Excelと紹介しておきながら無意識にLibreOffice使ってました…。だいたい同じだと思うので適当に読み替えてください(丸投げ