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

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

How-to

予測手法としての

指数平滑法

(単純)指数平滑法は、短期的な予測によく利用される時系列分析法のひとつで、直近のデータにより高いウエイトをおいて移動平均を求めていく手法です。

同じ時系列分析法である 移動平均法 との違いは、次のような点にあります。

  • 移動平均法:先行する一定の期に等しくウエイトをおく(たとえば、6 ヵ月の売上の移動平均をとるなら、先行する 6 ヵ月の各月のデータは同じ重要さを持つと考える)→指数平滑法:直近のデータほど高いウエイトを与える(過去に向かって指数的に減少)。
  • 上を受け、移動平均法との比較においては、より少ないデータで予測(というアクションを起こすことが)できる。

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

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

点は移動平均法と同じです。

したがって指数平滑法による予測は、はっきりとしたトレンドや季節性が見られず、かつ過去の事情より直近の事情の方により強く影響されることが見込まれる場合に叶うかと思います。ただ、どのような予測法であれ同じですが、分析環境において有効な方法となりうるか他の手法と比較するなど多方面から検討を加えることが不可欠なのは、この手法も例外ではないでしょう。

以下、エクセルによる 指数平滑法にもとづく予測値の求め方です。ここでは一連の手続きを Excel 2010 で追っています。これは Excel 2007, Excel 2013 でも、さらには Excel 2016 でも変わりません。

元データ

綾子

1

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

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

すぐに項目を追加するので、表の上部に 1 行分の空白行を残しておいてください。

※ データはサイドバーのボタンからご利用いただけます。

元データ・指数平滑法

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

綾子

2

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

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

すべて作成したら、右に 1 ブロック分(2列)だけコピーします。

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

綾子

3

コピーによってあたらしく作成されたブロックの方の、値部分を修正します。

下のように、前のブロックの α の値に 0.1 だけ加える式をつくります。

  • =E1+0.1

[セルG1]=E1+0.1

綾子

4

α の値が 0.2 のブロックを選択し(4つのセル)、これを α の値として 0.9 となるブロックができるまで(残り 7 ブロック分)右方にコピーします(ちなみにこのケースでは U列 までのコピーですべてのブロックが用意できます)。

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

予測式にあてはめてみる

綾子

5

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

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

説明のみ

予測値の計算式=(1つ前の実測値-1つ前の予測値)×平滑定数α+1つ前の予測値)

綾子

6

この式の内容をことばで示すと「前の期の誤差に一定のウエイトをかけて、その値に 1つ前 の予測値を加えたものをあたらしい予測値とする」というカンジになります。さらに簡単に言えば、期を経るごとに誤差を顧みて ブラッシュアップした予測値を用意していく…といったイメージでしょうか …というわけで、ウエイトの役割をつかさどる、式中の「α」が予測のカギとなってきます。この α は平滑(化)定数と呼ばれます。

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

説明のみ

綾子

7

と言っても、1 期目は予測値が存在しないので 2 期目の予測値が計算できません。したがって 2 期目の予測値にはそのまま 1 期目の実測値[売上]を転記します。

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

  • =$C$3

[セルD4]=$C$3

綾子

8

では、先にかかげた式に合致するような、予測値を求める計算式を作成します。

具体的には、α=0.1 のときの 3 期目の予測値の式は、

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

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


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

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

誤差を計算しておく

綾子

9

つづいて 絶対誤差を求めます。

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

  • =ABS($C4-D4)

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

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

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

綾子

10

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

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

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

綾子

11

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

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

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

綾子

12

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


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

なお、ここでいう「区間」とは、平均誤差を計算するにあたり対象とする期数のことです。ここではとりあえずの数字として「3」と入力しておきました。

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

綾子

13

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

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

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

この構造の式は別頁「移動平均法による予測ツールの作成 with Excel」でも使用しています。関数の役割など詳細についてはそちらで触れていますので、必要があればリンク先をご覧ください。

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

綾子

14

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

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

綾子

15

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

FINISHED

指数平滑法による売上予測表の完成

綾子

16

ここから少し Tips 的なコトを説明させてください。

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

Tips)誤差平均区間の変更

綾子

17

たとえば、区間 6 期で誤差を確認する場合、その平均が最も小さくなっているものを探すと、α=0.3 のブロックのものであることがわかります(青色の着色部分)。この α=0.3 としたときの 13 期目の値(緑色の着色部分)を、次期の予測値として採用する…といったことが可能です。


いつまでさかのぼって誤差を考慮に入れるか…つまり期数については一概に言えるものではないですが、移動平均法と違い そもそも直近のデータにより大きな影響をうけるものなので多くとらなければならない理由もないと思います。このケースのように 11 期分の誤差を求めた場合、その検証には 3~6 期ほどとれば十分なことが多いのではないでしょうか。

scrollable

Tips)予測値の決定

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

綾子

18

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

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

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

綾子

19

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

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

scrollable

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


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

LastUpdate

2016.7.12

.
このページの先頭へ