excel报告及答案.docx

资源描述

excel报告及答案.docx

《excel报告及答案.docx》由会员分享，可在线阅读，更多相关《excel报告及答案.docx（39页珍藏版）》请在冰豆网上搜索。

excel报告及答案.docx

excel报告及答案

《EXCEL预测与决策分析》

实验报告册

2011-2012学年第一学期

班级：

T953-5

学号：

20090530523

姓名：

刘莉

授课教师：

李保林实验教师：

李保林

实验学时：

16验组号：

信息管理系

实验一网上书店数据库的创建及其查询

实验类型：

验证性实验学时：

实验目的：

1-1理解数据库的概念；理解关系（二维表）的概念以及关系数据库中数据的组织方式；了解数据库创建方法

1-2理解odbc的概念；掌握利用microsoftquery进行数据查询的方法

1-3掌握复杂数据的查询方法：

多表查询、计算字段和汇总查询

实验步骤：

1-1

一、表的创建和联系的建立

步骤1：

创建空数据库“xddbookstore”在access中选择“文件”菜单中的“新建”命令，单击其中的“空数据库”，定好文件名（xddbookstore.mdb），然后单击“创建”按钮。

步骤2：

数据库中表结构的定义以“书”表为例在“数据库”窗口中的“对象”下，选择的是“表”，再双击“使用设计器创建表”，打开“设计”视图。

在“设计”视图的“字段名称”列中，单击第一个空单元格，键入“书号”将鼠标移至“数据类型”列，在其下拉列表框中选择“数字”数据类型，然后在“设计”视图下方“常规”选项卡的“字段大小”项中选择“整型”，单击“设计”视图第二行的“字段名称”列，键入“isbn”以新建“isbn”字段；按tab键移到“数据类型”列，选择“文本”数据类型，然后在“常规”选项卡的“字段大小”项中输入“20”；使用与上面同样的方法完成书表中其他字段的定义。

然后开始定义书表的主键（书号），方法是：

将鼠标移至书号字段最左边的灰色矩形块（行选择块）并单击，选中整个行，然后单击工具栏上的主键按钮即可以将书号字段定义为主键。

步骤3：

保存数据表。

单击文件菜单的保存命令，出现“另存为”对话框，在表名称项中输入“书”，然后单击“确定”按钮，书表定义完毕，单击关闭按钮关闭其“设计”视图。

步骤4：

定义“响当当”数据库的其他表。

方法与书表的定义相同。

步骤5：

“响当当”数据库中表之间联系的建立单击“工具”菜单的“关系”命令，出现“显示表”对话框，分别选择其中的每个表并按“添加”按钮，直到将所有表添加到“显示表”对话框后面的“关系”窗口中。

2、付款方式表的数据输入

步骤1：

选中需要输入数据的表（如付款方式表）。

在所示的“xddbookstore数据库”窗口的“表”选项卡中选中付款方式表图标并单击“打开”按钮，这时一个名为“付款方式：

表”的“数据表”视图窗口便显示出来。

步骤2：

输入数据。

在“数据表”视图中逐行输入付款方式表的各个记录，数据输入完毕，关闭该“数据表”视图，access便会将所输入的数据自动加以保存。

三、订单表的数据导入在本书配套磁盘提供的xddbookstore.xls文件中，包含了响当当数据库所有表的数据。

可以利用该文件将订单表数据导入到“xddbookstore.mdb”数据库中。

步骤1：

选择要导入的文件。

单击文件菜单的“获取外部数据/导入”命令，出现“导入”对话框。

单击要导入的文件“xddbookstore.xls”，然后单击“导入”按钮，出现导入数据表向导对话框。

步骤2：

规定要导入的数据表。

在“导入数据表向导”对话框中选择“订单表”，在该对话框的下方就会显示出“xddbookstore.xls”文件的“订单表”工作表中所包含的数据，其中的第一行是列标题；单击“下一步”按钮。

步骤3：

指明在要导入的数据中是否包含列标题。

在所示的“导入数据表向导”对话框中，选中“第一行包含列标题”复选框，单击“下一步”按钮。

步骤4：

规定数据应导入到哪个表中，可以是新表或现有的表。

在所示的“导入数据表向导”对话框的现有的表中选择“订单”表，单击“下一步”按钮，出现“导入数据表向导”对话框。

步骤5：

完成数据导入工作。

在所示的“导入数据表向导”对话框中单击“完成”按钮，就会出现的“导入数据表向导”对话框，其中显示的文字表明向“xddbookstore”数据库导入订单表的工作已经成功了。

单击其中的“确定”按钮即可。

1-2

一、建立odbc数据源在利用microsoftofficequery对“响当当”网上书店进行数据查询之前，必须先建立一个用于连接该数据库的odbc数据源“bookstore”，步骤如下：

启动microsoftofficequery应用程序，进入“创建新数据源”对话，输入数据源名字，选择数据库驱动程序，定义数据库连接信息，即选择数据源所要引用的数据库“northwind.mdb”。

二、查询设计1—低库存量图书信息查询对低库存量信息的查询，如查询库存量小于10的图书的isbn、书名和库存量，需要在查询时设定对库存量的查询要求，如“库存量<10”。

查询步骤如下：

步骤1：

选择“bookstore”数据源，进入“查询设计”窗口。

步骤2：

选择查询中需要使用的表。

步骤3：

选择要查询的字段。

步骤4：

添加“条件”窗格，设置查询条件。

三、查询设计2—低库存量图书信息以及出版社信息查询这里先查询库存量小于10的图书的isbn、书名、出版社名称、电话和地址，步骤如下：

步骤1：

选择“bookstore”数据源，进入“查询设计”窗口。

步骤2：

选择查询中需要使用的表。

步骤3：

选择查询字段。

步骤4：

设置查询条件。

四、查询设计3—特定作者图书信息查询有时会员需要了解某个作者编写的图书的信息，例如“boddie,john”编写的图书的信息，通过下列步骤可以得到这些信息。

步骤1：

选择“bookstore”数据源，进入“查询设计”窗口。

步骤2：

选择查询中需要使用的表，如“书”，将其添加到“表”窗格中。

步骤3：

选择“书”表中的所有字段。

步骤4：

添加“作者”表。

步骤5：

设置查询条件，显示查询结果。

五、查询设计4—低价图书信息查询有的会员去网上购书时，希望能了解低价图书的一些信息，如单价小于10元的图书的书名、出版年份和单价等，下面将设计一个查询，获取相关信息。

步骤如下：

步骤1：

选择数据源并添加表。

选择“bookstore”数据源，进入“查询设计”窗口。

在“添加表”对话框中选择“书”表，将其添加到“表”窗格中。

步骤2：

选择字段。

在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、“出版年份”和“单价”字段。

步骤3：

设置查询条件，显示查询结果。

在“条件”窗格的“条件字段”行的第一列中选择“单价”，并在下一行中输入“<10”后回车，即可在“查询结果”窗格中仅仅显示低价图书的相关信息。

六、查询设计5—新书信息查询

步骤1：

选择“bookstore”数据源并添加“书”和“作者”表

步骤2：

选择字段

步骤3：

设置查询条件，显示查询结果

步骤4：

设置其他查询条件

实验小结：

本次实验是一个基础实验，比较简单，主要是理解数据库的概念及数据库的创建方法，根据指导书的详细指导和老师的从旁协助，实验进行的还比较顺利，只是在订单表导入时遇到了一些小差错，后来跟同学请教之后就圆满的完成了实验。

可能正因为实验难度不大，所以老师没有要求我们交实验一处理结果吧！

思考题：

1、什么是表的主键？

在同一个表中，记录的主键值是否可以相等？

表的主键是指该表一个或多个字段的集合能够一行记录唯一地标识该表中的某一行记录，在同一个表中，记录的主键不能够相等。

2、在数据库中各表之间的联系建立以后，将外部数据导入到表中的过程中可能会遇到什么问题？

在导入数据过程中，可能只能导入一些规定的格式，比如.xls\.mdb等。

还有就是系统会提示违反了用户参照完整性，此时应该先导入父表数据，再导入子表数据，并且导入的数据表和被导入的数据表的数据类型要一致。

3、在数据查询过程中，如果所选择的表与其他表没有直接联系的话，可能查询不到相关表，因为之间没有产生对应关系。

4、

5、若响当当网上书店的某个会员想了解自己近两年图书订购情况，设计的查询为，在”条件”窗格中的“条件”字段选择订购日期，并且在下一行中输入“>=2009-1-1and<=2011-1-1”后按enter键，即可在查询窗口中显示2009-2010这两年的订书情况。

实验二贸易公司销售数据的分类汇总分析

实验类型：

验证性实验学时：

实验目的：

2-1 理解数据分类汇总在企业中的作用与意义；掌握数据透视表工具的基本分类汇总功能；掌握建立分类汇总数据排行榜、生成时间序列、绘制pareto曲线图、计算各地区客户分布、统计各地区客户的平均销售额和大宗销售时间序列的方法和步骤。

2-2 理解数据分类汇总在企业中的应用；掌握利用dsum函数汇总数据的基本方法；掌握利用控件选择汇总参考字段的方法；学习并掌握按照指定时间汇总数据，制作前十大客户销售额观测板、主要销售人员销售业绩观测板、主要城市销售业绩观测板和主要产品销售业绩观测板等的方法。

实验步骤：

2-1

1.利用数据透视表完成的汇总工作：

挑选对公司最重要的前三大客户，汇总他们各年月的销售额，在所获得的时间序列基础上，绘制销售额时间序列图形，以发现销售变化的规律

求和项:

销售额

公司名称

年

订购月

高上补习班

正人资源

大钰贸易

总计

1996年

7月

3488.68

8月

6796.64

9月

182.4

10月

5275.715

11月

4971.04

7088.505

12059.54

12月

4990.88

5062.55

10053.43

1997年

1月

1814.8

8467.715

10282.51

2月

3849.66

1792

6942.635

12584.29

4月

9921.3

8623.45

4707.54

23252.29

5月

15248.97

6月

550.5875

2944.4

3494.987

7月

3120

4725

14333.4

22178.4

8月

5510.592

1185.75

6696.342

9月

4464.6

1536.8

9628.1

15629.5

10月

15913.67

3436.443

12693.75

32043.87

11月

4529.8

5278

9807.8

12月

2247.1

13453.67

15700.77

1998年

1月

3812.7

7672

4971.92

16456.62

2月

17926.5

6379.4

3645.74

27951.64

3月

6315.875

6221.5

6542.4

19079.77

4月

9162.24

15719.75

16819.65

41701.64

5月

5218

4330.4

9548.4

总计

110277.3

104875

104361.9

319514.2

销售变化规律图

2.汇总各客户销售额与客户数，绘制客户销售额与客户数的帕累托曲线

3.汇总各客户销售额与客户数，绘制客户销售额与客户数的帕累托曲线

4.汇总“北风贸易”公司各地区客户的分布

计数项:

公司名称

地区

汇总

东北

华北

华东

华南

西北

西南

总计

5.汇总norrhwind公司各地区平均销售额，并利用前面汇总的数据，绘制各地区客户的分布以及平均销售额，以帮助分析各地区的销售情况

地区

客户数

平均销售额

销售额占总额百分比

东北

10477

华北

12040

39%

华东

17400

22%

华南

12054

19%

西北

5597

西南

27019

15%

求和项:

销售额

地区

汇总

东北

52385

华北

493658

华东

278399

华南

241073

西北

11194

西南

189135

总计

1265844

6.“北风贸易”公司把单张订单销售额超过2000元的销售定义为大宗销售，汇总大宗销售各月销售额，绘制销售额时间序列图形，并预测未来2个月的销售情况

2-2

步骤1：

获取汇总所需数据

步骤2：

汇总前十大客户月销售额。

制作组合框，数据源单元格指定为“前十大客户观测板!

$b$9:

$b$32”，单元格链接指定为“前十大客户观测板!

$b$8”。

在单元格c3输入公式：

“=">="&index（b9:

b32,b8）”，即大于等于从单元格b9:

b32中挑选出的b8所指定的日期。

在单元格d3输入公式

“="<"&date（year（index（b9:

b32,b8））,month（index（b9:

b32,b8））+1,1）”，在单元格b5输入公式：

“=dsum（sdata,原始数据!

e1,b2:

d3）”

步骤3：

汇总主要销售人员月销售额。

在单元格c5输入公式：

“=dsum（sdata,原始数据!

e1,主要销售人员销售额观测板!

b2:

d3）”

另外两个观测版的步骤一致，最后将四个观测版汇聚到一张表中，如图：

实验小结：

本次实验主要是对数据的分类汇总分析，然后得出相关结论，做出对企业发展有利的决策这些操作技巧在实际生活中的应用比较广泛而且作用也比较显著。

在实验2-2中，有多处用到了模拟运算表，可以说模拟运算表是分类汇总中的一个比较重要的方法，在模拟运算表中，通常需要经过多次模拟运算才能确定出最终的值，而且在运用模拟运算表的时候，一定要注意数据区域的选定，同时还要注意单元格的链接问题。

思考题：

1.你还能从哪些方面对客户的销售数据进行分析，帮助该公司促进销售或者为客户提供更好的服务？

答：

还可以从销售数量、销售价格和销售区域等方面进行数据分析，为客户提供更好的服务。

2.帕累托曲线可以帮助分析投入与产出之间的关系，它还能帮助该公司进行哪些方面的分析？

答：

还可以分析客户数量百分比与客户销售变量累计百分比之间的关系，也可以分析销售次数与销售额之间的关系。

实验三餐饮公司经营数据时间序列预测

实验类型：

验证性实验学时：

实验目的：

理解数平滑预测法、移动平均预测法、趋势预测法、非线性趋势预测法、季节指数的概念；

掌握在excel中建立指数平滑预测模型、移动平均模型、线性趋势预测模型、非线性趋势预测模型、季节指数预测的方法；

掌握寻找最优平滑常数、最优移动平均跨度、线性趋势模型参数、线性趋势值预测的各种方法。

实验步骤：

3-1

一、运用“数据分析”工具进行指数平滑预测，本实验的数据是年度数据，建议采用指数平滑预测法。

利用“数据分析”工具中的指数平滑功能进行预测。

二、运用指数平滑公式进行预测。

在单元格f1中输入平滑常数0.25，在单元格c2中输入公式：

“=b2”，作为第一年的预测值（），在单元格c3中输入指数平滑模型预测公式：

“=$f$1*b2+（1-$f$1）*c2”。

绘制指数平滑预测图。

三、寻找最优的平滑常数。

利用规划求解功能，寻找最优平滑常数。

最终个步骤实现后如图：

3-2

一、运用“数据分析”工具进行移动平均预测：

确定时间序列的类型，利用“数据分析”工具的移动平均功能进行预测。

二、运用移动平均公式进行预测：

利用average（）函数计算移动平均预测值，在单元格g1中输入移动平均跨度3，在单元格d5中输入移动平均模型预测公式：

“=average（c2:

c4）”。

三、寻找最优的移动平均跨度，在单元格g2中输入公式：

“=sumxmy2（c2:

c19,d2:

d19）/count（d2:

d19）”，计算均方误差mse。

最终实现结果：

3-3

一、确定时间序列，添加趋势线

二、用趋势线前推法大致预测线性趋势值。

三、用方程或函数准确预测线性趋势值。

利用forecast（）函数，在单元格c14中输入公式：

“=forecast（a13,c2:

c12,a2:

a12）”，计算得到2007年的预测值为999.65。

利用trend（）函数，在单元格c15中输入公式：

“=trend（c2:

c12,a2:

a12,a13）”

最终求取结果：

3-4

步骤1：

确定时间序列的类型。

步骤2：

添加非线性趋势线。

步骤3：

趋势线前推法大致预测非线性趋势值。

步骤4：

用方程或函数准确预测非线性趋势值。

序号

年月

发行量

2006-5

1.47

2006-6

9.48

2006-7

13.78

2006-8

15.56

2006-9

17.27

2006-10

18.76

2006-11

19.35

2006-12

19.93

2007-1

20.27

2007-2

21.71

2007-3

22.56

2007-4

22.93

2007-5

22.61

2007-6

22.98

2007-7

24.85

根据得到的方程公式y=7.7785ln（x）+3.7651，如图3-38所示，在单元格c16中输入公式：

“=7.7785*ln（a16）+3.7651”，即将x=15（2007年7月为第15个时间序列点）代入公式，计算得到2007年7月的会员卡发行预测值为24.83万张。

3-5

步骤1：

计算季节指数。

步骤2：

用方程或函数准确预测非线性趋势值。

根据得到的方程公式y=7.7785ln（x）+3.7651，如图3-38所示，在单元格c16中输入公式：

“=7.7785*ln（a16）+3.7651”，即将x=15（2007年7月为第15个时间序列点）代入公式，计算得到2007年7月的会员卡发行预测值为24.83万张。

实验小结：

本次实验主要目的是理解数平滑预测法、移动平均预测法、趋势预测法、非线性趋势预测法、季节指数的概念；掌握在excel中建立指数平滑预测模型、移动平均模型、线性趋势预测模型、非线性趋势预测模型、季节指数预测的方法，掌握寻找最优平滑常数、最优移动平均跨度、线性趋势模型参数、线性趋势值预测的各种方法。

实验过程大致相同。

难度较大的主要是对移动平行跨度的求解，在利用模拟运算表和查找引用函数功能是要注意公式的书写。

这次实验的公式较多，而且不是很容易理解，因此，还需要深入学习。

思考题：

1.为什么用模拟运算表加查找引用函数功能，得到的最优平滑常数（0.35）与用规划求解功能得到的结果（0.37）不一样？

答：

应为方法所选精度不一样。

2.可否利用规划求解功能，寻找最优的移动平均跨度？

答：

可以

3.excel提供的移动平均趋势线功能也可进行移动平均预测，但趋势线方法与本实验所介绍的方法有何不同？

答：

移动平均趋势线功能可以自动生成平均预测值曲线，但利用此方法只能得到移动平均跨度下的估计值，在生成的估计值数据的基础上不能计算出最优移动平均跨度。

4．为什么预测值一定在趋势线的延伸线上？

答：

线性预测假定因变量是随自身变量线性变化的。

5．除了本实验中介绍的添加趋势线方法可以找到线性趋势预测模型的参数外，还可以用哪些方法找到线性趋势预测模型y=a+bx中的参数a和b。

答：

最小二乘法

7．除了本实验中介绍的添加趋势线方法可以找到对数趋势预测模型的参数外，是否可以用规划求解法找到对数趋势预测模型y=a+bln（x）中的参数a和b？

答：

可以

8．计算趋势预测值时，若不用forcast（）函数，还可以有什么方法?

答：

可以用Trend（）函数线性趋势预测等。

9．季节指数模型是否只能用于季节数据的预测？

若是年度、月度、甚至周数据，可以用季节指数模型吗？

答：

不是只用于季节数据，经围应该是呈现周期性变化的数据。

实验四住房建筑许可证数量的回归分析

实验类型：

验证性实验学时：

实验目的：

4-1理解回归分析的概念；针对不同的问题，能够建立适当的模型进行分析；掌握一元线性回归分析方法；在给定已知变量的情况下，根据模型预测未知变量的值。

4-2理解一元回归的概念和方法

4-3理解多元回归分析的概念、掌握多元回归的方法

实验步骤：

4-1

步骤1：

确定因变量与自变量并输入观测值

步骤2：

绘制因变量与自变量关系散点图。

步骤3：

求出回归系数a、b的取值，计算判定系数r2，并进行预测。

步骤4：

假定回归系数的值，建立线性回归模型。

在单元c2中输入公式“=$f$2+$f$3*a2”，并将此公式复制到c3:

c19中，得到建筑许可证的颁发数量预测值。

在单元格f5中计算建筑许可证的颁发数量观测值与预测值的均方误差mse，即在单元格f5中输入公式“{=average（（c2:

c19-b2:

b19）^2）}”

步骤5：

启动规划求解工具，确定模型最优参数。

步骤6：

计算判定系数r2，说明建筑许可证数量的预测值的可信度。

4-2

步骤1：

确定因变量与自变量。

步骤2：

选择合适的回归方程。

步骤3：

假定回归系数的值，建立非线性回归模型。

在单元c2中输入公式“=$f$2+$f$3*ln（a2）”，并将此公式复制到c3:

c19中，得到建筑许可证的颁发数量预测值。

在单元格f5中计算建筑许可证的颁发数量观测值与预测值的均方误差mse，即在单元格f5中输入公式“{=average（（c2:

c19-b2:

b19）^2）}

步骤4：

确定参数a与b的值。

对于本例的问题，我们采用规划求解的方法来确定参数a与b的值，利用规划求解工具计算出使mse极小的参数a与b，规划求解对话框的设置如图

步骤5：

添加趋势线，显示值。

-78874.1

16877.32

MSE

2006983

自由房屋的均值（x单位：

百元）

建筑许可证的颁发数量

171.2

7620

7923.139

174.2

9120

8216.325

204.3

10670

10906.32

218.7

11160

12055.86

219.4

11900

12109.8

240.4

12920

13652.51

273.5

14340

15829.64

294.8

15900

17095.37

330.2

18000

19009.28

333.1

19300

19156.86

366

20100

20746.54

350.9

20180

20035.46

357.9

22100

20368.83

359

23000

展开阅读全文