利用Excel进行指数平滑分析与预测(2)
【例】连续10年的灌溉面积。
第一步，录入数据(图1)。
图1 原始数据
第二步，选项设置。
沿着主菜单的“工具(T)→数据分析(D)”路径打开“数据分析”选项框，选中“指数平滑”(图2)。
图2 数据分析选项框与“指数平滑”的位置
确定以后，弹出移动平均对话框(图3)，然后按如下步骤进行设置：
⒈ 将光标置入“输入区域”对应的空白栏，然后用鼠标从B1到B11选中全部时间序列连同标志；
⒉ 选中“标志”(位于第一行)；
⒊ 在“阻尼系数”对应的空白栏中键入“0.9”，表示指数平滑系数为0.1(即取α=0.1。注意：指数平滑系数与阻尼系数的关系是 “平滑系数＋阻尼系数＝1”)；
⒋ 将光标置入“输出区域”对应的空白栏，选中从C2到C11的单元格，作为计算结果的输出位置；
⒌ 选中“图表输出”和“标准误差”，这样会自动生成移动平均坐标图和标准误差值。
注意：如果“输入区域”对应的空白栏设置为“$B$2:$B$11”，即不包括数据标志项，则不要选中“标志”(图4)。
图3 指数平滑选项框(包括数据标志)
图3 指数平滑选项框(不包括数据标志)
第三步，输出结果。
完成上述设置以后，确定，即可得到计算结果，包括指数平滑结果及其标准误差(图5)，以及指数平滑曲线图(图6)。
图5 移动平均结果：平滑系数为α=0.1
图6 阻尼系数为0.9(平滑系数为0.1)的移动平均预测曲线(水红色)
如果不借助Excel的“数据分析”工具，指数平滑计算也是非常简单的，有关计算方法在“利用Excel进行指数平滑分析与预测(1)”中已经详细说明，不赘述，下面只介绍标准误差的计算方法。与移动平均法的标准误差计算类似，我们有两种途径。
计算方法之一：利用平方根命令sqrt和计算两个数组相对数值误差的平方和命令sumxmy2。
在E6单元格中输入组合命令“=SQRT(SUMXMY2(B3:B5,C3:C5)/3)”，回车，得到9.921047，然后选中E6单元格，将光标置于右下角，十字光标变得细小，按住鼠标左键，下拉至E11格，便可得到全部标准误差(图7)。
图7 标准误差的计算结果
计算方法之二：借助标准误差公式。
在E6单元格中键入公式“=(((B3-C3)^2+(B4-C4)^2+(B5-C5)^2)/3)^0.5”，回车得到9.921047；下拉，取得全部结果。标准误差的含义从计算公式可以看出。
至于指数平滑曲线，可以利用图表向导自行绘制：
首先，选中从A1到C11的全部数据及其移动平均结果(图8)。
图8 选中的数据范围
然后，点击“图表向导”图标，在弹出的“图表向导”选项框中选择“图表类型”中的“散点图”，在子项即“子图表类型”中选择“折线散点图”(图9)。确定，即可得到移动平均的预测曲线图。
图9 图表类型选项：折线散点图
最后，为了与图6进行对比，可对曲线图进行简单的编辑：打开“坐标轴格式”选项框，将横轴刻度改为1971－1980，将主要刻度改为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时的三次指数平滑结果(图19，图20)。
图19 二次指数平滑的设置
图18 三次指数平滑结果
图19 三次指数平滑曲线(α=0.3)
第八步，次数的选择(详细分析参见“利用Excel进行指数平滑分析与预测(1)”)。
1
1013
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
