エクセルで、それぞれの値と平均との差（偏差）を2乗して合計した値（偏差の2乗和）を出すには DEVSQ関数を使います。10人の点数なら、=DEVSQ(C3:C12) で 900 になります。
偏差の2乗和は、分散や標準偏差のもとになる数です。この記事では、DEVSQ関数の基本、計算のしくみ、SUMPRODUCT関数で同じ計算をする方法、分散（VAR.P・VAR.S）との関係、組ごとの比較までを、1つの表で説明します。下の練習ツールで、点数を書き換えて結果の変わり方を試せます。
DEVSQ関数の書き方
範囲やセル、数値を指定します。範囲の中の空欄・文字・TRUE/FALSE は無視されます。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
DEVSQ関数の使い方｜やりたいこと別の式
| やりたいこと | 式（例） | 結果 |
|---|---|---|
| 偏差の2乗和 | =DEVSQ(C3:C12) | 900 |
| SUMPRODUCTで同じ計算 | =SUMPRODUCT((C3:C12-AVERAGE(C3:C12))^2) | 900 |
| ÷人数（分散・母集団） | =DEVSQ(C3:C12)/COUNT(C3:C12) | 90 |
| ÷（人数−1）（分散・標本） | =DEVSQ(C3:C12)/(COUNT(C3:C12)-1) | 100 |
| A組の偏差の2乗和 | =DEVSQ(FILTER(C3:C12,B3:B12=”A”)) | 170 |
| B組の偏差の2乗和 | =DEVSQ(FILTER(C3:C12,B3:B12=”B”)) | 370 |
偏差の2乗和を求める
手順：F3 に DEVSQ の式を入れて Enter
F3 に次の式を入力して、Enter キーを押します❶。
=DEVSQ(C3:C12)C3:C12人数で割ると分散（母集団）、人数−1 で割ると分散（標本）になります。
確認：偏差の2乗和は 900
答えは 900 になりました❷。式は、次の順に計算しています。
- 平均を出す
⇒ 70 - （点数−70）の2乗を全員分足す
⇒ 900
結果は 900 です。
=DEVSQ(C3:C12)/COUNT(C3:C12) は 90 で、VAR.P と同じです（F6）。▶ この式（F3）を、練習ツールで試す ／ この式（F4）を、練習ツールで試す
偏差の2乗和の計算のしくみ
10人の平均は =AVERAGE(C3:C12) で 70 です。それぞれの点数から 70 を引いて（偏差）、2乗して合計すると 900 になります。
| 名前 | 点数 | 平均との差 | 差の2乗 |
|---|---|---|---|
| 山田 | 70 | 0 | 0 |
| 佐藤 | 85 | 15 | 225 |
| 鈴木 | 60 | −10 | 100 |
| 高橋 | 75 | 5 | 25 |
| 田中 | 55 | −15 | 225 |
| 伊藤 | 80 | 10 | 100 |
| 渡辺 | 65 | −5 | 25 |
| 中村 | 70 | 0 | 0 |
| 小林 | 80 | 10 | 100 |
| 加藤 | 60 | −10 | 100 |
| 合計 | 900 |
差をそのまま足すと、プラスとマイナスが打ち消し合って必ず 0 になります。2乗するのは、平均より上でも下でも「離れている大きさ」として数えるためです。
SUMPRODUCT関数で同じ計算をする
「点数−平均」を2乗して合計する式をそのまま書くと、結果は同じ 900 です。DEVSQ関数は、この計算を1つの関数でできるようにしたものです（SUMPRODUCT関数の使い方）。
分散との関係（人数で割る）
偏差の2乗和を人数の 10 で割ると 90 で、=VAR.P(C3:C12) の 90 と同じです。「人数−1」の 9 で割ると 100 で、=VAR.S(C3:C12) の 100 と同じになります。
| 割る数 | 結果 | 同じ結果になる関数 |
|---|---|---|
| 人数（10） | 90 | VAR.P（分散・母集団） |
| 人数−1（9） | 100 | VAR.S（分散・標本） |
さらに平方根をとると標準偏差になります（VAR.P関数の使い方・STDEV.S関数の使い方）。
▶ この式（F6）を、練習ツールで試す ／ この式（F7）を、練習ツールで試す ／ この式（F8）を、練習ツールで試す ／ この式（F9）を、練習ツールで試す
組ごとの偏差の2乗和（FILTER）
A組は 170、B組は 370 です。どちらも5人なので、そのまま比べて B組のほうがばらつきが大きいと分かります。人数が違う組を比べるときは、人数で割った分散で比べます。FILTER関数は Excel 2021 以降・Microsoft 365 で使えます（FILTER関数の使い方）。
▶ この式（F10）を、練習ツールで試す ／ この式（F11）を、練習ツールで試す
DEVSQ関数がうまくいかないときの確認表
| 症状 | 考えられる原因 | 対処 |
|---|---|---|
| 思った値にならない | 「欠席」などの文字や空欄が除かれている | COUNT関数で数値の個数を確かめる。 |
| 「#NUM!」になる | 範囲に数値が1つもない | 数値を入れる。IFERROR関数で表示を決める。 |
| 0 になる | すべて同じ値 | ばらつきが無いので 0 が正しい。 |
| 人数が違う組で比べられない | 偏差の2乗和は人数が多いほど大きくなる | VAR.P関数などで人数で割った分散を比べる。 |
DEVSQ関数のよくある質問
偏差の2乗和は何に使う？
分散・標準偏差の計算のほか、回帰分析や分散分析で「全体のばらつき」を表す数として使います。ふだんのばらつきの確認なら、VAR.P・STDEV.P などの関数のほうが分かりやすい数で出ます。
あわせて読みたい：統計の関連記事
練習用ファイル：練習用Excelファイル（.xlsx・無料）
