利用Excel进行指数平滑分析与预测

#利用Excel进行指数平滑分析与预测（1）【例】以连续10年的灌溉面积为例说明。这个例子并不典型，采用此例仅在说明指数平滑的操作过程。将我的计算过程在Excel上重复一遍，就会掌握指数平滑法的基本要领；然后利用SPSS练习几遍，就能学会实用技巧。rR第一步，录入数据，设置参数（图1）。rR录入数据以后，开始设置参数：1.设置平滑系数：在一个自己感到方便的位置如C2单元格设定一个参数作为指数平滑系数弘由于a介于0〜1之间，不妨从0开始，即首先取a=0。2•设置迭代计算的初始值S。'。初始值有多种取法，一般取S0'=X],对于本例，自然是取SJ=28.6，写于D2单元格，与1971年对应（图1）。ABIZc1年伪灌溉面积（千亩）平滑系数一次平滑2197128.6028.63197219.34197340.55197435.66197548.971976458197729.2g197834.110197946.711198037.4121981图1原始数据与参数设置第二步，指数平滑计算。按照下式进行显然当t=1时，我们有Ss—+（1—a）Ss—y1102根据公式在D3单元格中输入公式“=$C$2*B2+（1-$C$2）*D2”（图2）,回车，得到28.6；然后用鼠标抓住D3单元格的右下角，下拉（图3）,即可得到a=0时的全部数值，其中对应于1981年的数据便是预测值（图4）,当然，此时，它们全部都是28.6，即数据被极度修匀。第三步，复制并保存数据。将a=0时的计算结果复制到旁边，其中最后一个数据即1981年的预测值可以不必复制；最好在结果的上面注明对应的平滑系数，以便后来识别（图5）。第四步，计算全部结果。在C2单元格中，将0改为0.1，立即得到a=0.1时的平滑结果，复制并保存（图6）；重复以上操作，直到得到a在0〜1之间的全部数值（图7）。第五步，均方差（MSE）检验。

首先计算误差平方和（SSE），公式为SSE二工（y-x）2二工（Sr-x）2TOC\o"1-5"\h\zttt-1tt=1t=2注意这里是SJ对应xt!例如在H2单元格中输入公式“=（B2-G2）人2”，回车，即可得到（x1-y1）2的数值（图8）；下拉，得到1971—1980年间的全部结果：086.49141.6149412.09268.960.3630.25327.6177.44求和，即可得到SSE=1393.8（图9）。根据下式MSE=注=-^E（S'-x）2n-1n一1t-1t容易算出均方差。根据SSE或MSE最小原则取«=0.3（图11，图12），此时预测值为=38.5SUN-ITxj==$C$2松姑〔1-$C$刃未D2ABC|D|E|F1年份權濟面积鬥二田）平滑系数一次平滑2197128.6028.6319T219.3二就$2吒2十〔1-就$2）忙24197340.5519T435・6619T548.97197S45S19T729.2g197S34.11019T946.711198037.4121981图2指数平滑计算示意图D3==$C$2^B2+(1-K$2)^D2QA|B匚cD1CE1年臂灌漑面积〔千田）平滑系数一次平滑219712S.602S.63197219.323.6]4197340.55197435.6619754&勺7197645S197729.29197S34.110197946.711198037.4121981图3计算的第一步

A|B匚C匚D1年份灌溉面积（千田）平滑系数一次平滑2197128.6028.63197219.328.64197340.52S.65197435.6瓯66197543.923.6719764528.63197T29.228.69197834.128.610197946.723.611198037.428.61219S128.6图4平滑系数为0时的计算结果ABCD|EF|G1年份灌溉面积〔千田）平滑系数一次平滑参数0219712S.6028.6197128.63197219.328.6197228.64197340.528.6197328.65197435.S28.6197428.66197548.928.6197528.6719764528.6197628.68197729.228.6197728.6g197834.128.6197828.610197946.72S.6197928.61119S037.428.6198028.612193128.61931图5复制保存的数据AECDEFGH1年份灌溉面积（千亩）平滑系数一次平滑参数00.12197128.E0.128.6197128.628.63197219.-28.6197228.628.604197340.E27.7197328.627.675197435.E29.0197428.628.9561975阳.61975细6Z9.52719764F31.5197628.631.558197729.232.9197728.632.899197834.132.5197828.632.5210197946.732.7197928.632.6811198037.434.1198028.634.0812198134.4|11981图6平滑系数为0.1时的计算结果

FGHIJKLV.N0PQ100.10.工□.；£U.勺U.hu.tjU.7U.M().912-ir2：.|；2：-：.|•：：：.-•■：：■■-■■•■：：■■■■：-.6:■:-f；2：.u3197Z28,628.6028.6023.6028-6028.6D能60Z8.60Z8.60Z8.6023.6419732S.627.6725-£124.SS23.9523.0222-0921.1620.2319.S-hd2：.|：:-.<；!：;；：|.：2:3::-1J-l.I-:36.63<"6197E28.621623D.7131.8332.■3233.9134-7635-4135.SIS5.8935-6T197628-63L55沁亦36.9539-3141.di43.2544.E546.2847.60眦§S::■■.■■!：■:9.37■-!-<i.■1<<.1■l-l.I:id!：.：6■1!.:E■1!：g:1卍:…叩-<：-2<r-\..1■;h.V-.K:■：-.:-i.'：■.左i.：，10-1疋2：.|；:：■■■：■■■-:2:八,:「1.-討.Z::::..6:::■i"S<-'11:计订.7：-\-：741i'T41-2442.9044.1：45.41图7全部计算结果SUM==(B2-G2)"2ABCDEFGH1年份灌溉面积（千亩）平滑系数一次平滑参数0SSE02197128.E028.6197128.6辽-G2厂23197219.;28.6197228.64197340.E28.6197328.651A74祚一F1A74X-A6197548.£28.6197528.6719764E28.6197628.68197729.228.6197728.69197834.128.6197828.610197946.728.6197928.611198037.匚28.6198028.612198128.1981图8计算误差平方和示意图ABCDIEFIGIH1年份灌溉面积（二亩）平滑系数一次平滑参数0SSEO2197128.6028.619712E.60.03197219.328.619722E.686.54197340.528.619732E.6141.65197435.628.619742E.649.06197548.928.619752E.6412.1719764528.619762E.6269.08197729.228.619772E.60.49197834.128.619782E.630.310197946.728.619792E.6327.611193037.428.619802E.677.412193128.吐19811393.8图9平滑系数为0时的误差平方和（SSE）

RSTUVwXY2aaABAC1迹皿MSJilMSE2M沁A1KL4MSE5△ISMMSE7M£E8MSL1Q2L97100n000000003L97236.^19B6.3986.-49S6,49阳川9阪49S6,曲S&.4986.-y9防⑷q11'-';b-l.:H1IW.::-!■：L.■-:■::":.JI：.i!.!■:::■:-I:II...^751974站0QW.1837.3120.98EO.0011.39Q・3S0.391.078.25羽.oil61975412.09371.81330.T5291,32255.46224.63199,85181.901"•舸169.33176.89T1976268.9&181.01113.40轨7632.3B12.923.080.021忌6.7515.21S1:L13.63:.i--C153.^1im；227,95s-11.?>■?r;.-r■7V-.■■:249,6491屮■::...:■■■■.J-:in.:.-l■.<：■1.：..1-I::.■■!1...八:-.l110197932T.61196.5614667122-07122.79133.30147-48160.07167.3916T.17158.761119007T.4411.01CLOd2.45T.0312.4319.7330.30d5.0664.1186.我12SSE1393.811071=T9951.85920.14927.S5955.&4995.SI1046.38HOT.771131,751270.的1-1©E154.87119.091Q5.-6102.2103JI106.LB110jSS11&.26123.UJ?131.31141J1图10误差平方和（SSE）和均方差（MSE）SSE*SSE1500*SSE1400130012001100100090080000.20.40.60.81图11SSE随平滑系数变化的曲线MSE图12MSE随平滑系数变化的曲线第六步，绘制指数平滑曲线。

灌溉面积（千亩）一次平滑将*0.3时的平滑结果与原数据按顺序排列（图13），然后利用Excel的绘图功能不难绘制指数平滑曲线图一一将原始数据曲线与指数平滑曲线画在统一坐标系，便于比较指数平滑的效果：两条曲线越吻合，表明指数平滑的效果越好，从而预测也就越可靠。从图14可以看出，对于本例而言，指数平滑的效果并不见佳（图14）。灌溉面积（千亩）一次平滑ABCD1年偕權溉面积（千田）平滑系数一次平滑2197128.60.328.6003197219.328-6004197340.525.8105197435・630.2176197548.931.832719764536.9528197729.239.367g197S34.136.31710197946.735.6521119S037.438.96612198138.496图13将指数平滑结果与原始数据按顺序排列图14一次指数平滑曲线图（与原始数据比较）第七步，二次指数平滑。二次指数平滑是在一次指数平滑的基础上进行的，其计算过程和检验方法与对原始数据进行指数平滑的步骤完全一样。但是，有一点无需注意：在我们的指数平滑模型中，我们取y二S，t+1t而yt作为计算值对应的是xt，故xt实际上对应的是St1J。若以1971年为第t=1个时点，则1972年才对应SJ（1971年对应于SJ）。二次指数平滑的公式为

S〃=aS，+（1—a）S〃ttt—1显然，我们的计算的起点是从S1W始的，亦即从1972年开始的，否则会有t-1=-1的现象，而我们的时间序号不取负值。根据习惯方法，取S0>-S1>=x1=28.6，平滑系数不妨仍然从0开始，以C3单元格表示新的平滑系数«=0所在，在E3单元格输入28.6表示SJ,在E4单元格中建立公式“=$C$3*D3+（1-$C$3）*E3”（参见图15。注意这里是D3表示SJ,E3表示SJ,后面的三次指数平滑要考虑这个问题），回车，得到S「=28.6；下拉至1982年，给出a=0时全部的二次平滑结果；1971年对应的年份空着，取28.6。复制，保存（可以只保存1971-1980年间的结果）。E4-==$Ci3+D3+(l-$C$3)^E3ABCDE1年份灌溉面积（千田）平滑系数一次平滑二次平滑219712S.60.328.6003197219.3028-60028.6004197340.525.81028.600S197435.630.217619754&勺31.832719764536.952S19772^.239.367g197S34.136.31710197946.735.6521119S037.43S.96612198138.496图15二次指数平滑的初始值的计算（a=0）ABCDEFGH1年份灌漑面积（千亩）平滑系数一次平滑二次平滑参数02197128.60.328.60028.600197128.6003197219.3028.60028.600197228.6004197340.525.81028.600197328.6005197435.630.21728.600197428.6006197548.931.83228.600197528.600719764536.95228.600197628.6008197729.239.36728.600197728.6009197834.136.31728.600197828.60010197946.735.65228.600197928.60011198037.438.96628.600198028.60012198138.49628.600198128.60013198228.60028.600图16平滑系数a=0时的全部二次指数平滑结果在C3单元格中改变a值，以0.1为步长，分别取a=0.1、a=0.2、…、a=1,给出、复制、保存基于不同平滑系数的计算结果，最后比较发现，当a=1时，误差平方和SSE从均方差MSE最小，此时SSE=82.592,MSE=82.592/（10-1）=9.177（图17）。我们知道，当a=1时，原始数据实际上未经过任何修匀，但由于计算起点相对于一次指数平滑下移一步，故本例所谓的二次指数平滑实际上是对一次指数平滑的结果进行一次平移，

这一点从二次指数平滑坐标图可以看出。根据图17中的数据容易画出两次指数平滑曲线，二次指数平滑曲线（蓝色）实际上是对一次指数平滑曲线（绿色）的右向平移（图18）。因此，对于本例而言，二次指数平滑没有必要，当然三次指数平滑更是多余。不过，为了说明三次指数平滑的方法，我们还是计算三次指数平滑的结果。ABCDE|F|G1年份灌澱面积（千亩）平滑系数一次平滑二次平滑EitSSE2197128.60.328.60028.6000.0000.0003197219.3128.60028.6000.0000.0004197340.525.81028.600-2.7907.7845197435.630.21725.8104.40719.422619754S.931.33230.2171.6152.6087197S4536.95231.8325.12026.2198197729.239.36736.9522.4145.8299197834.136.31739.367-3.0509.30210197946.735.65236.317-0.6650.44211198037.438.96635.6523.31510.9861219S138.496弦紘82.5921319823&496图17二次平滑系数尸1时的计算结果及其误差、误差平方和灌溉面积（千亩）一一次平滑灌溉面积（千亩）一一次平滑二次平滑50454035302520151019711973197519771979图18两次指数平滑曲线图在理论上，我们可以建立指数平滑预测的线性模型y=a+b-Tt+Ttt式中a二2S'—S〃tttn

b二——（S'—S〃）

t1—att运用模型时原则上要求MSE最小条件下的两次指数平滑系数没有差别，但是，对于本例，二次指数平滑系数不仅与第一次不同，而且不能代入上面的公式，因为«=1时，必有b尸从而模型无意义。未来说明基于指数平滑结果的线性模型创建方法，姑取«=0.3，即假定SSE最小时的平滑系数没有变化。在F2单元格中输入公式“=2*D2-E2”，回车得a1=28.6，下拉至1981年，得到全部的at；在G2单元格中，输入公式“=[0.3/（1-0.3）]*（D2-E2）”，回车得b=0，下拉至1982年，得到全部的bt（图19）。取T=1，根据模型y=a+bT，在H3单元格中输入公式“=F2+G2*1”，得到y2=28.6，下拉至1982年，得到全部的预测值。显然，根据1980年对应的参数a10=42.281，b10=1.421，可以建立线性预测模型y二42.281+1.421T10+T根据1981年对应的参数可以建立预测模型y=38.027—0.201T11+T利用误差序列Err计算自相关系数进行检验一一只要自相关系数的绝对值不大于1.96/Vn，就认为模型可以接受。从预测曲线与原始曲线的关系可以看出，预测效果不太理想（图20）。ABCDEFGHI1年苗灌溉面积平滑系数一次平滑二次平滑btYiErr2197128.60.328.628.603197219.3128.628.6貂.&028.6-E.34197c40.525.8128.623.C2-1.1957128.611.95137C花.630.2172E.S134.6241.88871421.8242913.7757161^75c1.询9暫一21733.446S0.692112.327237197E4536.9523331.831942.072762.1944734.138910.8H18197729.2呢3566336.9523341.780931.034744.26723-15.06729197E34.136.3166439.366633126665-1.3071442.81563-8.7156310197946.735.6516536.31664別.9S666-j.28531.9595114.740491113SC37.438.9661535.6516542.28071.420534.7D1CC2.69834121AR1节一4柿13S.9661538.0265-0.201443.701213198238.4963137.8251图19基于两次指数平滑结果的线性模型参数及其预测结果——灌溉面积（千亩）■~yt图20线性预测曲线与原始数据曲线的关系（二者并不吻合）

第八步，三次指数平滑。三次指数平滑与二次指数平滑一样，是在平滑的结果上进行的，