Excelで勤務時間や作業時間の平均を計算していると、計算結果がおかしな表示になって困った経験はありませんか。
例えば、3日間の勤務時間が9時間、8時間30分、10時間で、平均すると9時間10分になるはずなのに、Excelで計算すると全く違う値が表示されたり、25時間の平均を出そうとしたら1時間と表示されたりします。
実はこれらの問題は、Excelの時間計算の仕組みと表示形式の設定を理解すれば、すぐに解決できます。
時間の平均がおかしくなる原因は主に3つあります。表示形式が適切でないこと、24時間を超える時間の扱い方を知らないこと、そして時間の入力方法を誤っていることです。
本記事では、時間の平均計算でよくある問題とその解決方法を、具体例を交えながら詳しく解説します。
勤怠管理や作業時間の集計を行っている方は、ぜひ参考にしてください。
ポイントは
・24時間を超える平均は表示形式を[h]:mmに設定する
・AVERAGE関数で時間の平均は正しく計算されている
・時間入力は0:25:30のように時間部分に0を付ける
です。
それでは詳しく見ていきましょう。
時間の平均がおかしくなる主な原因
まずは、なぜ時間の平均計算でおかしな結果が表示されるのか、その根本的な原因を理解しておきましょう。
24時間を超えると表示が巻き戻る現象
Excelで最もよく遭遇する問題が、24時間を超える平均値を計算すると、表示が0時に戻ってしまう現象です。
例えば、5日間の勤務時間がそれぞれ10時間、9時間30分、11時間、10時間30分、9時間だったとします。この平均は10時間ですが、通常の時間形式（h:mm）で表示すると、計算結果が正しく表示されません。
Excelは時間を内部的にシリアル値という数値で管理しており、24時間を「1」として扱っています。そのため、通常の表示形式では24時間を超えた部分が次の日として認識され、表示上は0時から始まってしまうのです。
具体的には、25時間の平均値は「1:00」と表示され、50時間なら「2:00」と表示されます。これは24で割った余りの時間だけが表示されているためです。
Excelでは1日（24時間）を「1」という数値で表現します。
そのため、1時間は1÷24で約0.041667、12時間は0.5という値になります。
25時間は1.041667という値として保存されており、通常の時間形式では小数点以下の部分（0.041667 = 1時間）だけが表示されます。
表示形式h:mmと[h]:mmの違い
時間の表示形式には「h:mm」と「[h]:mm」という2種類があり、この違いを理解することが時間平均の問題解決の鍵になります。
通常の「h:mm」形式は、時刻を表示するための形式です。これは0時から23時までの範囲で時刻を表示し、24時間を超えると0時に戻ります。例えば午後2時30分を表示するのに適しています。
一方、「[h]:mm」形式は、経過時間を表示するための形式です。角括弧で囲まれた[h]は、24時間を超えても時間を累積して表示し続けます。25時間なら「25:00」、50時間なら「50:00」と正しく表示されます。
勤務時間や作業時間の合計・平均を計算する場合は、必ず「[h]:mm」形式を使用する必要があります。
| 表示形式 | 用途 | 25時間の表示 | 50時間の表示 |
|---|---|---|---|
| h:mm | 時刻の表示 | 1:00 | 2:00 |
| [h]:mm | 経過時間の表示 | 25:00 | 50:00 |
| [h]:mm:ss | 秒単位の経過時間 | 25:00:00 | 50:00:00 |
| [m] | 分単位の表示 | 1500 | 3000 |
時間入力の間違いによる計算ミス
時間の平均がおかしくなるもう一つの原因が、時間データの入力方法の誤りです。
例えば、25分30秒を入力する際に「25:30」と入力すると、Excelはこれを「25時間30分」と解釈してしまいます。正しくは「0:25:30」と入力する必要があります。
持久走のタイムを記録する場合、多くの人が「5:30」と入力して5分30秒のつもりでいますが、Excelは5時間30分として認識します。このような入力が複数あると、平均値が大幅にずれてしまいます。
ただし、すべての記録が23分59秒以下であれば、「分:秒」のつもりで入力しても実用上は問題ありません。平均を計算する際も同様に「分:秒」として扱えば、結果の数値自体は正しくなります。
時間の入力形式には注意が必要です。
時:分:秒の形式で入力する場合、必ず3つの要素すべてを入力するか、セルの書式設定を事前に適切な形式に変更しておく必要があります。
特に分と秒だけのデータを扱う場合は、セルの表示形式を「m:ss」に設定してから「0:05:30」のように入力すると、表示は「5:30」となり見やすくなります。
AVERAGE関数で時間の平均を正しく計算する方法
それでは、実際に時間の平均を正しく計算する方法を確認していきましょう。
基本的なAVERAGE関数の使い方
時間の平均を計算するには、通常の数値と同様にAVERAGE関数を使用します。
AVERAGE関数は「=AVERAGE(範囲)」という形式で、指定した範囲の平均値を計算します。時間データに対しても、この関数は正しく平均値を計算してくれます。
例えば、A2からA6までのセルに勤務時間が入力されている場合、B2セルに「=AVERAGE(A2:A6)」と入力すれば、5日分の勤務時間の平均が計算されます。
関数を入力したら、Enter キーで確定します。この時点では計算結果がおかしく見えるかもしれませんが、実は数値としては正しく計算されています。問題は表示形式にあるのです。
| 日付 | 勤務時間（A列） | 数式（B列） | 結果 |
|---|---|---|---|
| 月曜日 | 9:00 | =AVERAGE(A2:A6) | 9:06（表示前） |
| 火曜日 | 8:30 | ||
| 水曜日 | 10:00 | ||
| 木曜日 | 9:00 | ||
| 金曜日 | 9:00 |
24時間を超える平均の表示形式設定手順
AVERAGE関数で計算した結果が24時間を超える場合、必ず表示形式を[h]:mmに変更する必要があります。
まず、平均値が表示されているセルを選択します。次に、セルを右クリックして「セルの書式設定」を選択します。
「表示形式」タブが開いたら、左側の「分類」から「ユーザー定義」を選択します。
右側の「種類」の入力欄に「[h]:mm」と入力します。角括弧を忘れずに入力することが重要です。
OKボタンをクリックすると、24時間を超える平均値が正しく表示されます。例えば、平均が25時間30分なら「25:30」と表示されます。
表示形式の設定手順：
1. 平均値セルを選択
2. 右クリック→「セルの書式設定」
3. 「ユーザー定義」を選択
4. 種類に「[h]:mm」と入力
5. OKをクリック
この設定により、何時間でも正しく表示されます。
もし秒単位まで表示したい場合は、「[h]:mm:ss」と入力します。分単位だけで表示したい場合は「[m]」と入力すると、総分数が表示されます。
ホームタブから素早く設定する方法
セルの書式設定ダイアログを開かずに、ホームタブから直接ユーザー定義書式を設定することもできます。
平均値が表示されているセルを選択した状態で、「ホーム」タブの「数値」グループにある小さな矢印ボタンをクリックします。
これでセルの書式設定ダイアログが開くので、前述と同じ手順で「ユーザー定義」から「[h]:mm」を設定できます。
また、よく使う表示形式は、「ホーム」タブの「数値」グループのドロップダウンから直接選択できますが、「[h]:mm」形式は標準では含まれていないため、ユーザー定義から設定する必要があります。
| 元のデータ（シリアル値） | h:mm形式 | [h]:mm形式 | 説明 |
|---|---|---|---|
| 1.0416667 | 1:00 | 25:00 | 25時間が正しく表示される |
| 2.0208333 | 0:30 | 48:30 | 48時間30分が表示される |
| 0.375 | 9:00 | 9:00 | 24時間未満は同じ |
| 1.5 | 12:00 | 36:00 | 36時間（1.5日分） |
よくある時間平均のトラブルと解決策
時間の平均計算でよく遭遇する具体的な問題と、その解決方法を確認していきます。
空白セルがある場合の平均計算
勤務時間データに空白セルがある場合、AVERAGE関数は空白セルを無視して計算します。
例えば、週5日勤務で土日が空白の場合、7日分ではなく5日分のデータだけで平均が計算されます。これは通常、望ましい動作です。
しかし、もし空白を0時間として扱いたい場合は、AVERAGEIF関数や条件付きの数式を使用する必要があります。
あるいは、空白セルに「0:00」と入力しておくという方法もあります。ただし、この場合は見た目で空白とデータなしの区別がつきにくくなります。
実務では、空白は「データなし」として扱い、AVERAGE関数でそのまま計算する方が自然です。
#DIV/0!エラーが表示される場合
時間の平均を計算しようとして「#DIV/0!」エラーが表示される場合があります。
これは計算対象となるセルがすべて空白の場合に発生するエラーです。AVERAGEは0で割り算をしようとするため、このエラーが表示されます。
解決方法は、IFERROR関数で囲んで、エラーの場合は空白または任意の文字列を表示させることです。
「=IFERROR(AVERAGE(A2:A6),””)」とすれば、エラーの場合は空白が表示されます。
または「=IFERROR(AVERAGE(A2:A6),”データなし”)」とすれば、データがないことが明確になります。
エラー処理を含めた時間平均の数式例：
=IFERROR(AVERAGE(A2:A6),””)
この数式なら、データがまだ入力されていない状態でもエラーが表示されず、データが入力されれば自動的に平均が計算されます。
月初の段階など、まだデータが揃っていない期間でも使いやすい数式になります。
時間と数値が混在している場合
時間データと数値データが混在しているシートで平均を取ろうとすると、正しい結果が得られないことがあります。
例えば、あるセルに「8:30」という時間形式のデータがあり、別のセルに「8.5」という数値形式のデータがある場合、AVERAGE関数はそれぞれを別の値として扱います。
8:30は約0.354というシリアル値として、8.5はそのまま8.5という数値として計算されるため、平均値がまったく意味のない値になってしまいます。
解決策は、すべてのデータを同じ形式で統一することです。時間形式なら全て時間形式に、数値形式なら全て数値形式に統一します。
数値を時間に変換するには、数値を24で割ってから表示形式を時間形式にします。逆に時間を数値に変換するには、時間データに24を掛けます。
| 問題のあるデータ | シリアル値 | 解決策 | 統一後 |
|---|---|---|---|
| 8:30（時間） | 0.354 | そのまま使用 | 8:30 |
| 8.5（数値） | 8.5 | =8.5/24で時間に変換 | 8:30 |
| 9:00（時間） | 0.375 | そのまま使用 | 9:00 |
| 9（数値） | 9 | =9/24で時間に変換 | 9:00 |
まとめ エクセルで時間平均がおかしい時の対処法
エクセルで時間の平均がおかしく表示される問題の解決方法をまとめると
・24時間を超える平均値は、表示形式を「[h]:mm」に設定することで正しく表示される、角括弧付きの[h]が重要で、通常のh:mmでは24時間で表示が巻き戻ってしまう
・AVERAGE関数は時間データに対しても正しく平均を計算している、問題は計算そのものではなく表示形式の設定にある
・時間の入力は「時:分:秒」の形式で行い、25分30秒なら「0:25:30」と入力する、「25:30」だと25時間30分と解釈されてしまう
・表示形式の設定は、セルを右クリック→セルの書式設定→ユーザー定義から「[h]:mm」と入力、秒まで表示なら「[h]:mm:ss」とする
・空白セルがある場合、AVERAGE関数は自動的に空白を無視して計算する、全てが空白の場合は#DIV/0!エラーになるため、IFERRORで対処
これらの対処法を理解すれば、勤怠管理や作業時間の集計で時間の平均を正確に計算できます。
特に重要なのは、Excelは時間を正しく計算しているが、表示形式が適切でないために結果がおかしく見えるだけという点です。
一度設定してしまえば、後は自動的に正しい表示で平均値が計算されます。
時間データを扱う際は、必ず表示形式を確認してから作業を進めるようにしましょう！
