Excelで標準偏差を求める: STDEV.SとSTDEV.Pの違い

=STDEV.S(B2:B9)はB2:B9の値を標本として扱った標準偏差を返し、=STDEV.P(B2:B9)はそれを母集団全体として扱います。標準偏差は、値がふつう平均からどれだけ離れているかを表します。小さければ、値どうしが近くに集まっています。

数式リセット

E2fx

=STDEV.S(B2:B9)

	A	B	C	D	E
1	Student	Score		Measure	Result
2	Ana	72		STDEV.S	12.82853961
3	Ben	84		STDEV.P	12
4	Cleo	84		Average	90
5	Dan	84
6	Eve	90
7	Finn	90
8	Gia	102
9	Hal	114

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

点数の平均は90です。STDEV.Pはちょうど12を、STDEV.Sは約12.83を返します。Halの点数を90に変えると、どちらも大きく下がります。ほかから大きく離れた1つの値が、標準偏差を大きく動かすのです。

STDEV.SとSTDEV.P: どちらを使うか

2つは1つの手順だけが違います。STDEV.Pは差の2乗の合計を値の個数nで割ります。STDEV.Sはnから1を引いた数で割るので、結果が少し大きくなります。理由は次のとおりです。標本のばらつきは標本自身の平均を中心に測られますが、その平均は本当の平均よりも標本の値に近いので、nで割るとグループ全体のばらつきを小さく見積もってしまいます。

• STDEV.P(母集団): 範囲に、説明したい値がすべて入っている。このクラスについて知りたいときの、クラスの生徒8人全員の点数です。
• STDEV.S(標本): 範囲がもっと大きなものの一部である。生徒600人の学校から選んだ8人で、学校全体のばらつきを推定するときです。

迷ったらSTDEV.Sを使ってください。スプレッドシートのデータのほとんどは標本で、統計の手法(t検定、信頼区間)も標本版を前提にしています。値が数百あれば2つの結果はほぼ同じで、値が8つなら差は約7%です。

古い関数STDEVとSTDEVPはSTDEV.SとSTDEV.Pと同じ結果を返し、どのバージョンのExcelでも今も使えます。STDEVAとSTDEVPAは文字列を0、TRUEを1として数えますが、それが望ましいことはめったにありません。

Excelの計算方法を1ステップずつ

このシートは、STDEV.Sが1回の呼び出しで行うことを手で行っています。各値から平均を引き、差を2乗し、それを合計し、nから1を引いた数で割り、平方根を取ります。

数式リセット

F5fx

=SQRT(F4)

	A	B	C	D	E	F
1	Value		Difference	Squared		Result
2	4		-2	4	Sum of squares	34
3	8		2	4	n	6
4	6		0	0	Variance (sample)	6.8
5	5		-1	1	Std dev (sample)	2.607680962
6	3		-3	9	STDEV.S	2.607680962
7	10		4	16

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

2乗の合計は34、標本の分散は6.8で、その平方根(約2.61)がF6のSTDEV.Sと一致します。F4を=F2/F3に変えると母集団の分散になり、その平方根がSTDEV.Pの返す値です。

分散: VAR.SとVAR.P

分散は平方根を取る前の標準偏差です。上のデータでは=VAR.S(A2:A7)は6.8を返し、=VAR.P(A2:A7)はnから1を引いた数ではなくnで割ります。分散の単位は2乗(点の2乗、ドルの2乗)なので、報告には標準偏差のほうが読みやすくなります。VARとVARPは古い名前です。

平均±1標準偏差

ばらつきを報告するよくある方法が「平均 ± 標準偏差」で、たとえば90 ± 12.8です。その範囲の両端は簡単な数式で求まり、条件付き書式のルールで範囲外の値に印を付けられます。

数式リセット

E3fx

=STDEV.S(B2:B9)

	A	B	C	D	E
1	Student	Score		Measure	Value
2	Ana	72		Mean	90.0
3	Ben	84		SD	12.8
4	Cleo	84		Low	77.2
5	Dan	84		High	102.8
6	Eve	90
7	Finn	90
8	Gia	102
9	Hal	114

B2:B9 を強調する条件

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

ルールはAnaとHalを強調します。約77.2から102.8の範囲から外れた2つの点数です。正規分布のデータでは、値の約3分の2が平均から1標準偏差以内に、約95%が2標準偏差以内に入ります。セルの文字列を「90.0 ± 12.8」の形にするには=TEXT(E2,"0.0")&" ± "&TEXT(E3,"0.0")を使います。

条件付きの標準偏差

STDEVIF関数はありません。STDEV.Sの中にIFを入れます。IFは地域が一致する行では点数を、それ以外ではFALSEを返し、STDEV.SはFALSEの値を飛ばします。

数式リセット

E2fx

=STDEV.S(IF(A2:A9=D2,B2:B9))

	A	B	C	D	E
1	Region	Sales		Region	STDEV.S
2	North	120		North	25.61737691
3	South	95		South	3.872983346
4	North	150
5	South	101
6	North	90
7	South	98
8	North	135
9	South	104

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Northの売上はSouthよりはるかにばらついています。Excel 365と2021では、この数式は入力したとおりに動きます。Excel 2019以前では、Ctrl+Shift+Enter(MacではCmd+Shift+Enter)で確定しないと、間違った結果か#VALUE!が返ります。Excel 365では=STDEV.S(FILTER(B2:B9,A2:A9=D2))とも書けます。

やってみよう: 配達日数のばらつき

数式リセット

E2fx

	A	B	C	D	E
1	Order	Days			Std dev
2	A1	3
3	A2	5
4	A3	4
5	A4	9
6	A5	3
7	A6	4
8	A7	6

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: B2:B8の注文は、すべての注文の中の標本です。E2に、その標準偏差を計算してください。

答え合わせ答えを見る

ヒント: 標本なら、.Sで終わる関数です。

よくある間違い: 範囲に合計の行が入っている

B2:B10のような範囲が列の最後の合計や平均まで含んでいると、その集計値がもう1つのデータとして扱われ、標準偏差がずっと大きくなります。データの行だけを選ぶか、このページのシートのように集計は別の列に置いてください。範囲内の空のセルと文字列は無視されますが、0は値として数えられます。欠けている点数を0と入力すると、本当の0と同じようにばらつきが広がります。何個の値が使われたかを確かめるには、結果の隣に=COUNT(B2:B9)を置きます。