過去の「似た動き」から次期を予測する、
Excelで実践する最近隣法のアプローチ。

ある販売担当部員の直近 24 か月の月別売上を集計したデータです。「期」列はデータ数を把握しやすくするための補助列で、処理上は必須ではありません。このデータから 25 期目（9 月）の売上予測値を求めるのが目的です。

「売上」列の右隣から順に以下の見出しを追加します。これらは計算プロセスを段階的に分類した列です。

• 2 期前の売上
• 1 期前の売上
• 距離
• 距離の逆数
• 順位
• ウエイト
• 売上×ウエイト

「2 期前の売上」「1 期前の売上」の両列を、3 期目から「売上」列を転記する式で入力します。両列の式を表の最下行より 1 行はみ出るところまでコピーします。

さて「距離」列の式は図のような構造を用意していきます。

「距離」はユークリッド距離——2 期前と 1 期前の値がつくる"形"と、過去の各時点の"形"との直線距離。値が小さいほど直近パターンに近しい。

これは図の直近パターン（赤い線）ライクな動きを探しています。

イメージとしては直前の赤い線の両端に図の上段のように線を引き、左端から順にそれらから売上までの距離をペアで走査していきます（図の中段）。このとき，黒い線がその「距離」を示します。

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

最後にルートをとって単位を戻したものが「距離」です。

では具体的に 3 期目のセルから式を入力し、「1 期前の売上」列の最下行と同じ行までコピーします。

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

「距離の逆数」列を計算します。距離が小さいほど重みを大きくするため逆数をとります。3 期目から 24 期の行までコピーします。

1 ÷ 距離

• =1/F4

「順位」列を計算します。距離の昇順でランクを付けます。ランクの対象範囲に距離がゼロのセルは含めません。

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

より厳密には同順位対策も必要となりますが、実務的には希と思われることから RANK 関数で処理しています。

表の右端に 採用期数 と初期値 6 を入力します（セル L1, L2）。

採用期数をもとに次の処理を行います。①順位の昇順で採用期数分のレコードを特定し、②該当レコードの売上に重みを掛けて合成します。

「ウエイト」列を計算します（初期状態では何も表示されませんが正常な処理です）。

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

式の構造は下図のとおり——「順位」が採用期数以下なら距離の逆数を按分してウエイトを算出し、そうでなければ空白処理します。

「売上×ウエイト」列を計算します。ウエイト列にデータがない行ではエラーが発生するため IFERROR 関数でガードします（初期状態では何も表示されませんが正常な処理です）。

• =IFERROR(C4*I4, "")

（IFERROR 関数 — "Office"）

「期」「月」列の最終行の 1 つ下に「25」「9 月」を入力します。

25 期目の「売上」列に、「売上×ウエイト」列の上方 3 行を除く全範囲を合計する式を入力します。

• =SUM(J4:J25)

この値を 25 期目の予測値と見立てます。実測値との混同を防ぐため、背景色やフォント色で区別しておくことを推奨します。

最近隣法による次期予測が完成しました。

セル L2 の採用期数を変えると、ウエイト・予測値がすべて連動して更新されます。下のグラフは採用期数に応じた誤差をプロットしたものです。今回採用した 6 期付近でこの例における誤差の最小水準にほぼ到達しており、「6」の妥当性が裏付けられます。

採用期数を変えるとウエイト・予測値が連動する。「何期採用すべきか」は先期データで誤差を検証して判断する——移動平均法の区間選択と同じ発想。

もう 1 期前（7 月）での同様の検証と、両月を重ねたグラフです。7 月は 8 月より少ない期数で誤差の最小水準に達する一方、期数「6」ではその前後より 1〜1.5 ポイント誤差が上昇しました。これらの検証から、この例では 6 を含む前後の期数の選択と相性がよいことが示唆されます。