Excel。事務職の資料にプラスα。重回帰分析を「データ分析」機能でみてみよう
＜データ分析：重回帰分析＞
Excelには、「データ分析」機能をアドインすることで、様々なデータを即座に解析して資料の下地を作成してくれます。今回は次のデータから、重回帰分析を「データ分析」でみてみましょう。
売上高と店舗面積と来店客数と駅からの距離のそれぞれの数値から未知のデータを予測してみたいわけです。
この時使う手法が【重回帰分析】です。
これらの数値は関連性があるのか、ないのか？がわかるだけでも、データを見る目が変わってきます。
今回は、売上高をUPさせたいという観点から分析させるので、売上高が『目的変数』にあたります。
ほかのデータは、売上高に関与しているであろうと思われるので『説明変数』にあたります。
【データ分析をつかってみよう】
関数を使ってもいいのですが、アドインした「データ分析」を使うことで簡単に算出することができます。
データタブの「データ分析」をクリックします。
表示されていない場合には、まだアドインされていませんので、ファイルタブのオプションをクリックして、Excelのオプションダイアログボックスが表示したら、メニューにある「アドイン」から追加しましょう。
データ分析ダイアログボックスが表示されたら、【回帰分析】を選択して、OKボタンをクリックします。
回帰分析ダイアログボックスが表示されます。
入力Y範囲には、「目的変数」を入力しますので、目的変数は、売上高なので、
$B$1:$B$11
見出し行も含みますので注意しましょう。
入力X範囲には、「説明変数」を入力しますので、$C$1:$E$11
ラベルに、チェックマークをいれます。
有意水準にチェックマークをいれて、99%に変更します。
99%で設定することで、算出されてくる信頼区間も99%の信頼度で算出されてきます。
入力後OKボタンをクリックします。
新しいシートに分析結果が算出されるので、列幅を調整しましょう。
【出力されたら”分散分析表”をみる】
最初にどこを見るのかといえば、『分散分析表』の有意Fです。このあたいが5%や1%よりも小さければ【有効】であるといえます。今回は、0.000107808と1%よりもはるかに小さい値なので、求めた重回帰分析の信頼性は高いと判断できます。
続いて、『P-値』を見てみましょう。
P-値が5%を下回っているのかを確認してみましょう。
店舗面積は、5%を上回っていますので、説明変数に加えるのか検討の余地があります。というか、売上高と店舗面積はあまり関係無いようです。
来店客数は、5%を大きく下回っていますので、有効な説明変数だと考えれれます。
駅から距離は、5%を少し上回っているので、検討の余地がありそうですね。
【予測値を算出してみよう】
重回帰分析の信頼性は高いようなので、売上高の予測を求めてみましょう。重回帰分析は、Y=A×x1+B×x2+C×x3…+Dで算出することができます。
店舗面積を10
来店客数を300
駅から距離を50
とした場合、
=E4*B18+F4*B19+G4*B20+B17
算出すると、約451.5と算出されました。
このように、「データ分析」機能を使うことで、比較的簡単に、データから色々なものがみえることができますので、日ごろ使っている資料に加えてみると面白いものがみえてくるかもしれませんね。
