【Excel】エクセルで偏差を求める方法（平均値との差を計算）

エクセルで複数の数値を比較するとき、平均値からどれだけ離れているかを確認したい場面があります。

この平均との差が偏差であり、テストの得点、売上実績、作業時間、アンケート結果などを分析する基本になる数値です。

偏差は難しい関数を使わなくても、平均値を求めて各データから引くだけで計算できます。

ただし、数式を下方向へコピーするときは、平均値のセルを固定する絶対参照が重要です。

偏差はデータの位置関係を把握するための数値であり、平均との差を符号付きで表せる点が大きな特徴です。

この記事では、AVERAGE関数を使った基本の求め方から、絶対参照、偏差値や標準偏差との違いまで詳しく解説していきます。

 

エクセルで偏差を求める数式入力

A	B	C
1	氏名	得点
2	田中	72
3	佐藤	85
4	鈴木	63

それではまず、個々の値から平均値を引く基本的な偏差の求め方について解説していきます。

結論として、平均値を別セルに計算し、得点セルからその平均値セルを引く数式を入力すれば偏差を求められます。

 

平均値をAVERAGE関数で求める操作

偏差を出す前に、比較の基準となる平均値を計算します。

たとえば、B列のB2からB6に5人分の得点が入力されている場合は、空いているB8セルを選択します。

数式バーまたはセルへAVERAGE関数を入力しましょう。

この数式では、B2からB6までの数値だけを合計し、件数で割った平均値を返します。

文字列や空白セルは基本的に平均の計算対象にならないため、氏名列を範囲に含めないよう注意が必要です。

サンプルの得点が72、85、63、78、52である場合、平均値は70になります。

平均値が70と分かれば、72点の人は平均より2点高く、63点の人は平均より7点低いと判断できます。

なお、平均値を同じ列の最下部に置くと集計範囲へ平均値自身を含めてしまうことがあります。

データ範囲と平均値のセルは、少なくとも1行以上離して配置すると見やすく安全です。

 

個々の値から平均値を引く数式

続いては、平均値との差を計算する偏差の数式を確認していきます。

平均値がB8セルにあり、最初の得点がB2セルにある場合、C2セルへ=B2-$B$8と入力します。

この式のB2は個々の得点を示す相対参照です。

一方で、$B$8は平均値の位置を固定する絶対参照になります。

結果がプラスなら平均より高い値、マイナスなら平均より低い値を意味します。

偏差がゼロの場合は、そのデータが平均値と完全に一致している状態です。

偏差の符号を消さずに残すことで、平均より上か下かを一目で判断できます。

絶対値だけを見たい場合はABS関数を使えますが、上昇と下降の方向を分析するなら符号付きの偏差を残すほうが便利です。

 

数式をオートフィルでコピーする手順

続いては、先頭セルに作成した偏差の数式を下の行へ反映する操作を確認していきます。

C2セルに数式を入力したら、セル右下に表示される小さな四角形であるフィルハンドルへマウスポインターを合わせます。

十字の形に変わったら、データの最終行までドラッグしてください。

これによりC3では=B3-$B$8、C4では=B4-$B$8のように、個々の得点セルだけが自動で変化します。

平均値セルの$B$8は固定されるため、すべての行で同じ基準の平均値と比較できます。

数式をコピーする前に絶対参照を設定しておくことが、偏差計算で最も重要な操作です。

キーボード操作では、数式入力中にB8の参照部分を選択してF4キーを押すと、$B$8へ切り替えられます。

ノートパソコンではFnキーとの同時押しが必要な場合もあります。

 

偏差の計算結果とプラスマイナスの見方

A	B	C
1	得点	偏差
2	72	2
3	85	15
4	63	-7

続いては、偏差の数値が示す意味と、計算結果を比較するときの考え方を確認していきます。

偏差は単なる差ではなく、平均を中心にして各データがどの位置にあるかを示す分析用の値です。

 

プラスの偏差が示す平均より高い状態

偏差がプラスになった場合、その値は平均値を上回っています。

平均70点に対して85点なら、偏差は15です。

この15という数値は、平均より15点高いことをそのまま表します。

プラスの偏差が大きいほど、対象の数値は平均から上方向へ離れています。

売上データなら平均を上回る店舗、作業時間なら平均より長くかかった工程を見つける手がかりになります。

ただし、偏差が大きいことが常に良いとは限りません。

たとえば不良件数や処理時間では、平均を大きく上回る数値は改善対象になる可能性があります。

 

マイナスの偏差が示す平均より低い状態

続いては、マイナスの偏差の見方を確認していきます。

平均70点に対して63点なら、偏差は-7です。

負の記号は計算ミスではなく、平均より7点低いという方向を示しています。

偏差が-18のデータは、平均より18だけ低い位置にあります。

偏差のマイナス記号は、平均との差の向きを表す大切な情報です。

平均との差の大きさだけを比較するためにマイナスを消すと、平均より高いのか低いのかという判断材料が失われます。

 

偏差の合計がゼロになる理由

続いては、偏差の合計に見られる特徴を確認していきます。

同じデータ範囲から正しく平均値を求めた場合、各偏差を合計すると基本的にゼロになります。

これは平均値が、全データの差をつり合わせる位置として計算されるためです。

偏差列の合計にこの数式を入力すると、通常は0と表示されます。

小数を含む計算では、表示形式や丸めの影響でごく小さな誤差が現れることもあります。

偏差の合計が大きくゼロから離れる場合は、平均の範囲または参照セルのずれを確認しましょう。

 

AVERAGE関数と絶対参照の設定

A	B	C
1	得点	偏差の数式
2	72	=B2-$B$8
8	70	平均値

続いては、偏差の数式を正確にコピーするために必要なAVERAGE関数と絶対参照について確認していきます。

データ件数が多いほど、計算の考え方を分けて表へ配置すると修正と確認がしやすくなります。

 

平均値セルを別に置くメリット

平均値を別セルへ表示する方法には、計算式を短く保てる利点があります。

偏差の各セルへAVERAGE関数を直接書くこともできますが、同じ範囲を何度も計算する構成になります。

平均値をB8へ一度だけ求めておけば、偏差列ではそのセルを参照するだけです。

平均値を独立したセルに置くと、計算の基準が表の中で見える化されます。

データ範囲を変更するときも、AVERAGE関数の範囲をB8で修正すれば偏差列へ反映されます。

平均値の表示セルには、背景色や太字を設定して基準値だと分かるようにするとよいでしょう。

 

相対参照と絶対参照の違い

続いては、参照形式が数式コピーへ与える影響を確認していきます。

相対参照は、数式をコピーした位置に応じて参照先が変化する指定です。

C2の=B2-$B$8をC3へコピーした場合、B2はB3へ変わります。

これに対し、$B$8はコピー先が変わっても常にB8を参照します。

偏差の計算では、個々の数値は相対参照、平均値は絶対参照にする組み合わせが基本です。

$を付け忘れると、下の行ほど平均値の参照先がずれて正しい偏差を計算できません。

 

平均値を数式内で直接計算する方法

続いては、平均値セルを作らずに偏差を求める書き方を確認していきます。

この数式は、B2の得点からB2からB6までの平均値を引きます。

AVERAGE関数内の範囲を$B$2:$B$6のように固定しておけば、下へコピーしても平均対象は変わりません。

表が小さい場合には便利ですが、数式が長くなり、平均値の確認もセル上では行いにくくなります。

初心者が偏差を扱う場合は、平均値を別セルに表示する方法のほうが計算過程を確認しやすいでしょう。

 

偏差列の作成とオートフィル操作

A	B	C	D
1	氏名	得点	偏差
2	田中	72	2
3	佐藤	85	15

続いては、偏差専用の列を作成し、オートフィルで数式を素早く広げる操作を確認していきます。

先頭の数式を正しく作れば、数十行や数百行のデータでも同じ手順で計算できます。

 

偏差列の見出しと表示形式

偏差を出力する列の1行目には、偏差という見出しを入力します。

得点がB列なら、隣のC列を偏差列にする構成が分かりやすいでしょう。

偏差に小数が出る場合は、ホームタブの小数点以下の表示桁数を増減して読みやすく調整します。

元の数値と偏差を横に並べると、各データが平均からどの程度離れているかを比較しやすくなります。

マイナスの偏差を目立たせたい場合は、条件付き書式で0未満のセルに色を付ける方法もあります。

ただし、色だけに依存せず、見出しと数値を確認できる表にしておくことが大切です。

 

フィルハンドルによる数式コピー

続いては、フィルハンドルを使った偏差のコピー操作を確認していきます。

C2に=B2-$B$8を入力して計算結果が表示されたら、C2セルを選択します。

セル右下の小さな四角形をダブルクリックすると、隣接するB列のデータが続く最終行まで数式をコピーできます。

データの途中に空白がある場合は、ダブルクリックでは途中で止まることがあります。

その場合は、フィルハンドルを最終行までドラッグするか、コピーと貼り付けを使いましょう。

コピー後は偏差列の先頭と末尾を確認し、平均値セルの参照が固定されているか確かめます。

画像のように、最初の偏差セルだけへ数式を入力し、右下のフィルハンドルを下方向へ引っ張ります。

コピー先のセルでは得点の行番号だけが変わり、平均値の絶対参照は変わりません。

 

コピー結果を確認するチェック方法

続いては、オートフィル後に偏差を確認する方法を見ていきます。

偏差列の任意のセルを選択し、数式バーで参照先を確認してください。

たとえばC5セルなら、=B5-$B$8のように表示される状態が正しい形です。

データを並べ替える場合も、数式が入力された偏差列を含めた表全体を選択して並べ替えます。

得点列だけを単独で並べ替えると、氏名や偏差との対応が崩れる可能性があります。

 

偏差値と標準偏差の違い

A	B	C
1	用語	意味
2	偏差	個々の値と平均値との差
3	標準偏差	データのばらつきの大きさ
4	偏差値	比較しやすく変換した指標

続いては、偏差と混同されやすい偏差値、標準偏差との違いを確認していきます。

用語の役割を分けて理解すると、エクセルでの集計やデータ分析が進めやすくなります。

 

偏差と偏差値の役割

偏差は、個々の値から平均値を引いた生の差です。

一方の偏差値は、偏差を標準偏差で調整し、平均が50になるよう変換した数値です。

偏差値は異なる集団やテストでも比較しやすいように、平均との差を共通の尺度へ変換した数値です。

偏差だけでは、テストごとの難易度や得点のばらつきが異なる場合に単純比較できません。

偏差値を求めるには、先に偏差と標準偏差を正しく計算する必要があります。

 

標準偏差で分かるデータのばらつき

続いては、標準偏差の基本的な意味を確認していきます。

標準偏差は、データが平均値の周辺にどの程度散らばっているかを示す数値です。

エクセルでは、標本データに対してSTDEV.S関数、母集団全体に対してSTDEV.P関数を使用します。

標準偏差が小さい場合は、データが平均値の近くへ集まっている傾向があります。

標準偏差が大きい場合は、平均から大きく離れた値が含まれ、ばらつきが大きい状態です。

偏差は一つひとつのデータの位置、標準偏差はデータ全体の散らばりを示します。

 

分析目的に応じた使い分け

続いては、偏差と関連指標を使い分ける考え方を確認していきます。

個別の社員や商品の実績が平均より上か下かを確認したいなら、偏差が適しています。

月ごとの売上が安定しているか、測定値がどれほど散らばるかを知りたいなら、標準偏差が役立ちます。

学力試験のように、平均50を基準に相対的な位置を示したい場合は偏差値を使います。

目的に合わない指標を選ぶと、計算結果が正しくても分析の判断を誤るかもしれません。

まずは偏差を作成して個々の差を確認し、必要に応じて標準偏差や偏差値へ進む流れがおすすめです。

 

まとめ エクセルで平均値との差を求める偏差の方法

項目	入力内容
平均値	=AVERAGE(B2:B6)
偏差	=B2-$B$8
偏差合計	=SUM(C2:C6)

エクセルで偏差を求めるには、まずAVERAGE関数で平均値を出し、各データから平均値を引きます。

基本の数式は、個々の値がB2セル、平均値がB8セルなら=B2-$B$8です。

平均値セルを絶対参照にしてからオートフィルを使うと、多数のデータでも正確に偏差を計算できます。

結果がプラスなら平均より高く、マイナスなら平均より低く、ゼロなら平均と同じ値です。

偏差列の合計がゼロになるかを確認すれば、参照範囲や数式コピーの誤りを見つける手がかりになります。

個々の数値の位置を調べるなら偏差、全体のばらつきを調べるなら標準偏差、比較しやすい尺度へ変換するなら偏差値を使いましょう。

平均値との差を表へ追加して、得点、売上、時間、数量などのデータをより具体的に分析してみてください。