戻る

Excel で偏差値をあつかう

Excel で生徒のテスト結果を集計するときに、偏差値を計算したいときがあります。数学の先生の話によると偏差値は、 　　　[平均点との差]×10÷標準偏差 と 50 との差 で求められるらしいです。 　この式の意味とか、標準偏差って何なのかは私にはわかりません。(^_^;;　すみません、英語教師なので．．．　しかし、とにかく Excel の関数を使えば簡単に求めることができます。平均点は AVERAGE(セル範囲) で、標準偏差は STDEV(セル範囲) で求めます。つまり、 =([その生徒の点数]-average(全生徒の点数のセル範囲))*10/stdev(全生徒の点数のセル範囲)+50 という式になります。ただし、このまま埋め込むと長い式になりますし、たいてい平均点ぐらいは集計してあるはずなので、平均点と標準偏差は別のセルに格納しておくことにしましょう。 　たとえば、下の図を見てください。 　この表は、 生徒の点数はセル範囲 D2:D361 に入力してある 平均点はセル D363 に =AVERAGE(D2:D361) と入力して格納してある 標準偏差はセル D364 に =STDEV(D2:D361) と入力して格納してある という状態です。 　ここで、偏差値を E列 に入力することにします。セル E2 に =(D2-$D$363)*10/$D$364+50 と入力して、この式を E361 までコピーすれば、ＯＫです。数式をコピーしますので、平均点と標準偏差が格納されているセルを絶対参照で指定する点に注意してください。 　このようにすれば Excel で偏差値を扱うことができますが、偏差値の性質上 40 人程度のデータが母集団では全く意味をなしませんし、１学年分のデータであっても上位・下位のあたりは無意味な数字になりかねません。偏差値を求めることが本当に必要かどうかを検討することが必要だと思います。 　ちなみに、Ｍｒ．Ｂｉｇ の『成績管理システム』や『教務手帳』で偏差値を扱っていないのも、不必要であると判断しているからです。 　とはいえ、偏差値を求めなければならない場面があることも事実です。試したことのない方は一度使ってみるのも面白いかもしれないと思って、紹介させていただきました。