Excel時間序列預(yù)測操作

1、時間序列分析預(yù)測EXCEL操作一、長期趨勢（T）的測定預(yù)測方法線性趨勢一：用回歸法非線性趨勢中的指數(shù)曲線”：用指數(shù)函數(shù) LOGESTLOGEST、增長函數(shù) GROWTHGROWTH（針對指數(shù)曲線）t 多階曲線（多項式）：用回歸法（一）回歸模型法長期趨勢（線性或非線性）模型法：具體操作過程： 在 EXCEL 中點擊“工具”t“數(shù)據(jù)分析”t“回歸”t 分別在“丫值輸入?yún)^(qū)域”和“X 值輸入?yún)^(qū)域”輸人數(shù)據(jù)和列序號的單元格區(qū)域一選擇需要的輸出項目，如“線性擬合圖”c回歸分析工具的輸出解釋：計算結(jié)果共分為三個模塊：1 1）回歸統(tǒng)計表：MultipleR（復(fù)相關(guān)系數(shù) R）:R2 的平方根，又稱為相關(guān)系數(shù)，它

2、用來衡量變量 xy 之間相關(guān)程度的大小。RSquare（復(fù)測定系數(shù) R2）：用來說明用自變量解釋因變量變差的程度，以測量同因變量 y 的擬合效果。AdjustedRSquare（調(diào)整復(fù)測定系數(shù) R2）：僅用于多元回歸才有意義，它用于衡量加入獨立變量后模型的擬合程度。當有新的獨立變量加入后，即使這一變量同因變量之間不相關(guān)，未經(jīng)修正的 R2 也要增大，修正的 R2 僅用于比較含有同一個因變量的各種模型。標準誤差：又稱為標準回歸誤差或叫估計標準誤差,它用來衡量擬合程度的大小，也用于計算與回歸有關(guān)的其他統(tǒng)計量，此值越小，說明擬合程度越好。表114Excel輸出的回歸分析結(jié)果SUMAKTaUTFUT.卜

3、;A.r1,-.,nii-18-1-TT1” 一.rI二主:R,三5：MultipleK;KSquare6jAdjustedRS呼ar聽標慳誤差觀測值0.843571:kdrusr;j0.T11813r、口*.JJ-Y0.699074-r-J1979948-v_T.-_ir255I,.IQ己aIVI1.10_，II12132；I6:17方差分析1222,48598-222.485979;56.753844;1.23i90.164421S3.920192;：FTTIFFF.、-mJ呼Ir.FffTf4fr、廣.-24；312.65。吐:InterceptXVari1jCoeEfiuient 三標

4、準誤差tStat：P-valueiLo-0.829521;0.723043!-L1472630.263068frHrjju.-gan.HI,F.-.a-、，JrA*iv-i-i-J-.-t-f-.-i-ffrj-r-m-PF!-F rr-r.- rr1-r,J-.j.一卜-石0.037895：0,005030；7.533515O.OOOOOD：回歸統(tǒng)計1fl1a.3-Ju,選K2 2）方差分析表：方差分析表的主要作用是通過 F 檢驗來判斷回歸模型的回歸效果。3 3）回歸參數(shù)：回歸參數(shù)表是表中最后一個部分：Intercept：截距 a第二、三行：a（截距）和 b（斜率）的各項指標。第二列：回歸系

5、數(shù) a（截距）和 b（斜率）的值。第三列：回歸系數(shù)的標準誤差第四列：根據(jù)原假設(shè) Ho：a=b=0 計算的樣本統(tǒng)計量 t 的值。第五列：各個回歸系數(shù)的 p 值（雙側(cè)）第六列：a 和 b95%的置信區(qū)間的上下限。（二）使用指數(shù)函數(shù) LOGESTLOGEST 和增長函數(shù) GROWTHGROWTH 進行非線性預(yù)測在 Excel 中，有一個專用于指數(shù)曲線回歸分析的 LOGEST 函數(shù)，其線性化的全部計算過程都是自動完成的。如果因變量隨自變量的增加而相應(yīng)增加，且增加的幅度逐漸加大；或者因變量隨自變量的增加而相應(yīng)減少，且減少的幅度逐漸縮小，就可以斷定其為指數(shù)曲線類型。具體操作過程：1 1 . .使用 LOG

6、ESTLOGEST 函數(shù)計算回歸統(tǒng)計量打開第 3 章時間數(shù)列分析與預(yù)測.xls 工作簿，選擇增長曲線”工作表如下圖所示。選擇 E2:F6 區(qū)域，單擊工具欄中的粘貼函數(shù)”快捷鍵，彈出粘貼函數(shù)”對話框，在函數(shù)分類”中選擇統(tǒng)計”，在函數(shù)名”中選擇“LOGEST 函數(shù)，則打開 LOGEST 對話框，如下圖 11.20 所示。在 Known_ys、Known_Xs 和 Stats 后分別輸入 C2:C16、B2:B16 和 1。按住 Ctrl+Shift 組合鍵，再按回車鍵或確定”按鈕，得到計算結(jié)果如下圖中 E2:F6 單元格所示。函數(shù)輸出結(jié)果參數(shù)b的估計值參數(shù)4的估計值參數(shù)b的標準誤差參數(shù)a的標準誤差

7、判定系數(shù)無丫值估計標準誤差產(chǎn)統(tǒng)計值自由度回歸平方和殘差平方和ABCD1年份時間電視產(chǎn)量 （萬臺）預(yù)測值2r1981：1539.4：二590.119822592.0689.719833 _684.01_806.1198441003.8942.1198551367.7J1101.119866二1459.41287.0198771634.41504.2(1988：8 71805.111758.010198992273.52054.711199010 _2448.12401.4121991112631.42806.7131992123467.83280.3141993133433.013833.915

8、1994144383.04480.916199515 74798.115237.0171996166120.8181997177153,7191998188361.0201999199772.0EFG回歸統(tǒng)計92040.0068550.0623230.975497_0.1147-517,5499136.808890OOO800070006000300040003000200010000T-電視產(chǎn)量（：某地區(qū)彩色也l?ei1S831385198713：根據(jù)單元格 E2,F2 中的計算結(jié)果可以寫出如下估計方程：?=504.9204乂1.16875

9、7x可以根據(jù)上面的指數(shù)曲線方程計算預(yù)測值：在 D2 單元格中輸入公式“=$F$2*$E$2AB2,將 D2 單元格中的公式復(fù)制到 D3:D20 元格，各年的預(yù)測值便一目了然了。但使用增長函數(shù)可以直接得到預(yù)測結(jié)果。2 2 . .使用增長函數(shù) GROWTHGROWTH 計算預(yù)測值選擇 D2:D20 單元格，在工具欄單擊粘貼函數(shù)”快捷鍵，彈出粘貼函數(shù)”對話框，在函數(shù)分類”中選擇統(tǒng)計”，在函數(shù)名”中選擇“GROWTH 函數(shù)，打開 GROWTH 對話框，如下圖所示。GROWTH返回一條指數(shù)回歸擬合曲線的一犯姒坐標值(y值)口Hew_xs一組新x值，通過GROWTH函數(shù)返回各自先且計算結(jié)果=590.1匚在

10、 Known_ys、Known_Xs、New_Xs 和 Const 后分別輸入 C2:C16、B2:B16、B2:B20 和 1。按住 Ctrl+Shift組合鍵，同時按回車鍵或確定”按鈕，計算結(jié)果如下圖所示。3 3 . .繪制實際值與預(yù)測值的折線圖季節(jié)變動的測定與預(yù)測分析季節(jié)變動的趨勢一循環(huán)剔除法長期趨勢剔除法是在移動平均法的基礎(chǔ)上，以乘法模型(Y=TXSXCXI)為理論基礎(chǔ)的測定季節(jié)變動的方法，它能避免長期趨勢與周期波動的影響，凈化季節(jié)變動的規(guī)律性，從而實現(xiàn)較為準確的預(yù)測。ExcelExcel 操作過程：打開第 3 章時間數(shù)列分析與預(yù)測.XLS”工作簿，選擇長期趨勢剔除”工作表，如下圖所示

11、。注第11章時間數(shù)列分衍與預(yù)惻ABCDE年鈔孝度|年學(xué)第岱顏丫一次巧動平均TC1中4：19931119932540-2229274|3T3T297.8|4T4I330.31994111994291.1223277633321.2q卜T354.31995111995T304.62|2348.4313_T350.84U1374.21996111996319.52fz_T361533-3369744J4ZZL395.219971119973326FTk長期趨起敏除灑曲波動廠JJLI在單元格 E1 中，點擊工具”菜單中選擇數(shù)據(jù)分析”選項，打開移動平均”對話框，對 D 列“銷售額”進行 4項移動平均。在

12、單元格 F3 中，點擊工具”菜單中選擇數(shù)據(jù)分析”選項，打開移動平均”對話框，對 E 列進行 2 項移動平均。把單元格 E4:F4 中的公式復(fù)制到 E19:F19,調(diào)整其小數(shù)部分使顯示 1 位小數(shù)，結(jié)果如下圖所示。01234561111111銷傷30Y四項移動平均TCI中心化移動平均TCSI1011234567890111111112297.8293.6298；309984330.1n302.9J30T.3rroT48291.1311.T314.60.925232T.631r6320,617OZ2O321.2323,6325.20.9876354.3326.9329.51.0T5Z304.633

13、2.1335.8口.9070348.4339.5J342,01.0187350.8344.5346.4J1.0128374.2P348,21349.9L0696319.5351,5353.8口.9030361.5j356.2358.81.0076369.43B1.4363.01.0175395.2364.7367,4I1.0756332.6370.2372.00.8941383.53T3.S373.811.0260254.0292.4383.821407.4在單元格 G4 中輸入公式=D4/F4”，并把它復(fù)制到 G5:G19。在單元格 H2 中輸入公式=AVERAGE(H6,H10,H14,H

14、1J8),并把它復(fù)制到單元格 H3 中。分別計算第一、第二季度的季節(jié)比率。在單元格 H4 中輸入公式=AVERAGE(H4,H8,H12,H16)”，并把它復(fù)制到單元格 H5 中。分別計算第三、第四季度的季節(jié)比率。選取單元格H6,點擊自動求和工具（Z）兩次。圖 1L33“長期趨勢剔除”工作表選取單元格12,輸入公式=H2*4/$H$6，?,并把它復(fù)制到格16,點擊自動求和工具（兩次。上述操作的結(jié)果如匾 （懵第StYITC1ITC1中心在移助平峋丁二E膝*學(xué)磷.哥龔Hl鐘龍?zhí)?54；0函舅函奔w_-A.:MTWWJm 1rui ，m= “ii，zt一“一II.E.I_.asm卜rH*11n,iT

15、Miiunv.-wvwu110196Ed司297.893.6更甜言0.9904:10041330.33029307.31.07481.0T3B幽麟爆Z91.i327.B,311T31T；6314.&320.6Q92524003B1.0220獺321.23543323.B326.9325.2FITJ.T.-.Wi1MUIMMSlAah、1AWVM32950.98761.0752符建豳304.61348.4332.t339.5:335.8一丫-irwi-i.v-fi-otferjA-.vurwa-,，.-rii342.0口.GOTO113aM4i.J.JB-.dl.m.10187奢）丁二濾

16、r30_,.Bj374.2344.5j346.4-,1111,!,VIITI,Ivrrmnn.rJ.7rr.v.V.TJA:.v.v.(ixA*-1.348.2349.91.01281.0696MV.T,inn11-1s1rs.n.-v.nwn一;工；或1:怨319.5351.5353r80.90303sls356.2350,BiTixm1L.J-r-工：；二:369.4361.4|sra.0LQ1TS逛鰥1卷產(chǎn);薄3952332.6364.7370.2367.4?.0ct、*wnw0a 一i372.01,07560.B94Lnt.?HTSMFEUmuukaiuuunjjjvfvnt4333.

17、5lvrunriBnAnnnnnmmBrrwimu3B3.6rrr-1(一w-wvmyrwr407.43738J!MJAUU“*IIi，rii.b.A:dh._atm.一,J3T3.Br-rrrrnmr/rnaKVAY-WTT-3T.I,.1.v-r-v-T-KWJTVIMIi.02abri.1drrwwufmiBMhjrwu方.TiM1圖 1134 長期趨勢剔除法計算表單擊“下一步靜按鈕，進入圖表向?qū)Р襟E2對話據(jù)。儂二二二i11993-1994s*vAJaw3353一4AzzzzZZ憎一:嚴19054!4Luk.199644Il it ， 一， re，rr， ，s_*“， 11laoe.30

18、4.B343一4一,1.J.H1.,.Mm,i一 i,.、jnJ111!PPPIpPJ-VJrrTB199TL11.34所示季節(jié)指數(shù)的意義可以通過制作雷達來表現(xiàn)工單擊工具欄中的“圖表向?qū)А笨旖輬D標鬻,打開表向?qū)Р襟E1表類型”列表下選擇“雷達“子表類型，下邇管率!皚:；門WF：jf 稈1口？門/321.2-8a-i3543.437%2111199511111irf-.11trjr?.rmwivri:w2233輸入“數(shù)據(jù)區(qū)域”為：sheet長期趨勢剔除！$1$2:$1$5,單擊“一表向?qū)Р襟E3對話框。季節(jié)指數(shù)雷達圖圖 11.35 季節(jié)指數(shù)雷達圖從雷達圖上可以清楚地看到時間數(shù)列中季節(jié)變動情況：銷售額二第四季度則達到旺季立得到各季的季節(jié)指數(shù)后，就可以根據(jù)公式剔除季節(jié)變動因素的二合季節(jié)指數(shù)進行預(yù)測口此過程的操作步驟如下：在區(qū)域JLL1中分別輸入標志“季節(jié)指數(shù)S序列”、“TCI”彳選中單元格12:15,點擊主菜單中“復(fù)制”圖標。選中單元格J2:J25,點擊“編輯”菜單，選擇“選擇性粘貼粘貼對話框中選擇“值”，單擊“確定”按鈕。即為季節(jié)調(diào)整后的序列TCI選中單元格K2:K2L點擊“編輯”菜單， 選擇“選擇性粘貼粘貼對話框中選擇“值”，單擊“