Excelで単回帰分析 データを分析して未来を予測しちゃおう！

寄稿しました。

 

作業フロー

1. まずは月別の売上高の棒グラフを作成してみる
2. xとyとして相関しそうな２つのデータを見つける
3. ２つの相関比を計測する
   →相関関係がない
   おしまい、他のデータを見よう。
4. ２つのデータから散布図を作成する
5. 単回帰分析で１次関数を割り出す
6. １次関数を使って未来を予測する

こんな流れでやっていきます。

 

 

 

まずは、月別と売上高の棒グラフを作ろうね

単純に月別の売上高の棒グラフは資料作りの定番ですし、あったほうが現状を視覚的に俯瞰できて良いでしょう。

上記のようなデータがあったとします。

 

 

 

【挿入】>> 【グラフ】を選択します。

 

タイトルをつけましょうね。

 

月別の売上高の推移が作れました。普通の一般的な月別の売上高でおなじみです。難しくないはずです。

 

 

 

ポスティング件数と売上高の関係性にせまる

売上高とポスティング数の関係に相関関係がどれだけあるかという数値を計測します。

• ポスティング数が増えれば、売上高があがり
• ポスティング数が減れば、売上高は下がる

そういった関係があるのかというのが、CORREL関数を使うことで割り出せます。

 

相関比が出力されるセルを用意して、選択します。

 

 

みんな大好き【オートSUM】の隣の▼タブをクリックして、【その他の関数】をクリックします。

 

 

 

【関数の分類】のセレクトボックスから【統計】を選択し、『CORREL』関数を選択し、【OK】をクリックしましょう。

 

 

 

 

 

売上高の配列の範囲、ポスティング数の配列の範囲を指定して、【OK】をクリックします。

 

 

 

 

相関比が出ました。0.7以上あれば十分相関があると言えると思います。

 

相関の強さ

• 0.7～1 強い相関がある
• 0.4～0.69 やや相関がある
• 0.4以下 弱い相関がある

 

 

なんで相関比なんて出すの？

気温が高くなるとアイスクリームが売れる！のように、気温の上昇とアイスクリームの販売数など、２つのデータに対して、相関の関係性がないと分析する意味がないからです。相関比はその判断を数値化して行うことが出来ます。

 

 

散布図を作ろう！

相関関係があるデータにおいて、散布図があるとデータの分布から、縦軸yと横軸xの規則性を確認・分析しやすいのです。

上記のように範囲して、【挿入タブ】>> グラフタブを選択します。

 

 

 

上記のように散布図を選択します。

 

 

あらら、、x軸とy軸はx軸にポスティング数、ｙ軸に売上高としたいのに、逆になっています。

そんな時は、【データの選択】をクリックして下さい。

 

 

 

 

【編集】をクリックして下さいね！

 

 

 

 

x軸とy軸の値はこうなっているので、

 

 

コピペで入れ替えてあげて下さい、入力が終わったら【OK】をクリックします。

 

 

 

 

【OK】をクリックします。

 

 

 

 

単回帰分析で未来を予測しよう！

ｘとyの１次関数のグラフがこの段階で見えてきますね。

 

このグラフを具体的な数字を使って関数にしたいと思います、

相関するデータから関数にする＝回帰分析

です。

 

近似的な関数をエクセルを使って割り出しましょ～！(もしアナログでおこないたいという場合は最小二乗法を使って、近似関数を割り出す必要があります。)

 

 

【近似曲線】をクリックします。

 

 

 

１次関数が出来ました。

 

 

 

点をどれでも良いのでクリックして選択し、右クリックして下さい。

 

 

 

 

【近似曲線の追加】を選択します。

 

 

 

 

【線形近似】、【グラフに数式を表示する】をクリックして有効化して、【×】で閉じて下さいね！

 

 

 

y=17745x + 2E+06

という関数が出来ました。

 

これはy = 17745x + 2×10の６乗を表します。

 

 

決定係数 R-2乗値 どれだけ関数が実態に当てはまっているか

今回導きだされた関数は、実際のデータとどれだけ当てはまって説明できるかを数値化したいと思います。

R^2 = 説明出来ている情報/全情報

 

数式を表示する時に、『グラフにR-2乗値を表示する』にチェックを入れます。

 

 

 

 

0.7138と出ましたね。
71%がこの関数で説明出来るということを表します。

 

決定係数の強さ

• 0.9 ～ 1 非常に当てはまる
• 0.7 ～ 0.89 強く当てはまる
• 0.5 ～ 0.69 当てはまる
• 0.5未満 あまり当てはまらない

0.7138は少し弱いが、おおかた当てはまってるなぁってところですね。

 

 

関数を使って未来を見通しちゃおう！

ポスティング数500の時に、10,872,500円の売り上げが見込めそうだなということがわかりますね！

 

数値だけシンプルに知りたい FORECAST()

シンプルに数値のみ出せます。

色々なデータの相関関係を割り出して、ビジネスに役立てていければ良いですよね！

 

お疲れ様です。