Categories: 教師のEXCEL講座

【教師のEXCEL講座】EXCELで偏差値を求める方法

こんにちは。福田泰裕です。

テストを実施した後、平均点と順位を計算すると思います。
しかし、大学入試などに向けた模試では、順位よりも偏差値の方が気になります。

この記事では、EXCELを使って偏差値を求める方法をご紹介します。

最後まで読んでいただけると嬉しいです。

目次

広告

偏差値の意味と計算方法

まず、偏差値の意味と計算方法はご存じでしょうか?
詳しくはこちらの記事に書いているので、ご覧ください👇

偏差値の計算方法は、次の通りです。

平均点 \(\bar{x}\)、標準偏差 \(s\) のテストで点数が \(x_i\) だったとき、偏差値は

$$\frac{x_i-\bar{x}}{s} \times 10 + 50$$

偏差値の計算には、平均点と標準偏差が必要です。
まずは平均点と標準偏差を求めるところから始めましょう。

EXCELで偏差値を求める

それでは、偏差値を求めていきましょう。
次のような表を使います。

実際、10人程度の少人数では偏差値を求める意味はありませんが、今回は説明のためということでご容赦ください。

EXCELで偏差値を求める①:平均点と標準偏差を求める

まず、偏差値の計算に必要な平均点と標準偏差を求めていきましょう。

表の最後に、平均点と標準偏差を求める部分を追加します👇

そして、平均点と標準偏差を求める関数を入力します。
標準偏差を求める関数については、こちらの記事をご覧ください👇

セルC12には、平均点を求めるために

=AVERAGE(C2:C11)

セルC13には、標準偏差を求めるために

=STDEV.P(C2:C11)

と入力します👇

Enterを押すと…👇

平均点と標準偏差を求めることができました。

EXCELで偏差値を求める②:偏差値を計算する

それでは、上で求めた平均点と標準偏差を使って、偏差値を計算していきましょう。

もう一度載せますが、偏差値の計算方法は次の通りです👇

平均点 \(\bar{x}\)、標準偏差 \(s\) のテストで点数が \(x_i\) だったとき、偏差値は

$$\frac{x_i-\bar{x}}{s} \times 10 + 50$$

この式の通り、セルD2へ

=(C2$C$12)/$C$13*10+50

と入力します👇

平均点のセルC12と、標準偏差のセルC13はオートフィルで移動しないように、$をつけて絶対参照にしています。
セルを選択した直後にF4を押すと、絶対参照にできます。

入力して、Enterを押すと…👇

岩鬼くんの偏差値が計算できました。
あとはオートフィルで下へコピーすると…👇

全員の偏差値を計算することができました!

まとめ:EXCELで偏差値を計算できる!

いかがでしたでしょうか。

EXCELには、一発で偏差値を計算してくれる関数は用意されていません。
しかし、今回の記事で紹介したように平均値と標準偏差を先に求めておくことで、偏差値も計算することが可能です。

偏差値を計算することで、他のテストと比較することも可能になります。
ぜひ偏差値を計算して、テストの分析に役立ててください!

最後まで読んでいただき、ありがとうございました。

質問やご意見、ご感想などがあればコメント欄にお願いします👇

福田 泰裕

33歳、2児の父。 山口県の高校教師で、担当は数学と情報。 毎日定時ダッシュするために、働き方改革を実施中。 数学教育・情報教育・教師の働き方・教師のEXCEL講座などを記事にしています!