Excelの関数で既知のデータを元に未知の値を予想してみます。
FORECAST関数を使用します。
“FORECAST”(フォーキャスト)とは、予想する、予測する と言う意味です。
“forecast the weather”と言うと「天気予報する」です。
例えばこんな、あるイベントに出店したソフトクリームの屋台のその日の気温とソフトクリームの販売数量のデータがあります。
気温が高いと売り上げが上がるだろうと予測できますよね。

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

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

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

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

OKするとこのように予測値が“364.45”と計算されました。
予想原理についてこのFORECAST関数は回帰直線を使って値を予測しています。
回帰直線とは近似直線のことで近似式は
y=ax+b
となり、この近似直線と実際の各データの誤差の二乗が最小になる時のaとbを求めています。これを最小二乗法と言います。
Excelでの最小二乗法の計算についてはコチラをご覧ください。んで、Excelではグラフで簡単に近似直線(曲線も)を描くことが出来ます。
プロットされているデータをクリック(選択)して、右クリします。
メニューから「近似曲線の追加」を選択します。

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

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

気温35℃の時の販売数量は360位になっているのが分かります。
ちなみにこの式のxに35を入れて計算するとyはFORECAST関数で計算した値とほぼ一致します。
関連ページソルバーを使った最小二乗法関数一覧(カテゴリ別)関数一覧(名前別)
posted by haku1569 at 21:03|
Excel関数
|

|