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

PAGE PAGE 1 利用Excel进行指数平滑分析与预测（1） 【例】以连续10年的灌溉面积为例说明。这个例子并不典型，采用此例仅在说明指数平滑的操作过程。将我的计算过程在Excel上重复一遍，就会掌握指数平滑法的基本要领；然后利用SPSS练习几遍，就能学会实用技巧。 第一步，录入数据，设置参数（图1）。 录入数据以后，开始设置参数： = 1 \* GB1 ⒈ 设置平滑系数：在一个自己感到方便的位置如C2单元格设定一个参数作为指数平滑系数α，由于α介于0～1之间，不妨从0开始，即首先取α=0。 = 2 \* GB1 ⒉ 设置迭代计算的初始值S0’。初始值有多种取法，一般取S0’=x1，对于本例，自然是取S0’=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年的预测值可以不必复制；最好在结果的上面注明对应的平滑系数，以便后来识别（图5）。 第四步，计算全部结果。 在C2单元格中，将0改为0.1，立即得到α=0.1时的平滑结果，复制并保存（图6）；重复以上操作，直到得到α在0～1之间的全部数值（图7）。 第五步，均方差(MSE)检验。 首先计算误差平方和（SSE），公式为 注意这里是St-1’对应xt！例如在H2单元格中输入公式“=(B2-G2)^2”，回车，即可得到(x1-y1)2的数值（图8）；下拉，得到1971－1980年间的全部结果： 0 86.49 141.61 49 412.09 268.96 0.36 30.25 327.61 77.44 求和，即可得到SSE=1393.8（图9）。 根据下式 容易算出均方差。根据SSE或MSE最小原则取α=0.3（图11，图12），此时预测值为y11=y(1981)=38.5。 图2 指数平滑计算示意图 图3 计算的第一步 图4 平滑系数为0时的计算结果 图5 复制保存的数据 图6 平滑系数为0.1时的计算结果 图7 全部计算结果 图8 计算误差平方和示意图 图9 平滑系数为0时的误差平方和（SSE） 图10 误差平方和（SSE）和均方差（MSE） 图11 SSE随平滑系数变化的曲线 图12 MSE随平滑系数变化的曲线 第六步，绘制指数平滑曲线。 将α=0.3时的平滑结果与原数据按顺序排列（图13），然后利用Excel的绘图功能不难绘制指数平滑曲线图——将原始数据曲线与指数平滑曲线画在统一坐标系，便于比较指数平滑的效果：两条曲线越吻合，表明指数平滑的效果越好，从而预测也就越可靠。从图14可以看出，对于本例而言，指数平滑的效果并不见佳（图14）。 图13 将指数平滑结果与原始数据按顺序排列 图14 一次指数平滑曲线图（与原始数据比较） 第七步，二次指数平滑。 二次指数平滑是在一次指数平滑的基础上进行的，其计算过程和检验方法与对原始数据进行指数平滑的步骤完全一样。但是，有一点无需注意：在我们的指数平滑模型中，我们取 而yt作为计算值对应的是xt，故xt实际上对应的是St-1’。若以1971年为第t=1个时点，则1972年才对应S1’（1971年对应于S0’）。二次指数平滑的公式为 显然，我们的计算的起点是从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