目次
クラスのテスト結果分析シート
統計のお勉強の練習問題として、「学校の先生がクラスの生徒のテスト点数を入力して、偏差値を計算したり、軽く傾向分析などをやってみる」というのを想定して、EXCELシートを作ってみました。。
こんなイメージのもんです。
こんなんでも、学校の先生とかで使ってみようと思ってくれる人が一人くらいはいるかもしれないな・・と思ったので、とりあえずダウンロードできるようにしました。
試験点数分析ブック.xlsxの方です。
興味があれば、ダウンロードして好きにしてもらえばいいかなと思ってます。
使っている統計値とEXCEL関数
C列（C4）から、生徒の点数を入力してもらう想定です。
とりあえず、50人分が入力できるように枠をとってます。
それで、以下をまず計算してます。
受験人数
テスト点数なので、0点以上が入力されている人数を数えてます。
=COUNTIF(C4:C53,">=0")
平均点
Avarageではなく、テスト点数の合計を、受験人数でわって四捨五入してます。
計算式はこんな感じ。
=ROUND(SUM(C4:C53)/G4,0)
中央値（得点）
点数順に並べたときに、ちょうど中央に位置する点数を示します。
平均点だと極端な話、100点が20人いて、50点以下が40人いるみたいに極端にばらついていても、それなりの点数になってしまうので、まあ、これも見ておいたほうがいいかなと思ってあげてます。
EXCELの関数「MEDIAN」を使ってます。
=MEDIAN(C4:C53)
最頻値（得点）
最も多くの人数が集中している点数です。
これも平均点だけではわからない傾向が発見できて面白いかなと思ってます。
EXCELの関数「MODE」を使ってます。
=MODE(C4:C53)
分散
点数が平均からどの程度ちらばっているかを示す値です。
分散は数字が大きくなるので、これだけ見て何か判断するようなものではない（少なくともこのシートにおいて）ですけど、お勉強のための参考としてのせてます。
EXCELの関数「VAR.P」を使ってます。
=VAR.P(C4:C53)
標準偏差
これも点数が平均からどの程度ちらばっているかを示す値です。
偏差値を計算するのに使います。
EXCELの関数「STDEVP」を使ってます。
=STDEVP(C4:C53)
例えば、平均が59点で、標準偏差が18.4点なら、平均値プラスマイナス18.4点の範囲中に全体の約68パーセント（偏差値で言うと40〜60）がはいり、平均値プラスマイナス36.8点の範囲に全体の約95パーセント（偏差値で言うと30〜70）がはいるということになります。
変動係数
標準偏差を平均値で割った値のことで、単位の異なるデータのばらつきや、平均値に対するデータとばらつきの関係を相対的に評価する際に用いる数値です。
テストの点数で使うような数値ではないですが、統計のお勉強をかねてなので、計算してます。
計算式はこんな感じですが。
=G9/G5
セルの意味合いは「標準偏差÷平均」です。
偏差値
偏差値は、個人単位で算出します。
計算式は以下です。
EXCELの計算式は。
=ROUND(50+(10**1,1)
上記の計算式を、そのまま計算式にしただけです。
得点分布とヒストグラム
下の段にある得点の分布とヒストグラムは、見ればわかると思うので、特に補足はしませんが、テストの点数の分布が視覚的に把握できるようにしてます。
最後に
だから、どうした？って感じではありますが（笑）。
お勉強がてらにまとめてみました。
ではでは。
*1:C4-$G$5)/$G$9