利用excel进行移动平均分析.doc

利用 Excel 进行移动平均分析 【例】连续 10 年的灌溉面积。 第一步，录入数据（图 1）。 图1 原始数据 第二步，选项设置。 沿着主菜单的“工具（T）→数据分析（D）”路径打开“数据分析”选项框，选中“移 动平均”（图 2）。 图2 数据分析选项框与“移动平均”的位置 确定以后，弹出移动平均对话框（图 3），然后按如下步骤进行设置： ⒈ 将光标置入“输入区域”对应的空白栏，然后用鼠标从 B1 到 B11 选中全部时间序 列连同标志； ⒉ 选中“标志位于第一行”； ⒊ 在间隔中键入“2”，表示将时间序列数据每两个进行移动平均（即取 m=2）； ⒋ 将光标置入“输出区域”对应的空白栏，选中从 C2 到 C11 的单元格，作为计算结 果的输出位置； ⒌ 选中“图表输出”和“标准误差”，这样会自动生成移动平均坐标图和标准误差值。 注意：如果“输入区域”对应的空白栏设置为“$B$2:$B$11”，即不包括数据标志项， 则不要选中“标志位于第一行”（图 4）。 1 图3 周期为 2 的移动平均选项：包括数据标志 图4 周期为 2 的移动平均选项：不包括数据标志 第三步，输出结果。 完成上述设置以后，确定，即可得到计算结果，包括移动平均结果及其标准误差（图 5）， 以及移动平均曲线图（图 6）。 图5 移动平均结果：周期m=2 2 移动平均 60 50 40 30 20 实际值 预测值 10 0 1 2 3 4 5 6 7 8 9 10 数据点 图6 周期为 2 的移动平均预测曲线（水红色） 如果不借助 Excel 的“数据分析”工具，移动平均计算也是非常简单的，说明如下： 计算方法之一：借助平均命令 average。 在 E3 单元格中键入命令“=average(B2:B3)”，表示 28.6 与 19.3 进行算术平均，回车， 立即得到 23.95；然后选中 E3 单元格，将光标置于右下角，十字光标变得细小，按住鼠标 左键，下拉至 E11 格，即可得到全部的移动平均结果（图 7）。 图7 移动平均的计算结果 计算方法之二：借助算术平均公式。 在 E3 但单元格中输入公式“=(B2+B3)/2”，回车，得到 23.95，下面的步骤与基于命令 的计算方法相同——下拉得到全部数值。 标准误差的计算方法也有两种： 计算方法之一：利用平方根命令 sqrt 和计算两个数组相对数值误差的平方和命令 sumxmy2。 在 F4 单元格中输入组合命令“=SQRT(SUMXMY2(B3:B4,C3:C4)/2)”，回车，得到 3 8.184818，然后选中 F4 单元格，将光标置于右下角，十字光标变得细小，按住鼠标左键， 下拉至 F11 格，便可得到全部标准误差（图 8）。 图8 标准误差的计算结果 计算方法之二：借助标准误差公式。 在 F4 单元格中键入公式“=(((B3-C3)^2+(B4-C4)^2)/2)^0.5”，回车得到 8.184818；下拉， 取得全部结果。标准误差的含义从计算公式可以看出。 如果希望分步绘制移动平均预测曲线图，亦可采用两种不同的办法： 方法之一：利用图表向导。 首先，选中从 A1 到 C11 的全部数据及其移动平均结果（图 9）。 然后，点击“图表向导”图标 ，在弹出的“图表向导”选项框中选择“图表类型” 中的“散点图”，在子项即“子图表类型”中选择“折线散点图”（图 10）。确定，即可得到 移动平均的预测曲线图。 最后，为了与图 6 进行对比，可对曲线图进行简单的编辑：打开“坐标轴格式”选项框， 将横轴刻度改为 1971－1980，将主要刻度改为 1，并将字体变小，得到与图 6 结构一样的预 测曲线图（图 11）。 图9 选中的数据范围 4 图10 图表类型选项：折线散点图 60 50 40 30 灌溉面积y(千亩) m=2 20 10 0 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 图11 借助图表向导绘制的移动平均预测曲线 方法之二：利用图表向导和添加趋势线功能。 首先，选中数据系列 B1:B11（连同数据标志），借助图表向导绘制折线散点图（图 12）。 然后，选中数据系列，按右键，点击菜单中的“添加趋势线”选项，弹出“添加趋势线” 选项框；在趋势线“类型”中选择“移动平均”，在“周期”中选中系统默认的“2”，对应 于 m=2（图 13）；确定，即可生成移动平均趋势线（图 14）。 最后，为了与图 6 进行对比，可对趋势线进行简单的编辑：通过“趋势线格式”选项将 颜色改为水红；通过“坐标轴格式”将横轴刻度改为 1-10 之间，并将刻度单位改为 1。于 是得到与图 6 类似的曲线图形（图 15）。 5 灌 溉 面 积 y(千 亩 ) 60 50 40 3