利用Excel进行指数平滑分析（2）

1、利用Excel进行指数平滑分析与预测（2）【例】连续10年的灌溉面积。第一步，录入数据（图1）。图1 原始数据第二步，选项设置。沿着主菜单的“工具（T）数据分析（D）”路径打开“数据分析”选项框，选中“指数平滑”（图2）。图2 数据分析选项框与“指数平滑”的位置确定以后，弹出移动平均对话框（图3），然后按如下步骤进行设置： 将光标置入“输入区域”对应的空白栏，然后用鼠标从B1到B11选中全部时间序列连同标志； 选中“标志”（位于第一行）； 在“阻尼系数”对应的空白栏中键入“0.9”，表示指数平滑系数为0.1（即取=0.1。注意：指数平滑系数与阻尼系数的关系是 “平滑系数阻尼系数1”）； 将光标

2、置入“输出区域”对应的空白栏，选中从C2到C11的单元格，作为计算结果的输出位置； 选中“图表输出”和“标准误差”，这样会自动生成移动平均坐标图和标准误差值。注意：如果“输入区域”对应的空白栏设置为“$B$2:$B$11”，即不包括数据标志项，则不要选中“标志”（图4）。图3 指数平滑选项框（包括数据标志）图3 指数平滑选项框（不包括数据标志）第三步，输出结果。完成上述设置以后，确定，即可得到计算结果，包括指数平滑结果及其标准误差（图5），以及指数平滑曲线图（图6）。图5 移动平均结果：平滑系数为=0.1图6 阻尼系数为0.9（平滑系数为0.1）的移动平均预测曲线（水红色）如果不借助Excel

3、的“数据分析”工具，指数平滑计算也是非常简单的，有关计算方法在“利用Excel进行指数平滑分析与预测（1）”中已经详细说明，不赘述，下面只介绍标准误差的计算方法。与移动平均法的标准误差计算类似，我们有两种途径。计算方法之一：利用平方根命令sqrt和计算两个数组相对数值误差的平方和命令sumxmy2。在E6单元格中输入组合命令“=SQRT(SUMXMY2(B3:B5,C3:C5)/3)”，回车，得到9.921047，然后选中E6单元格，将光标置于右下角，十字光标变得细小，按住鼠标左键，下拉至E11格，便可得到全部标准误差（图7）。图7 标准误差的计算结果计算方法之二：借助标准误差公式。在E6单元

4、格中键入公式“=(B3-C3)2+(B4-C4)2+(B5-C5)2)/3)0.5”，回车得到9.921047；下拉，取得全部结果。标准误差的含义从计算公式可以看出。至于指数平滑曲线，可以利用图表向导自行绘制：首先，选中从A1到C11的全部数据及其移动平均结果（图8）。图8 选中的数据范围然后，点击“图表向导”图标，在弹出的“图表向导”选项框中选择“图表类型”中的“散点图”，在子项即“子图表类型”中选择“折线散点图”（图9）。确定，即可得到移动平均的预测曲线图。图9 图表类型选项：折线散点图最后，为了与图6进行对比，可对曲线图进行简单的编辑：打开“坐标轴格式”选项框，将横轴刻度改为197119

5、80，将主要刻度改为1，并将字体变小，得到与图6结构一样的预测曲线图（图10）。图10 借助图表向导绘制的移动平均预测曲线第四步，重复移动平均计算。重新打开图3所示的“指数平滑选项框”，将阻尼系数改为0.8，对应于平滑系数=0.2；将输出区域改为E2:E11，其他选项不变（图11）。确定，立即得到结果。继续改变阻尼系数为0.7、0.6、0.1，直到算出所有的结果（图12为局部）。图11 阻尼系数为0.8的指数平滑选项（包括数据标志）图12 基于不同阻尼系数的指数平滑结果第五步，检验 参见“利用Excel进行指数平滑分析与预测（1）”，不赘述。当=0.3时，曲线图与SPSS给出的结果类似，但考虑1971年的数据点（图13）。图13 =0.3的指数平滑预测曲线（水红色）第六步，二次指数平滑首先，在1971年对应的C2中填上28.6（图14），然后打开“指数平滑”选项框，第一次指数平滑结果进行指数平滑，=0.3时的设置见图15。确定，即可得到二次指数平滑结果（图16，图17）。图14 =0.3的指数平滑预测曲线（水红色）图15 二次指数平滑的设置图16 二次指数平滑结果图17 二次指数平滑曲线（=0.3）第七步，三次指数平滑与二次指数平滑类似，首先在1971年对应的D2中填上28.6，然后打开“指数平滑”选项框（图18），对第二次指数平滑结果进行指数平滑，=0.3时的三次指数平滑结果（