■ 「回帰分析」に関する知識を強化する！
普段の生活には全く縁がないと思われる数学知識ですが、市場分析という
世界に足を踏み入れたのであれば無関係とは言えない知識になるでしょう。
でも今更学生時代の教科書を引っ張り出すには・・ (ﾉ_･｡)
と知識の取得を諦めてしまう方も多いことでしょう。当コンテンツは、そんな方々
へお贈りいたします。
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
記事で扱った処理は下記よりサンプルExcelファイル
をダウンロードできます
今回のファイル名 ： 『回帰－分散比と有意F値サンプル』
※ファイルの反映に時間がかかる時があります
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
■ 今回扱う知識以外に必要な知識
・ 分散を知ろう！
■ 今回扱う知識は「Excelによる分散比と有意F値」
【偏差平方和から有意F値まで全て求める！】
ついでと言ってはなんですが、Excelの分析ツールによる回帰分析で出力される
分散分析の欄を再現しながら分散比と有意F値を求めていきます。
サンプルファイルに合わせて、下記の例題を基に計算をしていきます！
予測と残差は「今回扱う知識以外に必要な知識」先の記事を参考にしてくださいね♪
まず残差、Y軸、予測値の順に偏差平方まで計算しておきます。この偏差平方部
分が土台になります。
＜一行目に入れる数式＞
残差平方 ⇒ =POWER(F4,2)
Y軸偏差平方 ⇒ =POWER(B4-AVERAGE($B$4:$B$103),2)
予測値偏差平方 ⇒ =POWER(E4-AVERAGE($E$4:$E$103),2)
※上記式をデータ数分下にコピーするだけです
■ 自由度
自由度は手で入力しても、念のため関数で導いてもどちらでもOKです。例題では、
データ数 ⇒ =COUNT(B4:B103)
独立変数 ⇒ =COUNT(C4:D4)
列と行をCOUNT関数で求めて各個数を導いています。ちなみに、ファイルでは訂正
してますが独立変数は説明変数のことです。そして自由度ですが、
回帰 ⇒ 説明変数の個数 ⇒ =L9
残差 ⇒ データ数－１－説明変数の個数 ⇒ =L8-1-L9
合計 ⇒ データ数－１ ⇒ =L8-1
となります。この自由度は不偏分散を導くための自由度となりますね！
■ 変動＝偏差平方和
変動は偏差平方和のことなので、冒頭部分で計算した各偏差平方の総和を求める
ことで算出します。
回帰 ⇒ 予測値偏差平方の総和 ⇒ =SUM(I4:I103)
残差 ⇒ 残差平方の総和 ⇒ =SUM(G4:G103)
合計 ⇒ Y軸偏差平方の総和 ⇒ =SUM(H4:H103)
■ 分散
分散は不偏分散として計算しなくてはならないので、先に求めた自由度を用いて
分散を求めます。回帰の分散は予測値の偏差平方和を説明変数で除算するとい
うことにイメージが持ち難いと思いますが、こんな計算なんだなと理解しておけば
良いかと思います。ちなみに合計の分散は必要ありません！
回帰 ⇒ 回帰の変動÷回帰の自由度 ⇒ =M13/L13
残差 ⇒ 残差の変動÷残差の自由度 ⇒ =M14/L14
■ 観測された分散比
分散比はF検定を行うために必要な計算です。単に回帰の分散を残差の分散で
除算するだけです。意味としては次に有意Fの値で説明されます。
観測された分散比 ⇒ =N13/N14
■ 有意F
有意FはF分布を基に算出されます。F分布はブログ内で扱っていないので詳細
は省略しますが、観測された分散比と、観測された分散比に使用した各分散の
自由度によって計算します。ExcelではFDIST関数によって値を導きます。
有意F ⇒ =FDIST(O13,L13,L14)
FDIST関数は自由度１に観測された分散比の分子の値、自由度２に分母の値を
指定しておけば問題ありません。ちなみにXの値は観測された分散比の値です。
以上をもってExcelによる回帰分析にて出力される分散分析表が再現されました。
回帰分析の評価項目を分析ツールで出力される方は多いと思いますが、内容を
再現できる方は少ないと聞きます。関数で評価項目が計算できますと、分析ツール
では出力されない他の評価項目を導くことが難しく感じるか、計算すらできなくな
りますので覚えておいて損はないと思います。
頻繁に説明変数を入れ替えて分析を繰り返す場合は関数で組んだ方が手間が
かからないってのもありますよ (^-^)/
