Excelでテスト結果やアンケートの点数を比較するとき、単純な得点だけでは集団の中でどの位置にいるのか判断しにくいものです。
そこで役立つ指標が偏差値です。
平均点が異なる試験でも相対的な成績を比べやすくなり、平均を偏差値50として自分の位置を数字で把握できるようになります。
ExcelならAVERAGE関数とSTDEV.P関数、またはSTDEV.S関数を組み合わせることで、複数人分の偏差値を効率よく計算できます。
偏差値の基本式
偏差値 ＝ 50 ＋ 10 × （個人の得点 － 平均点）÷ 標準偏差
本記事では、1行目に見出しがあり、B列に氏名、C列に得点を入力した表を例に、関数の入力方法、標準偏差の選び方、グラフでの見せ方、上位何パーセントかを調べる方法まで解説します。
数式をコピーするときの絶対参照や、偏差値を扱う際の注意点も確認していきましょう。
エクセルで偏差値を求める計算式と関数の入力方法
それではまず、Excelで偏差値を算出する基本の操作について解説していきます。
| 行 | B列 | C列 | D列 |
|---|---|---|---|
| 1 | 氏名 | 得点 | 偏差値 |
| 2 | 田中 | 82 | 62.4 |
| 3 | 佐藤 | 68 | 51.0 |
| 4 | 鈴木 | 55 | 40.4 |
偏差値の計算に必要な平均点と標準偏差
偏差値は、個人の得点、集団の平均点、そして得点のばらつきを示す標準偏差の3要素から作られます。
平均点より高い点数なら偏差値は50を超え、低い点数なら50を下回ります。
ただし、平均との差だけでは偏差値を出せません。
同じ10点差でも、全員の点数が近い集団では大きな差であり、点数が広く散らばる集団では小さな差になるからです。
標準偏差は得点の散らばり具合を数値化したものであり、偏差値ではこの値で平均との差を調整します。
平均点が60点で標準偏差が10の場合、70点の偏差値は60です。
平均点が60点で標準偏差が5の場合、70点の偏差値は70になります。
得点差が同じでも標準偏差が小さいほど、集団内での位置の差は大きく評価されます。
そのため偏差値は、難易度が違うテストや受験者集団の比較に向く指標です。
セルに入力する偏差値の数式
サンプル表で得点がC2からC11に入力されている場合、D2セルへ次の数式を入力します。
=50+10*(C2-AVERAGE($C$2:$C$11))/STDEV.P($C$2:$C$11)
この式のC2は、偏差値を求めたい本人の得点セルです。
AVERAGE関数はC2からC11までの平均点を求めます。
STDEV.P関数は同じ範囲の母標準偏差を求める関数です。
$C$2:$C$11のようにドル記号を付ける絶対参照が重要です。
絶対参照にしておけば、D2の数式を下の行へコピーしても、平均と標準偏差を求める対象範囲がずれません。
一方で先頭のC2にはドル記号を付けません。
ここはD3ではC3、D4ではC4へ変化し、それぞれの人の得点を参照する必要があるためです。
オートフィルによる全員分の偏差値計算
D2セルに数式を入力してEnterキーを押すと、最初の人の偏差値が表示されます。
小数点以下が長く表示された場合は、ホームタブの小数点以下の表示桁数を減らすボタンで1桁または2桁に整えましょう。
続いてD2セル右下の小さな四角にマウスポインターを合わせ、十字形になったら下方向へドラッグします。
これがオートフィルです。
隣のC列に連続した得点データがある場合は、フィルハンドルをダブルクリックして最終行までコピーする方法も便利です。
【操作のポイント】数式をコピーする前に、平均点と標準偏差の範囲だけが絶対参照になっているか数式バーで確認しましょう。
全員分を計算した後、偏差値の平均をAVERAGE関数で確認すると、通常はおおむね50になります。
表示の丸め方によってわずかな差が出ることはありますが、極端に離れている場合は参照範囲や数式を見直してください。
平均点と標準偏差を別セルで管理する方法
続いては、平均点と標準偏差を見える場所に表示しながら計算する方法を確認していきます。
| F列 | G列 |
|---|---|
| 平均点 | 66.8 |
| 標準偏差 | 12.3 |
| 田中の偏差値 | 62.4 |
AVERAGE関数で平均点を表示する手順
集計用のセルとして、たとえばF2に平均点、G2に計算結果を置きます。
G2セルには次の数式を入力します。
=AVERAGE(C2:C11)
平均点を別セルに表示する形なら、テストごとの成績表を確認するときにも全体の水準がすぐ分かります。
得点欄に空白セルがあってもAVERAGE関数は空白を平均の計算対象から除外します。
ただし、欠席者を0点として扱いたい場合は、空白のままにせず0を入力するか、別の集計ルールを明確にする必要があります。
欠席と未入力をどう扱うかで平均点と偏差値は変わるため、データを作成する段階で基準をそろえましょう。
文字列として入力された数字が混在していると計算結果に影響することもあります。
セルの表示位置が左寄せになっている数値は文字列の可能性があるため、確認が必要です。
STDEV.P関数とSTDEV.S関数の使い分け
標準偏差を出す関数には、STDEV.P関数とSTDEV.S関数があります。
G3セルへ母標準偏差を表示するなら、次の式です。
=STDEV.P(C2:C11)
STDEV.P関数は、入力した全員を対象集団そのものとして扱う場合に使用します。
たとえば、あるクラス全員のテスト結果から、そのクラス内の偏差値を出す用途ではSTDEV.P関数が自然です。
一方、STDEV.S関数はデータを全体から取り出した標本として扱う場合に用います。
調査対象者の一部だけを抽出し、より大きな母集団の傾向を推定したい場面ではSTDEV.S関数を検討します。
クラス全員、部署全員、受験者全員の順位を表す用途ではSTDEV.P関数を使うケースが一般的です。
抽出調査の分析ではSTDEV.S関数が適する場合があります。
どちらかを途中で混在させると比較基準が変わるため、同じ表では一方に統一しましょう。
集計セルを参照する偏差値の式
G2に平均点、G3に標準偏差が入っている場合、D2の偏差値の数式は短くできます。
=50+10*(C2-$G$2)/$G$3
計算の意味が見えやすく、数式を修正するときにも便利です。
G2とG3は下方向へコピーしても動かないよう、絶対参照にします。
集計値を別セルに置く方法は、数式の監査や共有に強い構成です。
なお、得点がすべて同じ場合、標準偏差は0になります。
この状態で偏差値を計算すると0による除算のエラーが発生します。
全員が同点なら相対的な差がないため、実務上は全員を偏差値50とするなど、別のルールを決める必要があります。
【操作のポイント】平均点と標準偏差のセルには、項目名を付けて表の近くに配置すると、後から数式を見た人にも計算根拠が伝わります。
偏差値の表示形式とエラーを防ぐ確認方法
続いては、算出した偏差値を読みやすく表示し、入力ミスを見つける方法を確認していきます。
| 氏名 | 得点 | 偏差値 | 判定例 |
|---|---|---|---|
| 田中 | 82 | 62.4 | 平均より高い |
| 佐藤 | 68 | 51.0 | 平均付近 |
| 鈴木 | 55 | 40.4 | 平均より低い |
小数点以下の桁数とユーザー定義表示
偏差値は小数第1位まで表示することが多いですが、用途によっては整数へ丸めても構いません。
セルを選択してホームタブの数値グループから小数点以下の表示桁数を調整すると、見た目だけを変更できます。
計算値そのものは保持されたままなので、表示を1桁にしても内部ではより細かな値で計算されています。
四捨五入した値そのものを使いたいなら、ROUND関数を数式に組み込みます。
=ROUND(50+10*(C2-AVERAGE($C$2:$C$11))/STDEV.P($C$2:$C$11),1)
この式では偏差値を小数第1位に丸めます。
表示形式の変更とROUND関数による丸めは別の処理です。
順位や上位割合を厳密に算出する表では、途中で丸めず元の数値を使い、最終表示だけ整えるほうが安全な場合があります。
数式エラーと不自然な偏差値の原因
偏差値のセルにDIV0エラーが表示される場合、標準偏差が0であることが主な原因です。
得点範囲に1人分しか数値がない場合も、計算目的によっては適切な標準偏差を求められません。
VALUEエラーが出る場合は、得点列に文字や記号が混ざっていないか確認します。
また、平均点の範囲と標準偏差の範囲が違っていると、数式自体は計算されても不自然な偏差値になります。
たとえば平均はC2からC11、標準偏差はC2からC10という状態では、同じ母集団を基準にしていません。
平均、標準偏差、個人得点は同じ集団のデータから求めることが基本です。
数式をクリックし、参照範囲が色付きの枠で正しく選択されるかを見ると、間違いを見つけやすくなります。
偏差値50を基準にした条件付き書式
偏差値の列を選び、ホームタブの条件付き書式を使うと、数値に応じてセルへ色を付けられます。
たとえば60以上を緑、40未満を赤に設定すると、成績の分布を表の中で直感的に把握できます。
条件付き書式の新しいルールから、指定の値を含むセルだけを書式設定するルールを選択しましょう。
偏差値が60以上、偏差値が40未満のようにルールを分けると、見分けやすい一覧になります。
【操作のポイント】色だけで評価を伝えず、偏差値の数値と判定列を併記すると、印刷時や白黒表示でも情報を確認できます。
偏差値は能力のすべてを決める数字ではありません。
特定のテストと集団の中での相対位置を表すものとして、得点、出席状況、学習内容などと合わせて扱う姿勢が大切です。
偏差値をグラフで見える化する方法
続いては、偏差値と得点の関係をExcelのグラフで分かりやすく見せる方法を確認していきます。
| 偏差値帯 | 人数 | 見方 |
|---|---|---|
| 60以上 | 4 | 平均より高い層 |
| 40以上60未満 | 12 | 平均付近の層 |
| 40未満 | 4 | 平均より低い層 |
偏差値の分布表を作成する手順
グラフを作る前に、偏差値帯ごとの人数を数える集計表を作成します。
偏差値がD2からD21にある場合、60以上の人数はCOUNTIF関数で求められます。
=COUNTIF($D$2:$D$21,”>=60″)
40以上60未満の人数は、COUNTIFS関数を使うと便利です。
=COUNTIFS($D$2:$D$21,”>=40″,$D$2:$D$21,”<60″)
このように区分ごとに人数を並べれば、縦棒グラフや円グラフの元になる表が完成します。
グラフは元データとなる集計表が整っているほど、修正もしやすくなります。
偏差値を1刻みや5刻みで分けたい場合も、境界値を別列に用意してCOUNTIFS関数で集計する方法が使えます。
縦棒グラフを挿入する操作
偏差値帯と人数の2列を選択し、挿入タブから縦棒グラフを選びます。
集合縦棒を選ぶと、各偏差値帯の人数を比較しやすいグラフになります。
グラフタイトルは偏差値の分布、テスト結果の偏差値分布など、内容がすぐ分かる名称に変更しましょう。
縦軸は人数、横軸は偏差値帯を示します。
人数が少ない表でも、データラベルを表示すると数値を読み取りやすくなります。
グラフの種類は、挿入後でもグラフのデザインタブから変更できます。
まずは縦棒グラフで人数の差を確認し、伝えたい内容に合う表示かを検討しましょう。
散布図と折れ線を使う場面
受験番号や氏名順に偏差値の変化を見たいときは、折れ線グラフを使えます。
ただし氏名順の折れ線は時間的な推移を示すものではないため、変化そのものを意味づけすぎない注意が必要です。
得点と偏差値の関係を確認するなら、散布図も選択肢になります。
横軸に得点、縦軸に偏差値を配置すると、得点が高いほど偏差値も高くなる関係が視覚化されます。
偏差値は同一集団内では得点の順序を保つため、基本的に右上がりの並びになります。
【操作のポイント】グラフの目的を先に決め、人数の比較なら縦棒、分布の説明ならヒストグラム風の集計、得点との関係なら散布図を選びましょう。
偏差値から上位何パーセントかを調べる方法
続いては、偏差値を基に上位何パーセント程度なのかを確認する方法を解説していきます。
| 偏差値 | 上位の目安 | 累積割合の目安 |
|---|---|---|
| 70 | 約2パーセント | 約98パーセント |
| 60 | 約16パーセント | 約84パーセント |
| 50 | 約50パーセント | 約50パーセント |
| 40 | 約84パーセント | 約16パーセント |
NORM.S.DIST関数で累積確率を求める方法
偏差値は平均50、標準偏差10になるよう変換された数値です。
正規分布を前提にすると、偏差値を標準化した値から累積確率を求められます。
偏差値がD2にある場合、偏差値以下にいる割合は次の式で計算できます。
=NORM.S.DIST((D2-50)/10,TRUE)
結果は0から1までの小数で返されます。
セルの表示形式をパーセントに変更すれば、84パーセントのように表示できます。
偏差値60では、およそ84パーセントとなります。
これは集団の約84パーセントが偏差値60以下、言い換えると本人は上位約16パーセントという意味です。
上位割合を知りたいときは、累積割合を100パーセントから引く考え方になります。
上位割合を直接表示する数式
偏差値D2から上位何パーセントかを求めるには、次の数式を使えます。
=1-NORM.S.DIST((D2-50)/10,TRUE)
結果のセルをパーセント表示にすると、偏差値60なら約15.9パーセントと表示されます。
表示を文章にしたい場合は、TEXT関数と組み合わせることも可能です。
=”上位約”&TEXT(1-NORM.S.DIST((D2-50)/10,TRUE),”0.0%”)
ただし、この方法は得点分布が正規分布に近いという前提を使った理論上の目安です。
人数が少ないクラスや、満点付近に得点が集中しているテストでは、実際の順位割合と差が出るかもしれません。
RANK.EQ関数で実際の順位を求める方法
実際のデータに対して何位なのかを知りたい場合は、RANK.EQ関数を使います。
得点がC2からC21にあり、C2の順位を求めるなら次の式です。
=RANK.EQ(C2,$C$2:$C$21,0)
最後の0は大きい数値を1位にする指定です。
順位を人数で割れば、実データにおけるおおよその上位割合も確認できます。
同点者がいる場合、RANK.EQ関数では同順位が表示され、その次の順位は飛びます。
実順位と偏差値からの上位目安は、用途が異なる指標です。
社内評価や学級内の順位を示すなら実データの順位を、模試のように大きな集団での相対的な位置を説明するなら偏差値と正規分布の目安を活用するとよいでしょう。
【操作のポイント】上位パーセントを資料に記載するときは、実順位による値か、正規分布を前提とした目安かを分けて表現しましょう。
まとめ エクセルで上位何パーセントと偏差値を求める方法
Excelで偏差値を求めるには、個人の得点から平均点を引き、標準偏差で割った値に10を掛けて50を足します。
基本の数式は、=50+10*(得点-平均点)/標準偏差です。
表の中で直接計算するならAVERAGE関数とSTDEV.P関数を使い、コピーする範囲は絶対参照に設定します。
平均点と標準偏差を別セルへ表示する構成にすると、数式の意味が分かりやすく、表の確認や修正も行いやすくなります。
クラスや受験者全員を対象に偏差値を出す場合は、STDEV.P関数を使うことが一般的です。
偏差値の表示は小数第1位程度に整え、エラーが出たときは得点範囲、文字列の混在、標準偏差が0になっていないかを確認しましょう。
分布表と縦棒グラフを作れば、偏差値帯ごとの人数を視覚的に伝えられます。
また、NORM.S.DIST関数を使うと正規分布を前提とした上位何パーセントかの目安も計算できます。
実際の順位を知りたい場合はRANK.EQ関数を併用する方法が適しています。
偏差値、順位、得点、グラフを目的に応じて使い分け、成績データを分かりやすい情報へ変えていきましょう。
