Excel。データから手早く複数の予測値を算出するにはどうしたらいいの
＜TREND関数＞
次のようなデータがあるので、予測値を算出する。
できれば、様々なケースの予測を同時に算出したい場合、Excelの関数をつかうことで、手早く算出することができます。
今回予測したいのは、来店客数です。
B列には、売上高。C列には、来店客数のデータがあります。
せっかくデータがあるので、売上高を1100にするには、どのぐらいの来店客数があればいいのかを算出したいわけですね。
B列とC列のデータを目視から、「このぐらいじゃない」というのは、マズいわけですね。
来店客数の予測を算出すること自体は、関数で手早く算出できるのですが、前提条件として、売上高と来店客数に、関係性があるのか、ないのか、ということがあげられます。
そこで、F1に、相関係数を算出することから始めます。
F1に設定している数式は、
=CORREL(B2:B12,C2:C12)
CORREL関数は、相関係数を算出できる関数です。
この値が、「1」に近いので、因果関係まではわかりませんが、何らかの関係性は強いと判断できます。
逆に「0」に近ければ、「関係性はない」ということがわかります。
来店客数が増えれば、売上高があがることがわかったわけです。
これを踏まえたうえで、売上高に必要な来店客数を算出していきます。
F4に
=INT(TREND(C2:C12,B2:B12,E4:E12))
という数式を設定します。
予測値を算出するだけならば、FORECAST関数を使うことで算出できますが、複数の予測値を算出するには、TREND関数をつかいます。
予測値を算出するにあたり、回帰直線のy=ax+bに基づき引数を設定していきます。
既知のyには、来客数の予測値を算出したいので、C2:C12を設定します。
既知のxには、売上高がxにあたりますので、B2:B12を設定します。
新しいxには、売上高の予定値を設定しますので、E4:E12を設定します。
今回は、定数は省略しています。
また、算出結果に小数点が表示されます。
来店客数なので、INT関数をつかって、整数化しています。
以前は、配列関数にしなければいけませんでしたが、今は、スピル機能が追加されましたので、F12まで予測値を算出してくれます。
このように、ちょっとしたデータでも、複数の予測値を素早く算出することができますので、今までの資料にも、このような関数を使いすることで、違った角度から資料を見ることができるかもしれませんね。
