Excelで中央値と最頻値を求める方法！MEDIANとMODEの使い方とAVERAGEとの違いも！

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

代表値をひとつ出すだけなら AVERAGE（平均） で足りますが、
極端な値があると平均はそこに引っぱられてしまいます。
そこで今回は
・中央値（=MEDIAN）
・最頻値（=MODE.SNGL）
・平均との違い（=AVERAGE）
をゴールにします。
1つのサンプル表で、3つの代表値を出して比べられるようになります。

上の画像が完成形です。中央値・最頻値・平均・外れ値を除いた平均が1つの表にそろい、3つの代表値をすぐに見比べられます。

なお、データが偶数のときの中央値や、最頻値が2つあるときの考え方（MODE.MULT）、
うまくいかないときの対処法（トラブルシュート）、VBAで一括計算する方法もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. 元データを用意する

まずは代表値を出したいテスト結果の表を用意します。
1行目が見出しで、A列が氏名、B列が点数です。
データは11件あります。
点数はわざと並んでいない状態にしています。
中央値も最頻値も、並べ替えをしなくてもそのまま求められるのがポイントです。
このあと集計用のセルを作っていくので、表の下に少し余白を空けておきましょう。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. 中央値（MEDIAN）を求める

中央値は、データを小さい順に並べたときの真ん中の値です。
結果を入れたいセルに、次の数式を入力します。
ここでは集計結果の表にある C15セル に入れます。

=MEDIAN(B2:B12)

引数は点数の範囲 B2:B12 を指定するだけです。
データは11件なので、小さい順に並べた6番目の値 78 が返ります。
元の表が並んでいなくても、MEDIANが中で並べ替えて真ん中を取ってくれます。
並べ替えの手間がいらないのでカンタンですね。
入力中は引数のヒントが表示されるので、迷わず進められます。

STEP3. 最頻値（MODE.SNGL）を求める

最頻値は、いちばん多く出てくる値です。
集計結果の C16セル に、次の数式を入力します。

=MODE.SNGL(B2:B12)

今回は 85 が3回出てくるので、最頻値は 85 になります。
同じ値が1つもないときは #N/A になる点だけ覚えておきましょう。
なお、古いバージョンとの互換のために用意された =MODE でも同じ結果になります。
これから作るなら MODE.SNGL を使うのがおすすめです。

STEP4. 平均（AVERAGE）も並べて比べる

次に、いちばんなじみのある平均を出して、中央値と比べてみましょう。
集計結果の C17セル に、次の数式を入力します。

=AVERAGE(B2:B12)

全体の平均は 72.5 になりました。
中央値が 78 なので、平均のほうが5.5低いですね。
同じ表から出した値なのに差が出るのはなぜか、次のステップで確認します。

STEP5. 外れ値があるときの平均と中央値の違い

表には 12点 という極端に低いデータが1件だけ入っています。
この1件があると、平均はどうなるでしょうか。
外れ値を除いた平均も出して、3つの値を比べてみましょう。
集計結果の C18セル に、次の数式を入力します。

=AVERAGEIF(B2:B12, ">=50")

50点以上だけを対象にした平均は 78.6 になりました。

平均・中央値・外れ値を除いた平均の比較

3つの値を並べると、外れ値の影響がはっきり見えます。

中央値の 78 は、12点があってもほとんど動きません。
一方で平均は 72.5 まで下がってしまいました。
このため、外れ値がありそうなデータでは中央値のほうが実態をつかみやすいのです。

STEP6. データが偶数のときの中央値

データの個数が偶数のときは、真ん中が2つになります。
その場合は 真ん中2つの平均 が中央値になります。
表の下にある例を見てください。
60・70・80・90 の4件なら、真ん中は 70と80 です。

=MEDIAN(A21:D21)   → 75

(70 + 80) ÷ 2 = 75 が中央値になります。
個数が奇数なら真ん中1つ、偶数なら真ん中2つの平均と覚えておきましょう。
中央値なのに整数にならないこともある、という点だけ注意してくださいね。

STEP7. 最頻値が2つあるとき（MODE.SNGLとMODE.MULT）

同じ回数の値が2つあると、最頻値は1つに決まりません。
表の下の例（6件）を見てください。
60・70・70・85・85・90 は、70と85がどちらも2回ずつ出ています。

=MODE.SNGL(A27:F27)   → 70

MODE.SNGLは、そのうち最初に見つかった1つ（70）だけを返します。
両方とも知りたいときは MODE.MULT関数 を使います。
ただしMODE.MULTは結果が複数になるため、複数セルを選んで配列数式（Ctrl + Shift + Enter）として入力します。
70 と 85 の2つのセルを選んで入力すると、両方の値が返ります。
ふだんはMODE.SNGLで足りることがほとんどなので、まずはこちらを覚えておけば十分です。

STEP8. VBAで4つの代表値をまとめて計算する

同じ集計を毎回くり返すなら、VBAで一括計算するのがラクです。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。

Sub StatSample()
    Dim ws As Worksheet
    Dim r As Range
    Set ws = ActiveSheet
    Set r = ws.Range("B2:B12")
    ws.Range("C15").Value = WorksheetFunction.Median(r)
    ws.Range("C16").Value = WorksheetFunction.Mode_Sngl(r)
    ws.Range("C17").Value = WorksheetFunction.Average(r)
    ws.Range("C18").Value = WorksheetFunction.AverageIf(r, ">=50", r)
End Sub

実行すると、集計結果の4つのセルに値が一瞬で入ります。
ボタンに登録しておけば、クリック1つで集計をやり直せます。

タカヒロ

VBAでは、セルに入れる関数を WorksheetFunction.関数名 の形で呼び出します。
MODE.SNGL のように「.」が付く関数は、VBAでは Mode_Sngl と「_（アンダースコア）」に置きかえて書くのがポイントです。
ここさえ押さえればカンタンですね。

3つの代表値の使い分け

場面ごとに使う関数を表にまとめました。
迷ったときは、この表に戻って選んでみてください。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったとおりの値にならないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

関数の説明

今回使う3つの関数の概要です。
書式と戻り値を押さえておけば、迷わず使い分けられます。

MEDIAN関数（メディアン）

=MEDIAN(B2:B12)

MODE.SNGL関数（モード・シングル）

=MODE.SNGL(B2:B12)

MODE.MULT関数（モード・マルチ）

=MODE.MULT(A27:F27)

AVERAGE関数（アベレージ）

=AVERAGE(B2:B12)

完成

集計結果の表に、中央値・最頻値・平均・外れ値を除いた平均がそろいました。
思ったとおりに出ましたか？
中央値が 78、最頻値が 85、平均が 72.5、外れ値を除いた平均が 78.6 になりました。
平均が中央値より低いのは、12点の1件に引っぱられているためです。
外れ値があるときは中央値もあわせて見ると、データの実態をつかみやすくなります。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル表で、ぜひ一度試してみてください。
平均・中央値・最頻値をセットで見られるようになると、データの読み取りがぐっとラクになりますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。