:::

用Excel實作指數平滑法 / Exponential Smoothing in Excel

image

指數平滑法是時間序列分析中用來預測的一種常用的做法。該做法是改良自移動平均法,將越久遠的觀察值對預測的影響降低。指數平滑法可以直接用Excel的「資料分析」工具來計算,我就根據「数据建模与决策」中「第11章 时间序列分析和预测」的介紹來練習一下吧。


時間序列資料 / Time Series Data

這邊使用的是「数据建模与决策」中「第11章 时间序列分析和预测」的表11-4的資料,包括年份以及居民消費價格指數(%)。我一樣做成Google試算表供大家參考:

image

下載之後可以用Excel開啟,大概會像上圖一樣。

開啟Excel的分析工具箱 / Open Data Analysis

image

指數平滑法的功能在Excel的分析工具箱(資料分析)當中,可是Excel預設並沒有開啟分析工具箱,我們必須手動開啟。

做法可以參考小歐ou菜鳥自救會的[Office] Excel 2010 載入分析工具箱 (Data Analysis)教學。他的圖文教學已經寫的很詳細了,我就不再重複,只概述操作步驟:

  1. 檔案 > 選項
  2. 在Excel選項中,開啟 增益集
  3. 找到「分析工具箱」,按下下面的「執行」按鈕
  4. 打勾「分析工具箱」
  5. 一路「確定」回到主畫面

這樣子在「資料」標籤裡面應該可以看到「資料分析」的按鈕了。

有這個工具之後,我們就能夠來做指數平滑法囉。


1. 在「資料分析」中選擇「指數平滑法」

2016-10-23_020648

首先,先在「資料」標籤裡面打開最右邊的「資料分析」功能,然後從中選擇「指數平滑法」,按下確定。

2. 指數平滑法設定

2016-10-23_021031

在「指數平滑法」對話視窗裡面,我們有四個參數要設定:

  • 輸入範圍:請選擇「居民消費價格指數」資料的B2:B16。
  • 阻尼因子:也就是「1-平滑係數」。值介於0到1之間。數字越小,表示過去影響預測的程度越少。一般取值不應小於0.5,本例中先將阻尼因子設為0.5。
  • 輸出範圍:預測值的輸出範圍,我們將「居民消費價格指數」右邊一欄作為輸出預測值的範圍,設C2:C16。
  • 圖表輸出:請打勾。

3. 檢視預測值

2016-10-23_021542

Excel會自動產生指數平滑法所計算出來的預測值跟圖表。C2的值為「#N/A」是因為當期沒有資料可以預測。直接觀看圖表,可以發現其實預測值跟實際值有點差異。

這樣子指數平滑預測結果算好嗎?我們可以試著改變「阻尼因子」來看看怎樣子的預測比較符合實際值。在「数据建模与决策」中舉的例子是使用0.5、0.3、0.1來計算,最後再用誤差平均和來找尋預測值最準的「阻尼因子」。

4. 計算誤差平方和

2016-10-23_022244

我們以D欄作為計算誤差平方的位置。首先先在D3設定公式「=(B3-C3)^2」,意思就是「實際值-預測值」的平方,得到結果為0.64。

2016-10-23_022520

以此類推將公式複製到D4:D16。

2016-10-23_022548

最後再到D17計算所有誤差平方之和,得到884.9477486。

在「数据建模与决策」書中以阻尼係數0.3得到的誤差平方和為810.3、阻尼係數0.1得到的誤差平方和為731.9,可以發現阻尼係數0.1的誤差最小,也就是預測效果最好。但是阻尼係數的值低於0.5,通常表示該資料有某種趨勢或是波動過大,不適合用指數平滑法來預測。

Excel中提供的預測分析也只有「移動平均法」跟「指數平滑法」這兩種。更進階的預測方法就得仰賴SAS、SPSS Trends或是R等專門統計分析工具了。