2023/7/10

イントロダクション

「ナイーブ(naïve)な予測」とは,次の1期の定量的な予測値を用意する必要に迫られたとき,直前の実測値をそのままスライドさせて“予測値”に充てる方法を指します。ここで扱う方法はその単純さを踏襲しつつも,「そのまま」ではなく移動平均を使います。

READ MORE

念のため,ここにいう「ナイーブ」は,英語圏でいうネガティブなニュアンスを保持したものです。予測という行動にシャープな論理性を同伴させる目的では薄弱ですが,裏を返せば取っ掛かり易いシンプルなルールであるので,ユーザーが他にノウハウを所持していなければ,もっとも利用しやすい部類の方法と言えます。

ただ,ナイーブなアプローチをとるにしろ,いかんせんここで扱う売上のようなデータは,変動要素(スパンによっては季節変動, あるいは無作為な変動)を含むのが常であって,ときに許容しがたい誤差を抱えることがあります。単純な方法をとる以上「それも止む無し」といえばそれまでですが,どうせならそうした変動の影響力を少しでも弱められるにこしたことはありません。この手段として,「移動平均」をとる平滑化のプロセスを介在させます。

Excelでの具体的な手続きとしては,年・四半期・月あるいは日といった単位で束にした任意のスパンで,このスパンの単純な平均をとります。そしてこのスパンを直近に向かって1単位ずつずらすことでグラフの上に移動平均線を描き,それが示すところの終端の値を読み取ります。ここで「任意のスパン」については,しくみの上で可変としていきたいと思います。

なおこの手法を利用にするあたっては,次のような制限を含みます。

  • 移動平均をとる区間と同じ分量のデータが先行するデータに不可欠(たとえば,6ヵ月移動平均をとるなら先行する6ヵ月分のデータが必要)。
  • 計算に利用する実測値のmaxを超える,あるいはminを下回る値を予測値として出すことはできない。

またどのような予測法であれ同じですが,これが分析環境において有効な方法となりうるかは,他の手法でも重ねてシミュレートするなどの多方面からの検討を抜きに断ずることは難しいと考えます。

以下,Excelによる移動平均法を使ったナイーブな予測の流れです。ここでは一連の手続きを Excel 2016 で追っています。一部ボタンの配置や名称などが異なる箇所がありますが(この場合,可能であれば当該箇所に明記します),手続きそのものは,「永続ライセンス版」にいうところの Excel 2019, Excel 2013 あるいは Excel 2010,そして,「Office365版」の Excel (本頁更新時点のver.1905)でも基本的には同じです。

綾子

元データ

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

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

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

元データ・予測手法としての移動平均法

区間を可変にするしくみをつくる

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

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

[セルD1]移動平均, [セルE1]相対距離

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

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

[セルG3]区間, [セルG4]6

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

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

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

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

計算式の構造(AVERAGE関数,OFFSET関数の役割)

(Row関数 ―"Office")

(Offset関数 ―"Office")

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

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

OFFSET関数の指定範囲

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

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

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

計算式の構造(ROW関数,NA()の説明とIF関数による分岐の説明)

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

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

[セルD2]式をセルD25までコピー

移動平均と実測値との距離を求めてみる

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

見出し直下のセルに

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

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

  • =ABS(C2-D2)

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

相対距離(相対誤差)の計算…[セルE3]=ABS((C2-D3)/C2)。この式をセルE25までコピー

直近の移動平均を予測値として利用する

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

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

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

移動平均表の完成と次期の予測値

「折れ線」から直感的に動向をつかむ

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

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

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

挿入→折れ線
DIFFERENT VERSIONS

2013: 挿入タブグラフグループにある折れ線グラフの挿入ボタン

2010: 挿入タブグラフグループにある折れ線ボタン

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

マーカー付き折れ線

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

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

この時点でのグラフのイメージ

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

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

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

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

デザイン→グラフの種類の変更

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

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

「グラフの種類の変更」ダイアログ…折れ線
DIFFERENT VERSIONS

2010: 折れ線グループにある折れ線ボタン

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

移動平均グラフの完成

あてはまりのいい区間の数を探ってみる

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

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

Tips)移動平均の区間変更に応じた予測値の変化

区間の数に係わる判断の一助としては,直近数期の絶対距離であったり,計算可能な全区間のそれの平均(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な選択であったことを窺えます。