【Excel】まるでAI?! 需要予測を司る驚異の関数★
こんにちは、HARUです。
今回は、関数を使ってデータの将来値を予測する方法を解説します。
実務においては、特定の商品やサービスの過去実績やその販売動向に影響を与える様々な外的環境の見通しから、製品やサービスの今後の需要を予測する場面が多く発生しますよね。
1年後、5年後、10年後といった中長期的な視点から、明日、来週、来月といった短期的なスパンまで、将来の動向を予測することは事業企画や生産供給計画の策定、商品・サービスの開発にあたって必要不可欠な要素です。
そこでこの記事では、諸条件をもとに将来値をサクッと求めてくれる便利な関数を、具体的に3つご紹介します。
あまり知られていないテーマではありますが、業績の未来予想を定量化するときに重宝できるアイテムですので、ぜひご一読ください！
3つの関数の構成と特長
本日使うのは、①TREND関数、②FORECAST関数、③GROWTH関数です。
それぞれの英語の意味は↓こちら↓です。
trend：傾向、動向
forecast：予報、予想、見通し
growth：成長、増大、拡張
今回はとあるサービスの直近7年の契約件数実績をもとに、2023年の予測値を求めるケースをサンプルにします。
（契約件数実績は3行とも同じ条件が入力されています）
TREND関数
TREND関数の構成は以下の通りです。
【=TREND(既知のy,[既知のx],[新しいx],[定数])】
統計学の世界では、求めたい「y」という値の動向に様々な影響を与える要素を「x」という変数で表します。
この場合の「y」は2023年のサービス契約数（予測値）なので、第１引数の「既知のy」すでに分かっているもの、要は2016年から2022年までの実績を指します。
そのため、B4～H4セルの範囲を選択します。
このサンプル表には、変数の役割となる「x」（販売動向に影響を与える外的要因）がありません。
※変数の効力については後半で解説します。
そのため第２引数の「既知のx」では一旦、B3～H3セルにある時系列（年度）の範囲を選択し、第３引数の「新しいx」はI3セルを参照します。
第４引数の「定数」は気にしなくてOKですので、[Enter]で決定します。
→"8,981"という値が返されました。
FORECAST関数
FORECAST関数を入力すると上位互換となる関数がいくつか出てきますが、単純に「FORECAST」と表記されている関数を選択されてください。
引数の順番は若干異なりますが、「y」や「x」にあたる要素を参照するのはTREND関数と同じです。
【=FORECAST(x,既知のy,既知のx)】
第１引数「x」：2023年（I3セル）
第２引数「既知のy」：直近7年の契約実績（B5～H5セル）
第３引数「既知のx」：2016～2022年（B3～H3セル）
ここまで参照できたら[Enter]で決定します。
TREND関数と同じ、"8,981"が返ります。
GROWTH関数
GROWTH関数の構成（引数）はTREND関数と同じです。
第１引数「既知のy」：直近7年の契約実績（B6～H6セル）
第２引数「既知のx」：2016～2022年（B3～H3セル）
第３引数「新しいx」：2023年（I3セル）
ここまで参照できたら[Enter]で決定します。
GROWTH関数だけ、"9,283"という値が返りました。
さて、これらの値はどのように求められたのでしょうか。
・2016年～2022年までの上昇率の平均？
・各年増減値の中央値を2022年の実績に加算?
実際にはより奥が深い統計学上の概念から導き出された結果です。
TREND関数：様々な（複数の）外的要素をもとに予測値を導き出す「重回帰分析」型。
FORECAST関数：１つの（単一の）外的要素をもとに予測値を導き出す「単回帰分析」型。
GROWTH関数：様々な（複数の）外的要素をもとに指数関数的に増大していく前提の「成長曲線」型。
重（単）回帰分析型と成長曲線型は同列の手法ではなく、"成長曲線"はあくまで違いがイメージしやすいように表現しています。
今回TREND関数とFORECAST関数の結果が同じ値となったのは、参照した変数が同じで一つだけだったから、というだけなんですね。
FORECAST関数が１つの外的要素しか考慮できないのに対して、TREND関数がより多くの変数から値を求められます。
そしてGROWTH関数は、これまでの実績や複数の外的要素の見通しから、growthが意味する通り、成長曲線に沿ったより強気の値を求めてくれる関数なんだということを、ざっくりおさえておけば大丈夫です。
※参照する変数によっては、GROWTH関数で予測した値がTREND関数やFORECAST関数の結果よりも小さくなる場合もありますが、統計学の難しい部分を深掘りしすぎるとこのテーマの主旨から離れてしまうのでこれくらいにしておきます。
実務での活用方法（具体例）
これらの関数をどのようなシーンでどう使い分けるのか、具体的なユースケースをご紹介します。
下図のサンプルは、8月1日～7日の１週間におけるとある季節商品の販売実績を、日ごとの最高気温と最低気温、１日あたりの広告投下量とともにまとめた表です。
この時期（夏の暑い時期）に売れる製品としては、アイスクリームや清涼飲料、ビールや制汗剤、レジャー用品といったものが想像できますね。
今回はこれらの情報をもとに、TREND関数とGROWTH関数を使って8月8日に売れるであろう個数を求めてみます。
FORECAST関数を使用しないのは、前述のとおり１つの変数（外的要因）しか考慮できないためです。
サンプルのように「最高気温」「最低気温」「広告量」という複数の要素がある場合、FORECAST関数で参照してみてもエラーとなってしまうのです。
TREND関数
TREND関数では以下のように参照します。
第１引数「既知のy」：直近の販売個数（E2～E8セル）
第２引数「既知のx」：直近の気温と広告量（B2～D8セル）
第３引数「新しいx」：8月8日の予想最高気温と広告投下計画（B11～D11セル）
複数の外的要因（最高気温、最低気温、広告量）をまとめて範囲選択できるのは便利ですね！
ここまで入力したら[Enter]で決定します。
→TREND関数では"5,461"という値が返りました。
GROWTH関数
GROWTH関数の構成はTREND関数と同じでしたね。
第１引数「既知のy」：直近の販売個数（E2～E8セル）
第２引数「既知のx」：直近の気温と広告量（B2～D8セル）
第３引数「新しいx」：8月8日の予想最高気温と広告投下計画（B12～D12セル）
TREND関数で求めた値よりも大きい、"6,319"という結果になりました。
気温推移や広告量、直近の販売実績から、TREND関数で求めた5,461個（約5,500個）での販売が想定されますが、もしかしたらGROWTH関数で求めた6,319個（約6,300個）というところまで需要が見込めるかもしれません。
そこまで見えていれば、事前調達や仕込みといった準備や人員配置の構えができますよね。
また今回のようなデイリー、ウィークリーの販売計画ではなく、1ヶ月後、半年後、１年後といった中期的なスパンでは、TREND関数で求めた値を中間値、GROWTH関数で求めた値をMax値、これらの差と同じ幅でMin値を仮設定しておくこともおすすめです。
マーケットの動向や販売進捗に合わせて、生産量や物流網、人的リソースやサポート体制を調整できます。
非常に便利ですよね！
いかがでしたか？
今回解説した関数を使い分けて市場や事業の行方を定量予測すてば、様々な想定パターンに構えることができます。
皆さんの日々の業務に合わせて、ぜひ使ってみてください！
↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。
↓↓Excel操作をとにかく高速化したい方へ↓↓
この記事が気に入ったらサポートをしてみませんか？