利用Excel进行移动平均分析Word文档下载推荐.docx

利用Excel进行移动平均分析Word文档下载推荐.docx

《利用Excel进行移动平均分析Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《利用Excel进行移动平均分析Word文档下载推荐.docx（12页珍藏版）》请在冰点文库上搜索。

图4周期为2的移动平均选项：

不包括数据标志

第三步，输出结果。

完成上述设置以后，确定，即可得到计算结果，包括移动平均结果及其标准误差（图5），以及移动平均曲线图（图6）。

图5移动平均结果：

周期m=2

图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）”，回车，得到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选中的数据范围

图10图表类型选项：

折线散点图

图11借助图表向导绘制的移动平均预测曲线

方法之二：

利用图表向导和添加趋势线功能。

首先，选中数据系列B1:

B11（连同数据标志），借助图表向导绘制折线散点图（图12）。

然后，选中数据系列，按右键，点击菜单中的“添加趋势线”选项，弹出“添加趋势线”选项框；

在趋势线“类型”中选择“移动平均”，在“周期”中选中系统默认的“2”，对应于m=2（图13）；

确定，即可生成移动平均趋势线（图14）。

最后，为了与图6进行对比，可对趋势线进行简单的编辑：

通过“趋势线格式”选项将颜色改为水红；

通过“坐标轴格式”将横轴刻度改为1-10之间，并将刻度单位改为1。

于是得到与图6类似的曲线图形（图15）。

图12原始数据的折线散点图

图13添加周期为2的移动平均趋势线选项

图14添加移动平均趋势线以后

图15经过简单的编辑以后

第四步，重复移动平均计算。

重新打开图3所示的“移动平均选项框”，将间隔改为3，对应于周期m=3；

将输出区域改为E2:

E11，其他选项不变（图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”，回车，得到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.3≈1/m=0.33，故取m=3更为合适。

总体看来，用移动平均方法预测连续10年的灌溉面积并不理想。

图20将平移结果下移一步

图21误差平方与误差平方和

附表 

连续10年的灌溉面积及其移动平均预测值

年份t

1971

1972

1973

1974

1975

1976

1977

1978

1979

1980

1981

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.1

123.9

m=3

29.5

31.8

41.7

43.2

41.0

36.1

36.7

39.4

99.6

m=4

31.0

42.5

39.7

39.3

38.8

36.9

110.8

m=5

34.6

37.9

39.8

38.6

40.8

38.5

58.8

m=6

36.3

36.4

38.9

39.9

40.2

30.9

m=7

35.3

40.0

39.6

40.3

m=8

35.2

66.7

m=9

0.9

图22周期m与均方差（MSE）的关系