【エクセル】成績の偏差値を求める方法と計算式を紹介。標準偏差と平均値の関係は？

大学受験などの際、自分の成績が周りと比べてどのくらいか気になりますよね？

自分の成績が相対的に見て、どのくらいか判断するために『偏差値』というものがあります。

大学の志望校にも、合格できる目安の『偏差値』が公開されています。

この記事では、「偏差値と何なのか？」「Excelで成績の偏差値を求める方法」「偏差値に関する補足情報」を紹介します。

偏差値とは？

例えば、算数のテストを結果が「80」点だったとしても、テストが簡単で平均点が「80」点だった場合、あまり良い結果とは言えないですよね？

そのため、テストの点数だけでは良し悪しが判断できません。

そんな時に利用するのが「偏差値」です。

「偏差値」は、テストを受けた集団の中で、自分がどのくらいの位置にいるかを表した数値です。

平均点が偏差値「50」になるように変換され、その基準からどれくらい高いか低いかを数値で表します。

自分の実力を相対的に把握することが出来るので、テストの点数以上に偏差値の結果を気にすることも多い重要な指標です。

テスト結果のバラつきに影響しますが、一般的に偏差値が「60」であれば、「上位15～20％」に該当します。

偏差値の計算式

偏差値をエクセルの計算式で求めることが出来ます。

「偏差値」を求めるためには、「平均点」と「標準偏差」という数値が必要になります。

自分の点数だけでは偏差値は算出できないので、テストを受けた全員の点数が分かっていることが、偏差値を算出する前提条件となります。

偏差値の計算式は、以下の通りです。

偏差値は「(得点−平均値)×10/標準偏差+50」の計算式で求めることができます。

「得点」は、テストを受けた人の点数のことです。

例えば、平均点「60」点、標準偏差が「10」、得点「70」点の場合の偏差値を計算式に当てはめてみます。

「(得点−平均値)×10/標準偏差+50」は、「(70−60)×10/10+50」となるので、偏差値は「60」となります。

Excelで成績の偏差値を求める方法

それでは、「(得点−平均値)×10/標準偏差+50」の計算式を使って、エクセルで偏差値を求める方法を紹介します。

手順１～6の順番に沿って作業してください。

標準偏差を算出するための関数を利用しますが、作業目安５分ほどで完成します。

手順１．成績表を準備する

まずは、エクセルに偏差値を算出する元となる表を準備します。

成績の点数表を氏名毎にすべて並べて入力します。

データのバラつき具合は標準偏差に影響するので、一部だけのデータだけでなく、全データを入力してください。

並び順は大きい順でもランダムでも、任意で問題ありません。

手順２．平均値を求める

次にAVERAGE関数を使って、点数を平均値を求めます。

=AVERAGE(C3:C9)

平均値の結果を確認します。下の表の場合、平均値は「61」となります。

手順３．標準偏差を計算する

次に、「標準偏差」を計算します。

標準偏差は、数値のバラつき具合や、平均値からのズレを表すための数値です。

エクセルには、標準偏差を求めることができるSTDEV.P関数(スタンダード・ディビエーション・ピー)が存在します。

構文と引数は、以下のとおりで、セルを指定するだけで利用することができます。

=STDEV.P（数値1,数値2…）

それでは、平均値の下のセルに以下のSTDEV.P関数を挿入して、標準偏差を求めます。

=STDEV.P(C3:C9）

標準偏差の結果を確認します。下の表の場合、標準偏差は「17」となります。

仮に、100点満点のテストで平均点が「60」点で標準偏差が「20」もあると、問題をほとんど解けた人と、ほとんど解けなかった人に分かれた、といった数値となります。

逆に標準偏差が小さく平均点が「60」点という場合は、ほとんど解けた人も少なく、ほとんど解けなかった人も少ない、と数値となります。

問題を作成する先生や講師であれば、生徒の理解度や問題の難易度を把握するために、標準偏差の大きさも意識するでしょう。

手順４．偏差値を計算する

次に、偏差値を求めます。

上の見出しでも紹介したとおり、偏差値の計算式は、「(得点−平均値)×10/標準偏差+50」で求めることができます。

そのため、一番上の点数の右のセルに、以下の数式を挿入します。

=(C3-$C$11)*10/$C$12+50

平均値のC11セルと標準偏差のC12セルは、数式を下のセルにも利用出来るように絶対参照の「$」マークを挿入します。

数式を「Enter」キーで確定し、偏差値の結果を確認します。

下の表のとおり、平均点「61」点、標準偏差が「17」の場合、「54」点の偏差値は「46」であることが分かりました。

手順５．数式をオートフィルでコピペする

下のセルにも数式を反映するため、手順４で挿入した数式を下のセルにコピペします。

D3セルの右下にカーソルを合わせて、マウスのポインターを「＋」マークに変えます。

その状態で、D9セルまでドラッグしてオートフィルで数式をコピぺします。

手順６．偏差値の結果を確認する

以上で、すべての点数の偏差値を求めることが出来ました。

このように、エクセルで偏差値を求めるためには、AVERAGE関数とSTDEV.P関数の２つの関数を利用するだけで、簡単に求めることが出来ます。

偏差値に関する補足情報

偏差値の平均は？

偏差値の平均値は「50」になります。

偏差値のセルに対して、「=AVERAGE(D3:D9)」を挿入すると、「50」になることが確認出来ます。

また、平均値の偏差値も「50」になります。

偏差値の計算式が「(得点−平均値)×10/標準偏差+50」なので、(得点−平均値)が「0」となります。そのため、計算結果は必ず0になります。

偏差値60ってどのくらいの割合？

上位だとイメージされやすい偏差値「60」とは、上位何パーセントの割合に入るか紹介します。

「偏差値」が左右対称の正規分布の場合、上位何パーセントの割合に入っているか、また1,000人中何位かを下の表で表します。

下の表のとおり、偏差値「60」は上位15.9％で、1,000人中159位ということになります。

この表から見ても、成績で偏差値を「5」上げることが、どれほど大変かが分かりますよね。

偏差値の最大値と最小値は？

「偏差値」は相対的にどのくらいの位置にいるかを判定するための数値です。

そのため、極端に数値にばらつきがある分布の場合、偏差値がマイナスになることも、100を超えることもあり得ます。

例えば、下のテストの結果のように、34人が「0」点で、１人だけが「100」点の極端な場合の偏差値を算出します。

この唯一「100」点を取った人の偏差値は「108」となり、100を超えるケースがあることが分かります。

その逆のケースで、34人が「100」点で、１人だけが「0」点だった場合、唯一「0」点を取った人の偏差値は「-8」となり、マイナスとなる場合もあります。

このため、偏差値の最大値や最小値というものが存在しませんが、偏差値は「30」～「80」に収まるというのが一般的です。

大学入試偏差値ランキング

大学入試になると、自分の偏差値と希望する大学の偏差値が気になるものです。

以下の東進ハイスクールのHPに、最新の合格可能性50％の大学入試偏差値ランキングが載せられているので、参考にしてみてください。