我們一起了解了什麼資料有時帶有周期的,如何識別.並且講解了如何透過CMA和週期指數進行資料過濾,我們今天一步一步透過EXCEL來完成 winter週期預測.

繪製CMA 中心移動平均線與多項式迴歸

需要資料或者有興趣一起完成的看官老爺們,可以到文章底部獲取資料哦,

透過 =AVERAGE() 獲取中心平均值

這裡需要注意,最下面一列需要為空,否者最後一個平均值只會獲取前三個數值

使用中心平均值是為了平移移動平均線,使其不至於過於延遲顯示,可以理解為MA向後移動幾個週期, cma = [週期 / 2] + 1 透過折中取整的方式來獲取偏移量, 例如 5 / 2 + 1 = 3

# python中可以這樣實現# 其中 pre為週期cma_shift = int(pre / 2) + 1

透過插入 --> 圖表 --> 選中資料 --> 對對應的資料新增趨勢線

在側欄中找到多項式迴歸,並且選擇顯示公式和顯示R²值

在這裡需要注意一個問題,X軸建議選取資料中最前面的序列號,由於計算機中對時間資料一般會處理為時間戳(timestamp)會造成X值取值有誤

如果一切順利的話,現在我們會看見公式

y = -6.0306x3 - 1983.4x2 + 184662x + 5E+06

當然如果你的資料和我不同,可能會有不同的結果.

我們再透過 =MONTH()函式計算月資料,方便後面匯入資料透視表

計算winter指數

需要預測的 Y / CMA

然後我們選取資料並且建立資料透視表

欄位選取 列=月/季 行=年 顯示值為平均值項 winter_index()

透過矩陣相等 {=B5:F16} 來複制 資料透視表中直接進行欄位運算無法拖動框選值

我們可以看見,最後一個值並非 1, 我們需要透過計算對值進行調整

輸入公式,獲取預測值

透過預測值 * winter_index值獲取最終的Y帽(Yhat)

繪製圖像

獲取資料

你可以直接複製下列資料,

,報告期,營業利潤(萬元)

1,2010年3月,5391700

2,2010年6月,5636700

3,2010年9月,5548900

4,2010年12月,4871400

5,2011年3月,6958200

6,2011年6月,7192100

7,2011年9月,6974100

8,2011年12月,5975600

9,2012年3月,6958200

10,2012年6月,7192100

11,2012年9月,6974100

12,2012年12月,5975600

13,2013年3月,7960100

14,2013年6月,8005700

15,2013年9月,7939200

16,2013年12月,6840800

17,2014年3月,7960100

18,2014年6月,8005700

19,2014年9月,7939200

20,2014年12月,6840800

21,2015年3月,8823500

22,2015年6月,9015800

23,2015年9月,8564400

24,2015年12月,7300900

25,2016年3月,8823500

26,2016年6月,9015800

27,2016年9月,8564400

28,2016年12月,7300900

29,2017年3月,9536000

30,2017年6月,9814600

31,2017年9月,9399900

32,2017年12月,7210700

33,2018年3月,9536000

34,2018年6月,9814600

35,2018年9月,9399900

36,2018年12月,7210700

37,2019年3月,9638900

38,2019年6月,9696000

39,2019年9月,9552500

40,2019年12月,7066100

41,2020年3月,9638900

42,2020年6月,9696000

43,2020年9月,9552500

我們可以透過EXCEL中資料 --> 匯入txt資料

或者 將資料儲存到txt檔案後,將字尾修改為CSV,兩種方法匯入資料.

這裡的資料是每季實際資料,而非當年累計資料,

好了這裡就完成了從資料獲取到完成winter指數週期預測的所有步驟了