ひとりマーケティングのためのデータ分析

手順解説 | Excel(エクセル)でおこなうビジネスデータの分析

How-to

移動平均法による
単純予測

この方法は,次期のみの定量的な予測を立てるにあたって,直前の実測値をそのまま充てる「ナイーブ(naïve)な予測法」と呼ばれる考え方を下敷きとした方法です。

字義どおり,そのしくみ自体よく言えばシンプルそのもので,「予測」といったものを広義に考えるとき,取っ掛かりの容易さという意味では最も利用しやすい部類の手法とみていいかと思います。ただ,ナイーブなアプローチをとるにしろ,いかんせんここで扱う売上のようなデータは,不規則な変動要素(無作為変動)を含むのが常であって「直前の実測値を次期の予測値とする」規準をそのままに適用しても,(次期の)実測値とのズレも決して少なくはならないだろうことが予期されます。そこで,「移動平均」をとる平滑化のプロセスを挟むことで,そうした変動の影響力を少しでも弱めたい,といったことを主旨に据えることを考えます。

つまり,ここでとりあげる方法では,次期の予測値として,直前の実測値に代えるかたちで移動平均を充てていきます(したがって当然のことながら,これは関係式を想定する「説明変数モデル」とは異なります)。

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

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

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

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

以下,エクセルによる 移動平均法にもとづく予測値の求め方,およびグラフの作り方です。ここでは一連の手続きを Excel 2016 で追っています。Excel 2013,Excel 2010およびExcel 2007についても基本的には同じ流れとなりますが,一部ボタンの配置や名称などが異なる箇所があります(この場合,可能であれば当該箇所に明記します)。

元データ

綾子

1

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

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

このデータより25期目(9月)の売上の予測をおこなうのがここでの目的となります。


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

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

綾子

2

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

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

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

綾子

3

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

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

scrollable

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

綾子

4

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

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

scrollable

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

綾子

5

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

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

Row関数 ―"Office")

Offset関数 ―"Office")

説明のみ

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

綾子

6

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

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

説明のみ

OFFSET関数の指定範囲

綾子

7

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

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

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

説明のみ

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

綾子

8

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

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

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

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

綾子

9

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

見出し直下のセルに= 絶対値( 売上-移動平均 )となる計算式を入力し,これを表の最下行までコピーします。

  • =ABS(C2-D2)

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

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

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

綾子

10

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

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

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

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

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

綾子

11

ここからはグラフ化の工程です。

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

綾子

12

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

2013

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

2010

2007

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

挿入→折れ線

綾子

13

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

マーカー付き折れ線

綾子

14

いちおうの図ができあがりました。

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

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

綾子

15

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

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

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

綾子

16

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

scrollable

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

綾子

17

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

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


2010

2007

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

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

綾子

18

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

FINISHED

移動平均グラフの完成

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

綾子

19

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

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

scrollable

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

綾子

20

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

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

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

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

scrollable

綾子

21

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

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

  • 予測値を“移動平均”で代用したんだけど,はたして余計なことをせずに“直前値”で処理した方がパフォーマンスがよかったんかも!?

について,確認しておきたいと思います。

綾子

22

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

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

また別の視点で,

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

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

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

  • 本頁で使用したデータはすべて架空のものです。また特定の会社等に実在する人物名,および同場所で実際に観測されたデータ群などを根拠にしたものでもありません。
.

LastUpdate

2017.5.7

.
このページの先頭へ