利用Excel进行指数平滑分析和预测（1）

《利用Excel进行指数平滑分析和预测（1）》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、利用Excel进行指数平滑分析与预测（1）【例】以连续10年的灌溉面积为例说明。这个例子并不典型，采用此例仅在说明指数平滑的操作过程。将我的计算过程在Excel上重复一遍，就会掌握指数平滑法的基本要领；然后利用SPSS练习几遍，就能学会实用技巧。第一步，录入数据，设置参数（图1）。录入数据以后，开始设置参数：⒈设置平滑系数：在一个自己感到方便的位置如C2单元格设定一个参数作为指数平滑系数α，由于α介于0～1之间，不妨从0开始，即首先取α=0。⒉设置迭代计算的初始值S0’。初始值有多种取法，一般取S0’=x1，对于本例，自然是取S0’=

2、28.6，写于D2单元格，与1971年对应（图1）。图1原始数据与参数设置第二步，指数平滑计算。按照下式进行显然当t=1时，我们有根据公式在D3单元格中输入公式“=$C$2*B2+(1-$C$2)*D2”（图2），回车，得到28.6；然后用鼠标抓住D3单元格的右下角，下拉（图3），即可得到α=0时的全部数值，其中对应于1981年的数据便是预测值（图4），当然，此时，它们全部都是28.6，即数据被极度修匀。第三步，复制并保存数据。将α=0时的计算结果复制到旁边，其中最后一个数据即1981年的预测值可以不必复制；最好在结果的上面注明对应的

3、平滑系数，以便后来识别（图5）。第四步，计算全部结果。在C2单元格中，将0改为0.1，立即得到α=0.1时的平滑结果，复制并保存（图6）；重复以上操作，直到得到α在0～1之间的全部数值（图7）。第五步，均方差(MSE)检验。10首先计算误差平方和（SSE），公式为注意这里是St-1’对应xt！例如在H2单元格中输入公式“=(B2-G2)^2”，回车，即可得到(x1-y1)2的数值（图8）；下拉，得到1971－1980年间的全部结果：086.49141.6149412.09268.960.3630.25327.6177.44求和，即可得

4、到SSE=1393.8（图9）。根据下式容易算出均方差。根据SSE或MSE最小原则取α=0.3（图11，图12），此时预测值为y11=y(1981)=38.5。图2指数平滑计算示意图图3计算的第一步10图4平滑系数为0时的计算结果图5复制保存的数据图6平滑系数为0.1时的计算结果10图7全部计算结果图8计算误差平方和示意图图9平滑系数为0时的误差平方和（SSE）10图10误差平方和（SSE）和均方差（MSE）图11SSE随平滑系数变化的曲线图12MSE随平滑系数变化的曲线第六步，绘制指数平滑曲线。10将α=0.3时的平滑结果与原数据按

5、顺序排列（图13），然后利用Excel的绘图功能不难绘制指数平滑曲线图——将原始数据曲线与指数平滑曲线画在统一坐标系，便于比较指数平滑的效果：两条曲线越吻合，表明指数平滑的效果越好，从而预测也就越可靠。从图14可以看出，对于本例而言，指数平滑的效果并不见佳（图14）。图13将指数平滑结果与原始数据按顺序排列图14一次指数平滑曲线图（与原始数据比较）第七步，二次指数平滑。二次指数平滑是在一次指数平滑的基础上进行的，其计算过程和检验方法与对原始数据进行指数平滑的步骤完全一样。但是，有一点无需注意：在我们的指数平滑模型中，我们取而yt作为计

6、算值对应的是xt，故xt实际上对应的是St-1’。若以1971年为第t=1个时点，则1972年才对应S1’（1971年对应于S0’）。二次指数平滑的公式为10显然，我们的计算的起点是从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

7、表示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.59

8、2/(10-1)=9.177（图17）。我们知道，当α10＝1时，原始数据实际上未经过任何修匀，但由于计算起点相对于一次指数平滑下移一步，故本例所谓的二次指数平滑实际上是对一次指数平滑的结果进行一次平移，这一点从二次指数