Excelでトレンド調整指数平滑法を計算する方法を徹底解説

指数平滑法は、最も関連性の高い期間に大きな重みを置く独自の加重計算式を用いて、データからトレンド方程式を算出する手法です。Microsoft Excelを使えば、指数平滑法の計算を正確かつ効率的に行えます。Excelには優れたツールやアドインが多数用意されており、この記事ではExcelでトレンド調整指数平滑法を計算する手順を詳しく解説します。

記事内で使用するワークブックをダウンロードして、実際に練習しながら進めることもできます。

Excelにおける指数平滑法とは?

Excelには「指数平滑法(Exponential Smoothing)」というスムージング機能が標準で搭載されています。主にトレンド予測、データの平滑化、将来予測などに利用され、経営判断に役立つ売上高などの予測にも活用できます。この手法は、説明変数の数を絞り込むことでデータを「平滑化」し、ノイズの少ない傾向を読み取りやすくします。

Excelで指数平滑法ツールを有効にする場所

指数平滑化ツールを使用するには、Excelリボンに「データ分析」タブを表示させる必要があります。「データ分析」機能を使えば、複雑なアルゴリズムを自作することなく、データを直感的に分析できます。以下の手順で「データ分析」を有効にしましょう。

手順:

• まず、リボンの「ファイル」タブをクリックします。

• バックステージビューが開くので、少し下へスクロールして「オプション」をクリックします。
• 「Excel のオプション」ダイアログボックスが表示されます。
• 左側のメニューから「アドイン」を選択し、「Analysis ToolPak(分析ツール)」を選びます。
• 画面下部の「管理」ドロップダウンから「Excel アドイン」を選択します。
• 「設定(Go)」ボタンをクリックします。

• 「アドイン」ダイアログボックスが表示されます。
• 「Analysis ToolPak」にチェックを入れます。
• 「OK」をクリックして設定を完了します。

• 最後に、Excelリボンの「データ」タブを開くと、「分析」グループに「データ分析」が追加されていることが確認できます。

トレンド調整指数平滑法を計算するステップバイステップの手順

調整済み指数平滑法(Trend Adjusted Exponential Smoothing)とは、過去の定量的なデータをもとに、直近期間の実際値と予測値の加重平均を計算し、さらにトレンド調整を加えて予測精度を高める手法です。それでは、具体的な計算手順を見ていきましょう。

ステップ1:必要なデータを入力する

まず、計算に必要なすべての情報をワークシートに入力します。

• B列に期間を入力します。ここでは10期間のデータを使用して指数平滑法を計算します。
• 次に、各期間の需要(実績値)を入力します。
• 続いて、平滑化定数であるアルファ(α)を入力します。本例では20%(0.2)とします。
• さらに、トレンド係数であるベータ(β)を入力します。本例では30%(0.3)とします。

ステップ2:指数平滑法による予測を計算する

Excelの指数平滑化機能は直感的でとても扱いやすいのが特徴です。専用ツールを使う方法と、数式を使う方法の両方があります。まずは「データ分析」ツールを使った方法から見ていきます。

• リボンの「データ」タブに移動します。
• 「分析」グループにある「データ分析」をクリックします。

• 「データ分析」ダイアログボックスが表示されます。
• 一覧をスクロールして「指数平滑法」を選択し、「OK」をクリックします。

• 「指数平滑法」ダイアログが開きます。
• 入力セクションで「入力範囲」と「減衰係数」を指定します。本例では需要の範囲($C$5:$C$14)を指定し、減衰係数にはアルファに対応する値を入力します。
• 「出力オプション」で、計算結果を表示させたい出力先のセル範囲を指定します。
• グラフも同時に表示したい場合は、「グラフ作成」にチェックを入れます。
• 最後に「OK」をクリックして完了です。

• 選択した出力範囲に指数平滑法の結果が表示され、グラフも確認できます。
• ただし、この方法には注意点があります。第1期の指数平滑値が#N/Aエラーになるため、そのままトレンドを計算すると、すべてのトレンド値にも#N/Aエラーが発生してしまいます。

そのため、実務では数式を使った指数平滑法の方がおすすめです。数式による結果は厳密には分析ツールの出力と完全一致するわけではありませんが、非常に近い値が得られます。それでは、数式を使った計算手順を確認しましょう。

• まず、第1期の指数平滑値としては需要の値をそのまま使います。セルD5を選択し、次のシンプルな数式を入力します。

• Enterキーを押します。

• 次に、セルD6を選択し、以下の数式を入力します。

• Enterキーを押して確定します。

• フィルハンドルを下方向へドラッグして、数式を範囲全体にコピーします。または、プラス(+)記号をダブルクリックしてオートフィルしても構いません。

• これで指数平滑法の計算結果が表示されました。

• さらに見やすくするために小数点以下の桁数を減らします。リボンの「ホーム」タブに移動します。
• 「数値」グループの「小数点以下の表示桁数を減らす」をクリックします。

• 以上で完成です!指数平滑法の結果をきれいに確認できます。

ステップ3:トレンドを計算する

次にトレンドを計算します。トレンドとは、時系列データがある期間を通じて、継続的に高い(または低い)値へと移動していることを示すパターンのことです。

• セルE6を選択し、トレンドを求める以下の数式を入力します。

• キーボードのEnterキーを押します。
• 選択したセルに計算結果が表示され、数式バーには入力した数式が確認できます。

• さらに、フィルハンドルを下方向へドラッグして、数式を範囲全体にコピーします。あるいは、プラス(+)記号をダブルクリックしてオートフィルしましょう。

• 第1期のトレンドには0を入力しておきます。以降の期間のトレンド値は、この数式によって自動的に計算されます。

ステップ4:調整済み指数平滑法を求める

続いて、指数平滑値とトレンド値を足し合わせることで、調整済み指数平滑法を計算します。

• まず、結果を表示したいセルを選択します。第1期の調整済み指数平滑値のセルは空白のままにするため、第2期のセルを選びます。
• セルF6に加算の数式を入力します。

• キーボードのEnterキーを押します。
• 選択したセルに結果が表示され、数式バーにも数式が確認できます。

• フィルハンドルを下方向へドラッグして、数式を残りの範囲にコピーします。または、プラス(+)記号をダブルクリックしてオートフィルしてください。

• 最後に、第1期の調整済み指数平滑値には、需要と指数平滑値と同じ値を入力しておきます。
• 以上で完成です!これらの手順に従うだけで、Excelで調整済み指数平滑法を計算できます。

ステップ5:グラフを挿入する

最後に、計算結果をより分かりやすく視覚化するためにグラフを挿入しましょう。グラフは、データ間の関係性を視覚的に示せる便利なツールです。テキストだけでは伝えきれない大量の複雑なデータを、省スペースで効果的に表現できます。

• まず、グラフに使用するデータを選択します。本例では「期間」「需要」「指数平滑法」「調整済み指数平滑法」を選択します。

• 次に、リボンの「挿入」タブに移動します。
• 「グラフ」グループの「散布図(X、Y)またはバブルチャートの挿入」ドロップダウンをクリックします。
• 「散布図(直線付きマーカー)」(散布図の2行目の最初のオプション)を選択します。

• これにより、トレンド調整指数平滑法の計算結果をひと目で確認できるグラフが作成されます。

トレンド調整指数平滑法の最終出力結果

以下が、グラフ付きのトレンド調整指数平滑法の最終的な出力結果です。

押さえておきたいポイント

• 減衰係数の値が大きくなるほど、データのピークや谷は丸められ(平滑化され)ます。
• Excelの指数平滑法は、非常に柔軟で計算もシンプルな手法です。
• 減衰係数が大きい場合は、アルファ(α)の値が小さくなるため、ピークや谷がより強く平滑化されます。
• 逆に減衰係数が小さい場合は、アルファ(α)の値が大きくなり、平滑化の結果が実際のサンプルデータに近づきます。

まとめ

本記事で紹介した手順を実践すれば、Excelでトレンド調整指数平滑法を計算することができます。この記事が皆さんの業務や学習のお役に立てば幸いです。ご質問・ご提案・フィードバックなどがございましたら、ぜひコメント欄でお知らせください。また、ExcelDemy.comブログの他の記事もあわせてご覧いただくと、さらに理解が深まります!