Excelによるデータ分析

Excelで簡単に予測とシミュレーション

時系列分析機械学習 を使うと予測ができますが、RやPythonといったソフトでは、手間がかかります。

Excelには、予測の方法が2種類用意されています。 重回帰分析 と、三重指数平滑法 です。 重回帰分析は、機械学習の基本になる方法です。 三重指数平滑法は、時系列分析の一種です。 私見ですが、いずれも汎用的に使えます。

三重指数平滑法については、専用のボタンもあり、びっくりするくらい簡単に使えます。

三重指数平滑法による予測

時間軸のデータの列と、予測したいデータの列を用意して、「データ」のタブにある「予測シート」をクリックします。
Excelによる予測モデル


下のようなウィンドウが出るので、「作成」をクリックします。
Excelによる予測モデル


できました。 手順はこれだけです。
Excelによる予測モデル

オレンジの線は、予測区間

青い線が、元のデータを表しています。 オレンジの線が予測値です。

オレンジの線は、3本できますが、真ん中の太い線が中心の値です。

上下の太い線は、「信頼下限」と「信頼上限」と書いてありますが、これは、データの現れる範囲を表しています。 デフォルトでは、95%の確率で現れる範囲です。

「信頼」と付いているので、 信頼区間 のように見えますが、中心値の分布ではなく、データの分布を表しているので、 予測区間 の一種です。

予測値が、範囲でわかるので、実務向きのツールになっています。 範囲がわかると、どのくらいの精確さの予測なのかがわかります。 また、範囲の上側と下側のそれぞれに対して、「もしもこうなった場合は」というアクションを考えておくことができます。

この例の場合は、7/5は、65000円から75000円の範囲と予測しています。

最近2、3年で、 コンフォーマル予測 の技術が注目されています。 Excelの予測シートは、正確にはコンフォーマル予測ではないですが、「範囲を予測する」という、コンフォーマル予測のひとつの利点ができるようになっています。

関数でも使える

結果のグラフのデータのセルを見ると、「FORECAST.ETS」という関数が使われていることがわかります。 この関数は、指数平滑法という予測のための手法の関数です。

Excelでは、「FORECAST.ETS」やその仲間で信頼区間を求める「FORECAST.ETS.CONFINT」という関数がボタン一つで簡単に使えるようになっています。 もちろん、一般的な関数のように、どこかのセルに関数を書いて、そのセルに計算結果を出す使い方もできます。

三重指数平滑法とは

三重指数平滑法は、とても強力なのですが、あまり知られていません。 「指数平滑法」という名前で、単純指数平滑法を紹介している本はあるものの、三重指数平滑法となると、日本語の本で紹介しているものはないようです。

幸い、ネットで読める教科書(日本語版)に、詳しく書かれていました。 「 Hyndman, R.J., & Athanasopoulos, G. (2021) Forecasting: principles and practice, 3rd edition, OTexts: Melbourne, Australia. OTexts.com/fppjp. Accessed on 2025/10/15 (予測: 原理と実践 (第3版)) https://otexts.com/fppjp/ets-forecasting.html」 の8章です。

FORECAST.ETS関数のサポートページを見ると、「指数三重平滑化 (ETS) アルゴリズムの AAA バージョン」と書かれています。 この本によれば、「AAA」というのは、「誤差、トレンド、季節性の3つのそれぞれが加法型」という意味です

このサイトでは、 三重指数平滑法 のページで、詳しく説明しています。

回帰分析による予測

まず、データを用意します。 予測したいのは売上です。
Excelによる予測モデル

「データ」タブの、「データ分析」を選びます。
Excelによる予測モデル

「回帰分析」を選びます。
Excelによる予測モデル

データの範囲を入力します。 Yが元データで、Xが新しく作った変数です。 ラベルにもチェックを入れます。
Excelによる予測モデル

分析データが新しくできたシートに記入されます。

まず、重決定R2(相関係数Rの二乗:寄与率)が0.97…なので、高い精度があることがわかります。

精度については、標準誤差からもわかります。 予測区間 は、標準誤差の±約2倍の範囲が95%の予測区間になります。 標準誤差が約2000(1929…)となので、予測値を中心として、上下に約4000円ずつばらつきがあります。 そのため、これは、例えば、「予測値が50000円の場合、実際は46000円〜54000円にある可能性はあるが、その範囲より外は可能性が低い」ということがわかります。

気温の係数が約2000(2193…)ですが、これは、1℃上がると売上が約2000円上がることを意味しています。

雨の係数が約8000(8300…)ですが、これは、雨の時の方が、売上が約8000円高いことを意味しています。


Excelによる予測モデル

TREND関数を使うと、 重回帰分析 による予測ができます。

下の例は、30℃で雨の時と、25℃で晴の時の予測です。
Excelによる予測モデル

なお、データは、21℃から28℃なので、 外挿 です。 そのため、予測はできますが、取り扱い注意です。

25℃で雨の時のデータがあるので、25℃で晴の時は、「 反事実 」と言います。

TREND関数で予測できるのは、中心値です。 予測シートのように、予測区間は出ませんが、予測区間は、中心値に標準誤差から求めた予測区間を加味したものになります。

質的データの扱い方

天気なら、データは「晴」、「雨」、「曇」としたいところですが、TREND関数は数値データしか扱えないので、 ここでは、「雨 = 1」としました。

0と1しかダメなわけではないので、「曇 = 0.5」としたり、 「半日雨 = 0.5」という工夫もできます。



Excelによるデータ分析


杉原データサイエンス事務所のロゴ
杉原データサイエンス事務所によるコンサルティングとセミナー