2017年03月20日

FORECAST関数で値(例えば販売数量とか)を予想してみた / Excel / 関数

Excelの関数で既知のデータを元に未知の値を予想してみます。
FORECAST関数を使用します。
“FORECAST”(フォーキャスト)とは、予想する、予測する と言う意味です。
“forecast the weather”と言うと「天気予報する」です。

例えばこんな、あるイベントに出店したソフトクリームの屋台のその日の気温とソフトクリームの販売数量のデータがあります。
気温が高いと売り上げが上がるだろうと予測できますよね。
20170320_01.JPG

グラフを描いてみるとこんな感じになります。
20170320_02.JPG

週間天気予報を見ると更に気温が上がって、35℃になる予報がでています。
さて、気温が35℃になったらソフトクリームは何個売れるでしょうか?
1日分の材料は何個分用意しておけばいいでしょうか?

と言うことで、FORECAST関数で気温が35℃になった時のソフトクリームの販売数量を予想してみます。
20170320_03.JPG

関数の挿入で「統計」から「FORECAST」を選択します。
20170320_04.JPG

引数を設定します。
X:予想する値Yの時のXの値。ここでは販売数量がYで気温がXなので、このXは気温35を入力しているセルを設定します。
既知のy:既に分かっている販売数量のデータ範囲(D4:D18)を設定します。
この表はテーブルに設定しているため、このような列のタイトル表示になります。
既知のx:既に分かっている気温のデータ範囲(C4:C18)を設定します。
20170320_05.JPG

OKするとこのように予測値が“364.45”と計算されました。

予想原理について
このFORECAST関数は回帰直線を使って値を予測しています。
回帰直線とは近似直線のことで近似式は
y=ax+b
となり、この近似直線と実際の各データの誤差の二乗が最小になる時のaとbを求めています。これを最小二乗法と言います。
Excelでの最小二乗法の計算についてはコチラをご覧ください。

んで、Excelではグラフで簡単に近似直線(曲線も)を描くことが出来ます。

プロットされているデータをクリック(選択)して、右クリします。
メニューから「近似曲線の追加」を選択します。
20170320_07.JPG

近似曲線の書式設定が開きます。
「近似または回帰の種類」を「線形近似」にします。
「グラフに数式を表示する」のチェックを入れます。
それから、35℃の予測も確認したいので、「前方補外」を“2”にします。
「前方」とは将来のこと。ここではグラフの右側つまり気温が高い方。
「後方」とは過去のことの予測になります。
20170320_08.JPG

んで、グラフの体裁を整えて、回帰直線(近似直線)はこうなります。
この直線の式も表示されています。
20170320_09.JPG

気温35℃の時の販売数量は360位になっているのが分かります。
ちなみにこの式のxに35を入れて計算するとyはFORECAST関数で計算した値とほぼ一致します。



関連ページ
ソルバーを使った最小二乗法
関数一覧(カテゴリ別)
関数一覧(名前別)


ラベル:forecast
posted by haku1569 at 21:03| Excel関数 | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。