偏差値

偏差値という言葉は、受験生であった人にとっては、あまりいい響きではないかもしれません。

偏差値はいろいろ言われますが、そのもの自体は平均からの乖離を表す、単なる便利な指標でしかありません。

ここでは、学校などでのテストを想定してその偏差値をどうつけるか・・・について解説します。

偏差値は、以下の式で計算されます。

偏差値 ＝(得点 − 平均点)×10÷標準偏差 ＋ 50

この式からわかるように得点が平均点と同じときは (得点 − 平均点) = 0　となりますから、偏差値は 50 になります。

• 理論的には平均点が 5 点とかいうテストで 100点をとったりすると100を超えるような偏差値も考えられます。
• あるいは、平均点 90点のテストで 0 点を取ったりしたような場合、偏差値が負の値になる（泣）こともあり得ます。

偏差値計算には平均点と標準偏差が必要ですので、それぞれについて簡単に解説します。

平均

書式

=AVERAGE(値1,値2,値3,…)=AVERAGE(範囲)

これは簡単ですね。範囲には絶対参照か相対参照かをよく吟味してください。

標準偏差

標準偏差とはデータのばらつき具合を示す指標です。
たとえば、クラスのテスト平均が80点で全員が80点であればばらつきは 0 、つまり、標準偏差は 0 となります。

一般的な標準偏差の計算式は、次の式で表されます。受験者数 $n$ 人とします。

$標準偏差 =\sqrt{ \dfrac{(得点1-平均点)^2+(得点2-平均点)^2+(得点3-平均点)^2\cdots}{n}}$

エクセルに用意されている関数には

• 全データを対象とする場合は、STDEV.P（STDEVP）関数
• 標本を対象とする場合はSTDEV.S（STDEV）関数

を使用します。学校では全データを使用することがほとんどでしょうから、STDEV.P を使うことが多いですね。

書式

=STDEV.P(値1, 値2,値3,…)=STDEV.P(範囲)

 

標準偏差は、例えば標準偏差が 10 の場合、多く（7割程度）の生徒が平均点から ±10 点の範囲に含まれる・・というイメージになります。

では具体的な事例を用いて表を作成してみます。

偏差値

図は架空のテスト結果です。

この表の下の行に（場所は任意）に平均と標準偏差を計算するセルを作ります。

平均を示す C12 セルへ　=AVERAGE(C3:C11)　と入力します。
標準偏差を示す C13 セルは　=STDEV.P(C3:C11)　とし、それぞれを I 列まで横へコピーします。

これで各教科の平均点と標準偏差が計算されました。

さて、偏差値を計算します。
今回は、横へ同じような表を作って偏差値を表示させましょう。

同じシートの、ちょっと横のNセルから同じような表を作りました。

ここで、O3 セルに次のように入力します。

偏差値 ＝(得点 − 平均点)×10÷標準偏差 ＋ 50

            =(C3-C$\$$12)*10/C$\$$13+50

ここで、C3 セルは生徒の得点データです。
C12 セルは得点の平均を。C13 セルは標準偏差を示しています。

C3-C$\$$12　として絶対参照 $\$$ を番地 12 の前につけておきます。
同様に　C3-C$\$$13　の13番地の前にも $\$$ を付けましょう。

このようにしておくと、O3 セルを、全体に一気にコピーしても、C12 と C13 セルの行番地は変化することがありません。
（注：列番地の C は絶対参照してはいけません。正しく番地が変換されなくなります）

もっと実戦的に

ではもっと実戦的に一覧表を作成してみましょう。（さきほどのデータとは異なります）

次の表では、VLOOKUP関数　SUM関数　AVERAGE関数　STDEV.P関数　RANK関数　を組み合わせています。

• 各テストの合計点、平均点を計算しています。
• 順位は RANK関数 を使って、合計点でランク付けをしています。
• 偏差値は各人の平均点から計算しています。
• VLOOKUP関数を使い、別表から A ～ E までの判定を行っています。
• さらに、条件付き書式を使って、評価 E には赤色でハイライトしています。

数式の一例、生徒 A について

• F3 セル　合計　　=SUM(C3:E3)
• G3 セル　順位　　=RANK(F3,F$\$$3:F$\$$13)
• H3 セル　平均　　=AVERAGE(C3:E3)
•  I 3 セル　偏差値　=(H3-$\$$H$\$$14)*10/$\$$H$\$$15+50
• J3 セル　  評価　　=VLOOKUP(I3,$\$$L$\$$3:$\$$M$\$$8,2,TRUE)

ほとんどの教科でこの方式が使えるはずです。
参考にしてください。