Excel。分析ツールで新しいデータを重視した移動平均を『指数平滑法』で算出する
＜移動平均：指数平滑法＞
次のデータがあります。
歴代のデータから今度どのようになるのかを予測するには、「移動平均」を使ったりします。
比較的簡単に移動平均を算出するならば、『
分析ツール』を使ってみるのも一つの方法ですね。
C2をアクティブにしておいて、データタブのデータ分析をクリックすると、データ分析ダイアログボックスが表示されます。
移動平均を選択して、OKボタンをクリックします。
移動平均ダイアログボックスが表示されます。
入力範囲は、データ範囲になりますので、$B$2:$B$19。
区間は、3年移動平均で今回は算出しようと思いますので、3。
出力先は、$C$2に算出します。
そして、せっかくグラフが作成できるので、グラフ作成にチェックマークをいれて、OKボタンをクリックしましょう。
移動平均と移動平均グラフが表示されました。
C4の数式を確認しておきましょう。
=AVERAGE(B2:B4)
3区間分の範囲で平均を算出してくれているのがわかります。
そして、新年のデータ予測をしますので、C20をクリックして、数式を作ります。
=AVERAGE(B18:B19,C19)
このような方法でも、ある程度の予測値を算出することができます。
しかし、グラフからみてもわかるように、この近年数値が上がり気味です。
移動平均自体は、過去のデータを”ならしたもの”です。
そこで、新しいデータに比重をおいた『指数平滑法』を使ってみましょう。
【分析ツールで指数平滑を算出】
D2をアクティブにして、データ分析ダイアログボックスを表示します。
指数平滑を選択して、OKボタンをクリックします。
入力範囲と出力先は、先ほどの移動平均と内容は同じですが、減衰率が『
？』ですね。
指数平滑法は、
予測値=A×前回売上高＋（1－A）×前回予測値
この数式の（1－A）の値が減衰率になります。
Aが0～1の間の数値で指定して、1に近づけると直近の数値を重視して、0に近づけると過去のデータを重視することができます。
なので、今回は、0.6を設定することで1-0.6の結果0.4を意味します。
減衰率を変化させて数値を確認したいので、D3の数式を
=$B$2
D4の数式を、
=D$1*$B3+(1-D$1)*D3
と修正します。
そして、0.9から0.1まで減衰率を変更して結果を確認してみましょう。
あとは、予測値を算出します。
C20の数式を修正します。
=AVERAGE($B$18:$B$19,C19)
予測値は算出できましたが、減衰率のうち、どの精度が高いのかわかりません。
そこで、
SUMXMY2関数をつかって、残差平方和というのを算出していきます。
D21をクリックして、SUMXMY2関数ダイアログボックスを表示します。
配列1には、$B$3:$B$19
配列2には、D3:D19
OKボタンをクリックして、数式をオートフィルでコピーします。
この中で、最小値は減衰率が0.5の時です。
今回は、条件付き書式を使ってわかりやすくしました。
よって、予測値は、592.029を使うといいことになります。
移動平均と数値が違います。
このように、分析データを使うことで予測することが比較的容易になりますので、使ってみると色々見えてくるかもしれませんね。
