クラス別偏差値の求め方　（STDEV関数を使わず標準偏差を求める）

全体偏差値を求める方法は前回説明しましたが、学習塾なんかだと特進クラスや学力毎でクラス分けをしているところがあります。その場合、全体偏差値よりもクラス内偏差値を見たい事もありますので、今回は他クラスも含んだ全体データからクラス内偏差値を求める方法を説明します。

STDEV関数を使わない標準偏差が必要になります。

まずは偏差値のおさらい

偏差値は以下の式でしたね。

（A君の点数　－　平均点）　÷　標準偏差　×　10　＋　50

この標準偏差を求める関数がSTDEV関数でした。点数のバラつきを数値化したものです。

前回はここを関数を使えば良いと言いましたが、今回のクラス別で出す場合、ちょっと工夫が必要でSTDEV関数が使えないんです。理由は後述しますがSUMIFのように条件と合致した場合だけ足してくる、というような使い方がSTDEVでは出来ないからです（配列数式を使えば可能ですが、今回はパスします。というか配列数式は極力避けた方が良いのが私の考えです。）

さてさて、標準偏差の求め方ですがわかりやすく説明されているサイトがこちらです。

偏差値の求め方

 

分散値の平方根　となるのですが、簡単に説明すると

1. 平均と実点数の差を求める（各人）
2. 1の差を2乗する
3. 2の数値の平均を求める　←　これが分散値
4. 3の平方根、つまり1/2乗する　←　これが標準差値

ちょっとむずかしいかもしれませんが、とりあえず標準偏差はSTDEV関数じゃなくても簡単に求められるという事です。

クラス内の偏差値

下図のように別クラスも同じシートにデータが並んでいる事が前提です。

まずはクラス（1組～3組）別の平均点

=SUMIF($A$2:$A$31,A2,$C$2:$C$31)/COUNTIF($A$2:$A$31,A2)

SUMIF
もし　A列が　A2（対象行のA列）と同じなら　C列を合計

COUNTIF
もし　A列が　A2（対象行のA列）と同じなら　その件数をカウント

つまり1組の点数合計　÷　1組の人数　をしています。

各個人の平均点との差を2乗

=(C2-D2)^2

2乗の式は　＾　（キャロットと呼ぶ）を使います。後ろの2を3にすれば3乗です。Excelではべき乗計算に使われます。

あとはE列合計を平方根を求めれば標準偏差です。

SQRT関数

平方根を求める関数がSQRT関数です。Excelには√という文字を扱う機能はありません。例えば =√4　とかしても　√　はただの文字列なので　#NAME?　とエラーになってしまいます。

脱線しますが、3乗根や4乗根などのべき乗の場合は、＾（キャロット）を使えば解決。
=8^(1/3)　こうすれば8の3乗根・・・結果2になります。

ですので平方根もSQRT関数使わずとも　=4^(1/2)　という方法もあります。あまり使う関数では無いので、もしかしたら＾（キャロット）の方が覚えやすいのかもしれませんね。

=SQRT(SUMIF($A$2:$A$31,A2,$E$2:$E$31)

SUMIFで、クラス別のE列合計をし、それの平方根を求めています。

さて、それを最初の偏差値を求める公式に当てはめるとこうなります。

=(C2-D2)/SQRT(SUMIF($A$2:$A$31,A2,$E$2:$E$31)/COUNTIF($A$2:$A$31,A2))*10+50

これで各個人、クラス内での偏差値を求める事ができました。

STDEV関数やSQRT関数についての記事でしたが、計算式の内容を理解すれば関数使わずに計算する事は可能ですし、またSUMIFやCOUNTIFを駆使することで希望のデータを抜き取る事が可能です。関数の組み合わせって本当に奥が深いですね。

おまけ

全体の偏差値と比べるため全体偏差値も追加してみました。
また、どのクラスが優秀なのか、クラスの偏差値も追加してみました。

3組が一番優秀で2組が一番成績が悪いです。

例えば2組の藤田さん（20行目）はクラス内では平均的なところに居ますが、全体で見ると偏差値46なので少し下になります。

この事から、偏差値というのは集計する単位によってコロコロ変わりますのであくまでも目安として理解しておきましょう。

サンプルファイル　ダウンロード