　５．　偏差値計算　

　　　５－１　偏差値の出し方
　　　５－２　標準偏差（STDEV 関数）
　　　５－３　偏差値を計算する（STANDARDIZE 関数）
　　　５－４　相対参照と絶対参照

５－１　偏差値の出し方

俗にいう偏差値の計算方法は、以下の通りです。

（得点－平均値）
――――――――　×　10　＋　50
　　標準偏差

大手予備校主催の模試でこの処理が行われていることから、けっこう馴染み深いことと思います。
高等学校の成績評価は絶対評価を標榜していますが、私の知る範囲では、考査素点の平均点が低くなりがちな（いわゆる進学校の）数学や理科の先生で、成績処理に偏差値を用いる人がいます。（偏差値を成績評価の素点にするということは、結局は相対評価で成績を付けるということですね。）あるいはまた、定期考査や実力テストの集計において、各教科の「重み」を均等にするように総合成績（順位付け）を計算する時などにも偏差値を使うことが多いと思います。
さて、本ページを覗いてみよう、という方々の中には「『偏差値』が示す値って、そもそも何なの？」といった方もいらっしゃるかもしれません（失礼……）。
偏差値とは、平均からどのくらい離れているか、を示す値です。データ量が十分に大きいため、データの分布が正規分布に従っていると推定される場合、各偏差値の生徒は、集団の中で下記のようなポジションにいるといえます。


集団全体＝100％として

偏差値80以上 …… 上位 0.18％以内
偏差値70以上 …… 上位 2.28％以内
偏差値60以上 …… 上位15.87％以内
偏差値50以上 …… 中位以上
偏差値50　　 …… 真ん中（平均値）
偏差値50以下 …… 中位以下
偏差値40以下 …… 下位15.87％以下
偏差値30以下 …… 下位 2.28％以下
偏差値20以下 …… 下位 0.18％以下

非常に荒っぽい話をすると、テストで「偏差値60」を（いつも）とれる生徒は、その学力が母集団全体の上位約１６％の位置にあるということでしょう。もし母集団から無作為に100人抽出したら、その中の１６位ぐらい、1000人だったら 160位ぐらいに食い込む実力があると見てよい生徒です。

５－２　標準偏差（STDEV 関数）

テストで「国語は平均点＋10点だった」「英語は平均点15点だった」という結果だった場合、この生徒は英語の方が得意だといえるでしょうか。これに、「国語の最高点は平均点＋15点だった」「英語の最高点は平均点＋40点だった」という情報が加わると、話が違ってきますね。国語の＋10点の方が、英語の＋15点より価値が高そうです。
標準偏差とは、大雑把にいえば、個々のデータが平均値からどのくらい離れているかの度合いを平均化したしたような値です。標準偏差が大きいほど、集団内の「ばらつきが大きい」ことを意味します。上の例で言えば、最高でも平均点＋15点の者しかいない国語は、最高点が平均点＋40点だった英語より、「標準偏差が小さい」＝「集団が平均点付近に固まっている」ことが予想されます。
テストの「１点の重み」は、本当は標準偏差との関わりの中で決まるべきです。

Excelには、セル範囲のデータの標準偏差を計算してくれる関数があります。

　　=STDEV(セル範囲 )
　　　　セル範囲を母集団の標本とみなし、標準偏差の推定値を返します。

　　=STDEVP(セル範囲 )
　　　　セル範囲を母集団全体とみなし、その標準偏差を返します。

　　=STDEVA(セル範囲 )
　　=STDEVPA(セル範囲 )
　　　上記と同じ関数ですが、セル範囲 の中に文字列・論理値をあると
　　　それを集団に含めて扱います（TRUE=1, FALSE=0 で計算）。

セル範囲 に有意のデータのみがあり、また学校事務、成績処理に要求される精度程度であれば、上記４つの関数のどれを用いても大差ありません。ただし、使うたびに違うようでは、比較する場合などに困ることもあるかも。
過年度比較が必要な処理、模試のデータを学校で再処理する場合、科学実験データの処理、無作為抽出アンケートの処理などについては、筋論からいえば STDEV関数を使うべきなのでしょう。

５－３　偏差値を計算する（STANDARDIZE関数）

偏差値計算の計算式をたててみましょう。

(a)　　=(得点 - 平均値)/標準偏差 * 10 + 50

(b)　　=STANDERDIZE(得点, 平均値, 標準偏差 ) * 10 + 50

上記２つの式 (a)、 (b) は同じことをしています。STANDERDIZE関数は、与えられた平均値と標準偏差を持つ分布に基づく「標準化変量」を返す関数です。
標準偏差 が 0 である時、式 (a) では当然のことながら ~~#DIV/0!~~エラーを返します。また、式 (b) では、標準偏差 が 0 以下である時に STANDARDIZE 関数が~~#NUM!~~エラーを返しますから、現実の表作りには、それぞれエラー処理への配慮が必要でしょう。

（例）　セル B2～B41 に得点データが記述されています。
　　　　セル B42 に　=AVERAGE(B2:B41)　が記述されています。
　　　　セル B43 に　=STDEV(B2:B41)　が記述されています。
　　　　セル C2 に　=(B2-B$42)/B$43*10+50　が記述されています。
　　　　セル C3 に　=(B3-B$42)/B$43*10+50　が記述されています。
　　　　セル C4 に　=(B4-B$42)/B$43*10+50　が記述されています。
　　　　セル C5 に　=(B5-B$42)/B$43*10+50　が記述されています。

	A	B	C	D
1	氏名	得点	偏差値
2	生徒氏名１	65	52.47
3	生徒氏名２	23	32.90
4	生徒氏名３	90	64.12
5	生徒氏名４	69	54.33
：：	：：	：：	：：	：：
42	平均	58.10
43	標準偏差	21.45

セルC2 に　=(B2-B$42)/B$43*10+50　を記述して、C3 ～ C41にはその式をコピー＆ペーストすればＯＫです。

５－４　相対参照と絶対参照

数式のコピーは便利な機能ですが、この時注意しなければならないのは、B42、 B43には "$" を付けて記述しなければならないということです。これをセル番地の「絶対参照」といいます。絶対参照にしないと、セル参照を含む数式を別のセルにコピーした時に

　　C2:　=(B2-B42)/B43*10+50
　　　　　　　　　↓コピー＆貼り付け
　　C3:　=(B3-B43)/B44*10+50

というふうにセル参照が変化してしまい、「平均値」「標準偏差」のセル番地を指さなくなってしまうからです。
一方、個人データを参照しているセルB2 は、C3に貼り付けた時 B3 に変化している方がありがたいです。実際にそのように変化してペーストされています。実は、セル C2 の式に記述されているB2 は、「絶対番地としてのＢ列２行目」を指しているのではなく、「C2から見た時のB2の位置」を示しているに過ぎないのです。これを「相対参照」といいます。

セルに書き込まれた数式の中に（"$"を含まない） B42、B2:B41といったセル参照が書かれている時、それは数式のあるセルからの「相対的な位置」を示す「相対参照」です。表面的には後述する絶対参照と同じように見えるので、初めは相対的な位置を示しているという感覚を持ちづらいかもしれません。

重要なポイントなので、もう少し例を挙げて説明しておきます。

（例）セル C2 の数式の中に、以下のセル参照が記述されている。
　　　　E2　← 同じ行にある、２列右のセルを示している。
　　　　C4　← 同じ列にある、２行下のセルを示している。
　　　　B5　← １列左、３行下のセルを示している。
　　　　B1:B40　← １列左、１行上～38行下のセル範囲を示している。

「=B5」という式が書かれたセル C2 をセル F3 にコピー＆ペーストすると、F3 に書かれた数式は「=E6」に変化します。

　　