利用Excel進(jìn)行指數(shù)平滑分析（2）

1、利用Excel進(jìn)行指數(shù)平滑分析與預(yù)測(cè)（2）【例】連續(xù)10年的灌溉面積。第一步，錄入數(shù)據(jù)（圖1）。圖1 原始數(shù)據(jù)第二步，選項(xiàng)設(shè)置。沿著主菜單的“工具（T）數(shù)據(jù)分析（D）”路徑打開(kāi)“數(shù)據(jù)分析”選項(xiàng)框，選中“指數(shù)平滑”（圖2）。圖2 數(shù)據(jù)分析選項(xiàng)框與“指數(shù)平滑”的位置確定以后，彈出移動(dòng)平均對(duì)話(huà)框（圖3），然后按如下步驟進(jìn)行設(shè)置： 將光標(biāo)置入“輸入?yún)^(qū)域”對(duì)應(yīng)的空白欄，然后用鼠標(biāo)從B1到B11選中全部時(shí)間序列連同標(biāo)志； 選中“標(biāo)志”（位于第一行）； 在“阻尼系數(shù)”對(duì)應(yīng)的空白欄中鍵入“0.9”，表示指數(shù)平滑系數(shù)為0.1（即取=0.1。注意：指數(shù)平滑系數(shù)與阻尼系數(shù)的關(guān)系是 “平滑系數(shù)阻尼系數(shù)1”）； 將光標(biāo)

2、置入“輸出區(qū)域”對(duì)應(yīng)的空白欄，選中從C2到C11的單元格，作為計(jì)算結(jié)果的輸出位置； 選中“圖表輸出”和“標(biāo)準(zhǔn)誤差”，這樣會(huì)自動(dòng)生成移動(dòng)平均坐標(biāo)圖和標(biāo)準(zhǔn)誤差值。注意：如果“輸入?yún)^(qū)域”對(duì)應(yīng)的空白欄設(shè)置為“$B$2:$B$11”，即不包括數(shù)據(jù)標(biāo)志項(xiàng)，則不要選中“標(biāo)志”（圖4）。圖3 指數(shù)平滑選項(xiàng)框（包括數(shù)據(jù)標(biāo)志）圖3 指數(shù)平滑選項(xiàng)框（不包括數(shù)據(jù)標(biāo)志）第三步，輸出結(jié)果。完成上述設(shè)置以后，確定，即可得到計(jì)算結(jié)果，包括指數(shù)平滑結(jié)果及其標(biāo)準(zhǔn)誤差（圖5），以及指數(shù)平滑曲線圖（圖6）。圖5 移動(dòng)平均結(jié)果：平滑系數(shù)為=0.1圖6 阻尼系數(shù)為0.9（平滑系數(shù)為0.1）的移動(dòng)平均預(yù)測(cè)曲線（水紅色）如果不借助Excel

3、的“數(shù)據(jù)分析”工具，指數(shù)平滑計(jì)算也是非常簡(jiǎn)單的，有關(guān)計(jì)算方法在“利用Excel進(jìn)行指數(shù)平滑分析與預(yù)測(cè)（1）”中已經(jīng)詳細(xì)說(shuō)明，不贅述，下面只介紹標(biāo)準(zhǔn)誤差的計(jì)算方法。與移動(dòng)平均法的標(biāo)準(zhǔn)誤差計(jì)算類(lèi)似，我們有兩種途徑。計(jì)算方法之一：利用平方根命令sqrt和計(jì)算兩個(gè)數(shù)組相對(duì)數(shù)值誤差的平方和命令sumxmy2。在E6單元格中輸入組合命令“=SQRT(SUMXMY2(B3:B5,C3:C5)/3)”，回車(chē)，得到9.921047，然后選中E6單元格，將光標(biāo)置于右下角，十字光標(biāo)變得細(xì)小，按住鼠標(biāo)左鍵，下拉至E11格，便可得到全部標(biāo)準(zhǔn)誤差（圖7）。圖7 標(biāo)準(zhǔn)誤差的計(jì)算結(jié)果計(jì)算方法之二：借助標(biāo)準(zhǔn)誤差公式。在E6單元

4、格中鍵入公式“=(B3-C3)2+(B4-C4)2+(B5-C5)2)/3)0.5”，回車(chē)得到9.921047；下拉，取得全部結(jié)果。標(biāo)準(zhǔn)誤差的含義從計(jì)算公式可以看出。至于指數(shù)平滑曲線，可以利用圖表向?qū)ё孕欣L制：首先，選中從A1到C11的全部數(shù)據(jù)及其移動(dòng)平均結(jié)果（圖8）。圖8 選中的數(shù)據(jù)范圍然后，點(diǎn)擊“圖表向?qū)А眻D標(biāo)，在彈出的“圖表向?qū)А边x項(xiàng)框中選擇“圖表類(lèi)型”中的“散點(diǎn)圖”，在子項(xiàng)即“子圖表類(lèi)型”中選擇“折線散點(diǎn)圖”（圖9）。確定，即可得到移動(dòng)平均的預(yù)測(cè)曲線圖。圖9 圖表類(lèi)型選項(xiàng)：折線散點(diǎn)圖最后，為了與圖6進(jìn)行對(duì)比，可對(duì)曲線圖進(jìn)行簡(jiǎn)單的編輯：打開(kāi)“坐標(biāo)軸格式”選項(xiàng)框，將橫軸刻度改為197119

5、80，將主要刻度改為1，并將字體變小，得到與圖6結(jié)構(gòu)一樣的預(yù)測(cè)曲線圖（圖10）。圖10 借助圖表向?qū)ЮL制的移動(dòng)平均預(yù)測(cè)曲線第四步，重復(fù)移動(dòng)平均計(jì)算。重新打開(kāi)圖3所示的“指數(shù)平滑選項(xiàng)框”，將阻尼系數(shù)改為0.8，對(duì)應(yīng)于平滑系數(shù)=0.2；將輸出區(qū)域改為E2:E11，其他選項(xiàng)不變（圖11）。確定，立即得到結(jié)果。繼續(xù)改變阻尼系數(shù)為0.7、0.6、0.1，直到算出所有的結(jié)果（圖12為局部）。圖11 阻尼系數(shù)為0.8的指數(shù)平滑選項(xiàng)（包括數(shù)據(jù)標(biāo)志）圖12 基于不同阻尼系數(shù)的指數(shù)平滑結(jié)果第五步，檢驗(yàn) 參見(jiàn)“利用Excel進(jìn)行指數(shù)平滑分析與預(yù)測(cè)（1）”，不贅述。當(dāng)=0.3時(shí)，曲線圖與SPSS給出的結(jié)果類(lèi)似，但考慮1971年的數(shù)據(jù)點(diǎn)（圖13）。圖13 =0.3的指數(shù)平滑預(yù)測(cè)曲線（水紅色）第六步，二次指數(shù)平滑首先，在1971年對(duì)應(yīng)的C2中填上28.6（圖14），然后打開(kāi)“指數(shù)平滑”選項(xiàng)框，第一次指數(shù)平滑結(jié)果進(jìn)行指數(shù)平滑，=0.3時(shí)的設(shè)置見(jiàn)圖15。確定，即可得到二次指數(shù)平滑結(jié)果（圖16，圖17）。圖14 =0.3的指數(shù)平滑預(yù)測(cè)曲線（水紅色）圖15 二次指數(shù)平滑的設(shè)置圖16 二次指數(shù)平滑結(jié)果圖17 二次指數(shù)平滑曲線（=0.3）第七步，三次指數(shù)平滑與二次指數(shù)平滑類(lèi)似，首先在1971年對(duì)應(yīng)的D2中填上28.6，然后打開(kāi)“指數(shù)平滑”選項(xiàng)框（圖18），對(duì)第二次指數(shù)平滑結(jié)果進(jìn)行指數(shù)平滑，=0.3時(shí)的三次指數(shù)平滑結(jié)果（