直前値の「ブレ」をならして予測精度を上げる、
Excelで実践する移動平均法のつくり方。

ある販売担当部員の直近 2 年間の月別売上を集計したデータです。左の「期」列はデータ数を分かりやすくするための補助列で、処理上は必須ではありません。このデータから 25 期目（9 月）の売上予測値を求めるのが目的です。

列見出し 移動平均・絶対距離 を追加します。「絶対距離」列は実測値と移動平均（予測値）のズレを確認するために使います。

表から少し離れた位置（ここではセル G3）に見出し 区間 を作り、その直下に初期値 6 を入力します。表から距離を取る理由は、後で入力する移動平均の計算式が長くなるため、数式入力時にセルが重なって選択しにくくなる事態を避けるためです。

「移動平均」列の先頭セル（D2）に次の式を入力します。

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

式の核心は OFFSET 部分。「C2 のすぐ上のセルから、区間数だけ上方にさかのぼったセル範囲」の平均を返す。区間数が $G$4 に集約されているので、ここを変えるだけで全体が連動する。

OFFSET 部分の参照範囲を図示するとこうなります。基点セルの上方に区間数分のセルが必要です。

区間数が 6 の場合、基点から上方に 7 つ以上のセルが存在しないと正しく計算できません。これを IF 関数で分岐させています——当該行の数が「区間数＋1」より大きければ平均を、そうでなければ NA()（データなし）を返します。

空白処理（""）ではなく NA() にする理由は、空白だとグラフ上で 0 としてプロットされグラフが崩れるからです。

先頭行では NA() が返ります。この式を表の最下行までコピーします。

「絶対距離」列に、

（売上 − 移動平均）の絶対値

を求める式を入力し、最下行までコピーします。

• =ABS(C2-D2)

言うなれば、これは移動平均を予測値としたときの誤差となります。ただ、このアバウトな使い方に「誤差」という呼称をあてるのは若干の躊躇もあるため、ここでは「絶対距離」という言葉を選んでいます。

25 期（9 月）の行を作り、「移動平均」列の最下行の式をさらに 1 行下へコピーします。この値を次期の予測値として見立てます。

「月」列・「売上」列・「移動平均」列を選択します。

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

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

グラフが描けました。縦軸の最小値を適当な値に固定すると、トレンドがよりクッキリします。

任意の判断ですが、移動平均系列のマーカーを削除して実測値との視覚的な差異を明示します。グラフの "移動平均" 系列を選択した状態で……

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

「移動平均」のグラフ種類を 折れ線（マーカーなし）に変更して OK。

実測値と移動平均グラフの完成です。

セル G4 の区間数を変えると、表・グラフ・予測値がすべて連動して更新されます。たとえば区間 3 か月では、25 期の予測値は約 75.3 万円となります。

区間選択の判断基準として、直近数期の絶対距離や、計算可能な全区間の平均誤差（MAE: Mean Absolute Error）を使えます。区間変更に連動させるには、

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

のように式を組みます。

MAE だけでなく「直前値とどちらが実測値に近かったか」を期ごとに比べてみると、移動平均の優位性がより直感的に分かる。

区間 3 の MAE は 96,349 円、つまり 1 期あたり約 9.6 万円のズレでした。では真のナイーブ予測（直前値そのまま）と比べてどうか——計算すると直前値の MAE は 12.3 万円でした。移動平均の方が誤差を抑えられています。

さらに期ごとに「実測値により近い方を WIN」として可視化すると、下図のように移動平均の優位性を頻度でも確認できます（灰色の円が実測値、その他が各期の WIN 側の予測値）。