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

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

How-to

予測手法としての

最近隣法

時系列分析手法としての最近隣法を扱います。最近隣法の特徴は、直近数期の連続するデータの “パターン” に注目して予測する…という点にあります。この “パターン” を蓄積された過去のデータと照らしあわせて、過去の適当な実測値に重みづけをおこない、その総和をとって予測値をみちびきます。

このような特徴から、最近隣法は一見して規則性の見られないようなデータに向く手法といわれます。逆に言えば、上昇あるいは下降傾向を描くことが躊躇なく識別可能な売上データなど、一定の傾向を持つデータに対しての適用は向かないようです。これは予測範囲が過去のデータの範囲内(=最大値・最小値を超える予測値が生まれない)という性質に起因します。

結局どのような予測法であれ同じですが、分析環境において有効な方法となりうるか、過去のデータを用いて誤差を検証したり、また他の手法でもシミュレートしてみるなど多方面からの検討を加えることが望まれるところのように思います。

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

元データ

晴花

1

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

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

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

元データ・最近隣法

直近の値・動きと近いパターンにアタリをつける

晴花

2

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

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

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

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

晴花

3

では、「2期前の売上」列と「1期前の売上」列をあわせて用意します。2 期前の売上・1 期前の売上 ともにデータが揃うところ…つまり 両列とも 3 期目からの入力とします。


ということで、下のように…「売上」列をそのまま転記する式を入力します。入力できたら、両列の計算式を表の最下行より 1つ はみだすところまでコピーします。

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

晴花

4

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

この列には、下の図のような構造の式を作成します。たとえば、ハイライト部分のセルでは、両列の最下行のデータ(絶対参照)から当該行のデータを差し引いた値を 2 乗したもの…いうなれば最下行を基準とした 2 乗誤差を求めます。さらにはそれらの値を足し合わせ、その平方根を成果(=単位を戻す)とする…といった構造です。

つまりここでの予測は、予測期時点での 2 期前・1 期前の実測値(D26, E26)を基準に、過去のある期時点でどれだけ似通ったパターンが見られたと言えるのかについて、それを F 列でおこなうようなある種の差の大きさに依拠して判断しようとする意図をもっています。

説明のみ

「距離」計算式の構造

晴花

5

では実際に「距離」列の上から 3 番目のセルから入力していきます。上のルールにしたがうと、計算式は

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

となります。入力できたら、これを「1期前の売上」列の最下行とおなじ行のセルまでコピーします。

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

晴花

6

距離の逆数」列を計算します。この列は “重みづけ” の根拠として利用します(ウエイト)。上から 3 番目のセルに = 1 ÷ 距離 となる計算式を入力し、これを 24 期の行までコピーします。

  • =1/F4

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

晴花

7

順位」列を計算します。ここでは、これまでのプロセスで計算した各期の距離に関して、昇順で順序をつけていきます。上から 3 番目のセルから、次の計算式

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

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

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

Rank関数 ―"Office")

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

注意をひく期の実測値を合成して予測値をつくる

晴花

8

ここで表の右端にいったん視点を移します。「売上×ウエイト」列の右方に 1 列あけて、あたらしい見出し「採用期数」と、そのとりあえずの値6を入力しておきます。

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

晴花

9

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

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

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

説明のみ

予測値計算のしくみ

晴花

10

では、「ウエイト」列を入力します。上から 3 番目のセルに

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

と入力します(このケースでは何も表示されませんが正しい処理です)。

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

Sumif関数 ―"Office")

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

晴花

11

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

ことばで示すと、「順位」列の値が「採用期数」で指定した値以下の順位であれば、下の着色部分の式でウエイト(係数)の計算をし、そうでなければ空白処理をする…といったところです。

説明のみ

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

晴花

12

次は「売上×ウエイト」列を入力します。これは見出しの文字のとおりの式で求めますので、上から 3 番目のセルに

  • =IFERROR(C4*I4, "")

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

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

Iferror関数 ―"Office")

説明のみ

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

晴花

13

見出し「」「」の最終行の 1 つ下の行に、それぞれ「25」「9月」と入力します。

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

晴花

14

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

具体的には

  • =SUM(J4:J25)

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

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

晴花

15

予測手法としての最近隣法による次期の予測売上額が求められました。表としては以上で完成です。

FINISHED

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

期数をいくつか試してみる

晴花

16

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


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

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

晴花

17

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

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

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

晴花

18

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

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

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

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


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

LastUpdate

2016.7.12

.
このページの先頭へ