エクセルのグラフに近似曲線を追加すると、売上推移や実験結果、アクセス数などの変化を視覚的に把握できます。
ただし、グラフ上に表示された数式を見ても、将来の予測値をどのように計算するのか、係数は何を示すのかが分かりにくい場合もあるでしょう。
この記事では、近似曲線の数式を表示して予測値を求める手順、SLOPE関数やINTERCEPT関数を使って数値化する方法、精度を確認する考え方まで解説します。
近似曲線の数式は、横軸の値をxとして入力することで予測値yを計算できます。
グラフで傾向を確認し、関数で係数を取得すれば、見た目だけで終わらない実用的な予測表を作成できます。
サンプルデータでは、1行目を見出し行として、A列に月、B列に売上を入力しているものとします。
エクセルの近似曲線の数式で予測値を求める手順
| 月 | 売上 | 予測売上 |
|---|---|---|
| 1 | 120 | ― |
| 2 | 135 | ― |
| 3 | 149 | ― |
| 4 | 164 | ― |
それではまず、グラフに近似曲線を設定し、数式から予測値を出す基本操作について解説していきます。
散布図と近似曲線の追加
まずA1からB5の範囲を選択し、挿入タブから散布図を作成します。
月のような数値を横軸にして売上のような数値の変化を確認する場合、折れ線グラフよりも散布図を使うほうがxとyの関係を正確に扱えます。
グラフ内のデータ系列を選択して右クリックし、近似曲線の追加を選びましょう。
右側に表示される近似曲線の書式設定で、最初は線形近似を選ぶと、一定の増減傾向を表す直線が表示されます。
線形近似は、月が1増えるごとに売上がほぼ同じ量だけ増える、または減ると考えられるデータに向く方法です。
【操作のポイント】日付が横軸にある場合でも、日付そのものではなく連続した数値として扱われることを確認してから散布図を作成しましょう。
グラフ上の数式と決定係数の表示
続いては、近似曲線の数式をグラフに表示する設定を確認していきます。
近似曲線の書式設定にあるグラフに数式を表示するへチェックを入れると、y = 14.8x + 105.5のような式がグラフ上に現れます。
この式では、xが月、yが売上の予測値です。
14.8は傾きであり、月が1増えるごとに売上が約14.8増えるという意味になります。
105.5は切片で、xが0のときの理論上の予測値です。
あわせてグラフにR-2乗値を表示するへチェックを入れると、近似曲線が元データにどの程度合っているかを確認できます。
表示例
y = 14.8x + 105.5
R² = 0.998
R²が1に近いほど、今回選んだ近似式はデータのばらつきをよく説明していると判断できます。
ただし、R²が高いからといって、将来も必ず同じ動きになるとは限りません。
【操作のポイント】数式とR²値は、近似曲線を選択した状態で書式設定を開くと同時に表示できます。
数式へのxの代入による予測値
続いては、表示された近似式に将来の月を代入する計算を確認していきます。
たとえば式がy = 14.8x + 105.5で、5か月目の予測売上を求める場合は、xに5を入れます。
y = 14.8 × 5 + 105.5
y = 179.5
したがって、5か月目の予測値は179.5です。
実際のワークシートでは、C6に予測式を入力すれば、グラフを見なくても予測値をセルに表示できます。
ただし、グラフ上の係数は表示桁数が少ないことがあり、表示された式をそのまま手入力すると、計算結果にわずかな差が出る場合があります。
精密な予測表を作るときは、後述するSLOPE関数とINTERCEPT関数を使う方法が便利です。
【操作のポイント】予測するxの値は、過去データの範囲から大きく離しすぎると誤差が増えやすいため注意が必要です。
関数で近似曲線の予測値を数値化する方法
| A列 | B列 | C列 |
|---|---|---|
| 月 | 実績売上 | 近似予測 |
| 5 | 未入力 | 数式で計算 |
続いては、近似曲線の係数を関数で取得し、予測値をセルに数値化する方法を確認していきます。
FORECAST.LINEAR関数による予測値
FORECAST.LINEAR関数は、既知のxとyのデータから、指定したxに対する線形予測値を直接返す関数です。
=FORECAST.LINEAR(A6,$B$2:$B$5,$A$2:$A$5)
この数式をC6に入力すると、A6に入れた5か月目に対応する予測売上が表示されます。
第1引数は予測したいx、第2引数は既知のyの範囲、第3引数は既知のxの範囲です。
B列とA列の範囲は、同じ行数で指定しなければなりません。
月を6、7、8と入力して数式を下へコピーすれば、複数月の予測値をまとめて作成できます。
FORECAST.LINEAR関数は、グラフの線形式近似曲線と同じ考え方で計算するため、定期的に予測値を更新したい表に適しています。
【操作のポイント】データ範囲を絶対参照にすると、オートフィルを使っても既知データの参照先がずれません。
TREND関数による複数行の予測
続いては、複数の予測値を一度に求められるTREND関数を確認していきます。
TREND関数は、線形回帰に基づく予測値を返す関数です。
=TREND($B$2:$B$5,$A$2:$A$5,A6:A9)
Microsoft 365やExcel 2021以降では、C6に数式を入力するだけで、C6からC9へ結果が自動的に展開されます。
以前のExcelでは、出力先のセル範囲をあらかじめ選択してから配列数式として確定する使い方が必要な場合があります。
予測期間が長く、月ごとや年度ごとの見通しをまとめて算出したいときに便利でしょう。
TREND関数は直線的な傾向を前提にするため、季節変動や急な制度変更を含むデータでは結果を慎重に読み取る必要があります。
【操作のポイント】予測先の月をA6からA9へ連続して入力しておくと、予測表の構造が分かりやすくなります。
LINEST関数による回帰式の取得
続いては、傾きや切片をまとめて取得できるLINEST関数について解説していきます。
LINEST関数は、最小二乗法による線形回帰の統計情報を返します。
=LINEST($B$2:$B$5,$A$2:$A$5,TRUE,TRUE)
この関数の結果には、傾き、切片、決定係数などが含まれます。
用途によっては、個別の関数で傾きと切片を取得するほうが扱いやすいですが、分析用の表ではLINEST関数も有効です。
係数をセルに保存しておけば、予測式を確認しながら条件別のシミュレーションもできます。
【操作のポイント】LINEST関数の結果は複数セルに広がるため、Microsoft 365以外では配列数式の操作方法を確認しておきましょう。
係数と切片を使った数式の読み取り
| 項目 | 数式 | 役割 |
|---|---|---|
| 傾き | =SLOPE(B2:B5,A2:A5) | xが1増えた際の変化量 |
| 切片 | =INTERCEPT(B2:B5,A2:A5) | xが0のときの値 |
続いては、近似曲線の数式を構成する係数と切片の意味を確認していきます。
SLOPE関数による傾きの取得
SLOPE関数は、回帰直線の傾きを返します。
=SLOPE($B$2:$B$5,$A$2:$A$5)
結果が14.8なら、月が1増加したときに売上が平均で14.8増える傾向を示します。
傾きがプラスなら増加傾向、マイナスなら減少傾向です。
たとえば広告費をx、問い合わせ件数をyにする場合、傾きは広告費を1単位増やしたときに問い合わせがどの程度変わるかを表します。
ただし、相関があることと因果関係があることは別です。
数値の意味は、業務の背景やデータの収集条件と合わせて判断しましょう。
【操作のポイント】SLOPE関数では、最初にyの範囲、次にxの範囲を指定します。引数の順番を逆にしないよう注意しましょう。
INTERCEPT関数による切片の取得
続いては、回帰式の切片を取得するINTERCEPT関数を確認していきます。
=INTERCEPT($B$2:$B$5,$A$2:$A$5)
切片は、xが0のときに予測されるyの値です。
売上と月の関係であれば、開始前の理論値を表すことになりますが、実務上はxが0に意味を持たないケースもあります。
そのため、切片を単独で評価するより、傾きと組み合わせて予測式を作るための係数として扱うほうが分かりやすい場合があります。
切片が大きくマイナスになる場合は、対象データの範囲外へ予測を広げると不自然な結果になる可能性も考えられます。
【操作のポイント】切片は予測の計算に必要ですが、必ずしも現実の開始値を意味するとは限りません。
係数セルを参照した予測式
続いては、傾きと切片を別セルに置き、予測値を計算する方法について解説していきます。
たとえばE2にSLOPE関数、E3にINTERCEPT関数を入力し、C6で予測値を出す構成にします。
E2 =SLOPE($B$2:$B$5,$A$2:$A$5)
E3 =INTERCEPT($B$2:$B$5,$A$2:$A$5)
C6 =$E$2*A6+$E$3
この形なら、E2とE3に回帰式の係数が明示され、C列には予測結果だけを並べられます。
グラフの表示式に頼らず、元データの精度を保ったまま数値計算できる点が大きな利点です。
係数の表示形式を小数点以下3桁や4桁に整えると、分析表としても読みやすくなります。
【操作のポイント】係数セルは絶対参照にし、予測するxのセルだけを相対参照にしてオートフィルしましょう。
近似曲線の種類と使い分け
| 種類 | 主なデータの形 | 予測時の注意点 |
|---|---|---|
| 線形 | 一定の増減 | 外挿しすぎない |
| 指数 | 増加率が拡大 | ゼロや負数に注意 |
| 多項式 | 曲がりを含む推移 | 次数を上げすぎない |
続いては、データの形に応じた近似曲線の選び方を確認していきます。
線形近似と指数近似の選択
線形近似は、増減幅がほぼ一定のデータに使います。
一方で、利用者数や複利的な増加のように、増加量そのものが徐々に大きくなる場合には指数近似が候補になります。
見た目が右肩上がりだからといって、必ず指数近似が正しいとは限りません。
まず線形式を表示し、残差やR²値を確認したうえで、指数近似と比較すると判断しやすくなります。
指数近似では、値が急激に大きくなる予測を出すことがあるため、事業上の上限や市場規模も考慮する必要があります。
【操作のポイント】複数の近似曲線を試す際は、元のグラフを複製して比較すると、設定の違いを見失いません。
近似曲線の書式設定画面
続いては、近似曲線の種類を選択する画面を確認していきます。
グラフを選択し、グラフ要素から近似曲線を追加すると、近似曲線の書式設定で線形、指数、対数、多項式、移動平均などを選択できます。
多項式近似では次数を指定できますが、次数を高くしすぎると、過去データにはよく合っていても将来予測が不安定になることがあります。
近似の種類は、R²値だけで決めず、データが生まれる仕組みに合うかどうかで選ぶことが大切です。
【操作のポイント】グラフ要素の近似曲線からその他のオプションを選ぶと、近似曲線の書式設定を開けます。
移動平均と将来予測の違い
続いては、移動平均と近似曲線の役割の違いを確認していきます。
移動平均は、短期的なばらつきをならして、売上や株価などの流れを見やすくする方法です。
一方で、線形近似や指数近似は、xとyの関係式を作り、指定したxに対する値を計算することを目的にします。
移動平均にも予測のヒントはありますが、将来のセルへ数式として予測値を出したい場合は、FORECAST.LINEARやTREND関数のほうが適しています。
分析の目的が傾向の可視化なのか、具体的な数値の予測なのかを分けて考えましょう。
【操作のポイント】移動平均の期間は、月次データなら3か月や6か月など、業務の周期に合わせて設定します。
予測精度を高めるデータ整備
| 確認項目 | 確認内容 |
|---|---|
| 空白セル | 欠損値とゼロを区別する |
| 外れ値 | 特別要因の有無を確認する |
| 期間 | 十分な観測数を確保する |
続いては、近似曲線の予測値を実務で使う前に確認したいデータ整備について解説していきます。
欠損値と文字列の確認
数式が正しくても、参照範囲に空白セル、文字列、エラー値が混在すると、予測の信頼性は下がります。
売上が未集計で空白なのか、実績がゼロだったのかは、同じように見えて意味が異なります。
欠損データをゼロとして扱うと、傾きや切片が大きく変わることがあります。
入力規則や表示形式を整え、数値として認識されているかを確認しましょう。
SUM関数で集計できない値が含まれている場合は、文字列として保存されている可能性もあります。
【操作のポイント】数値が左寄せで表示されている場合は、文字列扱いになっていないかを確認しましょう。
外れ値と特殊要因の判断
続いては、極端に大きい値や小さい値を含む外れ値の扱いを確認していきます。
大型キャンペーン、休業、システム障害、単発の受注などにより、通常とは異なる数値が入ることがあります。
こうした値を無条件に削除するのではなく、なぜ数値が変化したのかを記録したうえで、予測対象に含めるかを判断することが重要です。
通常時の需要予測をしたいなら、特殊要因を除いた別の分析表を作る方法もあります。
反対に、同じ要因が今後も起こる可能性が高いなら、除外せずにシナリオとして扱うほうが適切かもしれません。
【操作のポイント】外れ値を見つけるには、散布図と元データの両方を確認して、入力ミスと実際の変化を切り分けましょう。
R²値と予測範囲の管理
続いては、R²値の見方と予測範囲の考え方を確認していきます。
R²値は、近似式が既知データの変動をどの程度説明できるかを示す目安です。
値が1に近いほど当てはまりは良くなりますが、R²値だけで将来の精度を保証することはできません。
過去1年分の月次売上から翌月を予測する場合と、過去3か月分から3年後を予測する場合では、後者の不確実性が大きくなります。
近似曲線による予測は、既知データの近くを推定するほど使いやすく、遠い将来へ延長するほど慎重な判断が必要です。
予測値を確定値として扱うのではなく、予算や在庫計画を検討するための基準値として使うとよいでしょう。
【操作のポイント】予測値と実績値を翌月以降に並べ、誤差を継続的に確認すると、近似式の見直し時期を判断しやすくなります。
まとめ エクセルの数式で近似曲線の予測値を求める方法
| 目的 | 主な方法 |
|---|---|
| グラフで傾向を知る | 散布図と近似曲線 |
| 予測値をセルに出す | FORECAST.LINEAR関数 |
| 係数を管理する | SLOPE関数とINTERCEPT関数 |
エクセルの近似曲線では、散布図に近似曲線を追加し、グラフに数式を表示することで、データの傾向を式として確認できます。
線形式のy = ax + bでは、aが傾き、bが切片です。
予測値をセルで扱いたい場合は、FORECAST.LINEAR関数を使う方法が分かりやすく、傾きと切片を個別に管理したい場合はSLOPE関数とINTERCEPT関数を活用できます。
グラフは傾向の確認、関数は予測値の数値化というように役割を分けると、分析結果を業務に活かしやすくなります。
近似曲線の種類は、データの変化の仕方に合わせて選びましょう。
また、R²値、外れ値、欠損値、予測期間を確認し、予測値を過信しない姿勢も大切です。
まずは少量の実績データで散布図とFORECAST.LINEAR関数を試し、予測値と後日の実績値を比べるところから始めてみましょう。
