エクセル 偏差値をつける

スポンサーリンク

偏差値

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

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

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

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

偏差値 =(得点 − 平均点)×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 までの判定を行っています。
  • さらに、条件付き書式を使って、評価 には赤色でハイライトしています。

数式の一例、生徒 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)

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

コメント