1、7時間序列分析預(yù)測EXCEL操作一、長期趨勢（T）的測定預(yù)測方法 線性趨勢T：:用回歸法非線性趨勢 中的“指數(shù)曲線”：用指數(shù)函數(shù)LOGEST、增長函數(shù) GROWTH （針對指數(shù)曲線）多階曲線（多項式）：用回歸法（一）回歸模型法 長期趨勢（線性或非線性）模型法：具體操作過程：在EXCEL中點擊“工具”> “數(shù)據(jù)分析”> “回歸”，分別在“ Y值輸入?yún)^(qū)域”和“ X值輸入?yún)^(qū)域”輸人數(shù)據(jù)和列序號的單元格區(qū)域一選擇需要的輸出項目如“線性擬合圖”。回歸分析工具的輸出解釋：表 114Excel輸出的回歸分析結(jié)果mi：i卜丄 B ” J1護(hù) 5'j AdjustI - . I:Q匕I &g

2、t; VI . . . 10iu.-r '.亠Multiple Ks -j L - - l-.'i-s - - 1. -1 、 .f *;K Squareed R SquareT. » "y r> 'fa J fs. ii- -c".標(biāo)椎誤差觀測直SUMAKT OUTPUT0.843571: d a.r-id "SF q rij0.7116L30.6990741 979948:- . B.-_. -l -S. - .25!回歸T n. .H . -« L_ J "fc" 殘差”w.rfcr l總

3、計F1 222,48598222.485979: 56.753844 123i 90. 164421S 3.920192;：>. J '、"- I r.F II f r,F i齊r叫 * % -l -牛 i- 24; 312. 6504：I!17#7CxEj巨uients 標(biāo)催誤差 t Stat : P-valuei II !I'-Lo!-D. 829521S 0.723043! -L 14T263 ；0. 263068 6 037895CL85CK3Cd T. 533515” 000000?計算結(jié)果共分為三個模塊：1）回歸統(tǒng)計表：Multiple R （復(fù)相關(guān)

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

5、析表的主要作用是通過F檢驗來判斷回歸模型的回歸效果。3）回歸參數(shù)：回歸參數(shù)表是表中最后一個部分：Intercept :截距 a第二、三行：a （截距）和b （斜率）的各項指標(biāo)。第二列：回歸系數(shù) a （截距）和b （斜率）的值。第三列：回歸系數(shù)的標(biāo)準(zhǔn)誤差第四列：根據(jù)原假設(shè) Ho: a=b=0計算的樣本統(tǒng)計量t的值。第五列：各個回歸系數(shù)的 p值（雙側(cè)）第六列：a和b 95%的置信區(qū)間的上下限。（二）使用指數(shù)函數(shù) LOGEST和增長函數(shù)GROWTH進(jìn)行非線性預(yù)測在Excel中，有一個專用于指數(shù)曲線回歸分析的LOGEST函數(shù)，其線性化的全部計算過程都是自動完成的。如果因變量隨自變量的增加而相應(yīng)增加，且

6、增加的幅度逐漸加大；或者因變量隨自變量的增加而相應(yīng)減少，且減少的幅度逐漸縮小，就可以斷定其為指數(shù)曲線類型。具體操作過程：1 .使用LOGEST函數(shù)計算回歸統(tǒng)計量打開第3章時間數(shù)列分析與預(yù)測.xls工作簿，選擇 增長曲線”工作表如下圖所示。第章時司數(shù)歹LI分析與予頁 1剛ABCD1日寸間電幀產(chǎn)車方臺）予頁測値厶198 11539. 4_J19822592. O4198336 84-05198441003.8_ 6198551367.77198661459. 481 98771 6 34. 49198881805. 110198992273.51119901 0244 8. 112199 11 1

7、2631.41319921 23467.81419931 33433. O選擇E2:F6區(qū)域，單擊工具欄中的粘貼函數(shù)”快捷鍵，彈出 粘貼函數(shù)”對話框，在 函數(shù)分類”中選擇統(tǒng)計”在 函數(shù)名”中選擇“ LOGEST函數(shù)，則打開LOGEST對話框，如下圖11.20所示。3747在Known_y s、 Known_X s和Stats后分別輸入 C2:C16、B2:B16和1。按住Ctrl+Shift組合鍵，再按 回車鍵或 確定"按鈕，得到計算結(jié)果如下圖中E2:F6單元格所示。函數(shù)輸出結(jié)果參數(shù)b的估計值參數(shù)b的標(biāo)準(zhǔn)誤差判定系數(shù)圧F統(tǒng)計值回歸平方和ABCD1年份時間電視產(chǎn)量（萬臺）預(yù)測值2198

8、11539.4 1二 590. 119822592.0689.719833_684.0 1_ 806. 1198441003.8942. 1198551367. 7 J1101. 1198662 1459.41287.0198771634.41504.219888-1805. 111758. 010198992273.52054.7111990102443. 1 12401.4121991112631.42806.7131992123467. 83280.3141993133433. 0 13833. 9151994144383.04480. 916199515-4798. 115237.01

9、71996166120.8181997177153. 719199818836E0201999199772. 0參數(shù)口的估計值參數(shù)住的標(biāo)準(zhǔn)誤差，值估計標(biāo)準(zhǔn)誤差自由度殘差平方和EFG回歸統(tǒng)計值1. 168757504.92040. 0068550. 0623230.9754970, 1147517.5499136.808890. 171028某地區(qū)彩色電*10000SOOO800070006000500040003000200010000T-電視產(chǎn)量（:l?ei 1S83 1985 1987 19： 根據(jù)單元格E2，F(xiàn)2中的計算結(jié)果可以寫出如下估計方程：二504.9204 1.168757 x

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

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

12、2 3 4 5 61111111了89111孝度涪害SSY坎移魴平均TC1中丄199311 1993254. 022292. 433297. 844330. 31994111994291_122327. 633321.244354. 31995r 11 1995304, 622348. 433350. 844374,21996111996319. 522361.533369. 444395. 2199711 1997332. &夕夕>PhT1長期趨勢剔除人周期浪動/1 在單元格E1中，點擊工具”菜單中選擇 數(shù)據(jù)分析”選項，打開移動平均”對話框，對D列“銷售額” 進(jìn)行4項移動平均。

13、 在單元格F3中，點擊 工具”菜單中選擇 數(shù)據(jù)分析”選項，打開 移動平均”對話框，對E列進(jìn)行2項 移動平均。 把單元格E4:F4中的公式復(fù)制到 E19:F19，調(diào)整其小數(shù)部分使顯示 1位小數(shù)，結(jié)果如下圖所示。879723456789011111111.匹歐移動平均TC1中心化碑動平均TCSI254. 0292 + 4297. 8293_ 6298. 30.9984330, 3302.9 J30T. 3IT0T4829K 1311. T314. 60.9252327.廠317. 6320. 617OZ2O321.2323, 6325. 20.98T6354. 3326. 9329. 51.0T5

14、Z304. 6332. 1335. 80,9070348, 4339. 5 J342, 01.0187350. 8344. 5346. 41.01283T4, 2P348, 2 |349. 91.0696319. 5351,5353. 80,9030361.5356. 2358. 81.0076369.3B1_ 4363. 01.0175395. 2364. 7367, 4I 1.0756332 + 6370. 2372. D0,8941383. 53T3. 8373 8二1.02603S3. 8407. 4 在單元格G4中輸入公式 =D4 / F4” ,并把它復(fù)制到 G5:G19。 在單元格

15、 H2中輸入公式 =AVERAGE(H6,H1O,H14,H18), 并把它復(fù)制到單元格 H3中。 分別計算第一、第二季度的季節(jié)比率。在單元格 H4中輸入公式 =AVERAGE(H4 , H8, H12 , H16)”， 并把它復(fù)制到單元格 H5中。 分別計算第三、第四季度的季節(jié)比率。107Ll I rrmfimnrvri mil,V.1明 *、!| Ci 3.吊；門加列日們f討hlissrawSMKgSSS1994J BL r V BEmm 比切世I19051996Z'Z去亠4!4 »Luk.111 1995ill ii i rf- . i i vjr »、rmw

16、ivri: w2：233lI"lll-J -MM11 . «!./>_». ,.4 !4 4!>._>1>1：. * > « |T | | r ,SS- ! !1 i iaoe321. 23 55" IMV JM »I354 3y 304. 6TMWWWil TA-. I IVA J I34B. 4lj .h i .-.mtmn350.83T4, 2 選取單元格HG 點擊自動求和工具(Z )兩次。117#7si比311. TI 1 L、317 B323, B293 ： 601750756 89410260

17、1.0220349.9361. 4364. 7383 5363.8407 4r3®5_,B r 莖丘31.B._53S15li ”=- wtitrrr*=?. m I- .Trr t.369.4 I J I "_r- II' Mlii r H 4 » .1 395 2 J 332. 6n, .,. .1. ,r!fl ! .v frr svw anHw335.8:0.GOTO'/*vw fvrw=nw'vanrvB w i t>n 一 ip * u. ai . j. ja a.n.».«.» 342. Q

18、nwTEirP11 mi亠"nil » u i i - u. ., X346.4PE073 I 1,0195 rniToo4r i 0748 ! 4nrF. rA.fv .(»11M n .0 92521 01BT 1 1 = '353. B” ffTL" ' T XT«V11SWWW箕3. 0 wwi ii wm-vmm367. 4 rf i =-irr?r.se nil" + u«-” rr.v. .tvr.-.<u- '370. 2372. 0a b "« V n&qu

19、ot;«" c a _ V«'." '*'" Wka.a aE 0T3B.t - -4. 0038Anm I HiC ITTTB'H ri JIAT 11 UI KtaMHMNMMt99T圖1L33"長期培勢剔除"工作表 選取單元格口，輸入公式«=H2*4 / $H$6,?, 并把它復(fù)制到格IG 點擊自動求和工具(E )兩次。上述操作的結(jié)果如圖1134所示。圖1134長期趨勢剔除法計算表季節(jié)指數(shù)的意義可以通過制作雷達(dá)圖來表現(xiàn)乂 單擊工具欄中的“圖表向?qū)?quot;快捷圖標(biāo)jO,打開圖表向?qū)Р襟E1 在“圖表類型"列表下選擇強(qiáng)雷達(dá)圖”,在"子圖表類型"下邇 單擊“下一步”按鈕，進(jìn)入圖表向?qū)Р襟E2對話框°輸入“數(shù)據(jù)區(qū)域"為：she氏長期趨勢剔除！ $1$2:$1$