知らないと損！誰でも売上予測が作れるExcel2019「予測シート」機能とは

この連載では「Excel2013で止まっている人必見！業務効率がUPするExcel2019活用術」と題し、Excel2019（Office365）の新機能や活用方法を、13回にわたり紹介しています。

連載第4回～第7回は、Excel2013には無かった、様々なグラフについてご紹介しました。連載第8回の今回は、将来の数値を予測してくれる「予測シート」について解説します。

例えば、月半ばで当月売上を予測しないといけないとします。何を根拠に予測すればよいのか、考えるだけでも大変ですよね。

そんなとき、今までの実績数値とExcel2019のこの「予測シート」機能を使えば、誰でも簡単に、しかも素早く、将来の予測売上データとグラフが作れるのです。

今回の記事を読めば、「予測シート」がどれほど便利かわかっていただけると思います。

社内のみんなを「アッ！」と言わせるためにも、ぜひマスターしてみてください。

売上予測が簡単に作れる！Excel2019の「予測シート」とは

会社や店舗では、月の途中で月末時点の累計売上を予測することがよくありますよね。

また、来月の予測や来年の予測、売上以外にも来客数や利益など、期間やデータの違いによって、予測したい対象はたくさんあるでしょう。

Excel2013以前からある棒グラフや折れ線グラフは、過去の実績を報告するためにはよく使われるものの、将来の予測データをすぐ作れる機能はありませんでした。

しかしこのExcel2019の「予測シート」では、過去の実績から、将来を予測するデータおよびグラフまでを自動で作成できるようになりました

EXCEL2019「予測シート」の例

「予測シート」を作成してみよう

ではさっそく、「予測シート」を作成していきましょう。

ここでは、3月1日から3月20日までの売上が入った店舗データを使います。

この店舗（木曜日が休み）が月末までの残る期間で、売上が伸びるのか下がるのか、予測シートを作成して見てみましょう。

① セルA3からA22の日付部分を選択し、Ctrlキーを押しながらセルC3からC22の売上を選択します。（タイムラインと呼ばれる日付の範囲と、数値の部分を選択したことになります）

② 「データ」タブで「予測シート」を選択します。

③「予測ワークシートの作成」画面の右下にある「作成」をクリックすれば、この時点で予測シートは完成です。

が、ちょっと待ってください！

実はこの「予測ワークシートの作成」、一度「作成」をクリックしてしまうと同じ画面に戻れないので、注意が必要なんです。

「作成」を押す前に注意すべき事項は3点。詳しくは次のとおりです。

注意事項① 必ずオプション設定を確認しよう

「オプション」をクリックすると、下図のように、表示範囲や信頼度等の項目の設定画面が開きます。「作成」をクリックしてしまうと、この設定画面に戻れなくなるので注意してください。 「オプション」各項目の詳しい内容は後述します。

注意事項②「タイムライン」は文字列だとNG

元データで選択したタイムラインと数値は、文字列はNGです。日付・曜日・数値に見せかけた文字列が含まれていると、予測グラフが作成されないからです。

例えば下図のB列の曜日は、文字列が入っています。B列をタイムラインとして指定してしまうと、下図の右側のように、予測を作成できないというメッセージが表示されてしまいます。

タイムラインには、文字列ではなく、日付や時刻、数値といった連続した値を必ず選びましょう。

注意事項③ 休みの日は空白にしておく

定休日がある場合、売上を「0」とすると以下のように、普通のグラフと違い、波を打ったようなグラフになってしまいます。

休みの日には、売上は「0」ではなく空白にしておきましょう。そうすれば、予測シートの設定画面の右下にある「見つからない点の入力方法」に「補間」を指定することで、グラフが補間され、見栄え良くわかりやすいグラフになります。

以上3つが、予測シートで「作成」ボタンを押す前にチェックすべき注意事項です。

設定ミスで二度手間にならないよう、しっかりチェックしてくださいね。

「予測シート」各項目について理解しておこう

予測シートは、これまでの説明で作成しただけでも予測データとして十分使えますが、グラフの設定を理解すればより活用できます。

ここでは、予測シート作成時の各種設定や項目について、詳しく見ていきましょう。

予測シートの3つの折れ線の意味

上記は、横軸の「2021/3/20」を境に、左が過去の実績、右が将来の予測になっています。

見た目はExcel2013で使われているような普通の折れ線グラフですが、将来を予測するための折れ線グラフが追加されているのが特徴です。

この「将来の予測」は、3本の折れ線に分かれています。一番上の線を「信頼上限」、一番下の線を「信頼下限」といい、この2つの折れ線の間を「予測」といいます。

折れ線グラフ→棒グラフへ切り替える

予測シートの作成画面の右上のボタンで、折れ線グラフから棒グラフへ切り替えられます。すると「将来の予測」が、前々回の記事で紹介した箱ひげ図になります（信頼上限と信頼下限は「ひげ」で表されています）。

「将来の予測」期間を変更する

予測シートの作成画面左下の「予測終了」のカレンダーボタンをクリックします。

予測開始は、左の図にある通り3月21日から、予測終了は右の図の通り7週先の5月9日まで選択が可能です。実績の最終日より前や、かなり先の期間は選べないようになっています。

「信頼区間」を変更する

予測シートの作成画面左下の「オプション」をクリックし、「信頼区間」の数値を変更でき

ます。初期値では95％が設定されていますが、この95％とは、“100回の内95回はこの範囲に収まる”という意味です。

パーセントが大きければ、信頼区間が広がることになり、予想が当たる確率が高まります。逆にパーセントを小さくすれば、信頼区間が狭まり、予想が難しくなります。

「信頼区間」を非表示にして「予測」だけを表示するには、オプションの「信頼区間」のチェックを外すと、「信頼上限」と「信頼下限」の表示が無くなり、「予測」のみが表示されます。

「季節性」を反映させないようにする

オプションの「季節性」は、初期値では「自動的に検出する」となっています。例えば今回の売上データからは、平日は売上が低く、土日が高くなるというパターンが検出されているため、予測にもそのパターンが反映されています。

「季節性」を「手動設定」にして数値を「0」にすると、検出されたパターンが考慮されず、予測グラフは折れ線から直線に変わります。

予測シートでの「予測」値の算出方法を見てみよう

「予測シート」機能では、グラフの完成と同時に、元となる表も自動で作成してくれます。

ここからは、予測値がどのように算出されているのか気になる方に向けて、自動作成された表を見ながら解説していきます。

下の表の黄色の枠は、当初からあった実績の部分です。

そこから、下の緑の枠の部分に「予測」と「信頼下限」、「信頼上限」が自動で計算されて、表ができあがります。

では、予測の数式を見てみましょう。

3月21日の予測列のセルC22には、「FORECAST.ETS関数」というあまり聞きなれない関数が使われています。「FORECAST」は予測するという意味です。

下の図を見てください。信頼下限の列にあるセルD22と、信頼上限の列にあるセルE22には、先程のFORECAST.ETSの後にCONFINTの文字が入っています。

CONFINTは、信頼区間という意味です。

予測の値からFORECAST.ETS.CONFINT関数で求めた値を、差し引いた答えが信頼下限。加算したら信頼上限となります。

この2つの関数は、統計学でよく使われている関数です。

Excel2013以前は、今回のように売上予測を関数で作成するよう言われると、難しいなと思う人が多いと思います。

しかしExcel2019にある予測シートの設定を使うと、難しい関数を設定することなく、自動的に数式ができあがります。

統計学の知識がないと使いこなせないような関数が、「予測シート」機能のおかげで簡単に活用でき、予測データが作れるのです。

予測シートを使いこなせると、きっとあなたの評価もあがるはず！ぜひ挑戦してみてくださいね。

連載「Excel2013で止まっている人必見！業務効率がUPするExcel2019活用術」、今回は、過去の実績から簡単に将来のデータが作成できる「予測シート」についてご紹介しました。

過去の実績も大切ですが、将来を予測しそれに向かって進んで行くことは、もっと大切です。ぜひ適所で使い、仕事に活かしてみてくださいね。

次回は、以前紹介したマップグラフよりもより高度な「3Dマップ」を紹介します。

これまでの静止したグラフと違い、地図上のグラフを空の上から見て回れるような動画が作れる画期的なグラフです。お楽しみに！

これだけマスター！Excel講座

無料登録でオンラインの資格講座を体験しよう！

資格受け放題の学習サービス『オンスク.JP』では様々な資格講座のオンライン学習が可能です。
最短20秒の無料会員登録で、各講座の講義動画・問題演習の一部が無料体験できます。

※無料会員は、決済情報入力なしでご利用可能。
※自動で有料プランになることはありません。

無料会員登録

オンスク.JP 講座一覧