【Excel】エクセルで偏差値を求める方法（平均値・標準偏差から計算）

エクセルでテスト結果やアンケートの点数を比較するとき、単純な得点だけでは集団内での位置を正確に判断しにくい場面があります。

平均点が異なるテスト同士でも比較しやすくする指標が偏差値です。

偏差値は個人の得点、平均値、標準偏差を使って計算でき、Excelの関数を組み合わせれば大量のデータにも対応できます。

AVERAGE関数とSTDEV.P関数、またはSTDEV.S関数を使えば、セルに数式を入力するだけで偏差値を求められます。

この記事では、1行目に見出しがある成績表を例に、平均値と標準偏差から偏差値を計算する方法、関数選び、結果の見方を順番に解説します。

計算ミスを防ぐ絶対参照や、オートフィルで数式をコピーする操作も確認していきましょう。

 

エクセルで偏差値を計算する数式

氏名	得点	偏差値
田中	82	60.6
佐藤	68	50.0
鈴木	55	40.2

それではまず、得点一覧から偏差値を直接算出する基本数式について解説していきます。

 

偏差値の基本式

偏差値は、得点が平均値からどの程度離れているかを、データのばらつきである標準偏差を基準にして表した値です。

平均と同じ得点なら偏差値は50になります。

平均より標準偏差1個分だけ高い得点なら偏差値は60、標準偏差1個分だけ低い得点なら偏差値は40になる仕組みです。

式の最初に50を加えるため、結果の中心は50になります。

さらに10を掛けることで、標準偏差1個分の差が偏差値10ポイント分として読み取れるようになります。

偏差値は点数そのものの優劣ではなく、同じ集団内での相対的な位置を示す数値です。

たとえば難しい試験で70点を取った人と、平均点が高い易しい試験で70点を取った人では、偏差値が異なる場合があります。

 

得点列から直接計算する入力例

ここでは、A列に氏名、B列に得点、C列に偏差値を入力する表を想定します。

1行目には氏名、得点、偏差値という見出しがあり、実際の点数はB2からB11までに入力されているものとします。

C2セルには、次の数式を入力します。

この数式では、B2が田中さんなど先頭行の得点です。

AVERAGE関数はB2からB11までの平均値を求め、STDEV.P関数は同じ範囲の標準偏差を求めます。

得点のセルであるB2だけは行ごとに変化する必要がありますが、平均値と標準偏差の対象範囲は固定しなければなりません。

そこで、範囲を$B$2:$B$11のように絶対参照にします。

絶対参照を使わずに数式をコピーすると、B2:B11がB3:B12のようにずれてしまい、偏差値の基準が行ごとに変わるため注意が必要です。

 

小数点の表示とオートフィル

C2セルに数式を入力した直後は、小数点以下が長く表示されることがあります。

偏差値は小数第1位程度まで表示すると、見やすさと比較のしやすさのバランスが取れます。

ホームタブの小数点以下の表示桁数を減らすボタンを使うか、セルの書式設定で表示形式を数値、小数点以下の桁数を1に設定しましょう。

次にC2セル右下の小さな四角であるフィルハンドルを下方向へドラッグすると、C3以降にも数式をコピーできます。

数式をコピーした後は、最終行まで得点範囲の絶対参照が変わっていないか数式バーで確認することが大切です。

 

平均値と標準偏差を別セルに置く計算表

項目	セル	数式または値
平均値	F2	=AVERAGE(B2:B11)
標準偏差	F3	=STDEV.P(B2:B11)
偏差値	C2	=50+10*(B2-$F$2)/$F$3

続いては、平均値と標準偏差を別セルに表示し、計算内容を確認しやすくする方法を確認していきます。

 

集計セルを作るメリット

偏差値の数式にAVERAGE関数とSTDEV関数を直接入れる方法は便利ですが、数式が長くなりやすい特徴があります。

平均値と標準偏差を表の右側や上部に別途表示すれば、どの集団を基準にしているのかを目で確認できます。

計算対象の人数や点数を変更したときも、平均値と標準偏差の変化をすぐ把握できるでしょう。

複数科目の偏差値を扱う表では、集計値を別セルに置く設計のほうが修正や監査をしやすくなります。

たとえばF1に平均値、G1に標準偏差という見出しを入れ、F2とG2に各関数を入力します。

氏名や得点の一覧と集計欄を少し離して配置すると、データ入力部分と計算条件を区別しやすくなります。

 

AVERAGE関数と標準偏差関数の設定

F2セルに、=AVERAGE(B2:B11)と入力すると対象者全員の平均点が表示されます。

G2セルには、母集団を対象とする場合、=STDEV.P(B2:B11)と入力します。

学校のクラス全員の点数がそろっており、そのクラスを評価対象全体として扱うなら、STDEV.P関数が自然です。

データ範囲に空白セルが含まれていても、AVERAGE関数と標準偏差関数は通常、空白を計算対象から除外します。

ただし、未受験者を0点として扱うのか、集計対象外とするのかで結果は変わります。

偏差値を出す前に、欠席者や入力漏れの取り扱いを統一しておきましょう。

 

参照セルを使った偏差値数式

F2セルに平均値、G2セルに標準偏差がある場合、C2セルの数式は短くなります。

B2は個人の得点なので、下へコピーするたびにB3、B4へ変化して問題ありません。

一方でF2とG2は、全員共通の平均値と標準偏差であり、コピー後も動かしてはいけないためドル記号を付けます。

数式の意味を分解すると、B2から平均値を引いて平均との差を出し、その差を標準偏差で割ってから10倍し、50を足しています。

偏差値が50より大きければ平均より上、50より小さければ平均より下です。

表示される数値だけでなく、数式バーで参照先を読み解けるようになると、ほかの統計計算にも応用できます。

 

STDEV.PとSTDEV.Sの使い分け

関数	対象	利用例
STDEV.P	母集団	クラス全員の成績
STDEV.S	標本	一部を抽出した調査

続いては、偏差値の精度に関わるSTDEV.P関数とSTDEV.S関数の違いを確認していきます。

 

母集団として計算するSTDEV.P関数

STDEV.P関数は、手元にあるデータ全体を母集団として標準偏差を計算する関数です。

たとえば、あるクラスの全員分のテスト結果がB2:B41にあり、そのクラス内で偏差値を比較したいなら、STDEV.P関数を使います。

学年全体の順位を基準にするなら、学年全員分の得点を範囲に含める必要があります。

偏差値は比較する集団の範囲で変化するため、クラス基準と学年基準の数値を混在させないことが重要です。

対象者が増減した場合は、数式の範囲も見直します。

Excelのテーブル機能を使えば、新しい行を追加したときに計算範囲を広げやすくなります。

 

標本として計算するSTDEV.S関数

STDEV.S関数は、より大きな集団の一部を抽出したデータを標本として扱うときに使います。

たとえば全国の受験者全体を推定するために、一部のアンケート回答者だけを分析するケースが該当します。

STDEV.S関数は標本標準偏差を計算するため、同じデータであればSTDEV.P関数より少し大きい値になる傾向があります。

標準偏差が大きくなると、平均との差を割る値も大きくなるため、偏差値は平均の50に近づきます。

通常のクラス内成績表のように対象者全員の点数がある場合は、STDEV.SではなくSTDEV.Pを選ぶのが基本です。

組織内の計算ルールが決まっている場合は、その基準を優先してください。

 

旧関数との互換性と確認方法

古いExcelファイルでは、STDEVという関数名が使われていることがあります。

STDEVは現在のExcelでは互換性維持のために残っていますが、標本標準偏差に相当する動作です。

新しく作るブックでは、目的が分かりやすいSTDEV.PまたはSTDEV.Sを選ぶとよいでしょう。

標準偏差が0の場合、全員の得点が完全に同じです。

この状態で偏差値の式を実行すると、0で割るためエラーになります。

全員同じ得点のときは、偏差値を一律50と表示するなど、事前に運用ルールを決める必要があります。

 

偏差値計算時のエラー対策と表示設定

表示	主な原因	確認事項
#DIV/0!	標準偏差が0	全員の得点が同じか確認
#VALUE!	文字列や不正な値	得点セルの入力形式を確認
偏差値が不自然	参照範囲のずれ	ドル記号と対象範囲を確認

続いては、偏差値を計算するときに起こりやすいエラーと、読みやすい表に整える設定を確認していきます。

 

ゼロ除算を避けるIFERROR関数

全員が同じ得点の場合、標準偏差は0になり、通常の偏差値数式では#DIV/0!エラーが表示されます。

成績の集計途中で人数が少ない場合にも、同様の状態になることがあります。

エラーをそのまま表示しないためには、IFERROR関数で計算式を包みます。

この式では、通常は偏差値を計算し、エラーが出たときだけ50を返します。

IFERROR関数は見た目を整えるための機能であり、なぜエラーになったのかを確認しなくてよいという意味ではありません。

全員同じ得点なのか、得点範囲が空白なのかを確認してから使用しましょう。

 

文字列の得点と空白セルの確認

見た目が数字でも、セルの値が文字列として保存されていると計算結果が期待どおりにならないことがあります。

数字がセルの左側に寄っている場合や、セル左上に緑色の三角形が表示される場合は、文字列になっている可能性があります。

該当セルを選択して警告アイコンから数値に変換するか、入力し直してください。

また、点数欄にメモや欠席などの文字を直接入力すると、関数が対象外として扱うことがあります。

得点列には数値だけを入力し、欠席や備考は別列で管理すると、平均値と標準偏差の集計が安定します。

0点を有効な成績として扱う場合は、空白セルと混同しないよう必ず0を入力します。

 

条件付き書式による結果の見やすさ

偏差値が多い表では、数値を眺めるだけでは高低をつかみにくいことがあります。

C列の偏差値範囲を選択し、ホームタブの条件付き書式からカラースケールを設定すると、相対的な位置を色で見分けやすくなります。

たとえば高い偏差値を濃い色、低い偏差値を淡い色にすれば、全体の分布をすばやく確認できます。

色分けは判断を補助するための機能です。

偏差値の高低だけで評価を固定せず、得点、設問内容、受験人数などもあわせて確認しましょう。

 

複数科目とランキングへの応用

氏名	国語偏差値	数学偏差値	総合偏差値
田中	58.4	62.1	60.3
佐藤	51.2	48.8	50.0

続いては、複数科目の集計や順位表示に偏差値を活用する方法を確認していきます。

 

科目ごとに異なる範囲を参照する方法

国語の得点がB列、数学の得点がC列、英語の得点がD列にある場合、それぞれの列で平均値と標準偏差を計算します。

国語偏差値をE列に出すなら、E2セルではB列だけを基準にした数式を使います。

数学偏差値では、参照列をC列へ変更します。

科目ごとに平均点やばらつきが異なるため、国語の平均値と数学の標準偏差を混ぜないようにしましょう。

偏差値の比較は同一科目、または同じ方法で標準化した数値同士で行うと意味を持ちます。

 

総合偏差値を扱う際の注意点

複数科目の総合的な位置を見たいときは、まず各科目の偏差値を計算し、それらの平均を求める方法があります。

たとえば国語偏差値がE列、数学偏差値がF列、英語偏差値がG列なら、H2セルに=AVERAGE(E2:G2)と入力できます。

ただし、科目ごとの配点や重要度が異なる場合、単純平均では意図に合わないことがあります。

数学を2倍の重みで評価するなら、科目偏差値の加重平均を使う設計も考えられます。

総合偏差値という名称でも、どの科目をどの比率で含めたかを表の近くに記載しておくと、後から見返す人にも親切です。

 

RANK.EQ関数との併用

偏差値と順位を並べると、数値の位置と順位の両方を確認できます。

偏差値がC2:C31にある場合、D2セルに=RANK.EQ(C2,$C$2:$C$31,0)と入力すると、偏差値が高い順の順位を表示できます。

第3引数の0は降順を意味し、最も高い偏差値が1位になります。

同じ偏差値がある場合は同順位になり、次の順位が飛ぶことがあります。

順位は人数や同点者の影響を受けやすいため、偏差値と一緒に表示すると成績の解釈が偏りにくくなります。

 

まとめ エクセルで偏差値を求める方法（標準偏差・平均値から計算）

エクセルで偏差値を求めるには、個人の得点から平均値を引き、その値を標準偏差で割って10倍し、50を加えます。

基本となる数式は、=50+10*(得点-平均値)/標準偏差です。

得点一覧を基準に直接計算する場合は、AVERAGE関数とSTDEV.P関数の対象範囲をドル記号で固定することが最重要です。

平均値と標準偏差を別セルに置く方法なら、計算の根拠を確認しやすく、複数科目の表にも展開しやすくなります。

クラス全員のように集団全体のデータがある場合はSTDEV.P関数を使い、一部の標本から全体を推定する場合はSTDEV.S関数を検討します。

エラーが出たときは、標準偏差が0ではないか、得点が数値として入力されているか、参照範囲がずれていないかを確認しましょう。

偏差値を正しく使えば、異なる平均点のテストでも成績の位置を比較しやすくなります。