エクセルで偏差値を計算したいけれど、どの関数を使えばいいかわからない、という経験はありませんか？
偏差値はテストの成績や営業データの分析など、さまざまな場面で活用される指標です。
エクセルでは関数を組み合わせることで、簡単に偏差値を求めることができます。
偏差値の計算式は以下のとおりです。
偏差値 ＝ （個人の得点 － 平均点） ÷ 標準偏差 × 10 ＋ 50
ポイントは以下の3つです。
・AVERAGE関数で平均を求める
・STDEV.S関数で標準偏差を求める
・上記の式をセルに組み合わせて入力する
それでは詳しく見ていきましょう。
偏差値とは何か・エクセルで計算する前の基礎知識
偏差値とは、ある集団の中で自分がどの位置にいるかを示す指標です。
平均が50になるよう設計されており、標準偏差を基準に上下のばらつきを数値化します。
たとえばテストの平均点が60点で、自分が80点だったとしても、全員が高得点を取っていればその80点の価値は相対的に低くなります。
偏差値はそのような「集団の中での相対的な位置」を正確に表現するために使われます。
ビジネスの現場でも、売上データや顧客満足度スコアの分布を分析する際に偏差値の考え方が役立ちます。
偏差値の計算式を理解する
偏差値の計算式はシンプルです。
偏差値 ＝ （個人の値 － 平均値） ÷ 標準偏差 × 10 ＋ 50という式で表されます。
この式の意味を分解すると、まず「個人の値 － 平均値」で平均からのずれ（偏差）を求めます。
次にそのずれを標準偏差で割ることで、ばらつきを基準とした相対的な差を算出します。
最後に10を掛けて50を足すのは、偏差値を見やすいスケール（平均50・標準偏差10）に変換するためです。
エクセルではこの計算式をそのまま数式として入力することができます。
標準偏差とAVERAGE関数・STDEV.S関数の役割
エクセルで偏差値を計算するには、AVERAGE関数とSTDEV.S関数の2つが中心となります。
AVERAGE関数はデータの平均値を求める関数で、書式は「=AVERAGE(範囲)」です。
STDEV.S関数は標本の標準偏差を求める関数で、書式は「=STDEV.S(範囲)」となります。
STDEV.SはサンプルデータからなるSTDEV.Sと、母集団全体を対象とするSTDEV.Pの2種類がありますが、通常は標本データを扱うSTDEV.Sを使います。
テストの点数や営業成績など、限られたデータから全体を推測する場合はSTDEV.Sが適切です。
サンプルデータで構成を確認する
以下のようなサンプルデータを使って説明します。
1行目にはヘッダーがあり、A列に商品名、B列にスコアが入力されているとします。
C2セルに偏差値の数式を入力し、C7まで下にコピーする形で使います。
【操作のポイント】偏差値の計算では、AVERAGE関数とSTDEV.S関数の参照範囲を絶対参照（$B$2:$B$7）にするのがポイントです。絶対参照にしないと、数式をコピーした際に参照範囲がずれてしまい、正しい偏差値が計算されません。
エクセルで偏差値を計算する数式の作り方
それでは実際にエクセルで偏差値を求める数式を作っていきましょう。
先ほどのサンプルデータを使い、C2セルに数式を入力します。
基本の数式を入力する
C2セルに以下の数式を入力します。
=（B2-AVERAGE($B$2:$B$7)）/STDEV.S($B$2:$B$7)*10+50
オートフィルで一括処理しましょう。
この数式の構造を順番に解説します。
まず「B2」は個人のスコアです。
「AVERAGE($B$2:$B$7)」でB2からB7の全スコアの平均を求めます。
「B2-AVERAGE($B$2:$B$7)」でその個人のスコアが平均からどれだけ離れているか（偏差）を計算します。
「STDEV.S($B$2:$B$7)」でB2からB7の標準偏差を求め、偏差をこの値で割ることでデータ全体のばらつきを基準にした相対値を出します。
最後に「×10＋50」を計算することで、見慣れた偏差値のスケールに変換されます。
AVERAGE関数とSTDEV.S関数の引数は必ず絶対参照にすることを忘れないようにしましょう。
数式を下のセルにコピーする
C2セルに数式を入力したら、セルを選択した状態でセルの右下角にある小さな四角（フィルハンドル）をC7までドラッグします。
または、C2セルをコピーしてC3からC7を選択し、貼り付けることでも同じ結果が得られます。
コピー後、各行に正しい偏差値が表示されれば成功です。
平均スコアに近い商品の偏差値は50付近になり、高スコアの商品は50を超え、低スコアの商品は50を下回ります。
【操作のポイント】数式をコピーする際、絶対参照（$）が正しく設定されているかを必ず確認しましょう。コピー後にAVERAGEやSTDEV.Sの参照範囲がずれている場合は、元の数式の参照設定を見直してください。
計算結果の確認と小数点の表示調整
偏差値の計算が完了したら、表示形式を整えると見やすくなります。
デフォルトでは小数点以下に多くの桁数が表示される場合がありますが、偏差値は通常、小数点以下1桁や整数で表示されることが多いです。
小数点以下の桁数を揃える
偏差値が表示されているC列のセルを選択し、「ホーム」タブの「数値」グループにある小数点桁数の調整ボタンを使います。
「小数点以下の桁数を減らす」ボタンを押すことで、表示桁数を1桁や整数に揃えることができます。
また、セルを右クリックして「セルの書式設定」を開き、「数値」カテゴリで小数点以下の桁数を指定する方法もあります。
表示桁数を変えても、セル内部の計算値は変わりません。あくまで見た目の調整であることを覚えておきましょう。
ROUND関数で四捨五入する方法
表示だけでなく、実際の値自体を丸めたい場合はROUND関数を組み合わせます。
先ほどの偏差値計算式をROUND関数で囲む形で入力します。
=ROUND(（B2-AVERAGE($B$2:$B$7)）/STDEV.S($B$2:$B$7)*10+50, 1)
ROUND関数の第2引数「1」は小数点以下1桁に四捨五入することを意味します。
整数にしたい場合は「0」を指定します。
このようにROUND関数を使うと、計算値そのものが丸められるため、後続の集計などにも影響を与えます。
【操作のポイント】表示桁数の調整（セルの書式設定）は見た目だけの変更です。計算に使う値自体を丸めたい場合は、ROUND関数を使って数式の中で処理しましょう。
偏差値計算でよくあるエラーと対処法
偏差値の計算式を入力したときに、うまく計算できないケースがあります。
代表的なエラーと対処法を確認しておきましょう。
DIV/0！エラーが表示される場合
「#DIV/0!」エラーは、0で割り算しようとしたときに発生します。
偏差値の計算で#DIV/0!が出る主な原因は、データが1件しかなく標準偏差が0になってしまうケースです。
標準偏差が0の場合、全員が同じスコアであることを意味し、偏差値の計算自体が成立しません。
この場合はデータが2件以上あることを確認するか、IFERROR関数でエラーを非表示にする対処ができます。
=IFERROR(（B2-AVERAGE($B$2:$B$7)）/STDEV.S($B$2:$B$7)*10+50, “－”)
この数式では、エラーが発生した場合に「－」と表示されます。
参照範囲がずれている場合
偏差値の数式をコピーしたあと、計算結果がおかしいと感じたら参照範囲を確認しましょう。
AVERAGE関数やSTDEV.S関数の引数に絶対参照（$）が付いていないと、コピー時に参照範囲が1行ずつずれてしまいます。
たとえばC3セルにコピーされた数式の中でAVERAGEが「$B$3:$B$8」になっていたら、絶対参照が正しく設定されていない証拠です。
元のC2セルの数式を修正し、再度コピーし直しましょう。
【操作のポイント】エラーが出た場合はまず数式バーで参照範囲を確認しましょう。絶対参照の「$」が抜けていることが多いため、F4キーを使って参照形式を切り替えると素早く修正できます。
まとめ エクセルで偏差値の出し方（関数を組み合わせた計算式・求め方）
エクセルで偏差値を求める方法をまとめます。
基本の計算式は「=（個人の値－AVERAGE(範囲)）/STDEV.S(範囲)*10+50」です。
AVERAGE関数で平均を、STDEV.S関数で標準偏差を求め、偏差値の公式に当てはめるだけでシンプルに計算できます。
数式をコピーする際は絶対参照を忘れずに設定することが最大のポイントです。
表示を整えたい場合はROUND関数やセルの書式設定を活用しましょう。
エラーが出た場合はIFERROR関数で対処できます。
偏差値の考え方をエクセルで活用することで、データの相対的な分析がぐっとやりやすくなります。
ぜひ今回の方法を参考に、業務や学習データの分析に役立ててみてください。
コメント