Excel で Holt-Winters 指数平滑法を実行する (簡単な手順)

エクセル 巨大なデータセットを扱う場合、最も広く使用されているツールです。 Excel では、多次元の無数のタスクを実行できます .この記事では、Holt-Winters 指数平滑法の適用方法について説明します。 エクセルで .この方法は予測に役立ちます。

ワークブックをダウンロード

このワークブックをダウンロードして、この記事を読みながら練習してください。

Holt-Winters 指数平滑法について

ホルトウィンターズ method は、値を予測するための高度な方法です。予測を予測しながら、季節性と傾向の影響を考慮します。そのため、一部のランダム性を除いて、値は実際の値を表しています。

Holt-Winters Exponential Smoothing を使用して予測値を計算する式 エクセル です

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

どこ、F =予測値
小 =レベル
T =トレンド
M =4 四半期ごと、12 毎月の期間
S =季節指数

Excel でホルト ウィンターズ指数平滑法を実行するための 11 の簡単な手順

これが今日の記事のデータセットです。 四半期ごとの売上があります 2022 年まで。

2023 年の予測値を計算します。

ステップ 1:ランダムなアルファ、ベータ、ガンマ値を割り当てる

最初のタスクは、定数 alpha にいくつかのランダムな値を割り当てることです 、ベータ版、 とガンマ .

これらの値は後で最適化します。

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

その後、最初の季節指数を決定します 最初の 4 四半期。 各四半期の売上を平均売上で割って、初期の季節指数を決定します 最初の 4 の 四半期 . AVERAGE 関数を使用します

• F11 に移動 次の式を書き留めてください。

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

• では、ENTER を押してください . エクセル 出力を返します。

• その後、フィル ハンドルを使用します オートフィルへ F14まで .

ステップ 3:初期レベルとトレンドを決定する

データセットの初期レベルとトレンドを計算する時が来ました.

初期レベル レベルです 第 5 四半期 4あるので

初期レベルの式は、

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

どこ

小 =レベル。
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)

どこ

小 =レベル
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

どこ

小 =レベル
T =トレンド
M =4 四半期ごと、12 毎月の期間
S =季節性指数
β= 係数

トレンド効果を計算するには、

• E6 に移動 次の式を書き留めます

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

• ENTER を押します 続行します。

• オートフィル E22まで .

ステップ 7:予測値を見つけて実際の売上と比較する

次に、実際の販売と比較するための予測値を計算します。最初のものは第 6 四半期のものです。予測値を計算する式 (比較用) は次のとおりです。

ふ t =(L t-1 +T t-1 )* S t-M

では、やってみましょう。

• G16 に移動 次の式を書き留めます

=(D16+E16)*F12

• ENTER を押します 行きます。

• オートフィル G22まで .

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

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

• H16 に移動 数式を書き留めます

=C16-G16

• 次に ENTER を押します .

• オートフィル H22まで .

ステップ 9:予測する四半期に K 値を割り当てる

予測を計算する時が来ました。しかしその前に、係数 k を理解する必要があります。 .これは、予測の将来の時間を表します。この場合、4 の予測を計算します 2023 年の四半期。2022 年のデータも利用可能です。

したがって、2023 年の第 1 四半期では k の値は 1 になり、第 2 四半期では 2 というようになります。

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

これで、予測値を計算する準備が整いました。最新の利用可能なレベル、トレンド、および季節性を使用して計算します。

• G23 に移動 次の式を書き留めます

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

• 次に ENTER を押します 出力を取得します。

• さて、オートフィル G25まで .

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

ここで、エラーを最小限に抑えるために、alpha の値を最適化します。 、ベータ版 、およびガンマ . Excel ソルバーを利用します

• まず、二乗平均平方根誤差を計算する必要があります。これを行うには、C7 に移動します 次の式を書き留めます

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

数式の内訳:

• COUNT(H15:H21) →細胞数を数えます。
  • アウトプット → 7
• SUMSQ(H15:H21) → H5:H11 の二乗和を計算 .
  • 出力 → 463493653301
• =SQRT(SUMSQ(H15:H21)/COUNT(H15:H21)) → RMSE を計算します
• =SQRT(992.463493653301/7)
• =SQRT(141.780499093329)
  • 出力 → 9072

• 次に、ENTER を押します .

• さて、データに移動します タブ>> ソルバーを選択 .

• ソルバー パラメータ ウィンドウがポップアップします。エラーを最小限に抑えたいので、目的は RMSE を設定することです 係数の値を変更して最小にする .
• その後、制約を追加するには、[追加] をクリックします。 .

<強い>

• [制約の追加] ウィンドウが表示されます。制約は 0<=α,४,ß<=1 です .最初の制約を追加するには、セル参照を設定します そして価値 . (画像参照)

<強い>

• 同様に、2 番目の制約を追加すると、出力は次のようになります。次に [解決] をクリックします .

• エクセル alpha を最適化することでエラーを最小限に抑えます 、ベータ版、 とガンマ .

覚えておくべきこと

• ソルバー アドインを有効にする必要があります .
• k 値は気にしません 利用可能な実際の売上と比較する予測を計算するため。

結論

この記事では、Holt-Winters 指数平滑法の適用方法について説明しました。 エクセルで .皆さんのお役に立てば幸いです。質問があればコメントしてください。