Excel。グラフを使わずに、近似予測を関数で算出してみる
＜近似曲線:SLOPE・INTERCEPT・FORECAST・RSQ関数＞
Excelでは、グラフで、近似曲線を描くことが簡単にできます。例えば、近似直線を描き、近似曲線の書式設定ダイアログボックスに、
「グラフに数式を表示する」にチェックマークをオンすることで、グラフに、数式を表示することができ、その数式を使うことで、近似予測を算出することができますが、グラフを描くことなく、直接データから、近似予測を算出してみましょう。
【線形近似予測】
y=ax+bでお馴染みの、回帰直線。このaは傾きでbは切片と呼ばれていますが、このaとbを関数で算出してみましょう。
傾きは、SLOPE関数で算出することができます。
F2をクリックして、SLOPE関数ダイアログボックスを表示しましょう。
既知のyには、B2:B13
既知のxには、C2:C13
と入力してOKボタンをクリックしてみましょう。
数式は、
=SLOPE(B2:B13,C2:C13)
と設定してあります。
切片は、INTERCEPT関数で算出することができますので、F3をクリックして、INTERCEPT関数ダイアログボックスを表示しましょう。
既知のyには、B2:B13
既知のxには、C2:C13
と入力してOKボタンをクリックしてみましょう。
数式は、
=INTERCEPT(B2:B13,C2:C13)
と設定してあります。
では、結果を確認してみましょう。
グラフに表示した数式と同じ結果になりましたね。
このように関数を使うことで簡単に算出することができます。
y=ax+bを使うことで、例えば、700人の来店客数があれば、y=700×F2＋F3で、予測値は約1521となるのですが、この傾きや切片を算出しなくても、FORECAST関数を使うことで、予測値を算出することができます。
F5をクリックして、FORECAST関数ダイアログボックスを表示しましょう。
Xには700
既知のyには、B2:B13
既知のxには、C2:C13
と入力してOKボタンをクリックしてみましょう。
数式は、
=FORECAST(700,B2:B13,C2:C13)
と設定してあります。
では、結果を確認してみましょう。
予測値は、1521.219と算出することができました。
ところで、そもそも、売上高と来店客数との間の相関関係はどうなっているのでしょうか？
関係性がないのであれば、算出しても、予測値を大きく外れてしまうことになります。
そこで、決定係数も、RSQ関数で簡単に算出することができます。
F6をクリックして、RSQ関数ダイアログボックスを表示しましょう。
既知のyには、B2:B13
既知のxには、C2:C13
と入力してOKボタンをクリックしてみましょう。
数式は、
=RSQ(B2:B13,C2:C13)
と設定してあります。
それでは、結果を確認してみましょう。
結果は、0.851092と『1』に近いので、関係性は高いようですね。
このように、関数を使うことで、予測値を簡単に導くことができますので、ちょっと会議資料とかに加えてみるといいかもしれませんね。
