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

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

How-to

最近隣法による単純予測

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

次期の予測値として,この方法では直前の実測値に代え,最近隣法によって作った合成値を充てていきます。

最近隣法では,直近の“パターン”に注目して予測をおこなうことを特徴とします。このパターンを過去のデータの蓄積から照らしあわせて,適当な実測値に重みづけをおこない,その総和をとって予測値をみちびきます(仔細は文中で触れます)。

また制約としては,別頁の単純予測(移動平均法指数平滑法)による場合と同様,

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

を抱えます。

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

元データ

晴花

1

元のデータです。

ある販売担当部員のここ2年の売上実績を月ごとに集計したものです。左の「期」列はデータのサイズを分かりやすくするため挿入したものです。処理にあたっては,絶対に必要なもの!といった性格のものではありません。

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


元データ・最近隣法

直近のパターンに似た過去のそれを探る

晴花

2

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

売上」列の右隣から順に,「2期前の売上」「1期前の売上」「距離」「距離の逆数」「順位」「ウエイト」「売上×ウエイト」と入力します。

ぶっちゃけ,これらの列は計算のプロセスを適度に分類しただけのものです。個別の列それぞれに独立して大きな情報を示すものではありませんので,見出しそのものもポイントを外した点があることは含めおいてください。

scrollable

[セルD1]2期前の売上, [セルE1]1期前の売上, [セルF1]距離, [セルG1]距離の逆数, [セルH1]順位, [セルI1]ウエイト, [セルJ1]売上×ウエイト

晴花

3

では,「2期前の売上」列と「1期前の売上」列をあわせて用意します。

2期前の売上・1期前の売上 ともにデータが揃うところつまり,両列とも3期目からの入力とします。

ということで,下のように「売上」列をそのまま転記する式を入力します。

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

[セルD4]=C2, [セルE4]=C3。この式をセルD26までコピー

晴花

4

次に「距離」列を計算します。

この列には,下の図のような構造の式を用意していきます。

説明のみ

「距離」計算式の構造

晴花

5

はたしてこれで何をしているのかと言えば,下の上段の図にいう赤い線ライクなパターンを探しています。

イメージとしては,この赤い線の両端に下の下段の図のように線を引き

説明のみ

晴花

6

それらから売上までの距離を,下の上段の図にいう左端から順にペアで走査していきます。このとき,黒い線がその「距離」を示します。

この距離は向きが異なると足し合わせた際に相殺されてしまうので,分散をはかるときのように2つまり下の下段の図のような黒い正方形の面積を考えればいいわけですが,これを足し合わせたものがどれだけ小さいかで,step 5の上段の図で掲げたパターンと近しいことを見当づけることができます。

なお,“面積”のままでは以下での扱いが面倒なので,ルートをとって最後に単位を戻します。

説明のみ

晴花

7

ということで上述の意図をかたちにしたものが下式です。これを「距離」列,上から3番目のセルから入力していきます。

  • =SQRT(($D$26-D4)^2+($E$26-E4)^2)

入力できたら,これを「1期前の売上」列の最下行と同じ行までコピーします。

scrollable

[セルF4]=SQRT(($D$26-D4)^2+($E$26-E4)^2)。この式をセルF26までコピー

晴花

8

距離の逆数」列を計算します。この列は“重み(ウエイト)”として利用します。

先に見たように,「距離」はパターンが近ければ小さくなる関係にあるので,これをそのまま重みに充てるとができません。そこで,この逆数をとることで大小関係を逆転させます。

具体的には,上から3番目のセルに = 1 ÷ 距離 となる次の計算式を入力し,これを24期の行までコピーします。

  • =1/F4

scrollable

[セルG4]=1/F4。この式をセルG25までコピー

晴花

9

順位」列を計算します。これにより近しいパターンのものをいくつか,順位(昇順)をもとに拾い上げるしくみの端緒とします。具体的には,上から3番目のセルに,次の計算式

  • =RANK(F4, $F$4:$F$25, 1)

を入力します。このとき,ランクづけの対象範囲は下の図でいうシート上の青の囲み部分となります。0(黄色のバツ印)のセルは含めません

入力できたら,式を24期の行までコピーします。

Rank関数 ―"Office")

scrollable

[セルH4]=RANK(F4,$F$4:$F$25,1)。この式をセルH25までコピー

候補より “次に測定された値” を拾って合成する

晴花

10

ここで表の右端にいったん視点を移します。

売上×ウエイト」列の右方に1列あけて,あたらしい見出し「採用期数」と,そのとりあえずの値6を入力しておきます。

[セルL1]採用期数, [セルL2]6

晴花

11

この「採用期数」をもとに,この後のプロセスで下の図のような処理ができるよう表をととのえていきます。

具体的には,まず採用期数の分だけ,予測に利用するレコード(行)を特定します。この特定にあたっては,順位の昇順(1位からの順)で決めていきます。

そして,該当するレコードの売上(実測値)に重みを掛け合わせ,最終的にそれらを合成することによって予測値を作成します。

説明のみ

予測値の計算のしくみ

晴花

12

ウエイト」列を計算します。上から3番目のセルに

  • =IF(H4<=$L$2, G4/SUMIF($H$4:$H$25, "<="&$L$2, $G$4:$G$25), "")

と入力します(この例では何も表示されませんが正常な処理です)。

入力できたら24期の行までコピーします。

Sumif関数 ―"Office")

scrollable

[セルI4]=IF(H4<=$L$2,G4/SUMIF($H$4:$H$25,'<='&$L$2,$G$4:$G$25),'')。この式をセルI25までコピー

晴花

13

この一見ややこしそうな式は次のような構造になっています。

ことばで示すと,「順位」列の値が「採用期数」で指定した値以下の順位であれば,下の着色部分の式で重みを案分し,そうでなければ空白処理をするといったところです。

説明のみ

「ウエイト」計算式の構造

晴花

14

残る「売上×ウエイト」列を入力します。これは見出しの文字の意味するところそのままの式で求めますので,上から3番目のセルに

  • =IFERROR(C4*I4, "")

と入力します。ただし,左隣の「ウエイト」列にデータがないときエラーが発生するのもこの場合都合が悪いので,Iferror関数で左隣のセルにデータが存在するときのみ表示されるようにしています。したがってこの例では何も表示されませんが正常な処理です

入力できたら,同じくこれを24期の行までコピーします。

Iferror関数 ―"Office")

scrollable

[セルJ4]=IFERROR(C4*I4,'')。この式をセルJ25までコピー

晴花

15

見出し「」「」の最終行の1つ下の行に,それぞれ「25」「9月」と入力します(=「次期」を用意する)。

[セルA26]25, [セルB26]9月

晴花

16

25期目の「売上」列に,「売上×ウエイト」列の上方2期を除くすべての範囲(下のシート上の緑の囲み部分)の値を合計する式を入力します。

具体的には

  • =SUM(J4:J25)

となります。この値を次期(25期)の予測値と見立てます。

実測値との混同を防ぐため,背景やフォントの色を変更するなどして区別をつけておいた方がいいかもしれません。あるいはコメントで注意を促してもいいかと思います。

[セルC26]=SUM(J4:J25)

晴花

17

最近隣法により次期の予測売上を以下の通りみちびきました。

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

FINISHED

最近隣法による売上予測の完了

任意の期数に変更する

晴花

18

あとは採用期数をいじれば「ウエイト」列,「売上×ウエイト」列,および予測値が連動します。それぞれの項目を確認しながら採用期数をケースバイケースで変更することになります。


ここからは少しTips的なコトですが

ケースバイケースとした採用期数について,ここで少し考えてみたいと思います。今回のケースでは採用期数6つで25期目の予測値を求めましたが,いまだ25期目の実測値(売上)は出ていないという想定のもとにあります。したがって実測値,予測値ともに用意できる直近のデータ,つまり第24期のデータを使って採用期数と誤差の関係を見てみたいと思います。

晴花

19

下のグラフが,その採用期数に応じた誤差をプロットしたものです。

グラフからは,今回採用した6期までにこの例における誤差の最も小さな水準にまでほぼ達していることがうかがえます。より少ない期を採用する理由はもちろんのこと,6期をこえる期をあえて採用しなければならない理由もなさそうです。その意味で,今回の所与の値とした期数「6」は,この例において妥当だと考えられる理由が1つできたのかもしれません。

Tips)誤差の検証(先期データ)

晴花

20

せっかくなので,もう1期前においても同様の検証をしてみます。7・8両月のグラフを重ね合わせたものが,下のグラフです。

7月は8月と比べより少ない採用期数でこの例における誤差の最も小さな水準にまでほぼ達したようには見えます。ただし,7月の場合,所与の期数とした「6」ではその前後の「5」や「7」と比べ11.5ポイント誤差が上昇する結果となりました。

以上の点から推し量ると,次月の売上を予測する今回の例では6を含むその前後の期数の選択と相性がよさそうです。

Tips)誤差の検証(先々期データ)

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

LastUpdate

2017.5.10

.
このページの先頭へ