试验一：移动平均

1、实验一：移动平均法一、实验目的Part A: 一次移动平均法1熟悉数据文件的建立；2熟悉时间序列数据散点图的绘制及其一次移动平均法适用条件的判断；3熟悉应用一次移动平均法进行相应预测；4熟悉一次移动平均法预测精度的分析及其最优移动步长N的确定;Part B:二次移动平均法1根据时间序列数据散点图，熟悉二次移动平均法适用条件的判断；2熟悉应用二次移动平均法进行相应预测；3熟悉二次移动平均法预测精度的分析及其最优移动步长N的确定;二、实验内容及实验过程Part A问题描述某汽油批发商在过去 12周的汽油销售量统计数据如表1所示。表1:汽油销售量数据单位：千加仑周123456789101112销售量

2、172119231820221822201722(1)建立Excel数据文件，试分析统计数据，选择合适的模型来估计下周汽油销售量。(2)取移动步长N=3,采用一次移动平均法进行预测，并分析其预测精度。(3)如何选择合适的移动步长，使预测精度较高？实验过程Excel的数据处理除了提供很多的函数外，另外还有两个重要的功能：数据分析和规划求解。但是这两个工具必须加载相应的宏后才能够使用。具体步骤为点击Excel【工具】下的子菜单【加载宏】，出现如图的对话框，选择分析工具库和规划求解，点击确定后，在工具菜单栏下面就有了这两个分析工具了。步骤1:新建Excel文档，输入数据。启动 Excel ,打开Ex

3、cel的一个工作簿，用鼠标选中某 个单元格，就可以输入数据了，本实验数据如图。步骤2：绘制散点图。 点击Excel【插入】 菜单下的【图表】 子菜单。在【标准类】 选项卡 “图表类型”栏选择 XY散点图”，点击下一步按钮（图3-1 ）。“数据区域”选择数据所在的单元格区域“A2:B13，产生系列选择“列”击完成按钮，得到销售量与时间的散点图，如图。,如图。点步骤3:选择预测模型。从该汽油批发商在过去12周汽油销售量散点图可以看出，汽油销售量基本在20千加仑上下波动，无长期变化趋势，无季节影响，因而可以使用一次移动平均法进行相应预测。步骤4:计算一次移动平均值和预测值。方法1:公式法1）选择步长

4、为3,在单元格“ C4”中输入“ =（B2+B3+B4 /3 ”或者插入函数 AVERAGE 即“=AVERAGEB2:B4）”,如图rC Ilcrosoft Excel -实哈一，回区文件 编辑 视图。 插入（X）格式 工具 数据 窗口俎 帮助也） 1变电通FDF。_ 5 X一三B / U毛孙心胡越第:：$用户登录,L指标逢数 同行hIAVERAGE X；二（B2+B3+BG/3ABCDEFG1周销售量移动平均值5=3）21J732214319=（B2+B3+B4） /35.2365J876加8722981S1092211102012二17131222Vn k h Sheel/ Sheets

5、 / Sheets / 1编辑数字2）回车，并将单元格“ C4的内容复制到单元格区域“ C5:C13（选中单元格“ C4, 按下Ctrl键，拖动鼠标至 C13）,并在单元格区域 C4:C13的内容复制到 D5:D14区域（在单 元格D5,输入“ =C4”，并将其内容复制到区域 D6:D14）,预测值结果如图 。EH Wj croxofT Esrel -安至-|:回立忤如 编帽妙粗阂Ki插久 格尤tjo） 工且fl）数据m窗n俎、帮助但，文电通FDF也）一再X：宋任 B 宴 U = EE -= -t-11如二AT 士&空向二4出用户登录f敷J6 面灯各 眼衣 L 直标壮l歙u也怙叫D2L- X

6、4 岳ABCDEFGH-1吊销客量移珀平均值（距引预测值（N-3）2117S21q3151954232119t5_182021762U20.相兆加货29w72220 2Q. 33333339S13so2OrID92220. afiStMMT2011103020 20. 666666712111719.前6696印20131?2219 , 5565065T 田 0666S6 71413_1E.SGG6067VI* W* n Shoctl /Shcota Z Shecta/一 瓜 _.1* 1输入数宇方法2: 一次移动平均预测模块法Excel软件的数据分析工具提供了简单方便的一次移动平均预测模块。

7、1）点击Excel【工具】菜单下面的子菜单【数据分析】，打开“数据分析”对话框，从“分析工具”列表中选择“移动平均” ，如图，点击确定按钮。2）在“移动平均”对话框“输入区域”选择原始数据所在的单元格区域“B2:B13”,“间隔”中输入“ 3”（表示步长为3）, “输出区域”选择单元格 C2,如图，点击确定按钮，结果如图同公式法，将单元格区域 C4:C13的内容复制到 D5:D14,即得一次移动平均值的预测值, 如图。步骤5:计算预测误差、误差平方。在单元格“D5”和“E5”分别输入“=B5 C5、=D5A2”, 并将单元格区域 D5:E5”的内容复制到单元格区域D6:E13，如图。步骤6:计

8、算平均误差平方和。在单元格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(

9、A3$H$1,AVERAGE(OFFSET(C4,-$H$1,-1,$H$1,1)在单元格 D3中输入=IF(C3=,B3-C3)在单元格 E3中输入=IF(C3=,D3A2)将单元格区域 C3:E3的内容复制到单元格 C13:E13。其中IF()函数判断，例如单元格 C3,表示A3(周次)是否小于H1(移动步长)，如果是， 那么C3为空白，即这一周没有预测值，如果是否，就是该周对应的预测值，用 AVERAGE 数表示，其参数范围由OFFSET数确定。 OFFSET(reference,rows,cols,height,width) 函数是以指定的范围为参照系，通过给定偏移量得到新的范围。各参

10、数定义为：Reference 是 偏移量参照系的引用区域；Rows是相对于偏移量参照系的向上(用负数表示)或向下(用正数表示)偏移的行数；Cols相对于偏移量参照系的向左(用负数表示)或向右(用正数表示)偏移的列数； Height高度，即所要返回的引用区域的行数；Width宽度，即所要返回的引用区域的列数。可变移动步长的设置。将单元格H1设置为可变的移动步长微调项。点击Excel菜单【视图一一【工具栏】一一【窗体】，再点击“微调项”用鼠标拖到控制区H1,出现微调项，在微调项上点击鼠标右键，在弹出的快捷菜单上选择“设置控件格式”，如图 。进行参数设计，本实现移动步长确定为1-6,在最小值栏输入“

11、 1”，在最大值栏输入“ 6”，在步长栏输入“ 1 ”，单元格链接栏选择为步长所在位置“$H$1”改变步长，即可得到对应的预测值、预测误差、误差平方和误差均方的值，例如当步长等于6时的结果如图 。此时MS助4.9398148。变动步长，可得不同的MSE选择最小 MSE对应的步长则为最优步长。本实验为当N=5时，MSEt小。口回区：j文件理）编辑视图9 插入日 :格式工具9 数据窗口帮助文电通FDF。_ fl1 x:由用户登录Jj ”2 鼻叮1孙人A 3口1 区-X VABCDEFGH-1周销售量 预测值前测误差误差平方工 6 I21J7MSE=4.gags1483221431P54236518

12、76208_四，g668比彳2. 3333333 5,4444444981320.E-2.56.2510g222C2|411102020. E-0.50.251211172C-3|913122215,8冏333：2.1666667 4.69444141411(*H * n Sheetl Sheet2 ZSheets /iir就绪濠动当然，也可以采用规划求解的方法计算最优步长。规划求解方法后续实验中将分析，在此不再阐述。口 licrooft Excel -实鎏一Part B问题描述某企业在过去1-12月份，其彩电销售量统计数据如表2所示。表2:彩电量数据单位：台月份123456789101112

13、销售量：468478486495488512523508513:528536550(1)试分析统计数据，选择合适的模型来估计下月该产品的销售量。(2)取移动步长 N=3,采用二次移动平均法进行预测，并分析其预测精度。实验过程步骤1:输入数据，绘制过去 1-12月彩电销售量的“ XY散点图”，如图。从散点图可以看 出，彩电销售量基本呈现线性增长趋势，因此可以使用二次指数平滑法进行预测。步骤2:设置步长N=3,应用公式法计算各期的一次移动平均值和二次移动平均值，如图步骤3:计算线性趋势的截距和斜率at和bt,在单元格 G1和H1分别输入“ A”、B”。单元格G2输入截距公式，即“=2*C13 D1

14、3,单元格H2输入斜率计算公式，即“=2*(C13 D13)/ (31)”,结果如图。步骤4:计算预测值。在单元格 E2输入“ =$G$2+$H$2*(A2-12) ”，如图复制单元格E2内容到单元格区域“E13:E13，如图。下个月的销售量预测值为561,即在单元格E14输入“=G2+H2*1，如图。步骤4:计算预测的误差均方。在单元格F2输入“ =(B2-E2F2 ”，如图 ，并复制到单元格区域F3:F13,如图 。：Lj文件9 编辑 视图9 插入CL）格式 工具Q）数据 窗口理） EycelSim帮助如 _曰XF2 ， 拉=（B2-E2）2就绪求和=比69一 222222在单元格 G5输

15、入“MSE1, H5输入MSE勺值，即“ =AVERAGF2:F13）,结果如图：3j文件g 编辑 视图9 插入复）格式Q）工具任） 9 数据通）窗口/帮助QP文电通PDF ()-(5x1:名用尸登录后：25也专0 5 /某 ftLh ab|JLj 山AVERAGE-XV/7 =G2+H2*1ABCDEFGH T1月份销售量一次移动平均二次移动平均预测值A；B H21468549.33311.333333247843486477.3333333486.33333335449565488489.6666667484.444444476512498.3333333491.4444444875235

16、07.6666667498.555555698508514.3333333506.7777778109513514.6666667512. 22222221110528516.3333333515.11111111211536525.6666667518.88888891312550538526.666666714131=G2+H2*11VH H /Sheet2 Sheet3 /|J输入数字J E3 licrosoft Fixcel -实验一：sj文件g 编辑 视图9 插入 格式）工具复） 数据） 1守口也）帮助国）文电通PDF () I.S x|：$用户登录 ：10.5“二产 5 - a，4 曰4,式口 1 r-v XTK1 11iAF7-XV/7ABCDEFGH | T1.目份销售量一次移动平均二次移动平均坳测值a nB Ta2I468549.33311.333333247843486477.333333354495486.333333365488489.6666667484.444