Excel。事務職のデータ分析25。データ分析ツールで簡単に算出できるけど、自分で偏差値を算出するには？
＜分散～偏差値：VAR.P/STDEV.P/SQRT/COUNT関数＞
Excelのアドインで「データ分析」機能を使えば、あらかた分析系の数値を算出することが出来るのですが、
いったいどうやって算出したのか？
どこの数値からこの数値を算出したのか？
わかりにくいので、今回は、通しで、分散から偏差値までを求めていきます。
【分散を求める】
分散は、データのばらつき具合をみることが出来ます。使う関数は、VAR.P関数ですね。
この表を使って、分散を算出します。
C12の数式は、
=VAR.P(C2:C11)
です。この数式をオートフィルでE12までコピーしています。
分散の数値が大きれければ、それだけ、ばらばら具合が大きいことを表しています。
3回目はあえて、全部同じ数値にしていますので、
ばらつきがないので、0（ゼロ）と算出されたわけです。
なお、分散は、各数値を平均の差の2乗和をデータの個数で割ったものなのですが、
「各数値を平均の差」はそのまま和算すると、
プラスとマイナスで相殺されてしまうので、2乗してから「和」することで、
相殺されることを防いでいます。
そこで、この分散の2乗をとったもの、
つまり、分散の平方根をとったのが、次の『標準偏差』です。
【標準偏差】
標準偏差を算出するには、STDEV.P関数を使います。
C13の数式は、
=STDEV.P(C2:C11)
となっています。
ちなみに、VAR.PのPやSTDEV.PのPですが、
このPは、母集団のPopulationのPなので、
母集団全体とみなした場合は、Pを使い、
一部のデータとみなした場合は、Sをついている関数を使います。
【標準誤差】
分析ツールを使うと、その中に、「標準誤差」という項目がありますが、これはどのように算出しているのでしょうか？
標準誤差とは、母集団データの平均と標本データの平均とのずれを示しています。
そこで、標準誤差を算出する公式があります。
=STDEV.P(範囲)/SQRT(COUNT(範囲))
算出したのが次の図ですね。
C14の数式は、
=STDEV.P(C2:C11)/SQRT(COUNT(C2:C11))
SQRT関数は、平方根を算出する関数ですね。
【標準化得点：Z得点】
15行目に平均を算出しておきます。
データのばらつきを加味して、
全体の中での位置を知ることが出来るのが、「標準化得点」ですね。
Z得点ともいわれています。
この標準化得点は、偏差値の元になっている数値です。
正数ならば、平均値よりも大きく平均値よりも遠くなるほど、値は大きくなります。
負数ならば、平均値よりも小さく平均値よりも遠くなるほど、値は小さくなります。
その、F2の数式は、
=STANDARDIZE(C2,C$15,C$13)
となっています。
この数値を算出することで、偏差値を算出することができます。
【偏差値】
偏差値は、皆さんお馴染みの、学力試験などの結果を比べる指標として使われていますよね。
この偏差値を一発で算出する関数はありません。
偏差値は次の公式で算出することができます。
=10×（得点－平均）÷標準偏差＋50
この公式の最後の+50は、偏差値は50を中心とした値になるように調整しています。
25～75までの間にデータの99%が分布することになります。
また、この公式よりも、Z得点を算出しているならば、
次の公式を使うと、もっと簡単に偏差値を算出することが出来ます。
=Z得点×10＋50
H2の数式は、
=10*(C2-C$15)/C$13+50
J2の数式は、
=F2*10+50
どちらでも算出することが出来ます。
今回は、アドインのデータ分析で算出された結果は、
どのようにして算出されたのか？ということで、
分散～偏差値まで流れでご紹介してみました。
