【Excel】エクセルで時間の平均がおかしい（24時間以上・計算・表示形式の設定・引き算た足し算も）原因と対処法

Excelで平均を計算していると、「AVERAGE関数の結果がおかしい」「時間の平均を出したら意味不明な数値になった」「0が含まれるデータの平均が期待と違う」といったトラブルに遭遇することがあります。

平均の計算はExcelの基本的な操作のひとつですが、データの型や表示形式によって結果が変わることがあるため注意が必要でしょう。

この記事では、Excelの平均・時間の平均がおかしくなる原因と、時間計算・表示形式の設定を含めた具体的な対処法をわかりやすく解説していきます。平均に関するトラブルをひとつずつ確認していきましょう。

ExcelのAVERAGE関数の結果がおかしい基本的な原因と対処法

それではまず、ExcelのAVERAGE関数の結果がおかしくなる基本的な原因と対処法について解説していきます。

AVERAGE関数の結果がおかしい場合に最初に疑うべきは、計算対象のデータに文字列や空白・0が混在していることです。

AVERAGE関数は文字列と空白セルを無視して計算しますが、0は数値として計算に含まれるため、0の扱いによって結果が大きく変わります。

文字列が混在してAVERAGEの結果がおかしい場合の対処法

データの中に文字列として保存された数値が混在していると、AVERAGE関数がその値を無視して計算するため結果がおかしくなります。

ステータスバーの「個数」と「数値の個数」を比較することで文字列の混在を素早く確認できます。両者の数値が一致していればすべて数値として認識されているため、AVERAGE関数が正しく機能しているといえるでしょう。

0を除いた平均を計算する方法

データに0が含まれていて、0を除いた値だけで平均を求めたい場合はAVERAGEIF関数を使いましょう。

 

AVERAGEIF関数は条件を満たすセルのみを対象に平均を計算できる非常に便利な関数です。「<>0」という条件で0以外のデータを対象にした平均を求められます。条件が複数ある場合はAVERAGEIFS関数に切り替えましょう。

空白セルの扱いによって平均がおかしくなる場合の対処法

AVERAGE関数は空白セルを無視して計算しますが、空白に見えてもスペースが入力されていると文字列として扱われて計算がおかしくなることがあります。

スペースが入力されたセルは見た目では空白と区別がつきません。「検索と置換」でスペースを一括削除する方法が最も効率的な対処法といえるでしょう。大量のデータを扱う場合に特に役立ちます。

 

症状	原因	対処法
AVERAGEの結果が少なすぎる・多すぎる	文字列データが計算から除外されている	データ型を数値に統一してから再計算する
0を含む平均が正しくない	AVERAGE関数が0を計算に含めている	AVERAGEIF関数で「<>0」条件を指定する
空白セルが平均に影響している	空白セルにスペースが入力されている	「検索と置換」でスペースを一括削除する

 

Excelの時間の平均がおかしい原因と対処法

続いては、Excelの時間の平均がおかしくなる原因と対処法を確認していきます。

時間データの平均がおかしい場合、表示形式が時刻形式になっておらず数値やシリアル値がそのまま表示されていることが主な原因として挙げられます。AVERAGE関数自体は時間データでも正しく動作しますが、結果の表示形式を正しく設定しなければ意味不明な数値が表示されてしまいます。

時間の平均をAVERAGE関数で正しく計算・表示する方法

時間データの平均はAVERAGE関数で計算できますが、結果を表示するセルの表示形式を必ず時刻形式に設定しましょう。

時間の平均を求めるAVERAGE関数の計算自体は正確です。結果を表示するセルの表示形式を「時刻」または「h:mm」に設定するだけで正しい平均時間が表示されます。データを修正する必要はなく、書式の設定変更だけで解決できるでしょう。

24時間を超える時間の平均が正しく表示されない場合の対処法

勤務時間の月次平均など、24時間を超える可能性がある時間の平均計算では通常の時刻形式では正しく表示されません。

時間の平均を分・時間単位の数値として取り出す方法

時間の平均を時刻形式ではなく「150分」「2.5時間」のような数値として表示したい場合は、シリアル値に変換係数を掛けて計算します。

時間の平均を数値として活用したい場合はシリアル値に24を掛けることで時間単位の数値に変換できます。給与計算や作業効率の分析など、時間を数値として扱いたい場面で非常に役立つ方法でしょう。

Excelの平均計算に関するその他のトラブルと対処法

続いては、平均計算に関するその他のトラブルと対処法を確認していきます。

基本的な平均計算のトラブル以外にも、加重平均の計算方法・移動平均の設定・条件付き平均のおかしな結果など、応用的な場面でトラブルが発生することがあります。それぞれの対処法を確認しておきましょう。

加重平均を正しく計算する方法

単純平均ではなく重みをつけた加重平均を求めたい場合、AVERAGE関数では正しく計算できません。SUMPRODUCT関数を使って加重平均を算出しましょう。

SUMPRODUCT関数は配列同士の積の合計を計算する関数です。加重平均の計算には「各値×重み」の合計を「重みの合計」で割る方法が基本となります。売上分析や学業成績の計算など幅広い場面で活用できるでしょう。

AVERAGEIF関数の条件設定がおかしい場合の対処法

AVERAGEIF関数で条件を設定しても期待した結果にならない場合、条件の記述方法に誤りがあることが多いでしょう。

AVERAGEIF関数の条件は文字列として入力する必要があります。比較演算子を使う場合はダブルクォーテーション（”）で囲むことを忘れずに設定しましょう。セル参照と組み合わせる場合は「&」で連結する方法を覚えておくと便利です。

移動平均がおかしい場合の対処法

過去N期間の移動平均を計算したい場合にAVERAGE関数の範囲指定がずれてしまうことがあります。OFFSET関数を使うことで動的に移動平均を計算できます。

移動平均はOFFSET関数とAVERAGE関数を組み合わせることで数式を下にコピーするだけで自動的に範囲がずれる動的な計算が実現できます。データ分析ツールの移動平均機能を使う方法も視覚的でわかりやすく便利でしょう。

まとめ

この記事では、Excelの平均・時間の平均がおかしくなる原因と、時間計算・表示形式の設定を含めた具体的な対処法をまとめて解説しました。

AVERAGE関数のトラブルはデータ型の混在や0・空白の扱いが主な原因で、AVERAGEIF関数を活用することで条件付きの正確な平均計算が実現できます。時間の平均は表示形式を「h:mm」または「[h]:mm」に設定することで正しく表示されるでしょう。

加重平均にはSUMPRODUCT関数、移動平均にはOFFSET関数とAVERAGE関数の組み合わせが有効です。今回紹介した方法を参考に、目的に合った正確な平均計算を実現してみてください。