利用Excel进行移动平均分析.docx

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

2、的输出位置；选中“图表输出”和“标准误差”，这样会自动生成移动平均坐标图和标准误差值。注意：如果“输入区域”对应的空白栏设置为“$B$2:$B$11”，即不包括数据标志项，则不要选中“标志位于第一行”（图4）。图3 周期为2的移动平均选项：包括数据标志图4 周期为2的移动平均选项：不包括数据标志第三步，输出结果。完成上述设置以后，确定，即可得到计算结果，包括移动平均结果及其标准误差（图5），以及移动平均曲线图（图6）。图5 移动平均结果：周期m=2图6 周期为2的移动平均预测曲线（水红色）如果不借助Excel的“数据分析”工具，移动平均计算也是非常简单的，说明如下：计算方法之一：借助平均命令a

3、verage。在E3单元格中键入命令“=average(B2:B3)”，表示28.6与19.3进行算术平均，回车，立即得到23.95；然后选中E3单元格，将光标置于右下角，十字光标变得细小，按住鼠标左键，下拉至E11格，即可得到全部的移动平均结果（图7）。图7 移动平均的计算结果计算方法之二：借助算术平均公式。在E3但单元格中输入公式“=(B2+B3)/2”，回车，得到23.95，下面的步骤与基于命令的计算方法相同下拉得到全部数值。标准误差的计算方法也有两种：计算方法之一：利用平方根命令sqrt和计算两个数组相对数值误差的平方和命令sumxmy2。在F4单元格中输入组合命令“=SQRT(SUM

4、XMY2(B3:B4,C3:C4)/2)”，回车，得到8.184818，然后选中F4单元格，将光标置于右下角，十字光标变得细小，按住鼠标左键，下拉至F11格，便可得到全部标准误差（图8）。图8 标准误差的计算结果计算方法之二：借助标准误差公式。在F4单元格中键入公式“=(B3-C3)2+(B4-C4)2)/2)0.5”，回车得到8.184818；下拉，取得全部结果。标准误差的含义从计算公式可以看出。如果希望分步绘制移动平均预测曲线图，亦可采用两种不同的办法：方法之一：利用图表向导。首先，选中从A1到C11的全部数据及其移动平均结果（图9）。然后，点击“图表向导”图标，在弹出的“图表向导”选项框

5、中选择“图表类型”中的“散点图”，在子项即“子图表类型”中选择“折线散点图”（图10）。确定，即可得到移动平均的预测曲线图。最后，为了与图6进行对比，可对曲线图进行简单的编辑：打开“坐标轴格式”选项框，将横轴刻度改为19711980，将主要刻度改为1，并将字体变小，得到与图6结构一样的预测曲线图（图11）。图9 选中的数据范围图10 图表类型选项：折线散点图图11 借助图表向导绘制的移动平均预测曲线方法之二：利用图表向导和添加趋势线功能。首先，选中数据系列B1:B11（连同数据标志），借助图表向导绘制折线散点图（图12）。然后，选中数据系列，按右键，点击菜单中的“添加趋势线”选项，弹出“添加趋

6、势线”选项框；在趋势线“类型”中选择“移动平均”，在“周期”中选中系统默认的“2”，对应于m=2（图13）；确定，即可生成移动平均趋势线（图14）。最后，为了与图6进行对比，可对趋势线进行简单的编辑：通过“趋势线格式”选项将颜色改为水红；通过“坐标轴格式”将横轴刻度改为1-10之间，并将刻度单位改为1。于是得到与图6类似的曲线图形（图15）。图12 原始数据的折线散点图图13 添加周期为2的移动平均趋势线选项图14 添加移动平均趋势线以后图15 经过简单的编辑以后第四步，重复移动平均计算。重新打开图3所示的“移动平均选项框”，将间隔改为3，对应于周期m=3；将输出区域改为E2:E11，其他选项

7、不变（图16）。确定，立即得到结果（图17，图18）。图16 周期为3的移动平均选项（包括数据标志）图17移动平均结果：周期m=3图18 周期为3的移动平均预测曲线（水红色）在图16中，分别将周期改为m=4，m=5，m=10，反复重复上述操作过程，可以得到全部的移动平均结果。图19 不同周期的移动平均结果第五步，检验上述移动平均计算是在不同周期条件下计算的，但实际应用时并不需要我们采用各种周期，而是应该找到预测精度最好的周期，即m值，为此需要进行MSE检验。计算公式为下面以m=2的情况为例，予以说明。首先，将指数平移结果下移一步（图20）。然后，在D4单元格中输入公式“=(B4-C4)2”，回

8、车，得到273.9025；下拉直D11得到全部结果；求和，得到991.243。最后，计算MSE值，根据公式，我们有MSE(m=2)=991.243/(10-2)=123.905。用这种方法，可以求出m=3、m=4、m=9时的全部MSE值，选择MSE最小时的移动平均结果作为预测值（参见附表）。从图22可以看出，很难找到一个合适的周期，m=3和m=6似乎是可取的，从后面的指数平滑结果看，=0.31/m=0.33，故取m=3更为合适。总体看来，用移动平均方法预测连续10年的灌溉面积并不理想。图20 将平移结果下移一步图21 误差平方与误差平方和附表 连续10年的灌溉面积及其移动平均预测值年份t197

9、11972197319741975197619771978197919801981 MSE灌溉面积28.619.340.535.648.945.029.234.146.737.4预测值检验值m=224.029.938.142.347.037.131.740.442.142.1123.9m=329.531.841.743.241.036.136.739.439.499.6m=431.036.142.539.739.338.836.936.9110.8m=534.637.939.838.640.838.538.558.8m=636.336.438.939.940.240.230.9m=735.336.140.039.639.640.3m=835.237.439.739.766.7m=936.437.437.40.9图22 周期m与均方差（MSE）的关系