Excelの関数で、売上や来客数の先を予測できる？

日付と数値の2列があれば、先の値を計算できる

Excelには、過去の記録から先の値を計算する関数がいくつかあります。売上や来客数のように、日によって増えたり減ったりしながら曜日ごとの波がある数字に使えるのが、FORECAST.ETS関数です。

Microsoftの関数の説明では、過去の値をもとに、指数平滑法（ETS）のAAA版という計算方法で先の値を出すと書かれています。指数平滑法は、新しい記録ほど重みを大きくして平均をとっていく方法です。同じ資料は使いみちとして、将来の売上、必要な在庫、消費の傾向の予測を挙げています。

機械学習のための道具や開発の作業は要りません。手元の表計算ソフトで、予測とはどんな数字が出てくるものかを試せるのが、この関数の良いところです。

表の作り方と、入れる式

表はA列に日付、B列にその日の販売数や来客数を1日1行で並べます。たとえば2行目から85行目までの84日分（12週分）があり、86行目のA列に予測したい翌日の日付を入れたとします。このとき、B86に入れる式は次のとおりです。

=FORECAST.ETS(A86, B$2:B$85, A$2:A$85, 7)

1つ目が予測したい日付、2つ目が過去の数値、3つ目が過去の日付です。4つ目の7は、7日ごとに繰り返す波があるとして計算する指定で、省くとExcelが繰り返しの長さを自動で探します。87行目以降にも日付を足し、同じ式を下へ写せば、数日先まで並べられます。

同じ資料によると、予測したい日付が過去の日付より前だとエラーになります。日付の列は並べ替えておかなくても計算されますが、日付の列と数値の列の行数が違う場合はエラーになります。

日付は一定の間隔で並べる。休んだ日の扱いを先に決める

この関数は、日付が一定の間隔で並んでいることを前提にしています。Microsoftの資料は、毎月1日の月別の記録、年別の記録などを例に挙げ、細かな記録はあらかじめまとめてから予測するほうが良い結果になると書いています。レジの記録が1件ずつ並んでいるなら、先に1日1行の合計にしてから渡します。

同じ日付の行が複数あると、既定ではその平均でまとめられます。合計して見たい販売数が平均されると数字が小さくなるので、自分で合計を出してから渡すほうが確実です。

日付に欠けがあっても、全体の30%までなら計算できます。既定では、欠けた日を前後の値の平均で補います。5つ目の引数に0を指定すると、欠けた日を0として扱います。欠けが30%を超えるとエラーになります。

臨時休業の日をどう扱うかで、結果が変わります。その日の行を外せば前後の平均で補われ、販売数0のまま残せば「売れなかった日」として計算に入ります。営業していない日を0のまま入れると、予測は低めに出ます。休業日や返品の整理は売上データは発注の目安にそのまま使えるかで取り上げています。

曜日の波は、自動で探すか7を指定する

4つ目の引数を省くと、Excelが記録の中から繰り返しの長さを探して使います。0なら繰り返しを使わず、正の整数なら指定した長さを使います。指定できる上限は8,760（1年の時間数）です。

自動で見つかった長さは、FORECAST.ETS.SEASONALITY関数で確かめられます。Microsoftの説明では、同じ記録を渡せば、FORECAST.ETSが使った長さと同じ値が返るとされています。日別の記録で7が返れば、曜日の波を見つけたことになります。

曜日で売れ方が違うと分かっているのに7以外の数が返ってきたときは、記録の期間が短いか、催しや休業で波が乱れている可能性があります。そのときは7を指定した結果と、自動の結果を並べて比べます。

予測の幅をFORECAST.ETS.CONFINTで出す

予測値は1つの数字で出ますが、実績はその上下にばらつきます。どれくらいの幅を見込むかは、FORECAST.ETS.CONFINT関数で出せます。先ほどの表なら、=FORECAST.ETS.CONFINT(A86, B$2:B$85, A$2:A$85, 0.95, 7) のように、4つ目に信頼度、5つ目に繰り返しの長さを入れます。

Microsoftの説明では、信頼度95%の幅とは、将来の値の95%が予測値からこの幅の中に入ると見込む（ばらつきが正規分布に従うとして）という意味で、信頼度を省くと95%になります。返ってくるのは片側の幅なので、予測値から引いた数が下限、足した数が上限です。

幅が広いほど、その日の予測には自信がないということです。幅のどちら側に寄せて発注するかは、品切れと売れ残りのどちらが痛いかで変わります。考え方は発注は多めにするか少なめにするかにまとめています。

直近の数週間を隠して予測し、実績と比べる

予測の式が入ったら、そのまま使い始める前に一度試します。当社がおすすめしているのは、直近の4週間分を隠して、それより前の記録だけで4週間分を予測し、隠した実績と並べる方法です。日付と予測と実績を3列にして、差の列を足すと、どの曜日や日に大きく外れたかが見えます。

比べる相手として、同じ曜日の直近数週の平均も並べておくと判断しやすくなります。関数の予測が平均とほとんど変わらないなら、手間をかけて関数を使う理由は小さくなります。平均の出し方は発注量の計算はExcelでどこまでできるかで紹介しています。

誤差を数字で見たいときは、FORECAST.ETS.STAT関数で、MAEやRMSEといった誤差の指標も返せます。こうした数字が何を表しているかはAIの「精度95%」は何を表している？を参考にしてください。

関数で扱えないこと

FORECAST.ETSが受け取るのは、過去の数値と日付の並びだけです。天気、気温、近くの催し、特売のような手がかりを足して計算させる引数はありません。雨の日に客足が落ちる店でも、関数は過去の並びの続きとして数字を出します。

同じExcelのFORECAST.LINEAR関数は、過去の値に直線を当てはめて先の値を出します。Microsoftの説明では線形回帰を使うとされ、曜日の波は扱いません。増えているか減っているかの傾向を見るのには使えますが、日ごとの数量を出すならFORECAST.ETSのほうが向いています。

品目が多くなると、品目ごとに式を作って毎日の記録を貼り足す作業が増えます。天気や催しを手がかりに入れたい、何十品目もまとめて予測したい、という段階になったら、機械学習を使った予測を考える時期です。手がかりを増やすときの注意はAIに手がかりを全部入れれば、予測は良くなる？で説明しています。

まず1品目で、予測と幅を並べてみる

始めるときは、売上の大きい1品目について、日別の記録を2〜3か月分並べ、翌週の7日分を予測と幅つきで出してみてください。お使いのExcelで関数名を入力しても候補に出ない場合は、その版ではこの関数を使えない可能性があります。

翌週が終わったら、予測・幅・実績を並べます。幅の外に出た日があれば、その日に何があったかを1行メモしておくと、関数では扱えない手がかりが何かが分かってきます。そのメモは、あとで予測の仕組みを作るときにも役に立ちます。