Introduction
Ver. 2016 2019 2021 365
Excelでは「予測シート」という機能で、これまでの数値データから今後の予測をグラフ化することができます。
バージョン2016以降の機能なので、予測シート機能が使えない方は、バージョンを確認してみましょう。
これまでの数値データから今後を予測するにゃ
予測の根拠となる計算方法は？
予測してくれると言っても、その予測の根拠を全く知らないと信頼して良いのか分かりません。
しかし、深く知ろうとすると、専門用語の理解や統計学の学習が必要です。
ここでは、簡単に「こんな感じで予測しているよ」とざっくり説明してみます。
予測計算には、エクセルで使用できるFORECAST関数と同じものが使用されます。
「指数三重平滑化 (ETS) アルゴリズムの AAA バージョン」です。
とりあえず適当なものではないみたい
「指数三重平滑化」が気になりますね。
- 指数平滑法・・・移動平均法の一つで、古いデータよりも新しいデータを重視する平滑法
うん。難しくなってきました。
予測を算出するには、移動平均法や加重移動平均法などありますが、指数平滑法を使用しているということですね。
では、「指数平滑法とは・・・」という感じでさらに細かい話になっていきますが、ここでは割愛します。
もう少し詳しく知りたい方は、マイクロソフトのこちらのページをご覧ください。
とりあえず、
この予測は何が元になっているの？
と聞かれたら、
という答えで良いのでは。
もう少し具体的な説明が必要な場合は
となりますね。
予測シートの使い方
では、下の一か月の売上データから今後の売上を予測しましょう。
データ部分を選択します。
選択する範囲は、日付、時刻などの列と、それに対応する数値データです。
時系列のデータが必要なことに注意しましょう。
選択したら、［データ］タブ → ［予測シート］をクリック。
最終的には、新しいワークシートが作成されて［履歴値］（選択した場所のデータ）、［予測値］、［グラフ］が表示されるのですが、
まずは、下のような予測値が含まれたグラフだけが表示されます。
赤枠で囲んだ凡例部分を見ると、それぞれの折れ線の意味が分かります。
画面右側のオレンジ色の折れ線が予測で、その上下の薄い線がそれぞれ「信頼下限」と「信頼上限」です。
まずチェックしたいのが左下の［予測終了］の場所です。
カレンダーのボタンを押すと、予測期間を調整することができます。
少し期間を短くしてみました。
予測期間が最初よりも短くなりましたね。
細かい指定を行う場合は、さらに下にある［オプション］ボタンを押します。
下にオプション画面が表示されます。
［信頼区間］の場所で、予測の上下の「信頼下限」と「信頼上限」の折れ線を調整できます。
最初は「95%」になっています。
これを「70％」にすると、下のようになります。
「信頼下限」と「信頼上限」の幅が狭くなりました。
↓
最初の「95%」は確かに信頼性が高いですが、信頼下限と上限はかなり広くなってしまいます。
「そんなに広い範囲なら、そりゃ必ず当たるよ」と言われてしまいそう。
少し調整しても良いかもしれません。
また、チェックを外すと「信頼下限」と「信頼上限」を消すこともできます。
見栄えを考えると、消してしまう選択も考えられますね。
次に、［季節性］の場所です。
「季節性」とは、決まった時期に数値が上下する場合に指定します。
例えば、月ごとの年間データで年末は必ず数値が上がる場合や、日ごとのデータで週末は数値が必ず下がる場合など。
前者の年間での傾向であれば「12」を指定して、後者の週ごとの傾向であれば「7」を指定することになります。
しかし、最初に［自動的に検出する］のチェックが入っているため、自動でこの季節性を読み取って適切な設定にしてくれているはずです。
次の［予測統計情報を含める］にチェックを入れると、
使用された統計情報が下のような形式で追加表示されます。
通常の業務でこの表を見て「なるほど！」と思う方はごく小数だと思いますので、付ける必要はないでしょう。
右側の部分で注意したい場所としては［見つからない点の入力方法］です。
この場所は、元のデータに空欄があった時の対応方法を［補間］と［ゼロ］から選ぶことができます。
例えば、下のデータ２か所が空欄になっています。
その時に、［補間］が選ばれていたときは、
その2か所は前後のデータから補間された数値で表示されます。
［ゼロ］にした場合は、
空欄の場所は「0」が入力されたものとして処理されます。
状況によりますが、見栄えを考えると［補間］を選ぶことが多いと思います。
元のデータが「空欄」の時だけ［補間］が使用できます。
［補間］を使用してグラフを作成する際は、データ未入力の場所は「0」を入力せず「空欄」にしておきましょう。
最後に右下の［作成］を押せば完成です。
まとめ
予測は客層や気候や社会情勢など数多くの要素から行うものであり、業務内容によってさまざまです。
その要素の1つとして、エクセルの「予測シート」を活用するのも面白いのではないでしょうか。
元のデータの総数やバラつきによって、予測のグラフが極端な結果になることもありますので注意してください。
右上のボタンで棒グラフにもできるんだにゃ