tag エクセル tag 集計
最終更新日: 2024年05月09日

開始〜終了2つの時刻データを元にエクセルで稼働時間を算出する方法

作業ごとにかかった時間を日報に書くとき、その作業日報を1枚のエクセルにまとめると以下のような表になります。 E列、F列が作業時間を表し、H列が作業の分類を表示しています。

-ABCDEFGHI
1提出日提出者名承認者1承認者1詳細【自】作業時間【至】作業時間【休憩】作業時間作業内容備考
22023/10/26ボブAdminA未処理08:0010:00掘削掘削をおこなった
32023/10/26ボブAdminA未処理10:0011:00研磨
42023/10/26ボブAdminA未処理11:0012:00保守
52023/10/26ボブAdminA未処理13:0014:30掘削
62023/10/26ボブAdminA未処理14:3016:00研磨
72023/10/26ボブAdminA未処理16:0017:30掘削
82023/10/26ボブAdminA未処理17:3019:00保守
92023/10/23アリスAdminA未処理09:0011:00掘削
102023/10/23アリスAdminA未処理13:3016:00研磨
112023/10/10アリスAdminA未処理08:3010:00研磨
122023/10/10アリスAdminA未処理10:0011:30保守
132023/10/10アリスAdminA未処理13:0018:00掘削
142023/10/05アリスAdminA未処理08:0011:00掘削掘削を行った
152023/10/05アリスAdminA未処理11:0014:00研磨A製造ライン用の研磨
162023/10/05アリスAdminA未処理14:0017:30保守油の補充

このような業務報告から、各作業の所要時間をエクセルで簡単に求めることができます。

各行の作業時間の算出方法

エクセルでは 10:00 のような形式のセルを「時刻」として自動で認識するため、時刻はそのまま計算式に使うことができます。

キャラクター

正しく認識されないときは次章の「補足」を御覧ください

かかった時間は「終了時刻ー開始時刻」で求めることができますので、サクッと計算してみましょう。

エクセルを使って経過時間を計算するには、終了時刻-開始時刻と計算するだけです
エクセルを使って経過時間を計算するには、終了時刻-開始時刻と計算するだけです
エクセルを使って経過時間を計算するには、終了時刻-開始時刻と計算するだけです

パッと見た限り正しく計算されていることがわかります。経過時間自体は非常に簡単に算出することができるのです。

【補足】エクセルの計算で24時間を超えるときは折り返しが発生するため注意

エクセルでは時刻が24時間を超えると翌日と扱われ、ケースによっては意図しない結果が表示されることがあるので注意が必要です。 例えば上の例でいうと、J列の各作業の合計時間を、更に合計したいと思うこともあるでしょうがこれは正しく計算できません。 なぜなら24時間を余裕で超過するためです。

作業合計時間をSUMで集計した結果、明らかにおかしな結果が表示されます。これは24時間を超えたことで時刻の折返しが発生するためです
作業合計時間をSUMで集計した結果、明らかにおかしな結果が表示されます。これは24時間を超えたことで時刻の折返しが発生するためです
作業合計時間をSUMで集計した結果、明らかにおかしな結果が表示されます。これは24時間を超えたことで時刻の折返しが発生するためです

この問題を回避するには、セルの表示形式を変更します。

  1. 合計セル(上記の例ではJ17)を選択し、右クリック > セルの書式設定を開きます。
  2. 書式のコードの部分に[hh]:mmと入力します。
  3. OKをクリックしてダイアログを閉じます。
24時間を超えて表示させる場合は表示形式から[hh]:mmのように表示形式を変更します
24時間を超えて表示させる場合は表示形式から[hh]:mmのように表示形式を変更します
24時間を超えて表示させる場合は表示形式から[hh]:mmのように表示形式を変更します

サンプルの箇所で正しく24時間を超えた値が表示されていることを確認できますね。あとはOKボタンを押して作業は完了です。

どの作業に何時間かかったのか?作業区分ごとに作業時間を集計してみる

どの作業にどれだけの時間がかかっているのか、分類別に集計をしてみましょう。 予め作業区分の一覧を用意しておきます。ここでは「掘削・研磨・保守」の3種類の作業があります。

SUMIFを使うことで作業区分ごとにかかった作業時間を算出できます
SUMIFを使うことで作業区分ごとにかかった作業時間を算出できます
SUMIFを使うことで作業区分ごとにかかった作業時間を算出できます
作業区分ごとに分けて作業時間の計算をすることができました。
作業区分ごとに分けて作業時間の計算をすることができました。
作業区分ごとに分けて作業時間の計算をすることができました。

計算結果が小数点のように意図しない表示になることがあります。この場合は表示形式を修正してください。詳しくは補足のセクションを御覧ください

SUMIF関数の詳しい使い方については「SUMIF関数のページ」を御覧ください

時刻の計算結果の単位を「分」に統一して計算する

時刻の計算は頭が混乱することがあります。次の2つのうち、どちらが直感的に計算できますか?

  • 1時間30分+2時間50分= ???
  • 90分+170分 = ???

計算の際、単位を「分」に揃えると扱いやすくなります。

エクセルで先ほどの計算結果を「分」の単位に揃えてみましょう。 エクセルで 1:30 のような値を 90(分)に変換するには、HOUR関数とMINUTE関数と組み合わせます。

HH:mmではなく、分単位に変換すると計算しやすくなります
HH:mmではなく、分単位に変換すると計算しやすくなります
HH:mmではなく、分単位に変換すると計算しやすくなります

例えば次のように使います。
=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) & “分”

単位が’「分」だと最後の結果がわかりにくいので、最後の結果はHH時間MM分の形式に戻します
単位が’「分」だと最後の結果がわかりにくいので、最後の結果はHH時間MM分の形式に戻します
単位が’「分」だと最後の結果がわかりにくいので、最後の結果はHH時間MM分の形式に戻します

ここまでエクセルデータサンプルをダウンロード

作業日報の入力をアプリ化して効率よくデータを用意する

今回サンプルに使用した作業日報は一般的な業務日報を1枚のエクセルシートにまとめたものです。 集計のためのデータがあればExcelの豊富な機能で様々な集計や分析が可能ですが、最大の問題は集計データの用意です。 日報は1日1枚提出されますが、個々の日報を1枚1枚見てエクセルに入力するのはあまりにも非効率です!

データを効率よく用意するために、日報の入力を見直してみませんか?

キャラクター

転記漏れや転記ミス、そして無駄な転記の時間を節約しよう

今回使ったデータは全てクラウド日報NipoPlusで入力したレポートをエクセルで使用しました。NipoPlusを使えばスマートフォンやタブレットから簡単に日報の作成ができます。

作業区分ごとに時刻を入力できる日報アプリです。所要時間を1枚のエクセルにまとめて出力できるのでデータの加工も簡単です作業区分ごとに時刻を入力できる日報アプリです。所要時間を1枚のエクセルにまとめて出力できるのでデータの加工も簡単です 作業区分ごとに時刻を入力できる日報アプリです。所要時間を1枚のエクセルにまとめて出力できるのでデータの加工も簡単です

そして作成されたレポートを今回の記事で使用したように、1枚のエクセルへ簡単に一括変換できるので、データの入力にも最適です。また、NipoPlus上からも簡単な集計やグラフ作成も可能ですので、 用途によってはエクセル無しで集計が完結できるかも?

無料でも使えるので是非お試しください。

tag エクセル tag 集計