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

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

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

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

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

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

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

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

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

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

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

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

12、合指數(shù)曲線，并預測指數(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ù)列2911.3.2 使用直線函數(shù)LINEST和趨勢函數(shù)TREND線性預測法 1. 使用LINEST函數(shù)計算回歸統(tǒng)計值例

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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ù)列72對初始季節(jié)指數(shù)調(diào)整為正規(guī)化季節(jié)指數(shù)Sj。依據(jù)的公式為：計算剔除季節(jié)變動后的時間數(shù)列TCIi：TCIi=Yi/Si。對TCIi序列進行外推預測，得到一組預測值Ti 。計算最終預測值：Y* i= Sj Ti 。 41114iiijSSS上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列

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

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

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

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

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

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

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

38、度-19.387* 第二季度- 22.574*第三季度回歸分析工具產(chǎn)生的時序圖中顯示了實際值和擬合值，如下圖所示。實際值與預測值比較圖200250300350400450119933119943119953119963119973119983時間銷售額Y預測值銷售額季節(jié)變動上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列90預測：把區(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ù)組方式輸入公式 “=TREND(D2:D21,E2:H21,E2:H25,1)”， 調(diào)整小數(shù)位數(shù)為1。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列91結(jié)果如下圖中J列所示。上一頁下一頁返回本節(jié)首頁第11章時間數(shù)列