「この平均、ほんとに合ってますか？」
前の職場で、店舗別の客単価をまとめた資料を出したときに、ある店長からそう言われたことがあります。使った数式は =AVERAGE(...) ひとつだけ。3分で終わる仕事のはずでした。
慌てて元データを開いて、そこでようやく気づきました。数式は何も間違っていなくて、悪かったのは表のほうだったんです。臨時休業した日の行に、誰かが親切のつもりで「0」を入力していました。売上がなかったのだから0でいい——気持ちはわかります。でもAVERAGE関数から見れば、その0は「その日は客単価が0円だった」という立派なデータです。分母に3日分が足され、平均は一気に下がっていました。
AVERAGE関数は、書き方だけならExcelの中でも一、二を争うくらい簡単です。それなのに、出てきた数字が現場の感覚とズレることがいちばん多い関数でもあります。今回はその「ズレ」がどこで生まれるのかを、原因ごとに切り分けながら整理していきます。
そもそもどの関数を選べばいいのか迷う方は、目的別に整理したこちらの記事から読むのがおすすめです。
平均がズレているとき、疑う場所は4つしかない
原因を頭から順に探すと時間がかかります。私はいつも、この4つを上から確認するようにしています。自分の表がどれに当てはまるか、先に見当をつけてから読み進めてください。
| 症状 | 疑うべき原因 |
|---|---|
| 平均が思ったより低い | 「0」が分母に混ざっている |
| 平均が思ったより高い | 本来含めたいデータが計算から漏れている |
| ひとつのデータに引っ張られている感じがする | 外れ値の影響 |
| 条件を付けた平均だけ合わない | 引数の並びか、範囲のサイズ違い |
この4つのどれでもなければ、表示形式の丸めを確認してみてください。私の経験では、この5か所でほぼ全部です。
1. 同じ売上データなのに、平均が3種類できてしまう話
言葉で説明するより、実際の数字を見たほうが早いと思います。10日分の売上（単位：千円）があって、そのうち3日は臨時休業だったとします。営業した7日間の売上は次のとおりです。
120 / 140 / 95 / 130 / 110 / 150 / 105（合計850）
この7日分だけの平均は、850 ÷ 7 で約121.4です。では、休業した3日分をどう記録するかで、AVERAGE関数の答えがどう変わるか見てみます。
| 休業日の入力内容 | AVERAGEの結果 | Excelの解釈 |
|---|---|---|
| 何も入力しない（空白） | 約121.4 | 7個のデータの平均 |
| 「0」と入力 | 85 | 10個のデータの平均 |
| 「休」と文字で入力 | 約121.4 | 文字は無視して7個の平均 |
| 「-」（ハイフン）と入力 | 約121.4 | 同じく文字扱いで無視 |
同じ売上実績なのに、121.4と85.0という、まったく印象の違う数字が出てきます。85という数字を見た人は「この店は不調だ」と判断するでしょうし、121という数字を見た人は「悪くない」と判断するでしょう。表の作り方ひとつで、読む人の結論が変わってしまうわけです。
大事なのは、どちらが正解かではありません。「休業日を含めた1日あたりの売上」を知りたいなら0を入れるのが正しいですし、「営業した日の平均」を知りたいなら空白にしておくのが正しい。正解が状況によって変わるからこそ、表を作る段階で「この空白は何を意味しているのか」を決めておく必要があります。
私は、集計用の表を作るときは必ず備考欄を1列つくって、そこに「臨時休業」と書き残すようにしています。数値のセルは空けておき、理由は別の列に文字で書く。こうしておけば、半年後にファイルを開き直した自分も、引き継いだ同僚も迷いません。
2. AVERAGE関数の基本の書き方は、実は4パターンある
範囲をドラッグする書き方しか知らない、という方が意外と多いのですが、AVERAGE関数の引数の指定方法は1つではありません。
基本の手順
- 平均を表示したいセルをクリックする
- 「=AVERAGE(」と入力する
- 平均を求めたいセル範囲をドラッグで選択する
- 「)」を入力してEnterキーを押す
書き方のバリエーション
=AVERAGE(A2:A11) … 連続した範囲の平均
=AVERAGE(A2:A11,C2:C11) … 離れた2つの範囲をまとめて平均
=AVERAGE(B2,D2,F2) … 飛び飛びのセルを個別に指定
=AVERAGE(A2:A11,100) … 範囲と数値を混ぜて指定2つ目の「離れた範囲をまとめる」書き方は、上期と下期の表が分かれているようなときに重宝します。カンマで区切るだけで、2つの範囲を1つのかたまりとして平均してくれます。引数は最大255個まで指定できるので、実務で足りなくなることはまずありません。
ちなみに、ちょっと確認したいだけなら関数を入れる必要すらありません。セル範囲をドラッグして選択すると、画面下のステータスバーに平均値が表示されます。ステータスバーを右クリックすれば、平均・データの個数・数値の個数などの表示項目を選べるので、「平均」と「数値の個数」の2つにチェックを入れておくと、次の章の検算がその場でできて便利です。
3. AVERAGEが「何個のセルを見たのか」を数えて検算する
平均が高すぎる気がするときは、分子ではなく分母を疑ってください。AVERAGE関数は、計算に使えないセルを黙って飛ばします。エラーも警告も出ません。だから「入れたはずのデータが数えられていない」という事故に気づきにくいのです。
確認方法はシンプルで、隣のセルにこの2つを並べるだけです。
=COUNT(A2:A11) … 数値として認識されているセルの個数
=COUNTA(A2:A11) … 空白以外のセルの個数10行分のデータを入れたつもりなのにCOUNTが7しか返さないなら、3つは数値として認識されていません。さらにCOUNTAが10なら、その3つには何かが入っている——つまり数値が文字列になっている、ということです。原因はたいてい、全角で入力された数字、前後に混ざった空白、他システムからコピーしたときに付いてくる見えない文字のどれかです。
数値が文字列になってしまう原因と、それを元に戻す手順については、数値が文字列になってしまう原因と直し方で症状別に詳しくまとめています。合計が合わないときの診断手順がそのまま平均にも使えるので、COUNTの数が合わなかった方はこちらもあわせて確認してみてください。
なお =SUM(A2:A11)/COUNT(A2:A11) は、AVERAGE関数とまったく同じ結果になります。AVERAGEが内部で何をしているのかを確かめたいときは、この式を一度並べて書いてみると納得できると思います。
4. 「0は数えたくない」ときの、いちばん短い書き方
第1章で見たように、0が混ざると平均は下がります。とはいえ、元データの0を消して回るのは現実的ではありません。伝票データやシステムからの出力を勝手に書き換えるわけにはいかないからです。
そんなときは、AVERAGE関数ではなくAVERAGEIF関数を使って、条件のほうで0をよけます。
=AVERAGEIF(A2:A11,"<>0") … 0以外のセルだけで平均する
=AVERAGEIF(A2:A11,">0") … プラスの値だけで平均する（マイナスも除外）AVERAGEIFは本来「条件範囲・条件・平均対象範囲」の3つを指定する関数ですが、3つ目を省略すると、条件範囲そのものを平均してくれます。この省略形は覚えておくと出番が多いです。
返品やマイナス調整が入るデータでは、2つ目の ">0" を使うかどうかで意味が変わります。マイナスも実績のうちと考えるなら "<>0"、プラスの取引だけを見たいなら ">0"。ここは業務のルール次第なので、資料の欄外に「0円の日を除く」と一言添えておくと、読む人が迷いません。
5. AVERAGEIFとAVERAGEIFSは、引数の順番が逆
条件付きの平均でつまずく原因の8割は、この一点だと思っています。
=AVERAGEIF(条件範囲, 条件, 平均対象範囲)
=AVERAGEIFS(平均対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2)見比べてください。AVERAGEIFは「平均したい範囲」が最後、AVERAGEIFSは「平均したい範囲」が最初です。条件を1つ増やそうとしてIFをIFSに書き換えたときに、引数の順番まで入れ替える必要があることを忘れて #DIV/0! や妙な数字が出る——これが本当によくあります。私も一度、条件列の平均値を延々と眺めていたことがあります。
もうひとつ注意したいのが、範囲のサイズ違いです。
=AVERAGEIF(A2:A11,"東京",B2:B20)条件範囲は10行なのに、平均対象範囲を20行分指定してしまった例です。この場合Excelはエラーを出さず、平均対象範囲の左上（B2）を起点に10行分だけを使って計算します。つまり、意図しない範囲で計算されているのに、結果はもっともらしい数字で返ってくるということです。条件範囲と平均対象範囲は、必ず同じ行数で指定してください。
そして、条件に合うデータが1件もなければ #DIV/0! が返ります。合計を求めるSUMIFなら0が返るところなので、この挙動の違いも頭の隅に置いておくと安心です。
条件の書き方そのもの（部分一致、期間指定、セル参照で条件を渡す方法など）は、条件付き集計が合わないときの確認ポイントで7つのパターンに分けて解説しています。AVERAGEIFはSUMIFと条件の書き方が共通なので、そのまま応用できます。
また、条件範囲の中身が途中から別の意味に変わっている表では、条件そのものが正しく効きません。心当たりのある方は列の意味が途中で変わっている表の見抜き方もご覧ください。
6. AVERAGEAは「文字を無視する関数」ではありません
ここは誤解が広まりやすいところなので、はっきり書いておきます。AVERAGEA関数は、文字列を無視しません。0として計算に含めます。
| セルの中身 | AVERAGEの扱い | AVERAGEAの扱い |
|---|---|---|
| 数値 | 計算に含む | 計算に含む |
| 空白 | 無視 | 無視 |
| 文字列（「休」など） | 無視 | 0として含む |
| TRUE | 無視 | 1として含む |
| FALSE | 無視 | 0として含む |
第1章の例で、休業日に「休」と入力した場合を思い出してください。AVERAGEなら121.4でしたが、同じ表にAVERAGEAを使うと、850 ÷ 10 で85.0になります。文字が0に化けて分母に加わるからです。
「AVERAGEAを使えば文字が混ざっていても大丈夫」と覚えてしまうと、静かに平均が下がった表ができあがります。実務では、まず文字列を取り除くか、AVERAGEIFで条件を付けるほうが安全です。AVERAGEAの出番は、TRUE/FALSEの並んだ判定列を「該当率」として平均したいような、かなり限られた場面だけだと思っています。
7. ひとつの数字に引っ張られている気がしたら
平均値の弱点は、極端な値に弱いことです。9人が年収400万円、1人が5,000万円という集団の平均は860万円になります。誰の実感とも合わない数字です。
こういうときに使うのが、MEDIAN関数とTRIMMEAN関数です。
=MEDIAN(A2:A11) … データを並べたときの真ん中の値
=TRIMMEAN(A2:A11,0.2) … 上下10%ずつを除いてから平均するMEDIANは、データが偶数個のときは中央にある2つの値の平均を返します。外れ値がいくら極端でも、真ん中の順位は動かないので影響を受けません。
TRIMMEANの2つ目の引数は「全体から除外する割合」です。0.2なら全体の20%、つまり上下から10%ずつ除外します。ただし除外される個数は2の倍数に切り捨てられるので、10個のデータに0.2を指定すれば上下1個ずつ、計2個が除かれます。アンケートの極端な回答や、1件だけ混ざった大口取引を外して傾向を見たいときに向いています。
判断の目安として、私はAVERAGEとMEDIANを並べて表示するようにしています。この2つが1割以上離れていたら、データの分布が偏っているサインです。そのまま平均だけを報告せず、「中央値では○○です」と一言添えると、資料の説得力が変わります。
8. #DIV/0!エラーと、四捨五入の落とし穴
範囲が全部空のときに出る #DIV/0!
AVERAGE関数は「合計 ÷ 個数」で計算するため、範囲に数値が1つもないと0で割ることになり、#DIV/0! が表示されます。入力前のテンプレートを配布すると、赤いエラーがずらりと並んで受け取った人を不安にさせるので、次のように包んでおくと親切です。
=IFERROR(AVERAGE(A2:A11),"-")エラーを隠すべき場面と、隠さずに気づけるようにしておくべき場面の判断については、IF関数とIFERRORの使い分けで整理しています。条件分岐の書き方に自信がない方は先にこちらを読んでおくと、この後の応用がぐっと楽になります。
表示を丸めても、中身は丸まっていない
セルの書式設定で小数点以下1桁に見せても、セルの中身は元の小数のままです。画面上は121.4でも、実際には121.42857…が入っています。この平均値をさらに合計したり比率を出したりすると、電卓で検算した数字と数円単位でズレます。
対策は、どの段階で丸めるかを決めてしまうことです。報告書の最終数値として四捨五入するなら =ROUND(AVERAGE(A2:A11),1) のように関数で丸め、途中の計算に使う値は丸めないまま持っておく。この使い分けを決めておくだけで、「合計が1円合わない」という不毛な確認作業が減ります。
9. よくある質問
Q. 数式で出した結果のセルも平均できますか？
できます。AVERAGEから見れば、手入力の数値も数式の結果も同じ「数値」です。ただし、数式の結果が ""（空文字）になっている場合は文字列扱いになり、AVERAGEでは無視されます。空白に見えるのに個数が合わない、という現象の原因はたいていこれです。
Q. フィルターで絞り込んだ行だけの平均を出したいのですが。
AVERAGEは非表示の行も計算に含めます。表示されている行だけを平均したいときは =SUBTOTAL(101,A2:A11) を使ってください。101が「非表示行を除いた平均」を意味する番号です。
Q. 単価と数量から、加重平均を出したいときは？
単価の平均を出しても、取引量の違いが反映されません。=SUMPRODUCT(単価範囲,数量範囲)/SUM(数量範囲) で加重平均が求められます。仕入単価や為替の平均を出すときは、こちらのほうが実態に近い数字になります。
Q. パーセントの列をそのまま平均してもいいですか？
母数が同じならかまいませんが、店舗ごとの達成率のように母数が違う場合は要注意です。小さい店舗の高い達成率と、大きい店舗の低い達成率が同じ重みで扱われてしまいます。この場合も、実数どうしを合計してから割り算するのが正確です。
まとめ
AVERAGE関数そのものは、覚えることの少ない関数です。つまずくのはいつも、関数ではなく表の側でした。
- 空白と0は、Excelにとってまったく別のもの
- 平均が高いと感じたら、COUNTで分母を数えて検算する
- 0を除きたいなら
=AVERAGEIF(範囲,"<>0") - AVERAGEIFとAVERAGEIFSは引数の順番が逆
- AVERAGEAは文字列を0として数える
- 外れ値が気になるならMEDIANを併記する
この6つを押さえておけば、「この平均、合ってますか？」と聞かれても、根拠を持って答えられるようになります。
別々の表から数値を集めてきて平均する場面では、値の取り出し方のほうが本題になります。その段階で困っている方は、INDEX関数とMATCH関数の使い方もあわせてどうぞ。
なお、「特定の期間・特定の分類に絞ったうえで、さらに外れ値と0を除いた平均を出す」といった、条件がいくつも重なるケースになると、記事1本では収まらない組み立ての手順が必要になります。もう一歩踏み込みたい方向けに、複数条件と除外条件が同時にからむ平均の組み立て方をnoteでまとめました。ご興味があればのぞいてみてください。
コメント