エクセルで偏差を求める完全ガイド！標準偏差や偏差値との違いを徹底解説

業務データの分析やテスト結果の集計で「偏差を出してほしい」と指示され、どの数式や関数を使えばよいか迷った経験を持つ方は少なくありません。日常会話では「偏差」「標準偏差」「偏差値」という言葉が混同されて使われがちですが、統計学およびエクセルにおける扱いは明確に分かれています。

言葉の定義を取り違えたまま計算を進めると、報告書の数値が根本から狂ってしまうリスクを抱えることになります。本稿では、エクセルを用いた偏差の正確な求め方をはじめ、標準偏差（STDEV系関数）や分散の算出法、偏差値の計算式、実務で役立つSTANDARDIZE関数の活用術まで、現場目線で分かりやすく解説します。

📌 【この記事の重要ポイントまとめ】

• 要点1：「偏差」は個別のデータから平均値を引いた差分であり、専用関数ではなく「セル - AVERAGE関数」の四則演算で求める。
• 要点2：「標準偏差」はデータのばらつき度合いを示し、全数データならSTDEV.P、サンプル調査ならSTDEV.Sを使い分けるのが鉄則。
• 要点3：「偏差値」は平均50・標準偏差10を基準に正規化した数値であり、STANDARDIZE関数を組み合わせることで簡潔に算出可能。

【基礎から整理】「偏差・標準偏差・偏差値」の違いと正しい概念

エクセルで作業を始める前に、まず押さえておくべきなのが「偏差」「標準偏差」「偏差値」という3つの概念の根本的な違いです。現場の実務では、依頼者が「ばらつき（標準偏差）」を見たいのか、「全体の中での相対位置（偏差値）」を知りたいのかを正確に汲み取る必要があります。

統計学上の定義は以下の通り極めてシンプルです。

• 偏差（Deviation）：各データが「平均値からどれだけ離れているか」を表す数値（個別データ − 平均値）。プラスにもマイナスにもなります。
• 分散（Variance）：偏差をそのまま足すと合計がゼロになってしまうため、偏差を2乗した値（偏差平方和）の平均をとったもの。
• 標準偏差（Standard Deviation）：分散の平方根をとったもの。データの単位を元の数値と揃えた「ばらつきの平均的な大きさ」を示します。
• 偏差値（Standard Score / T-score）：平均を50、標準偏差を10になるよう変換した数値。異なる母集団やテスト間でも相対的な実力を比較できます。

単に「偏差」を求めたい場合は、エクセルの特別な統計関数を探す必要はありません。基本の引き算と平均値を求めるAVERAGE関数の組み合わせだけで即座に算出できます。

【実務手順】エクセルで偏差・標準偏差を求める具体的な計算式

実際のワークシートを想定して、偏差と標準偏差を求める手順を具体的に確認していきます。ここでは、A列に氏名、B列（B2:B21）にテストの点数が入力されているケースを例に挙げます。

1. 単純な「偏差」を求める計算式

偏差は「対象セルの値 − 全体の平均値」で求められます。C列に偏差を出力する場合、C2セルに以下の数式を入力して下方向へオートフィルします。

=B2-AVERAGE($B$2:$B$21)

ここで極めて重要なのが、AVERAGE関数の参照範囲に「$」を付けた絶対参照（$B$2:$B$21）を指定する点です。絶対参照を忘れて相対参照のままコピーすると、計算対象の範囲が1行ずつズレてしまい、正しい平均値との差が取れなくなります。

2. 「偏差平方和」と「分散」の求め方

ばらつきの指標を段階的に算出する場合、偏差の2乗（偏差平方和）を求めるにはDEVSQ関数を使用します。

=DEVSQ(B2:B21)

また、分散を直接算出する場合は、対象データが全数か標本かに応じてVAR.P関数またはVAR.S関数を用います。

• データ全体（母集団）を対象とする場合：=VAR.P(B2:B21)
• 一部のサンプル（標本）から全体を推測する場合：=VAR.S(B2:B21)

3. 「標準偏差」を求める関数（STDEV.PとSTDEV.Sの使い分け）

実務で最も頻出するのが標準偏差の算出です。エクセルには複数のSTDEV系関数が存在しますが、現在推奨されているのは以下の2種類です。

社内全員の成績や全工場の検査データなど、手元にあるデータそのものが母集団全体である場合は「STDEV.P」を選択します。一方、アンケート調査のサンプリングや顧客の抽出データから母集団のばらつきを推定する場合は「STDEV.S」を使用するのが統計学上の正しいアプローチです。

【一覧比較】エクセル統計関数の特徴と使い分けガイド

エクセルには偏差やばらつきを分析するための統計関数が多数用意されています。目的やデータ構造に合致した関数を即座に選べるよう、主要な統計関数の違いと用途を一覧表にまとめました。

旧バージョンのエクセル互換関数として「STDEV」や「VAR」も残されていますが、これらは現在の「STDEV.S」「VAR.S」と同等の標本計算を行います。数式の意図を明確にするためにも、末尾に「.P」または「.S」が付いた新形式の関数を採用するのがベストプラクティスです。

【一発計算】偏差値の求め方とSTANDARDIZE関数の実践テクニック

実務の現場で「偏差を出して」と言われた際、実際には「偏差値（平均50を基準とした評価スコア）」を求められているケースが多々あります。エクセルで偏差値を算出する方法には「基本計算式」と「STANDARDIZE関数」の2パターンがあります。

1. 算術式による偏差値の計算

統計学上の偏差値は「（個人の得点 − 平均点）÷ 標準偏差 × 10 ＋ 50」で求められます。これをそのままエクセルの数式に落とし込むと以下のようになります（得点がB2、全データ範囲がB$2:B$21の場合）。

=(B2-AVERAGE($B$2:$B$21))/STDEV.P($B$2:$B$21)10+50

2. STANDARDIZE関数を活用したスマートな記述

エクセルのSTANDARDIZE関数は、指定した数値を平均0・標準偏差1に標準化（z値化）する関数です。これを利用することで、数式をすっきりと整理できます。

STANDARDIZE関数の書式はSTANDARDIZE(x, 平均, 標準偏差)です。あらかじめD1セルに平均値（=AVERAGE(B2:B21)）、D2セルに標準偏差（=STDEV.P(B2:B21)）を求めておく場合、偏差値の計算式は以下のように記述できます。

=STANDARDIZE(B2, $D$1, $D$2)10+50

STANDARDIZE関数が返す値に「10を掛けて50を足す」だけで、正規化された偏差値が一瞬で求められます。数式の可読性が高まるため、共同でシートを管理するビジネス現場で非常に有効なテクニックです。

【実態検証】現場で頻発する計算ミスとエラー対処法

エクセルでの偏差集計において、QAサイトや現場のサポートデスクに寄せられるトラブルの約8割は、共通する初歩的なミスに起因しています。エラーの発生原因と具体的な解決策を把握しておきましょう。

1. 「#DIV/0!」エラーの発生原因と対処

標準偏差や偏差値を計算した際、セルに「#DIV/0!（ゼロ除算エラー）」が表示される場合、主な要因は以下の2点です。

• データがすべて同一の値である：全メンバーが80点など均一の場合、標準偏差が「0」になり、割り算ができなくなります。
• STDEV.Sでデータが1件しかない：STDEV.Sは分母を「データ数 − 1」で計算するため、データが1件だけだと分母が0になります。

IFERROR関数を組み合わせて=IFERROR(STANDARDIZE(B2, $D$1, $D$2)*10+50, 50)のように記述すれば、標準偏差が0のときは基準値「50」を返す安全設計が可能です。

2. 「#VALUE!」エラーや計算結果のズレ

計算範囲の中に「欠席」「未提出」といった文字列や空白セルが含まれていると、計算結果に歪みが生じたり「#VALUE!」エラーを吐き出す原因になります。

AVERAGE関数やSTDEV.P関数は自動的に文字列セルを除外して計算しますが、単純な四則演算（-演算子）で文字列セルを参照すると「#VALUE!」が発生します。数値以外のステータスが存在する場合は、事前にFILTER関数で除外するか、IF関数で数値判定を挟む処理を徹底してください。

一般に知られていない盲点とネットの誤解

ネット上の情報や表計算の解説記事において、長年放置されている誤解や見落とされがちな盲点が存在します。

「母標準偏差（STDEV.P）」と「標本標準偏差（STDEV.S）」の取り違え

多くの入門サイトで「エクセルの標準偏差はSTDEVを使えばOK」と一括りに説明されがちですが、データ数が10〜30件程度の小規模サンプルにおいて両者を取り違えると、数値に数％〜十数％の明確な差異が発生します。

たとえばサンプルサイズが5件の場合、STDEV.P（分母5）とSTDEV.S（分母4）では標準偏差の計算結果が約1.12倍も異なります。全数集計の社内データにSTDEV.Sを適用してしまうと、ばらつきを過大評価することになり、人事評価や品質管理で不公平な判断を招く恐れがあります。

「偏差値50＝ちょうど真ん中の順位」という思い込み

偏差値50はあくまで「平均点と同じスコア」を意味する基準であり、「順位が中央（中央値：メディアン）」であることを保証するものではありません。データが左右対称の正規分布から大きく外れ、一部の極端な高得点者が平均を引き上げている場合、平均点以下の人数が全体の60〜70％を占める現象が起こります。「偏差値50未満だから下位半分にいる」と短絡的に解釈するのは統計学的な誤りです。

【プロの結論】おすすめできる人・慎重になるべき人の判断基準

エクセルの統計関数を活用したデータ分析を推進するにあたり、現場での適切な導入判断基準を提示します。

• 即座に導入をおすすめできるケース：
  • 定期テストや資格試験など、受験者全体の順位付けや相対評価を行いたい場合。
  • 製造ラインにおける製品寸法のばらつき（公差管理）を定量的に監視したい場合。
  • 異なる指標（売上金額と成約件数など）を標準化して総合評価スコアを作成したい場合。
• 利用に慎重になるべきケース：
  • データ数が極端に少ない（数件程度）環境下での無理な偏差値化（統計的信頼性が著しく低下します）。
  • 所得分布やWebサイト滞在時間のように、一部の外れ値によって極端に歪んだ「べき乗則」に従うデータの分析（中央値や四分位範囲を用いた分析が適しています）。

【エクセル 偏差 求め 方】に関するよくある質問（FAQ）

Q1：エクセルで「偏差」そのものを一発で出す専用関数は存在しないのですか？
A1：存在しません。偏差は「個別の値 − 平均値」という極めて単純な引き算であるため、専用関数は用意されていません。セル内で=B2-AVERAGE($B$2:$B$21)のようにAVERAGE関数を組み合わせて算出します。

Q2：古いエクセルにあった「STDEV」関数はもう使ってはいけないのでしょうか？
A2：後方互換性のため現在も動作しますが、新規のシート作成では非推奨です。「STDEV」は現在の「STDEV.S（標本用）」と同じ計算式であるため、全数データに対して誤用されるリスクがあります。意図を明確にするため「STDEV.P」または「STDEV.S」を使用してください。

Q3：偏差値がマイナスになったり100を超えたりすることはありますか？
A3：数学的に発生し得ます。極端な外れ値が存在するデータ群（例：平均20点・標準偏差5点の試験で1人だけ100点を取った場合など）では、偏差値が100を超えたり、逆にマイナスの値になったりすることがあります。異常値ではなく計算結果として正常です。

まとめ：正しい偏差の理解がデータ分析の精度を飛躍させる

エクセルにおける「偏差」の算出は、基本の四則演算から高度な標準偏差関数、偏差値の標準化まで、用途に応じた明確なステップが存在します。依頼内容が「単純な平均との差」なのか「集団のばらつき」なのか、あるいは「正規化された偏差値」なのかを見極めることが実務の第一歩です。

AVERAGE関数の絶対参照固定、全数（STDEV.P）と標本（STDEV.S）の適切な選択、そしてSTANDARDIZE関数のスマートな活用をマスターすることで、計算ミスを根絶し、信頼性の高いデータ分析レポートを迅速に作成できるようになります。日々の業務シートに正しい数式を組み込み、データ分析の精度を高めていきましょう。 （出典: エクセル 偏差 求め 方（Yahoo!ニュース））