利用Excel進(jìn)行指數(shù)平滑分析報(bào)告與預(yù)測(cè)

1、標(biāo)準(zhǔn)實(shí)用利用 Excel 進(jìn)行指數(shù)平滑分析與預(yù)測(cè)（1 ）【例】以連續(xù)10 年的灌溉面積為例說(shuō)明。這個(gè)例子并不典型，采用此例僅在說(shuō)明指數(shù)平滑的操作過(guò)程。將我的計(jì)算過(guò)程在 Excel 上重復(fù)一遍，就會(huì)掌握指數(shù)平滑法的基本要領(lǐng)；然后利用 SPSS 練習(xí)幾遍，就能學(xué)會(huì)實(shí)用技巧。第一步，錄入數(shù)據(jù)，設(shè)置參數(shù)（圖 1）。錄入數(shù)據(jù)以后，開(kāi)始設(shè)置參數(shù)： 設(shè)置平滑系數(shù)：在一個(gè)自己感到方便的位置如C2 單元格設(shè)定一個(gè)參數(shù)作為指數(shù)平滑系數(shù)，由于 介于 0 1 之間，不妨從0 開(kāi)始，即首先取=0 。 設(shè)置迭代計(jì)算的初始值S0 。初始值有多種取法，一般取S0= x1，對(duì)于本例，自然是取 S0=28.6 ，寫(xiě)于 D2 單元

2、格，與1971 年對(duì)應(yīng)（ 圖 1 ）。圖 1原始數(shù)據(jù)與參數(shù)設(shè)置第二步，指數(shù)平滑計(jì)算。按照下式進(jìn)行Stxt(1) St1顯然當(dāng) t=1時(shí)，我們有S1x1(1)S0y2根據(jù)公式在D3 單元格中輸入公式“=$C$2*B2+(1-$C$2)*D2”（ 圖2 ），回車(chē)，得到28.6 ；然后用鼠標(biāo)抓住D3 單元格的右下角，下拉（圖 3 ），即可得到 =0時(shí)的全部數(shù)值，其中對(duì)應(yīng)于 1981 年的數(shù)據(jù)便是預(yù)測(cè)值（圖 4 ），當(dāng)然，此時(shí)，它們?nèi)慷际?8.6 ，即數(shù)據(jù)被極度修勻。文案大全標(biāo)準(zhǔn)實(shí)用第三步，復(fù)制并保存數(shù)據(jù)。將時(shí)的計(jì)算結(jié)果復(fù)制到旁邊，其中最后一個(gè)數(shù)據(jù)即1981 年的預(yù)測(cè)值可以不必復(fù)制；=0最好在結(jié)果的

3、上面注明對(duì)應(yīng)的平滑系數(shù)，以便后來(lái)識(shí)別（圖 5）。第四步，計(jì)算全部結(jié)果。在 C2 單元格中，將0 改為 0.1 ，立即得到 =0.1時(shí)的平滑結(jié)果，復(fù)制并保存（圖 6 ）；重復(fù)以上操作，直到得到在0 1 之間的全部數(shù)值（圖 7）。第五步，均方差 (MSE) 檢驗(yàn)。首先計(jì)算誤差平方和（ SSE），公式為nnSSE( yt xt ) 2(St 1 xt ) 2t 1t 2注意這里是 St -1 對(duì)應(yīng) xt！例如在H2 單元格中輸入公式“ =(B2-G2)2 ”，回車(chē)，即可得到(x1 - y1) 2 的數(shù)值（ 圖 8 ）；下拉，得到1971 1980年間的全部結(jié)果：086.49141.6149412.0

4、9268.960.3630.25327.6177.44求和，即可得到 SSE=1393.8（圖 9）。根據(jù)下式SSE1nxt ) 2MSE( St 1n - 1n 1 t 2容易算出均方差。根據(jù)SSE 或 MSE最小原則取 =0.3 （圖11 ，圖12 ），此時(shí)預(yù)測(cè)值為y11 = y(1981)= 38.5 。圖 2 指數(shù)平滑計(jì)算示意圖文案大全標(biāo)準(zhǔn)實(shí)用圖 3 計(jì)算的第一步圖 4 平滑系數(shù)為0 時(shí)的計(jì)算結(jié)果文案大全標(biāo)準(zhǔn)實(shí)用圖 5 復(fù)制保存的數(shù)據(jù)圖 6 平滑系數(shù)為0.1 時(shí)的計(jì)算結(jié)果圖 7 全部計(jì)算結(jié)果圖 8 計(jì)算誤差平方和示意圖文案大全標(biāo)準(zhǔn)實(shí)用圖 9 平滑系數(shù)為0 時(shí)的誤差平方和（SSE）圖 1

5、0 誤差平方和（SSE）和均方差（MSE ）SSE1500140013001200SSE1100100090080000.20.40.60.81圖 11 SSE 隨平滑系數(shù)變化的曲線文案大全標(biāo)準(zhǔn)實(shí)用MSE160150140130MSE12011010000.20.40.60.81圖 12 MSE隨平滑系數(shù)變化的曲線第六步，繪制指數(shù)平滑曲線。將=0.3時(shí)的平滑結(jié)果與原數(shù)據(jù)按順序排列（圖 13 ），然后利用Excel 的繪圖功能不難繪制指數(shù)平滑曲線圖將原始數(shù)據(jù)曲線與指數(shù)平滑曲線畫(huà)在統(tǒng)一坐標(biāo)系，便于比較指數(shù)平滑的效果：兩條曲線越吻合，表明指數(shù)平滑的效果越好，從而預(yù)測(cè)也就越可靠。從圖14可以看出，對(duì)于

6、本例而言，指數(shù)平滑的效果并不見(jiàn)佳（圖 14）。圖 13 將指數(shù)平滑結(jié)果與原始數(shù)據(jù)按順序排列文案大全標(biāo)準(zhǔn)實(shí)用5550454035灌溉面積 ( 千畝)30一次平滑2520151019711973197519771979圖 14一次指數(shù)平滑曲線圖（與原始數(shù)據(jù)比較）第七步，二次指數(shù)平滑。二次指數(shù)平滑是在一次指數(shù)平滑的基礎(chǔ)上進(jìn)行的，其計(jì)算過(guò)程和檢驗(yàn)方法與對(duì)原始數(shù)據(jù)進(jìn)行指數(shù)平滑的步驟完全一樣。但是，有一點(diǎn)無(wú)需注意：在我們的指數(shù)平滑模型中，我們?nèi)t 1St而 y t 作為計(jì)算值對(duì)應(yīng)的是xt ，故 xt 實(shí)際上對(duì)應(yīng)的是St -1 。若以 1971年為第 t=1 個(gè)時(shí)點(diǎn)，則 1972 年才對(duì)應(yīng) S1 （ 19

7、71 年對(duì)應(yīng)于 S0）。二次指數(shù)平滑的公式為StSt(1) St 1顯然，我們的計(jì)算的起點(diǎn)是從S1開(kāi)始的，亦即從1972年開(kāi)始的，否則會(huì)有t-1=-1的現(xiàn)象，而我們的時(shí)間序號(hào)不取負(fù)值。根據(jù)習(xí)慣方法，取 S0 = S1 = x1 =28.6，平滑系數(shù)不妨仍然從 0開(kāi)始，以 C3單元格表示新的平滑系數(shù) 0 所在，在 E3 單元格輸入 28.6 表示 S0 ，在E4 單元格中建立公式“= $C$3*D3+ (1-$C$3)*E3”（參見(jiàn) 圖 15 。注意這里是D3 表示 S1，E3 表示 S0 ，后面的三次指數(shù)平滑要考慮這個(gè)問(wèn)題），回車(chē)，得到S1=28.6 ；下拉至 1982年，給出 0 時(shí)全部的二

8、次平滑結(jié)果；1971 年對(duì)應(yīng)的年份空著，取28.6 。復(fù)制，保存（可以只保存1971 1980 年間的結(jié)果）。文案大全標(biāo)準(zhǔn)實(shí)用圖 15 二次指數(shù)平滑的初始值的計(jì)算（ 0 ）圖 16 平滑系數(shù) 0 時(shí)的全部二次指數(shù)平滑結(jié)果在 C3 單元格中改變 值，以 0.1 為步長(zhǎng)，分別取 0.1 、 0.2 、 、 1 ，給出、復(fù)制、保存基于不同平滑系數(shù)的計(jì)算結(jié)果，最后比較發(fā)現(xiàn)，當(dāng) 1 時(shí)，誤差平方和SSE 從均方差 MSE 最小，此時(shí)SSE=82.592 ，MSE=82.592/(10-1)=9.177（圖 17 ）。我們知道，當(dāng) 1 時(shí)，原始數(shù)據(jù)實(shí)際上未經(jīng)過(guò)任何修勻，但由于計(jì)算起點(diǎn)相對(duì)于一次指數(shù)平滑下移

9、一步，故本例所謂的二次指數(shù)平滑實(shí)際上是對(duì)一次指數(shù)平滑的結(jié)果進(jìn)行一次平移，這一點(diǎn)從二次指數(shù)平滑坐標(biāo)圖可以看出。根據(jù)圖 17 中的數(shù)據(jù)容易畫(huà)出兩次指數(shù)平滑曲線，二次指數(shù)平滑曲線（藍(lán)色）實(shí)際上是對(duì)一次指數(shù)平滑曲線（綠色）的右向平移（圖 18 ）。因此，對(duì)于本例而言，二次指數(shù)平滑沒(méi)有必要，當(dāng)然三次指數(shù)平滑更是多余。不過(guò)，為了說(shuō)明三次指數(shù)平滑的方法，我們還是計(jì)算三次指數(shù)平滑的結(jié)果。文案大全標(biāo)準(zhǔn)實(shí)用圖 17二次平滑系數(shù) 1 時(shí)的計(jì)算結(jié)果及其誤差、誤差平方和5550454035灌溉面積 ( 千畝)一次平滑30二次平滑2520151019711973197519771979圖 18 兩次指數(shù)平滑曲線圖在理論上

10、，我們可以建立指數(shù)平滑預(yù)測(cè)的線性模型yt TatbtT式中at2StStbt( StSt )1文案大全標(biāo)準(zhǔn)實(shí)用運(yùn)用模型時(shí)原則上要求MSE最小條件下的兩次指數(shù)平滑系數(shù)沒(méi)有差別，但是，對(duì)于本例，二次指數(shù)平滑系數(shù)不僅與第一次不同，而且不能代入上面的公式，因?yàn)?1時(shí)，必有b t= ，從而模型無(wú)意義。未來(lái)說(shuō)明基于指數(shù)平滑結(jié)果的線性模型創(chuàng)建方法，姑取 0.3 ，即假定 SSE最小時(shí)的平滑系數(shù)沒(méi)有變化。在 F2 單元格中輸入公式“ =2*D2-E2”，回車(chē)得 a1=28.6 ，下拉至 1981 年，得到全部的at ；在 G2 單元格中，輸入公式“=0.3/(1-0.3)*(D2-E2)”，回車(chē)得b1 =0

11、，下拉至 1982年，得到全部的b t （圖 19 ）。取T=1 ，根據(jù)模型yt = at+ b tT，在 H3單元格中輸入公式“=F2+G2*1 ”，得到 y 2=28.6 ，下拉至1982 年，得到全部的預(yù)測(cè)值。顯然，根據(jù)1980 年對(duì)應(yīng)的參數(shù) a10 =42.281 ， b10 =1.421 ，可以建立線性預(yù)測(cè)模型y10 T42.2811.421T根據(jù) 1981 年對(duì)應(yīng)的參數(shù)可以建立預(yù)測(cè)模型y11 T38.0270.201T利用誤差序列Err 計(jì)算自相關(guān)系數(shù)進(jìn)行檢驗(yàn)只要自相關(guān)系數(shù)的絕對(duì)值不大于1.96/ n ，就認(rèn)為模型可以接受。從預(yù)測(cè)曲線與原始曲線的關(guān)系可以看出，預(yù)測(cè)效果不太理想（圖

12、20）。圖 19 基于兩次指數(shù)平滑結(jié)果的線性模型參數(shù)及其預(yù)測(cè)結(jié)果文案大全標(biāo)準(zhǔn)實(shí)用50454035灌溉面積 ( 千畝)30yt2520151970197519801985圖 20線性預(yù)測(cè)曲線與原始數(shù)據(jù)曲線的關(guān)系（二者并不吻合）第八步，三次指數(shù)平滑。三次指數(shù)平滑與二次指數(shù)平滑一樣，是在平滑的結(jié)果上進(jìn)行的，起點(diǎn)則是從1973年開(kāi)始，原因與二次指數(shù)平滑從1972 年開(kāi)始一樣。取0= 1=28.6 ，平滑系數(shù)仍然從S S0 開(kāi)始，以 C4 單元格表示新的平滑系數(shù) 0 所在，在 F4單元格輸入28.6 表示 0，S 在 F5 單元格中建立公式“ = $C$4*E4+ (1-$C$4)*F4 ”，回車(chē)，得 S1 = 28.6 ，下拉至1983 年，得到全部的三次平滑結(jié)果；在1971 、 1972 年對(duì)應(yīng) F 單元格中取28.6 、 28.6 。改變值，可得不同平滑系數(shù)條件下的三次平滑結(jié)果（圖 21 ）。正如所料，當(dāng) 1 ，誤差平方和最小，此時(shí)SSE=71.606 ，MSE=7.956（圖