時間數(shù)列分析與預(yù)測

1、第11章 時間數(shù)列分析與預(yù)測 11.1 時間數(shù)列的基本特征 11.2 移動平均分析與預(yù)測 11.3 回歸分析與預(yù)測 11.4 指數(shù)平滑法與預(yù)測 11.5 季節(jié)變動的測定與分析 11.6 周期波動的測定與分析 下一頁返回目錄第11章時間數(shù)列111.1 時間數(shù)列的基本特征 11.1.1 時間數(shù)列的特點 11.1.2 時間數(shù)列的構(gòu)成與分解 上一頁下一頁返回本章首頁第11章時間數(shù)列211.1.1 時間數(shù)列的特點 時間數(shù)列按時間先后順序排列時間數(shù)列是按一定方式搜集的一系列數(shù)據(jù) 時間數(shù)列中的觀察值具有差異 時間數(shù)列中的數(shù)據(jù)不許遺漏 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列311.1.2 時間數(shù)列的構(gòu)成與分

2、解 上一頁下一頁返回本節(jié)首頁通常把時間數(shù)列（Y）分解為以下四種變動：(1)長期趨勢變動（T）(2)季節(jié)變動（S） (3)周期波動（C） (4)不規(guī)則變動（I）乘法模型的一般形式為：Y=TSCI 式中 Y、T是總量指標(biāo), S、C、I為比率。加法模型的一般形式為：Y=T+S+C+I 式中Y、T、S、C、I都是總量指標(biāo)。 第11章時間數(shù)列411.2 移動平均分析與預(yù)測 11.2.1 移動平均法的概念及特點 11.2.2 趨勢圖直接預(yù)測法11.2.3 利用Excel創(chuàng)建公式預(yù)測 11.2.4 移動平均分析工具預(yù)測 上一頁下一頁返回本章首頁第11章時間數(shù)列511.2.1 移動平均法的概念及特點 移動平均

3、法是在算術(shù)平均法的基礎(chǔ)上發(fā)展起來的預(yù)測方法。它是利用過去若干期實際值的均值來預(yù)測現(xiàn)象的發(fā)展趨勢。簡單移動平均公式如下： 式中，N 為期數(shù)；At-j+1 ：t-j+1期的實際值； Mt+1 ：t+1期的預(yù)測值。njjttANM1111上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列611.2.2 趨勢圖直接預(yù)測法例 已知1993年第1季度到1997年第四季 度的某地區(qū)的季度零售額資料試對 1998年的零售額進行預(yù)測。操作過程如下： 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列7(1)產(chǎn)生“年季”變量打開“第11章 時間數(shù)列分析與預(yù)測.XLS”工作簿，選擇“移動平均”工作表如下圖所示。 在C列選定任一個單元格

4、，選擇“插入”菜單中的“列”選項，則原來C列的內(nèi)容被移到D列。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列8在C1單元格中輸入標(biāo)志“年季” ，在C2單元格中輸入公式 “=B2&CHAR(13)&A2” ，再把單元格C2中的公式復(fù)制到C3:C21。結(jié)果如下圖所示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列9(2)根據(jù)圖中資料繪制銷售額趨勢圖打開“插入”菜單中的“圖表”選項，Excel彈出“圖表向?qū)А睂υ捒蛉缦聢D所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列10在 “圖表類型”列表中選擇“折線圖”選項；在“子圖表類型”列表中選擇“數(shù)據(jù)點折線圖”。單擊“下一步”按鈕 ，進入向?qū)Р襟E2對話

5、框，如下圖所示 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列11在 “圖表數(shù)據(jù)源”對話框中，在數(shù)據(jù)區(qū)域輸入 “C1:D21”，并單擊“下一步”按鈕，如下圖所示 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列12在“圖表選項”中，選擇“標(biāo)題”頁面，在“圖表標(biāo)題”、“分類（X）軸”、“數(shù)值（Y）軸”中分別填入“某地區(qū)銷售額趨勢圖”、“季度”和“銷售額（萬元）”。選擇“網(wǎng)格線”頁面，把“數(shù)值（Y）軸”下的“主要網(wǎng)格線”設(shè)為空。選擇“圖例”頁面，取消圖例顯示。單擊“完成”按鈕，得到趨勢圖 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列13(3)在圖表中插入趨勢線進行預(yù)測單擊圖表以激活它，選取垂直軸，雙擊或單擊鼠

6、標(biāo)右鍵并從快捷菜單中選擇“坐標(biāo)軸格式”選項，Excel彈出對話框如下圖所示。打開“刻度”頁面，在“最小值”、“最大值”、“主要刻度單位”中分別輸入200、450和50；打開“字體”頁面，設(shè)置字號為“8”。單擊“確定”按鈕 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列14選取水平軸，雙擊或單擊鼠標(biāo)右鍵并從快捷菜單中選擇“坐標(biāo)軸格式”選項，Excel彈出對話框如下圖所示。打開“對齊”頁面，取消自動設(shè)置；打開“字體”頁面，設(shè)置字號為“8”。單擊“確定”按鈕。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列15選取圖中的折線，單擊鼠標(biāo)右鍵并從快捷菜單中選擇“添加趨勢線”選項，打開“添加趨勢線”對話框如下圖所示

7、。選擇“類型”頁面，在“趨勢預(yù)測/回歸分析類型”框中選擇“移動平均”，設(shè)置“周期”為4。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列16單擊“確定”按鈕。如下圖所示。 某地區(qū)銷售額趨勢圖20025030035040045011993311994311995311996311997311998上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列1711.2.3 利用Excel創(chuàng)建公式預(yù)測 例 已知1993年第1季度到1997年第四季 度的某地區(qū)的季度零售額資料，試 對1998年的零售額進行預(yù)測。操作步驟如下:上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列18打開“第11章 時間數(shù)列分析與預(yù)測”工作簿，選擇“移動平均”

8、工作表。在單元格E1中輸入“公式預(yù)測值”。在單元格E6中輸入公式“=AVERAGE(D2:D5)”，此處需要相對引用以便復(fù)制。把單元格E6中的公式復(fù)制到E7:E22各單元格中，結(jié)果如下圖所示 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列1911.2.4 移動平均分析工具預(yù)測 1. 移動平均分析工具的內(nèi)容 移動平均分析工具對話框如下圖所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列202. 利用移動平均分析工具進行預(yù)測打開“第11章 時間數(shù)列分析與預(yù)測.xls”工作簿，選擇“移動平均”工作表。在單元格F1、G1中分別輸入“分析工具預(yù)測值”和“預(yù)測標(biāo)準(zhǔn)誤差” 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列

9、21從“工具”菜單中選擇“數(shù)據(jù)分析”選項，在彈出的“數(shù)據(jù)分析”對話框中選中“移動平均”選項，并單擊“確定”按鈕，此時將出現(xiàn)“移動平均”對話框，如下圖所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列22在輸入?yún)^(qū)域中輸入D1:D21，選定標(biāo)志位于第一行，間隔設(shè)為4，在輸出區(qū)域中輸入F2，即輸出區(qū)域的左上角的絕對引用。選擇圖表輸出和標(biāo)準(zhǔn)誤差。單擊“確定”按鈕，所得結(jié)果如下圖所示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列233使用移動平均分析工具注意事項移動平均分析工具自動將公式放在工作表中。單元格G8中含有的公式為： SQRT(SUMXMY2(D5:D8, F5:F8)/4)請注意上表中用兩種方法所

10、得出的輸出結(jié)果的差異：用移動平均分析工具得到的移動平均值較直接輸入公式法輸出結(jié)果上移了一個單元格。這意味著用數(shù)據(jù)分析工具所計算的第一個值，即293.6，是對1983年第四季度的預(yù)測。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列24移動平均法的不足之處n計算一次移動平均值必須儲存多個實際值，當(dāng)預(yù)測項目很多時，就要占據(jù)相當(dāng)大的預(yù)測空間。n注重最近的幾個實際值，沒有利用t-n期以前各期的數(shù)據(jù)信息。n只對最近的一期進行預(yù)測，不能對更遠(yuǎn)的未來做出預(yù)測計劃。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列2511.3 回歸分析與預(yù)測 11.3.1 時間數(shù)列預(yù)測工作表函數(shù) 11.3.2 使用直線函數(shù)LINEST和趨

11、勢函數(shù)TREND 線性預(yù)測法 11.3.3 使用指數(shù)函數(shù)LOGEST和增長函數(shù)GROWTH進行非線性預(yù)測 上一頁下一頁返回本章首頁第11章時間數(shù)列2611.3.1 時間數(shù)列預(yù)測工作表函數(shù) FORECAST預(yù)測函數(shù) 功能：根據(jù)給定的數(shù)據(jù)計算或預(yù)測未來值 語法結(jié)構(gòu)：ORECAST(x,known_ys,known_xs)TREND趨勢函數(shù) 功能：得到根據(jù)最小平方法所得的估計直線上的一 組縱坐標(biāo)值（y 值）。 語法結(jié)構(gòu)：REND(known_ys,known_xs,new_xs,const) 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列27GROWTH增長函數(shù) 功能：根據(jù)給定的數(shù)據(jù)擬合指數(shù)曲線，并預(yù)測指

12、數(shù) 增長值。 語法結(jié)構(gòu)：ROWTH(known_ys,known_xs,new_xs,const) LINEST線性擬合函數(shù) 功能：使用最小平方法對已知數(shù)據(jù)擬合一個線性模型。 語法結(jié)構(gòu)：LINEST(known_ys,known_xs,const,stats)LOGEST函數(shù) 功能：用于進行指數(shù)回歸擬合曲線，并返回描述該曲線 的數(shù)組。 語法結(jié)構(gòu)：LOGEST(known_ys,known_xs,const,stats) 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列2811.3.2 使用直線函數(shù)LINEST和趨勢函數(shù)TREND線性預(yù)測法 1. 使用LINEST函數(shù)計算回歸統(tǒng)計值例 有某公司99年1至

13、10月份的銷售量資料， 試用最小平方法進行趨勢預(yù)測，并預(yù) 測11、12月份的銷售量。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列29操作步驟如下：打開“第11章 時間數(shù)列分析與預(yù)測.xls”工作簿，選擇“趨勢”工作表，如下圖所示。 在E1中輸入“回歸統(tǒng)計量”。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列30選擇E2:F6，單擊“粘貼函數(shù)”，Excel彈出“粘貼函數(shù)”對話框，如下圖所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列31在“函數(shù)分類”中選擇“統(tǒng)計”，在“函數(shù)名”中選擇“LINEST”函數(shù)，則打開LINEST函數(shù)對話框，如 下圖所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列32在Known_y

14、s、 Known_xs和Stats后分別輸入B2:B11、A2:A11、和1。按住Ctrl+Shift組合鍵，單擊 “確定”按鈕。得計算結(jié)果如下圖中的E2:F6所示 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列33根據(jù)上圖中的計算結(jié)果可以寫出如下估計方程： y=8.86+0.83*x 據(jù)此方程計算預(yù)測值：在D1單元格中輸入“預(yù)測值”。在D2單元格中輸入公式“=$F$2+$E$2*B2”。將D2單元格中的公式復(fù)制到D3：D16各單元格。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列342使用趨勢函數(shù)TREND求預(yù)測值在單元格C1中輸入“擬合值”。在“插入”菜單中選擇“函數(shù)”選項，將打開“粘貼函數(shù)”對話框

15、。在“函數(shù)分類”中選擇“統(tǒng)計”，在“函數(shù)名”中選擇TREND函數(shù)，單擊“確定”按鈕，將打開TREND對話框，如下頁圖所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列35上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列36在known_ys中輸入B2:B11；在known_xs中輸入A2:A11；由于不需預(yù)測，故忽略new_xs；本題意在保留常數(shù)項，故忽略const。按住Ctrl+Shift組合鍵，單擊“確定”。 n也可以在選定單元格C2:C11的情況下，輸入數(shù)組公式“=TREND(B2:B11,A2:A11)”，按住Ctrl+Shift組合鍵，按回車鍵。輸出結(jié)果如下圖所示。 上一頁下一頁返回本節(jié)首頁第1

16、1章時間數(shù)列373. 趨勢預(yù)測仍使用上圖中的數(shù)據(jù)，預(yù)測過程如下： 在區(qū)域A12中輸入11。在單元格C12中輸入公式：=TREND(B2:B11,A2:A11,A12)。該公式的含義是，給出區(qū)域B2:B11種的y變量和A2:A11中的x變量之間的關(guān)系，當(dāng)x值為11時，y值是多少？上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列3811.3.3 使用指數(shù)函數(shù)LOGEST 和增長函數(shù)GROWTH進行非線性預(yù)測 例 已知某地區(qū)19811995年歷年彩色電視 機產(chǎn)量資料。試對其擬合指數(shù)曲線，并 預(yù)測19961999年產(chǎn)量。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列391使用LOGEST函數(shù) 計算回歸統(tǒng)計量打開“

17、第11章 時間數(shù)列分析與預(yù)測.xls”工作簿，選擇“增長曲線”工作表如下圖所示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列40選擇E2:F6區(qū)域，單擊工具欄中的“粘貼函數(shù)”快捷鍵，彈出“粘貼函數(shù)”對話框，在“函數(shù)分類”中選擇“統(tǒng)計”，在“函數(shù)名”中選擇“LOGEST”函數(shù)，則打開LOGEST對話框，如下圖11.20所示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列41在Known_ys、 Known_xs和Stats后分別輸入C2:C16、B2:B16和1。按住Ctrl+Shift組合鍵，再按回車鍵或“確定”按鈕，得到計算結(jié)果如下圖中E2:F6單元格所示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)

18、列42根據(jù)單元格E2，F(xiàn)2中的計算結(jié)果可以寫出如下估計方程：可以根據(jù)上面的指數(shù)曲線方程計算預(yù)測值：在D2單元格中輸入公式“=$F$2*$E$2B2” ，將D2單元格中的公式復(fù)制到D3:D20元格，各年的預(yù)測值便一目了然了。但使用增長函數(shù)可以直接得到預(yù)測結(jié)果。xy168757. 19204.504上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列432. 使用增長函數(shù)GROWTH計算預(yù)測值選擇D2:D20單元格，在工具欄單擊“粘貼函數(shù)”快捷鍵，彈出“粘貼函數(shù)”對話框，在“函數(shù)分類”中選擇“統(tǒng)計”，在“函數(shù)名”中選擇“GROWTH”函數(shù)，打開GROWTH對話框，如下圖所示。 上一頁下一頁返回本節(jié)首頁第11章

19、時間數(shù)列44在Known_ys、 Known_xs、New_xs和Const后分別輸入C2:C16、B2:B16、B2:B20和1。按住Ctrl+Shift組合鍵，同時按回車鍵或“確定”按鈕，計算結(jié)果如下圖所示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列453. 繪制實際值與預(yù)測值的折線圖在工具欄中單擊 “圖表向?qū)А笨旖輬D標(biāo)，打開“圖表向?qū)А睂υ捒?。在步驟1中選擇“圖表類型”列表中的“折線圖”，在“子圖表類型”下選擇“數(shù)據(jù)點折線圖”，單擊“下一步”按鈕，進入圖表向?qū)Р襟E2對話框。確認(rèn)數(shù)據(jù)區(qū)域為：sheet增長曲線!$A$2:$A$16，sheet增長曲線!$C$2:$D$16，單擊“下一步”按

20、鈕，進入圖表向?qū)Р襟E3對話框。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列46在“標(biāo)題”欄下輸入“某地區(qū)彩色電視機產(chǎn)量發(fā)展趨勢”，單擊“完成”按鈕，得到的結(jié)果如下圖所示 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列4711.4 指數(shù)平滑法與預(yù)測 11.4.1 指數(shù)平滑法的基本內(nèi)容與要求 11.4.2 案例研究：小汽車租賃預(yù)測 11.4.3 指數(shù)平滑分析工具預(yù)測 11.4.4 最佳平滑常數(shù)的確定 上一頁下一頁返回本章首頁第11章時間數(shù)列4811.4.1 指數(shù)平滑法的基本內(nèi)容與要求 指數(shù)平滑法通過對歷史時間數(shù)列進行逐層平滑計算，從而消除隨機因素的影響，識別經(jīng)濟現(xiàn)象基本變化趨勢，并以此預(yù)測未來。 上一頁下

21、一頁返回本節(jié)首頁第11章時間數(shù)列49遞推公式為： 其中：平滑常數(shù)，值域在0到1之間，其大小決定了本次預(yù)測對前期預(yù)測誤差的修正程度。Excel稱（1-）的值為“阻尼因子”。Yt ：t期的實際觀察值；Yt*、Yt+1* ：分別為t 期、t+1期的預(yù)測值。 *11tttYYY上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列50指數(shù)平滑法的兩點說明：1平滑常數(shù)的意義 一般說來，0.2到0.3之間的數(shù)值可作為合理的平 滑常數(shù)。這些數(shù)值表明本次預(yù)測需要將前期預(yù) 測值的誤差調(diào)整 20% 到 30%。2初始預(yù)測值的確定問題 可以指定初始預(yù)測值，即令它等于前幾期（如前 6期或前12期等）實際值的平均數(shù) 。上一頁下一頁返

22、回本節(jié)首頁第11章時間數(shù)列5111.4.2 案例研究：小汽車租賃預(yù)測 例 冬天即將來臨，某從事汽車租賃業(yè)務(wù)的經(jīng)理著手調(diào)查客戶對防雪汽車的需求情況。經(jīng)過監(jiān)測后，一場初冬的暴風(fēng)雪席卷了整個地區(qū)，正如所料，每天的需求量都有顯著增長。這時，可能想知道第10天應(yīng)該儲備多少輛防雪汽車以備第11天使用。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列52(1) 計算預(yù)測值 打開“第11章 時間數(shù)列分析與預(yù)測.xls”工作簿，選擇“指數(shù)平滑”工作表如下圖所示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列53在單元格C2中輸入公式 “=AVERAGE(B2:B6)”。在單元格C3中輸入公式 “=0.3*B2+0.7*C2

23、”。將單元格C3中的公式復(fù)制到區(qū)域C4:C11中，結(jié)果如下圖所示 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列54(2)繪制實際值與擬合值的折線圖單擊工具欄中的“圖表向?qū)А笨旖輬D標(biāo)，打開“圖表向?qū)А睂υ捒颉＿x擇“圖表類型”下的“折線圖”，在“子圖表類型”中選擇“數(shù)據(jù)點折線圖”，單擊“下一步”按鈕 ，進入圖表向?qū)Р襟E2對話框。輸入數(shù)據(jù)區(qū)域：sheet指數(shù)平滑!$A$2:$C$12，單擊“下一步”按鈕，進入圖表向?qū)Р襟E3對話框。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列55在“標(biāo)題”欄下輸入“指數(shù)平滑”，單擊“完成”按鈕，得到折線圖如下圖所示 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列5611.4.3

24、 指數(shù)平滑分析工具預(yù)測 1指數(shù)平滑分析工具的內(nèi)容nExcel所提供的指數(shù)平滑分析工具，為進行指數(shù)平滑分析與預(yù)測提供了方便。指數(shù)平滑分析工具對話框如下圖所示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列572指數(shù)平滑分析工具的使用打開“第11章 時間數(shù)列分析與預(yù)測.XLS”工作簿，選擇“指數(shù)平滑”工作表。把區(qū)域A2:B11內(nèi)容下移一行，在空白的單元格B2中輸入公式“=AVERAGE(B3:B7)。打開“工具”菜單中的“數(shù)據(jù)分析”選項，彈出“數(shù)據(jù)分析”對話框，在“分析工具”列表中選擇“指數(shù)平滑”選項，單擊“確定”按鈕，打開“指數(shù)平滑對話框”。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列58在輸入?yún)^(qū)域輸

25、入B2:B11，阻尼系數(shù)設(shè)為0.7，輸出區(qū)域中輸入C2，選擇圖表輸出。單擊“確定”按鈕 。除單元格C2中顯示的公式為“=B2”外，單元格C3:C12中顯示的公式法均為平滑公式形式，這些是擬合值，要求的預(yù)測值，需把單元格C12中的公式復(fù)制到C13中。則得到的結(jié)果如下圖所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列5911.4.4 最佳平滑常數(shù)的確定 最佳的平滑常數(shù)應(yīng)使實際值和預(yù)測值之間的差最小，通常是預(yù)測誤差的平方和的平方根（RMSPE）最小。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列60niniiiiniiYYnYYnYYnS1122122)(11)(11)(11總iYY上一頁下一頁返回本節(jié)首頁

26、計算誤差的公式為： RMSPE= 式中：Yi 為實際觀測值； 為預(yù)測值； 為實際值的平均值。 總S第11章時間數(shù)列611公式判斷法打開“第11章 時間數(shù)列分析與預(yù)測.XLS”工作簿，選擇“指數(shù)平滑”工作表。在單元格F1中輸入值0.3 。選取區(qū)域E1:F5，從“插入”菜單中選擇“名稱指定”選項，則彈出指定名稱對話框，如下圖所示，選中“最左列”，單擊“確定”按鈕。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列62選取區(qū)域D7:D19，從“插入”菜單中選擇“名稱指定”選項，在彈出的“指定名稱”對話框中，選中“首行”，單擊“確定”按鈕。在單元格C3中輸入公式“=平滑常數(shù)*B2+(1-平滑常數(shù))*C2”，并

27、把它復(fù)制到單元格C4:C11中。在單元格D7中輸入公式“=B7-C7”，并把它復(fù)制到單元格D8:D11中。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列63在單元格F3、F4、F5中分別輸入公式：“=AVERAGE(預(yù)測誤差)”、“=STDEV(預(yù)測誤差)”、和“=SQRT(平均誤差2+標(biāo)準(zhǔn)誤差2)”。結(jié)果如下圖所示 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列64在單元格F1中試用不同的值，則預(yù)測值、預(yù)測誤差值、平均誤差、標(biāo)準(zhǔn)誤差和RMSPE的值以及圖中曲線也將相應(yīng)發(fā)生變化，比較RMSPE的大小，選取RMSPE最小時的值作為最優(yōu)平滑常數(shù)進行預(yù)測。本例中平滑常數(shù)為0.85時RMSPE最小。此時得到的修

28、飾后的指數(shù)平滑圖如下圖所示。 指數(shù)平滑曲線5101520251234567891011租賃數(shù)擬合值 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列652利用“單變量求解”自動尋找最佳平滑常數(shù) n單變量求解是指通過調(diào)整其他單元格內(nèi)的 數(shù)值為某一單元格尋求一個特定值。單變量求解對話框如下圖所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列66單變量求解的實現(xiàn)過程繼11.4.3所示的工作表，在“工具”菜單中選擇“單變量求解”選項，將出現(xiàn)“單變量求解”對話框。 在目標(biāo)單元格、目標(biāo)值、可變單元格中分別輸入F5、0和F1，單擊“確定”按鈕。求解的結(jié)果如下頁圖1所示，同時，工作表F列也在發(fā)生變化，如下頁圖2所示。 單

29、擊“確定”按鈕 ，則計算機自動尋找的最佳平滑常數(shù)結(jié)果被呈現(xiàn)在工作表上。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列67圖1圖2上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列6811.5 季節(jié)變動的測定與分析 11.5.1 長期趨勢剔除法 11.5.2 利用啞變量進行季節(jié)調(diào)整與預(yù)測 上一頁下一頁返回本章首頁第11章時間數(shù)列6911.5.1 長期趨勢剔除法 長期趨勢剔除法是在移動平均法的基礎(chǔ)上，以乘法模型（Y=TS C I ）為理論基礎(chǔ)的測定季節(jié)變動的方法，它能避免長期趨勢與周期波動的影響，凈化季節(jié)變動的規(guī)律性，從而實現(xiàn)較為準(zhǔn)確的預(yù)測。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列70長期趨勢剔除法的計算步驟

30、：利用中心化移動平均計算長期趨勢與周期波動要素TCi。從時間數(shù)列中剔除掉TCi ，就得到季節(jié)波動與不規(guī)則變動SIi： SIi =Yi/Tci。按季求SIi的平均數(shù)，從而剔除不規(guī)則變動I，得到各季季節(jié)指數(shù)Si1。計算公式為： 式中：N為年數(shù)。NSISNjjii1)1(41上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列71對初始季節(jié)指數(shù)調(diào)整為正規(guī)化季節(jié)指數(shù)Sj。依據(jù)的公式為：計算剔除季節(jié)變動后的時間數(shù)列TCIi：TCIi=Yi/Si。對TCIi序列進行外推預(yù)測，得到一組預(yù)測值Ti 。計算最終預(yù)測值：Y* i= Sj Ti 。 41114iiijSSS上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列72例 已知1

31、993年第1季度到1997年第四 季度的某地區(qū)的季度零售額資料， 試對1998年的零售額進行預(yù)測 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列73操作過程： 打開“第11章 時間數(shù)列分析與預(yù)測.XLS”工作簿，選擇“長期趨勢剔除”工作表，如下圖所示 。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列74在單元格E4中輸入公式“AVERAGE(D2:D5)”。在單元格F4中輸入公式 “AVERAGE(E4:E5)”。把單元格E4:F4中的公式復(fù)制到E19:F19，調(diào)整其小數(shù)部分使顯示1位小數(shù)，結(jié)果如下圖所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列75在單元格G4中輸入公式“D4F4”，并把它復(fù)制到G5:

32、G19。在單元格H2中輸入公式 “AVERAGE(H6,H10,H14,H18)”, 并把它復(fù)制到單元格H3中。在單元格H4中輸入公式 “AVERAGE(H4，H8，H12，H16)”， 并把它復(fù)制到單元格H5中。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列76選取單元格H6，點擊自動求和工具（）兩次。選取單元格I2，輸入公式“H2*4$H$6”， 并把它復(fù)制到單元格I3:I5。選取單元格I6，點擊自動求和工具（）兩次。上述操作的結(jié)果如下圖所示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列77通過雷達圖表現(xiàn)季節(jié)指數(shù)的意義:單擊工具欄中的“圖表向?qū)А笨旖輬D標(biāo)，打開“圖表向?qū)А睂υ捒?。在“圖表類型”列

33、表下選擇“雷達圖”，在“子圖表類型”下選擇“數(shù)據(jù)點雷達圖”，單擊“下一步”按鈕，進入圖表向?qū)Р襟E2對話框。輸入數(shù)據(jù)區(qū)域為： sheet長期趨勢剔除!$I$2:$I$5，單擊“下一步”按鈕，進入圖表向?qū)Р襟E3對話框。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列78在“標(biāo)題”頁面下輸入“季節(jié)指數(shù)雷達圖”，選擇“圖例”頁面，取消顯示圖例。單擊“完成”按鈕。經(jīng)修飾后得到的雷達圖如下圖所示。n結(jié)論：銷售額在第一季度進入淡季，在第四季度則達到旺季。 季節(jié)指數(shù)雷達圖0.800.901.001.101234上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列79預(yù)測： 在區(qū)域J1:L1中分別輸入標(biāo)志“季節(jié)指數(shù)S序列”、“T

34、CI*”和“預(yù)測值”。選中單元格I2:I5，點擊主菜單中“復(fù)制”圖標(biāo) 。選中單元格J2:J25，點擊“編輯”菜單，選擇“選擇性粘貼”選項，在出現(xiàn)的選擇性粘貼對話框中選擇“值”，單擊“確定”按鈕。在單元格K2中輸入公式“D2J2”，并把它復(fù)制到K2:K21。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列80選中單元格K2:K21，點擊“編輯”菜單，選擇“選擇性粘貼”選項，在出現(xiàn)的選擇性粘貼對話框中選擇“值”，單擊“確定”按鈕。選中單元格K2:K21，單擊單元格K21右下角的填充句柄并向下拖動到單元格K25。在單元格L22中輸入公式 “I22*K22”。并把它復(fù)制到L23:L25。上述操作結(jié)果如下頁圖所

35、示。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列81上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列82可以通過繪制實際銷售額和預(yù)測值圖表察 看預(yù)測情況。選取單元格C1:D25，按住Ctrl鍵并選取單元格L1:L25，單擊工具欄中的“圖表向?qū)А笨旖輬D標(biāo)，打開“圖表向?qū)А睂υ捒?。?“圖表類型”列表下選擇“折線圖”，在“子圖表類型”下選擇“數(shù)據(jù)點折線圖”，單擊“下一步”按鈕。輸入數(shù)據(jù)區(qū)域為： sheet長期趨勢剔除!$ C1$2:$D$25，sheet長期趨勢剔除!$ L$1:$L$25，單擊“下一步”按鈕。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列83在“標(biāo)題”頁面下輸入“實際值、季節(jié)調(diào)整TCI及預(yù)測值比

36、較圖”。單擊“ 完成”按鈕，結(jié)果如下圖所示。 實際值、季節(jié)調(diào)整TCI及預(yù)測值比較圖200250300350400450119933119943119953119963119973119983銷售額YTCI*預(yù)測值上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列8411.5.2 利用啞變量進行季節(jié)調(diào)整與預(yù)測這種季節(jié)調(diào)整方法是以季節(jié)變動的變動服從加法模型為前提，同時利用季節(jié)影響和時間作為自變量，進行多元回歸分析。時間變量可用時間序號表示，季節(jié)變量可用啞變量來描述 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列85操作過程：打開“第11章 時間數(shù)列分析與預(yù)測.xls”工作簿，選擇“季節(jié)變動”工作表如下圖所示。 上

37、一頁下一頁返回本節(jié)首頁第11章時間數(shù)列86從“工具”菜單中，選擇“數(shù)據(jù)分析”選項，則出現(xiàn)“數(shù)據(jù)分析”工具對話框。選中“回歸”選項，并單擊“確定”按鈕，此時將出現(xiàn)“回歸”對話框。如下圖所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列87在“Y值輸入?yún)^(qū)域”、“X值輸入?yún)^(qū)域”、“輸出區(qū)域”中分別輸入D1:D21，E1:E21和K1；選擇標(biāo)志、置信度、殘差、殘差圖、標(biāo)準(zhǔn)殘差和線性擬合圖。單擊“確定”按鈕。 n下圖所示為編輯過的一部分結(jié)果。 上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列88根據(jù)上圖中輸出的計算結(jié)果可以得出擬合方程為： 銷售額Y=311.005+5.106*時間-56.601* 第一季度-19.

38、387* 第二季度- 22.574*第三季度回歸分析工具產(chǎn)生的時序圖中顯示了實際值和擬合值，如下圖所示。實際值與預(yù)測值比較圖200250300350400450119933119943119953119963119973119983時間銷售額Y預(yù)測值銷售額季節(jié)變動上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列89預(yù)測：把區(qū)域A18:C21的內(nèi)容復(fù)制到單元格A22:C25，在單元格A22中輸入1988。選取區(qū)域E20:E21并把填充句柄向下拖到單元格E25。把區(qū)域F18:I21的內(nèi)容復(fù)制到單元格F22:I25。在單元格J1中輸入標(biāo)記“預(yù)測值”選取區(qū)域J2:J25，按數(shù)組方式輸入公式 “=TREND(D2:D21,E2:H21,E2:H25,1)”， 調(diào)整小數(shù)位數(shù)為1。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列90結(jié)果如下圖中J列所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列9111.6 周期波