上网书店实验数据分析报告大学论文.doc

数据分析实验报告

20 15 - 20 16 学年 第 一 学期
班 级：
学 号：
姓 名：
授课教师： 况湘玲 实验教师：况湘玲

实验一 网上书店数据库的创建及其查询
实验类型：验证性 实验学时：2
实验目的：理解数据库的概念；
理解关系（二维表）的概念以及关系数据库中数据的组织方式；
了解数据库创建方法。
实验步骤：
步骤1：选择“bookstore”数据源，进入“查询设计”窗口。
步骤2：选择查询中需要使用的表。
在“添加表”对话框的“表”列表中分别选择“书”和“出版社”表，并单击“添加” 按钮将它们添加至表窗格。

查询设计4—低价图书信息查询
步骤1：选择数据源并添加表。
选择“bookstore”数据源，进入“查询设计”窗口。在“添加表”对话框中选择“书”
表，将其添加到“表”窗格中。
步骤2：选择字段。
在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、“出版年份”和“单价” 字段。
步骤3：设置查询条件，显示查询结果。
在“条件”窗格的“条件字段”行的第一列中选择“单价”，并在下一行中输入“<10 ”

查询设计5—新书信息查询
步骤1：选择“bookstore”数据源并添加“书”和“作者”表。
步骤2：选择字段。
在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、“单价”，以及“作者”
表的“姓名”字段。
步骤3：设置查询条件，显示查询结果。
在“条件”窗格的“条件字段”行的第一列中选择“出版年份”，并在下一行中输入“2007”

实验 1-3 “响当当”网上书店会员分布和图书销售信息查询
实验目的
•掌握复杂的数据查询方法：多表查询、计算字段和汇总查询
步骤1：选择“bookstore”数据源并添加“会员”表。
步骤2：选择分类字段、汇总字段和汇总方式。
本查询的分类字段是会员的城市，汇总字段是会员号，汇总方式是计数。在“查询设计”
窗口的“表”窗格中，双击“会员”表的“城市”和“会员号”字段。然后双击“会员号”字段的列标，在“编辑列”对话框中输入列标“会员人数”，并选择汇总方式：“计数”，

步骤3：设置查询条件。
若仅仅想了解上海和北京的会员人数，可以在条件窗格中设置相应的条件，

二、查询设计2—图书总订购量和总销售金额查询
步骤1：选择“bookstore”数据源，并添加“订单”、“订单明细”和“书”表。
步骤2：选择分类字段和汇总字段。
在“查询设计”窗口的“表”窗格中，双击“订单”表的“订购日期”、“订单明细”表的“订购数量”字段。另外还要构造一个计算字段“销售金额”，方法是直接在某空白列的列标中输入公式；在上面的字段中，“订购数量”和“销售金额”是汇总字段，而分类字段是“订购年份”，该字段也是一个计算字段，可以利用 year()函数将订购日期转换成订购年份，方法是双击“订购日期”列的列标，在编辑列对话框的字段项中输入公式“year(订购日期)”，在其中的列标项中输入“订购年份”；
然后分别双击“订购数量”和“订购数量*单价”字段的列标，在编辑列对话框的列标项中分别输入“总
订购数量”和“总销售金额”。

步骤3：设置查询条件。
在“条件”窗格的“条件字段”行的第一列中选择“订购日期”，并在下一行中输入
“>=2005-7-1 and <=2006-6-30”后回车，即可在“查询结果”窗格中显示2005上半年和2006下半年的
图书总订购量和总销售金额。再查询设计窗口中增加一个“订购月份”分类字段。

三、查询设计3—会员订购图书详细信息查询
步骤1：选择“bookstore”数据源并添加“会员”、“订单”、“订单明细”和“书”表。在表之间建立合适的联系。
步骤2：选择字段。
在“查询设计”窗口的“表”窗格中，双击“会员”表的“城市”、“会员号”、“姓名”字段，“订单”表的“订单号”字段、“书”表的“书名”字段和“订单明细”表的“订购数量”字段。
步骤3：规定查询结果的排序方式。
选择“记录”菜单的“排序”命令，在随后出现的“排序”对话框中设置排序方式，

四、查询设计4—各城市会员图书订购数量和销售金额统计 书店工作人员想了解位于各个城市的会员在网上订购的图书的总订购数量和总销售金额。
步骤1：选择“bookstore”数据源并添加“会员”、“订单”、“订单明细”和“书”表。在表之间建立合适的
联系。
步骤2：选择分类字段和汇总字段。
在“查询设计”窗口的“表”窗格中，双击“会员”表的“城市”、“订单明细”表的“订购数量” 字段。另外还要构造一个计算字段“销售金额”，方法是直接在某空白列的列标中输入公式“订购数量*单价”；
步骤3：规定汇总方式。
分别双击“订购数量”和“订购数量*单价”字段的列标，在编辑列对话框的列标项中分别输入“总订购数量”和“总销售金额”字样，并在总计项中选择“求和”。

五、查询设计5—被订购图书的作者和出版社信息查询 书店工作人员想了解2007年会员“刘丹”所订购图书的作者以及出版社的信息。
步骤1：选择“bookstore”数据源并添加“会员”、“订单”、“订单明细”、“书”、“作者”和“出版社”表。
在表之间建立合适的联系。
步骤2：选择查询字段。
在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、“作者”表的“姓名”和“出版社”
表的“出版社名称”字段。
步骤3：添加查询条件。 在条件窗格中添加关于订购日期和会员姓名的条件。
步骤4：查看查询结果。 会员“刘丹”在2007年共订购了7本图书。

实验小结：
其实在这第一个实验中感觉还是挺简单的，只要找到每一个表之间的联系就会很容易做。特别注意
的在设计查询时记住把“向导”关闭。然后就是在实验三中需要计算销售额，这个时候就要在表中
添加“单价”，只有这样才可以计算销售额。
思考题：
1,在数据查询过程中，如果所选择的某个表与其他表之间没有联系的话，会产生什么问题？
答：如果所选择的某个表与其他表之间没有联系，那么所得出的数据就会是错误的，并且很难继续
下面的步骤。

2,若“响当当”网上书店的某个会员想了解自己最近 2 年的图书订购情况，请为他设计一个查询。
步骤1：选择数据源并添加表。
选择“bookstore”数据源，进入“查询设计”窗口。在“添加表”对话框中选择“书”
表，将其添加到“表”窗格中。
步骤2：选择字段。
在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、，订单，订单明细。
步骤3：设置查询条件，显示查询结果。
在“条件”窗格的“条件字段”行的第一列中选择“订购日期”，并在下一行中选择“值域”，选择“2006-2007年”。

1在进行汇总查询的过程中，如果被选择的字段除了分类字段和汇总字段以外还包含了其他字段，
查询结果是否正确？为什么？请举例说明。
答：不正确，如果还有其他字段，quary将会把多余的字段自动作为分类字段。
2“响当当”网上书店的管理人员想了解最近2年中哪位作者的书最畅销，请你设计一个查询找到相关作者。

通过汇总发现近两年shammas,Namir c.和wellin, paul的书最畅销。
实验二企业销售数据的分类汇总分析
实验类型：验证性 实验学时：2
实验目的：理解数据分类汇总在企业中的作用与意义；
掌握数据透视表工具的基本分类汇总功能；
掌握建立分类汇总数据排行榜、生成时间序列、绘制pareto曲线图、计算各地区客户分布、统计各地区客户的平均销售额和大宗销售时间序列的方法和步骤。
实验步骤：
步骤I：获取各客户每笔销售的销售额、销售产品的类别和时间。
在一张空自的工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，随后启动了Microsoft query，选择所建立的连接到Northwind.mdb数据库的ODBC数据源---“nw”，并选中“选择数据源”窗口下方“使用‘查询向导’创建/编辑查询”选项，单击“确定”按钮，选择“客户”表中的“公司名称”、“订单”表中的“订购日期”以及“类别”表中“类别名称”字段，随后query弹出窗口显示“‘查询向导’无法继续，因为该表格无法链接到您的查询中。您必须在Microsoft query中的表格之间拖动字段，人工链接。”这是因为“类别”表无法同“订单”表建立联系。单击“确定”按钮。
要查询销售额，需要在query中首先增加“订单明细”表，利用其中的“单价”、“数量”与“折扣”字段中的数据，计算销售额。在“数据”窗格中一个空白字段的名称处输人公式“订单明细.单价*数量*(1-折扣)”，按回车键后就可以计算出销售额。随后，将“产品”表也添加到查询中，虽然查询结果中并不包括任何“产品”表的字段，但是该表能够建立“类别”表与“订单明细”表之间的联系(“订单明细”表指明所订购产品的ID，“产品”表指明该产品属于哪一个类别)。此时，查询中的表都建立了正确的联系，并在查询结果中包括了汇总所需要的数据，从而也建立“类别”表与“订单”表之间的联系。将计算销售额的字段的列标题命名为“销售额”。选择query菜单中的“文件”→“将数据返回Microsoft Office Excel”命令，此时query已经关闭，操作对象回到了Excel，单击“下一步”按钮，指定位置在“现有工作表”，单元格A3，单击“完成”按钮。

步骤2：汇总客户销售额排行榜，并排序。
首先将“订购日期”拖至行标签，将“销售额”拖至数值，“类别名称”拖至列标签，为了能将销售额按照年度汇总，将光标停留在“行标签”下方的任何单元格，右击鼠标，选择“创建组(G)…”命令，选择组合的步长为“年”。
然后将行标签的字段名称“订购日期”改为“订购年”，将它拉至报表筛选域，将字段列表中的“公司名称”字段拖到行标签处，让透视表按照行总计，从大到小排列，就得到了图表。
2汇总前三大客户各月销售额，并绘制图形
步骤1：将实验要求1所汇总的数据透视表复制到新的工作表。
步骤2：利用数据透视表，汇总前三大客户的销售额时间序列。
按照实验要求1汇总的数据透视表，反映出“高上补习班”、“正人资源”、“大钰贸易”是公司的前三大客户。点开“行标签”字段，勾选中这3个公司名称，并拖到列标签。将列标签的字段“类别名称”拖出数据透视表。将报表筛选中的字段“订购年”拖到到行标签，将其重新组合。选择组合的步长为“月”和“年”，把字段名称修改为“订购年”与“订购月”。光标停留在数据表中任何单元格，右击鼠标，选择“数据透视表选项(O)…”，打开后“布局和格式”选项卡中的“对于空单元格，显示”填写为“0”，此时得到的前三大客户销售额时间序列。

步骤3：绘制前三大客户销售额时间序列图。
光标停留在数据透视表的数据区域中，选择“数据透视表工具”选项卡中的“选项”卡→“数据透视图”命令，在当前工作簿自动插入一张图表。选择“折线图”→“带数据标记的折线图”命令。随后，再对该图的大小、外观以及数据系列的格式加以调整，就能得到”北风”贸易公司前三大客户销售额时间序列图。

3.绘制按照客户汇总的客户数与销售额Pareto曲线
步骤l：查询“订购日期”、客户的“公司的名称”与“销售额”等数据。
在一张空自的工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query，从“订单”表、“订单明细”表与“客户”表中查询“订购日期”、客户的“公司名称”与“销售额”(销售额=订单明细.单价×数量×（1-折扣））等字段，将所查询数据返回Excel。
步骤2：利用查询的数据，制作数据视表。
从数据透视表的字段列表中，选择“订购日期”字段，并将其拖至行标签，将“销售额”字段拖至“数值”区域。将“订购日期”字段按年组合，然后拖至“报表筛选”区域，将“公司名称”字段拖至行标签，在“行标签”上双击，输入“公司名称”，将“销售额”设置为降序排列字段。在“求和项：销售额”栏下点右键，点“值字段设置(N)…”选项，点击“值显示方式”选项卡，设置“值显示方式”为“列汇总的百分比”，得到按照年度和客户公司名称汇总的数据透视表。

步骤3：利用透视表的数据，计算客户数累计百分比与客户销售额累计百分比，绘制Pareto曲线。
在单元格D3:G3中依次输入说明文字，“公司名称”、“客户百分比”、“客户数累计百分比”、“销售额累计百分比”。按照图2-12所示输入公式，并向下拖动，将E列和F列显示形式设置为百分比，保留1位小数，得到如图2-13所示的汇总数据。
In D4:
=A4
In E4:
=1/COUNTA($D$4:$D$92)
In F4:
=SUM($E$4:E4)
In G4:
=SUM($B$4:B4)

选中单元格F3:G92中的数据，单击“插入”选项卡→选择“散点图”→选择“带平滑线的散点图”，单击该折线图，点击“布局”选项卡中的“网格线”按钮，点击“主要纵网格线”中的“主要网格线”按钮，显示纵向的横坐标网格线。步骤4：在曲线上添加代表20%客户数的垂直参考线。
在单元格I5:I7中输入“20%”，在单元格J5与J7中输入“0”和“120%”，在单元格J6中输入公式：“=INDEX（G4:G92，MATCH(I5,F4:F92,1),1）”，即从客户累计百分比中，查找到20%的客户数在第几行，然后用INDEX函数查找该行对应的销售额累计百分比，计算结果如图2-15所示。选中“图表工具”选项卡组中的“设计”选项卡，点击“选择数据”按钮打开“选择数据源”窗口，选中该窗口中的“添加”按钮，打开“编辑数据系列”窗口，在“系列名称”中输入散点图名称为“20%垂直参考线”，点击“X轴系列值”后的拾取器，选中I5至I7的区域，点击“Y轴系列值”后的拾取器，选中J5至J7的区域，返回并点击“确定”按钮，回到“选择数据源”窗口，点击“确定”按钮。即在前面所绘制的图表上，添加了一条垂直参考线，也就是在源数据中添加了一个系列，这个系列散点图线的X轴数据来自单元格I5:I7，Y轴数据来自单元格J5:J7，得到Pareto曲线。

4.绘制按照订单汇总的销售额与销售次数Pareto曲线
步骤1：查询“订购日期”、“订单ID”与“销售额”等数据。
在一张空自的工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query，从“订单”表、“订单明细”表中查询“订购日期”、“订单ID”与“销售额”（销售额=订单明细.单价×数量×（1-折扣））等字段，将查询数据返回Excel。
步骤2：利用查询的数据，制作数据透视表。
从数据透视表的字段列表中，选择“订购日期”字段，并将其拖至行标签，将“销售额”字段拖至“数值“区域。将“订购日期”字段按年组合，拖至“报表筛选”区域，将“订单ID”字段拖至行标签，在“求和项：销售额”栏下任意位置单击右键打开并设置按销售额降序排列，得到按照年度和订单ID汇总的数据透视表。

步骤3：利用数据透视表的数据，计算客户数累计百分比与销售额累计百分比，绘制Pareto曲线。
在单元格E3:G3中依次输人说明文字：“销售次数百分比”、“销售次数累计百分比”、“销售额累计百分比”。按照图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)

选中单元格F3:G833中的数据，绘制“带平滑线的散点图”，并添加纵向网格线，设置横坐标小数位数为0位
步骤4：在曲线上添加代表20%销售次数的垂直参考线。
在单元格I5:I7中输入“20%”，在单元格J5与J7中输入“0”和“120%”，在单元格J6中输入公式：“=INDEX（G4:G833,MATCH(I5,F4:F833,1),1）”，即从销售次数累计百分比中，查找20%的销售次数在第几行，用INDEX函数查找，该行对应的销售额累计百分比。在前面所绘制的图表上，添加一条垂直参考线。该参考线的X轴数据来自单元格I5:I7，Y轴数据来自单元格J5:J7，

5.汇总各地区客户分布
步骤1：查询“公司名称”’与“地区”等数据。
将Excel一张空白工作表命名为“5.各地区客户分布”，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query，从“客户”表中查询“公司名称”与“地区”字段，然后将所查询的数据返回Excel。
步骤2：利用查询的数据，制作数据透视表。
从数据透视表的字段列表中，选择“地区”字段，拖至行标签。选择“公司名称”字段，拖至“数值”区域，得到按照地区汇总的客户数的数据透视表，

步骤3：利用数据透视表的数据，制作数据透视图。
光标停留在数据透视表中，点击“数据透视表工具”选项卡组中的“数据透视图”命令，选择“簇状柱形图”，在当前工作表中建立数据透视图。

6.绘制各地区平均销售额及销售额占总销售额百分比
步骤1：查询“地区”与“销售额”等数据。
在Excel的空白工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query，从“客户”和“订单明细”表中，查询客户的“地区”与“销售额”（销售额=订单明细.单价×数量×（1-折扣））字段，将查询数据返回Excel。查询时应包括“订单”表，该表能建立“客户”表和“订单明细”表之间的联系。
步骤2：利用查询的数据，制作数据透视表。
从数据透视表的字段列表中，选择“地区”字段，并将其拖至行标签，将“销售额”字段拖至“数值”区域，得到按照地区汇总的销售额的数据透视表，

步骤3：利用数据透视表的数据，计算各地区平均销售额与销售额占总销售额的百分比。
在单元格D3:G3中依次输入说明文字：“地区”、“客户数”、“平均销售额”与“销售额占总额百分比”。按照图2-22所示输入公式，设置平均销售额列显示方式为小数，小数位为0，百分比列显示方式为百分比，小数位为0。
In D4:
=A4
In E4:
=’5.各地区客户分布’!B4
In F4:
=B4/E4
In G4:
= B4/SUM($B$4:$B$9)

D
E
F
G
3
地区
客户数
平均销售额
销售额占总额百分比
4
东北
5
10477
4%
5
华北
41
12040
39%
6
华东
16
17400
22%
7
华南
20
12054
19%
8
西北
2
5597
1%
9
西南
7
27019
15%

实验小结：
在实验六中，因为需要插入数据，但是在自己的电脑中无法显示数据，所以只能复制指导书中的数
据，文中已经用红色标出。综合这些实验，给我们最大的体会就是学会了如何查询数据。如何对一
个数据进行分析。
思考题：
1. 你还能从哪些方面对客户的销售数据进行分析，帮助该公司促进销售或者为客户提供
更好的服务？
答：首先除了可以对客户的销售数据进行汇总还可以对数据进行预测，从而得知客户下个季度的销售情况，从而可以进行相应的应对措施。同时可以按照客户的不同级别汇总各级别客户的总销售额，销售额占总销售额的比重，为不同的客户提供不同的服务。
2. 帕累托曲线可以帮助分析投入与产出之间的关系，它还能帮助该公司进行哪些方面的分析？
答：可以准确的找出销售量达到的层次，便于查看。
实验三 餐饮公司经营数据时间序列预测
实验类型：验证性 实验学时：2
实验目的：
理解指数平滑预测法的概念；
掌握在excel中建立指数平滑预测模型的方法；
掌握寻找最优平滑常数的各种方法。
实验步骤：
步骤1：确定时间序列的类型。
在单元格a1:b21中布置好公司从1987-2006年的销售量数据。然后，绘制公司从1987年至2006年共20年的销售量折线图。

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

二、运用指数平滑公式进行预测
步骤1：在单元格f1中输入平滑常数0.25，在单元格c2中输入公式：“=b2”，作为第一年的预测值（F1），在单元格c3中输入指数平滑模型预测公式“=$f$1*b2+(1-$f$1)*c2”。将单元格c3往下复制，便得到2007年的指数平滑预测值7.96。

步骤2：绘制指数平滑预测图。
利用单元格a2:c22中的数据绘制公司销售量指数平滑预测图。

三、寻找最优的平滑常数
步骤1：计算均方误差。
单元格f2中输入公式：“=average((b2:b21-c2:c21)^2)”，作为数组运算，需要同时按Ctrl+Shift+Enter三个键作为输入结束，计算均方误差MSE。
步骤2：利用模拟运算表及查找引用函数功能，寻找最优平滑常数。
单元格e7:e24中给出不同的平滑常数（大于0小于1），在单元格f6中输入公式：“=f2”，选定单元格e6:f24，在“数据”菜单中选择“模拟运算表”。

在单元格f4中输入公式：“=index(e7:e24,match(min(f7:f24),f7:f24,0))”，找到最优平滑常数为0.35。然后，根据最优平滑常数0.35（将此值代入单元格f1中），2007年的预测值为7.94。
步骤3：利用规划求解功能，寻找最优平滑常数。
在“文件”选项卡中选择“选项”，将打开“Excel选项”对话框，在该对话框的左边一栏里选择“加载项”，右边一栏的最下方单击“转到”按钮，Excel将显示“加载宏”对话框。在 “加载宏”对话框中选择“规划求解加载项”，单击“确定”按钮，将会在“数据”选项卡下方的“分析”组中出现“规划求解”选项，选中“规划求解”。 在设置目标栏输入$f$2,然后点击“求解”按钮。
实验3-2 “美食佳”公司月管理费预测
实验目的：
• 理解移动平均预测法的概念；
• 掌握在excel中建立移动平均模型的方法；
• 掌握寻找最优移动平均跨度的各种方法。
实验步骤：
一、运用“数据分析”工具进行移动平均预测
步骤1：确定时间序列的类型。
绘制公司从2006年1月至2007年6月共18个月的管理费用折线图，

步骤2：利用“数据分析”工具的移动平均功能进行预测。
在“数据”选项卡中选择“数据分析”，在出现的“数据分析”对话框中选择“移动平均”，
在“移动平均”对话框中，在“输入区域”输入“c2:c19”单元格，“间隔”输入“3”（注：移动平均跨度为3），在“输出区域”输入“d3”单元格，单击“确定”按钮。

二、运用移动平均公式进行预测
步骤1：利用average()函数计算移动平均预测值。
在单元格g1中输入移动平均跨度3，在单元格d5中输入移动平均模型预测公式：“=average(c2:c4)”。将单元格d5往下复制，便得到2007年7月的移动平均预测值20.3。

步骤2：绘制移动平均预测图。
利用单元格c2:d20中的数据绘制公司18个月的管理费用及移动平均预测图。

三、寻找最优的移动平均跨度
步骤1：计算均方误差。
此处用到两个函数：sumxmy2()函数和count()函数。sumxmy2()函数的功能是返回两数组中对应数值之差的平方和，它需要两个参数，一个参数是第一个数组或数值区域，另一个参数是第二个数组或数值区域。count()函数的功能是计算某一范围内包含数值的单元格的个数。在单元格g2中输入公式：“=sumxmy2(c2:c19,d2:d19)/count(d2:d19)”，计算均方误差MSE。
步骤2：利用offset()函数辅助进行不同移动平均跨度下的预测。
借助average()函数进行的移动平均计算仅对跨度3有效，若跨度改为其他值，则要修改average()函数的参数。为此引入offset()函数解决average()函数的参数范围变化问题。offset()函数的功能是以指定的范围为参照系，通过给定偏移量得到新的范围。返回（求出）的范围可以为一个单元格或单元格区域，并可以指定返回的行数或列数。它需要五个参数，第一个参数是作为参照系的基准位置；第二个参数是相对于这个基准位置向上（用负数表示）或向下（用正数表示）偏移的行数；第三个参数是相对于这个基准位置向左（用负数表示）或向右（用正数表示）偏移的列数；第四个参数是要返回数据范围的行数；第五个参数是要返回数据范围的列数。事实上前三个参数指定了要返回数据范围的起始单元格。
在单元格d5中输入公式：
“=if(a5<=$g$1,"",average(offset(d5,-$g$1,-1,$g$1,1)))”，拖动单元格d5的填充柄向上复制至d2，向下复制至d20，从而可在变化的移动平均跨度下计算移动平均值。
将单元格g1中的移动平均跨度改为2，不必改动d列的公式，

步骤3：利用模拟运算表及查找引用函数功能，寻找最优移动平均跨度。
在单元格f7:f21给出不同的移动平均跨度，在单元格g6中给出公式：“=g2”，选定单元格f6:g21，在“数据”菜单中选择“模拟运算表”，

在单元格g4中输入公式：“=index(f7: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提供的移动平均趋势线方法与本实验所介绍的方法与本实验所介绍方法的区别在于趋势线的作用是对已知的一堆数据作回归分析，以找到一个可以直接计算的方程式并对其他任意未经测量的数值进行计算。趋势线方法考虑了大量可能的结果。
实验3-3 “美食佳”华东分公司销售额趋势预测
实验目的
• 理解趋势预测法的概念；
• 掌握在excel中建立线性趋势预测模型的方法；
• 掌握寻找线性趋势模型参数的各种方法；
• 掌握线性趋势值预测的不同方法
步骤1：确定时间序列的类型。

步骤2：添加线性趋势线。
在图中选中数据系列，右键菜单中选择“添加趋势线”，出现“添加趋势线”对话框。
在“添加趋势线”对话框的“类型”中选择“线性”。在“添加趋势线”对话框的“选项”中选择“显示公式”和“显示r平方值”。
步骤3：用趋势线前推法大致预测线性趋势值。
选定线性趋势线，右键菜单中选择“趋势线格式”，在“趋势线格式”对话框中选定“选项”，将趋势预测前推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()函数，在单元格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．为什么预测值一定在趋势线的延伸线上？
答：预测值一定在趋势线上的原因是预测值是依据趋势线作出来的。
4．若要预测公司2008年的全国销售额，可以怎么做？若要预测公司2009年、2010年、甚至更远年份的销售额，会有什么问题？
答：若要预测2008年的全国销售额，可依据2007年的预测值来作。但若要预测更远年份的销售额，则不能以之为基础由趋势线函数进行预测，因为彼时销售额呈线性增长，与客观事实不符。
5．除了本实验中介绍的添加趋势线方法可以找到线性趋势预测模型的参数外，还可以用哪些方法找到线性趋势预测模型y=a+bx中的参数 a和b。
答：还可用回归方法找到Y=a+bX中参数a,b的值。

实验3-4 “美食佳”公司会员卡发行量趋势预测
实验类型：验证性 实验学时：2
实验目的：
• 理解非线性趋势预测法的概念；
• 掌握在excel中建立非线性趋势预测模型的方法；
• 掌握非线性趋势值预测的方法。
• 预测公司2007年7月会员卡的发行量。
实验步骤：
步骤1：确定时间序列的类型。

步骤2：添加非线性趋势线
在图中选中数据系列，右键菜单中选择“添加趋势线”，出现“添加趋势线”对话框。
在“添加趋势线”对话框的“类型”中选择“对数”。在“添加趋势线”对话框的“选项”中选中“显示公式”和“显示r平方值”
步骤3：趋势线前推法大致预测非线性趋势值。
选定对数趋势线，右键菜单中选择“趋势线格式”，
在“趋势线格式”对话框中选定“选项”。将趋势预测前推1周期。
步骤4：用方程或函数准确预测非线性趋势值。
根据得到的方程公式y=7.7785ln(x)+3.7651，在单元格c16中输入公式：“=7.7785*ln(a16)+3.7651”，即将x=15（2007年7月为第15个时间序列点）代入公式，计算得到2007年7月的会员卡发行预测值为24.83万张。
步骤5：将预测结果在图中表示。
趋势线前推只能在图中看到大致预测结果，

实验思考
1．请试一下，图3-39可否考虑用xy散点图做？ 这时用什么数据作为x轴合适？
答：可以，用序号的对数值ln(x)代替x，作为x轴合适，y与x是一次线性函数关系。
2．为什么预测值一定在趋势线的延伸线上？
答：预测值一定在趋势线上的原因是预测值是依据趋势线作出来的。
3．除了本实验中介绍的添加趋势线方法可以找到对数趋势预测模型的参数外，是否可以用规划求解法找到对数趋势预测模型y=a+bln(x)中的参数a和b？
答：还可用回归方法找到Y=a+bX中参数a,b的值。
实验3-5 “美食佳”火锅连锁店原料年度采购成本预测
实验目的：
• 理解季节指数的概念；
• 掌握季节指数预测方法。
实验步骤：
步骤1：确定时间序列的类型。

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

在单元格F2中输入公式：“=D2/E2”，将公式复制到单元格F3:F17中，得到消除季节影响后的结果。
利用单元格F2:F17中的数据绘制公司从2003年第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）。
2．计算趋势预测值时，若不用forcast()函数，还可以有什么方法？请至少用两种方法试试看。
答：计算趋势预测值还可用移动平均预测法、指数平滑预测法、一元线性回归分析模型等。
3．季节指数模型是否只能用于季节数据的预测？若是年度、月度、甚至周数据，可以用季节指数模型吗？
答：季节指数模型不是只能用于季节数据的预测，年度、月度、周数据等在某些情况下均能用季节指数模型。
实验小结：
在实验中遇到的问题是，在进行指数平滑求解时，最优的平滑指数求法很麻烦，在多次进行求解后才可以得到。
思考题：
1. 为什么用模拟运算表加查