数据分析实验报告册分析.docx

数据分析实验报告册分析.docx

《数据分析实验报告册分析.docx》由会员分享，可在线阅读，更多相关《数据分析实验报告册分析.docx（72页珍藏版）》请在冰豆网上搜索。

数据分析实验报告册分析

《数据分析》

实验报告册

2015-2016学年第一学期

班级：

学号：

姓名：

授课教师：

实验教师：

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

实验1-1“响当当”网上书店的数据库创建

实验1-2“响当当”网上书店库存、图书和会员信息查询

实验1-3“响当当”网上书店会员分布和图书销售查询

实验二企业销售数据的分类汇总分析

实验2-1Northwind公司客户特征分析

实验2-2“北风”贸易公司销售业绩观测板

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

实验3-1“美食佳”公司半成品年销售量预测

实验3-2“美食佳”公司月管理费预测

实验3-3“美食佳”华东分公司销售额趋势预测

实验3-4“美食佳”公司会员卡发行量趋势预测

实验3-5“美食佳”火锅连锁店原料年度采购成本预测

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

实验4-1“家家有房”公司建筑许可证一元线性回归分析

实验4-2“家家有房”公司建筑许可证一元非线性回归分析

实验4-3“家家有房”公司建筑许可证多元线性回归分析

实验4-4“家家有房”公司建筑许可证多元非线性回归分析

实验五手机用户消费习惯聚类分析

实验六新产品价格敏感度测试模型分析

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

实验1-1“响当当”网上书店的数据库创建

实验类型：

验证性实验学时：

2

实验目的：

•理解数据库的概念；

•理解关系（二维表）的概念以及关系数据库中数据的组织方式；

•了解数据库创建方法。

实验步骤：

这个实验我们没有直接做，只是了解了一下数据库的概念。

实验1-2“响当当”网上书店库存、图书和会员信息查询

实验目的

• 理解odbc的概念；

• 掌握利用microsoftquery进行数据查询的方法。

实验步骤：

1..建立odbc数据源：

启动microsoftofficequery应用程序，在microsoftofficequery应用程序窗口中，执行“文件/新建”命令，出现“选择数据源”对话框，单击“确定”按钮，出现“创建新数据源”对话框，按照要求做相应的操作。

选择数据源对话框创建新数据源窗口

做图上所示的选择odbcmicrosoftaccess安装对话框

选择数据库对话框选择数据源对话框

2.查询设计1—低库存量图书信息查询：

选择“bookstore”数据源，点击“确定”，进入“添加表”窗口，添加书表后，在“查询设计”窗口的“表”窗格中，分别双击“书”表中需要查询的“书名”、“isbn”、“库存量”等字段，执行“视图/条件”命令，在“条件”窗格的“条件字段”行的第一列中选择“库存量”，并在下一行中输入“<10”后回车，即可在“查询结果”窗格中显示响当当网上书店中库存量小于10的图书信息。

选择bookstore数据源选择好表后点击“添加”

查询设计窗口-查询的相关数据查询设计窗口-输入条件

3.查询设计2—低库存量图书信息以及出版社信息查询：

分别添加“书”和“出版社”表，双击“书”表的“书名”和“isbn”字段以及“出版社”表的“出版社名称”、“电话”和“地址”字段，再输入相应的条件即可进行查询。

添加了表后的查询设计窗口

实验小结：

因为我们没有尝试建立数据库，直接开始数据查询，所以实验时遇到了很多问题。

比如我们在选择数据源时就遇到了麻烦，弄了半天才开始查询设计，而且不是很熟练，一直做了四五个查询设计才慢慢熟练起来。

实验思考：

1、在数据查询过程中，如果所选的某个表与其他表之间没有联系的话，会

产生什么问题？

答：

所选的查询数据将会全部显示在查询窗口，与其它表的数据没有直接联系。

这样就不能表现出表与表数据之间的关联性，数据也就失去赋予的意义。

2、若“响当当个”网上书店的某个会员了解自己最近2年的图书订购情况，请为他设计一个查询。

答：

分别添加“书”、“会员”、“订单明细”和“订单”表，双击“书”表的“书名”、“会员”表的“姓名”和“订单明细”表的“订购数量”以及“订单”表的“订购日期”字段，在向查询条件窗口中输入某一会员姓名以及相应的订购日期.

实验1-3“响当当”网上书店会员分布和图书销售查询

实验目的

•掌握复杂的数据查询方法：

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

实验步骤:

1.查询设计1—会员分布信息查询：

添加“会员”表到查询设计窗口，在“查询设计”窗口的“表”窗格中，双击“会员”表的“城市”和“会员号”字段。

然后双击“会员号”字段的列标，在“编辑列”对话框中输入列标“会员人数”，并选择汇总方式：

“计数”，单击“确定”按钮后即可看到查询结果，其中显示了各城市的会员人数，再设置相应的条件，进行相应的查询。

选择汇总方式查询结果

2.查询设计2—图书总订购量和总销售金额查询：

添加“订单”、“订单明细”和“书”表。

在“查询设计”窗口的“表”窗格中，双击“订单”表的“订购日期”、“订单明细”表的“订购数量”字段。

另外还要构造一个计算字段“销售金额”，方法是直接在某空白列的列标中输入公式；在上面的字段中，“订购数量”和“销售金额”是汇总字段，分类字段是“订购年份”，双击“订购日期”列的列标，在编辑列对话框的字段项中输入公式“year（订购日期）”，在其中的列标项中输入“订购年份”然后分别双击“订购数量”和“订购数量*单价”字段的列标，在编辑列对话框的列标项中分别输入“总订购数量”和“总销售金额”字样，并在总计项中选择“求和”。

在“条件”窗格的“条件字段”行的第一列中选择“订购日期”，并在下一行中输入“>=2005-7-1and<=2006-6-30”后回车，即可在“查询结果”窗格中显示2005上半年和2006下半年的图书总订购量和总销售金额。

此时，若想了解各月份的图书总订购量和总销售金额，只要再查询设计窗口中增加一个“订购月份”分类字段即可

增加了订购月份后的查询结果排序对话框

3.查询设计3—会员订购图书详细信息查询：

添加“会员”、“订单”、“订单明细”和“书”表，在表之间建立合适的联系。

在“查询设计”窗口的“表”窗格中，双击“会员”表的“城市”、“会员号”、“姓名”字段，“订单”表的“订单号”字段、“书”表的“书名”字段和“订单明细”表的“订购数量”字段。

选择“记录”菜单的“排序”命令，在随后出现的“排序”对话框中设置排序方式

查询结果共84条记录

4.查询设计4—各城市会员图书订购数量和销售金额统计：

添加“会员”、“订单”、“订单明细”和“书”表，在表之间建立合适的联系。

在“查询设计”窗口的“表”窗格中，双击“会员”表的“城市”、“订单明细”表的“订购数量”字段。

分别双击“订购数量”和“订购数量*单价”字段的列标，在编辑列对话框的列标项中分别输入“总订购数量”和“总销售金额”字样，并在总计项中选择“求和”。

按“确定”按钮后即可查看结果。

5.查询设计5—被订购图书的作者和出版社信息查询：

添加“会员”、“订单”、“订单明细”、“书”、“作者”和“出版社”表。

在表之间建立合适的联系。

在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、“作者”表的“姓名”和“出版社”表的“出版社名称”字段。

在条件窗格中添加关于订购日期和会员姓名的条件。

可以查看到结果为会员“刘丹”在2007年共订购了7本图书。

实验小结：

实验1-3的实验比较难，但是经过前面的练习还是比之前快一点，不过还是遇到一些困难，比如查看到结果为会员“刘丹”在2007年共订购了几本书的查询设计就在输入指令时卡住了，经过几个同学讨论还是做出来了。

一直到晚上天黑了才把一共十个实验做完。

实验思考:

1、在进行汇总查询的过程中，如果被选择的字段除了分类字段以外还包含了其他字段。

查询结果是否正确？

为什么?

请举例说明。

答：

不正确，如果被选择的字段除了分类字段以外还包含了其他字段,那么query将把多余的字段自动作为分类字段。

2、“响当当”网上书店的管理人员想了解最近2年中那位作者的书是最畅销，请你设计一个查询找到相关作者。

答：

查询近两年的总订购量。

实验二企业销售数据的分类汇总分析

实验2-1Northwind公司客户特征分析

实验类型：

验证性实验学时：

2

实验目的：

•理解数据分类汇总在企业中的作用与意义；

•掌握数据透视表工具的基本分类汇总功能；

•掌握建立分类汇总数据排行榜、生成时间序列、绘制praetor曲线图、计算各地区客户分布、统计各地区客户的平均销售额和大宗销售时间序列的方法和步骤。

实验步骤：

一、汇总客户销售额排行榜

为了汇总客户销售额的排行榜，首先要获得客户每笔销售的销售额、所购买产品的类别以及销售发生的时间，然后再利用数据透视表工具将销售额按照客户名称、产品类别和销售时间加以汇总。

步骤1：

获取各客户每笔销售的销售额、销售产品的类别和时间。

在一张空白的工作表中，选择菜单“数据”→“数据透视表和数据透视图”→“外部数据源”，单击“获取数据按钮”，随后启动了MicrosoftQuery，选择所建立的连接到Northwind.mdb数据库的ODBC数据源——“NW”，并选择“确定”，选择“客户”表中的的“公司名称”、“订单”表中的“订购日期”、以及“类别”表中“类别名称”，随后Query弹出窗口“‘查询向导’无法继续，因为该表格无法链接到您的查询中。

您必须在MicrosoftQuery中的表格之间拖动字段，人工链接。

”这是因为类别表无法同订单表建立联系。

单击“确定”。

要查询销售额，需要在Query中首先增加“订单明细”表，利用其中的“单价”、“数量”与“折扣”字段中的数据，才能计算销售额。

在数据窗格中，在一个空白字段的名称处输入公式：

“订单明细.单价*数量*（1-折扣）”。

键入回车后就可以计算出销售额。

见图2-7。

随后，将“产品”表也添加到查询中，虽然查询结果中并不包括任何“产品”表中的字段，但是该表的能够建立“类别”表与“订单明细”表之间的联系（“订单明细”表指明所订购产品的ID，“产品”表指明该产品属于哪一个类别）。

此时，查询中的表都建立了正确的联系，并在查询结果中包括了汇总所需要的数据。

如图2-7。

图2-7查询各客户每笔销售的销售额、销售产品的类别和时间

将计算销售额的字段的列标命名为“销售额”。

选择Query菜单中的“文件”→“将数据返回MicrosoftOfficeExcel”，此时Query已经关闭，我们的操作对象回到了Excel，单击“下一步”，指定位置在“现有工作表”，单元格A3，单击完成。

步骤2：

汇总客户销售额排行榜，并排序。

此时，在工作表的区域A1:

G16的位置，出现了数据透视表的框架，数据透视表的浮动工具栏和数据透视表的字段列表。

为了能对销售的时间——“订购日期”进行组合以获得各年的销售额，首先将“订购日期”拖至行域，将“销售额”拖至数据域，“类别名称”拖至列域，得到如图2-8所示的数据透视表。

图2-8按订购日期与类别汇总销售额

为了能将销售额按照年度汇总，将光标停留在“订购日期”下方的任何单元格，右击鼠标，选择“组合及显示明细数据”→“组合”，选择组合的步长为年。

然后将单元格A4当中的字段名称“订购日期”改为“订购年”，将它推至页域，将字段列表中的“公司名称”拖到行域，让透视表按照列总计，从大到小排列，就得到了如图2-9所示的数据透视表。

它能够反映了三年或者各年度，各个客户的销售额的大小，以及排列名词的先后，还能够观察到各客户订购的产品类别和该类别的销售额。

图2-9按照订购年、客户公司名称、类别名称汇总的销售额排行榜

二、汇总前三大客户各月销售额，并绘制图形

在前一部分实验的基础上，选择前三个最重要的客户，进一步观察购买情况，他们购买情况的变化将对公司整体销售业绩产生很大影响。

所以，将前三大客户的销售数据加以展开，按月显示其销售的变化。

步骤1：

将实验要求1所汇总的数据透视表复制到新的工作表。

步骤2：

利用数据透视表，汇总前三大客户的销售额时间序列。

按照实验要求1汇总的数据透视表，反映出“高上补习班、正人资源、大钰贸易”是公司的前三大客户。

点开“公司名称”字段，选中这三个公司名称，并拖到列域。

将列域的字段“类别名称”拖出数据透视表。

将页域的字段“订购年”旋转到行域，将其重新组合。

选择组合的步长为“月”和“年”，把字段名称修改为“订购年”与“订购月”。

光标停留在数据表中任何单元格，右击鼠标，选择“表格选项”，将“对于空数据项显示”设置为“0”，即当该单元格汇总出的数据值为空时，在数据透视表中将其显示为0。

此时得到的前三大客户销售额时间序列见图2-10。

图2-10Northwind公司前三大客户销售额时间序列

步骤3：

绘制前三大客户销售额时间序列图。

光标停留在数据透视表中，选择菜单“插入”→“图表”，在当前工作簿自动插入一张图表。

选择菜单“图表”→“位置”，将该图表调整到与数据透视表位于同一张工作表。

选择菜单“图表”→“图表类型”，选择“折线图”→“数据点折线图”。

随后，再对该图的大小、外观以及数据系列的格式加以调整，就能得到Northwind公司前三大客户销售额时间序列图，如图2-1所示。

三、绘制按照客户汇总的客户数与销售额帕累托曲线

步骤1：

查询“订购日期”、客户的“公司名称”与“销售额”等数据。

在Excel的空白工作表中，选择菜单“数据”→“数据透视表与数据透视图”→“外部数据源”→“获取数据”，利用MicrosoftQuery，从“订单”表、“订单明细”表与“客户”表中查询“订购日期”、客户的“公司名称”与“销售额”（销售额=订单明细.单价*数量*（1-折扣））等字段，将所查询数据返回Excel。

步骤2：

利用查询的数据，制作数据透视表。

从数据透视表的字段列表中，选择“订购日期”，拖至行域，将“销售额”拖至数据域。

将“订购日期”字段按年组合，然后拖至页域，将“公司名称”拖至行域，按照销售额从大到小的顺序排列，得到按照年度和客户公司名称汇总的数据透视表，如图2-11。

图2-11Northwind公司按照年度汇总的各客户销售额

步骤3：

利用数据透视表的数据，计算客户数累计百分比与客户销售额累计百分比，绘制帕累托曲线。

在区域D4:

G4依次输入说明文字，“公司名称”、“客户百分比”、“客户数累计百分比”、“销售额累积百分比”。

按照图2-12输入公式，得到如图2-13所示的汇总数据。

图2-12Northwind公司按照年度汇总客户数累计百分比和销售额累计百分比公式

图2-13Northwind公司按照年度汇总客户数累计百分比和销售额累计百分比

区域F5:

F93汇总累计的客户数，即到该客户为止，已有客户数占到总客户数的百分比。

区域G5:

G93汇总了到该客户为止，已有客户实现的销售额占总销售额的百分比。

选中区域F4:

G93的数据，绘制无数据点散点图，得到如图2-14所示图形。

图2-14客户数与销售额的帕累托初步曲线

步骤4：

在曲线上添加代表20%客户数的垂直参考线。

在I5:

I7单元格输入“20%”，在J5与J7单元格输入“0”和“120%”，在J6单元格输入公式：

“=INDEX（G5:

G93,MATCH（I5,F5:

F93,1）,1）”，即从客户数累计百分比中，查找到20%的客户数在第几行，然后用INDEX函数查找，该行对应的销售额累计百分比，计算结果如图2-15。

在前面所绘制的图表上，添加一条垂直参考线。

该参考线的X轴数据来自区域I5:

I7，Y轴数据来自区域J5:

J7，得到如图2-2所示的帕累托曲线。

该曲线反映出，Northwind公司20%的客户只实现了59%的销售额，低于20/80准则的比例。

通过在数据透视表中调整页域的值，可以发现：

96年、97年比例都不足58%，98年有所增长，达到了63%，还是远低于80%。

因此，Northwind公司今后销售工作的重点应是更多地关注大客户，通过增加大客户的销售额来增加整个公司的销售额。

图2-15Northwind公司客户数与销售额帕累托曲线垂直参考线数据

最后公司客户销售额与客户数parato曲线呈现如图所示:

图2-2公司客户销售额与客户数parato曲线

四、绘制按照订单汇总的销售额与销售次数帕累托曲线

步骤1：

查询“订购日期”、“订单ID”与“销售额”等数据。

在Excel的空白工作表中，选择菜单“数据”→“数据透视表与数据透视图”→“外部数据源”→“获取数据”，利用MicrosoftQuery，从“订单”表和“订单明细”表中查询“订购日期”、“订单ID”与“销售额”（销售额=订单明细.单价*数量*（1-折扣））等字段，将查询数据返回Excel。

步骤2：

利用查询的数据，制作数据透视表。

从数据透视表的字段列表中，选择“订购日期”，拖至行域，将“销售额”拖至数据域。

将“订购日期”字段按年组合，拖至页域，将“订单ID”拖至行域，按照销售额从大到小的顺序排列，得到按照年度和订单ID汇总的数据透视表，如图2-16。

图2-16Northwind公司按照年度汇总各订单销售额

步骤3：

利用数据透视表的数据，计算客户数累计百分比与销售额累计百分比，绘制帕累托曲线。

在区域D4:

G4依次输入说明文字，“销售次数百分比”、“销售次数累计百分比”、“销售额累计百分比”。

按照图2-17输入公式，得到如图2-18所示的汇总数据。

图2-17Northwind公司按照年度汇总客户数累计百分比和销售额累计百分比公式

图2-18Northwind公司按照年度汇总客户数累计百分比和销售额累计百分比

区域E5:

E834计算单次销售占总销售次数（即订单数）的百分比，区域F5:

F834汇总累计销售次数占总销售次数的百分比，即到该订单为止，已有订单数占到总订单数的百分比。

区域G5:

G834汇总到该订单为止，已有订单实现的销售额占总销售额的百分比。

选中区域F4:

G834的数据，绘制无数据点散点图，得到如图2-19图形。

图2-19销售次数与销售额的帕累托初步曲线

步骤4：

在曲线上添加代表20%客户数的垂直参考线。

在I5:

I7单元格输入“20%”，在J5与J7单元格输入“0”和“120%”，在J6单元格输入公式：

“=INDEX（G5:

G834,MATCH（I5,F5:

FG834,1）,1）”，即从销售次数累计百分比中，查找20%的销售次数在第几行，用INDEX函数查找，该行对应的销售额累计百分比。

在前面所绘制的图表上，添加一条垂直参考线。

该参考线的X轴数据来自区域I5:

I7，Y轴数据来自区域J5:

J7，得到如图2-3所示的帕累托曲线。

图2-3norwthwind公司销售次数与销售额parato曲线

五、汇总各地区客户分布

步骤1：

查询“公司名称”与“地区”字段等数据。

将Excel一张空白工作表命名为“5.各地区客户分布”。

选择菜单“数据”→“数据透视表与数据透视图”→“外部数据源”→“获取数据”，利用MicrosoftQuery，从“客户”表中查询“公司名称”与“地区”字段，然后将所查询的数据返回Excel。

步骤2：

利用查询的数据，制作数据透视表。

从数据透视表的字段列表中，选择“地区”，拖至行域，选择“公司名称”，拖至数据域，得到按照地区汇总的客户数的数据透视表，如图2-20。

图2-20按照地区汇总客户数的数据透视表

步骤3：

利用数据透视表的数据，制作数据透视图。

光标停留在数据透视表中，选择菜单“插入”→“图表”，在新建工作表中建立数据透视图，改变该图表位置，将其调整到“5.各地区客户分布”工作表中，得到了如图2-4所示的图形。

图2-4公司各地区客户的分布

六、绘制各地区平均销售额及销售额占总销售额百分比

步骤1：

查询“地区”与“销售额”等数据。

在Excel的空白工作表中，选择菜单“数据”→“数据透视表与数据透视图”→“外部数据源”→“获取数据”，利用MicrosoftQuery，选择数据源，从“客户”、和“订单明细”表中，查询客户的“地区”与“销售额”（销售额=订单明细.单价*数量*（1-折扣））等字段，将查询数据返回Excel。

查询时应包括“订单”表，该表能建立“客户”表和“订单明细”表之间的联系。

步骤2：

利用查询的数据，制作数据透视表。

从数据透视表的字段列表中，选择“地区”，拖至行域，将“销售额”拖至数据域，得到按照地区汇总的销售额的数据透视表，如图2-21。

图2-21Northwind公司按照地区汇总的销售额

步骤3：

利用数据透视表的数据，计算各地区平均销售额与销售额占总销售额的百分比。

在区域D4:

G4依次输入说明文字：

“地区”、“客户数”、“平均销售额”与“销售额占总额百分比”。

按照图2-22输入公式，得到如图2-23所示的汇总数据。

图2-22Northwind公司按照地区汇总平均销售额、销售额占总销售额百分比公式

图2-23Northwind公司按照地区汇总平均销售额、销售额占总销售额百分比

区域E5:

E10存放各地区的客户数，区域F5:

F10计算各地区平均销售额，区域G5:

G10计算各地区销售额占总销售额的百分比。

利用区域D5：

D10与区域F5：

G10中的数据，绘制柱型图。

由于一个数据系列是平均销售额，一个数据系列是百分比，两个系列数值相差悬殊，所以在图2-24中，只能观察到一个数据系列的柱型，另一个系列的柱型贴近“0”，无法观察到。

选中代表百分比的系列（选中平均销售额的系列，移动上下箭头，直到选中代表百分比的系列），选择菜单“格式”→“数据系列格式”→“坐标轴”→“次坐标轴”，将代表百分比的系列对应到次坐标轴。

图2-24Northwind公司按照地区汇总平均销售额、销售额占总销售额百分比图

观察该图形，可以发现“华东”与“西南”地区，客户的平均销售额比其他地区高，说明这两个地区大客户的销售情况比较好。

华北地区虽然销售额占总销售额的百分比最高，是Northwind公司最重要的市场，但该地区大客户的销售情况并不理想，平均销售额并不高。

今后该地区应更注重改善大客户销售情况。

七、绘制大宗销售的销售额时间序列。

步骤1：

查询“公司名称”、“订单ID”、“订购日期”与“销售额”等数据。

将Excel的空白工作表命名为“7.大宗销售数据”，选择菜单“数据”→“获取外部数据”→“外部数据源”→“获取数据”，利用MicrosoftQuery，从“客户”表、“订单”表和“订单明细”表中，查询“公司名称”、“订单ID”、“订购日期”与“销售额”（销售额=订单明细.单价*数量*（1-折扣））等字段，将查询数据返回Excel，存放在区域A1:

D831。

步骤2：

挑选出销售额超过2000元的订单。

在区域F1：

F2，按照图2-25，输入筛选的条件。

利用Excel高级筛选功能，挑选出满足条件的记录，存放在区域H1：

K186中。

图2-25Northwind公司2000元以上销售额的订单的销售情况

步骤3：

利用挑选出的订单，制作数据透视表。

利用区域H1：

K186中的数据，制作数据透视表。

从数据透视表的字段列表中，选择“订购日期”，拖至行域，将“销售额”拖至数据域，将“订购日期”字段按年组合，然后拖至页域，将“公司名称”拖至页域，规定行域字段必须“显示空数据项”，得到如图2-26所示的数据