Excel。偏差値って意外と簡単に算出することが出来るんです。
＜AVERAGE関数&STDEV.P関数＞
偏差値と聞くと、どうやって算出しているのか？
算出するには、とても難しいイメージがしますが、Excelをつかうと、比較的容易に算出することができるので、ご紹介していきます。
次のデータを用意しました。
B列のWritingとC列のListeningの点数が入力されている表です。
WritingとListeningは同じ試験であるはずもなく、同じ点数を取ったとしても、難易度が異なっているので、その点数が、科目内での良し悪しは、単純にはわからないわけですね。
データの見た目から、Writingの点数がListening全体的に低い感じがしますね。
というか、見た目で判断するのはよくありません。
折角Excelというツールがあるわけですから、平均値から算出してきます。
F2の数式は、
=AVERAGE(B2:B11)
オートフィルで数式をコピーして、G2のListeningの平均も算出します。
最低限、平均値は算出したいところですね。
これで、Listeningのほうが20ポイントWritingより高いことがわかりました。
平均値は算出しましたが、WritingとListeningのそれぞれの中で、データの「ばらつき」も考える必要があります。
要するに、平均値は、平均値よりも離れた極端な値があると、極端に影響を受けてしまうからです。
この「ばらつき」を表すのが「標準偏差」です。
この標準偏差も、STDEV.P関数をつかえば、算出することができます。
F3の数式は、
=STDEV.P(B2:B11)
標準偏差は、値が大きいほど、「ばらつき」がありますので、今回のデータでは、Listeningよりも、Writingのほうが得点に差がある。
つまり「ばらつき」があることがわかりました。
平均値と標準偏差を算出することができれば、偏差値を算出するには、公式に当てはめてあげればいいわけです。
偏差値の公式は、
偏差値＝（データ－平均点）÷標準偏差×10+50
で求めることができます。
それぞれの偏差値を算出しますので、列を増やしておきます。
D2の数式は、
=(B2-H$2)/H$3*10+50
複合参照をつかっていますが、これは、オートフィルで数式をコピーするために複合参照をつかっておりますが、わかりにくい場合は、絶対参照にして、列ごとに算出するといいですね。
データ内で、どこに位置しているのかがわかるようになります。
例えば、Writingでは、67点で、Listeningが78点と、Listeningのほうが点数自体は高いのですが、そのグループの中では、Listeningのほうが低い位置にあることがわかるわけですね。
むしろ力を入れないといけないのは、Listeningのほうだったわけです。
数値は、見た目のイメージだけで、優劣をつけてしまいがちなので、注意したいところですね。
