Average関数 - 平均値と外れ値
何かの「平均値」を計算したり、または平均値を時系列に並べてみる際に、なんとなく数字が大きすぎるような気がしたり、期によって数字が異様にばらつくことがあります。例えば、新規会員数の月平均、または、年間の購入額の1人当たりの平均、というようなケースです。収益やコストの大きな増減に一喜一憂する前に、まず考えるべきは「なぜそのような大きな変化が発生しているのか」です。もし、それらの変化を説明できるようなトレンドに身に覚えがないのであれば、まず最初に疑うべきは「外れ値」の存在です。外れ値は全体の数字の分布から、上方または下方に異常に大きく外れている値のことを指します。マイナスを取らない数字を扱う場合（顧客数、金額、など）には、上方の外れ値が発生しやすくなります。いくつかの極端に大きな数字のために、集計値（合計や平均値）が上方に引っ張られてしまいます。
例えば、新しい店舗を開いた場合の施設のメンテナンスコストを予測したい場合、「既存の店舗の延床面積当たりのコスト」を新設店舗の床面積で掛けることによって算出するのが自然でしょう。では、「既存の店舗の延べ床面積当たりのコスト」はどのように計算したらよいでしょうか？まず思い浮かぶ方法は、各店舗の床面積当たりのコストの「平均値」でしょう。しかし外れ値がある場合には、その平均値が大きく上方に引き寄せられてしまうかもしれません。特に、平均を計算する際の個体数（店舗数）が少ない場合には、その影響は計り知れません。つまり、コストを過大に予測してしまう危険があります。
平均値を直接計算するAverage関数は非常に便利なのですが、外れ値のような性質の数字を全く考慮しておらず、集計があまりに無機的過ぎるように思える時があります。
そこで、平均値が重要な役割を果たすようなケースでは、Average関数を使う前に、まず外れ値の有無を確かめてみましょう。外れ値を確認する簡単な方法は、グラフ機能の中の「箱ひげ図」(Boxplot)を用いてデータを視覚化することです。
エクセルの箱ひげ図での外れ値の判別の方法ですが、第3四分位＋1.5 x (第3四分位 - 第1四分位) より大きい数が外れ値となっています（上側のみ）。
外れ値がないようであれば、そのまま平均値を計算してもまず問題ないでしょう。また、外れ値の定義にかかる値があるとしても、ヒストグラムの分布が滑らかであれば、あえて外す必要はありません。
しかし、分布が滑らかではなく、突如大きな数字が表れているような場合には、それは外れ値と見てよいでしょう。では外れ値がある場合にはどのように処理したらよいでしょうか？シンプルな方法は、そのような外れ値を対象から外した後に平均を集計することです。箱ひげ図を見て、ある値以上の数字は外れ値であると確認できた場合には、境界線より小さい値のみを平均を計算する、つまりAverageif(s)関数ですぐに計算することができます。
もしくは、平均値ではなくて、外れ値の影響を受けにくい「中央値」(Median関数)を使用するという方法も適切かもしれません。データの上下〇〇％を除外した上で平均を計算するTrimmean関数も可能でしょう。フィギアスケートの採点で最高点と最低点がカットされているのと同じような計算です。または、「期待値」を計算したいのであれば、集計対象のデータが確立分布を反映していなければなりません。場合によっては、一番出現率の高そうな「最頻値」の方が目的にあっているかもしれません。どの方法が適切なのか決定するために大切なことは、そもそものビジネス上での目的は何であるのかを見失わない、また実際に箱ひげ図やヒストグラムなどのデータを自らの目で見て判断する、ということです。
また、データが十分あるのであれば、目的となる対象と性質の近いデータをのみを使った集計をするべきです。例えば、新規店舗のコスト予測のために床面積当たりのコストの平均を知りたい場合、予定されている新規店舗の大きさが似ており、同じ地域にある既存の店舗を集計対象をします。特に光熱費などは、店舗の規模によっては固定費と変動費の割合が大きく異なるかもしれません。また料金も地元の電力会社によって異なります。
仮に箱ひげ図と同様の定義を採用するとして、外れ値を除いた平均値を計算してみましょう。Averageifs関数は、Sumifs関数と非常に似ている関数ですので、全く難しいものではありません。問題は、外れ値の境界線の値を求める方法です。前述したとおり「第3四分位から箱の高さの1.5倍を乗せた高さ」が外れ値の境界線となります。単純に考えれば、境界線の値は以下のように計算できます。上記の
=QUARTILE.EXC(集計範囲, 3) + 1.5*(QUARTILE.EXC(集計範囲, 3) - QUARTILE.EXC(集計範囲, 1))
上記の計算で返された値を境界線とし、Averageifs関数で外れ値を除いた平均値を計算できます。
充分にデータがあるのであれば、全体の中から特定の条件に合ったものだけ（規模や地域など）で境界線を求めましょう。四分位の計算には、条件を指定できるような単独の関数はありませんので、配列関数を使って集計範囲を条件で絞ります（Ctrl+Shft+Enter）。あとは、この値を参照してAverageifs関数を使い、平均値を求めます。
外れ値は除外する、という考えは間違っていないと思いますが、逆に外れ値を探して活用することも可能かもしれません。データの分析というと何か難しいイメージを持つ人もいるかもしれませんが、何か他とは結果が大きく異なるケースを探す、ということも新しい洞察に繋がるかもしれません。ある数字が異常に高いもしくは低いような場合は、そこに探るべき成功事例または修正が必要な課題が潜んでいるのかもしれません。
