概要
偏差値は、テストの点数などが「集団の中でどの位置にいるか」を、平均と標準偏差を使って 50 点基準で表した指標です。
一般的な計算式は「偏差値＝(個人の得点−平均点)÷標準偏差×10＋50」で、Excel でも同じ式をそのまま数式として書くことができます。
偏差値の考え方
偏差値の元になっているのは「標準化」です。
まず「値−平均」で、平均からどれだけ上か下かという“ズレ”を出し、それを標準偏差で割ることで「ばらつきの大きさで割った相対的なズレ」に変換します。
この標準化された値に 10 を掛けて 50 を足すことで、「平均が 50、標準偏差が 10」というスケールに並べ替えたものが偏差値です。
標準偏差は「データが平均からどれくらい散らばっているか」を表す指標で、Excel では母集団全体なら STDEV.P、標本なら STDEV.S を使って求めます。
Excel での基本テンプレート
テストの点数が B2:B101 に入っているとします。
このとき、各人の偏差値を C 列に出す基本形は次のようになります。
平均をどこかのセルに出さず、式の中で直接計算するパターンです。
=(B2-AVERAGE($B$2:$B$101))/STDEV.P($B$2:$B$101)*10+50
AVERAGE($B$2:$B$101) が「全員の平均点」、STDEV.P($B$2:$B$101) が「全員の標準偏差」です。
この式を C2 に入力して下方向にコピーすると、B 列の点数に対応する偏差値が C 列にずらっと並びます。
標準偏差に STDEV.S を使う場合は、次のように書き換えます。
=(B2-AVERAGE($B$2:$B$101))/STDEV.S($B$2:$B$101)*10+50
どちらを使うかは「母集団全体か、標本か」の考え方によりますが、学校のテストのように「そのクラス全員」を対象にするなら STDEV.P でそろえておくと分かりやすいです。
実務での使い方の流れ
偏差値計算の流れを、Excel の操作に落とし込むと次のようになります。
まず、対象となる点数の範囲を決めます（例：B2:B101）。
次に、その範囲を使って平均と標準偏差を求めます。平均は AVERAGE、標準偏差は STDEV.P または STDEV.S です。
そのうえで、「(自分の点数−平均)÷標準偏差×10＋50」を 1 本の式として書くか、平均と標準偏差を別セルに出しておいて参照するかを決めます。
別セルに出す場合の例として、D2 に平均、D3 に標準偏差を計算し、C2 に次のように書く形もよく使われます。
=(B2-$D$2)/$D$3*10+50
このように分けておくと、「平均や標準偏差を確認しながら偏差値を見る」「範囲を変えたときに D2・D3 だけ直せばよい」といったメリットがあります。
例題
問題1
B2:B31 に 30 人分のテスト点数が入っています。
このクラス全員を母集団とみなして偏差値を計算したいとき、C2 に「B2 の点数の偏差値」を求める式を、AVERAGE と STDEV.P を使って書いてください。
また、その式を C31 までコピーすると、どのように偏差値が計算されていくかを説明してください。
問題2
同じく B2:B31 の点数について、D2 に平均、D3 に標準偏差をあらかじめ計算しておくことにしました。
D2 には =AVERAGE($B$2:$B$31)、D3 には =STDEV.P($B$2:$B$31) と入力したとき、C2 に「B2 の偏差値」を求める式を書いてください。
そのうえで、「平均や標準偏差を別セルに出しておく」ことのメリットを、自分の言葉で説明してください。
問題3
偏差値の計算式は「(個人の得点−平均点)÷標準偏差×10＋50」です。
この式の中で、「−平均点」と「÷標準偏差」がそれぞれどんな意味を持っているかを、
「平均からのズレ」と「ばらつきの大きさで割る」というキーワードを使って説明してください。
問題4
B2:B31 の点数について、STDEV.P ではなく STDEV.S を使って偏差値を計算した場合、数値はどう変わりそうかを考えてみてください。
「母集団」と「標本」という言葉を使いながら、STDEV.P と STDEV.S の違いを自分なりに整理してみてください。
問題5
平均点だけを見た場合と、偏差値を見た場合では、「自分がクラスの中でどの位置にいるか」の見え方がどう違うでしょうか。
標準偏差や分散が大きいクラスと小さいクラスをイメージしながら、「偏差値がなぜ便利なのか」を自分の言葉で説明してみてください。