Excelで開始時刻と終了時刻を記録していると、「13時台に何分稼働していたか」「1時間ごとの稼働時間を集計したい」ということがあります。
特に設備の稼働時間や勤務時間、対応時間などを時間帯ごと・時間帯別に集計したい場合、開始時刻と終了時刻が複数の時間帯にまたがると単純な引き算だけでは計算できません。
例えば13:45～14:15のように時間帯をまたぐデータは、13時台15分・14時台15分のように重なった部分だけを分ける必要があります。
この記事では、MAX関数とMIN関数を使い、開始時間・終了時間から時間帯ごとの稼働時間を集計する方法を解説します。
Excelで時間帯ごとの稼働時間を集計する考え方
ポイントは「イベントの時間」と「集計したい時間帯」が重なっている部分だけを求めることです。
MAX関数とMIN関数を組み合わせることで、重なっている時間だけを計算できます。
=MAX(0,MIN(終了時刻,時間帯終了)-MAX(開始時刻,時間帯開始))MIN関数で「早いほうの終了時刻」、MAX関数で「遅いほうの開始時刻」を取得し、その差を求めます。
2つの時間帯がまったく重なっていない場合は計算結果がマイナスになるため、外側のMAX関数で0にします。
例）13:45～14:15を時間帯ごとに分けて計算する
例えば、イベントが13:45～14:15まであったとして、そこから
・13:00～14:00
・14:00～15:00
・15:00～16:00
それぞれの時間帯での稼働時間を計算するとします。
この場合、次の数式を入力して下へコピーします。
=MAX(0,MIN($B$3,B7)-MAX($B$2,A7))すると、それぞれの時間帯と重なる時間のみ集計することができます。
| 時間帯 | 稼働時間 |
|---|---|
| 13:00～14:00 | 0:15 |
| 14:00～15:00 | 0:15 |
| 15:00～16:00 | 0:00 |
13:45～14:15の30分を、13時台15分・14時台15分に分けて集計できました。
複数のデータを1時間ごとに集計する方法
複数のデータをまとめて集計する場合は、表の配置を少し変更します。
B列に開始時刻、C列に終了時刻を入力し、D1以降に「13:00」「14:00」「15:00」のように集計したい時間帯を横方向へ並べます。
そしてD2へ次の数式を入力し、右方向・下方向へコピーします。
=MAX(0,MIN($C2,D$1+TIME(1,0,0))-MAX($B2,D$1))最後に各列をSUM関数で合計します。13時台なら =SUM(D2:D4) といった具合です。
合計時間が正しく表示されない場合は「[h]:mm」にする
複数件の稼働時間を合計すると24時間を超える場合があります。
その場合は、結果セルの表示形式を [h]:mm にすると、24時間を超えた合計も正しく表示できます。
数式の結果が「0:15」ではなく小数で表示される場合も、セルの表示形式をh:mmまたは[h]:mmに変更してください。
👉時間を合計すると24時間で戻る場合や、日またぎの計算が必要な場合は、以下の記事もあわせて確認してください。
30分ごとに集計することもできる
1時間ごとではなく、30分ごとに集計することも可能です。
30分ごとに集計する場合は、時間帯の見出しも「13:00、13:30、14:00、14:30…」のように30分刻みで入力します。
そのうえで、数式内のTIME(1,0,0)をTIME(0,30,0)へ変更します。
=MAX(0,MIN($C2,D$1+TIME(0,30,0))-MAX($B2,D$1))日付をまたぐデータには注意
23:30～翌1:30のようなデータは、時刻だけではなく日付を含めた日時として管理するほうが安全です。
開始・終了と集計時間帯を同じ日時軸で比較してください。
Excelで時間帯ごとの稼働時間を集計する方法まとめ
時間帯別集計では、
=MAX(0,MIN(終了時刻,時間帯終了)-MAX(開始時刻,時間帯開始))という考え方を使います。
13:45～14:15も、13時台15分・14時台15分のように重なっている時間だけを正しく分けられます。設備稼働、対応時間、障害時間、勤務時間、会議室利用などへも応用できます。
ぜひ活用してみてください。
コメント