Excelで実績と予測を分けたグラフを作る方法｜売上予測を点線で表示する

「売上の今後の見込みをグラフで見せたい」
「実績と予測を分けて表示したい」
「未来部分だけ色や点線を変えたい」

このように、Excelで“見やすい予測グラフ”を作りたいと思ったことはないでしょうか？

ただ、普通に折れ線グラフを作るだけでは、

実績と予測が同じ線でつながる
どこから未来予測なのか分からない
会議資料として見づらい

という問題があります。

そこで本記事では、

実績と予測を色分けしたグラフの作り方
FORECAST.ETS を使った売上予測
FORECAST.LINEAR との違い
予測エリアを見やすくするコツ

を、実務目線で分かりやすく解説します。

INDEX

完成イメージ
グラフの作成方法
売上予測値を計算する方法
よくある質問（FAQ）
まとめ｜売上予測グラフは「見せ方」と「予測方法」が重要

完成イメージ

完成すると、以下のようなグラフを作れます。

これにより、

ここまでは実績
ここからは予測
前年と比べてどうか

が一目で分かるグラフになります。

特に、

・どこまでが実績か
・どこからが予測か
・前年と比べてどうか

を一目で判断できるため、会議資料や売上分析で非常に使いやすくなります。

👉「増減要因を分析したい」「売上がなぜ変化したかを見える化したい」という場合は、ウォーターフォールグラフもおすすめです。

グラフの作成方法

では実際にどのようにグラフを作成していけばいいか説明していきます。

今回使用する表

今回は、以下のような月次売上表を使います。

ポイントは、
・・実績線と予測線を自然につなげるため、最新実績月を両方に入れる
・「今年実績」と「今年予測」は列（横並びの場合は行）を分けて入力する
ということになります。

なぜ「今年実績」と「今年予測」を分けるの？

ここが今回の重要ポイントです。
Excelのグラフは、

同じ列（または行）に並べた場合、途中からグラフの書式を変えることができない
エラー部分は描画しない

という性質があります。

つまり、実績と予測を分けて入力することで、

実績と予測で別の書式を設定することができる
実績→未来を表示しない、予測 → 過去を表示しない

という状態を作ることができます。

これにより、

実績→青色実線、予測→オレンジ点線に書式を変更できる
実績から予測まで、一つのグラフのように見せることができる

というわけです。

折れ線グラフの作成手順

では実際にグラフを作っていきましょう。
折れ線グラフの作成手順は以下の通りです。

・グラフ化したい範囲を選択し、「挿入」タブ→「マーカー付き折れ線」を選択

これで、ベースとなるマーカー付き折れ線グラフが作成されます。

空白セルが0扱いの場合はエラー値（NA関数）を使用する

Excelのグラフでは、数式で空白セルにしているにもかかわらず0表示となってしまいます。
これだと、グラフが0で続いてしまい、実績→予測につながるきれいなグラフとならないケースがあります。

この場合は、NA関数を使い、わざとエラー値を発生させることで解決することができます。
NA関数とは、#N/Aエラーを意図的に返すことができる関数です。

先ほどの状態に対し、NA関数を使って意図的にエラーを発生させたのが以下の画像です。
画像のように、エラーが発生することによって0表示が消えていることが確認できます。

予測エリアの背景色を変える

績と予測の境界をより分かりやすくしたい場合は、「予測部分の背景色」を変える方法がおすすめです。

例えば、

実績エリア → 白背景
予測エリア → 薄いグレー

にすると、「ここから先は予測」ということが直感的に伝わります。
会議資料やPowerPointでもよく使われるテクニックです。

１．「挿入」タブから、「図形」→「正方形/長方形」を選択

2．作成した図形の書式を、「塗りつぶし」→グレー・「透明度」→90%、線なしを選択
（こちらはお好みで調整してください）

あとは作成した図形を、対象のグラフに重ねます。
これで、実績と予測部分の境界がより分かりやすくなりました。

売上推移を分析したい場合は、相関分析もおすすめです。
👉「広告費と売上」「来店数と売上」など、数値同士の関係性を分析したい方は、こちらも参考にしてください。

売上予測値を計算する方法

ここまでで、実績と予測を分けたグラフを作ることはできました。
次に悩みやすいのが、

予測値はどのように計算したらいいの？

という点です。

今回のような売上データは、

季節変動
セール時期
ボーナス月
キャンペーン

などで、月ごとに上下するケースが多いです。

この場合のおすすめの予測値計算方法を紹介します。

予測値は前年同月比で作るのがおすすめ

今回のように、前年1年分と今年途中までの実績がある場合は、統計関数（FORECAST.ETSなど）よりも「前年同月比」で予測したほうが分かりやすいケースがあります。

例えば、今年1〜5月の前年比平均を計算し、その倍率を6月以降の前年売上に掛けます。

=round(前年同月売上*AVERAGE(今年1月:今年5月/前年1月:前年5月),0)

これなら、前年の季節変動を残したまま、今年の伸び率を反映できます。
売上予測では、こちらのほうが現実的なケースも多いです。

FORECAST.ETS関数が向いているケース

Excelには、予測値を計算する関数が用意されています。
今回のように季節変動を考慮する必要がある場合、データが2年以上あるとFORECAST.ETS関数が使いやすいケースもあります。

以下オレンジ部分は、FORECAST.ETSを使用した予測値です。
グラフから、季節変動も反映できていることが確認できます。

そのため、月ごとに増減がある売上データでは、FORECAST.LINEARより自然な予測になりやすいのが特徴です。

FORECAST.ETS関数とは？

FORECAST.ETS関数は簡単に言うと、過去の波を見て、未来も同じように動くと予測する関数です。
指数平滑法の三重平滑（Exponential Triple Smoothing）という手法になります。

このように波があるデータは、FORECAST.ETS関数のほうが自然な予測になりやすいです。

FORECAST.ETS関数の構文は以下の通りです。

=FORECAST.ETS(目標期日,値,タイムライン,[季節性],[データ補間],[集計])

今回の場合、
・目標期日：2026年6月以降
・値：2024年1月～2026年5月の各月売上実績
・タイムライン：2024年1月～2026年5月の各月期間
・季節性：12（1年＝12か月周期のため）
で入力しました。

注意点としては、値とタイムラインは1行（または列）でつないでいないと計算できないため、

FORECAST.ETS　→　計算用に1行（または1列）にデータをまとめる
グラフ用　→　前年実績・今年実績・今年予測のように行（または列）を分ける

としておく必要があります。

よくある質問（FAQ）

FORECAST.ETSがうまく動きません

FORECAST.ETSは、過去データの「周期性」を学習して予測する関数です。
そのため、データ数が少ない場合や、周期性が不規則な場合は、予測精度が下がることがあります。
月次売上データの場合は、最低でも1〜2年分程度のデータがあると使いやすくなります。
また、日付が連続していない場合もエラーや不自然な結果になることがあります。

なぜ #N/A を使うの？

Excelのグラフでは、#N/A のセルを描画しない仕様になっています。
この性質を利用することで、
・実績部分
・予測部分
を別の線として表示できます。
その結果、
・実績　→　実線
・予測　→　点線
のように、未来部分だけデザインを変えられます。

FORECAST.ETSと前年比予測はどちらが良いですか？

ケースによって異なります。
■FORECAST.ETSの場合
・過去データの周期性を考慮できる
・データ量が多いほど強い
・自動予測向き
■前年同月比
・計算根拠を説明しやすい
・実務で使われやすい
・季節性を維持しやすい
実務では、
・まず前年比で予測
・補助的にETSを確認
という使い方もよく行われます。

FORECAST.ETSで予測値が大きくズレます