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

1利用Excel进行指数平滑分析与预测（1）【例】以连续10年的灌溉面积为例说明。这个例子并不典型，采用此例仅在说明指数平滑的操作过程。将我的计算过程在Excel上重复一遍，就会掌握指数平滑法的基本要领；然后利用SPSS练习几遍，就能学会实用技巧。第一步，录入数据，设置参数（图1）。录入数据以后，开始设置参数：⒈设置平滑系数：在一个自己感到方便的位置如C2单元格设定一个参数作为指数平滑系数α，由于α介于0～1之间，不妨从0开始，即首先取α=0。⒉设置迭代计算的初始值S0’。初始值有多种取法，一般取S0’=x1，对于本例，自然是取S0’=28.6，写于D2单元格，与1971年对应（图1）。图1原始数据与参数设置第二步，指数平滑计算。按照下式进行1)1(tttSxS显然当t=1时，我们有2011)1(ySxS根据公式在D3单元格中输入公式“=$C$2*B2+(1-$C$2)*D2”（图2），回车，得到28.6；然后用鼠标抓住D3单元格的右下角，下拉（图3），即可得到α=0时的全部数值，其中对应于1981年的数据便是预测值（图4），当然，此时，它们全部都是28.6，即数据被极度修匀。第三步，复制并保存数据。将α=0时的计算结果复制到旁边，其中最后一个数据即1981年的预测值可以不必复制；最好在结果的上面注明对应的平滑系数，以便后来识别（图5）。第四步，计算全部结果。在C2单元格中，将0改为0.1，立即得到α=0.1时的平滑结果，复制并保存（图6）；重复以上操作，直到得到α在0～1之间的全部数值（图7）。第五步，均方差(MSE)检验。2首先计算误差平方和（SSE），公式为ntttntttxSxy22112)()(SSE注意这里是St-1’对应xt！例如在H2单元格中输入公式“=(B2-G2)^2”，回车，即可得到(x1-y1)2的数值（图8）；下拉，得到1971－1980年间的全部结果：086.49141.6149412.09268.960.3630.25327.6177.44求和，即可得到SSE=1393.8（图9）。根据下式ntttxSnn221)(111-SSEMSE容易算出均方差。根据SSE或MSE最小原则取α=0.3（图11，图12），此时预测值为y11=y(1981)=38.5。图2指数平滑计算示意图图3计算的第一步3图4平滑系数为0时的计算结果图5复制保存的数据图6平滑系数为0.1时的计算结果4图7全部计算结果图8计算误差平方和示意图图9平滑系数为0时的误差平方和（SSE）5图10误差平方和（SSE）和均方差（MSE）SSE80090010001100120013001400150000.20.40.60.81SSE图11SSE随平滑系数变化的曲线MSE10011012013014015016000.20.40.60.81MSE图12MSE随平滑系数变化的曲线第六步，绘制指数平滑曲线。6将α=0.3时的平滑结果与原数据按顺序排列（图13），然后利用Excel的绘图功能不难绘制指数平滑曲线图——将原始数据曲线与指数平滑曲线画在统一坐标系，便于比较指数平滑的效果：两条曲线越吻合，表明指数平滑的效果越好，从而预测也就越可靠。从图14可以看出，对于本例而言，指数平滑的效果并不见佳（图14）。图13将指数平滑结果与原始数据按顺序排列1015202530354045505519711973197519771979灌溉面积(千亩)一次平滑图14一次指数平滑曲线图（与原始数据比较）第七步，二次指数平滑。二次指数平滑是在一次指数平滑的基础上进行的，其计算过程和检验方法与对原始数据进行指数平滑的步骤完全一样。但是，有一点无需注意：在我们的指数平滑模型中，我们取ttSy1而yt作为计算值对应的是xt，故xt实际上对应的是St-1’。若以1971年为第t=1个时点，则1972年才对应S1’（1971年对应于S0’）。二次指数平滑的公式为71)1(tttSSS显然，我们的计算的起点是从S1’开始的，亦即从1972年开始的，否则会有t-1=-1的现象，而我们的时间序号不取负值。根据习惯方法，取S0’’=S1’=x1=28.6，平滑系数不妨仍然从0开始，以C3单元格表示新的平滑系数α＝0所在，在E3单元格输入28.6表示S0’’，在E4单元格中建立公式“=$C$3*D3+(1-$C$3)*E3”（参见图15。注意这里是D3表示S1’，E3表示S0’’，后面的三次指数平滑要考虑这个问题），回车，得到S1’’=28.6；下拉至1982年，给出α＝0时全部的二次平滑结果；1971年对应的年份空着，取28.6。复制，保存（可以只保存1971－1980年间的结果）。图15二次指数平滑的初始值的计算（α＝0）图16平滑系数α＝0时的全部二次指数平滑结果在C3单元格中改变α值，以0.1为步长，分别取α＝0.1、α＝0.2、…、α＝1，给出、复制、保存基于不同平滑系数的计算结果，最后比较发现，当α＝1时，误差平方和SSE从均方差MSE最小，此时SSE=82.592，MSE=82.592/(10-1)=9.177（图17）。我们知道，当α＝1时，原始数据实际上未经过任何修匀，但由于计算起点相对于一次指数平滑下移一步，故本例所谓的二次指数平滑实际上是对一次指数平滑的结果进行一次平移，8这一点从二次指数平滑坐标图可以看出。根据图17中的数据容易画出两次指数平滑曲线，二次指数平滑曲线（蓝色）实际上是对一次指数平滑曲线（绿色）的右向平移（图18）。因此，对于本例而言，二次指数平滑没有必要，当然三次指数平滑更是多余。不过，为了说明三次指数平滑的方法，我们还是计算三次指数平滑的结果。图17二次平滑系数α＝1时的计算结果及其误差、误差平方和1015202530354045505519711973197519771979灌溉面积(千亩)一次平滑二次平滑图18两次指数平滑曲线图在理论上，我们可以建立指数平滑预测的线性模型TbayttTt式中9)(12ttttttSSbSSa运用模型时原则上要求MSE最小条件下的两次指数平滑系数没有差别，但是，对于本例，二次指数平滑系数不仅与第一次不同，而且不能代入上面的公式，因为α＝1时，必有bt=∞，从而模型无意义。未来说明基于指数平滑结果的线性模型创建方法，姑取α＝0.3，即假定SSE最小时的平滑系数没有变化。在F2单元格中输入公式“=2*D2-E2”，回车得a1=28.6，下拉至1981年，得到全部的at；在G2单元格中，输入公式“=[0.3/(1-0.3)]*(D2-E2)”，回车得b1=0，下拉至1982年，得到全部的bt（图19）。取T=1，根据模型yt=at+btT，在H3单元格中输入公式“=F2+G2*1”，得到y2=28.6，下拉至1982年，得到全部的预测值。显然，根据1980年对应的参数a10=42.281，b10=1.421，可以建立线性预测模型TyT421.1281.4210根据1981年对应的参数可以建立预测模型TyT201.0027.3811利用误差序列Err计算自相关系数进行检验——只要自相关系数的绝对值不大于1.96/√n，就认为模型可以接受。从预测曲线与原始曲线的关系可以看出，预测效果不太理想（图20）。图19基于两次指数平滑结果的线性模型参数及其预测结果15202530354045501970197519801985灌溉面积(千亩)yt图20线性预测曲线与原始数据曲线的关系（二者并不吻合）10第八步，三次指数平滑。三次指数平滑与二次指数平滑一样，是在平滑的结果上进行的，起点则是从1973年开始，原因与二次指数平滑从1972年开始一样。取S0’’’=S1’’=28.6，平滑系数仍然从0开始，以C4单元格表示新的平滑系数α＝0所在，在F4单元格输入28.6表示S0’’’，在F5单元格中建立公式“=$C$4*E4+(1-$C$4)*F4”，回车，得S1’’’=28.6，下拉至1983年，得到全部的三次平滑结果；在1971、1972年对应F单元格中取28.6、28.6。改变α值，可得不同平滑系数条件下的三次平滑结果（图21）。正如所料，当α＝1，误差平方和最小，此时SSE=71.606，MSE=7.956（图22）。根据图22中的数据，可以画出三次指数平滑曲线图（图23）。进而可以建立二次抛物线性预测模型，由于对于本例而言抛物线模型没有实质意义，在此从略。图23三次平滑系数α＝1时的计算结果及其误差、误差平方和1015202530354045505519711973197519771979灌溉面积(千亩)一次平滑二次平滑三次平滑图24三次指数平滑曲线图