SUMSQ 関数は、数をそれぞれ 2 乗して合計する関数です。=SUMSQ(3,4) は 9＋16 で 25 になります。SUMXMY2 関数は、2 つの範囲の差の 2 乗の合計を出し、予測と実績のずれを調べるときに使います。
この記事では、SUMSQ となかまの関数の使い方を、実際の Excel で確かめた結果で説明します。
練習用Excelファイル（無料ダウンロード）
予測と実績の表と、この記事の SUMSQ・SUMXMY2 の式をまとめた練習ファイルです。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
目次
SUMSQ・SUMXMY2・SUMX2PY2・SUMX2MY2 関数の書き方
=SUMSQ(数値1, [数値2], …)
=SUMXMY2(範囲1, 範囲2)
=SUMX2PY2(範囲1, 範囲2)
=SUMX2MY2(範囲1, 範囲2)| 関数 | 計算 |
|---|---|
| SUMSQ | 2 乗の合計（x²＋…） |
| SUMXMY2 | 差の 2 乗の合計（(x－y)²＋…） |
| SUMX2PY2 | 2 乗の和の合計（x²＋y²＋…） |
| SUMX2MY2 | 2 乗の差の合計（x²－y²＋…） |
SUMXMY2 などの 2 つの範囲は、同じ大きさにします。
1. 2 乗の合計を出す（SUMSQ）
3²＋1²＋2²＋4² を計算します。
=SUMSQ(A2:A5)
結果は 30 でした（9＋1＋4＋16）。
2. 斜辺の長さを出す
直角三角形の 2 辺が 3 と 4 のとき、斜辺は √(3²＋4²) です。
=SQRT(SUMSQ(3,4))
結果は 5 でした。2 点の距離も、x の差と y の差を SUMSQ に入れて同じように出せます。
3. 予測と実績の差の 2 乗の合計（SUMXMY2）
(3－4)²＋(1－2)²＋(2－5)²＋(4－1)² です。差がプラスでもマイナスでも 2 乗するので打ち消しあいません。
=SUMXMY2(A2:A5,B2:B5)
結果は 20 でした（1＋1＋9＋9）。
4. ずれの大きさ（RMSE）を出す
差の 2 乗の合計を個数で割って平方根にすると、平均的なずれの大きさ（RMSE）になります。
=SQRT(SUMXMY2(A2:A5,B2:B5)/COUNT(A2:A5))
結果は 2.24 でした。
【実機で検証】SUMSQ でつまずくところ
| ためしたこと | 式 | 結果 |
|---|---|---|
| SUMPRODUCT で 2 乗の合計 | =SUMPRODUCT(SUMSQ!A2:A5^2) | 30 |
| 範囲に文字と空白（D4:D7） | =SUMSQ(D4:D7) | 13 |
| 文字の “3” を直接 | =SUMSQ("3",4) | 25 |
| 文字の “a” を直接 | =SUMSQ("a",4) | #VALUE! |
| 大きさがちがう（A2:A5 と B2:B4） | =SUMXMY2(SUMSQ!A2:A5,SUMSQ!B2:B4) | #N/A |
| SUMX2PY2（予測²＋実績²） | =SUMX2PY2(SUMSQ!A2:A5,SUMSQ!B2:B5) | 76 |
SUMPRODUCT(A2:A5^2) でも 30 で、SUMSQ と同じでした。
範囲の中の文字の「4」と空白は無視され、13（3²＋2²）になりました。一方、式に直接 “3” と書くと数として計算され 25、”a” のように数にできない文字は #VALUE! です。
大きさのちがう範囲を SUMXMY2 に入れると #N/A になりました。SUMX2PY2 は 76 です。
よくある質問
平方根（ルート）を出したいときは？
SQRT 関数を使います。
平均からのずれの 2 乗の合計を出したいときは？
DEVSQ 関数で、偏差の 2 乗の合計を出せます。
掛け算の合計を出したいときは？
2 つの範囲を掛けて合計するなら SUMPRODUCT 関数を使います。
練習用Excelファイル（無料ダウンロード）
この記事の例をまとめた練習ファイルです。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
コメント