Excelでホルト・ウィンターズ(Holt-Winters)指数平滑法を実行する方法|初心者向け11ステップ解説

Excelは、大量のデータを扱う際に最も広く使われているツールです。多岐にわたるタスクをExcel上で実行できます。この記事では、ホルト・ウィンターズ(Holt-Winters)指数平滑法をExcelで適用する方法を詳しく解説します。この手法は将来の値を予測する際に非常に役立ちます。

練習用ワークブックのダウンロード

この記事を読みながら実際に操作できるよう、練習用ワークブックをダウンロードしてご利用ください。

ホルト・ウィンターズ指数平滑法とは?

ホルト・ウィンターズ法は、高度な予測手法の一つです。この方法の最大の特徴は、季節性(seasonality)と傾向(trend)の影響を考慮しながら予測値を算出する点にあります。そのため、ランダムな変動を除けば、予測値は実際の値に非常に近い結果となります。

Excelでホルト・ウィンターズ指数平滑法を用いて予測値を計算する基本式は以下の通りです。

Ft+k = (Lt+k × Tt) × St-m+k

各記号の意味は次の通りです。

• F = 予測値(Forecasted Value)
• L = 水準(Level)
• T = 傾向(Trend)
• M = 四半期データの場合は4、月次データの場合は12
• S = 季節指数(Seasonality Index)

Excelでホルト・ウィンターズ指数平滑法を実行する11のステップ

今回使用するデータセットは、2022年までの四半期ごとの売上データです。

このデータをもとに、2023年の予測値を計算していきます。

ステップ1:アルファ・ベータ・ガンマの仮の値を入力する

最初に行うのは、定数であるアルファ(α)、ベータ(β)、ガンマ(γ)に仮の値を設定することです。

これらの値は後ほど最適化しますので、まずは適当な値を入れておきましょう。

ステップ2:初期季節指数を計算する

次に、最初の4四半期における初期季節指数を求めます。計算方法は、各四半期の売上を、最初の4四半期の平均売上で割るというものです。ここではAVERAGE関数を使用します。

• セルF11に以下の数式を入力します。

=C11/AVERAGE($C$11:$C$14)

• Enterキーを押すと、計算結果が表示されます。

• 続けて、フィルハンドルを使ってF14までオートフィルします。

ステップ3:初期水準と初期傾向を求める

ここからは、データセットの初期水準と初期傾向を計算します。

1年が4四半期で構成されているため、初期水準は第5四半期の水準として定義されます。

初期水準を求める式は以下の通りです。

L5 = Y5 / S1

• Y5 = 第5四半期の売上
• S1 = 第1四半期の季節指数

具体的には、

• セルD15に以下の数式を入力します。

=C15/F11

• Enterキーを押して計算結果を確認します。

続いて、初期傾向(こちらも第5四半期の値)を計算します。初期傾向を求める式は以下の通りです。

T5 = L5 − Y4 / S4

• L5 = 第5四半期の水準
• Y4 = 第4四半期の売上
• S4 = 第4四半期の季節指数

具体的には、

• セルE15に以下の数式を入力します。

=D15-C14/F14

• Enterキーを押して結果を取得します。

ステップ4:次の季節指数を計算する

次に、一般式を使って以降の季節指数を計算します。季節指数を求める一般式は以下の通りです。

St = γ(Yt / Lt) + (1 − γ)St-m

• L = 水準
• T = 傾向
• M = 四半期データの場合は4、月次データの場合は12
• S = 季節指数
• γ = 係数

具体的には、

• セルF15に以下の数式を入力します。

=$C$6*(C15/D15)+(1-$C$6)*F11

• Enterキーを押します。

• F22までオートフィルします。

注意:この時点ではエラーが表示されていても問題ありません。次の水準と傾向を計算すれば正しい値に変わります。

ステップ5:次の水準を求める

続いて、以下の式を使って次の水準を計算します。

Lt = α(Yt / St-m) + (1 − α)(Lt-1 + Tt-1)

• L = 水準
• T = 傾向
• M = 四半期データの場合は4、月次データの場合は12
• S = 季節指数
• α = 係数

具体的には、

• セルD16に以下の数式を入力します。

=$C$4*(C16/F12)+(1-$C$4)*(D15+E15)

• Enterキーを押します。

• D22までオートフィルします。

ステップ6:次の傾向を求める

次に、傾向効果を計算します。使用する式は以下の通りです。

Tt = β(Lt − Lt-1) + (1 − β)Tt-1

• L = 水準
• T = 傾向
• M = 四半期データの場合は4、月次データの場合は12
• S = 季節指数
• β = 係数

具体的には、

• セルE16に以下の数式を入力します。

=$C$5*(D16-D15)+(1-$C$5)*E15

• Enterキーを押します。

• E22までオートフィルします。

ステップ7:実際の売上と比較するための予測値を求める

ここで、実際の売上と比較するための予測値を計算します。最初の予測対象は第6四半期です。比較用の予測値を求める式は以下の通りです。

Ft = (Lt-1 + Tt-1) × St-M

それでは計算していきましょう。

• セルG16に以下の数式を入力します。

=(D16+E16)*F12

• Enterキーを押します。

• G22までオートフィルします。

ステップ8:予測誤差を計算する

次に、実際の売上から予測値を引いて、予測誤差を計算します。

• セルH16に以下の数式を入力します。

=C16-G16

• Enterキーを押します。

• H22までオートフィルします。

ステップ9:予測対象四半期のk値を設定する

いよいよ予測値の計算に入りますが、その前に係数kについて理解しておく必要があります。kは予測対象となる将来の時点を表す値です。今回は2023年の4四半期を予測しますが、データは2022年までしかありません。

したがって、2023年第1四半期のkは「1」、第2四半期は「2」、というように設定します。

ステップ10:予測値を計算する

これで予測値を計算する準備が整いました。最後に得られた水準・傾向・季節指数を使用して予測を行います。

• セルG23に以下の数式を入力します。

=($D$22+F23*$E$22)*F19

• Enterキーを押して結果を確認します。

• 続けて、G25までオートフィルします。

ステップ11:アルファ・ベータ・ガンマを最適化する

最後に、誤差を最小化するためにアルファ(α)、ベータ(β)、ガンマ(γ)の値を最適化します。ここではExcelのソルバー(Solver)を活用します。

• まず、二乗平均平方根誤差(RMSE)を計算します。セルC7に以下の数式を入力してください。

=SQRT(SUMSQ(H15:H21)/COUNT(H15:H21))

数式の解説:

• COUNT(H15:H21) → セルの個数をカウントします。
  • 結果 → 7
• SUMSQ(H15:H21) → H15:H21の各値の二乗和を計算します。
  • 結果 → 463493653301
• =SQRT(SUMSQ(H15:H21)/COUNT(H15:H21)) → RMSE(二乗平均平方根誤差)を算出します。

• Enterキーを押します。

• 次に、データタブ → ソルバーを選択します。

• ソルバーパラメーター画面が表示されます。誤差を最小化したいので、目的は係数の値を変更変数としてRMSEを最小化することに設定します。
• 続けて、制約条件を追加するために追加ボタンをクリックします。

• 制約条件の追加画面が表示されます。制約条件は0 ≤ α、β、γ ≤ 1です。最初の制約条件として、セル参照と値を設定しましょう(画像参照)。

• 同様に2つ目の制約条件も追加すると、以下のような状態になります。すべて設定できたら解決をクリックします。

• Excelがアルファ・ベータ・ガンマを最適化し、誤差を最小化してくれます。

注意ポイント

• ソルバー アドインを事前に有効化しておく必要があります。
• 既存の実際の売上と比較するための予測値を計算する際には、k値は考慮しません。

まとめ

この記事では、Excelでホルト・ウィンターズ指数平滑法を適用する方法を段階的に解説しました。季節指数・水準・傾向の計算から、ソルバーによる係数の最適化まで、一連の流れをマスターすれば、季節変動を含むデータでも精度の高い売上予測が可能になります。ご不明な点があれば、ぜひコメントでお知らせください。