予測値と実績値から平均二乗誤差を計算するには？
予測と実績の差を二乗して、モデルごとの平均二乗誤差（MSE）を求めます。実績と予測が両方そろったデータだけを使い、評価件数も確認します。
誤差＋2と−2をそのまま平均すると0になりますが、どちらの予測も正確ではありません。JOIN SHEETで誤差をそれ自身に掛けてから平均すれば、符号が相殺されない平均二乗誤差（MSE）を計算できます。
実績と予測を同じ行で対応させる
下の架空データでは、各行に同じ対象・期間の実績と予測が並んでいます。どちらかが空欄の行は評価から外し、集計に使った件数も表示します。モデルAの空欄は、予測数量0という意味ではありません。
| モデル | 実績数量 | 予測数量 |
|---|---|---|
| A | 10 | 12 |
| A | 20 | 18 |
| A | 30 | 空の値 |
| B | 10 | 11 |
| B | 20 | 23 |
引き算、二乗、平均の順で設定する
- 元シートで実績数量と予測数量の型を「数値」にそろえ、変換できず空欄になった値がないか確認します。データを結果の基準に選び、「結果を整理」で「絞り込み」を2つ追加し、両項目の条件をそれぞれ「空の値以外」にします。これで数値のそろった組だけを評価できます。
- 「計算」で予測数量から実績数量を引き、「予測誤差」を作ります。
- 次の「計算」は「掛け算 · A × B」にし、「最初の値 (A)」と「次の値 (B)」の両方に予測誤差を選びます。「結果の項目名」に誤差の二乗と入力します。
- 「集計」を追加してモデルでまとめます。最初の集計を誤差の二乗の「平均」に変え、「結果の項目名」に平均二乗誤差と入力します。「集計を追加」で「件数」も追加し、「結果の項目名」を評価件数に変えて「適用」を押します。
分母は評価した組の件数
Aは4と4の平均で4、Bは1と9の平均で5になります。どちらも2組なのでnは2です。MSEの単位は数量単位の二乗であり、4という結果は4%を意味しません。平方根を取るRMSEとも異なります。
| モデル | 平均二乗誤差 | 評価件数 |
|---|---|---|
| A | 4 | 2 |
| B | 5 | 2 |
参考にした質問では、Excelの回帰分析にあるANOVA表の分母との違いが話題になっています。この記事では、実績と予測がそろった件数nで二乗誤差の合計を割ります。残差分散の推定に使うn−p−1で割る計算ではありません。モデルを比べるときは対象と単位をそろえ、空欄のために除外したデータも確認してください。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
MSE denominatorの質問を参考に、独自の架空データで手順を作成しました。
