エクセルでデータのばらつきを求めるときは、標準偏差の STDEV.P 関数を使うのが基本です。ほかにも、範囲（最大−最小）・分散・四分位範囲・変動係数といった「ものさし」があり、目的で使い分けます。
この記事では、平均がどちらも 70 点で、ばらつきだけがちがう A 組と B 組の点数を、実際の Excel で 7 つのものさしで比べました。外れ値があるときに結果がどう変わるかも確かめています。
ばらつきを求める式 一覧
| ものさし | 式（A 組は B4:B13） | A 組 | B 組 |
|---|---|---|---|
| 平均 | =AVERAGE(B4:B13) | 70.0 | 70.0 |
| 範囲（最大−最小） | =MAX(B4:B13)-MIN(B4:B13) | 6 | 40 |
| 分散 | =VAR.P(B4:B13) | 2.8 | 150.0 |
| 標準偏差 | =STDEV.P(B4:B13) | 1.7 | 12.2 |
| 四分位範囲 | =QUARTILE.INC(B4:B13,3)-QUARTILE.INC(B4:B13,1) | 1.5 | 17.5 |
| 平均偏差 | =AVEDEV(B4:B13) | 1.2 | 10.0 |
| 変動係数 | =STDEV.P(B4:B13)/AVERAGE(B4:B13) | 2.4% | 17.5% |
平均が同じでも、B 組は標準偏差が 12.2 点で、A 組の 1.7 点よりずっとばらついています。どのものさしでも、B 組のほうが大きくなりました。
1. いちばん簡単なのは「範囲」（最大−最小）
一番高い点数から一番低い点数を引きます。すぐ計算できますが、1 人だけ極端な点数があると、その人だけで大きく変わります。
=MAX(B4:B13)-MIN(B4:B13)
2. 基本は「標準偏差」（STDEV.P）
標準偏差は、平均から平均してどれくらい離れているかを表す数です。単位は元のデータと同じ「点」なので、「平均 70 点、標準偏差 12.2 点」のように言えます。分散は標準偏差の 2 乗で、単位が「点の 2 乗」になるため、ふだんは標準偏差のほうが読みやすいです。
=STDEV.P(B4:B13)
クラス全員のように、データが全部そろっているときは STDEV.P、一部を取り出したデータ（標本）から全体を考えるときは STDEV.S を使います。違いはSTDEV.S 関数の使い方で説明しています。
3. 外れ値に強いのは「四分位範囲」
点数を低い順に並べて、4 分の 3 の位置の点数から 4 分の 1 の位置の点数を引きます。真ん中の半分の人がどのくらいの幅にいるかを表すので、極端な点数があっても変わりにくいのが特長です。
=QUARTILE.INC(B4:B13,3)-QUARTILE.INC(B4:B13,1)
4. 平均がちがうデータを比べるなら「変動係数」
標準偏差を平均で割ったものです。「身長と体重」や「価格が大きくちがう商品の売上」のように、平均や単位がちがうデータのばらつきを比べるときに使います。パーセントの表示にすると読みやすくなります。
=STDEV.P(B4:B13)/AVERAGE(B4:B13)
【実機で検証】外れ値・文字・1 件だけのとき
A 組の 10 人目だけを 0 点にした列と、10 人目を文字の「欠席」にした列を用意して比べました。
| ためしたこと | A 組 | 1 人だけ 0 点 | 欠席あり |
|---|---|---|---|
| 標準偏差 STDEV.P | 1.7 | 21.1 | 1.8 |
| 四分位範囲 | 1.5 | 2.5 | 2.0 |
| 標準偏差 STDEV.S | 1.8 | 22.2 | 1.9 |
| 数えたデータの個数 | 10 | 10 | 9 |
1 人だけ 0 点にすると、標準偏差は 1.7 から 21.1 に大きく増えました。四分位範囲は 1.5 から 2.5 で、それほど変わりません。外れ値があるかもしれないデータは、四分位範囲も一緒に見ると安心です。
「欠席」のような文字は計算から外れ、9 人分で計算されました。0 点として数えたいときは、文字を 0 として扱うSTDEVPA 関数を使います。データが 1 件だけのときは、STDEV.P は 0、STDEV.S は #DIV/0! になりました。
よくある質問
標準偏差はいくつなら「ばらつきが大きい」？
決まった基準はありません。同じ単位・同じくらいの平均のデータどうしで比べます。平均がちがうときは、変動係数で比べます。
分散と標準偏差は、どちらを使えばいい？
ふだんは標準偏差を使います。分散は単位が「点の 2 乗」になり、B 組なら 150.0 と大きな数になるためです。標準偏差は分散の平方根で、B 組は 12.2 点でした。
偏差値はどう求める？
平均と標準偏差を使って、=(点数-平均)/標準偏差*10+50 で求めます。くわしくは偏差値を求める方法をご覧ください。
コメント