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

数式 / 関数 , , , , ,

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

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

まずは偏差値のおさらい

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

(A君の点数 - 平均点) ÷ 標準偏差 × 10 + 50

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

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

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

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

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

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

クラス内の偏差値

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

2014100301

まずはクラス(1組~3組)別の平均点

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

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

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

2014100302

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

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

2014100303

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

SQRT関数

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

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

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

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

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

2014100304

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

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

おまけ

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

 

2014100305

 

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

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

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

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

コメントをどうぞ

メールアドレスが公開されることはありません。

SUM STDEV ネットプリント 無料 クイズ 標準報酬月額 COLUMN SUMIF バーコード 作業効率UP 目標 印刷 ワイルドカード TODAY 図形 コピペ 条件付き書式 XLSTART 配列数式 VLOOKUP コンビニ フロー図 テンプレート COUNTIF 検索 偏差値 封筒 NA() 履歴書 チルダ
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑