【Excel】エクセルでの時間・時刻の平均がおかしい原因と対処法（時間計算・表示形式の設定方法も）
エクセルで時間や時刻の平均を求めようとしたとき、「AVERAGE関数の結果がおかしい」「平均時間が正しく表示されない」「時刻のデータを合計・平均しても数値がずれる」といった問題が発生することがあります。
時間・時刻の計算トラブルは、エクセルの時間シリアル値・表示形式・24時間超えの扱いへの理解不足から生じることがほとんどです。本記事では時間・時刻の平均がおかしい原因と正しい対処法を詳しく解説していきます。
時間・時刻のAVERAGE関数がおかしい原因と対処法
それではまず、AVERAGE関数を使って時間・時刻の平均を求める際に発生するトラブルの原因と対処法について解説していきます。
時刻データの表示形式が「数値」になっていて正しく表示されない
AVERAGE関数で時刻の平均を計算した結果、「0.52」などの小数が表示される場合があります。これはエクセルが時刻を内部的に0〜1の小数（シリアル値）として管理しているためです。
対処法：平均値セルの表示形式を「時刻」に変更する
1. AVERAGE関数の結果が入ったセルを選択する
2. 「セルの書式設定」→「表示形式」→「時刻」を選択する
3. または「ユーザー定義」で「h:mm」と入力して時刻形式で表示させる
時間データに文字列が混在して平均が正しく出ない
「1:30」のような時刻データが文字列として入力されている場合、AVERAGEの計算対象から除外されてしまいます。セルの左上に緑の三角が表示されているデータは文字列の可能性があるため確認が必要です。
対処法：TIMEVALUE関数で文字列の時刻をシリアル値に変換する
=TIMEVALUE(“1:30”) → 1時間30分を時刻シリアル値（0.0625）に変換する
空白セルや0が混在した時間データの平均を正しく求める方法
時間データの一部が空白（欠勤・休日など）の場合、AVERAGEは空白を無視して計算しますが、0が入力されている場合は0も計算に含まれます。
=AVERAGEIF(A1:A10,”0″) → 0を除いた時間データの平均を求める
=AVERAGEIF(A1:A10,””) → 空白と0の両方を除外して平均を求める
24時間を超える時間の計算・平均がおかしい場合
続いては、24時間を超える時間データの計算・平均がおかしくなる原因と対処法を確認していきます。
24時間を超える時間が正しく合計されない
1週間の合計勤務時間のように24時間を超える時間を合計すると、表示が「5:30」（実際は29時間30分）のように24時間分が切り捨てられて表示されることがあります。
対処法：表示形式を「[h]:mm」に変更する
1. 合計・平均のセルを選択する
2. 「セルの書式設定」→「ユーザー定義」をクリックする
3. 「[h]:mm」と入力してOKをクリックする
4. 24時間を超えても正しい時間数で表示されるようになる
時間の平均が24時間を超えた場合の表示設定
AVERAGE関数で時間の平均を求めた結果が24時間を超える場合も同様に、表示形式を「[h]:mm」に設定することで正しく表示できます。
| 表示形式 | 表示例（29時間30分の場合） | 説明 |
|---|---|---|
| h:mm | 5:30 | 24時間超えが切り捨てられる |
| [h]:mm | 29:30 | 24時間を超えても正しく表示 |
| h”時間”mm”分” | 5時間30分 | 漢字付きで表示（24時間超え切り捨て） |
| [h]”時間”mm”分” | 29時間30分 | 漢字付きで24時間超えも正しく表示 |
INT関数とMOD関数で時間を時・分・秒に分解する方法
シリアル値として格納された時間データを時・分・秒に分解して表示したい場合は、HOUR・MINUTE・SECOND関数を使います。
=HOUR(A1) → 時間部分を取得する（例：1:30:00 → 1）
=MINUTE(A1) → 分部分を取得する（例：1:30:00 → 30）
=SECOND(A1) → 秒部分を取得する（例：1:30:45 → 45）
時間の引き算・差分計算がおかしい場合の対処法
続いては、時間の引き算や差分計算がおかしくなる場合の対処法を確認していきます。
退勤時間から出勤時間を引くとマイナスになる場合
日をまたいで勤務した場合（例：23:00出勤→翌7:00退勤）など、退勤時間が出勤時間より小さい時刻になることがあります。この場合、単純な引き算では結果がマイナスになり「######」と表示されます。
対処法：IF関数で日をまたいだ計算に対応する
=IF(B1>=A1, B1-A1, B1-A1+1)
→ B1（退勤）がA1（出勤）以上なら通常の引き算、小さければ+1（1日分のシリアル値）を加算する
時間計算の結果を分単位・秒単位の数値に変換する
時間の差分を分や秒の数値として扱いたい場合（例：グラフのデータとして使いたい場合）は、シリアル値を変換する必要があります。
時間差を分数値で取得する：=(B1-A1)*24*60
時間差を秒数値で取得する：=(B1-A1)*24*60*60
例：30分の差 → (B1-A1)*24*60 = 30（数値として使用可能）
TIME関数で時間データを正しく入力する方法
時・分・秒を指定して時刻シリアル値を作成したい場合は、TIME関数を使います。
=TIME(時,分,秒)
例：=TIME(9,30,0) → 9時30分のシリアル値を生成する
=TIME(HOUR(A1),MINUTE(A1)+30,0) → A1の時刻から30分後を計算する
エクセルの時間計算では「表示形式」と「実際のシリアル値」を常に区別して考えることが重要です。画面上で「1:30」と表示されていても内部値は「0.0625」であるため、数値として計算する際は必ず24×60などの変換を行う必要があります。
まとめ
本記事では、【Excel】エクセルでの時間・時刻の平均がおかしい原因と対処法として、AVERAGE関数の時刻への適用・24時間超えの表示設定・引き算のマイナス問題・TIME関数の活用まで詳しく解説しました。
時間・時刻計算のトラブルのほとんどは「[h]:mm」という表示形式の設定と、シリアル値への理解によって解決できます。時間データを扱う際はこの2点を常に意識して設定することで、計算ミスやトラブルを大幅に減らすことができるでしょう。
