偏差値60と上位16パーセントの関係を確認するExcelファイル

偏差値を上位何パーセントなのかに換算するにはNORMDIST関数を、上位何パーセントという数値を偏差値に換算するにはNORMINV関数を使うということを記事にしました。

偏差値60が上位約16パーセントに該当するというのは、データが完全に正規分布している状態の、あくまで理論上の値ですから、実際のデータでは当然変動します。

どれくらい変化するものなのかが気になったので、Excelファイルを作ってみました。

偏差値60と上位16%を確認するサンプルファイル

サンプルファイルの、
　　D列に得点
　　E列に偏差値
　　F列に上位何%か
を、それぞれ計算するようにしています。

条件付き書式で、E列には60以上の数値に水色の背景色、F列には0.16以下の数値にベージュ色の背景色を、設定しています。

▼サンプルファイル（32KByte）ダウンロード

ショートカットキー［F9］で再計算を行うと、得点が生成しなおされて、F列とG列の色のついているセルが変化することを確認できます。

何度か［F9］キーを押すと、偏差値60と上位16パーセントが、一致することもあれば、乖離することもあるということが見えてきます。

サンプルファイルで行っていること

A列に正規乱数

偏差値の計算には、得点が正規分布しているという大前提がありますから、単なる乱数では意味を成しません。

そこでA列に
「=NORMINV(RAND(), $A$1, $A$2)」
という数式で、正規分布する乱数を生成するようにしています。

正規乱数を生成するNORMINV関数で利用する、平均はA1セルに標準偏差はA2セルで、それぞれ指定するようにしています。

B列でA列の正規乱数を降順で並べ替え

このあとの状態を確認しやすくするためB列に
「=LARGE($A$5:$A$204, ROW() -4)」
という数式を入れて、A列の正規乱数を、降順で並べ替えを行っています。

D列に得点

B列のデータをそのまま得点として使ってもいいのかもしれませんが、100点を超える点数が含まれるのは、ちょっとあれなので、D列には
「=IF(B5>=100, 100, INT(B5))」
という数式を入れ、100以下の整数だけにしています。

なお、A列セルに生成する正規分布した乱数の生成で利用する、A1セルの平均点やA2セルの標準偏差によっては、負の数値も発生する可能性もありますが、平均を65・標準偏差を10としている場合は、まず発生しないでしょうから、ここでは考慮していません。

E列に偏差値

平均をD1セルにはAVERAGE関数でD列の平均、D2セルにはSTDEVP関数でD列の標準偏差を計算しています。

E列にはこのD1セル・D2セルの値を利用した
「=(D5 - $D$1) / $D$2 * 10 + 50」
という数式で偏差値を計算しています。

F列に上位n%

F列には
「=RANK(E5, E$5:E$204) / COUNT($E$5:$E$204)」
という数式で、上位何パーセントなのかを計算しています。

散布図

F列までで、偏差値60と上位16%の関係は確認できますが、そもそも生成した得点が、どんな分布をしているのかが気になったので、H5:I25セルに5点ごとの分布数をカウントして、H5:I25セルを使った散布図を作成してあります。