開始〜終了２つの時刻データを元にエクセルで稼働時間を算出する方法
作業ごとにかかった時間を日報に書くとき、その作業日報を１枚のエクセルにまとめると以下のような表になります。 E列、F列が作業時間を表し、H列が作業の分類を表示しています。
| - | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 提出日 | 提出者名 | 承認者1 | 承認者1詳細 | 【自】作業時間 | 【至】作業時間 | 【休憩】作業時間 | 作業内容 | 備考 |
| 2 | 2023/10/26 | ボブ | AdminA | 未処理 | 08:00 | 10:00 | 掘削 | 掘削をおこなった | |
| 3 | 2023/10/26 | ボブ | AdminA | 未処理 | 10:00 | 11:00 | 研磨 | ||
| 4 | 2023/10/26 | ボブ | AdminA | 未処理 | 11:00 | 12:00 | 保守 | ||
| 5 | 2023/10/26 | ボブ | AdminA | 未処理 | 13:00 | 14:30 | 掘削 | ||
| 6 | 2023/10/26 | ボブ | AdminA | 未処理 | 14:30 | 16:00 | 研磨 | ||
| 7 | 2023/10/26 | ボブ | AdminA | 未処理 | 16:00 | 17:30 | 掘削 | ||
| 8 | 2023/10/26 | ボブ | AdminA | 未処理 | 17:30 | 19:00 | 保守 | ||
| 9 | 2023/10/23 | アリス | AdminA | 未処理 | 09:00 | 11:00 | 掘削 | ||
| 10 | 2023/10/23 | アリス | AdminA | 未処理 | 13:30 | 16:00 | 研磨 | ||
| 11 | 2023/10/10 | アリス | AdminA | 未処理 | 08:30 | 10:00 | 研磨 | ||
| 12 | 2023/10/10 | アリス | AdminA | 未処理 | 10:00 | 11:30 | 保守 | ||
| 13 | 2023/10/10 | アリス | AdminA | 未処理 | 13:00 | 18:00 | 掘削 | ||
| 14 | 2023/10/05 | アリス | AdminA | 未処理 | 08:00 | 11:00 | 掘削 | 掘削を行った | |
| 15 | 2023/10/05 | アリス | AdminA | 未処理 | 11:00 | 14:00 | 研磨 | A製造ライン用の研磨 | |
| 16 | 2023/10/05 | アリス | AdminA | 未処理 | 14:00 | 17:30 | 保守 | 油の補充 |
このような業務報告から、各作業の所要時間をエクセルで簡単に求めることができます。
各行の作業時間の算出方法
エクセルでは 10:00 のような形式のセルを「時刻」として自動で認識するため、時刻はそのまま計算式に使うことができます。
正しく認識されないときは次章の「補足」を御覧ください
かかった時間は「終了時刻ー開始時刻」で求めることができますので、サクッと計算してみましょう。
パッと見た限り正しく計算されていることがわかります。経過時間自体は非常に簡単に算出することができるのです。
【補足】エクセルの計算で24時間を超えるときは折り返しが発生するため注意
エクセルでは時刻が24時間を超えると翌日と扱われ、ケースによっては意図しない結果が表示されることがあるので注意が必要です。 例えば上の例でいうと、J列の各作業の合計時間を、更に合計したいと思うこともあるでしょうがこれは正しく計算できません。 なぜなら24時間を余裕で超過するためです。
この問題を回避するには、セルの表示形式を変更します。
- 合計セル（上記の例ではJ17）を選択し、右クリック > セルの書式設定を開きます。
- 書式のコードの部分に[hh]:mmと入力します。
- OKをクリックしてダイアログを閉じます。
![24時間を超えて表示させる場合は表示形式から[hh]:mmのように表示形式を変更します](/excel/time/fixed-excel-time-over24_hu500b06062ae5fcb71634639a592d05ea_69249_480x0_resize_q85_bgffffff_lanczos_3.jpg)
サンプルの箇所で正しく24時間を超えた値が表示されていることを確認できますね。あとはOKボタンを押して作業は完了です。
どの作業に何時間かかったのか？作業区分ごとに作業時間を集計してみる
どの作業にどれだけの時間がかかっているのか、分類別に集計をしてみましょう。 予め作業区分の一覧を用意しておきます。ここでは「掘削・研磨・保守」の3種類の作業があります。
計算結果が小数点のように意図しない表示になることがあります。この場合は表示形式を修正してください。詳しくは補足のセクションを御覧ください
SUMIF関数の詳しい使い方については「SUMIF関数のページ」を御覧ください
時刻の計算結果の単位を「分」に統一して計算する
時刻の計算は頭が混乱することがあります。次の2つのうち、どちらが直感的に計算できますか？
- 1時間30分＋2時間50分= ???
- 90分＋170分 = ???
計算の際、単位を「分」に揃えると扱いやすくなります。
エクセルで先ほどの計算結果を「分」の単位に揃えてみましょう。 エクセルで 1:30 のような値を 90（分）に変換するには、HOUR関数とMINUTE関数と組み合わせます。
例えば次のように使います。 =HOUR(J2)*60 + MINUTE(J2)
- HOUR
- 時間を返す関数です。1時間は60分のため、HOUR関数の結果に60を掛けます
- MINUTE
- 分を返す関数です。HOURの結果とMINUTEの結果を足すことで分に変換できるという寸法です
特にExcelで24時間を超えると折り返されるといった問題も、この手法なら問題なく回避できます。 何より直感的でわかりやすいのが「分」のメリットです。
最終計算結果を時刻形式に戻す方法
分による計算は計算過程において便利ですが、計算の最終結果は桁が大きくなるとすこしわかりにくいです。 例えば作業合計時間が「1920分」と言われても、一体どのくらいか少しわかりにくいですね。 この場合は逆に時刻形式に変換してあげましょう。これ以上計算に使用しないなら、 「XX時間XX分」のように明確に書いてあげるとより親切です。
K16のセルに1920が入っているとして、以下の数式を入れることで見やすく整形できます。
=INT(K16/60) & “時間” & MOD(K16,60) & “分”
作業日報の入力をアプリ化して効率よくデータを用意する
今回サンプルに使用した作業日報は一般的な業務日報を1枚のエクセルシートにまとめたものです。 集計のためのデータがあればExcelの豊富な機能で様々な集計や分析が可能ですが、最大の問題は集計データの用意です。 日報は1日1枚提出されますが、個々の日報を1枚1枚見てエクセルに入力するのはあまりにも非効率です！
データを効率よく用意するために、日報の入力を見直してみませんか？
転記漏れや転記ミス、そして無駄な転記の時間を節約しよう
今回使ったデータは全てクラウド日報NipoPlusで入力したレポートをエクセルで使用しました。NipoPlusを使えばスマートフォンやタブレットから簡単に日報の作成ができます。
そして作成されたレポートを今回の記事で使用したように、1枚のエクセルへ簡単に一括変換できるので、データの入力にも最適です。また、NipoPlus上からも簡単な集計やグラフ作成も可能ですので、 用途によってはエクセル無しで集計が完結できるかも？
無料でも使えるので是非お試しください。