標準偏差と偏差値をExcelで求める

今回は「標準偏差」と「偏差値」をExcelで求める方法を紹介していこう。「偏差値」は高校受験や大学受験などで頻繁に使われている指標なので、聞いたことがある人も多いはずだ。これらの数値も「ばらつき具合」を把握するときに役立つ指標となる。

標準偏差を求める

前回の連載では「分散」を求めることにより「データのばらつき具合」を数値化した。ただし、「分散」の計算式には「（値－平均値）の2乗」が含まれるため、どうしても数値が大きくなりすぎてしまうのが弱点といえる。

そこで、「分散」の平方根を「ばらつき具合を示す指標」として使用する場合もある。こちらは「標準偏差」と呼ばれる指標であり、計算式で示すと以下のようになる。

• 

  標準偏差の計算式

今回も「新人研修のテスト結果」を例に解説を進めていこう。すでに「分散」が求められている場合は、その平方根を関数SQRT()で求めると「標準偏差」を算出することができる。

• 

  標準偏差の算出

• 

  算出された標準偏差

また、Excelには「標準偏差」を求める関数STDEV.P()も用意されている。こちらは、まだ「分散」を求めていない場合であっても利用することが可能だ。カッコ内にデータのセル範囲を指定するだけで、そのデータの「標準偏差」を求めることができる。

• 

  関数STDEV.P()を使った標準偏差の算出

• 

  算出された標準偏差

もちろん、どちらの方法で計算しても結果は同じになる。今回の例では、「東京本社」は15.99、「大阪支社」は11.33という数値が得られた。

• 

  東京と大阪の標準偏差

両者を比較すると、「東京本社」の方が「大阪支社」より「標準偏差」は大きくなる。よって、「東京本社の方がデータのばらつき具合が大きい」という結論になる。

一般的な統計学では、「平均値」±「標準偏差」の範囲に大半のデータ（約68％）が含まれる、と考えることができる。たとえば、「東京本社」と「大阪支社」について「平均値」±「標準偏差」の範囲を計算すると、以下のような結果になる。

■「平均値」±「標準偏差」の範囲
　東京本社・・・・・52.36～84.34点
　大阪支社・・・・・57.07～79.73点

この範囲は「大阪支社」の方が狭い。よって、狭い範囲に多くのデータが集中していると考えられる。なお、こういった考え方については、次回の連載で詳しく解説する予定だ。

各データの偏差値を求める

「平均値」と「標準偏差」を使って、個々のデータの「偏差値」をExcelで求めることも可能である。「偏差値」を求める計算式は、以下のようになる。

• 

  偏差値の計算式

先ほどと同じデータについて、実際に「偏差値」を求めてみよう。この数式は以下の図のように記述できる。なお、ここでは数式をコピーできるように、「平均値」と「標準偏差」のセル参照を絶対参照で記述している。

• 

  偏差値を算出する数式（東京）

この数式をオートフィルでコピーすると、「東京本社」の各データについて「偏差値」を求めることができる。

• 

  オートフィルで数式をコピー

念のため、「大阪支社」の各データについて「偏差値」を求める数式も示しておこう。参照するセルが異なるだけで、数式の記述は基本的に同じである。

• 

  偏差値を算出する数式（大阪）

この数式をオートフィルでコピーすると、「大阪支社」の各データについても「偏差値」を求めることができる。

• 

  各データの偏差値

以上が、受験などでよく耳にする「偏差値」の求め方となる。

全データに対する標準偏差、偏差値を求める

先ほど算出した「偏差値」をよく見ると、少しだけ不可解な点があることに気が付く。それは、同じ点数でも「偏差値」が異なるケースがあることだ。

たとえば、「東京本社」の59点（No.4、No.12）は偏差値44.2となっているが、「大阪」の59点（No.2）は偏差値41.7となっている。

• 

  各データの偏差値

「東京本社」と「大阪支社」で同じ内容のテストを実施したのであれば、これらは不適切な「偏差値」であると考えられる。このような不本意な結果になってしまうのは、それぞれのデータで個別に「平均値」と「標準偏差」を算出していることが原因だ。

すべてのデータを対象に「偏差値」を求めたいのであれば、全データについて「平均値」を求めなければならない。今回の例のように「離れたセル範囲」について「平均値」を求めるときは、それぞれのセル範囲を「,」（カンマ）で区切って関数AVERAGE()を記述する。

• 

  関数AVERAGE()で全データの平均値を求める

• 

  全データの平均値

同様に、「標準偏差」も全データを対象にして算出する必要がある。関数STDEV.P()も複数のセル範囲に対応しているため、それぞれのセル範囲を「,」（カンマ）で区切って記述することが可能だ。これで全データの「標準偏差」を求めることができる。

• 

  関数STDEV.P()で全データの標準偏差を求める

• 

  全データの標準偏差

あとは、これらの指標を使って「偏差値」を算出していくだけ。たとえば「東京本社」の場合、「偏差値」を求める数式は以下のような記述になる。

• 

  偏差値を算出する数式（東京）

念のため、「大阪支社」のデータについても「偏差値」を求める数式を示しておこう。数式から参照する「平均値」と「標準偏差」は、「東京本社」の場合と同じである。

• 

  偏差値を算出する数式（大阪）

これらの数式をオートフィルでコピーすると、全データについて「偏差値」を算出することができる。この場合、同じ点数は同じ「偏差値」で示されるようになる。たとえば、先ほど例にした59点のデータの場合、「東京本社」でも「大阪支社」でも、偏差値が43.2と示されていることを確認できるだろう。

• 

  各データの偏差値

このように、「平均値」と「標準偏差」から各データの「偏差値」を算出することも可能である。ただし、これを有効活用するには「偏差値は何を示す数値なのか？」を正しく理解しておく必要がある。

一般的に偏差値70と聞くと、「凄い！」という印象を持つ人が多いかもしれない。でも、「偏差値70がどれくらい凄いのか？」を正確に説明できる人は少ないだろう。そこで次回は、「偏差値は何を意味する数値なのか？」について詳しく解説していこう。