第12章 時(shí)間數(shù)列分析與預(yù)測(cè) 2009

1、LOGO于洪彥于洪彥 劉金星劉金星 張洪利張洪利Excel Excel 統(tǒng)計(jì)分析與決策統(tǒng)計(jì)分析與決策第第1212章章 時(shí)間序列分析與預(yù)測(cè)時(shí)間序列分析與預(yù)測(cè) 主要內(nèi)容主要內(nèi)容 12.1 時(shí)間數(shù)列的基本特征 12.2 移動(dòng)平均分析與預(yù)測(cè) 12.3 回歸分析與預(yù)測(cè) 12.4 指數(shù)平滑法與預(yù)測(cè) 12.5 季節(jié)變動(dòng)的測(cè)定與分析 12.6 周期波動(dòng)的測(cè)定與分析 v時(shí)間數(shù)列的概念與特點(diǎn) 時(shí)間數(shù)列按時(shí)間先后順序排列 時(shí)間數(shù)列是按一定方式搜集的一系列數(shù)據(jù) 時(shí)間數(shù)列中的觀察值具有差異 時(shí)間數(shù)列中的數(shù)據(jù)不許遺漏12.1 12.1 時(shí)間數(shù)列的基本特征時(shí)間數(shù)列的基本特征 v時(shí)間數(shù)列的構(gòu)成與分解 乘法模型的一般形式為：

2、Y=TSCI 式中 Y、T是總量指標(biāo) S、C、I為比率 加法模型的一般形式為： Y=T+S+C+I 式中Y、T、S、C、I都是總量指標(biāo) 12.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè)v移動(dòng)平均法的基本內(nèi)容 移動(dòng)平均法是在算術(shù)平均法的基礎(chǔ)上發(fā)展起來(lái)的預(yù)測(cè)方法。 它是利用過(guò)去若干期實(shí)際值的均值來(lái)預(yù)測(cè)現(xiàn)象的發(fā)展趨勢(shì) 簡(jiǎn)單移動(dòng)平均公式如下： njjttANM111112.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè)v利用趨勢(shì)圖測(cè)定長(zhǎng)期趨勢(shì) 例 12.1 對(duì)2008年的零售額進(jìn)行預(yù)測(cè)。 產(chǎn)生“年季”變量 選擇“移動(dòng)平均”工作表。 在C列選定任一個(gè)單元格，選擇“插入”菜單中的“列”選項(xiàng)，則

3、原來(lái)C列的內(nèi)容被移到D列。在C1單元格中輸入標(biāo)志“年季” 在單元格C2中輸入公式“=B2&CHAR(13)&A2”。再把單元格C2中的公式復(fù)制到C3：C21。結(jié)果如圖所示。12.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè) 根據(jù)圖12.2中資料繪制銷(xiāo)售額趨勢(shì)圖。 打開(kāi)“插入”菜單中的“圖表”選項(xiàng)，Excel彈出“圖表向?qū)А睂?duì)話(huà)框。 在步驟1的“圖表類(lèi)型”列表中選擇“折線(xiàn)圖”選項(xiàng)；在“子圖表類(lèi)型”列表中選擇“數(shù)據(jù)點(diǎn)折線(xiàn)圖”。單擊“下一步”按鈕 ，進(jìn)入向?qū)Р襟E2對(duì)話(huà)框。12.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè) 在步驟2的“圖表數(shù)據(jù)源”對(duì)話(huà)框中，在數(shù)據(jù)區(qū)域輸入

4、“C1：D21”，并單擊“下一步”按鈕，進(jìn)入向?qū)Р襟E3對(duì)話(huà)框。 在步驟3的“圖表選項(xiàng)”中，選擇“標(biāo)題”頁(yè)面，在“圖表標(biāo)題”、“分類(lèi)（X）軸”、“數(shù)值（Y）軸”中分別填入“某地區(qū)銷(xiāo)售額趨勢(shì)圖”、“季度”和“銷(xiāo)售額（萬(wàn)元）”；選擇“網(wǎng)格線(xiàn)”頁(yè)面，把“數(shù)值（Y）軸”下的“主要網(wǎng)格線(xiàn)”設(shè)為空；選擇“圖例”頁(yè)面，取消圖例顯示。單擊“完成”按鈕，得到趨勢(shì)圖。12.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè) 在圖表中插入趨勢(shì)線(xiàn)進(jìn)行預(yù)測(cè) 單擊圖表以激活它，選取垂直軸，雙擊或單擊鼠標(biāo)右鍵并從快捷菜單中選擇“坐標(biāo)軸格式”選項(xiàng)，Excel彈出對(duì)話(huà)框。打開(kāi)“刻度”頁(yè)面，在“最小值”、“最大值”、“主要刻度單位

5、”中分別輸入200、450和50；打開(kāi)“字體”頁(yè)面，設(shè)置字號(hào)為“8”。單擊“確定”按鈕。 選取水平軸，雙擊或單擊鼠標(biāo)右鍵并從快捷菜單中選擇“坐標(biāo)軸格式”選項(xiàng)，Excel彈出對(duì)話(huà)框。打開(kāi)“對(duì)齊”頁(yè)面，取消自動(dòng)設(shè)置；打開(kāi)“字體”頁(yè)面，設(shè)置字號(hào)為“8”。單擊“確定”按鈕。12.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè) 選取圖中的折線(xiàn)，單擊鼠標(biāo)右鍵并從快捷菜單中選擇“添加趨勢(shì)線(xiàn)”選項(xiàng)，打開(kāi)“添加趨勢(shì)線(xiàn)”對(duì)話(huà)框。選擇“類(lèi)型”頁(yè)面，在“趨勢(shì)預(yù)測(cè)/回歸分析類(lèi)型”框中選擇“移動(dòng)平均”，設(shè)置“周期”為4。 單擊“確定”按鈕。12.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè)v 利用Excel創(chuàng)建

6、公式測(cè)定長(zhǎng)期趨勢(shì) 選擇“移動(dòng)平均”工作表。 在單元格E1中輸入“公式預(yù)測(cè)值”。 在單元格E6中輸入公式“=AVERAGE(D2：D5)”。 把單元格E6中的公式復(fù)制到E7：E22各單元格中。結(jié)果如圖所示。12.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè)v移動(dòng)平均分析工具測(cè)定長(zhǎng)期趨勢(shì) 移動(dòng)平均分析工具的內(nèi)容12.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè) 利用移動(dòng)平均分析工具進(jìn)行預(yù)測(cè) 選擇“移動(dòng)平均”工作表。 在單元格F1、G1中分別輸入“分析工具預(yù)測(cè)值”和“預(yù)測(cè)標(biāo)準(zhǔn)誤差”。 從“工具”菜單中選擇“數(shù)據(jù)分析”選項(xiàng)，在彈出的“數(shù)據(jù)分析”對(duì)話(huà)框中選中“移動(dòng)平均”選項(xiàng)，并單擊“確定”按

7、鈕，出現(xiàn)“移動(dòng)平均”對(duì)話(huà)框。 在輸入?yún)^(qū)域中輸入D1：D21，選定標(biāo)志位于第一行，間隔設(shè)為4，在輸出區(qū)域中輸入F2。選擇圖表輸出和標(biāo)準(zhǔn)誤差。單擊“確定”按鈕，所得結(jié)果如圖所示。12.2 12.2 移動(dòng)平均分析與預(yù)測(cè)移動(dòng)平均分析與預(yù)測(cè)v 時(shí)間數(shù)列預(yù)測(cè)工作表函數(shù) FORECASTFORECAST預(yù)測(cè)函數(shù)預(yù)測(cè)函數(shù) TRENDTREND趨勢(shì)函數(shù)趨勢(shì)函數(shù) GROWTHGROWTH增長(zhǎng)函數(shù)增長(zhǎng)函數(shù) LINESTLINEST線(xiàn)性擬合函數(shù)線(xiàn)性擬合函數(shù) LOGESTLOGEST函數(shù)函數(shù)v 使用直線(xiàn)函數(shù)LINEST和趨勢(shì)函數(shù)TREND線(xiàn)性預(yù)測(cè)法 使用LINEST函數(shù)計(jì)算回歸統(tǒng)計(jì)值 例12.2 用最小平方法進(jìn)行趨 勢(shì)

8、預(yù)測(cè)11、12月份的銷(xiāo)售量。 選擇“趨勢(shì)”工作表。12.3 12.3 回歸分析測(cè)定長(zhǎng)期趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì)在E1中輸入“回歸統(tǒng)計(jì)量”。選擇E2：F6，單擊“插入”菜單，選擇“函數(shù)”，Excel彈出“插入函數(shù)”對(duì)話(huà)框。在“選擇類(lèi)別”中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”中選擇“LINEST”函數(shù)，則打開(kāi)LINEST函數(shù)對(duì)話(huà)框。在Known_ys、 Known_xs和Stats后分別輸入B2：B11、A2：A11、和1。按住Ctrl+Shift組合鍵，單擊 “確定”按鈕。12.3 12.3 回歸分析測(cè)定長(zhǎng)期趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì) 在選中E2：F6的情況下輸入數(shù)組公式：“=LINEST(C2:C11,

9、B2:B11,1)”，同時(shí)按住Ctrl+Shift兩鍵，再按回車(chē)鍵。得到的結(jié)果與上面操作相同。計(jì)算預(yù)測(cè)值： 在D1單元格中輸入“預(yù)測(cè)值”。 在D2單元格中輸入公式“=$F$2+$E$2*B2”。 將D2單元格中的公式復(fù)制到D3：D11各單元格。12.3 12.3 回歸分析測(cè)定長(zhǎng)期趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì) 使用趨勢(shì)函數(shù)TREND求預(yù)測(cè)值 在單元格C1中輸入“擬合值”。 選定單元格C2:C11。 在“插入”菜單中選擇“函數(shù)”選項(xiàng)，打開(kāi)“插入函數(shù)”對(duì)話(huà)框。 在“選擇類(lèi)別”中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”中選擇TREND函數(shù)，單擊“確定”按鈕，將打開(kāi)TREND對(duì)話(huà)框。12.3 12.3 回歸分析測(cè)定長(zhǎng)期

10、趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì) 在known_ys中輸入B2：B11；在known_xs中輸入A2：A11；由于不需預(yù)測(cè)，故忽略new_xs；意在保留常數(shù)項(xiàng)，故忽略const。按住Ctrl+Shift組合鍵，單擊“確定”。 也可以在選定單元格C2：C11的情況下，輸入數(shù)組公式“=TREND(B2:B11,A2:A11)”，按住Ctrl+Shift組合鍵，按回車(chē)鍵。輸出結(jié)果如圖所示。12.3 12.3 回歸分析測(cè)定長(zhǎng)期趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì)趨勢(shì)預(yù)測(cè)在單元格A12中輸入11。在單元格C12中輸入公式：“=TREND(B2:B11,A2:A11,A12)”。用TREND函數(shù)也可以進(jìn)行多期預(yù)測(cè)在區(qū)域A12

11、:A25中分別輸入1124。選定區(qū)域C12:C25，輸入數(shù)組公式：=TREND(B2:B11,A2:A11,A12:A25)，按住Ctrl+Shift組合鍵，按回車(chē)鍵。將在C12：C25的各單元格中返回第11到第24個(gè)時(shí)間點(diǎn)的預(yù)測(cè)值。12.3 12.3 回歸分析測(cè)定長(zhǎng)期趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì)v 使用指數(shù)函數(shù)LOGEST和增長(zhǎng)函數(shù)GROWTH擬合長(zhǎng)期趨勢(shì) 例 12.3 擬合指數(shù)曲線(xiàn)，并預(yù)測(cè)20082011年產(chǎn)量。 使用LOGEST函數(shù) 計(jì)算回歸統(tǒng)計(jì)量 選擇“增長(zhǎng)曲線(xiàn)”工作表。12.3 12.3 回歸分析測(cè)定長(zhǎng)期趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì) 選擇E2：F6區(qū)域，單擊“插入函數(shù)”快捷鍵，彈出“插入函數(shù)

12、”對(duì)話(huà)框。 在“選擇類(lèi)別”中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”中選擇“LOGEST”函數(shù)，打開(kāi)LOGEST對(duì)話(huà)框。 在Known_ys、 Known_xs和Stats后分別輸入C2：C16、B2：B16和1。按住Ctrl+Shift組合鍵，再按回車(chē)鍵或“確定”按鈕，得計(jì)算結(jié)果如圖中E2：F6單元格所示。12.3 12.3 回歸分析測(cè)定長(zhǎng)期趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì) 使用增長(zhǎng)函數(shù)GROWTH計(jì)算預(yù)測(cè)值 選擇D2：D20單元格，在工具欄單擊“插入函數(shù)”快捷鍵，彈出“插入函數(shù)”對(duì)話(huà)框。 在“選擇類(lèi)別”中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”中選擇“GROWTH”函數(shù)，打開(kāi)GROWTH對(duì)話(huà)框。 在Known_ys、 K

13、nown_xs、New_xs和Const后分別輸入C2：C16、B2：B16、B2：B20和1。按住Ctrl+Shift組合鍵，同時(shí)按回車(chē)鍵或“確定”按鈕，計(jì)算結(jié)果如圖所示。 或者在選擇D2：D20單元格的情況下，輸入數(shù)組公式“=GROWTH(C2:C16,B2:B16, B2:B20,1)”，同時(shí)按住Ctrl+Shift組合鍵，同時(shí)按回車(chē)鍵。結(jié)果與上述相同。12.3 12.3 回歸分析測(cè)定長(zhǎng)期趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì) 繪制實(shí)際值與預(yù)測(cè)值的折線(xiàn)圖： 在工具欄中單擊 “圖表向?qū)А笨旖輬D標(biāo)打開(kāi)“圖表向?qū)А睂?duì)話(huà)框。 在步驟1中選擇“圖表類(lèi)型”列表中的“折線(xiàn)圖”，在“子圖表類(lèi)型”下選擇“數(shù)據(jù)點(diǎn)折線(xiàn)圖”

14、，單擊“下一步”按鈕。 確認(rèn)數(shù)據(jù)區(qū)域?yàn)椋簊heet增長(zhǎng)曲線(xiàn)！$A$2:$A$16，sheet增長(zhǎng)曲線(xiàn)！$C$2:$D$16，單擊“下一步”按鈕。 在“標(biāo)題”欄下輸入“某地區(qū)彩色電視機(jī)產(chǎn)量發(fā)展趨勢(shì)”，單擊“完成”按鈕，得到的結(jié)果如圖所示。12.3 12.3 回歸分析測(cè)定長(zhǎng)期趨勢(shì)回歸分析測(cè)定長(zhǎng)期趨勢(shì)v指數(shù)平滑法的基本內(nèi)容與要求 通過(guò)對(duì)歷史時(shí)間數(shù)列進(jìn)行逐層平滑計(jì)算，從而消除隨機(jī)因素的影響，識(shí)別經(jīng)濟(jì)現(xiàn)象基本變化趨勢(shì)，并以此預(yù)測(cè)未來(lái)。 遞推公式 平滑常數(shù)的意義 一般說(shuō)來(lái)，0.2 到 0.3 之間的數(shù)值可作為合理的平滑常數(shù)。這些數(shù)值表明本次預(yù)測(cè)需要將前期預(yù)測(cè)值的誤差調(diào)整 20% 到 30%。 初始預(yù)測(cè)值的

15、確定問(wèn)題 不指定初始預(yù)測(cè)值，計(jì)算機(jī)把第一個(gè)實(shí)際值作為初始預(yù)測(cè)值進(jìn)行預(yù)測(cè)。 指定初始預(yù)測(cè)值，即令它等于前幾期實(shí)際值的平均數(shù)。 指定初始預(yù)測(cè)值對(duì)用Excel進(jìn)行計(jì)算的操作過(guò)程沒(méi)有影響，只對(duì)預(yù)測(cè)結(jié)果產(chǎn)生影響。 *11tttYYY12.4 12.4 指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)v案例研究：小汽車(chē)租賃預(yù)測(cè) 例12.4 計(jì)算預(yù)測(cè)值 選擇“指數(shù)平滑”工作表。 在單元格C2中輸入公式“=AVERAGE(B2：B6)”。 在單元格C3中輸入公式“=0.3*B2+0.7*C2”。 將單元格C3中的公式復(fù)制到區(qū)域C4:C12中。結(jié)果如圖所示。12.4 12.4 指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)指數(shù)平滑法測(cè)定長(zhǎng)

16、期趨勢(shì) 繪制實(shí)際值與擬合值的折線(xiàn)圖 單擊工具欄中的“圖表向?qū)А笨旖輬D標(biāo)，打開(kāi)“圖表向?qū)А睂?duì)話(huà)框。 在步驟1中選擇“圖表類(lèi)型”下的“折線(xiàn)圖”，在“子圖表類(lèi)型”下選擇“數(shù)據(jù)點(diǎn)折線(xiàn)圖” ，單擊“下一步”按鈕。 輸入數(shù)據(jù)區(qū)域：sheet指數(shù)平滑！$B$1:$C$12，單擊“下一步”按鈕。 在“標(biāo)題”欄下輸入“指數(shù)平滑”，單擊“完成”按鈕，如圖所示。12.4 12.4 指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)v 指數(shù)平滑分析工具預(yù)測(cè) 指數(shù)平滑分析工具的內(nèi)容 指數(shù)平滑分析工具的使用 選擇“指數(shù)平滑”工作表。 把區(qū)域A2:B12內(nèi)容下移一行，在空白的單元格B2中輸入公式“=AVERAGE(B3:B7)。

17、 打開(kāi)“工具”菜單中的“數(shù)據(jù)分析”選項(xiàng)，彈出“數(shù)據(jù)分析”對(duì)話(huà)框。在“分析工具”列表中選擇“指數(shù)平滑”選項(xiàng)，單擊“確定”按鈕，打開(kāi)“指數(shù)平滑對(duì)話(huà)框” 。12.4 12.4 指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì) 在輸入?yún)^(qū)域輸入B2：B12，阻尼系數(shù)設(shè)為0.7，輸出區(qū)域中輸入C2，選擇圖表輸出。單擊“確定”按鈕。 除單元格C2中顯示的公式為“=B2”外，單元格C3:C12中顯示的公式法均為平滑公式形式，這些是擬合值，要求的預(yù)測(cè)值，需把單元格C12中的公式復(fù)制到C13中。則得到的結(jié)果如圖所示。12.4 12.4 指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)v最佳平滑常數(shù)的確定 公式判斷法 選擇

18、“指數(shù)平滑”工作表。 在單元格F1中輸入值0.3。 選取區(qū)域E1：F5，從“插入”菜單中選擇“名稱(chēng)指定”選項(xiàng)，則彈出指定名稱(chēng)對(duì)話(huà)框，選中“最左列”，單擊“確定”按鈕。12.4 12.4 指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì) 選取區(qū)域D6：D11，從“插入”菜單中選擇“名稱(chēng)指定”選項(xiàng)，在彈出的指定名稱(chēng)對(duì)話(huà)框，選中“首行”，單擊“確定”按鈕。 在單元格C2中輸入公式“=AVERAGE(B2：B6)”。在單元格C3中輸入公式“=平滑常數(shù)*B2+(1-平滑常數(shù))*C2”，并復(fù)制到單元格C4:C11中。 在單元格D7中輸入公式“=B7-C7”，并復(fù)制到單元格D8:D11中。 在單元格F3、F4、

19、F5中分別輸入公式：“=AVERAGE(預(yù)測(cè)誤差)”、“=STDEV(預(yù)測(cè)誤差)”、和“=SQRT(平均誤差2+標(biāo)準(zhǔn)誤差2)”。結(jié)果如圖所示。12.4 12.4 指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì) 在單元格F1中試用不同的值，則預(yù)測(cè)值、預(yù)測(cè)誤差值、平均誤差、標(biāo)準(zhǔn)誤差和RMSPE的值以及圖中曲線(xiàn)也將相應(yīng)發(fā)生變化，比較RMSPE的大小，選取RMSPE最小時(shí)的值作為最優(yōu)平滑常數(shù)進(jìn)行預(yù)測(cè)。本例中平滑常數(shù)為0.85時(shí)RMSPE最小。此時(shí)得到的修飾后的指數(shù)平滑圖如圖所示。指 數(shù) 平 滑 曲 線(xiàn)51 01 52 02 51234567891 01 1租 賃 數(shù)擬 合 值 12.4 12.4 指數(shù)平

20、滑法測(cè)定長(zhǎng)期趨勢(shì)指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì) 繼“指數(shù)平滑”工作表，在“工具”菜單中選擇“單變量求解”選項(xiàng)，出現(xiàn)單變量求解對(duì)話(huà)框。 在目標(biāo)單元格、目標(biāo)值、可變單元格中分別輸入F5、0和F1，單擊“確定”按鈕。將出現(xiàn)單變量求解狀態(tài)對(duì)話(huà)框，可以看到計(jì)算機(jī)的計(jì)算篩選過(guò)程。當(dāng)計(jì)算機(jī)篩選出最終結(jié)果后，將停止計(jì)算。 工作表F列也在發(fā)生變化，如圖所示。 單擊“確定”按鈕，計(jì)算機(jī)自動(dòng)尋找的最佳平滑常數(shù)結(jié)果呈現(xiàn)在工作表上。12.4 12.4 指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)指數(shù)平滑法測(cè)定長(zhǎng)期趨勢(shì)12.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 v季節(jié)變動(dòng)測(cè)定的基本內(nèi)容 簡(jiǎn)單平均法 長(zhǎng)期趨勢(shì)剔除法v 利用簡(jiǎn)單平均法測(cè)

21、定季節(jié)變動(dòng)及其應(yīng)用 例12.5 計(jì)算季節(jié)指數(shù)。 選擇“簡(jiǎn)單平均”工作表。 在單元格H3中輸入公式“AVERAGE(B3：G3)”，計(jì)算季平均數(shù)。 將單元格H3公式復(fù)制到H4:H6。 在單元格B7中輸入公式“AVERAGE(B3：G6)”，計(jì)算總平均數(shù)。 在單元格I3中輸入公式“H3$B$7”，并把它復(fù)制到I4：I6，計(jì)算各個(gè)季節(jié)指數(shù)，結(jié)果如圖所示。12.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 v利用長(zhǎng)期趨勢(shì)剔除法測(cè)定季節(jié)變動(dòng)及其應(yīng)用 選擇“長(zhǎng)期趨勢(shì)剔除”工作表。 在單元格E4中輸入公式“AVERAGE(D2：D5)”。 在單元格F4中輸入公式“AVERAGE(E4：E5)”。

22、把單元格E4：F4中的公式復(fù)制到E5：F19，調(diào)整其小數(shù)部分使顯示1位小數(shù)，結(jié)果如圖所示。12.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 在單元格G4中輸入公式“D4F4”，并把它復(fù)制到G5：G19。 在單元格H2中輸入公式“AVERAGE(H6，H10，H14，H18)”, 并把它復(fù)制到單元格H3中。 在單元格H4中輸入公式“AVERAGE(H4，H8，H12，H16)”， 并把它復(fù)制到單元格H5中。 選取單元格H6，點(diǎn)擊自動(dòng)求和工具（）兩次。 在單元格I2輸入公式“H2*4$H$6”， 把它復(fù)制到單元格I3：I5。選取單元格I6，點(diǎn)擊自動(dòng)求和工具（）兩次。上述操作的結(jié)果如上圖

23、所示。12.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 v 制作雷達(dá)圖 單擊工具欄中的“圖表向?qū)А笨旖輬D標(biāo)，打開(kāi)“圖表向?qū)А睂?duì)話(huà)框。 在步驟1中，于“圖表類(lèi)型”列表下選擇“雷達(dá)圖”，在“子圖表類(lèi)型”下選擇“數(shù)據(jù)點(diǎn)雷達(dá)圖”，單擊“下一步”按鈕。 輸入數(shù)據(jù)區(qū)域?yàn)椋簊heet長(zhǎng)期趨勢(shì)剔除！$I$2:$I$5，單擊“下一步”按鈕。 在“標(biāo)題”頁(yè)面下輸入“季節(jié)指數(shù)雷達(dá)圖”，選擇“圖例”頁(yè)面，取消顯示圖例。單擊“完成”按鈕。經(jīng)修飾后得到的雷達(dá)圖，如圖所示： 季節(jié)指數(shù)雷達(dá)圖0.800.901.001.10123412.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 根據(jù)公式剔除季節(jié)變動(dòng)的

24、影響，利用回歸模型結(jié)合季節(jié)指數(shù)進(jìn)行預(yù)測(cè)。 在區(qū)域J1：L1中分別輸入標(biāo)志“季節(jié)指數(shù)S序列”、“TCI*”和“預(yù)測(cè)值”。 選中單元格I2：I5，點(diǎn)擊主菜單中“復(fù)制”圖標(biāo)。 選中單元格J2：J25，點(diǎn)擊“編輯”菜單，選擇“選擇性粘貼”選項(xiàng)，在出現(xiàn)的選擇性粘貼對(duì)話(huà)框中選擇“數(shù)值”，單擊“確定”按鈕。 在單元格K2中輸入公式“D2J2”，并把它復(fù)制到K2：K21，則區(qū)域K2：K21中的值即為季節(jié)調(diào)整后的序列TCI*。 選中單元格K2：K21，點(diǎn)擊“編輯”菜單，選擇“選擇性粘貼”選項(xiàng)，在出現(xiàn)的選擇性粘貼對(duì)話(huà)框中選擇“數(shù)值”，單擊“確定”按鈕。把K2：K21中的公式形式變?yōu)閿?shù)值形式。 選中單元格K2：K2

25、1，單擊單元格K21右下角的填充句柄并向下拖動(dòng)到單元格K25。當(dāng)Excel用于這種方式自動(dòng)填充時(shí)，區(qū)域K2:K21中的數(shù)列自動(dòng)用線(xiàn)性趨勢(shì)預(yù)測(cè)的數(shù)據(jù)對(duì)K22:K25進(jìn)行擴(kuò)展預(yù)測(cè)。這種方法相當(dāng)于用TREND函數(shù)作簡(jiǎn)單線(xiàn)性回歸，對(duì)序列21到24進(jìn)行預(yù)測(cè)。 把回歸預(yù)測(cè)的TCI*分量和季節(jié)分量S組合得到預(yù)測(cè)值Y*，即：Y*=TCI* S。12.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 在單元格L22中輸入公式“I22*K22”，并把它復(fù)制到L23：L25。 結(jié)果如圖所示。12.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 繪制實(shí)際銷(xiāo)售額和預(yù)測(cè)值圖表察看預(yù)測(cè)情況。 選取單元格C1：

26、D25，按住Ctrl鍵并選取單元格KI：K21和LI：L25，單擊“圖表向?qū)А笨旖輬D標(biāo)，打開(kāi)“圖表向?qū)А睂?duì)話(huà)框。 在步驟1中，于“圖表類(lèi)型”列表下選擇“折線(xiàn)圖”，在“子圖表類(lèi)型”下選擇“數(shù)據(jù)點(diǎn)折線(xiàn)圖”，單擊“下一步”按鈕。 輸入數(shù)據(jù)區(qū)域?yàn)椋簊heet長(zhǎng)期趨勢(shì)剔除！$ C1$2:$D$25，sheet長(zhǎng)期趨勢(shì)剔除！$ K$1:$K$21，sheet長(zhǎng)期趨勢(shì)剔除！$ L$1:$L$25，單擊“下一步”按鈕。 在“標(biāo)題”頁(yè)面下輸入“實(shí)際值、季節(jié)調(diào)整TCI及預(yù)測(cè)值比較圖”。單擊“ 完成”按鈕，結(jié)果如圖所示。實(shí)際值、季節(jié)調(diào)整TCI及預(yù)測(cè)值比較圖200250300350400450119933119943

27、119953119963119973119983銷(xiāo)售額YTCI*預(yù)測(cè)值12.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 v 利用啞變量進(jìn)行季節(jié)調(diào)整與預(yù)測(cè) 選擇“季節(jié)變動(dòng)”工作表。 從“工具”菜單中，選擇“數(shù)據(jù)分析”選項(xiàng)，出現(xiàn)“數(shù)據(jù)分析”工具對(duì)話(huà)框。 選中“回歸”選項(xiàng)，單擊“確定”按鈕，出現(xiàn)“回歸”對(duì)話(huà)框。 在“Y值輸入?yún)^(qū)域”、“X值輸入?yún)^(qū)域”、“輸出區(qū)域”中分別輸入D1:D21，E1:E21和K1；選擇標(biāo)志、置信度、殘差、殘差圖、標(biāo)準(zhǔn)殘差和線(xiàn)性擬合圖。單擊“確定”按鈕。12.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 計(jì)算機(jī)將在工作表中輸出回歸分析結(jié)果。下圖為編輯過(guò)的一

28、部分結(jié)果： 回歸分析工具產(chǎn)生的時(shí)序圖中顯示了實(shí)際值和擬合值，修飾后的圖表如圖所示。實(shí) 際 值 與 預(yù) 測(cè) 值 比 較 圖200250300350400450119933119943119953119963119973119983時(shí) 間銷(xiāo)售額Y預(yù) 測(cè) 值銷(xiāo) 售 額季 節(jié) 變 動(dòng)12.5 12.5 季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)的測(cè)定與分析 v 使用TREND函數(shù)計(jì)算多個(gè)季度的擬合值和預(yù)測(cè)值。 把區(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ù)測(cè)值”。 選取區(qū)域J2：J25，按數(shù)組方式輸入公式 “=T