エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】 | 統計解析 - ChatGPT・Python・エクセルを使った講義で最速マスター

セミナー詳細                    解析ご相談                    LINEでお友達

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】
最小二乗法による回帰係数の推定は、残差平方和を最小化することで一意に決定されます。このプロセスを通じて、全平方和(全変動)、回帰平方和(説明された変動)、および残差平方和(未説明変動)の関係が確立され、これらの和は等しくなります。この関係を基に、決定係数(R2乗)が計算され、エクセルの関数を用いて簡単に求めることができます。R2乗は、モデルがデータをどの程度説明しているかを示す指標で、値が1に近いほど、モデルによる予測の精度が高いことを意味します。


目次  エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】

 

回帰係数と予測値と残差平方和の関係

 

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】

 

最小2乗法の解は一意に決まるので、ソルバーによる最小2乗法で得られた変化させるセルから回帰係数(傾きとY切片)が求まります。

 

これらの回帰係数を予測式に代入することにより、それぞれの予測値が求まります。

 

上のExcelのテーブルではF列のYihatというのが予測値です。

 

そして、観測値Yiから予測値を引いたものが残差(H列のEi)です。

 

これを関数SUMSQで平方和を計算した結果がカラムH8に表示されています。

 

これが残差平方和です。ここでは値は27.527 先にソルバーで計算したものと一致していることを確認しましょう。

 

流れを整理すると、

 

@ソルバーで回帰係数を求める

 

A回帰係数を式に代入し予測値を求める

 

B観測値と予測値の差(残差)を求める

 

C残差平方和を求める

 

Dソルバーで求めた残差平方和と一致することを確認する

 

これら@〜Dの手順をExcelシート上で行ってみてください。

 

直線回帰分析の考え方を肌で感じとることができます。

 

Excelの優れた点は、セル単位で計算処理するので計算過程を実感できることです。

 

統計専用ソフトのように、自動的に結果だけが出力されるのと違った旨みがあります。

 

統計専用ソフトでも実行し、得られた結果が一致することを確認できるとなお良いでしょう。

 

 

全平方和=回帰平方和+残差平方和

 

回帰分析はただ機械的に行うのではなく、実行した回帰分析の結果が妥当であるかどうかを確認することが大切です。

 

確認する上で大切なのが、回帰診断です。

 

回帰診断では、いくつかの診断統計量と呼ばれるものにより評価します。

 

診断統計量には数多くのものがありますが、ここでは決定係数(R2乗)について解説しましょう。

 

さて、先ずは決定係数(R2乗)ですが、R2乗を理解する上で必要不可欠なのが、以下の関係式です。

 

全平方和(ST)=回帰平方和(SR)+残差平方和(SE)

 

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】

 

先ずSTですが、これはYの全変動を表します。Yの4つの観測値からそれぞれYの平均値Ymを引いたものの平方和です。

 

まじめに計算してもよいですが、C列のYiを全て選択し、DEVSQという関数により一発で計算できます。

 

次いでSRですが、これは回帰直線の変動、すなわちXとYの関係に起因する変動です。

 

そして、SEは残差(誤差)に起因する変動です。

 

予測値はYの平均値を引いたものの平方和です。G列の値をSUMSQで計算します。

 

図のようにST=SR+SEという関係となるので、

 

決定係数(R2乗)をエクセルで計算

 

決定係数(R2乗)=SR/ST で表されます。

 

つまり、決定係数(R2乗)とは目的変数の全変動中の回帰直線の変動の割合を表します。

 

以下の図のように、すべての残差が0、すなわちSE=0のときは、ST=SRとなり、R2乗は1となります。

 

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】

 

つまり、R2乗というのは、観測値がいかに直線上にのっているかの指標です。

 

極端な例では、観測値すべてが直線にちょうどのっている場合、SE=0となり、R2乗は1になります。

 

目安として、R2乗は0.7以上が良いとされています。ここではR2乗は0.898となっております。

 

R2乗を理解する上では、ST=SR+SEという関係が大切です。

 

R2乗というのは、簡単に言えば、観測値がいかに直線上にのっているかの指標です。

 

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】

 

上記のExcelテンプレート中央下にR2乗の計算式が入っています。

 

こちらのExcelテンプレートが欲しい方は以下お問い合わせフォームからリクエスト頂けれは差し上げます。

 

お問い合わせ

 

 

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】


セミナー詳細                    解析ご相談                    LINEでお友達

 

 

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】

エクセルで簡単! R2乗で見る予測精度の真実【ChatGPT統計解析】