数据分析实验报告册资料Word格式.docx
《数据分析实验报告册资料Word格式.docx》由会员分享,可在线阅读,更多相关《数据分析实验报告册资料Word格式.docx(60页珍藏版)》请在冰豆网上搜索。
![数据分析实验报告册资料Word格式.docx](https://file1.bdocx.com/fileroot1/2022-12/14/3a59354c-dd8b-415d-a21c-2aaaddb11e00/3a59354c-dd8b-415d-a21c-2aaaddb11e001.gif)
实验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公司客户特征分析
•理解数据分类汇总在企业中的作用与意义;
•掌握数据透视表工具的基本分类汇总功能;
•掌握建立分类汇总数据排行榜、生成时间序列、绘制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汇总的数据透视表,反映出“高上补习班、正人资源、大钰贸易”是公司的前三大客户。
点开“公司名称”字段,选中这三个公司名称,并拖到列域。
将列域的字段“类别名称”拖出数据透视表。
将页域的字段“订购年”旋转到行域,将其重新组合。
选择组合的步长为“月”和“年”,把字段名称修改为“订购年”与“订购月”。
光标停留在数据表中任何单元格,右击鼠标,选择“表格选项”,将“对于空数据项显示”设置为“0”,即当该单元格汇总出的数据值为空时,在数据透视表中将其显示为0。
此时得到的前三大客户销售额时间序列见图2-10。
图2-10Northwind公司前三大客户销售额时间序列
步骤3:
绘制前三大客户销售额时间序列图。
光标停留在数据透视表中,选择菜单“插入”→“图表”,在当前工作簿自动插入一张图表。
选择菜单“图表”→“位置”,将该图表调整到与数据透视表位于同一张工作表。
选择菜单“图表”→“图表类型”,选择“折线图”→“数据点折线图”。
随后,再对该图的大小、外观以及数据系列的格式加以调整,就能得到Northwind公司前三大客户销售额时间序列图,如图2-1所示。
三、绘制按照客户汇总的客户数与销售额帕累托曲线
查询“订购日期”、客户的“公司名称”与“销售额”等数据。
在Excel的空白工作表中,选择菜单“数据”→“数据透视表与数据透视图”→“外部数据源”→“获取数据”,利用MicrosoftQuery,从“订单”表、“订单明细”表与“客户”表中查询“订购日期”、客户的“公司名称”与“销售额”(销售额=订单明细.单价*数量*(1-折扣))等字段,将所查询数据返回Excel。
利用查询的数据,制作数据透视表。
从数据透视表的字段列表中,选择“订购日期”,拖至行域,将“销售额”拖至数据域。
将“订购日期”字段按年组合,然后拖至页域,将“公司名称”拖至行域,按照销售额从大到小的顺序排列,得到按照年度和客户公司名称汇总的数据透视表,如图2-11。
图2-11Northwind公司按照年度汇总的各客户销售额
利用数据透视表的数据,计算客户数累计百分比与客户销售额累计百分比,绘制帕累托曲线。
在区域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曲线
四、绘制按照订单汇总的销售额与销售次数帕累托曲线
查询“订购日期”、“订单ID”与“销售额”等数据。
在Excel的空白工作表中,选择菜单“数据”→“数据透视表与数据透视图”→“外部数据源”→“获取数据”,利用MicrosoftQuery,从“订单”表和“订单明细”表中查询“订购日期”、“订单ID”与“销售额”(销售额=订单明细.单价*数量*(1-折扣))等字段,将查询数据返回Excel。
将“订购日期”字段按年组合,拖至页域,将“订单ID”拖至行域,按照销售额从大到小的顺序排列,得到按照年度和订单ID汇总的数据透视表,如图2-16。
图2-16Northwind公司按照年度汇总各订单销售额
利用数据透视表的数据,计算客户数累计百分比与销售额累计百分比,绘制帕累托曲线。
G4依次输入说明文字,“销售次数百分比”、“销售次数累计百分比”、“销售额累计百分比”。
按照图2-17输入公式,得到如图2-18所示的汇总数据。
图2-17Northwind公司按照年度汇总客户数累计百分比和销售额累计百分比公式
图2-18Northwind公司按照年度汇总客户数累计百分比和销售额累计百分比
区域E5:
E834计算单次销售占总销售次数(即订单数)的百分比,区域F5:
F834汇总累计销售次数占总销售次数的百分比,即到该订单为止,已有订单数占到总订单数的百分比。
G834汇总到该订单为止,已有订单实现的销售额占总销售额的百分比。
G834的数据,绘制无数据点散点图,得到如图2-19图形。
图2-19销售次数与销售额的帕累托初步曲线
G834,MATCH(I5,F5:
FG834,1),1)”,即从销售次数累计百分比中,查找20%的销售次数在第几行,用INDEX函数查找,该行对应的销售额累计百分比。
J7,得到如图2-3所示的帕累托曲线。
图2-3norwthwind公司销售次数与销售额parato曲线
五、汇总各地区客户分布
查询“公司名称”与“地区”字段等数据。
将Excel一张空白工作表命名为“5.各地区客户分布”。
选择菜单“数据”→“数据透视表与数据透视图”→“外部数据源”→“获取数据”,利用MicrosoftQuery,从“客户”表中查询“公司名称”与“地区”字段,然后将所查询的数据返回Excel。
从数据透视表的字段列表中,选择“地区”,拖至行域,选择“公司名称”,拖至数据域,得到按照地区汇总的客户数的数据透视表,如图2-20。
图2-20按照地区汇总客户数的数据透视表
利用数据透视表的数据,制作数据透视图。
光标停留在数据透视表中,选择菜单“插入”→“图表”,在新建工作表中建立数据透视图,改变该图表位置,将其调整到“5.各地区客户分布”工作表中,得到了如图2-4所示的图形。
图2-4公司各地区客户的分布
六、绘制各地区平均销售额及销售额占总销售额百分比
查询“地区”与“销售额”等数据。
在Excel的空白工作表中,选择菜单“数据”→“数据透视表与数据透视图”→“外部数据源”→“获取数据”,利用MicrosoftQuery,选择数据源,从“客户”、和“订单明细”表中,查询客户的“地区”与“销售额”(销售额=订单明细.单价*数量*(1-折扣))等字段,将查询数据返回Excel。
查询时应包括“订单”表,该表能建立“客户”表和“订单明细”表之间的联系。
从数据透视表的字段列表中,选择“地区”,拖至行域,将“销售额”拖至数据域,得到按照地区汇总的销售额的数据透视表,如图2-21。
图2-21Northwind公司按照地区汇总的销售额
利用数据透视表的数据,计算各地区平均销售额与销售额占总销售额的百分比。
G4依次输入说明文字:
“地区”、“客户数”、“平均销售额”与“销售额占总额百分比”。
按照图2-22输入公式,得到如图2-23所示的汇总数据。
图2-22Northwind公司按照地区汇总平均销售额、销售额占总销售额百分比公式
图2-23Northwind公司按照地区汇总平均销售额、销售额占总销售额百分比
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。
挑选出销售额超过2000元的订单。
在区域F1:
F2,按照图2-25,输入筛选的条件。
利用Excel高级筛选功能,挑选出满足条件的记录,存放在区域H1:
K186中。
图2-25Northwind公司2000元以上销售额的订单的销售情况
利用挑选出的订单,制作数据透视表。
利用区域H1:
K186中的数据,制作数据透视表。
从数据透视表的字段列表中,选择“订购日期”,拖至行域,将“销售额”拖至数据域,将“订购日期”字段按年组合,然后拖至页域,将“公司名称”拖至页域,规定行域字段必须“显示空数据项”,得到如图2-26所示的数据