偏差値を計算する関数
偏差値って学生の頃とかよく聞きましたよね。偏差値○○の学校とか。
今回のテスト結果、９５点だった！良かった！！と手放しで喜んではいけないのが偏差値です。
クラス平均が９７点だったらどうですか？平均以下なので９５点ではむしろ悪い方です。そのテストの難易度によって点数の意味は変わってきます。平均も加味して自分がどのくらいの位置に居るのか、を分かるようにしたのが偏差値であり、平均であれば５０。偏差値が高いほど平均よりも高いという事です。
- 偏差値60以上（あるいは40以下）は、全体の15.866％
- 偏差値70以上（あるいは30以下）は、全体の2.275％
- 偏差値80以上（あるいは20以下）は、全体の0.13499％
- 偏差値90以上（あるいは10以下）は、全体の0.00315％
- 偏差値100以上（あるいは0以下）は、全体の0.00002％
となっています。
１０００万人が受けたテストがあった場合、偏差値１００以上の人は２人だけ居る事になります。
と、偏差値の話しではなく、それの求め方ですね。エクセルで計算式をどうすればいいのか。
STDEV関数
まずは偏差値の計算式を知る必要があります。
偏差値（Wikipedia）
学力偏差値（Wikipedia）
A君のテスト結果偏差値を計算式に書くとこうなります。
（A君の点数 － 平均点） ÷ 標準偏差 × 10 ＋ 50
ここで気になるのが標準偏差ですね。簡単に言うと点数のバラつき、偏りを数値化したものです。例えばクラスの全員が100点を取っていればバラつきが無いので標準偏差は０になります。
標準偏差（ひょうじゅんへんさ、英語: Standard Deviation）は、分散の正の平方根である。統計値や確率変数の散らばり具合（ばらつき）を表す数値のひとつであり、σ や s で表す。例えば、ある試験でクラス全員が同じ点数であった場合（すなわち全員が平均値であった場合）、データにはばらつきがないので、標準偏差と分散は0になる。
という事である。ではどうやって求めればいいか？だが、これはエクセルの関数で存在しています。それがSTDEV関数です。STDEV関数はこの標準偏差を返してくれます。
引数はSTDEV（ 数値1,数値2, …）というように数値を入れるだけなのでSUMのように B2:B11 という範囲指定でいけます。
サンプル
以下の様なテスト結果があったとします。
ここに先ほどの計算式を当てはめてA君の偏差値を計算してみましょう。
（A君の点数 － 平均点） ÷ 標準偏差 × 10 ＋ 50
つまり
（ B2 – AVERAGE(B2:B11) ) ÷ STDEV(B2:B11) × 10 ＋ 50
になりますね。平均点はAVERAGE関数、標準偏差はSTDEV関数を使います。Excelのセルには以下の様な数式になります。
=(B2-AVERAGE($B$2:$B$11))/STDEV($B$2:$B$11)*10+50
するとこうなりました。
A君の偏差値は61.5です。J君が78点で偏差値50.5なのでいわゆる平均点あたりを取ったという事になります。
テストの点数だけを返されるよりも偏差値も一緒に返してくれるほうが、学生としては自分がどのくらいに居るのか解るので結構助かりますね。
なぜ一発で偏差値が出る数式が無いのか
それは国や求める偏差値によって平均点（日本の学力偏差値では50）が違ったり、標準偏差に掛ける数字（10）が違ったりする為、偏差値の計算式が違ってくる為です。どの偏差値にも対応出来るよう標準偏差だけをExcelはカバーしています。
日本では、平均点を50、標準偏差を10に対応させた偏差値が学力偏差値として広く用いられているが、SATやGRE（北米の大学や大学院へ進学する際に必要な共通試験）では、平均点を500に、標準偏差を100に対応させた値[1]を得点としていたり、SATS（英国の初等教育における学力試験）では、平均点を100に、標準偏差を15に対応させた値[2]が用いられたりしている。
すなわち、偏差値の計算式
偏差値 = (得点 − 平均点) × A / 標準偏差 + B
において、日本ではA = 10, B = 50であり、SATやGREではA = 100, B = 500であり、SATSではA = 15, B = 100である。
引用：Wikipedia学力偏差値より
