【Excel】エクセルの平均がおかしい原因とAVERAGE関数の計算を確認する方法（時間平均・合計）
ExcelでAVERAGE関数を使ったのに平均がおかしいと感じるときは、数式そのものではなく、集計範囲、セルの表示形式、文字列として保存された数値などに原因があることが少なくありません。
特に時間の平均や合計では、24時間を超えた値の表示、空白セルとゼロの扱い、時刻データの入力方法によって、見た目と計算結果が一致しないように見える場合があります。
平均がおかしいときは、平均の対象範囲、数値か文字列か、ゼロの有無、時間の表示形式の順に確認することが近道です。
AVERAGE関数は数値を合計して数値の個数で割るため、文字列や空白、エラー値の存在で結果の見え方が変わります。
この記事では、エクセルの平均がおかしい原因とAVERAGE関数の計算を確認する方法について、通常の数値、時間平均、合計との違いを含めて解説していきます。
AVERAGE関数の計算範囲と数値データの確認
それではまず、AVERAGE関数の平均値を正しく計算するための基本的な確認方法について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 氏名 | テスト得点 | 平均 |
| 田中 | 80 | =AVERAGE(B2:B6) |
| 佐藤 | 75 | 79 |
| 鈴木 | 82 | |
| 高橋 | 78 | |
| 伊藤 | 80 |
AVERAGE関数の基本式と平均の考え方
AVERAGE関数は、指定したセル範囲にある数値の合計を、数値が入力されているセル数で割る関数です。
=AVERAGE(B2:B6)
上の例では、B2からB6までの得点を合計し、その件数で割った値が平均として表示されます。
数式を入力するセルを選択し、数式バーにAVERAGE関数を入力してEnterキーを押しましょう。
1行目がヘッダーの場合、実際のデータは通常2行目から始まるため、見出しセルを範囲に含めないことが大切です。
たとえばB1にテスト得点という文字があり、B2からB6に数値があるなら、=AVERAGE(B2:B6)が適切な式になります。
AVERAGE関数では文字列の見出しは原則として平均の分母に数えられませんが、範囲の指定を正確にする習慣は集計ミスの防止につながります。
数値が80、75、82、78、80なら、合計は395、件数は5件であり、平均は79です。
手計算の結果とセルの値を比較すると、数式の間違いかデータの問題かを切り分けやすくなります。
【操作のポイント】数式の参照範囲は、数式バーをクリックすると色枠で確認できます。
数式バーと参照範囲の確認
平均が想定より高い、または低いときは、まず平均値が表示されているセルをクリックします。
数式バーには、セルに入力されている数式と、AVERAGE関数が参照しているセル範囲が表示されます。
たとえば本来はB2からB10を平均するべきなのに、=AVERAGE(B2:B9)となっていれば、最終行の数値が計算に含まれていません。
反対に、過去月のデータや小計行まで含めてしまうと、意図しない平均になります。
小計、合計、平均という文字がある行を範囲内に含めると、数値セルだけが集計される場合でも、計算の意味が分かりにくくなります。
フィルターを使った表では、非表示の行もAVERAGE関数の対象です。
表示中の行だけを平均したい場合は、AVERAGEではなくSUBTOTAL関数やAGGREGATE関数を検討する場面もあります。
数式をコピーした場合には、参照範囲がずれていないかも確認しましょう。
固定したいセル参照には$記号を付け、相対参照との違いを理解しておくと、列ごとの平均や月別平均を作るときに役立ちます。
【操作のポイント】平均セルを選択し、数式バーと色付きの参照枠を見て、最初と最後のセルを照合します。
合計と件数による検算
AVERAGE関数の結果を検算する最も確実な方法は、SUM関数とCOUNT関数を別のセルに入力する方法です。
=SUM(B2:B6)
=COUNT(B2:B6)
=SUM(B2:B6)/COUNT(B2:B6)
SUM関数の合計とCOUNT関数の数値件数から算出した値がAVERAGE関数と一致すれば、基本的な計算は正しく行われています。
一致しない場合は、AVERAGEの参照範囲とSUMやCOUNTの参照範囲が同じかを確認してください。
COUNT関数は数値だけを数えるため、見た目は数字でも文字列として保存されたセルは件数に入りません。
COUNTが想定より少ないなら、平均から除外されているセルがある可能性を疑いましょう。
数値を文字列にしたまま入力している場合、セルの左上に緑色の三角形が表示されることがあります。
そのセルを選択して警告アイコンから数値に変換するか、VALUE関数を利用して数値化すると改善することがあります。
【操作のポイント】AVERAGEだけで判断せず、合計と数値件数を並べて表示すると原因を見つけやすくなります。
空白セルとゼロが平均値に与える影響
続いては、空白セル、ゼロ、文字列が平均計算にどのように影響するかを確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| 担当者 | 販売数 | 入力状態 |
| 田中 | 10 | 数値 |
| 佐藤 | 0 | ゼロ |
| 鈴木 | 空白 | |
| 高橋 | 8 | 数値 |
空白セルが平均の分母に入らない仕組み
AVERAGE関数は、完全に空白のセルを平均の分母に含めません。
販売数が10、空白、8の場合、合計18を3件で割るのではなく、数値がある2件で割るため、平均は9になります。
未入力を集計対象外にしたいとき、空白セルは便利な扱いです。
ただし、数式の結果として空文字列が返っているセルは、見た目が空白でも扱いを慎重に確認する必要があります。
たとえば=IF(C2=””,””,C2)のような式を使っている表では、セルには数式が存在しています。
見た目だけで空白と判断せず、数式バーを見て本当に何も入力されていないセルかを確認しましょう。
入力漏れがあるのに空白を除外して平均すると、実績が高めに見えることがあります。
平均の目的が、入力済みデータだけの評価なのか、対象者全員の評価なのかを先に決めることが重要です。
【操作のポイント】未入力を除外する平均なら空白のままにし、未実施をゼロとして扱う場合だけ0を入力します。
ゼロを含めた平均と除外した平均
数値の0は空白ではないため、AVERAGE関数では通常の数値として分母に含まれます。
10、0、8の平均は、合計18を3件で割るため6です。
ゼロを販売なし、欠勤、未達成として評価に含めたいなら、そのままAVERAGE関数を使えます。
一方で、ゼロを未入力の代替として入力している場合は、平均が不必要に低くなる原因になります。
ゼロを除外して平均したい場合の例
=AVERAGEIF(B2:B5,”<>0″)
AVERAGEIF関数では、条件に合うセルだけを平均できます。
“<>0″はゼロではないという条件を表し、0以外の数値だけを平均する指定です。
ただし、ゼロを除外することが業務上正しいかどうかは別の問題です。
欠損値と実際のゼロを混同すると、集計結果の信頼性が下がるため、入力ルールを統一しておきましょう。
【操作のポイント】0を除外する式を使う前に、その0が実績ゼロか未入力の代替かを確認します。
文字列の数値とエラー値の判定
セルに80と表示されていても、先頭にアポストロフィが付いている、全角数字が混ざっている、外部データを貼り付けたなどの理由で文字列になっていることがあります。
文字列の数値はAVERAGE関数で計算対象にならず、平均が高く見える原因になる場合があります。
セルを選択してホームタブの表示形式を確認し、左寄せになっている数字や緑色のエラー表示を探しましょう。
エラー値が範囲内にあると、AVERAGE関数はエラーを返します。
#DIV/0!や#VALUE!が表示されるときは、平均関数より前に元データの数式や入力内容を修正する必要があります。
エラーを除外して平均を出す必要がある場合は、IFERROR関数を補助列で使う方法や、データの入力規則を整える方法があります。
ただし、エラーを単に0へ置き換えると平均が変わるため、元のエラーの意味を確認してから処理することが大切です。
【操作のポイント】貼り付けた数値は、エラー表示と配置を確認し、必要に応じて数値へ変換します。
時間平均と合計時間の表示形式
続いては、時間の平均や合計が想定どおりに表示されないときの確認方法を解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 日付 | 作業時間 | 平均時間 |
| 4月1日 | 8:30 | =AVERAGE(B2:B4) |
| 4月2日 | 7:45 | 8:00 |
| 4月3日 | 7:45 |
Excelにおける時間データの内部計算
Excelでは、時間は1日を1とする小数として内部的に保存されています。
12時間は0.5、6時間は0.25として計算されるため、時間をAVERAGE関数で平均すること自体は可能です。
=AVERAGE(B2:B4)
表示形式は h:mm または [h]:mm を設定します。
8時間30分、7時間45分、7時間45分の平均は8時間です。
平均結果が0.333333のような小数で見える場合は、計算が間違っているのではなく、セルの表示形式が標準になっている可能性があります。
平均セルを右クリックし、セルの書式設定から時刻またはユーザー定義を選択して、h:mm形式に変更しましょう。
時刻として入力する場合は、8.5ではなく8:30のようにコロンを使って入力します。
8.5と入力した数値は8時間30分ではなく、8.5日として扱われるため注意が必要です。
【操作のポイント】時間の平均セルは、数式だけでなく表示形式もh:mmに設定します。
二十四時間を超える合計時間の表示
勤務時間や作業時間をSUM関数で合計したとき、合計が24時間を超えると表示が0時から繰り返されることがあります。
たとえば8時間を4日分合計すると32時間ですが、通常のh:mm形式では8:00と表示される場合があります。
これは時刻形式が一日の時刻として表示され、24時間ごとに繰り返されるためです。
累計時間を正しく表示したいときは、セルの書式設定でユーザー定義を開き、[h]:mmを指定します。
角かっこ付きの[h]は、24時間を超えても時間を繰り返さず、32:00のように累計時間として表示する書式です。
分まで合計するなら[h]:mm、秒まで必要なら[h]:mm:ssが実用的です。
数式が=SUM(B2:B5)で正しくても、書式だけで結果が誤って見えるため、合計と表示形式をセットで確認しましょう。
【操作のポイント】24時間を超える合計には、時刻形式ではなくユーザー定義の[h]:mmを使います。
時刻入力と経過時間の計算
開始時刻と終了時刻から作業時間を求める場合は、終了時刻から開始時刻を引きます。
開始時刻がB2、終了時刻がC2の場合
=C2-B2
日付をまたぐ勤務の場合
=MOD(C2-B2,1)
終了時刻が翌日になる夜勤では、単純な引き算で負の時間となり、#######と表示されることがあります。
MOD関数を使うと、一日をまたいだ差を正の時間として求められます。
計算した経過時間の列をAVERAGE関数で平均すれば、平均作業時間や平均対応時間を算出できます。
開始と終了の時刻、経過時間、平均時間では、それぞれ適切な書式を設定することが重要です。
【操作のポイント】日付またぎの時間差にはMOD関数を使い、結果のセルは[h]:mmで表示します。
AVERAGE関数とAVERAGEIF関数の使い分け
続いては、条件付きの平均を出すときに役立つAVERAGEIF関数とAVERAGEIFS関数を確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| 部門 | 売上 | 判定 |
| 営業 | 120 | 対象 |
| 開発 | 90 | 対象外 |
| 営業 | 140 | 対象 |
条件なしの平均に適したAVERAGE関数
すべての数値を同じ基準で平均する場合は、AVERAGE関数が最も分かりやすい選択です。
=AVERAGE(B2:B10)のように、平均したい数値範囲だけを指定します。
日別の売上、テスト得点、作業時間など、条件を分けず全体の傾向を知りたい場面に向いています。
ただし、別部門や別商品のデータが混在しているなら、全体平均だけでは実態を把握しにくいかもしれません。
集計目的が全体比較なのか、条件に一致するデータだけの比較なのかで、使用する関数を選びます。
平均セルには、どの範囲を何の基準で集計した値なのかを近くに記載しておくと、後から表を見直す人にも親切です。
【操作のポイント】比較条件がない全体集計にはAVERAGE関数を使用します。
一つの条件を指定するAVERAGEIF関数
営業部門だけの平均売上を出したい場合は、AVERAGEIF関数を使います。
=AVERAGEIF(A2:A4,”営業”,B2:B4)
最初の範囲は条件を確認するセル範囲、次の営業は条件、最後の範囲は平均する数値範囲です。
この例では、A列が営業である行だけを抽出し、その行のB列の売上を平均します。
営業の売上が120と140なら、結果は130です。
条件範囲と平均範囲は同じ行数にそろえる必要があります。
条件範囲がA2からA4なのに平均範囲がB2からB10のようにずれると、意図しない結果やエラーの原因になります。
セルに部門名を入力し、そのセルを条件に指定すれば、部門を切り替えるだけで平均を変更できる集計表も作れます。
【操作のポイント】AVERAGEIFでは、条件を調べる列と平均する列の開始行と終了行をそろえます。
複数条件を扱うAVERAGEIFS関数
部門に加えて月や担当者など、複数の条件を指定したい場合はAVERAGEIFS関数を使います。
=AVERAGEIFS(C2:C20,A2:A20,”営業”,B2:B20,”>=2026/4/1″,B2:B20,”<2026/5/1″)
この式は、営業部門であり、指定した期間内に該当するC列の数値を平均する考え方です。
AVERAGEIFS関数では、最初に平均範囲を指定し、その後に条件範囲と条件を組み合わせます。
条件に一致する数値が一つもない場合は、#DIV/0!エラーになります。
その場合は、条件の表記ゆれ、日付が文字列になっていないか、対象期間の指定を確認しましょう。
平均値がエラーになるときは、計算式よりも条件に一致するデータが実際に存在するかを先に確認します。
【操作のポイント】複数条件の平均では、平均範囲を最初に指定するAVERAGEIFSの引数順を確認します。
平均値がおかしいと感じたときの確認手順
続いては、実務で平均値の違和感を見つけたときに、効率よく原因を絞り込む手順を確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| 確認項目 | 確認内容 | 原因例 |
| 範囲 | 最初と最後の行 | 小計行を含む |
| 件数 | COUNT関数 | 文字列の数値 |
| 書式 | 時刻表示 | 24時間超の折り返し |
極端な値と入力ミスの抽出
平均は一つの極端な数値によって大きく変わります。
得点の列に800、作業時間の列に80:00など、桁を一つ多く入力したセルがないかを確認しましょう。
並べ替えを使って数値を大きい順または小さい順にすると、外れ値を見つけやすくなります。
ただし、元の順番が必要な表では、並べ替え前に別の列へ連番を付けるか、コピーした表で確認すると安全です。
平均値だけを見るのではなく、最小値、最大値、件数を並べると、異常値の影響を判断しやすくなります。
MAX関数とMIN関数を利用すれば、範囲内の最大値と最小値をすぐに確認できます。
【操作のポイント】平均がおかしいときは、対象列を並べ替え、極端に大きい値と小さい値を確認します。
非表示行とフィルターの集計範囲
表にフィルターを設定して一部の行を非表示にしても、AVERAGE関数は通常、非表示の行を含めて計算します。
画面上で見えているデータだけの平均を期待していると、表示結果と計算結果に差があるように感じます。
フィルター後の可視セルだけを平均したい場合、SUBTOTAL関数の関数番号101を使う方法があります。
=SUBTOTAL(101,B2:B20)
101は非表示行を除外して平均する指定です。
フィルターで除かれた行だけを対象外にするか、手動で非表示にした行も除外するかによって、関数番号の選び方が変わります。
フィルターを利用した集計では、AVERAGEとSUBTOTALの集計対象の違いを意識しましょう。
【操作のポイント】絞り込み後の表示行だけを平均したいなら、SUBTOTAL関数を検討します。
再計算設定と数式の更新状態
入力値を変更しても平均が更新されない場合は、計算方法の設定が手動になっている可能性があります。
数式タブから計算方法の設定を確認し、自動が選択されているかを見ます。
手動計算では、F9キーで再計算するまで結果が古いまま表示されることがあります。
また、コピー元の数式に参照先の固定が含まれていると、オートフィル後も同じ範囲を平均し続ける場合があります。
平均が更新されないときは、セルの値、数式、参照範囲、再計算設定の四つを順番に確認します。
共有ブックや外部参照を使っている場合は、参照先ファイルが更新されているかも確認が必要です。
【操作のポイント】値を変更したら、平均セルの数式が更新されているかを数式バーで確認します。
まとめ エクセルの平均がおかしい原因と計算確認の方法
エクセルの平均がおかしいと感じる原因は、AVERAGE関数の参照範囲、空白とゼロの扱い、文字列の数値、時間表示の設定などに分けて考えられます。
通常の数値では、AVERAGE関数の範囲を確認し、SUM関数とCOUNT関数で合計と件数を検算すると、計算内容を確かめやすくなります。
空白セルは平均の分母に含まれず、ゼロは含まれるため、未入力と実績ゼロを同じ扱いにしないことが重要です。
条件を絞った平均にはAVERAGEIF関数やAVERAGEIFS関数を使い、条件範囲と平均範囲の行数をそろえましょう。
時間平均はh:mm形式、24時間を超える合計時間は[h]:mm形式に設定すると、正しい値を読み取れます。
平均値に違和感があるときは、極端な値、フィルターで隠れた行、計算方法の設定も確認してください。
平均の結果だけを見るのではなく、元データ、合計、件数、表示形式まで順に見直すことで、Excelの集計ミスを防ぎやすくなります。
