Excelで平均二乗誤差(MSE)を計算する方法【ステップバイステップ完全ガイド】

統計学において、平均二乗誤差(MSE)は統計モデルに含まれる誤差の大きさを測る重要な指標です。将来の値を予測するモデルの精度を評価する際によく活用されています。

本記事では、MSEの概要を詳しく解説したうえで、ExcelでMSEを計算する具体的な手順をご紹介します。

平均二乗誤差(MSE)とは?

平均二乗誤差(MSE)とは、データセットにおける予測値(推定値)と実際の値との差を二乗し、その平均を求める計算手法です。言い換えれば、統計モデルに含まれる誤差の大きさを推定する指標であり、統計学の観点から見ると、回帰直線がデータポイント群にどれだけ適合しているかを測るものと言えます。

例えば、ソフトウェア会社の年間売上額を予測するモデルがあるとしましょう。年末に実際の売上数値を入力し、MSEを計算することで、モデルがどれほど正確に結果を予測できたかを検証できます。

MSEは、予測値と実際の値の差を二乗し、その平均を取ることで算出されます。

MSEの数式は以下の通りです。

各記号の意味は次の通りです。

1. Σ:値の総和(合計)を表します
2. n:サンプルサイズ(観測数)
3. Yi:観測値(実際の値)
4. Ŷi:予測値

誤差の値が小さいほど、モデルの標準誤差が小さく、目的変数の予測精度が高いことを示します。MSEは統計的回帰やデータ分析の分野で広く利用されており、複数のモデルの比較や、予測精度を高めるためのパラメータ調整にも役立ちます。

数式は一見複雑に見えますが、以下のセクションで順を追って分解していくので、決して難しくありません。

ExcelでMSEを計算する主な方法は2つあります。「SUMSQ関数」を使う方法と、「AVERAGE関数」を使う方法です。以下のサンプルデータを使って、それぞれの手順を解説します。

この簡易的な例では、各月の架空の売上値を扱います。A列に月、B列に予測値、C列に実際の値が入力されています。

SUMSQ関数を使ってMSEを計算する方法

ExcelのSUMSQ関数は、指定した範囲内の数値の二乗和を計算する関数です。これを使ったMSEの計算手順は以下の通りです。

1. D列に「差分」という名前の新しい列を作成します。ここには予測値と実際の値の差が入ります。

1. 数式「=C2-B2」を入力し、各行のデータポイントについて予測値と実際の値の差を計算します。

1. 任意の空きセルを選択し、次の数式を入力します:「=SUMSQ(D2:D13)/COUNT(D2:D13)」。数式内のセル参照は、ご自身のスプレッドシートに合わせて適宜変更してください。このセルに表示される最終的な結果がMSEです。

AVERAGE関数を使ってMSEを計算する方法

ExcelのAVERAGE関数は、指定した範囲の数値の平均を計算する関数です。この方法はSUMSQ関数と同じ結果になりますが、各ステップを手動で実行していく点が異なります。

1. まず、SUMSQ関数の方法で紹介したステップ1と2を実行し、予測値と実際のデータの差を求めます。

1. E列に「差分の二乗」という名前を付け、数式「=D2^2」を使ってD列の各値の差を二乗します。

1. 新しい空きセルを選択し、E列の平均値を計算します。これにより、先ほど求めた二乗値の平均、すなわちMSEが得られます。「=AVERAGE(E2:E12)」と入力してEnterキーを押してください。セル参照は、ご自身のワークシートで使用している範囲に合わせて変更してください。

まとめ:Excelでの統計分析

Microsoft Excelは、長年にわたり最も強力なデータ分析ツールの一つとして活用されてきました。このチュートリアルを参考にすれば、ExcelでMSEを簡単に計算できるようになったはずです。また、操作方法が非常に似ているため、Googleスプレッドシートでも同様の計算を行うことができます。