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

標準偏差は、データが平均値のまわりにどれくらい散らばっているか(ばらつき)を表す値です。Excelでは、データが標本(全体の一部)なら =STDEV.S(B2:B7)、調べたい対象をすべて含む母集団なら =STDEV.P(B2:B7) で求めます。迷ったらSTDEV.Sを使います。
結果はデータと同じ単位になります。売上なら円、納期なら日数です。値が大きいほど、データのばらつきが大きいことを示します。
例:平均は同じでもばらつきが違う2店舗
2つの店舗の6週間分の週間売上です。
| A | B | C | |
|---|---|---|---|
| 1 | 週 | 東京店(千円) | 大阪店(千円) |
| 2 | 第1週 | 1,200 | 900 |
| 3 | 第2週 | 1,250 | 1,500 |
| 4 | 第3週 | 1,180 | 1,100 |
| 5 | 第4週 | 1,220 | 1,400 |
| 6 | 第5週 | 1,150 | 1,000 |
| 7 | 第6週 | 1,200 | 1,300 |
平均はどちらも同じです。=AVERAGE(B2:B7) も =AVERAGE(C2:C7) も1,200千円を返します。平均ではわからない違いが、標準偏差で見えてきます。
=STDEV.S(B2:B7)の結果は、東京店で 34.06千円=STDEV.S(C2:C7)の結果は、大阪店で 236.64千円
東京店は毎週ほぼ同じ売上です。大阪店は好調な週と不調な週の差が大きく、在庫やシフトの計画が立てにくい店だとわかります。2列を折れ線グラフにすると違いがひと目でわかります。東京店の線はほぼ平らで、大阪店の線は週ごとに上下します。
STDEV.SとSTDEV.Pの違いと使い分け
2つの関数の計算はほぼ同じで、違いは1点だけです。偏差の2乗の合計を、STDEV.Sは n − 1 で、STDEV.Pは n で割ります(n はデータの個数)。
- STDEV.S(標本標準偏差):手元のデータが大きな集団の一部で、そこから全体を推定したい場合。店の安定性を判断するための6週間分の売上、顧客2,000人のうち50人へのアンケート、生産ロットから抜き取った20個の検査結果などです。
- STDEV.P(母集団の標準偏差):知りたい対象のデータがすべてそろっていて、一般化しない場合。クラス全員(28人)の点数でそのクラスを説明する、小さな会社の全社員の給与を見る、といったケースです。
今回の例で比べると次のとおりです。
| 店舗 | STDEV.S | STDEV.P |
|---|---|---|
| 東京店 | 34.06千円 | 31.09千円 |
| 大阪店 | 236.64千円 | 216.02千円 |
STDEV.Sの結果は常に少し大きくなります。データが6個だと差がはっきり出ますが、数百個あれば2つの結果はほとんど同じになります。
そのほかの標準偏差の関数
関数の一覧には、標準偏差の関数が6つあります。違いは次のとおりです。
| 関数 | 対象 | ポイント |
|---|---|---|
| STDEV.S | 標本 | 基本はこれを使います。 |
| STDEV.P | 母集団 | すべてのデータがそろっているとき。 |
| STDEV | 標本 | 旧バージョンの関数名。STDEV.Sと同じ結果です。古いブックとの互換性のために残されています。 |
| STDEVP | 母集団 | 旧バージョンの関数名。STDEV.Pと同じ結果です。 |
| STDEVA | 標本 | 範囲内の文字列(0として扱う)と論理値(TRUE = 1、FALSE = 0)も計算に含めます。 |
| STDEVPA | 母集団 | STDEVAの母集団版です。 |
STDEV.SとSTDEV.Pは、範囲内の空白セル、文字列、論理値を無視します。STDEVAとSTDEVPAを使うのは、「欠席」などの文字やFALSEを0として数えたい場合に限られます。
結果の読み方
標準偏差の値だけでは、大きいか小さいか判断できません。236千円のばらつきは、売上1,200千円の店では大きく、週の売上が1億円の店ではごくわずかです。比較するときは平均値で割ります。これを変動係数といいます。
=STDEV.S(B2:B7)/AVERAGE(B2:B7)
パーセント表示にすると、東京店は2.8%、大阪店は19.7%です。単位や規模が違うデータどうしも、この比率なら比べられます。
標準偏差が0なら、すべての値が同じという意味です。マイナスになることはありません。
条件付きで標準偏差を求める
データが縦に並んだリスト形式(A列に店舗名、B列に金額、2行目から13行目)の場合、「STDEVIF」のような関数はありません。STDEV.Sの中にIFを入れます。
=STDEV.S(IF(A2:A13="東京店",B2:B13))
IFは「東京店」の行では金額を、それ以外の行ではFALSEを返し、STDEV.SはFALSEを無視します。Microsoft 365とExcel 2021以降では Enter で確定できます。それより前のバージョンでは Ctrl + Shift + Enter で配列数式として確定しないと、正しい結果になりません。
Microsoft 365やExcel 2021以降なら、FILTER関数を使うと読みやすくなります。
=STDEV.S(FILTER(B2:B13,A2:A13="東京店"))
エラーや予想外の結果が出るとき
#DIV/0! になる
STDEV.Sは n − 1 で割るため、数値が2つ以上必要です。数値が1つだけ、または文字列しかない範囲では #DIV/0! になります。Excelが数値として認識している個数は =COUNT(B2:B7) で確認できます。
結果が小さすぎる・大きすぎる
- 文字列として保存された数値:他のシステムから取り込んだ金額(左寄せで、緑の三角が付いている)は、警告なしに無視されます。
=COUNT(B2:B7)が行数より少なければ、エラーのアイコンから「数値に変換する」を選んで変換します。 - 空白のかわりに0が入っている:データのない週に0を入力すると、売上0円の週として計算され、標準偏差が大きくなります。値がわからないセルは空白のままにします。
- 合計行が範囲に含まれている:範囲に「合計」の行が入っていると、その極端な値が計算全体をゆがめます。
値ごとに重みが違う平均を求めたい場合は、Excelで加重平均を求める方法を参照してください。関数の詳細はMicrosoftのSTDEV.S関数のヘルプで確認できます。


