以前デイリーポータルZさんとコラボした記事について、そこで収拾したデータを用いてさらに重回帰分析を行う、という（アホな）企画をやりました。
この記事の中で、（無理矢理）重回帰分析を用いて最もスイーツ需要過多な駅を割り出したわけですが、この記事を読んで下さった方から「重回帰分析をExcelでやる方法を知りたい！」というリクエストを複数頂いたので、今更ながらその方法を解説したいと思います。
「重回帰分析をExcelでやる方法を知りたい！」というリクエストを複数頂いたので、今更ながらその方法を解説したいと思います。
はじめに
Excelで統計解析処理を行うためにはまず「分析ツール」というアドインを有効にする必要があります。
まずはExcelを開いて「ファイル」→「オプション」→「アドイン」を選択してください。
そして｢アクティブなアプリケーションアドイン」に分析ツールがない場合は、「管理」で「Excelアドイン」を選択肢し、「設定」をクリックします。
すると次のような画面になるので、「分析ツール」にチェックをいれ「OK」をクリックすると、｢データ」メニューに「データ分析ツール」というアイコンが表示されるようになります。
これでデータ分析の準備は完了です！
仮説を立てる
データ分析をビジネスの場で活用してみたいという人は、売り上げやコストなどのKPIを改善したいという人がほとんどだと思います。
知りたい目標が決まったら、その目標の違いを生みそうな仮説を立てて、それを検証してみましょう。
冒頭で紹介したスイーツの記事では、最もスイーツの供給が足りていない駅を知りたかったので、まずスイーツ店が生息する地域の特徴を調べるために４つの仮説を立てました。
仮説1．学生の多い駅にはスイーツが多い。
仮説2．高齢者の多い街にはスイーツが多い。
仮説3．アパレル店の多い駅にはスイーツが多い。
仮説4．居酒屋の多い駅にはスイーツが少ない。
ちなみに、前回の記事ではデータセットを用意するためにいろいろなサイトからデータを引用しましたが、これからデータ分析をしようという皆さんの場合、既に目の前にデータがあると思うので、そんな面倒なことをする必要はありません。DPZのお二人は本当にお疲れ様でした（笑）。
相関関係を調べる
それでは実際に仮説を検証していきます。
今回はそれぞれの仮説に相関関係があるかを検証しました。
相関関係とは、平たく言うと２つの値の関連性のことです。今回は変数が2つと単純なので、散布図を書いて直感的に関連性を把握してみましょう。一番あてはまりの良かった仮説4で実践してみます。
まずは上記のように配置された表を作ってください。そして｢挿入｣メニュー→「グラフ」→「散布図」と選択していってください。最後に下記のように範囲を指定すればこのような散布図ができているはずです！（今回はタイトルを消して、軸ラベルを追加してあります。）
散布図の点の一つ一つが山手線の駅になっています。
これらの点が線形に近いほど相関関係が高く、２つの値は関連性があるといえます。
この図でもなんとなく右下がりの関係が見えると思います。オレンジの線が近似直線です。
これは居酒屋率が高くなるほど、糖分濃度が低くなっていることを意味します。
続いて、もう少し正確に相関関係を把握するために先ほどの｢分析ツール」を使います。
「データ」→｢分析」→「データ分析ツール」とクリックしていき、「相関」を選択して「OK」を押します。
上記の範囲を選択します。また先頭行は変数名なので、「先頭行をラベルとして使用」にチェックを入れましょう
するとこのような表が出力されます。-0.5068が相関係数です。
相関係数は-1から1までの値をとり、絶対値が大きいほどその関係性が高いことを意味します。
今回はやや負の相関があるといえるでしょう。
ここからはやや面倒くさいですが統計の話に入ります。
ここで算出された、-0.5068という相関係数が本当に関係あるものなのか、それとも偶然高い値が出てしまったのかを検証します。
これを確かめるには無相関検定を行う必要があります。
無相関検定とは、相関係数が0であるという帰無仮説が採用される確率、
つまり本当は相関がないのに、たまたま高い値が出てしまった確率を出してくれます。
簡便のために説明は省きますが、統計量Tは以下の式で出ます。
これをExcelの式に直すと、 T = ABS( r ) * SQRT( n – 2) / SQRT( 1 – r^2) となります。
ちなみに、ｒは相関係数、nはデータの数を表しています。
これを解くと、統計量Tは約3.166となりました。
この統計量を使って有意確率ｐを出します。
無相関検定では統計量Tは自由度ｆ＝n – 2の分布に従うので、T.DIST.2T関数を用いて、
p = T.DIST.2T( T , n-2) より有意確率pの値は約0.004だとわかります。
有意確率0.004とは、たまたま相関係数が高く出てしまったとしたら、それは250回に1回の確率だったということを表しています。
よって、今回の相関係数-0.5068は偶然ではないということができるのです。
一般的には、p ＜ 0.05 、厳密に検定してもp ＜ 0.01の場合、帰無仮説は棄却され、偶然ではないということができます。
重回帰分析で予測値を出す
先に挙げた仮説のそれぞれの相関係数を調べ、無相関検定をしてみたところ、居酒屋率と中学校の数がスイーツの分布に関連がありそうでした。それを踏まえて、重回帰分析をしてみます。
あらかじめ、先ほど相関関係が確認できた中学校数と居酒屋率と糖度の表を作っておきます。
次に、先ほどと同様に｢分析ツール｣を起動し、「回帰分析」を選択します。
「入力Y範囲」は知りたい値なので、今回はB1:B30を選択します。「入力X範囲」はYを説明する値なので、C1:D30を選択します。ここでも先頭行は変数名なので、「ラベル」にチェックをいれます。｢有意水準｣は本来なら95%ですが、今回は95%でやると居酒屋率しか説明変数に採用されず、つまらないので便宜的に90%にしました。｢出力オプション｣は｢新規のワークシート｣を選択し、｢残差｣にチェックを入れて「OK」をクリックします。
そうすると新しいシートに上記の表が出てきます。
ここでまず確認するのは「補正R2」です。これは説明変数で目的変数をどれだけ説明できているかを表しています。
今回は約30％しか説明できていないので、あまりいいモデルとは言えません。
次にみるのが「P-値」と下限95%、上限95%です。P-値が0.05以下のとき、係数の下限95%と上限95%は0を跨ぎません。
つまり、係数が0になる確率が5%以下なので、その変数を安心して説明変数として使うことができる、ということです。
一方、中学校数はわずかですがP-値が0.05を超えており、厳密には有意であるとは言えません。
しかし、下限90%と上限90%をみると0が含まれていないので、｢中学数の係数は信頼水準90%で0.002815から0.03651である｣ということができます。
次に、念のため多重共線性の診断をしておきます。多重共線性は、居酒屋率と中学校数という二つの説明変数が同じことを説明していないかを確認するためのものです。これは糖分濃度を固定したときの居酒屋率と中学校数の偏相関分析によって求めることができます。
遍相関分析は上記の式で求めることができます。x=居酒屋数、y=中学校数、z=糖分濃度とおくと、偏相関係数=0.1024となるので、糖分濃度の影響を取り除いたときに2つの変数間には相関がなく、多重共線性はないといえます。
残差出力
最後に｢残差出力｣に注目します。
予測値は重回帰分析の結果から計算された値なので、予測より糖分濃度が高い、つまり供給過多駅は残差がプラスに、予測より糖分濃度が低く、需要過多な駅は残差がマイナスになります。よって、rank関数を使ってこれらの駅を順位付けすると、駒込が最下位となり、最も需要過多な駅であることがわかります。
如何でしたでしょうか。是非皆さんもエクセルでの重回帰分析にチャレンジしてみてください！
