利用excel进行移动平均分析资料

《利用excel进行移动平均分析资料》由会员分享，可在线阅读，更多相关《利用excel进行移动平均分析资料（10页珍藏版）》请在金锄头文库上搜索。

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

2、时间序列数据每两个进行移动平均（即取 m=2） ； 将光标置入“输出区域”对应的空白栏，选中从 C2 到 C11 的单元格，作为计算结 果的输出位置； 选中“图表输出”和“标准误差” ，这样会自动生成移动平均坐标图和标准误差值。 注意：如果“输入区域”对应的空白栏设置为“$B$2:$B$11” ，即不包括数据标志项， 则不要选中“标志位于第一行” （图 4） 。 2 图 3 周期为 2 的移动平均选项：包括数据标志 图 3 周期为 2 的移动平均选项：包括数据标志 图 4 周期为 2 的移动平均选项：不包括数据标志 图 4 周期为 2 的移动平均选项：不包括数据标志 第三步，输出结果。 完成上

3、述设置以后， 确定， 即可得到计算结果， 包括移动平均结果及其标准误差 （图 5） ， 以及移动平均曲线图（图 6） 。 图 5 移动平均结果：周期图 5 移动平均结果：周期 m=2 =2 3 移动平均 0 10 20 30 40 50 60 12345678910 数据点 值 实际值 预测值 图 6 周期为 2 的移动平均预测曲线（水红色） 图 6 周期为 2 的移动平均预测曲线（水红色） 如果不借助 Excel 的“数据分析”工具，移动平均计算也是非常简单的，说明如下： 计算方法之一：借助平均命令 average。 在 E3 单元格中键入命令“=average(B2:B3)” ，表示 28

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

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

6、全部数据及其移动平均结果（图 9） 。 然后，点击“图表向导”图标，在弹出的“图表向导”选项框中选择“图表类型” 中的“散点图” ，在子项即“子图表类型”中选择“折线散点图” （图 10） 。确定，即可得到 移动平均的预测曲线图。 最后，为了与图 6 进行对比，可对曲线图进行简单的编辑：打开“坐标轴格式”选项框， 将横轴刻度改为 19711980，将主要刻度改为 1，并将字体变小，得到与图 6 结构一样的预 测曲线图（图 11） 。 图 9 选中的数据范围 图 9 选中的数据范围 5 图 10 图表类型选项：折线散点图 图 10 图表类型选项：折线散点图 0 10 20 30 40 50 60

7、 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 灌溉面积y(千亩) m=2 图 11 借助图表向导绘制的移动平均预测曲线 图 11 借助图表向导绘制的移动平均预测曲线 方法之二：利用图表向导和添加趋势线功能。 首先，选中数据系列 B1:B11（连同数据标志） ，借助图表向导绘制折线散点图（图 12） 。 然后，选中数据系列，按右键，点击菜单中的“添加趋势线”选项，弹出“添加趋势线” 选项框；在趋势线“类型”中选择“移动平均” ，在“周期”中选中系统默认的“2” ，对应 于 m=2（图 13） ；确定，即可生成移动平均趋势线（图 14） 。

8、最后，为了与图 6 进行对比，可对趋势线进行简单的编辑：通过“趋势线格式”选项将 颜色改为水红；通过“坐标轴格式”将横轴刻度改为 1-10 之间，并将刻度单位改为 1。于 是得到与图 6 类似的曲线图形（图 15） 。 6 灌 溉 面 积 y ( 千 亩 ) 0 1 0 2 0 3 0 4 0 5 0 6 0 051 01 5 灌 溉 面 积 y ( 千 亩 ) 图 12 原始数据的折线散点图 图 12 原始数据的折线散点图 图 13 添加周期为 2 的移动平均趋势线选项 图 13 添加周期为 2 的移动平均趋势线选项 图表标题 0 10 20 30 40 50 60 051015 灌溉面积y

9、(千亩) 2 天移动平均 (灌溉面积y(千 亩) 图 14 添加移动平均趋势线以后 图 14 添加移动平均趋势线以后 7 图表标题 0 10 20 30 40 50 60 12345678910 灌溉面积y(千亩) 2 天移动平均 (灌溉面积y(千 亩) 图 15 经过简单的编辑以后 图 15 经过简单的编辑以后 第四步，重复移动平均计算。 重新打开图 3 所示的“移动平均选项框” ，将间隔改为 3，对应于周期 m=3；将输出区 域改为 E2:E11，其他选项不变（图 16） 。确定，立即得到结果（图 17，图 18） 。 图 16 周期为 3 的移动平均选项（包括数据标志） 图 16 周期为

10、 3 的移动平均选项（包括数据标志） 图 17 移动平均结果：周期图 17 移动平均结果：周期 m=3 =3 8 移动平均 0 10 20 30 40 50 60 12345678910 数据点 值 实际值 预测值 图 18 周期为 3 的移动平均预测曲线（水红色） 图 18 周期为 3 的移动平均预测曲线（水红色） 在图 16 中，分别将周期改为 m=4，m=5，m=10，反复重复上述操作过程，可以 得到全部的移动平均结果。 图 19 不同周期的移动平均结果 图 19 不同周期的移动平均结果 第五步，检验 上述移动平均计算是在不同周期条件下计算的， 但实际应用时并不需要我们采用各种周 期，而

11、是应该找到预测精度最好的周期，即 m 值，为此需要进行 MSE 检验。计算公式为 += = N mt ttm Mx mN 1 2 1) ( 1 MSE 下面以 m=2 的情况为例，予以说明。 首先，将指数平移结果下移一步（图 20） 。 然后，在 D4 单元格中输入公式“=(B4-C4)2” ，回车，得到 273.9025；下拉直 D11 得 到全部结果；求和，得到 991.243。 最后，计算 MSE 值，根据公式，我们有 MSE(m=2)=991.243/(10-2)=123.905。 用这种方法，可以求出 m=3、m=4、m=9 时的全部 MSE 值，选择 MSE 最小时的移 动平均结果

12、作为预测值（参见附表） 。从图 22 可以看出，很难找到一个合适的周期，m=3 和 m=6 似乎是可取的，从后面的指数平滑结果看，=0.31/m=0.33，故取 m=3 更为合适。 9 总体看来，用移动平均方法预测连续 10 年的灌溉面积并不理想。 图 20 将平移结果下移一步 图 20 将平移结果下移一步 图 21 误差平方与误差平方和 图 21 误差平方与误差平方和 附表 连续 10 年的灌溉面积及其移动平均预测值 附表 连续 10 年的灌溉面积及其移动平均预测值 年份年份 t 19711971 19721972 19731973 1974 1975 1976 1977 1978 1979

13、 198019811974 1975 1976 1977 1978 1979 19801981 MSE MSE 灌溉面积 28.6 19.3 40.5 35.6 48.9 45.0 29.2 34.1 46.7 37.4 预测值预测值 检验值检验值 m=2 24.0 29.9 38.1 42.3 47.0 37.1 31.7 40.4 42.142.142.1 123.9 m=3 29.5 31.8 41.7 43.2 41.0 36.1 36.7 39.439.439.4 99.6 m=4 31.0 36.1 42.5 39.7 39.3 38.8 36.936.936.9 110.8 m=5 34.6 37.9 39.8 38.6 40.8 38.538.538.5 58.8 m=6 36.3 36.4 38.9 39.9 40.240.240.2 30.9 m=7 35.3 36.1 40.0 39.639.639.6 40.3 m=8 35.2 37.4 39.739.739.7 66.7 m=9 36.4 37.437.437.4 0.9 10 0 20 40 60 80 100 120 140 012345678910 图 22 周期图 22 周期 m 与均方差（MSE）的关系 与均方差（MSE）的关系