Excel時(shí)間序列預(yù)測(cè)操作

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

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

3、;A.r1,-.,nii-18-1-TT1” 一.rI二主:R,三5：MultipleK;KSquare6jAdjustedRS呼ar聽標(biāo)慳誤差觀測(cè)值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 三標(biāo)

4、準(zhǔn)誤差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)計(jì)1fl1a.3-Ju,選K2 2）方差分析表：方差分析表的主要作用是通過(guò) F 檢驗(yàn)來(lái)判斷回歸模型的回歸效果。3 3）回歸參數(shù)：回歸參數(shù)表是表中最后一個(gè)部分：Intercept：截距 a第二、三行：a（截距）和 b（斜率）的各項(xiàng)指標(biāo)。第二列：回歸系

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

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

7、判定系數(shù)無(wú)丫值估計(jì)標(biāo)準(zhǔn)誤差產(chǎn)統(tǒng)計(jì)值自由度回歸平方和殘差平方和ABCD1年份時(shí)間電視產(chǎn)量 （萬(wàn)臺(tái)）預(yù)測(cè)值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)計(jì)92040.0068550.0623230.975497_0.1147-517,5499136.808890OOO800070006000300040003000200010000T-電視產(chǎn)量（：某地區(qū)彩色也l?ei1S831385198713：根據(jù)單元格 E2,F2 中的計(jì)算結(jié)果可以寫出如下估計(jì)方程：?=504.9204乂1.16875

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

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

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

12、單元格 F3 中，點(diǎn)擊工具”菜單中選擇數(shù)據(jù)分析”選項(xiàng)，打開移動(dòng)平均”對(duì)話框，對(duì) E 列進(jìn)行 2 項(xiàng)移動(dòng)平均。把單元格 E4:F4 中的公式復(fù)制到 E19:F19,調(diào)整其小數(shù)部分使顯示 1 位小數(shù)，結(jié)果如下圖所示。01234561111111銷傷30Y四項(xiàng)移動(dòng)平均TCI中心化移動(dòng)平均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 中。分別計(jì)算第一、第二季度的季節(jié)比率。在單元格 H4 中輸入公式=AVERAGE(H4,H8,H12,H16)”，并把它復(fù)制到單元格 H5 中。分別計(jì)算第三、第四季度的季節(jié)比率。選取單元格H6,點(diǎn)擊自動(dòng)求和工具（Z）兩次。圖 1L33“長(zhǎng)期趨勢(shì)剔除”工作表選取單元格12,輸入公式=H2*4/$H$6，?,并把它復(fù)制到格16,點(diǎn)擊自動(dòng)求和工具（兩次。上述操作的結(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 長(zhǎng)期趨勢(shì)剔除法計(jì)算表單擊“下一步靜按鈕，進(jìn)入圖表向?qū)Р襟E2對(duì)話據(jù)。儂二二二i11993-1994s*vAJaw3353一4AzzzzZZ憎一:嚴(yán)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ù)的意義可以通過(guò)制作雷達(dá)來(lái)表現(xiàn)工單擊工具欄中的“圖表向?qū)А笨旖輬D標(biāo)鬻,打開表向?qū)Р襟E1表類型”列表下選擇“雷達(dá)“子表類型，下邇管率!皚:；門WF：jf 稈1口？門/321.2-8a-i3543.437%2111199511111irf-.11trjr?.rmwivri:w2233輸入“數(shù)據(jù)區(qū)域”為：sheet長(zhǎng)期趨勢(shì)剔除！$1$2:$1$5,單擊“一表向?qū)Р襟E3對(duì)話框。季節(jié)指數(shù)雷達(dá)圖圖 11.35 季節(jié)指數(shù)雷達(dá)圖從雷達(dá)圖上可以清楚地看到時(shí)間數(shù)列中季節(jié)變動(dòng)情況：銷售額二第四季度則達(dá)到旺季立得到各季的季節(jié)指數(shù)后，就可以根據(jù)公式剔除季節(jié)變動(dòng)因素的二合季節(jié)指數(shù)進(jìn)行預(yù)測(cè)口此過(guò)程的操作步驟如下：在區(qū)域JLL1中分別輸入標(biāo)志“季節(jié)指數(shù)S序列”、“TCI”彳選中單元格12:15,點(diǎn)擊主菜單中“復(fù)制”圖標(biāo)。選中單元格J2:J25,點(diǎn)擊“編輯”菜單，選擇“選擇性粘貼粘貼對(duì)話框中選擇“值”，單擊“確定”按鈕。即為季節(jié)調(diào)整后的序列TCI選中單元格K2:K2L點(diǎn)擊“編輯”菜單， 選擇“選擇性粘貼粘貼對(duì)話框中選擇“值”，單擊“