网上书店实验数据分析报告

1、数据分析实验报告20 15- 20 16学年 第一学期班 级：学 号：姓 名：授课教师：况湘玲 实验教师：况湘玲实验一 网上书店数据库的创建及其查询实验类型：验证性实验学时：2实验目的：理解数据库的概念；理解关系（二维表）的概念以及关系数据库中数据的组织方式；了解数据库创建方法。实验步骤：步骤1 ：选择“ bookstore数据源，进入 查询设计窗口。步骤2:选择查询中需要使用的表。在添加表”对话框的 表”列表中分别选择 书”和出版社”表，并单击 添加”按钮将它们添加至表窗 格。酔Microsoft Query- a文件 请京血 规豊M 唱式CO 去虽用O iSSCR ffla（W）I樹法旧I

2、财 画 囲鬲is 冋可 因Tin it何1阿七査向来自bookitore二 已丨23 |书库存更书容伯: 或.L5BB库冬塑|芳呂0-0701160-7-5374Ax a Second. Latn0-OT0I66O-9-B2DD-&.1 A.bas IxpArtE91 G&-0701639-1-0122D oil a. Al ni stral a onCHTFOEatn-fi-g22ETal百屯a LagiTi Scrip11-0703400-0130Th.e Handjbook o叫*1记录IMM选走丈坤捋数IS返回11:0ft EmcqI艮!以将SHS传输到阴户需应用程匸11-r NUM1

3、L查询设计4低价图书信息查询 步骤1 :选择数据源并添加表。选择“bookstore数据源，进入 查询设计窗口。在 添加表对话框中选择 书表，将其添加到 表窗格中。步骤2 :选择字段。在 查询设计”窗口的 表”窗格中，双击 书”表的 书名” 出版年份”和 单价”字段。步骤3:设置查询条件，显示查询结果。在 条件”窗格的 条件字段”行的第一列中选择 单价”，并在下一行中输入“10iaflScfij tft O rwiiff 毗1“枷击IN口1可*1丽Ll 丽鬲 阪y-r可 UTITFI 口 (R) Sn(W) W&*i(H) 画田阳两涮鬲阳岡可因囿rn両画实验1-3响当当”网上书店会员分布和图书

4、销售信息查询实验目的 ?掌握复杂的数据查询方法：多表查询、计算字段和汇总查询 步骤1：选择“bookstore数据源并添加 会员”表。步骤2:选择分类字段、汇总字段和汇总方式。本查询的分类字段是会员的城市，汇总字段是会员号，汇总方式是计数。在查询设计会员号”字段的列标，在编窗口的 表”窗格中，双击 会员”表的 城市”和 会员号”字段。然后双击辑列”对话框中输入列标 会员人数”并选择汇总方式：计数”歹IU3 too kstoreA.i上海4L*-I-冉許器器需焉IOM.需二氏常| G 兀 3 $I !*!iA左-+w.CE3” 忻曲加件 E -垃丄步骤3 :设置查询条件。若仅仅想了解上海和北京的

5、会员人数，可以在条件窗格中设置相应的条件,Microsoft Query査询来自bookstore4= 号电临 市址U系苕甌 3E地亠If联益St4*4.019.95007Q.80004013.015.022.9500II4.T5QO12094.017 023 m39LOOOO8005.07.029.00209.5004QI2.G3 0珂 9500叭聊QD2006.06.036.0000216.0000B0IS.Q14 03S.Z500507.50006015.01.O37.0MI592.0000n1勺n购 oraTi町口琳门门Tmu记录巾LLku4 I厂选走“砸”菜单的“杀件”.以显示戏雕挙

6、件。步骤3 :设置查询条件。在条件”窗格的条件字段”行的第一列中选择 订购日期”并在下一行中输入=20057-1 and =5Ln (W20DB/1/23*. *2(V库存麵比库曰明 订準号 订单状恵 订刚曰期作看号弋I刻馳姓左.丨订购轨里丨订甌曰期N Shwwfcis.C. S2006-01-23 00:00.00Tvllin, Fvul弓Dd?-04.-J. 胡：血0： 口口hzi记录 i rT雄文护棚枚磁回IhmqFt Exits”亂捋粘诵件辅到用户端应用程序冲INU通过汇总发现近两年 shammas,Namir c.和wellin, paul的书最畅销。实验二企业销售数据的分类汇总分析

7、实验类型：验证性 实验学时：2实验目的：理解数据分类汇总在企业中的作用与意义；掌握数据透视表工具的基本分类汇总功能；掌握建立分类汇总数据排行榜、生成时间序列、绘制pareto曲线图、计算各地区客户分布、统计各地区客户的平均销售额和大宗销售时间序列的方法和步骤。实验步骤：步骤I :获取各客户每笔销售的销售额、销售产品的类别和时间。在一张空自的工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，随后启动了Microsoft query，选择所建立的连接到Northwi nd.mdb数据库的ODB(数据源-“nW,并选中“选择数据源”窗

8、口下方“使用查询向 导创建/编辑查询”选项，单击“确定”按钮，选择“客户”表中的“公司名称”、“订单”表中的“订购日期”以及“类别”表中“类别名称”字段，随后query弹出窗口显示“查询向导无法继续，因为该表格无法链接到您的查询中。您必须在Microsoft query中的表格之间拖动字段， 人工链接。”这是因为“类别”表无法同“订单”表建立联系。单击“确定”按钮。要查询销售额，需要在 query中首先增加“订单明细”表，利用其中的“单价”、“数量”与“折扣”字段中的数据，计算销售额。在“数据”窗格中一个空白字段的名称处输人公式“订单明细单价*数量*（1-折扣）”，按回车键后就可以计算出销售额

9、。随后，将“产品”表也添加到查询中，虽然查 询结果中并不包括任何“产品”表的字段，但是该表能够建立“类别”表与“订单明细”表之间的 联系（“订单明细”表指明所订购产品的ID，“产品”表指明该产品属于哪一个类别）。此时，查询中的表都建立了正确的联系，并在查询结果中包括了汇总所需要的数据，从而也建立“类别”表与“订单”表之间的联系。将计算销售额的字段的列标题命名为“销售额”。选择query菜单中的“文件” t “将数据返回 Microsoft Office Excel ”命令，此时query已经关闭，操作对象回到了 Excel ,单击“下一步”按钮，指定位置在“现有工作表”，单元格A3,单击“完成

10、”按钮。1U户TJ翠曰目J*S-teilWrUMM* Bfra.1*fn钾订咽曰期TWl盘掩FlMfl罔.埒华位嵌ismF砂再巧LW!fli扌口可网,t103 ?e-31 44 ME曰3 LP. nvsI 2口口3*0 环.i口曰 amriiTttinB Mh-i-H-BWMnrarjnfi i 龙OSDPPDT 1工三丸 e卩审京Rwr i 叫=fei 3et(F WMC 3 WOJHIC VS11SflE 車iB 血 R i as O CW3 的 H、naiwi日阿i佔廖i Fi u wi |声川jui i a爭仁整F 正悲衣11 vyti-LHd3u OD LIU1 W&-O9- 30

11、OO OO牆人孙打社iwa-1 in7 口口 口口1&9S-1 i-H OO OO i 歸讥nrb qq OO1MT-O1 口言 口口 Cl 口汕誉设lMT-tli-1 OO OO UMT-13IT - oq立丸ESI升+fr企业ma蜩皱切良仝価urFTjqnu 白白 uu1 MT-0i-O& OO OO1&3TDfi=3 口口 口忌曲血机1WT-07-07 OO OOI MT-O7-OD OO1!T DT- ES- 口口 口口ti-inn rtn1 TKir. rm E-n ci口 口口:性儘北毎曲 片济1牛“ ”尼丄莊云齐$录I牛W*U V MHf 匕!=卿 H M15i .03HUM步骤

12、2:汇总客户销售额排行榜，并排序。首先将“订购日期”拖至行标签，将“销售额”拖至数值，“类别名称”拖至列标签，为了能将销售额按照年度汇总，将光标停留在“行标签”下方的任何单元格，右击鼠标，选择“创建组（G）, ”命令，选择组合的步长为“年”。然后将行标签的字段名称 “订购日期”改为“订购年”，将它拉至报表筛选域， 将字段列表中的“公 司名称”字段拖到行标签处，让透视表按照行总计，从大到小排列，就得到了图表。2汇总前三大客户各月销售额，并绘制图形步骤1 :将实验要求1所汇总的数据透视表复制到新的工作表。步骤2:利用数据透视表，汇总前三大客户的销售额时间序列。按照实验要求1汇总的数据透视表，反映出

13、“高上补习班”、“正人资源”、“大钰贸易”是公司的前三大客户。点开“行标签”字段，勾选中这3个公司名称，并拖到列标签。将列标签的字段“类别名称”拖出数据透视表。将报表筛选中的字段“订购年”拖到到行标签，将其重新组合。选择组合量x（ 1-折扣）等字段，将所查询数据返回Excel。的步长为“月”和“年”，把字段名称修改为“订购年”与“订购月”。光标停留在数据表中任何单元格，右击鼠标，选择“数据透视表选项（0）, ”，打开后“布局和格式”选项卡中的“对于空单元格，显示”填写为“ 0”，此时得到的前三大客户销售额时间序列。O34BS-G796. BS99929月1B2. 4月O1丄月4971, O39

14、S977OBS-O 4：99Ob砂年月月冃月月月月月0J171245678-91 93157999434SS. 79994.OS796-659992OD1S2_ 4.O5275- 71496 5S275- 71496S904.91412035= 5445079973062. 34999Tioo33a11月诈月1S14b 8：3S49. 659iS4 921B它舍3舍Y3 15248. 97497O3119合9998日 0 4464 599997 15913 6754529 S2247. 09997B4S7. 7149S1172 8iS23x 45 0 550. 58796147255510 S

15、92461 1536 799994 3436 443498O13453P 67499 SS42-各合4自昌企 4707. 54：0 2944 399989 14333生 11S5 749997 9G281 12693 7499 5273lO2S2a 51498 12584.23252. 2897 15248 7497 3494.987465 2178 39999 6696. 342458 15629 49999 32043 86349 9907. S15亠._旦宇_.旦呈迪丄丁述 髙丁X学师突永五华椅盍苗通牢祥两远广%Sr71%1,12-24lT.OO41.125.24%22841-1%S.f

16、i%33J3%L1%&7悯35.82%7.938.26%P.O勺缶40.60*44245.04*412.441.1%13.5*49184.1.1%Sl.1541I15.7%53.0516.9%54.9018.0Sfi.7341-1%H%58.5214%20.2i60,17吉户百分晋尸齣察稍宫額孚 比计臣好比汁百分比选中单元格F3G92中的数据，单击“插入”选项卡t选择“散点图” t选择“带平滑线的散点图”， 单击该折线图，点击“布局”选项卡中的“网格线”按钮，点击“主要纵网格线”中的“主要网格 线”按钮，显示纵向的横坐标网格线。步骤4:在曲线上添加代表 20%客户数的垂直参考线。在单元格15:

17、17中输入“ 20%,在单元格J5与J7中输入“ 0”和“ 120%,在单元格J6中输入公 式：“ =INDEX(G4:G92, MATCH(I5,F4:F92,1),1 )”，即从客户累计百分比中，查找到 20%的客户数 在第几行，然后用INDEX函数查找该行对应的销售额累计百分比，计算结果如图 2-15所示。选中“图表工具”选项卡组中的“设计”选项卡，点击“选择数据”按钮打开“选择数据源”窗口，选中该窗口中的“添加”按钮，打开“编辑数据系列”窗口，在“系列名称”中输入散点图名称为 “20% 垂直参考线”，点击“ X轴系列值”后的拾取器，选中I5至I7的区域，点击“ Y轴系列值”后的拾取器，

18、选中J5至J7的区域，返回并点击“确定”按钮，回到“选择数据源”窗口，点击“确定” 按钮。即在前面所绘制的图表上，添加了一条垂直参考线，也就是在源数据中添加了一个系列，这 个系列散点图线的 X轴数据来自单元格I5:I7 , Y轴数据来自单元格 J5:J7，得到Pareto曲线。一誚客踊第訝旨甘比20K020常O.5S52352205$120%绘制按照订单汇总的销售额与销售次数Pareto曲线步骤1 :查询“订购日期”、“订单ID ”与“销售额”等数据。在一张空自的工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Micros

19、oft query ，从“订单”表、“订单明细”表中查询“订购日期”、“订单ID”与“销售额”（销售额=订单明细.单价X数量x（ 1-折扣）等字 段，将查询数据返回 Excel。步骤2:利用查询的数据，制作数据透视表。从数据透视表的字段列表中，选择“订购日期”字段，并将其拖至行标签，将“销售额”字段拖至“数值“区域。将“订购日期”字段按年组合，拖至“报表筛选”区域，将“订单ID ”字段拖至行标签，在“求和项：销售额”栏下任意位置单击右键打开并设置按销售额降序排列，得到按照年度和订单ID汇总的数据透视表。AaLrU1订购曰朗23f亍标整J求和峻：销售薇iloseslfi3B7a 4999951Q

20、9SJ1581031103012615. 057108891133031041711188, 4310S17109S2. S449S010S971035. 2411047910495. 621054010191, 731069110164. 84105159921 2999735103729210. 96 .104249194. 5599667110328902, 5e105148623- 45H1co cnrccu q步骤3:利用数据透视表的数据，计算客户数累计百分比与销售额累计百分比，绘制Pareto曲线。在单元格E3G3中依次输人说明文字：“销售次数百分比”、“销售次数累计百分比”、“销

21、售额累计 百分比”。按照图2-17所示输人公式，并复制到下方单元格至G833,设置E、F、G三列数值的显示形式为百分比，3位小数，单元格E4:E833中计算单次销售占总销售次数（即订单数）的百分比，单元格 F4:F833中汇总累计销售次数占总销售次数的百分比，即到该订单为止，已有订单数占到总订单数的百分比。单元格G4:G833中汇总到该订单为止，已有订单实现的销售额占总销售额的百分比In E4:=1/COUNT($A$4: $A$833)In F4:=SUM($E$4:E4)In G4:=SUM($B$4:B4)/SUM($B$4:$B$833)百科*匕l-l百:幻”匕芦HL匕即LJLJZU呼

22、如O .卫:ZQ号张O.2%4.4%5.32:3ri.l S：fl脾鼻7_B73IKJINOWbJL.NO如T -斗*3 7工!&%1丄Q.JLN打阴dm%毗 1I选中单元格F3G833中的数据，绘制“带平滑线的散点图”，并添加纵向网格线，设置横坐标小数位数为0位步骤4:在曲线上添加代表 20喘肖售次数的垂直参考线。在单元格15:17中输入“ 20%,在单元格J5与J7中输入“ 0”和“ 120%,在单元格J6中输入公式：“=INDEX(G4:G833,MATCH(I5,F4:F833,1),1)”，即从销售次数累计百分比中，查找20%勺销售次数在第几行，用INDEX函数查找，该行对应的销售额

23、累计百分比。在前面所绘制的图表上，添加一条垂直参考线。该参考线的X轴数据来自单元格I5:I7 , Y轴数据来自单元格 J5:J7 ,20%020%55略20%120%汇总各地区客户分布步骤1 :查询“公司名称”与“地区”等数据。将Excel 一张空白工作表命名为“ 5.各地区客户分布”，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query ,Excel。从“客户”表中查询“公司名称”与“地区”字段，然后将所查询的数据返回 步骤2:利用查询的数据，制作数据透视表。从数据透视表的字段列表中，选择“地区”字段，拖

24、至行标签。选择“公司名称”字段，拖至“数值”区域，得到按照地区汇总的客户数的数据透视表，步骤3:利用数据透视表的数据，制作数据透视图。光标停留在数据透视表中，点击“数据透视表工具”选项卡组中的“数据透视图”命令，选择“簇 状柱形图”，在当前工作表中建立数据透视图。汇总45绘制各地区平均销售额及销售额占总销售额百分比 步骤1 :查询“地区”与“销售额”等数据。在Excel的空白工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query，从“客户”和“订单明细”表中，查询客户的“地区”与“销售额”（销售额=

25、订单明细单价X数量X（ 1-折扣）字段，将查询数据返回Excel。查询时应包括“订单”表，该表能建立“客户”表和“订单明细”表之间的联系。步骤2:利用查询的数据，制作数据透视表。从数据透视表的字段列表中，选择“地区”字段，并将其拖至行标签，将“销售额”字段拖至“数值”区域，得到按照地区汇总的销售额的数据透视表,AEC1 :2行标签求和项:销售額3S2385 493658 278399 2410734 5 6 7 8 9齧slgIr1119418913512058441112步骤3:利用数据透视表的数据，计算各地区平均销售额与销售额占总销售额的百分比。在单元格D3:G3中依次输入说明文字：“地区

26、”、“客户数”、“平均销售额”与“销售额占总额百分比”。按照图2-22所示输入公式，设置平均销售额列显示方式为小数，小数位为0,百分比列显示方式为百分比，小数位为0。In D4:=A4In E4:=5.各地区客户分布!B4In F4:=B4/E4In G4:=B4/SUM($B$4:$B$9)DEFG3地区客户数平均销售额销售额占总额百分比4东北5104774%5华北411204039%6华东161740022%7华南201205419%在单元格 a1:b21中布置好公司从1987-2006年的销售量数据。然后，绘制公司从 1987年至在单元格 a1:b21中布置好公司从1987-2006年的

27、销售量数据。然后，绘制公司从 1987年至8西北255971%9西南72701915%实验小结：在实验六中，因为需要插入数据， 但是在自己的电脑中无法显示数据，所以只能复制指导书中的数据，文中已经用红色标出。综合这些实验，给我们最大的体会就是学会了如何查询数据。如何对一 个数据进行分析。思考题：你还能从哪些方面对客户的销售数据进行分析，帮助该公司促进销售或者为客户提供更好的服务？答：首先除了可以对客户的销售数据 进行汇总还可以对数据进行预测，从而得知客户下个季度的销售情况，从而可以进行相应的应对措施。同时可以按照客户的不同级别汇总各级别客户的总销售额，销售额占总销售额的比重，为不同的客户提供不

28、同的服务。帕累托曲线可以帮助分析投入与产出之间的关系，它还能帮助该公司进行哪些方面的分析？答：可以准确的找出销售量达到的层次，便于查看。实验三餐饮公司经营数据时间序列预测实验类型：验证性实验学时：2实验目的：理解指数平滑预测法的概念；掌握在excel中建立指数平滑预测模型的方法；掌握寻找最优平滑常数的各种方法。实验步骤：步骤1:确定时间序列的类型。2006年共20年的销售量折线图。AE-121967*.53a1T-Jl5現T611fl. T76.66flue9BBA.fl- 3104 11 1iwee. t12T- 11319966LSl日白白7 j.1 nZDOOT,S162001%孑172

29、0024 3ia2003札的19003加zooe忑212006步骤2 :利用“数据分析”工具中的指数平滑功能进行预测。在“文件”选项卡中选择“选项”，将打开“Excel选项”对话框，在该对话框的左边一栏里选择“加载项”，右边一栏的最下方单击“转到”按钮，Excel将显示“加载宏”对话框。在“加载宏”对话框中选择“分析工具库”，单击“确定”按钮，将会在“数据”选项卡下方出现“分析”组，其中包含“数据分析”选项，选中“数据分析”，在出现的“数据分析”对话框中选择“指数平滑”，在“指数平滑”对话框中，在“输入区域”输入“ b2:b21 ”单元格，“阻尼系数”输入“0.75 ” （注：阻尼系数=1-平

30、滑常数），在“输出区域”输入“c2 ”单元格，单击“确定”按钮。、运用指数平滑公式进行预测步骤1:在单元格fl中输入平滑常数0.25，在单元格c2中输入公式：“=b2”，作为第一年的预测值 （Fi）,在单元格c3中输入指数平滑模型预测公式“ =$f$1*b2+（1-$f$1）*c2将单元格C3往下复制，便得到 2007年的指数平滑预测值7.96 。F2-吞 E=AVERAGE.CCP2;&21-C2C21J舟C尸1ton干満前缺EL 2B21 9S7& 5唱.弓口wskIn.025 |3SPSSTF41 987e 3e. a J5丄 S3UO仇7血946tJ7?. 3671ET. 211993

31、氐忑7r 069 394眞17r 441O丄 EI?EB. 1a L11丄曰夕貯喜.7TT 96121997T1ie131卿靠S仇*0147. 17r砲15OD7- S7. 49162001S. 37u BT17002協1化751S2003守6甘.141 口20037- &歇25307-理Wl 1421zaoc肌97-Z2?0O77,卫&步骤2 :绘制指数平滑预测图。利用单元格a2:c22中的数据绘制公司销售量指数平滑预测图。念司甫售教呈楷竝平樹用测三、寻找最优的平滑常数步骤1:计算均方误差。单元格f2中输入公式：=average(b2:b21-c2:c21)A2)”，作为数组运算，需要同时按

32、Ctrl+Shift+E nter三个键作为输入结束，计算均方误差MSE步骤2:利用模拟运算表及查找引用函数功能，寻找最优平滑常数。单元格e7:e24中给出不同的平滑常数(大于0小于1)，在单元格f6中输入公式：“ =f2 ”，选定单元格 e6:f24，在“数据”菜单中选择“模拟运算表”。ABCELF1i -i平郃r栽1257,睾qI9Q91兀3尢平滑祕.D- 3E旧艸SL 7显S461991乐7I- 330-卵沾7汨专26L客化卽J.他4宫】曹曹*氐吞Tr 0$(L 巾01轉4& ?- 440.0.917410ISB蛍7-fil0.992S11&?札黯心恥0.8S24123 957& “a垂

33、山叫131998瞅耳化90(L 40(LUTPtii1419997 1T.flu 4508812152000T.ST. 490. 50W33200137. 570. 55山朋因172Q02&. 3T. 75a 631182003备&S. 144U9S9O192003T.S& 25o,jro0,SS47L2020057, 5-. 140,75山辟餌21sooe7,儿蜩心阳22T陽心S50.915b23LL卵0.92SS0屯-9T|0-941Q在单元格 f4 中输入公式：“ =index(e7:e24,match(min(f7:f24),f7:f24,0)”，找到最优平滑常数为 0.35。然后，根

34、据最优平滑常数 0.35 （将此值代入单元格fl中），2007年的预测值为7.94 。步骤3 :利用规划求解功能，寻找最优平滑常数。在“文件”选项卡中选择“选项”，将打开“ Excel选项”对话框，在该对话框的左边一栏里选择“加载项”，右边一栏的最下方单击“转到”按钮，Excel将显示“加载宏”对话框。在“加载宏”对话框中选择“规划求解加载项”，单击“确定”按钮，将会在“数据”选项卡 下方的“分析”组中出现“规划求解”选项，选中“规划求解 ”。 在设置 目标栏输入$f$2,然后点击“求解”按钮。实验3-2美食佳”公司月管理费预测实验目的：?理解移动平均预测法的概念；?掌握在exceI中建立移动

35、平均模型的方法；?掌握寻找最优移动平均跨度的各种方法。实验步骤：一、运用“数据分析”工具进行移动平均预测 步骤1：确定时间序列的类型。绘制公司从 2006年1月至2007年6月共18个月的管理费用折线图,公司莒理费用时间序列步骤2 :利用“数据分析”工具的移动平均功能进行预测。在“数据”选项卡中选择“数据分析”，在出现的“数据分析”对话框中选择“移动平均”在“移动平均”对话框中，在“输入区域”输入c2:c19 ”单元格，“间隔”输入“ 3”（注:移动平均跨度为 3），在“输出区域”输入“d3”单元格，单击“确定”按钮。A序号月桁G73o1 111 z4 5 6-760-0 12 3i 1 1

36、118:2 12007-07IS. 21*0 26 20. 3 20, 20. a 20. 7 20. 1乩1 i j. -ri. 721* 3i 21-03 20L 33、运用移动平均公式进行预测步骤1 ：利用average。函数计算移动平均预测值。在单元格g1中输入移动平均跨度3 ,在单元格d5中输入移动平均模型预测公式：“ =average(c2:c4) ”将单元格d5往下复制，便得到 2007年7月的移动平均预测值20.3 。D5* /；-AVERAGE（C2：C4）ACDEFG1丹営谨需移动宇咖厘3212Q0C-QI17M5E5.41322006-0221432006-0319Tq2

37、Q06-Mi2l + 0!52006-05762006-062020.03T2006-072220+3g耳2006-08IS20+01092006-09?pn, n102006-102020.712II2D06-1I1720.013122Q06-122219.714132IK7-QI2319.7151420（17-0?1920.71F152007=03212L317is2Q07-042h0IBn2007-052219.319182D07-0S2120.320192007-0720+3步骤2 :绘制移动平均预测图。利用单元格 c2:d20中的数据绘制公司18个月的管理费用及移动平均预测图。会司管

38、理黃用稽动平均预测三、寻找最优的移动平均跨度步骤1:计算均方误差。此处用到两个函数：sumxmy2()函数和 count()函数。sumxmy2()函数的功能是返回两数组中对 应数值之 差的平方和，它需要两 个参数，一个参数是第 一个数组或数 值区域， 另一个参数是第二个数组或数值区域。count()函数的功能是计算某一范围内包含数值的单元格的个数。在单元格g2中输入公式：“ =sumxmy2(c2:c19,d2:d19)/count(d2:d19)”，计算均方误差 MSE。步骤2 :利用ofset()函数辅助进行不同移动平均跨度下的预测。借助average。函数进行的移动平均计 算仅对跨度3

39、有效，若跨度改为其 他值，则要 修改average()函数的参数范围变化问题。average。函数的参数。为此引入ofset()函数解决offset()函数的功 能是以指定出)的范围可个参数，第(用负数表示向左(用负数的行数；第五格或单元格区域，并可以指定返回的行数或列数。它需要五参照系的基准位置；第二个参数是相对于这个基准位置向上正数表示)偏移的行数;第三个参数是相对于这个基准位置(用正数表示)偏移的列数；第四个参数是要返回数据范围回数据范围的列数。事实上前三个参数指定了要返回数据范给定偏移量得到新的范围。返回以为一个单元个参数是作为)或向下(用表示)或向右个参数是要返的范围为参照系，通过围

40、的起始单元格。在单元格 d5中输入公式:”，拖动单元格d5的填充柄向上复“ =if(a5=$g$1,average(ofset(d5,-$g$1,-1,$g$1,1)制至d2，向下复制至d20，从而可在变化的移动平均跨度下计算移动平均值。将单元格 g1中的移动平均跨度改为2，不必改动 d列的公式,D20呼t|* AVERAGE (OFFSEl(D20.-LACDE 1FGI1序寻221IT32213200S-031919.042006-01232D.C5SM4-051975sa2Q.$172M6-07229.09a20C6-08182L01092000-092220.01110200S-1D2

41、D20.012IL2006-111721.01312200S-1222!B-SM132007-0123142007-021922.516152007-032321.01710200T-(M1818172001-0S2219.6IS182007-06J2I20.1Q20192007-0111步骤3 :利用模拟运算表及查找引用函数功能，寻找最优移动平均跨度。g6中给出公式：“ =g2 ”，选定单元格在单元格f7:f21给出不同的移动平均跨度，在单元格f6:g21 ,在“数据”菜单中选择“模拟运算表”输入引用行的覃元格墮）：辑入引用列的无格；阿山區3在单元格g4 中输入公式：“ =index(f7:

42、f15,match(min(g7:g15),g7:g15,0)，找到最优移动平均跨度为5。实验思考1.可否利用规划求解功能，寻找最优的移动平均跨度?答：在实验3-2中，无法利用规划求解功能寻找最优的移动平均刻度。因为求MSE所用的公式为=SUMXMY2(C2:C19,D2:D19)/COUNT(D2:D19)”与移动平均刻度值所在的G1单元格无直接联系2. excel提供的移动平均趋势线功能也可进行移动平均预测，但趋势线方法与本实验所介绍的方法有何不同？答：Excel提供的移动平均趋势线方法与本实验所介绍的方法与本实验所介绍方法的区别在于趋势线的作用是对已知的一堆数据作回归分析，以找到一个可以

43、直接计算的方程式并对其他任意未经测量的数值进行计算。趋势线方法考虑了大量可能的结果。实验3-3美食佳”华东分公司销售额趋势预测实验目的?理解趋势预测法的概念；?掌握在excel中建立线性趋势预测模型的方法;?掌握寻找线性趋势模型参数的各种方法；?掌握线性趋势值预测的不同方法步骤1：确定时间序列的类型。公司销售额时问序列步骤2 :添加线性趋势线。在图中选中数据系列，右键菜单中选择“添加趋势线”，出现“添加趋势线”对话框。在“添加趋势线”对话框的“类型”中选择“线性”。在“添加趋 势线”对话框的“选项”中选择 “显示公式r 平方值公司销售额趙势预测步骤3 :用趋势线前推法大致预测线性趋势值。选定线

44、性趋势线，右键菜单中选择“趋势线格式”，在“趋势线格式”对话框中选定“选项”，将趋势预测前推1周期。步骤4 :用方程或函数准确预测线性趋势值。根据得到的线性趋势方程公式y=11.473x+861.98 ，如图3-28所示，在单元格c13中输入公式：“ =11.473*a13+861.98”，即将x=12 （ 2007年为第12个时间序列点）代入公式，计算得到 2007 年的预测值为 999.66 。利用 forecast() 函数，在单元格c14 中输入公式： “ =forecast(a13,c2:c12,a2:a12) ”，计算得到 2007 年的预测值为 999.65 。利用 trend(

45、) 函数，在单元格 c15 中输入公式： “ =trend(c2:c12,a2:a12,a13) ”，计算得到 2007 年的预测值为 999.65 。步骤 5：将预测结果在图中表示。同时选中单元格 b13 和 c13 ，并作为新数据点复制到图形的数据线上，趋势线前推只能在图中看到大致预测结果 1000 左右，实验思考1本实验的几张图中， x 轴是“分类”还是“自动” ？ 本实验(实验 3-3)中， X 轴是自动2预测点数据如果作为新数据系列添加到图形中，结果与图 3-29 有何不同？ 答：实验 3-3 中，预测点数据如果作为新数据系列添加到图形中，预测部分的值将是一条直线。 3为什么预测值一

46、定在趋势线的延伸线上？ 答：预测值一定在趋势线上的原因是预测值是依据趋势线作出来的。4若要预测公司 2008 年的全国销售额，可以怎么做？若要预测公司 2009 年、 2010 年、甚 至更远年份的销售额，会有什么问题？答：若要预测 2008 年的全国销售额， 可依据 2007年的预测值来作。 但若要预测更远年份的销售额， 则不能以之为基础由趋势线函数进行预测，因为彼时销售额呈线性增长，与客观事实不符。5除了本实验中介绍的添加趋势线方法可以找到线性趋势预测模型的参数外，还可以用 哪些方法找到线性趋势预测模型 y=a+bx 中的参数 a 和 b。答：还可用回归方法找到 Y=a+bX 中参数 a,

47、b 的值。实验 3-4 “美食佳 ”公司会员卡发行量趋势预测实验类型：验证性实验学时： 2实验目的: ?理解非线性趋势预测法的概念；?掌握在excel中建立非线性趋势预测模型的方法;?掌握非线性趋势值预测的方法。?预测公司2007年7月会员卡的发行量。实验步骤：步骤1:确定时间序列的类型。公司会员卡发行量时间序列步骤2 :添加非线性趋势线在图中选中数据系列，右键菜单中选择“添加趋势线”，出现“添加趋势线”对话框。在“添加趋势线”对话框的“类型”中选择“对数”。在“添加趋势线”对话框的“选项”中选中“显示公式”和“显示 r平方值”步骤3 :趋势线前推法大致预测非线性趋势值。选定对数趋势线，右键菜

48、单中选择“趋势线格式”，在“趋势线格式”对话框中选定“选项”。将趋势预测前推1周期。步骤4:用方程或函数准确预测非线性趋势值。根据得到的方程公式 y=7.7785In(x)+3.7651，在单元格 c16中输入公式：“ =7.7785*1n(a16)+3.7651 ”,即将x=15(2007年7月为第15个时间序列点)代入公式，计算得到2007年7月的会员卡发行预测值为24.83万张。步骤5:将预测结果在图中表示。趋势线前推只能在图中看到大致预测结果,公司会员卡发行量m酸性趋势预测实验思考1请试一下，图3-39可否考虑用xy散点图做？ 这时用什么数据作为 x轴合适？答：可以，用序号的对数值ln

49、(x)代替x，作为x轴合适，y与x是一次线性函数关系。2 为什么预测值一定在趋势线的延伸线上？答：预测值一定在趋势线上的原因是预测值是依据趋势线作出来的。3除了本实验中介绍的添加趋势线方法可以找到对数趋势预测模型的参数外，是否可以用规划求 解法找到对数趋势预测模型y=a+bln(x)中的参数a和b ?答：还可用回归方法找到Y=a+bX中参数a,b的值。实验3-5美食佳”火锅连锁店原料年度采购成本预测实验目的：?理解季节指数的概念；?掌握季节指数预测方法。实验步骤： 步骤1:确定时间序列的类型。公司采购成本时间序列步骤2 :计算季节指数。一年有4个季度，所以以4为移动平均跨度，计算移动平均数，其

50、结果应该对应放在每4个季度的中间位置。但当移动平均跨度为 4时，没有中间季度位置可放， 因此只能放在第3个季度对应的位 置处。所以从第一年开始， 4个季度的移动平均数放在单元格 d4中，即在单元格 d4中输入公式：“ =average(c2:c5) ”。拖动单元格 d4的填充柄复制到单元格 d5:d16中，在单元格e4中输入公式：“ =(d4+d5)/2 ”，并将它复制到单元格e5:e15内。由此完成中心化移动平均数的计算。把中心化后的移动平均数单元格e2:e17添加到图3-41所示的折线图中，得到如图3-44的结果。可见中心化的移动平均数体现了原材料采购成本的稳定水平，即在一定程度上消除了原

51、材料采购成本时间序列的不规则成分。在单元格f4中输入公式：“ =c4/e4”，并将它复制到单元格f5:f15中，由此完成季节不规则值的计算。 分别计算每一年对应季度的不规则值的平均值，就可得到各个季度的季节指数。如在单元格i2中输入公式：“ =average(f2,f6,f10,f14) ”，可计算得到第1季度的季节指数。公司采购成本季书揭数预劃用每一个季节指数除以未调整的季节指数之和再乘以季度指数总和4。如图3-43中单元格i6所示,未调整前的季节指数之和为 3.9852，所以需要调整。在单元格 j2中输入公式：“ =i2/$i$6*4 ”，往下 复制到j3:j5，得到调整后的季节指数。步骤

52、3:消除季节影响。将调整后的季节指数复制到E列，分别对应2003-2007年的4个季度。F2农=D2/E2ABCDEF1序号年季度采购成本调整后季 节指数消除季 节影响2120帕年详度1竄01.210S413.2 I.322李度匸0 0. 3304621. 2433季度1 0 0. 2092719. 1544季度E1. 02. 2494422. 765200 Q 年译度28. 01. 2108423. 1762季度90. 3304629.4E7$季度&. T0. 2092732. 0984季度7L52. 2494431.510g加05年1李度45. 0 11.2108437. 211102季度

53、12, 60. 330463S. 112113季度10. 20. 2092748. 71312q季度105. 02. 2494446. 7142006】季度阪01.2108445.4151415.20. 3304646. 016IE；?季度14.10. 2092767, Q171&4季庚114. 02. 24S4450. 7LY加笛年1季度1. 2108419182季度0. 330462Q19痔度0. 2092721204季度2. 24944在单元格F2中输入公式：“ =D2/E2 ”，将公式复制到单元格F3:F17中，得到消除季节影响后的结果。利用单元格F2:F17中的数据绘制公司从 200

54、3年第1季度到2006年第4季度共16个季度的原材料步骤4:计算预测值。在单元格H2中输入公式：“ =G2*E2 ”，将公式复制到单元格 H3:H21中，即在线性趋势预测值的基础上乘以调整后的季节指数得到最终的季节预测值。公司2007年1至4季度的采购成本预测值分别为73.0、20.9、13.8、154.9。根据D列的原始采购成本数据和H列的季度预测值数据，作折线实验思考1图3-47中的“序号”一列有什么用？答：图3-47中 序号”一列的作用是为趋势线公式的获得提供依据（作为自变量X）。计算趋势预测值时，若不用forcast（）函数，还可以有什么方法？请至少用两种方法试试看。答：计算趋势预测值

55、还可用移动平均预测法、指数平滑预测法、一元线性回归分析模型等。季节指数模型是否只能用于季节数据的预测？若是年度、月度、甚至周数据，可以用季节指数模型吗？答：季节指数模型不是只能用于季节数据的预测，年度、月度、周数据等在某些情况下均能用季节指数模型。实验小结：在实验中遇到的问题是，在进行指数平滑求解时，最优的平滑指数求法很麻烦，在多次进行求解后才可以得到。思考题：为什么用模 拟运算表加查找引用函数功能，得到的最优平滑常数（0.35 ），与用 规划求解功能得到的结果（0.37 ）不一样？答：用模拟运算表加查找引用函数功能，得到的最优平滑指数(0.35 ),与规划求解的结果(0.37 )比一样的原因

56、是因为系统误差的存在。可否调整模拟运算表的输入数据间隔，再试一试，结果会如何？答：结果不变，因为模拟运算表只是将数据代入变量中来求得对应的值，所得到的值与数据的间 隔无关。实验四 住房建筑许可证数量的回归分析实验类型：验证性实验学时：2实验目的：理解一元线性回归分析的概念；针对不同的问题，能够建立适当的一元线性回归模型；掌握内建函数slope()、intercept()与linest()的用法；掌握用规划求解法、添加线性趋势线法、回归分析报告法确定线性回归方程的系数；给定自变量的情况下，根据线性回归模型预测因变量的值。实验步骤：步骤1：确定因变量与自变量并输入观测值。根据实验要求，我们确定因变

57、量为建筑许可证的颁发数量，自变量为人口密度，并将数据合理的布置在excel工作表的单元格a1:b19中AB讦可 讣的简塩臣卑的人口27S2031&1204623G1O&705111G06打丁 00119007&44412320S680014340會1590010T5711113001龙S1Q03010013201 SO1462302210015832023000It-84 S024050IT910025300isSi 3019驷mo2700步骤2:绘制因变量与自变量关系散点图。利用图中的数据，以每平方公里的人口密度为x值，建筑许可证的颁发数量为y值，绘制xy散点图。30000崖這许前证的颉贡數

58、量与蓦平方公里人口密庚关果裁点图25000# +20000 *150D0-* *100DD-:*-5000101il50006(10070008000900010000步骤3:求出回归系数a、b的取值，计算判定系数R2，并进行预测。excel提供了几种不同的工具，包括规划求解工具，intercept()、slope()与linest()等内建函数，在散点图中添加趋势线和趋势线方程以及生成回归分析报告等方法来确定回归系数a和b。我们这里介绍利用规划求解的方法来求解回归系数。步骤4 :假定回归系数的值，建立线性回归模型。假定回归系数的值为a=1，b=1并将之放在单元格f2:f3中。用回归直线方 程

59、y=a+bx以及每平方公里的人口密度来计算建筑许可证的颁发数量预测值，放在单元格c2中，即在单元c2中输入公式“=$f$2+$f$3*a2 ”，并将此公式复制到c3:c19中，得到建筑许可证的颁发数量预测值。在单元格f5中计算建筑许可证的颁发数量观测值与预测值的均方误差mse，即在单元格f5中输入公式“=average(c2:c19-b2:b19)A2)” (注：其中的花括号不是直接输入，是将所有内容输入完后按住ctrl+shift键后再按回车键生成的)。在“规划求解参数”对话框中将目标单元格设为$f$5，使其等于最小值，将可变单元格ACLiE1里的人口 至席崖轴午可 证旳务浚点讲可证魏#25

60、9517S2O5952a f SE J1354&b I *4 和1432 3 a1OSTQ6237R干右O. S 365 352(51564利M 160MSEI27SS10CTT. 7&37401U 1 SQQ7417&44412920S厳韋0014340SSOl盘壬方英空旳人 口誉fitTOGO97宜401&0OOT24L建fit许可证旳頤汝勲重TOO!107571laooaT5T2117 06&1930070612810011 DO*31Q113810201301031422100$231158320230003321161胸024 OSOM&l17号丄0口ZB 300910113S*13