《EXCEL_数据分析-透视图ppt课件》由会员分享，可在线阅读，更多相关《EXCEL_数据分析-透视图ppt课件（70页珍藏版）》请在金锄头文库上搜索。
1、Excel预测与决策分析,第三章 数据分类汇总分析,基础篇 数据分类汇总分析的意义和作用 数据分类汇总的四种方法 数据透视表 D函数和模拟运算表 提高篇 利用DSUM函数和控件控制分类汇总结果 分类汇总数据的应用 Excel中的多维数据分析OLAP,内容简介：,第一节 数据分类汇总分析的意义和作用,获得销售额分类统计值,ABC公司1995年不同省份不同类别商品的销售额总计值,0,10000,20000,30000,40000,50000,艺术品,自行车,服装,食品,儿童用品,体育用品,瓷器,类别,销售额,第一节 数据分类汇总分析的意义和作用（续）,获得各类销售额排行榜,第一节 数据分类汇总分析
2、的意义和作用（续）,获得各类销售额的时间序列,第一节 数据分类汇总分析的意义和作用（续）,获得各经济量之间的相关性,第一节 数据分类汇总分析的意义和作用（续）,获得各种产品需求量的频率分布,第二节数据分类汇总的四种方法,数据查询的统计值功能 数据清单功能 数据透视表 D函数与模拟运算表,第二节数据分类汇总的四种方法,汇总字段 ：待汇总的变量或字段。 销售额、销售数量、工作时间等。 分类字段 ：对数据进行汇总时，以某个变量或字段的不同值为参考来对汇总字段进行汇总。 参考不同产品类别 (分类字段) 汇总销售额（汇总字段） 参考不同的生产人员姓名（分类字段）汇总工作时间 （汇总字段）,第二节 数据分
3、类汇总的四种方法（续）,ABC公司1995各省份、各类别商品净销售额总计值 （图3-7）,一、数据查询的统计值功能,从Excel中启动Microsoft Query，利用Query的统计值功能，将数据汇总后返回Excel 【例3-1】 ABC公司销售数据.dbf中保存着公司从1994年到1997年三年的销售数据，利用Query软件的统计值功能汇总该公司的销售数据，并按照图3-7的形式，汇总出1995年不同省份、不同类别商品的净销售额。,一、数据查询的统计值功能（续）,二、Excel数据清单功能,数据清单 数据清单是包含相关数据的一系列工作表数据行 。 数据清单的首行为字段名，首行下的各行是各个
4、记录，数据清单中不能出现空行 。 数据清单的基本功能 排序 筛选 分类总计,二、Excel数据清单功能（续）,利用Excel数据清单处理功能进行分类汇总的一般步骤： 获得数据清单； 设定筛选条件； 筛选数据，将筛选结果复制到工作表其他位置； 对经过筛选的数据按分类字段进行排序； 对排序后的数据进行分类汇总。,二、Excel数据清单功能（续）,【例3-2】利用Excel的数据清单功能，将ABC公司的销售数据按照图3-7的形式，汇总出1995年不同省份、不同类别商品的净销售额总计值。,将上述汇总表选中第3级汇总，然后选中所需数据区域，单击“选定可见单元格”（工具-自定义-编辑），再单击“复制”，将
5、光标移到空白数据区域，单击粘贴，得到如图数据。 根据右图数据再加以调整，便可得到如图3-7的数据。,第三节 数据透视表,数据透视表的基本功能 数据透视表结构的灵活性 数据透视图的灵活性 利用数据透视表生成时间序列 统计不同规模销量的发生次数以及概率分布 计算占同列数据总和的百分比,一、数据透视表的基本功能,数据透视表分类汇总的两种方法 先将数据导入Excel成为数据清单，利用数据透视表汇总对数据清单进行汇总 利用数据透视表直接从数据库中查询、并汇总数据,一、数据透视表的基本功能（续）,【例3-3】利用Excel获取外部数据功能，获取ABC公司1995年的销售数据，使用Excel数据透视表功能制
6、作如图3-7所示的分类汇总表。 提示：启动excel-数据数据透视表和数据透视图导入外部数据新建数据库查询，在query获得汇总数据后返回excel，单击数据透视图的“图表向导”，生成数据透视图。,一、数据透视表的基本功能（续）,一、数据透视表的基本功能（续）,【例3-4】直接利用Excel数据透视表功能从数据库中查询数据，并根据查询结果汇总，制作出如图3-7所示的分类汇总表。 提示：启动excel-数据数据透视表和数据透视图外部数据源,二、数据透视表结构的灵活性,改变数据透视表的行列结构 利用页域对数据进行分页汇总 新增、删除和修改行域、列域或页域字段（分类字段） 新增、删除和修改汇总字段
7、改变汇总字段的汇总方式 同一个字段既作分类字段又作汇总字段,二、数据透视表结构的灵活性（续）,改变分类字段值的位置 对分类字段的值加以合并 隐藏行域、列域的字段值 展开汇总值，显示详细数据,三、数据透视图的灵活性,变换分类字段的位置 将行、列分类字段换成页分类字段 新增、删除分类字段 新增、删除汇总字段 改变被汇总方式 显示分类字段的部分值,四、利用数据透视表生成时间序列,时间序列: 按照一定时间间隔汇总的数据序列 【例3-5】利用数据透视表，对Northwind Trader公司的销售数据按月汇总种各产品销售额。,例3-5 操作方法,数据进行数据查询-query，获得Northwind公司的
8、订购日期，产品名称，销售额(订单明细.单价*数量*(1-折扣) 光标停留在目标数据的任意单元格，数据数据透视表和数据透视图Microsoft Excel数据列表或数据库数据透视表，next，新建工作表，next。 订购日期拖至行域，销售额拖至列域，产品名称拖至页域。 右击目标数据任意单元格，组及显示明细数据组合，起始于1996-7-1，终止于1998-5-31，依据为先月，后年。,五、统计不同规模销量的发生次数及频率分布,【例3-6】利用数据透视表，对Northwind公司的销售数据按照不同产品、不同时间段，统计不同规模销售数量的发生次数，并计算其频率分布。,例3-6操作方法,利用外部数据获得
9、Northwind公司产品名称，订购日期，数量。 选定数据清单任意单元格，数据-数据透视表和数据透视图，默认选项next。 订购日期拖至行域，产品名称拖至页域，数量拖至数据域。 双击“求和项：数量”，汇总方式选“计数”-确定。 按例3-5方法对年月进行分组，然后将“年”和“订购日期”拖到页域，从数据透视表字段列表中将“数量”拖至行域，在数量列任意单元格右键，组及显示明细数据-组合，起始于1，终止于130，确定。 右击“数量”数据任意单元格，表格选项，去掉行、列总计。 D6单元格输入“销量分组”，E6输入“频率”，D7输入“=A7”，E7输入“B7/SUM($B$7:$B$19)”，将E7数据格
10、式设为百分数，小数点保留一位，并将D7和E7复制到D19和E19单元格。 绘制频率分布图：选中D6到E19，单击图表向导，选柱状图，next，next。,五、统计不同规模销量的发生次数及频率分布,Northwind公司不同规模销量的频率分布,0%,5%,10%,15%,20%,25%,30%,35%,1-10,11-20,21-30,31-40,41-50,51-60,61-70,71-80,81-90,91-100,101-110,111-120,121-130,计数项:数量,第四节 D函数和模拟运算表,D函数 模拟运算表 生成时间序列 统计不同规模销量的发生次数以及概率分布,一、 D函数,
11、D函数：对数据清单或数据库中的数据进行分析有 12个数据库内建函数。 常用的D函数包括： DSUM() DAVERAGE() DCOUNT() DCOUNTA(),一、 D函数（续）,D函数名称(数据清单,统计字段,条件范围) 统计满足条件记录制定字段的总计值。 数据清单：包含相关数据的工作表中的一系列数据行 统计字段：被汇总字段。可以使用字段名、字段名所在的单元格、或者字段在数据清单中所在列的序号。 条件范围：规定函数的匹配条件的一个工作表区域。D函数只对满足该条件范围所规定条件的记录进行汇总。,一、 D函数（续）,【例3-8】利用ABC公司的销售数据，应用DSUM()函数，生成一个与图3-
12、7相同的数据分类汇总表，列出ABC公司1995年不同省份不同类别净销售额总计值。,例3-8操作方法,1、获得数据清单。日期，省份，类别，净销售额，毛销售额。 2、命名一空白工作薄为DSUM()函数用法，在单元格B2:E2输入：日期、日期、省份、类别。在B3:E3输入：=95/1/1、=95/12/31、安徽、儿童用品。在B5输入：净销售额的总计值，D5输入：=DSUM(数据清单,被汇总字段,条件范围)，数据清单为abc公司销售数据，被汇总字段为净销售额，条件范围为B2:E3。 更方便方法：适用EXCEL的区域名。在数据清单选中A1:E3300，在区域名起新名字：原始数据。这时，DSUM()函数
13、可以写成DSUM(原始数据,净销售额,B2:E3)。 根据上面原理构造图3-7表。,一、 D函数（续）,通过上例知道，利用DSUM函数制作分类汇总表是可行的，只是过于繁琐，需要给汇总表的每个单元格都输入DSUM函数，还要给每个函数设定不同的条件。 EXCEL有个更好的工具，就是模拟运算表。 将DSUM函数和模拟运算表结合使用，可以避免DSUM函数的重复运算和条件的重复设置。,二、模拟运算表,模拟运算表 用于计算一系列自变量给定值对应的函数值。 自动计算函数值。 一维模拟运算表，计算一个自变量的一系列给定值的一个或若干个函数的函数值。 二维模拟运算表，计算两个自变量的系列给定值的一个函数的函数值
14、。,二、模拟运算表（续）,【例3-9】在例3-8的基础上，利用DSUM()函数和模拟运算表计算ABC公司不同省份不同类别净销售额总计值。,例3-9 操作方法,1、获得ABC公司的日期、省份、类别、净销售额、毛销售额。将数据清单命名为“原始数据”。 2、列出如例3-8的条件，输入DSUM函数公式。 3、在G3:G8放置ABC公司类别的名称如儿童用品等。 4、一维模拟运算表，汇总不同类别的净销售额： 在H2输入”=D5”。选中G2:H8，数据-模拟运算表，只需引入列（因为我们自变量一序列的值放在列中）：$E$3，确定。,二维模拟运算表，计算不同类别，不同省份的净销售额总计值。 同上，先计算出DSU
15、M函数值，先构造出行为省份，列为类别的二维表格，在行列交叉处输入DSUM函数“=D5”，选中需进行模拟运算的区域（G2:L8）。 然后，数据模拟运算表，将省份和类别依次输入模拟运算表的引用行$D$3、列$E$3单元格，确定。,以下内容部分不作要求,二、模拟运算表（续）,利用控件控制汇总条件分类字段 【例3-10】在例3-9的基础上，利用DSUM()函数和模拟运算表，计算ABC公司不同省份、不同类别净销售额总计值，使用窗体中的组合框对省份进行控制，当选择组合框中的不同省份时，模拟运算表计算出该省份净销售额总计值，并以柱形图显示该结果。,二、模拟运算表（续）,第五节 利用DSUM函数和控件控制分类
16、汇总结果,利用控件控制汇总条件年份,【例3-11】在例3-10的基础上，使用组合框控制汇总的年份，并以柱形图形式显示各个年份各个类别的净销售额总计值，如图3- 90所示。,利用控件控制汇总字段,第五节 利用DSUM函数和控件控制分类汇总结果（续）,【例3-12】在例3-11的基础上，使用列表框控制汇总字段为“净销售额”或“毛销售额”，并以柱形图形式显示结果，如图3-92所示 。,【例3-13】在例3-12的基础上，汇总特定省份和各省平均的净销售额，并以图形显示结果，如图3-94所示。,第五节 利用DSUM函数和控件控制分类汇总结果（续）,【例3-14】使用Northwind Trader 公司的销售数据，利用DSUM()和模拟运算表生成净销售额时间序列，创建可以调节的产品列表框，并绘制特定产品销售额的时间序列图形。,第五节 利用DSUM函数和控件控制分类汇总结果
