【Excel】エクセルの予測関数で将来の数値を求める方法（種類・回帰分析）
売上や利用者数、在庫数などの実績データから、これから先の数値を見積もりたい場面は少なくありません。
Excelには過去の傾向を使って将来値を計算する予測関数があり、複雑な統計ソフトを使わなくても基本的な需要予測や回帰分析を行えます。
予測関数では、既知の数値と時系列の対応関係を正しく指定することが重要です。
FORECAST.LINEARは直線的な傾向を使い、FORECAST.ETSは季節ごとの変動も含めて予測します。
この記事では、FORECAST関数の種類、数式の入力方法、回帰分析との違い、計算時に確認したい注意点まで順番に解説します。
サンプルデータはすべて1行目に見出しがあり、2行目以降に実績値が入力されているものとして説明します。
エクセルの予測関数で将来値を計算する方法
| 月 | 売上 | 予測売上 |
|---|---|---|
| 1 | 120 | |
| 2 | 128 | |
| 3 | 135 | |
| 4 | 142 | |
| 5 | ? |
それではまず、FORECAST.LINEAR関数を使って将来の数値を求める基本操作について解説していきます。
結論からいうと、月や日付などの説明変数と、対応する売上などの実績値を用意すれば、次の月の予測値をセルへ表示できます。
基本の書式はFORECAST.LINEAR 予測したいx、既知のy、既知のxです。
たとえばA列に月、B列に売上がある場合、5月の予測ではFORECAST.LINEAR 5、B2:B4、A2:A4という考え方になります。
FORECAST.LINEAR関数の数式入力
FORECAST.LINEAR関数は、過去データを直線で近似し、その延長上にある数値を求める関数です。
月ごとに売上がほぼ一定の幅で増減しているような場合に向いています。
上の表では、A2からA4に1、2、3、B2からB4に120、128、135が入力され、A5に5を入力している状態を想定します。
C5を選択し、次の数式を入力します。
=FORECAST.LINEAR(A5,$B$2:$B$4,$A$2:$A$4)
最初のA5は予測したい月です。
次のB2:B4は実際に観測された売上であり、既知のyに該当します。
最後のA2:A4は各売上に対応する月であり、既知のxです。
yの範囲とxの範囲は、必ず同じ行数にそろえる必要があります。
計算結果は、過去3か月の増加傾向に基づく5月の推定売上です。
【操作のポイント】売上範囲と月の範囲を逆に指定しないよう、結果として予測したい値を先に確認します。
セル参照とオートフィルによる連続予測
6月、7月、8月もまとめて予測したいときは、予測対象の月をA5以降へ並べ、C5の数式を下方向へコピーします。
このとき実績データの範囲は、コピーしても動かないよう絶対参照にする方法が便利です。
C5に入力する数式は、次のようになります。
=FORECAST.LINEAR(A5,$B$2:$B$4,$A$2:$A$4)
数式中のA5は相対参照なので、C6へコピーするとA6へ自動的に変わります。
一方、B2:B4とA2:A4はドル記号を付けているため、コピー後も実績データの参照先が固定されます。
予測値を過去の実績値として再利用する場合と、元の実績だけで予測する場合では結果が変わります。
月次計画を作る場合は、どちらの方式かをあらかじめ決めておくと、担当者間で数値の意味を共有しやすくなります。
【操作のポイント】複数月を予測する前に、実績範囲を固定するのか、月ごとに拡張するのかを決めます。
予測値を読むときの考え方
FORECAST.LINEAR関数で得られる数値は、確定した未来の売上ではなく、過去の直線的な傾向から算出した推定値です。
広告出稿、価格改定、競合の動き、天候など、過去と異なる要因がある場合は大きく外れることがあります。
特に実績が少ない状態では、1件の異常値が回帰直線を大きく動かすかもしれません。
予測結果は単独で確定値として扱わず、予算や現場情報と照らし合わせて使うことが大切です。
予測値が小数になる場合は、売上金額なら表示形式で桁区切りを設定し、人数や個数ならROUND関数で整数へ丸めることもできます。
【操作のポイント】予測値の小数点処理は、計算式ではなく表示形式で対応できるかも確認します。
FORECAST関数と予測シートの種類
| 機能 | 向いているデータ | 主な特徴 |
|---|---|---|
| FORECAST.LINEAR | 増減が直線的な実績 | 単回帰による予測 |
| FORECAST.ETS | 季節変動がある時系列 | 指数平滑法による予測 |
| 予測シート | グラフで確認したい実績 | 予測表とグラフを作成 |
続いては、Excelで利用できる予測機能の種類と使い分けを確認していきます。
関数名が似ていても、想定しているデータの性質は異なります。
直線的な増減にはFORECAST.LINEAR、周期的な上下にはFORECAST.ETSが基本の選択です。
FORECAST.LINEARと旧FORECAST関数
FORECAST.LINEAR関数は、現在のExcelで線形予測を明示するために使われる関数です。
以前からあるFORECAST関数も同じ考え方で利用できますが、新しく数式を作る場合はFORECAST.LINEARを使うと内容が伝わりやすくなります。
線形とは、説明変数が1増えるごとに予測値が一定量ずつ増減するという仮定です。
たとえば月が1増えるごとに売上が平均7万円増えるような傾向があるとき、回帰直線を延長して将来の値を求めます。
データが波のように上下する場合は、直線予測だけでは傾向を十分に表せないことがあります。
【操作のポイント】既存ブックにFORECASTがある場合も、引数の意味を確認してからFORECAST.LINEARへ置き換えます。
FORECAST.ETS関数と季節性
FORECAST.ETS関数は、季節ごとに似た動きを繰り返す時系列データの予測に利用できます。
たとえば夏に飲料の販売数が増え、冬に減るようなデータでは、月ごとの季節性を考慮する余地があります。
書式はFORECAST.ETS 目標日、値、タイムラインです。
=FORECAST.ETS(A14,$B$2:$B$13,$A$2:$A$13)
この例では、A列に日付または連続した月、B列に売上を入力し、A14の日付に対応する予測値を求めます。
タイムラインは一定間隔で並んでいる必要があり、月次データの途中に大きな欠損があると扱いに注意が必要です。
季節性を反映したい場合でも、少なくとも複数周期分の実績を集めてから判断することが望ましいです。
【操作のポイント】日付の間隔が不規則なデータは、月初日などの基準日へそろえてから予測します。
予測シートによるグラフ確認
予測シートは、時系列の実績データから予測グラフと予測表を作成するExcelの機能です。
数式だけでは傾向をつかみにくい場合に、実績と予測のつながりを視覚的に確認できます。
データ範囲内の日時列と値列を選択してから、データタブの予測グループにある予測シートを選びます。
表示される画面では、予測終了日や信頼区間などを設定できます。
グラフの急な山や谷を見ると、平均的な計算結果だけでは気付きにくい異常値を発見しやすくなります。
【操作のポイント】予測シートを作成する前に、日付順に並び替えられているか確認します。
回帰分析による予測式と散布図
| 広告費 | 問い合わせ件数 | 予測件数 |
|---|---|---|
| 10 | 21 | |
| 15 | 29 | |
| 20 | 36 | |
| 25 | ? | ? |
続いては、回帰分析の考え方を利用して、要因から将来値を推定する方法を確認していきます。
月の経過だけではなく、広告費、訪問件数、作業時間などを説明変数にしたい場合に役立つ方法です。
単回帰分析では、予測値を切片と傾きに説明変数を掛けた値として考えます。
回帰直線とFORECAST.LINEARの関係
FORECAST.LINEAR関数は、既知のxと既知のyの組み合わせから、最も近い直線を求める単回帰分析の考え方を利用しています。
広告費と問い合わせ件数の例では、広告費がx、問い合わせ件数がyです。
広告費が25万円になったときの問い合わせ件数を予測するなら、C5へ次の数式を入力します。
=FORECAST.LINEAR(A5,$B$2:$B$4,$A$2:$A$4)
予測値は、広告費が増えるほど問い合わせも増えるという過去の関係を直線として表した結果です。
相関があることと、広告費だけが結果の原因であることは同じではありません。
繁忙期やキャンペーン内容など、数値に影響する別の要素も意識しましょう。
【操作のポイント】x列とy列は同じ案件、同じ月など、対応関係が取れるデータだけを使います。
傾きと切片を求める数式
予測式の中身を確認したいときは、SLOPE関数とINTERCEPT関数を使います。
SLOPE関数は回帰直線の傾き、INTERCEPT関数はxが0のときの切片を返します。
=SLOPE($B$2:$B$4,$A$2:$A$4)
=INTERCEPT($B$2:$B$4,$A$2:$A$4)
仮に傾きが1.5、切片が6なら、予測式は問い合わせ件数が1.5掛ける広告費に6を加えた形です。
広告費25に対する予測は、1.5掛ける25に6を加える計算になります。
関数で直接予測する方法と、傾きと切片から式を組み立てる方法は、同じデータなら近い結果になります。
【操作のポイント】切片が業務上不自然な値でも、データの範囲内での予測精度を先に確認します。
散布図と分析ツールの操作画面
回帰分析では、数式の前に散布図を作成し、データが直線的な関係に見えるか確認すると判断しやすくなります。
挿入タブから散布図を選ぶと、xとyの分布を点として表示できます。
散布図の点が右上がりに並ぶなら、正の関係がある可能性を視覚的に確認できます。
データタブの分析ツールを利用できる環境では、回帰分析を実行して重相関Rや係数などを確認することも可能です。
【操作のポイント】散布図では横軸が説明変数、縦軸が結果の数値になっているか確認します。
予測関数で発生しやすいエラーと注意点
| 確認項目 | よくある状態 | 対処の考え方 |
|---|---|---|
| データ数 | 実績が少ない | 期間を増やして確認 |
| 範囲 | xとyの行数が違う | 開始行と終了行をそろえる |
| 値 | 文字列や空白が混在 | 数値形式を確認 |
続いては、予測関数の結果が正しく出ないときに確認したい項目を解説していきます。
数式の形が正しく見えても、元データの状態によってはエラーや不自然な予測値につながります。
既知のxと既知のyの範囲不一致
FORECAST.LINEAR関数では、既知のxと既知のyの個数が一致している必要があります。
たとえば既知のyをB2:B10にしたのに、既知のxをA2:A9にすると、対応する組が1つ不足します。
このような場合は参照範囲を見直し、開始行と終了行をそろえましょう。
見出し行を片方の範囲だけに含めるミスも起こりやすいため、数式バーで範囲を確認します。
【操作のポイント】データの選択時は、1行目のヘッダーを含めず、実績が始まる2行目から指定します。
空白セルと文字列データの扱い
売上列に空白、ハイフン、未確定などの文字列が混ざると、意図したデータだけで計算されないことがあります。
特に数値に見える文字列は、セルの配置や数式の結果を確認しないと見分けにくい場合があります。
数値として扱うべきセルは、VALUE関数や区切り位置の機能などで数値へ変換しておくと安全です。
=ISNUMBER(B2)
この数式でTRUEが表示されれば、B2は数値として認識されています。
0と未入力は意味が違うため、実績がゼロだったのか、まだ集計していないのかも区別しましょう。
【操作のポイント】未確定データをゼロで埋める前に、そのゼロが予測の傾向をゆがめないか検討します。
異常値と予測期間の長さ
一時的な大型受注やシステム障害による売上ゼロなど、通常と異なる実績値は予測結果へ影響します。
削除するのではなく、発生理由を記録したうえで、異常値を含める場合と除く場合の両方を比較する方法があります。
実績期間から大きく離れた未来を予測するほど、不確実性は高まります。
1年分のデータだけで5年後を推定するより、近い将来を短い区切りで見直す運用が実務では扱いやすいでしょう。
【操作のポイント】予測値と実績値を毎月比較し、誤差が大きい場合は前提やデータ範囲を更新します。
売上予測に使える関連関数と実務での工夫
| 目的 | 関数 | 活用例 |
|---|---|---|
| 平均的な水準 | AVERAGE | 月平均の確認 |
| 丸め処理 | ROUND | 人数や個数の整数化 |
| 誤差の確認 | ABS | 予測と実績の差の絶対値 |
続いては、予測関数と組み合わせやすい関数や、実務での数値管理の工夫を解説していきます。
予測式を作ることだけでなく、結果を比較して改善し続ける仕組みが重要です。
AVERAGE関数との比較
売上の増減傾向が弱い場合は、FORECAST.LINEARの結果を平均値と比較すると判断材料になります。
平均値は次の数式で求められます。
=AVERAGE(B2:B13)
予測値が平均より大きく離れている場合は、直近の増減が強く反映されている可能性があります。
平均値は単純ですが、予測結果の妥当性を確認する基準として役立ちます。
【操作のポイント】直近平均と年間平均を並べると、短期傾向と長期傾向を比較しやすくなります。
ROUND関数による予測値の丸め
FORECAST.LINEARの計算結果は小数になることがあります。
売上金額を千円単位へ丸めたい場合は、ROUND関数を組み合わせます。
=ROUND(FORECAST.LINEAR(A5,$B$2:$B$4,$A$2:$A$4),0)
第2引数を0にすると整数へ、マイナス3にすると千の位で丸められます。
ただし、途中で丸めた値を次の予測の実績として使うと誤差が積み重なることもあります。
【操作のポイント】計算用の予測値は小数のまま保持し、表示用セルだけを丸める方法も有効です。
予測誤差を記録する管理表
予測の精度を改善するには、予測した時点の値と、後から判明した実績値を同じ表で管理します。
予測がC列、実績がD列なら、誤差の絶対値はE2へ次の式で入力できます。
=ABS(D2-C2)
さらに実績に対する誤差率を確認したい場合は、ゼロ除算を避けるためIFERROR関数を組み合わせます。
誤差の大きい月を確認すると、季節性、キャンペーン、欠品など、予測モデルへ追加すべき要因が見えてきます。
【操作のポイント】予測表は毎回作り直すより、予測日と実績確定日を残して更新する運用が向いています。
まとめ エクセルの予測関数で将来の数値を求める方法
| 確認した内容 | 活用の目安 |
|---|---|
| FORECAST.LINEAR | 直線的な増減をもとに予測 |
| FORECAST.ETS | 季節性のある時系列を予測 |
| 回帰分析 | 広告費などの要因から推定 |
エクセルの予測関数では、既知のxと既知のyを指定することで、過去データから将来の数値を見積もれます。
増減が比較的直線的ならFORECAST.LINEARを使い、季節ごとの繰り返しがあるならFORECAST.ETSや予測シートも検討しましょう。
回帰分析の考え方を理解すると、月の経過だけでなく、広告費や作業時間などを使った予測にも応用できます。
予測精度を左右するのは関数の種類だけではなく、データの対応関係、欠損、異常値、予測期間の長さです。
まずは少量のサンプルデータで数式を作り、予測値と実績値の差を継続して確認してみましょう。
