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

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

How-to

予測手法としての

移動平均法

売上(または販売)予測手法のひとつとしての移動平均法は、一定の区間(期間)を定め範囲をずらしながら平均をとっていくことで、規則的な変動要素(季節変動など)と不規則な変動要素(無作為変動)の影響を除いた推移をみちびき、近い将来の予測に役立てようとする手法です。

移動平均法による予測では、年・四半期・月あるいは日といった測定のスパンにしたがってあらたなデータを加えるたび、過去x期の平均をとる区間をひとつずつ移動させ、グラフ上の移動平均線の直近時点での位置を確認します(後方移動平均)。

この手法のその他の特徴として、次のようなことがあげられます。

  • 移動平均をとる区間と同じ分量のデータが先行するデータに不可欠(たとえば、6 ヵ月移動平均をとるなら先行する 6 ヵ月分のデータが必要)。
  • 計算に利用する観測値の max. を超える、あるいは min. を下回る値を予測値として出すことはできない。
  • 区間とする期数(年, 月, 日など同一単位からなる期間)を増やす(長くする)ほど、平滑化はよりすすむ。

以上の諸点は、この方法が単純な変動パターンを繰り返すようなデータの予測に向いていることを想起させます。ただ、短期と長期の視点ではトレンドの方向性も異なって見えるケースがあるように、予測としての移動平均法の成果は期数の選択に依存する部分も少なくないように思います。そうした判断の難しい部分もあり、実際に予測をせまられる現場においては、この予測手法は予備的な位置づけで利用した方が better かもしれません。結局どのような予測法であれ同じですが、分析環境において有効な方法となりうるか他の手法でもシミュレートするなど多方面から検討を加えることが、この手法にも不可欠となるのではないでしょうか。

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

元データ

綾子

1

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

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

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

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

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

綾子

2

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

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

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

綾子

3

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

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

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

綾子

4

移動平均」列の最初のセル(ここではセル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())

綾子

5

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

下の図のように、こちらはセルC2(相対参照)のすぐ真上のセルから、指定した区間の数のぶんだけ、上方に範囲をとったセル範囲の平均を求める…ということになります。

Row関数 ―"Office")

Offset関数 ―"Office")

説明のみ

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

綾子

6

簡単に言えば下の領域の平均です。もっとも、当該セル(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

相対距離」列を入力します。上から 2 番目のセルに「=ABS((直前期の売上-当該行の移動平均値)/直前期の売上)となる計算式を入力し、最下行までコピーします。

  • =ABS((C2-D3)/C2)

絶対誤差を実測値で除しているので、要するに中身は相対誤差です。ただ、ここでのような、どちらかといえばアバウトな移動平均の使い方に誤差という呼称をあてるのも迷いがあったので、ここでは便宜的に相対距離ということばに置き換えています。

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

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

綾子

10

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

そして、「移動平均」列の最下行の計算式をひとつ下のセルへコピーします。

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

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

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

綾子

11

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

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

綾子

12

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

2013

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

2016

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

挿入→折れ線

綾子

13

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

マーカー付き折れ線

綾子

14

いちおうの表ができました。たとえば縦軸の最小値を適当な値に固定し、下の図で言う網掛け部分を除いてやるとトレンドもより見やすくなるかもしれません。

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

綾子

15

グラフの「移動平均」系列のマーカーが可読性を落としているように思いますので、マーカーを削除します。

グラフの"移動平均"系列をクリックして選択された状態にしておきます。

綾子

16

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

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

綾子

17

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

折れ線グループにある折れ線ボタンをクリックします。

最後にOKボタンをクリックします。

2013

2016

移動平均の "グラフの種類" を「折れ線」に変更(下の図を参照)。

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

綾子

18

実測値(原系列)と予測値としての移動平均グラフの完成です。

FINISHED

移動平均グラフの完成

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

綾子

19

あとは区間数をいじれば表とグラフがサクサク反応します。数字を変えて直近の相対距離、あるいは一定期間の相対距離の和などで判断を補いながら、次期の移動平均の値を予測値として見立てます。

たとえば区間 3 ヵ月を採用して予測をすると、25 期における売上の予測値は下のように 75.3 万円あまりとなります。

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


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

LastUpdate

2016.7.12

.
このページの先頭へ