实验一：移动平均.doc

实验一：移动平均法一、实验目的Part A:一次移动平均法1熟悉数据文件的建立；2熟悉时间序列数据散点图的绘制及其一次移动平均法适用条件的判断；3熟悉应用一次移动平均法进行相应预测；4熟悉一次移动平均法预测精度的分析及其最优移动步长N的确定；Part B:二次移动平均法1根据时间序列数据散点图，熟悉二次移动平均法适用条件的判断；2熟悉应用二次移动平均法进行相应预测；3熟悉二次移动平均法预测精度的分析及其最优移动步长N的确定； 二、实验内容及实验过程 Part A问题描述某汽油批发商在过去12周的汽油销售量统计数据如表1所示。 表1：汽油销售量数据 单位：千加仑周123456789101112销售量172119231820221822201722（1） 建立Excel数据文件，试分析统计数据，选择合适的模型来估计下周汽油销售量。（2） 取移动步长N=3，采用一次移动平均法进行预测，并分析其预测精度。（3） 如何选择合适的移动步长，使预测精度较高？实验过程 Excel的数据处理除了提供很多的函数外，另外还有两个重要的功能：数据分析和规划求解。但是这两个工具必须加载相应的宏后才能够使用。具体步骤为点击Excel【工具】下的子菜单【加载宏】，出现如图 的对话框，选择分析工具库和规划求解，点击确定后，在工具菜单栏下面就有了这两个分析工具了。步骤1：新建Excel文档，输入数据。启动Excel ，打开Excel 的一个工作簿，用鼠标选中某个单元格，就可以输入数据了，本实验数据如图。步骤2：绘制散点图。点击Excel【插入】菜单下的【图表】子菜单。在【标准类】选项卡“图表类型”栏选择“XY散点图”，点击下一步按钮（图3-1）。 “数据区域”选择数据所在的单元格区域“A2:B13”，产生系列选择“列”，如图 。点击完成按钮，得到销售量与时间的散点图，如图。步骤3：选择预测模型。从该汽油批发商在过去12周汽油销售量散点图可以看出，汽油销售量基本在20千加仑上下波动，无长期变化趋势，无季节影响，因而可以使用一次移动平均法进行相应预测。步骤4：计算一次移动平均值和预测值。方法1：公式法1）选择步长为3，在单元格“C4”中输入“=（B2+B3+B4）/3”或者插入函数AVERAGE，即“=AVERAGE（B2:B4）”，如图2）回车，并将单元格“C4”的内容复制到单元格区域“C5:C13”（选中单元格“C4”，按下Ctrl键，拖动鼠标至C13），并在单元格区域C4:C13的内容复制到D5:D14区域（在单元格D5，输入“= C4”，并将其内容复制到区域D6:D14），预测值结果如图 。方法2：一次移动平均预测模块法Excel软件的数据分析工具提供了简单方便的一次移动平均预测模块。1）点击Excel【工具】菜单下面的子菜单【数据分析】，打开“数据分析”对话框，从“分析工具”列表中选择“移动平均”，如图，点击确定按钮。2）在“移动平均”对话框 “输入区域”选择原始数据所在的单元格区域“B2:B13”，“间隔”中输入“3”（表示步长为3），“输出区域”选择单元格“C2”，如图 ，点击确定按钮，结果如图 。 同公式法，将单元格区域C4:C13的内容复制到D5:D14，即得一次移动平均值的预测值，如图 。步骤5：计算预测误差、误差平方。在单元格“D5”和“E5”分别输入“=B5C5”、“=D52”，并将单元格区域“D5:E5”的内容复制到单元格区域“D6:E13”，如图。步骤6：计算平均误差平方和。在单元格E14输入“MSE=”，单元格F14输入或插入AVERAGE函数，即“=AVERAGE(E5:E13)”，如图 。可见当步长为3时，预测误差平方和为5.6296296，下一期的预测值为19.6666667。步骤7：最优步长的确定。首先输入原始数据，周次和销售量。单元格C1、D1、E1分别输入“预测值”、“预测误差”、“误差平方”。将单元格H1设定为移动步长，输入任一值。在单元格G2输入“MSE=”，单元格H2则为预测的误差均方，即H1中设定的步长所对应于MSE的值，输入“=AVERAGE(E($H$1+1):E13)。在单元格C3中输入=IF(A3$H$1,AVERAGE(OFFSET(C4,-$H$1,-1,$H$1,1) 在单元格D3中输入=IF(C3=,B3-C3)在单元格E3中输入=IF(C3=,D32)将单元格区域C3:E3的内容复制到单元格C13:E13。其中IF()函数判断，例如单元格C3，表示A3(周次)是否小于H1(移动步长)，如果是，那么C3为空白，即这一周没有预测值，如果是否，就是该周对应的预测值，用AVERAGE函数表示，其参数范围由OFFSET函数确定。OFFSET(reference,rows,cols,height,width)函数是以指定的范围为参照系，通过给定偏移量得到新的范围。各参数定义为： Reference 是偏移量参照系的引用区域；Rows 是相对于偏移量参照系的向上（用负数表示）或向下（用正数表示）偏移的行数；Cols 相对于偏移量参照系的向左（用负数表示）或向右（用正数表示）偏移的列数； Height 高度，即所要返回的引用区域的行数；Width 宽度，即所要返回的引用区域的列数。可变移动步长的设置。将单元格H1设置为可变的移动步长微调项。点击Excel菜单【视图【工具栏】【窗体】，再点击“微调项”用鼠标拖到控制区H1，出现微调项，在微调项上点击鼠标右键，在弹出的快捷菜单上选择“设置控件格式”，如图 。进行参数设计，本实现移动步长确定为1-6，在最小值栏输入“1”, 在最大值栏输入“6”, 在步长栏输入“1”,单元格链接栏选择为步长所在位置“$H$1”改变步长，即可得到对应的预测值、预测误差、误差平方和误差均方的值，例如当步长等于6时的结果如图 。此时MSE为4.9398148。变动步长，可得不同的MSE，选择最小MSE对应的步长则为最优步长。本实验为当N=5时，MSE最小。当然，也可以采用规划求解的方法计算最优步长。规划求解方法后续实验中将分析，在此不再阐述。Part B问题描述某企业在过去1-12月份，其彩电销售量统计数据如表2所示。 表2：彩电量数据 单位：台月份123456789101112销售量468478486495488512523508513528536550（1）试分析统计数据，选择合适的模型来估计下月该产品的销售量。（2）取移动步长N=3，采用二次移动平均法进行预测，并分析其预测精度。实验过程步骤1：输入数据，绘制过去1-12月彩电销售量的“XY散点图”，如图 。从散点图可以看出，彩电销售量基本呈现线性增长趋势，因此可以使用二次指数平滑法进行预测。步骤2：设置步长N=3，应用公式法计算各期的一次移动平均值和二次移动平均值，如图 。步骤3：计算线性趋势的截距和斜率at和bt，在单元格G1和H1分别输入“A”、“B”。单元格G2输入截距公式，即“=2*C13D13”，单元格H2输入斜率计算公式，即“=2*(C13D13)/（31）”，结果如图 。步骤4：计算预测值。在单元格E2输入“=$G$2+$H$2*(A2-12)”，如图 。复制单元格E2内容到单元格区域“E13