Excelで偏差値を求める
Excelで偏差値を求める方法を考えます。
生徒にテストを受けさせて、その得点から偏差値を求めるものとします。
(1) 全員の得点の平均を計算する。【平均】※ワークシート関数としてはAVERAGE
(2) 一人ずつ得点と平均との差の2乗を計算する。
(3) (2)の平均を計算する。【分散】※ワークシート関数としてはVARP
(4) (3)の平方根を計算する。【標準偏差】※ワークシート関数としてはSTDEVP
(5) 一人ずつ 10*(得点-平均)/標準偏差+50 を計算する。【偏差値】
もしExcelで偏差値をダイレクトに計算するためには次のような数式を使います。
A1セルからA10セルに10人分の得点がセットされている場合です。
=10*(A1-AVERAGE(A1:A10))/STDEVP(A1:A10)+50
なお「10*(得点-平均)/標準偏差+50」の「10」と「50」は固定です。
実際に計算例を見てみます。
10人の生徒が何らかのテストを受けて、得点分布を適当に決め、その得点から偏差値を計算します。
| 得点 | 偏差値 |
|---|---|
| 5 | 34 |
| 15 | 38 |
| 25 | 41 |
| 35 | 45 |
| 45 | 48 |
| 55 | 52 |
| 65 | 55 |
| 75 | 59 |
| 85 | 62 |
| 95 | 66 |
上の表は5点から95点まで等間隔でまんべんなく分布した場合の偏差値です。
| 得点 | 偏差値 |
|---|---|
| 70 | 34 |
| 71 | 38 |
| 72 | 41 |
| 73 | 45 |
| 74 | 48 |
| 75 | 52 |
| 76 | 55 |
| 77 | 59 |
| 78 | 62 |
| 79 | 66 |
上の表は70点台に固まった場合の偏差値です。
固まったとはいえ、等間隔の場合は、まんべんなく分布した場合と同じ偏差値になります。
| 得点 | 偏差値 |
|---|---|
| 90 | 34 |
| 91 | 38 |
| 92 | 41 |
| 93 | 45 |
| 94 | 48 |
| 95 | 52 |
| 96 | 55 |
| 97 | 59 |
| 98 | 62 |
| 99 | 66 |
上の表は90点台に固まった場合の偏差値です。
やはり同じ偏差値になります。
| 得点 | 偏差値 |
|---|---|
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 100 | 80 |
上の表は一人だけ100点で他は0点の場合の偏差値です。
100点の生徒は偏差値80になります。
| 得点 | 偏差値 |
|---|---|
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 0 | 47 |
| 1 | 80 |
上の表は一人だけ1点で他は0点の場合の偏差値です。
一人だけ100点の場合と同じ偏差値になります。
つまり
100点満点で100点を取った場合
100点満点で1点を取った場合
1点満点で1点を取った場合
どれも同じ結果になります。
これが偏差値の便利な点の一つで、満点が異なっても比較ができます。
どんなテストも、平均が50、標準偏差が10となるテストに変換して、そのテストならば何点か、を見るのが偏差値です。
[ 2011年10月27日 | カテゴリー: Excel | タグ: 偏差値 , 統計 ]
« ワンピースARカードダス一覧 第2弾 | JavaScriptのmatchでマルチラインフラグを使う »
コメントを残す