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

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

How-to

指数平滑法による
単純予測

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

次期の予測値として,この方法では直前の実測値に代え,指数平滑移動平均を充てていきます。

平滑化,という手続きを挟む点で,原理的には移動平均法と重なる部分の多い手法です。最も大きな違いといえば,やはり

  • 移動平均法:先行する各期の実測値は,扱いの上で対等(たとえば,6ヵ月の売上の移動平均をとるなら,先行する6ヵ月の各月のデータは同じ重要さを持つと考える)
  • 指数平滑法:同,扱いの上で直近のデータほど重きを置かれる(過去に向かって重要さは指数関数的に減少:文中で触れます)。

といった点をあげるべきでしょう。

さらに移動平均法に対する指数平滑法のメリットは,

  • より少ないサイズ(データの数)でも予測というアクションを起こすことができる。

ことに尽きるかと思います。

また,ここでの例のように,最初の予測値=1期目の実測値 として処理を進めた場合(step 7),

  • 計算に利用する観測値のmax.を超える,あるいはmin.を下回る値を予測値として出すことはできない。

といった移動平均法の場合と同様の制限を含みます。

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

元データ

綾子

1

元のデータです。

ある販売担当部員のここ1年の売上実績を月ごとに集計したものです。左の「期」列はデータ数を分かりやすくするため便宜的に挿入したものです。処理上どうしても必要なものというわけではありません。

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

なお,すぐに項目を追加するので,表の上部に1行分の空白行を残しておいた方がbetterです。


元データ・指数平滑法

9パターンのαを想定する

綾子

2

あたらしく見出しを作り,値を入力します。

下のように「α」および「0.1」を入力し(ここでは順に セルD1, E1),その下の行に見出し「予測値」と「絶対誤差」(ここでは順に セルD2, E2)を作ります。

すべて終えたら,これらを右に1ブロック分(2列)だけコピーします。

scrollable

[セルD1]α, [セルE1]0.1, [セルD2]予測値, [セルE2]絶対誤差。セル範囲D1:E2を右に1ブロック分コピー

綾子

3

あたらしくコピーされた方のブロックについて,値部分を修正します。

具体的には,下のように前のブロックのαの値に0.1だけ加える式に書き換えます。

  • =E1+0.1

scrollable

[セルG1]=E1+0.1

綾子

4

αの値が0.2のブロックを選択し(4つのセル),これをαの値として0.9となるブロックができるまで(残り7ブロック分)右方にコピーします。

この例では,U列までのコピーによってすべてのブロックを用意することができます。

scrollable

セル範囲F1:G2を右に7ブロック分コピー

予測式にあてはめてみる

綾子

5

では以降,各々のブロックごとに予測値と絶対誤差を計算していきます。

まずは次の期の予測値についてですがこれは下の上段の式で計算します。

ただ,ことばでこれを示すのも以下冗長かとも思いますので,ここではFtt期の予測値,Xtt期の実測値として,下の下段のような表現を使いたいと思います。

説明のみ

t+1期の予測値=平滑定数α×t期の実測値+(1-α)×t期の予測値)

F_(t+1)=αX_t+(1-α)F_t

綾子

6

「α(アルファ)」は平滑(化)定数と呼ばれ,ある意味,この手法のキモとなる要素で“重み(以下「ウエイト」)”の役割を果たします。

またこのαは,0 < α < 1の範囲をとります。そこで先にα=0.1~0.9まで総当たりで計算するため,9つのブロックを作っておいたというわけです。

指数平滑法を扱う以上,このウエイトの部分をスルーして手続きを追っても,発表などで数字の背景について説明を求められたとき,あわあわするのが関の山なので,以降,適宜この話に触れていきたいと思います。

というわけでここでもちらっとαについて眺めておきたいのですが,先のstepで掲げた式ではα2か所差しこまれているのがわかります。前段がXtに掛かるα,後段がFtに掛かる(1-α)です。

つまり片方に掛かるウエイトが増えれば,もう片方のそれが減るといった関係にあることがわかります。

仮に先のルールを曲げるとしてαが境界の値をとることができたとして話を進めると,Xtαに全振りした場合(α=1)には(次の)予測値=(直前の)実測値となりナイーブな方法と変わりません。反対にFtに全振りした場合(α=0)には,(次の)予測値=(直前の)予測値といったこちらも(意味があるかどうかは別として)ごく単純な予測のアルゴリズムとなることがわかります。

すなわちウエイトαの値の大小は,次期の予測をおこなうにあたっての,当期の実測値により重きを置くか,それとも(当期の「実測値」に対応する)予測値にそれをより置くのか,を左右します。

説明のみ

綾子

7

では具体的な手続きに戻ります。

とはいえ,step 5式において予測には先の期の予測値が必要とされました。ここで1期目については予測値が存在せず,ゆえに当然2期目の予測ができないことがわかります。したがって新規で予測をつくる場合はこうした初期値の設定がいくつかの方法で必要となりますが,この例では,2期目の予測値にはそのまま1期目の実測値を転用するといった,ごく単純な方法をとっていきます。

なお,後にコピーすることを考慮して,これは絶対参照としておきます。

  • =$C$3

[セルD4]=$C$3

綾子

8

これ以降はstep 5式をそのまま利用することができます。

具体的に,α=0.1のときの3期目の式は,

  • =E$1*$C4+(1-E$1)*D4

となります。こちらもコピーすることを考慮して,C4のセルとE1のセルについては複合参照(実測値の“列”αの値の“行”についてのみ固定)にしておきます。


入力できたら,この式を表の最下行より1つはみ出るところまでコピーします。

[セルD5]=($C4-D4)*E$1+D4。この式をセルD15までコピー

綾子

9

一旦手続きをお休みして上での作業を振り返ってみます。

時間の経過においてもっともあたらしい期,すなわち第12期をtとしたとき,次の期の予測値Ft+1step 5式に則って,t期の実測値,および予測値にそれぞれウエイトを乗せて下の上段の図のように求めました。

t期以前の予測値についてもこの構成は同じであって,これらをすべて示せば下の下段の図のようにあらわすことができます(ただし第2期のみ第1期のXを流用)。

説明のみ

綾子

10

こうして細かに見ていくと,下のように緑色で彩色した,連綿とした流れがあることに気づきます。

すなわち過去におこなった予測について,程度の差こそあれ(後述)すべての結果を取り込むかたちでFt+1の計算がおこなわれていることがわかります。

またXについてはどうかといえば,直前の期のそれのみが参照される構造のようです。したがって,移動平均法とは対照的に,ある程度のサイズのXを揃えられなくても計算そのものは可能であることがわかります。

説明のみ

綾子

11

上でお話ししたいくつかのことより,おのずから次のことが見えてきます。

  1. α が 0 に近づくほど,過去からおこなってきた一連の予測,すなわち「連綿とした流れ」にウエイトを置く。
  2. 反対に α が 1 に近づくほど「連綿とした流れ」に向かう関心は相対的に軽くなり,転じて当期の実測値,つまり変化に対する敏感さ,ワードを換えれば「フットワークの良さ」にウエイトを置く。
説明のみ

1.αが0に近づくほど過去からの一連の予測のプロセスが重視される 2.αが1に近づくほどt期の実測値,つまり変化に対する敏感さが重視される

綾子

12

上の2.について,当期の実測値を重要視することがなぜフットワークの良さにつながるのか,ここはいまいち理解のしがたい部分かもしれません。

そこで,step 5式を再掲します。

  • Ft+1=αXt+(1-α)Ft

この式をαでくくりなおして変形してやると,次の式を導くことができます。

  • Ft+1=α(Xt-Ft)+Ft

下の図にいうこの式の強調部分はXt-Ft,すなわち誤差に相当する部分です。この誤差にウエイトαを掛け,それを先期のFに加算して予測値をつくっていることがこの式では示されますが,ここでαが大きいほど,次の予測に誤差をそのまま組み込んで修正していくかたちになることがイメージできます。「フットワークの良さ」については,こうしたことを言っています。

ただし念のため,“フットワークが良い=すぐれた予測”になるわけでも,“連綿とした流れを大切にする=すぐれた予測”になるわけでもありません。この点については強調しておきたいと思います。

説明のみ

綾子

13

さて次に,step 10ではことばで掲げたにすぎなかった「連綿とした流れ」を,あらためて数式によってあらわしてみたいと思います。

まずは表の最下行,次期予測のFt+1は,step 10図からもわかるとおり

  • Ft+1=αXt+(1-α)Ft

でした。ここで末尾のFtは,同じようにstep 10図から

  • Ft=αXt-1+(1-α)Ft-1

であったので,これをそのまま最初の式に突っ込んでやると,下の上段の式が導けます。

これと同じことを,時間を戻すように1つずつ延々と遡ってつづけていくと,下の下段のような結果となります。

説明のみ

F_(t+1)=αX_t+α(1-α) X_(t-1)+α(1-α)^2 X_(t-2)+α(1-α)^3 X_(t-3)+α(1-α)^4 X_(t-4)+α(1-α)^5 X_(t-5)+α(1-α)^6 X_(t-6)+⋯

綾子

14

次期の予測値は,実のところウエイトが掛けられた当期から過去の各期の実測値Xを合成したものであることを見てとれます。

ここで再び注目したいのが,まさにその「ウエイト」です。

下の上段の図,緑で彩色して示した箇所のように,掛かってくるウエイトが各期のXでそれぞれ同じでないことに気づかされます。

ということでたとえばの話としてα0.5を投げてみたいのですがとりあえず,これについてウエイトα(1-α)α(1-α)2だけを求めてみると,下の下段の図のような値が返ってきます。

説明のみ

α=0.5のとき,α(1-α)=0.25,α(1-α)^2=0.125

綾子

15

こうしてXに掛かるすべてのウエイトを求め,グラフにプロットしていくと下のような図が出来上がります。

  • ウエイトは,過去に向かって指数関数的に減少していく。

まさにこの特徴が「指数」平滑法という呼称の由来となっています。このように,指数平滑法ではより近くのXから相対的に重要とされる扱いを受けていきます。

説明のみ

誤差を計算しておく

綾子

16

これ以降,具体的な作業に戻ります。

ここでは,絶対誤差を求めます。

式は =ABS(実測値-予測値) です。具体的には

  • =ABS($C4-D4)

と入力します。ここでも,実測値「売上」の“列”(ここではC列)については,コピーすることをふまえて固定しておきます(複合参照)。

入力できたら,この式を表の最下行までコピーします。

誤差の計算…[セルE4]=ABS($C4-D4)。この式をセルE14までコピー

綾子

17

先ほど計算式を入力した領域を選択し(下の図のハイライトの部分),αの値が0.9となるブロック(このケースではU列)まで一気にコピーします。

scrollable

セル範囲D4:E15を右に8ブロック分コピー

予測値として採用する値を絞り込む

綾子

18

予測ですから13期,ここでいう9月の行見出しを下のように用意しておきます。

すなわち青の着色部分(計9個。下の図は一部のみ)の値が次期の予測値(この時点では候補)ということになります

[セルA15]13, [セルB15]9月

綾子

19

ここより,αの値の分だけ計算した9個の予測値のなかから,よりフィットしそうだと思われる値を絞り込んでいくためのしくみをととのえていきます。


その第一として,下のような見出しと値を入力しておきます(3ヵ所)。

なお,ここでいう「区間」とは,絶対誤差の平均を求めるにあたって対象として組み入れる区間(期数)のことです。ここでは,とりあえずの数字として「3」と入力しておきました。

[セルA17]区間, [セルB17]3, [セルD17]誤差の平均

綾子

20

第二にα=0.1のときの誤差の平均を計算します。

見出し「誤差の平均」のすぐ右のセル(ここではセルE17)に,次の計算式を入力します。

  • =AVERAGE(OFFSET(E14, 0, 0, $B$17*-1, 1))

この構造の式は別頁「移動平均法による単純予測 with Excel」でも使用しています。関数の役割など仔細についてはそちらで触れていますので,必要があればリンク先にて確認ください。

scrollable

平均誤差の計算…[セルE17]=AVERAGE(OFFSET(E14,0,0,$B$17*-1,1))

綾子

21

上で入力した計算式とその1つ右の空白セルを選択し,αの値が0.9となるブロック(この例ではU列)までコピーします。

scrollable

セル範囲E17:F17を右に8ブロック分コピー

綾子

22

指数平滑法による次期の予測売上 および 各平滑定数(α=0.1~0.9)を採用した場合の誤差の平均について計算できました。

表としては以上で完成です。

FINISHED

指数平滑法による需要予測の完了

綾子

23

ここから少しTipsを加えます。

シートの「区間」の値を変更する都度,誤差の平均について再計算がおこなわれます。式の修正を必要としないので,適当と思われる区間を推量していく際に,いろいろと数字を変えてサクサクと検討できるかと思います。

Tips)誤差平均、区間の変更

綾子

24

たとえば,直近の6期(区間6)における誤差のみを考慮に入れたい(重要視したい)場合,その平均が最も小さくなっているものを探すと,α=0.3のブロックのものであることがわかります(青色の着色部分)。このα=0.3としたときの13期目の値(緑色の着色部分)を,次期の予測値として採用するといったことが可能です。


いつまで遡って誤差を考慮に入れるかつまり期数については一概に言えるものではないですが,移動平均法と違い そもそもいくらか前のXのもつ影響力はほぼ無視される程度となるので,そうした点を鑑みれば必ずしもすべての期間でとらなければならない理由もないと思います。この例のように11期分の誤差を求めた場合,現実的なその判断の場面では半数程度を加味すれば十分となることが多いかもしれません。もちろん,判断に迷えばすべての期をとりいれて計ってやってもよいかと思います。

scrollable

Tips)予測値の決定

あわせてグラフを作る場合

綾子

25

それからグラフが必要な場合にはB, C2列と目的のαの「予測値」列とを選択して,移動平均法と同様折れ線グラフで描画します。

Tips)指数移動平均のグラフ

綾子

26

参考までに,上の手続きプラスアルファで,たとえば次のようなグラフを作ってみました。

実測値(実線)に対して異なるαによる予測値を並べています(破線)。この頁の中盤あたりで見たとおり,αの値が小さな方がより大きなそれよりも,より平滑化されていることをうかがい知ることができます(「連綿とした流れ」に重きが置かれる)。

α=0.3と0.6の場合の指数平滑移動平均線の比較

ソルバーによるαへのアプローチ

綾子

27

最後に,αの求め方についてはソルバーを利用する方法もあります。

具体的には下の上段の図のような設定で,誤差が最小となるαの値を0<α<1という制約の中からソルバー機能によって探索させ(ただしソルバーの仕様にもとづいて,下図では左記の制約を曲げ両端を含む条件を与えています),このページでの作例で導いたαよりさらに精緻なαの値を,下の下段の図のように求めることができます。

この場合,予測値と誤差の列は1ブロックだけ用意すればいいといった点では効率的です。ただアドインが導入できるor稼働している環境であることが前提となりますし,複数のアウトプットが必要な場合や区間を変化させた場合には都度ソルバーを走らせる必要に迫られるので,シートの再計算についての利便性を重視したい場合には,なじみがたい方法にもなりえます。

scrollable

Tips)ソルバーによるαの決定

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

LastUpdate

2017.5.7

.
このページの先頭へ