Excelで標準偏差と偏差値を求める方法！STDEV.SとSTDEV.Pの使い分けも！

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

ですよね。

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

についてまとめます！

完成イメージ

テストの点数から、平均・分散・標準偏差を計算し、一人ひとりの偏差値まで出せるようにします。
今回は
・点数の表に「偏差値」の列を追加し、下の集計エリアに標準偏差や分散をまとめる
をゴールにします。
偏差値は平均が50になるので、集団の中での自分の位置がひと目で分かります。

上の画像が完成形です。点数の右に「偏差値」の列が並び、下の集計エリアに平均・分散・標準偏差がまとまっています。

なお、STDEV.SとSTDEV.Pの使い分けや、計算がうまくいかないときの対処法（トラブルシュート）、VBAで一括計算する方法もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. テスト結果の表を用意する

まずは標準偏差を求めたいテスト結果の表を用意します。
1行目が見出しで、A列が氏名、B列がクラス、C列が点数です。
データは10件あります。
この点数をもとに、平均や標準偏差、偏差値を計算していきましょう。

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

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

STEP2. まずは平均点を求める（AVERAGE）

標準偏差や偏差値を出すには、まず平均点が必要です。
表の下のA13セルに「集計結果（標準偏差と偏差値）」と入力します。
続いてA14セルに「平均点」、C14セルに =AVERAGE(C2:C11) と入力します。
点数が10件なので、合計を10で割った平均が出ます。
実際の入力画面がこちらです。

STEP3. 分散を求める（VAR.S）

次に、ばらつきの大きさを表す「分散」を求めます。
A15セルに「分散（標本）」、C15セルに =VAR.S(C2:C11) と入力します。
分散は「各データと平均との差を2乗したものの平均」で、標準偏差の2乗にあたります。
標本の分散は件数から1を引いた数で割るので、Excelでは VAR.S を使います。
実際の入力画面がこちらです。

STEP4. 標準偏差を求める（STDEV.S）

分散の平方根（ルート）をとったものが「標準偏差」です。
A16セルに「標準偏差（標本）」、C16セルに =STDEV.S(C2:C11) と入力します。
標準偏差は、点数のばらつきがどれくらい大きいかを表す値です。
値が大きいほど、点数の散らばりが大きいことを意味します。
ここでは標本の標準偏差を求める STDEV.S を使っています。
実際の入力画面がこちらです。

STEP5. 偏差値の列を作る

平均と標準偏差が出たら、いよいよ偏差値です。
偏差値は次の式で求められます。
偏差値 ＝（点数 − 平均）÷ 標準偏差 × 10 ＋ 50
D1セルに「偏差値」と入力し、D2セルに =(C2-$C$14)/$C$16*10+50 と入力します。
平均と標準偏差のセルは、下にコピーしてもずれないように $C$14・$C$16 と絶対参照にします。
あとはD11セルまでコピーすれば、全員の偏差値が一気に求まります。
実際の入力画面がこちらです。

STEP6. 標準化得点を求める（STANDARDIZE）

偏差値のもとになっているのが「標準化得点」です。
標準化得点は、平均から見て標準偏差何個分はなれているかを表します。
A17セルに「標準化得点（佐藤）」、C17セルに =STANDARDIZE(C2,C14,C16) と入力します。
82点の佐藤さんは平均より少し上なので、0.4056 とプラスの値になりました。
この標準化得点を10倍して50を足すと、偏差値になります。
実際の入力画面がこちらです。

STEP7. 母集団の標準偏差も求める（STDEV.P）

ここまでは「標本」の標準偏差でした。
手元のデータが調べたい対象のすべてである場合は、「母集団」の標準偏差を使います。
A18セルに「分散（母集団）」、C18セルに =VAR.P(C2:C11) と入力します。
A19セルに「標準偏差（母集団）」、C19セルに =STDEV.P(C2:C11) と入力します。
標本は件数から1を引いた数で、母集団は件数そのもので割るため、母集団のほうが少し小さな値になります。
実際の入力画面がこちらです。

標準偏差まわりの関数の比較

求めたいものによって使う関数が変わります。
場面に合わせて選べるように、表にまとめました。

VBAで偏差値をまとめて計算する

決まったテストで毎回偏差値まで出したいなら、VBAにするとボタン1つで計算できます。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。

Sub 偏差値計算()
    Dim i As Long, n As Long
    Dim total As Double, mean As Double
    Dim ss As Double, sd As Double
    n = 10
    For i = 2 To 11
        total = total + Cells(i, 3).Value
    Next i
    mean = total / n
    For i = 2 To 11
        ss = ss + (Cells(i, 3).Value - mean) ^ 2
    Next i
    sd = Sqr(ss / (n - 1))
    For i = 2 To 11
        Cells(i, 4).Value = (Cells(i, 3).Value - mean) / sd * 10 + 50
    Next i
End Sub

実行すると、D列に全員の偏差値が一気に入ります。
数式を残したくないときにも便利ですね。

タカヒロ

VBAだと数式を入れずに、値として偏差値を出せます。
「Cells(i, 3)」の 3 はC列（点数）、「Cells(i, 4)」の 4 はD列（偏差値）という意味です。
データの件数が変わったら、n と For i = 2 To 11 の数値を合わせてくださいね。

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

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

関数の説明

今回使った関数の概要です。
どれもばらつきを調べるときによく使う関数なので、違いを押さえておきましょう。

STDEV.S関数（エスティーデブ・ドット・エス）

=STDEV.S(C2:C11)

STDEV.P関数

=STDEV.P(C2:C11)

STANDARDIZE関数（スタンダーダイズ）

=STANDARDIZE(C2,C14,C16)

VAR.S関数（バリアンス・ドット・エス）

=VAR.S(C2:C11)

完成

点数の右に偏差値の列が並び、下の集計エリアに平均・分散・標準偏差がまとまりました。
82点の佐藤さんは偏差値54.1、95点の中村さんは64.2、55点の伊藤さんは33.0になりました。
全員の平均点は76.8なので、偏差値50を基準にすると、それぞれの位置がよく分かります。
思ったとおりに動きましたか？

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル表で、ぜひ一度試してみてください。
手元のデータが一部なら STDEV.S、全部なら STDEV.P と使い分けるのがポイントです。
偏差値まで出せるようになると、テスト結果の分析がぐっとラクになりますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。

ぜひ試してみてください。