第12章 時間數(shù)列分析與預測 2009

1、LOGO于洪彥于洪彥 劉金星劉金星 張洪利張洪利Excel Excel 統(tǒng)計分析與決策統(tǒng)計分析與決策第第1212章章 時間序列分析與預測時間序列分析與預測 主要內(nèi)容主要內(nèi)容 12.1 時間數(shù)列的基本特征 12.2 移動平均分析與預測 12.3 回歸分析與預測 12.4 指數(shù)平滑法與預測 12.5 季節(jié)變動的測定與分析 12.6 周期波動的測定與分析 v時間數(shù)列的概念與特點 時間數(shù)列按時間先后順序排列 時間數(shù)列是按一定方式搜集的一系列數(shù)據(jù) 時間數(shù)列中的觀察值具有差異 時間數(shù)列中的數(shù)據(jù)不許遺漏12.1 12.1 時間數(shù)列的基本特征時間數(shù)列的基本特征 v時間數(shù)列的構成與分解 乘法模型的一般形式為：

2、Y=TSCI 式中 Y、T是總量指標 S、C、I為比率 加法模型的一般形式為： Y=T+S+C+I 式中Y、T、S、C、I都是總量指標 12.2 12.2 移動平均分析與預測移動平均分析與預測v移動平均法的基本內(nèi)容 移動平均法是在算術平均法的基礎上發(fā)展起來的預測方法。 它是利用過去若干期實際值的均值來預測現(xiàn)象的發(fā)展趨勢 簡單移動平均公式如下： njjttANM111112.2 12.2 移動平均分析與預測移動平均分析與預測v利用趨勢圖測定長期趨勢 例 12.1 對2008年的零售額進行預測。 產(chǎn)生“年季”變量 選擇“移動平均”工作表。 在C列選定任一個單元格，選擇“插入”菜單中的“列”選項，則

3、原來C列的內(nèi)容被移到D列。在C1單元格中輸入標志“年季” 在單元格C2中輸入公式“=B2&CHAR(13)&A2”。再把單元格C2中的公式復制到C3：C21。結果如圖所示。12.2 12.2 移動平均分析與預測移動平均分析與預測 根據(jù)圖12.2中資料繪制銷售額趨勢圖。 打開“插入”菜單中的“圖表”選項，Excel彈出“圖表向?qū)А睂υ捒颉?在步驟1的“圖表類型”列表中選擇“折線圖”選項；在“子圖表類型”列表中選擇“數(shù)據(jù)點折線圖”。單擊“下一步”按鈕 ，進入向?qū)Р襟E2對話框。12.2 12.2 移動平均分析與預測移動平均分析與預測 在步驟2的“圖表數(shù)據(jù)源”對話框中，在數(shù)據(jù)區(qū)域輸入

4、“C1：D21”，并單擊“下一步”按鈕，進入向?qū)Р襟E3對話框。 在步驟3的“圖表選項”中，選擇“標題”頁面，在“圖表標題”、“分類（X）軸”、“數(shù)值（Y）軸”中分別填入“某地區(qū)銷售額趨勢圖”、“季度”和“銷售額（萬元）”；選擇“網(wǎng)格線”頁面，把“數(shù)值（Y）軸”下的“主要網(wǎng)格線”設為空；選擇“圖例”頁面，取消圖例顯示。單擊“完成”按鈕，得到趨勢圖。12.2 12.2 移動平均分析與預測移動平均分析與預測 在圖表中插入趨勢線進行預測 單擊圖表以激活它，選取垂直軸，雙擊或單擊鼠標右鍵并從快捷菜單中選擇“坐標軸格式”選項，Excel彈出對話框。打開“刻度”頁面，在“最小值”、“最大值”、“主要刻度單位

5、”中分別輸入200、450和50；打開“字體”頁面，設置字號為“8”。單擊“確定”按鈕。 選取水平軸，雙擊或單擊鼠標右鍵并從快捷菜單中選擇“坐標軸格式”選項，Excel彈出對話框。打開“對齊”頁面，取消自動設置；打開“字體”頁面，設置字號為“8”。單擊“確定”按鈕。12.2 12.2 移動平均分析與預測移動平均分析與預測 選取圖中的折線，單擊鼠標右鍵并從快捷菜單中選擇“添加趨勢線”選項，打開“添加趨勢線”對話框。選擇“類型”頁面，在“趨勢預測/回歸分析類型”框中選擇“移動平均”，設置“周期”為4。 單擊“確定”按鈕。12.2 12.2 移動平均分析與預測移動平均分析與預測v 利用Excel創(chuàng)建

6、公式測定長期趨勢 選擇“移動平均”工作表。 在單元格E1中輸入“公式預測值”。 在單元格E6中輸入公式“=AVERAGE(D2：D5)”。 把單元格E6中的公式復制到E7：E22各單元格中。結果如圖所示。12.2 12.2 移動平均分析與預測移動平均分析與預測v移動平均分析工具測定長期趨勢 移動平均分析工具的內(nèi)容12.2 12.2 移動平均分析與預測移動平均分析與預測 利用移動平均分析工具進行預測 選擇“移動平均”工作表。 在單元格F1、G1中分別輸入“分析工具預測值”和“預測標準誤差”。 從“工具”菜單中選擇“數(shù)據(jù)分析”選項，在彈出的“數(shù)據(jù)分析”對話框中選中“移動平均”選項，并單擊“確定”按

7、鈕，出現(xiàn)“移動平均”對話框。 在輸入?yún)^(qū)域中輸入D1：D21，選定標志位于第一行，間隔設為4，在輸出區(qū)域中輸入F2。選擇圖表輸出和標準誤差。單擊“確定”按鈕，所得結果如圖所示。12.2 12.2 移動平均分析與預測移動平均分析與預測v 時間數(shù)列預測工作表函數(shù) FORECASTFORECAST預測函數(shù)預測函數(shù) TRENDTREND趨勢函數(shù)趨勢函數(shù) GROWTHGROWTH增長函數(shù)增長函數(shù) LINESTLINEST線性擬合函數(shù)線性擬合函數(shù) LOGESTLOGEST函數(shù)函數(shù)v 使用直線函數(shù)LINEST和趨勢函數(shù)TREND線性預測法 使用LINEST函數(shù)計算回歸統(tǒng)計值 例12.2 用最小平方法進行趨 勢

8、預測11、12月份的銷售量。 選擇“趨勢”工作表。12.3 12.3 回歸分析測定長期趨勢回歸分析測定長期趨勢在E1中輸入“回歸統(tǒng)計量”。選擇E2：F6，單擊“插入”菜單，選擇“函數(shù)”，Excel彈出“插入函數(shù)”對話框。在“選擇類別”中選擇“統(tǒng)計”，在“選擇函數(shù)”中選擇“LINEST”函數(shù)，則打開LINEST函數(shù)對話框。在Known_ys、 Known_xs和Stats后分別輸入B2：B11、A2：A11、和1。按住Ctrl+Shift組合鍵，單擊 “確定”按鈕。12.3 12.3 回歸分析測定長期趨勢回歸分析測定長期趨勢 在選中E2：F6的情況下輸入數(shù)組公式：“=LINEST(C2:C11,

9、B2:B11,1)”，同時按住Ctrl+Shift兩鍵，再按回車鍵。得到的結果與上面操作相同。計算預測值： 在D1單元格中輸入“預測值”。 在D2單元格中輸入公式“=$F$2+$E$2*B2”。 將D2單元格中的公式復制到D3：D11各單元格。12.3 12.3 回歸分析測定長期趨勢回歸分析測定長期趨勢 使用趨勢函數(shù)TREND求預測值 在單元格C1中輸入“擬合值”。 選定單元格C2:C11。 在“插入”菜單中選擇“函數(shù)”選項，打開“插入函數(shù)”對話框。 在“選擇類別”中選擇“統(tǒng)計”，在“選擇函數(shù)”中選擇TREND函數(shù)，單擊“確定”按鈕，將打開TREND對話框。12.3 12.3 回歸分析測定長期

10、趨勢回歸分析測定長期趨勢 在known_ys中輸入B2：B11；在known_xs中輸入A2：A11；由于不需預測，故忽略new_xs；意在保留常數(shù)項，故忽略const。按住Ctrl+Shift組合鍵，單擊“確定”。 也可以在選定單元格C2：C11的情況下，輸入數(shù)組公式“=TREND(B2:B11,A2:A11)”，按住Ctrl+Shift組合鍵，按回車鍵。輸出結果如圖所示。12.3 12.3 回歸分析測定長期趨勢回歸分析測定長期趨勢趨勢預測在單元格A12中輸入11。在單元格C12中輸入公式：“=TREND(B2:B11,A2:A11,A12)”。用TREND函數(shù)也可以進行多期預測在區(qū)域A12

11、:A25中分別輸入1124。選定區(qū)域C12:C25，輸入數(shù)組公式：=TREND(B2:B11,A2:A11,A12:A25)，按住Ctrl+Shift組合鍵，按回車鍵。將在C12：C25的各單元格中返回第11到第24個時間點的預測值。12.3 12.3 回歸分析測定長期趨勢回歸分析測定長期趨勢v 使用指數(shù)函數(shù)LOGEST和增長函數(shù)GROWTH擬合長期趨勢 例 12.3 擬合指數(shù)曲線，并預測20082011年產(chǎn)量。 使用LOGEST函數(shù) 計算回歸統(tǒng)計量 選擇“增長曲線”工作表。12.3 12.3 回歸分析測定長期趨勢回歸分析測定長期趨勢 選擇E2：F6區(qū)域，單擊“插入函數(shù)”快捷鍵，彈出“插入函數(shù)

12、”對話框。 在“選擇類別”中選擇“統(tǒng)計”，在“選擇函數(shù)”中選擇“LOGEST”函數(shù)，打開LOGEST對話框。 在Known_ys、 Known_xs和Stats后分別輸入C2：C16、B2：B16和1。按住Ctrl+Shift組合鍵，再按回車鍵或“確定”按鈕，得計算結果如圖中E2：F6單元格所示。12.3 12.3 回歸分析測定長期趨勢回歸分析測定長期趨勢 使用增長函數(shù)GROWTH計算預測值 選擇D2：D20單元格，在工具欄單擊“插入函數(shù)”快捷鍵，彈出“插入函數(shù)”對話框。 在“選擇類別”中選擇“統(tǒng)計”，在“選擇函數(shù)”中選擇“GROWTH”函數(shù)，打開GROWTH對話框。 在Known_ys、 K

13、nown_xs、New_xs和Const后分別輸入C2：C16、B2：B16、B2：B20和1。按住Ctrl+Shift組合鍵，同時按回車鍵或“確定”按鈕，計算結果如圖所示。 或者在選擇D2：D20單元格的情況下，輸入數(shù)組公式“=GROWTH(C2:C16,B2:B16, B2:B20,1)”，同時按住Ctrl+Shift組合鍵，同時按回車鍵。結果與上述相同。12.3 12.3 回歸分析測定長期趨勢回歸分析測定長期趨勢 繪制實際值與預測值的折線圖： 在工具欄中單擊 “圖表向?qū)А笨旖輬D標打開“圖表向?qū)А睂υ捒颉?在步驟1中選擇“圖表類型”列表中的“折線圖”，在“子圖表類型”下選擇“數(shù)據(jù)點折線圖”

14、，單擊“下一步”按鈕。 確認數(shù)據(jù)區(qū)域為：sheet增長曲線！$A$2:$A$16，sheet增長曲線！$C$2:$D$16，單擊“下一步”按鈕。 在“標題”欄下輸入“某地區(qū)彩色電視機產(chǎn)量發(fā)展趨勢”，單擊“完成”按鈕，得到的結果如圖所示。12.3 12.3 回歸分析測定長期趨勢回歸分析測定長期趨勢v指數(shù)平滑法的基本內(nèi)容與要求 通過對歷史時間數(shù)列進行逐層平滑計算，從而消除隨機因素的影響，識別經(jīng)濟現(xiàn)象基本變化趨勢，并以此預測未來。 遞推公式 平滑常數(shù)的意義 一般說來，0.2 到 0.3 之間的數(shù)值可作為合理的平滑常數(shù)。這些數(shù)值表明本次預測需要將前期預測值的誤差調(diào)整 20% 到 30%。 初始預測值的

15、確定問題 不指定初始預測值，計算機把第一個實際值作為初始預測值進行預測。 指定初始預測值，即令它等于前幾期實際值的平均數(shù)。 指定初始預測值對用Excel進行計算的操作過程沒有影響，只對預測結果產(chǎn)生影響。 *11tttYYY12.4 12.4 指數(shù)平滑法測定長期趨勢指數(shù)平滑法測定長期趨勢v案例研究：小汽車租賃預測 例12.4 計算預測值 選擇“指數(shù)平滑”工作表。 在單元格C2中輸入公式“=AVERAGE(B2：B6)”。 在單元格C3中輸入公式“=0.3*B2+0.7*C2”。 將單元格C3中的公式復制到區(qū)域C4:C12中。結果如圖所示。12.4 12.4 指數(shù)平滑法測定長期趨勢指數(shù)平滑法測定長

16、期趨勢 繪制實際值與擬合值的折線圖 單擊工具欄中的“圖表向?qū)А笨旖輬D標，打開“圖表向?qū)А睂υ捒颉?在步驟1中選擇“圖表類型”下的“折線圖”，在“子圖表類型”下選擇“數(shù)據(jù)點折線圖” ，單擊“下一步”按鈕。 輸入數(shù)據(jù)區(qū)域：sheet指數(shù)平滑！$B$1:$C$12，單擊“下一步”按鈕。 在“標題”欄下輸入“指數(shù)平滑”，單擊“完成”按鈕，如圖所示。12.4 12.4 指數(shù)平滑法測定長期趨勢指數(shù)平滑法測定長期趨勢v 指數(shù)平滑分析工具預測 指數(shù)平滑分析工具的內(nèi)容 指數(shù)平滑分析工具的使用 選擇“指數(shù)平滑”工作表。 把區(qū)域A2:B12內(nèi)容下移一行，在空白的單元格B2中輸入公式“=AVERAGE(B3:B7)。

17、 打開“工具”菜單中的“數(shù)據(jù)分析”選項，彈出“數(shù)據(jù)分析”對話框。在“分析工具”列表中選擇“指數(shù)平滑”選項，單擊“確定”按鈕，打開“指數(shù)平滑對話框” 。12.4 12.4 指數(shù)平滑法測定長期趨勢指數(shù)平滑法測定長期趨勢 在輸入?yún)^(qū)域輸入B2：B12，阻尼系數(shù)設為0.7，輸出區(qū)域中輸入C2，選擇圖表輸出。單擊“確定”按鈕。 除單元格C2中顯示的公式為“=B2”外，單元格C3:C12中顯示的公式法均為平滑公式形式，這些是擬合值，要求的預測值，需把單元格C12中的公式復制到C13中。則得到的結果如圖所示。12.4 12.4 指數(shù)平滑法測定長期趨勢指數(shù)平滑法測定長期趨勢v最佳平滑常數(shù)的確定 公式判斷法 選擇

18、“指數(shù)平滑”工作表。 在單元格F1中輸入值0.3。 選取區(qū)域E1：F5，從“插入”菜單中選擇“名稱指定”選項，則彈出指定名稱對話框，選中“最左列”，單擊“確定”按鈕。12.4 12.4 指數(shù)平滑法測定長期趨勢指數(shù)平滑法測定長期趨勢 選取區(qū)域D6：D11，從“插入”菜單中選擇“名稱指定”選項，在彈出的指定名稱對話框，選中“首行”，單擊“確定”按鈕。 在單元格C2中輸入公式“=AVERAGE(B2：B6)”。在單元格C3中輸入公式“=平滑常數(shù)*B2+(1-平滑常數(shù))*C2”，并復制到單元格C4:C11中。 在單元格D7中輸入公式“=B7-C7”，并復制到單元格D8:D11中。 在單元格F3、F4、

19、F5中分別輸入公式：“=AVERAGE(預測誤差)”、“=STDEV(預測誤差)”、和“=SQRT(平均誤差2+標準誤差2)”。結果如圖所示。12.4 12.4 指數(shù)平滑法測定長期趨勢指數(shù)平滑法測定長期趨勢 在單元格F1中試用不同的值，則預測值、預測誤差值、平均誤差、標準誤差和RMSPE的值以及圖中曲線也將相應發(fā)生變化，比較RMSPE的大小，選取RMSPE最小時的值作為最優(yōu)平滑常數(shù)進行預測。本例中平滑常數(shù)為0.85時RMSPE最小。此時得到的修飾后的指數(shù)平滑圖如圖所示。指 數(shù) 平 滑 曲 線51 01 52 02 51234567891 01 1租 賃 數(shù)擬 合 值 12.4 12.4 指數(shù)平

20、滑法測定長期趨勢指數(shù)平滑法測定長期趨勢 繼“指數(shù)平滑”工作表，在“工具”菜單中選擇“單變量求解”選項，出現(xiàn)單變量求解對話框。 在目標單元格、目標值、可變單元格中分別輸入F5、0和F1，單擊“確定”按鈕。將出現(xiàn)單變量求解狀態(tài)對話框，可以看到計算機的計算篩選過程。當計算機篩選出最終結果后，將停止計算。 工作表F列也在發(fā)生變化，如圖所示。 單擊“確定”按鈕，計算機自動尋找的最佳平滑常數(shù)結果呈現(xiàn)在工作表上。12.4 12.4 指數(shù)平滑法測定長期趨勢指數(shù)平滑法測定長期趨勢12.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 v季節(jié)變動測定的基本內(nèi)容 簡單平均法 長期趨勢剔除法v 利用簡單平均法測

21、定季節(jié)變動及其應用 例12.5 計算季節(jié)指數(shù)。 選擇“簡單平均”工作表。 在單元格H3中輸入公式“AVERAGE(B3：G3)”，計算季平均數(shù)。 將單元格H3公式復制到H4:H6。 在單元格B7中輸入公式“AVERAGE(B3：G6)”，計算總平均數(shù)。 在單元格I3中輸入公式“H3$B$7”，并把它復制到I4：I6，計算各個季節(jié)指數(shù)，結果如圖所示。12.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 v利用長期趨勢剔除法測定季節(jié)變動及其應用 選擇“長期趨勢剔除”工作表。 在單元格E4中輸入公式“AVERAGE(D2：D5)”。 在單元格F4中輸入公式“AVERAGE(E4：E5)”。

22、把單元格E4：F4中的公式復制到E5：F19，調(diào)整其小數(shù)部分使顯示1位小數(shù)，結果如圖所示。12.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 在單元格G4中輸入公式“D4F4”，并把它復制到G5：G19。 在單元格H2中輸入公式“AVERAGE(H6，H10，H14，H18)”, 并把它復制到單元格H3中。 在單元格H4中輸入公式“AVERAGE(H4，H8，H12，H16)”， 并把它復制到單元格H5中。 選取單元格H6，點擊自動求和工具（）兩次。 在單元格I2輸入公式“H2*4$H$6”， 把它復制到單元格I3：I5。選取單元格I6，點擊自動求和工具（）兩次。上述操作的結果如上圖

23、所示。12.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 v 制作雷達圖 單擊工具欄中的“圖表向?qū)А笨旖輬D標，打開“圖表向?qū)А睂υ捒颉?在步驟1中，于“圖表類型”列表下選擇“雷達圖”，在“子圖表類型”下選擇“數(shù)據(jù)點雷達圖”，單擊“下一步”按鈕。 輸入數(shù)據(jù)區(qū)域為：sheet長期趨勢剔除！$I$2:$I$5，單擊“下一步”按鈕。 在“標題”頁面下輸入“季節(jié)指數(shù)雷達圖”，選擇“圖例”頁面，取消顯示圖例。單擊“完成”按鈕。經(jīng)修飾后得到的雷達圖，如圖所示： 季節(jié)指數(shù)雷達圖0.800.901.001.10123412.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 根據(jù)公式剔除季節(jié)變動的

24、影響，利用回歸模型結合季節(jié)指數(shù)進行預測。 在區(qū)域J1：L1中分別輸入標志“季節(jié)指數(shù)S序列”、“TCI*”和“預測值”。 選中單元格I2：I5，點擊主菜單中“復制”圖標。 選中單元格J2：J25，點擊“編輯”菜單，選擇“選擇性粘貼”選項，在出現(xiàn)的選擇性粘貼對話框中選擇“數(shù)值”，單擊“確定”按鈕。 在單元格K2中輸入公式“D2J2”，并把它復制到K2：K21，則區(qū)域K2：K21中的值即為季節(jié)調(diào)整后的序列TCI*。 選中單元格K2：K21，點擊“編輯”菜單，選擇“選擇性粘貼”選項，在出現(xiàn)的選擇性粘貼對話框中選擇“數(shù)值”，單擊“確定”按鈕。把K2：K21中的公式形式變?yōu)閿?shù)值形式。 選中單元格K2：K2

25、1，單擊單元格K21右下角的填充句柄并向下拖動到單元格K25。當Excel用于這種方式自動填充時，區(qū)域K2:K21中的數(shù)列自動用線性趨勢預測的數(shù)據(jù)對K22:K25進行擴展預測。這種方法相當于用TREND函數(shù)作簡單線性回歸，對序列21到24進行預測。 把回歸預測的TCI*分量和季節(jié)分量S組合得到預測值Y*，即：Y*=TCI* S。12.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 在單元格L22中輸入公式“I22*K22”，并把它復制到L23：L25。 結果如圖所示。12.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 繪制實際銷售額和預測值圖表察看預測情況。 選取單元格C1：

26、D25，按住Ctrl鍵并選取單元格KI：K21和LI：L25，單擊“圖表向?qū)А笨旖輬D標，打開“圖表向?qū)А睂υ捒颉?在步驟1中，于“圖表類型”列表下選擇“折線圖”，在“子圖表類型”下選擇“數(shù)據(jù)點折線圖”，單擊“下一步”按鈕。 輸入數(shù)據(jù)區(qū)域為：sheet長期趨勢剔除！$ C1$2:$D$25，sheet長期趨勢剔除！$ K$1:$K$21，sheet長期趨勢剔除！$ L$1:$L$25，單擊“下一步”按鈕。 在“標題”頁面下輸入“實際值、季節(jié)調(diào)整TCI及預測值比較圖”。單擊“ 完成”按鈕，結果如圖所示。實際值、季節(jié)調(diào)整TCI及預測值比較圖200250300350400450119933119943

27、119953119963119973119983銷售額YTCI*預測值12.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 v 利用啞變量進行季節(jié)調(diào)整與預測 選擇“季節(jié)變動”工作表。 從“工具”菜單中，選擇“數(shù)據(jù)分析”選項，出現(xiàn)“數(shù)據(jù)分析”工具對話框。 選中“回歸”選項，單擊“確定”按鈕，出現(xiàn)“回歸”對話框。 在“Y值輸入?yún)^(qū)域”、“X值輸入?yún)^(qū)域”、“輸出區(qū)域”中分別輸入D1:D21，E1:E21和K1；選擇標志、置信度、殘差、殘差圖、標準殘差和線性擬合圖。單擊“確定”按鈕。12.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 計算機將在工作表中輸出回歸分析結果。下圖為編輯過的一

28、部分結果： 回歸分析工具產(chǎn)生的時序圖中顯示了實際值和擬合值，修飾后的圖表如圖所示。實 際 值 與 預 測 值 比 較 圖200250300350400450119933119943119953119963119973119983時 間銷售額Y預 測 值銷 售 額季 節(jié) 變 動12.5 12.5 季節(jié)變動的測定與分析季節(jié)變動的測定與分析 v 使用TREND函數(shù)計算多個季度的擬合值和預測值。 把區(qū)域A18：C21的內(nèi)容復制到單元格A22：C25，在單元格A22中輸入1988。 選取區(qū)域E20：E21并把填充句柄向下拖到單元格E25。 把區(qū)域F18：I21的內(nèi)容復制到單元格F22：I25。 在單元格J1中輸入標記“預測值”。 選取區(qū)域J2：J25，按數(shù)組方式輸入公式 “=T