【Excel】エクセルで偏差値を出す方法（平均・標準偏差・正答率・合格率）
エクセルでテスト結果を集計するとき、単純な点数だけでは受験者ごとの位置を比較しにくい場面があります。
そこで役立つ指標が偏差値です。
偏差値は平均点を基準にして、得点が集団の中でどの程度高いか、または低いかを数値化したものです。
ExcelならAVERAGE関数、STDEV.P関数、STDEV.S関数を組み合わせることで、複数人分の偏差値を効率よく算出できます。
さらに正答率、合格率、順位を併記すれば、試験結果の分析表としても活用しやすくなります。
偏差値の基本式は、得点から平均点を引き、標準偏差で割った値に10を掛けて50を足す形です。
偏差値 ＝ 50 ＋ 10 × （個人の得点 － 平均点）÷ 標準偏差
この記事では、1行目に見出しがあるテスト結果表を例に、数式の入力方法から正答率と合格率の確認方法までを解説します。
元データを残したまま計算列を追加することが、ミスを減らす基本です。
エクセルで偏差値を計算する数式
それではまず、得点一覧から偏差値を求める基本の数式について解説していきます。
| 氏名 | 得点 | 平均点 | 標準偏差 | 偏差値 |
|---|---|---|---|---|
| 田中 | 82 | 68.4 | 12.5 | 60.9 |
| 佐藤 | 71 | 68.4 | 12.5 | 52.1 |
| 鈴木 | 55 | 68.4 | 12.5 | 39.3 |
ここではA列を氏名、B列を得点、C列を平均点、D列を標準偏差、E列を偏差値として扱います。
偏差値の基本構造
偏差値は、平均点との差を標準偏差で調整して表す数値です。
得点が平均点と同じなら、偏差値は50になります。
標準偏差1個分だけ平均より高い得点なら偏差値は60前後、1個分低い得点なら40前後になる考え方です。
個人の得点が82点、平均点が68.4点、標準偏差が12.5の場合です。
50 ＋ 10 × （82 － 68.4）÷ 12.5
計算結果は60.88となり、小数第1位で表示すると60.9です。
偏差値は点数そのものではなく、同じ集団内での相対的な位置を示します。
そのため、平均点や受験者構成が異なる試験同士では、偏差値だけを単純比較しない配慮も必要です。
セルに入力する数式
続いては、実際にE2セルへ入力する数式を確認していきます。
=50+10*(B2-AVERAGE($B$2:$B$11))/STDEV.P($B$2:$B$11)
この数式ではB2が田中さんの得点、B2からB11が全受験者の得点範囲です。
AVERAGE関数で平均点を求め、STDEV.P関数で標準偏差を求めています。
$記号を付けた絶対参照により、数式を下方向へコピーしても集計範囲が動きません。
個人の得点であるB2だけは相対参照のままにすることが重要です。
この設定なら、E3ではB3、E4ではB4を自動参照しながら、平均と標準偏差の範囲は固定されます。
小数点と表示形式
続いては、計算した偏差値を見やすく表示する設定を確認していきます。
偏差値は小数第1位まで表示すると、差が把握しやすくなります。
E列を選択して、ホームタブの小数点以下の表示桁数を増やすボタンまたは減らすボタンを使用しましょう。
一般的な成績表では60.9のように小数第1位まで、簡潔な一覧では61のように整数で表示します。
=ROUND(50+10*(B2-AVERAGE($B$2:$B$11))/STDEV.P($B$2:$B$11),1)
ROUND関数を加えると、数式そのものを小数第1位へ丸められます。
画面上の表示形式だけを変える方法と、数値そのものを丸める方法は用途に応じて使い分けましょう。
【操作のポイント】偏差値の数式は最初の1セルで正しく完成させてから、フィルハンドルで下へコピーします。
平均点と標準偏差の算出
続いては、偏差値計算の土台になる平均点と標準偏差について確認していきます。
| セル | 項目 | 数式 | 結果例 |
|---|---|---|---|
| C2 | 平均点 | =AVERAGE($B$2:$B$11) | 68.4 |
| D2 | 標準偏差 | =STDEV.P($B$2:$B$11) | 12.5 |
| E2 | 偏差値 | =50+10*(B2-C2)/D2 | 60.9 |
平均点と標準偏差を別セルへ表示しておくと、数式の意味を確認しやすく、修正作業にも対応しやすくなります。
AVERAGE関数による平均点
続いては、平均点を求めるAVERAGE関数について確認していきます。
C2セルには、=AVERAGE($B$2:$B$11) と入力します。
AVERAGE関数は指定範囲の数値を合計し、データ件数で割った平均値を返します。
空白セルは原則として計算対象から除かれますが、0点は数値として平均に含まれます。
欠席者を空白にするか0点にするかで平均点は大きく変わるため、集計ルールを先に決めることが大切です。
追試者や未受験者を含める場合は、成績評価の方針と一致した入力方法を選びましょう。
STDEV.P関数とSTDEV.S関数
続いては、標準偏差を求める関数の違いを確認していきます。
クラス全員や受験者全員の得点を対象に偏差値を出す場合は、STDEV.P関数を使うケースが基本です。
=STDEV.P($B$2:$B$11)
STDEV.P関数は、指定したデータ全体を母集団として扱う標準偏差です。
一方、STDEV.S関数は、より大きな集団から抽出した標本データとして扱うときに用います。
同じ得点範囲を使うなら、平均・標準偏差・偏差値の計算方針を統一することが欠かせません。
学校内の試験結果を全員分集計するなら、STDEV.P関数を選ぶと理解しやすいでしょう。
補助セルを使った偏差値
続いては、平均点と標準偏差を補助セルに置く計算方法を確認していきます。
E2セルには、=50+10*(B2-$C$2)/$D$2 と入力します。
この方法では、C2に平均点、D2に標準偏差が既に計算されている状態を前提とします。
数式が短くなり、どの要素を使っているかを表上で確認しやすいことが利点です。
平均点と標準偏差を各行に表示したくない場合は、表の右側や別シートに集計欄を作る方法もあります。
【操作のポイント】補助セルを使う場合は、平均点と標準偏差のセル番号にも$を付けて絶対参照にします。
正答率と合格率の計算
続いては、偏差値と合わせて確認したい正答率と合格率の計算方法を解説していきます。
| 氏名 | 得点 | 満点 | 正答率 | 判定 |
|---|---|---|---|---|
| 田中 | 82 | 100 | 82.0% | 合格 |
| 佐藤 | 71 | 100 | 71.0% | 合格 |
| 鈴木 | 55 | 100 | 55.0% | 不合格 |
正答率は個人の達成度を示し、合格率は受験者全体の達成状況を示す指標です。
正答率を求める数式
続いては、得点を満点で割る正答率の数式を確認していきます。
たとえばB列が得点、C列が満点、D列が正答率なら、D2セルへ =B2/C2 と入力します。
計算後にD列をパーセントスタイルへ変更すると、0.82が82.0パーセントとして表示されます。
=B2/C2
満点が全員同じでも、満点をセルに記録しておくと試験形式の変更に対応しやすくなります。
満点が100点ではないテストでも、同じ計算式で正答率を比較できる点が便利です。
IF関数による合否判定
続いては、基準点を使って合格と不合格を判定する方法を確認していきます。
合格基準を60点とし、E列に判定を表示するなら、E2セルへ =IF(B2>=60,”合格”,”不合格”) と入力します。
=IF(B2>=60,”合格”,”不合格”)
基準点を別セルに置くなら、たとえばH2セルに60を入力し、=IF(B2>=$H$2,”合格”,”不合格”) とします。
基準点を別セルにする設計は、合格ラインの変更が多い場合に特に有効です。
点数ではなく正答率で判定する場合は、=IF(D2>=0.6,”合格”,”不合格”) のように指定します。
COUNTIF関数による合格率
続いては、クラス全体の合格率を求める方法を確認していきます。
判定結果がE2からE11にある場合、合格人数は =COUNTIF(E2:E11,”合格”) で求められます。
合格率は、=COUNTIF(E2:E11,”合格”)/COUNTA(E2:E11) と入力してパーセント表示にします。
=COUNTIF($E$2:$E$11,”合格”)/COUNTA($E$2:$E$11)
COUNTA関数は空白以外のセルを数えるため、判定欄に結果が入っている人数を分母にできます。
未受験者を合格率の分母へ含めるかどうかは、集計目的に合わせて判断しましょう。
【操作のポイント】正答率はパーセント表示、合格率は合格人数と併記すると集計結果を読み取りやすくなります。
偏差値の数式コピーとエラー対策
続いては、偏差値の数式を複数行へコピーするときの操作とエラー対策を確認していきます。
| 得点 | 偏差値の数式 | 確認点 |
|---|---|---|
| B2 | =50+10*(B2-$C$2)/$D$2 | B2のみ相対参照 |
| B3 | =50+10*(B3-$C$2)/$D$2 | 平均と標準偏差は固定 |
数式をコピーした後は、最初と最後の行を確認して、参照先が意図どおりかを確かめましょう。
フィルハンドルによるコピー
続いては、数式を下方向へ反映するフィルハンドルの操作を確認していきます。
偏差値を入力したE2セルを選択すると、右下に小さな四角が表示されます。
この四角をフィルハンドルと呼び、下へドラッグすると数式をコピーできます。
隣接するB列に連続データがある場合は、フィルハンドルをダブルクリックして最終行まで自動入力する方法も便利です。
コピー後に平均点の範囲がB3からB12へずれていないかを数式バーで確認しましょう。
DIVゼロエラーの原因
続いては、偏差値で表示されることがあるDIVゼロエラーを確認していきます。
DIVゼロエラーは、標準偏差が0で割り算ができないときに発生します。
全員の得点が同じ場合や、得点データが1件しかない場合は標準偏差が0になるため、通常の偏差値は計算できません。
=IF($D$2=0,”計算不可”,50+10*(B2-$C$2)/$D$2)
IF関数を使うと、標準偏差が0のときだけ計算不可と表示できます。
エラーを隠すだけではなく、データ件数や得点の入力状態を確認することが根本的な対策です。
欠席者と空白セルの扱い
続いては、欠席者や未入力の得点を含む表での注意点を確認していきます。
空白セルを含む範囲でAVERAGE関数とSTDEV.P関数を使うと、空白は通常計算から除外されます。
しかし、欠席を0点として入力すると平均点と標準偏差に反映されます。
評価対象外の欠席者は空白、0点評価の受験者は0とするなど、ルールを表全体で統一しましょう。
個人の得点セルが空白のときに偏差値も空白にしたいなら、=IF(B2=””,””,50+10*(B2-$C$2)/$D$2) と設定できます。
【操作のポイント】偏差値の異常値を見つけたときは、数式より先に得点範囲、空白、0点の扱いを確認します。
順位と条件付き書式による成績分析
続いては、偏差値をより見やすい成績表へ仕上げる順位と条件付き書式を確認していきます。
| 氏名 | 得点 | 偏差値 | 順位 | 評価 |
|---|---|---|---|---|
| 田中 | 82 | 60.9 | 1 | 上位 |
| 佐藤 | 71 | 52.1 | 2 | 標準 |
| 鈴木 | 55 | 39.3 | 3 | 要確認 |
偏差値だけでなく順位や評価を加えると、集計表を見る人が結果を理解しやすくなります。
RANK.EQ関数による順位
続いては、得点または偏差値から順位を出す方法を確認していきます。
得点がB2からB11にある場合、順位列には =RANK.EQ(B2,$B$2:$B$11,0) と入力します。
最後の0は大きい数値を1位にする降順指定です。
同点者には同じ順位が付き、その次の順位は飛ぶ形式になります。
偏差値の順位と得点の順位は、同じ集団かつ同じ計算条件なら基本的に一致します。
偏差値に応じた評価表示
続いては、IF関数を使った簡易的な評価表示を確認していきます。
偏差値がE列にある場合、F2セルには =IF(E2>=60,”上位”,IF(E2>=50,”標準”,”要確認”)) と入力できます。
この評価は指導や振り返りの補助として使うものであり、個人を一面的に判断する目的ではありません。
教科や試験の難易度、受験者数も併せて確認する姿勢が重要です。
=IF(E2>=60,”上位”,IF(E2>=50,”標準”,”要確認”))
条件付き書式による視認性
続いては、偏差値の高低を色で見分ける条件付き書式を確認していきます。
偏差値の列を選択し、ホームタブの条件付き書式からカラースケールを選ぶと、数値の大小に応じて色を付けられます。
高い偏差値と低い偏差値を直感的に見分けられるため、人数が多い表で役立ちます。
色だけで結論を決めず、数値と得点、正答率を合わせて読むことを意識しましょう。
【操作のポイント】順位、偏差値、正答率を横並びにすると、相対評価と絶対評価の両方を確認できます。
まとめ エクセルで偏差値を出す方法（合格率・正答率・標準偏差・平均）
エクセルで偏差値を出すには、まずAVERAGE関数で平均点を求め、STDEV.P関数で標準偏差を求めます。
そのうえで、=50+10*(個人の得点-平均点)/標準偏差 の形に当てはめれば、受験者ごとの偏差値を計算できます。
全員分を対象にした試験結果では、STDEV.P関数を使う方法が基本です。
平均点と標準偏差を補助セルへ置き、絶対参照を使って数式をコピーすると、成績表を安定して作成できます。
正答率は得点を満点で割り、合格率はCOUNTIF関数で合格者数を集計して求めます。
さらに順位、条件付き書式、評価欄を加えることで、単なる点数表を分析しやすい一覧へ発展させられます。
欠席者の扱い、0点の扱い、合格基準は、計算前に決めておくと集計の信頼性が高まります。
まずは少人数のサンプルデータで数式を試し、結果を確認してから実際の成績表へ適用していきましょう。
