Excelによる、移動平均法を使ったナイーブな予測。

元のデータです。ある販売担当部員のここ2年の売上を月ごとに集計したものです。

左の「期」列はデータの数を分かりやすくするため便宜的に挿入したものです。ですので処理上，なくてはならないもの！というわけではありません。

このデータより25期目(9月)の売上の予測値をつくるのが目的です。

では，あたらしく見出しを作ります。

移動平均列と絶対距離列です。「絶対距離」列は実測値と移動平均(ここでは予測値とするそれ)がどの程度離れているか確認するために利用します。

行・列ともに，表より少し離れたところにあたらしく見出し区間を作ります(ここではセルG3)。その直下のセルに，とりあえずの区間数として6と入力しておきます。

なぜこんな中途半端な位置にといいますと，このあと入力する移動平均の計算式が長くなってしまうので上に余白を作っておかないと式を入力するときに表示上干渉しちゃってセルの選択が難しくなっちゃうゆえの苦肉の策です

「移動平均」列の最初のセル(ここではセルD2)に次の計算式を入力します。

• =IF(ROW()>$G$4+1, AVERAGE(OFFSET(C2, -1, 0, $G$4*-1, 1)), NA())

この長ったらしい計算式の強調部分から説明します。

下の図のように，こちらはセルC2(相対参照)のすぐ真上のセルから，指定した区間の数の分だけ，上方に幅をとったセル範囲の平均を求めるといった構造です。

(Row関数 ―"Office")

(Offset関数 ―"Office")

あえて図示するとしたら，この領域は下の「基点」から「6つ」のセルを指して言います。

もっとも，4で最初に組み立てた計算式の場合，「基点」のセル(C1)の上方の5つのセルは存在しないことになります。おまけに見出しも存在するので，たとえば区間数が6の場合には，最低でも基点から上方に7つのセルが存在しないと，正しく計算が処理できないことがわかります。

そうした理由で，処理をIf関数で分岐させています。

これにより，当該行の数が区間数+1より大きなときには平均が，そうでなければN/A(Not Available, Not Applicable：データなし)が返ってきます。

なぜ空白処理("")ではなくて「N/A」を返すようにするのかといいますと，空白処理だとグラフを作るときに0(ゼロ)値でプロットされてしまうといったこの場合の不都合を回避するためです(グラフの見た目的にカッコワルイですし)。

と，いうことで先頭行ではN/Aが返ってきます。

つづけて，この式をそのまま表の最下行までコピーします。

「絶対距離」列の計算です。

見出し直下のセルに

(売上－移動平均)の絶対値

となる計算式を入力し，これを表の最下行までコピーします。

• =ABS(C2-D2)

中身はつまるところ，移動平均を“予測値”としたときの誤差そのものです。ただ，ここでのような，どちらかといえばアバウトな移動平均の使い方に誤差という呼称をあてるのは若干の躊躇もあって，便宜上「絶対距離」というワードを選好しています。

今回の目的は予測ですから25期，ここでいう9月の行を作ります。

そのうえで「移動平均」列の最下行の計算式を，さらにそのひとつ下のセルへとコピーします。

この値を次期の予測値，として見立てます。

以降グラフに加工します。

まず「月」列と「売上」列，さらに「移動平均」列を選択し

リボンの挿入タブグラフグループにある折れ線/面グラフの挿入ボタンをクリックします。

2-D折れ線グループのマーカー付き折れ線ボタンをクリックします。

いちおうの図が描けました。

ここで，たとえば縦軸の最小値を適当な値に固定し，下の図で言う網掛け部分を除いてやるとトレンドそのものもクッキリするかと思います。

また，これは任意の判断になりますが，ここではグラフの「移動平均」系列のマーカーを削除して，実測値(「売上」)との外形上の差異を明示的につけておきたいと考えます。

とりわけ2010以前のバージョンでは，デフォルトで吐き出されるグラフのそれは可読性に影響を与えるレベルに大きくなるので，わたし的にはオススメの作業です。

具体的には，グラフの"移動平均"系列をクリックして選択された状態にしておき

リボンのデザインタブ種類グループにあるグラフの種類の変更ボタンをクリックします。

グラフの種類の変更ダイアログが表示されます。

「移動平均」の"グラフの種類"を「折れ線」に変更(下図参照)しOKボタンをクリックします。

実測値，および移動平均グラフの完成です。

あとは区間数をいじれば表とグラフがサクサクと反応するハズです。

たとえば，区間3ヵ月を採用して予測を立てる場合，25期におけるそれは下のように75.3万円あまり，とみなすことができます。

区間の数に係わる判断の一助としては，直近数期の絶対距離であったり，計算可能な全区間のそれの平均(MAE: Mean Absolute Error)であったりと，任意の基準に依拠することができます。

たとえばMAEを求める場合は，

• =AVERAGE(OFFSET(E25, 0, 0, -$A$25+$G$4, 1))

とするなどで，区間の変更に連動するしくみを整えることができます。

この場合の，区間3のMAEは96349となりました。つまり，1期あたり9.6万円のズレが生じたわけです。

「ナイーブ予測だし，ま，こんなものかな」といった判断もまた人それぞれかとも思いますが，これに関してひとつ別の興味がわいてきました。ということで最後に，

• はたして余計なことをせずに，“直前値”そのまま・真の意味でのナイーブ予測をした方が，パフォーマンスがよかったかも知れない！？

について，確認してみたいと思います。

ということで，直前値で処理した場合のMAEを計算してみたところ，これは12.3万円となりました。

MAEのみを単純に比べれば，幸いにも移動平均の方がちょっとばかりズレを抑えられたようです。

また別の視点で，

• 実測値により近いものを“WIN”，遠い方を“LOSE”

として，WINのみの可視化をおこなったのが下の図です。

灰色の円が各期の実測値，その他が各期にWINとなった方の予測値です。このように，頻度からパフォーマンスを考えた場合にも，移動平均の方がbetterな選択であったことを窺えます。