EXCEL第3章_数据分类汇总分析.ppt

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 公司

17、的销售数据，利用DSUM()和模拟运算表生成净销售额时间序列，创建可以调节的产品列表框，并绘制特定产品销售额的时间序列图形。,第五节 利用DSUM函数和控件控制分类汇总结果（续）,第六节分类汇总数据的应用,企业经常在“观测板”上显示企业最关心的信息：销售额排在前十位产品的当月销售情况，最优秀员工目前的工作进度，销售额增长最快地区的销售增长率，以及销路最差产品的销售情况等等。 例3-15，介绍为Northwind Trader公司发现连续2个季度销量下滑产品的 “观测版”的方法。 零售超市需要对那些易变质食品打折，越接近保质期的产品打折比例越高。 例3-16帮助超市找到需要打折的产品，确定打折比

18、例。,第六节分类汇总数据的应用（续）,【例3-15】Northwind Trader公司将根据产品的销售情况来调整产品结构。他们决定找到那些连续两个季度销量都在下降的产品，分析产品销售量下降原因，进而决定是否停止这些产品的进货和销售。试为Northwind Trader公司找到连续两个季度销量下降的产品，并以柱形图的形式显示这些产品最近三个季度的销量，如图3- 106所示。,第六节分类汇总数据的应用（续）,第六节分类汇总数据的应用（续）,第一步，获得所需数据； 第二步，增加新字段“订购季度”； 第三步，计算不同季度和各产品的总销量； 第四步，利用模拟运算表，计算97年第3季度、97年第4季度和

19、98年第1季度各产品的销售总量； 第五步，计算两个季度销量的下降数量、累计下降数量，并按照从小到大的顺序排列； 第六步，找出连续两季度销量下降的产品以及其前3季度度、前2季度、前1季度的销量； 第七步，绘制柱形图。,第六节分类汇总数据的应用（续）,【例3-16】迅捷是一家便利店，销售日用品、饮料和食品等产品。对于部分易变质的饮料和食品，因为保质期比较短，他们总是先销售先进货的产品。他们为每种产品制订出了打折的开始时间（产品库存数据工作表中的打折提前天数），不同时间对应的打折比例（即每天打折比例，例如产品“康康原味酸奶”在到期的前3天给与15%的折扣，前2天给与30%的折扣，前1天给与45%的折

20、扣，当天给与60%的折扣），如图3-114所示。迅捷便利店希望利用产品库存数据和进货数据，如图3-114和图3-115所示。建立模型，发现需要打折的产品、打折比例。,第六节分类汇总数据的应用（续）,产 品 库 存 数 据,进 货 原 始 数 据,第六节分类汇总数据的应用（续）,第一步，在原有进货数据清单中增加新字段“产品进货次数”和“产品ID与订货次数”； 第二步，计算某产品的累计进货次数； 第三步，计算当前库存产品中倒数第2次进货剩余的产品； 第四步，计算产品到保质期剩余天数； 第五步，找到该产品打折开始时间、计算打折比例、库存总数量、打折产品数量； 第六步，利用模拟运算表计算所有产品的打折

21、开始时间、计算打折比例、库存总数量、打折产品数量； 第七步，制作不同汇总方式的组合框； 第八步，准备绘图数据； 第九步，绘图。,第七节Excel中的多维数据分析OLAP,多维数据分析的定义 通过关系数据库创建多维数据集,多维数据分析的定义,OLAP定义 数据字段和维 确定数据字段 确定维 指定源数据,多维数据分析的定义（续）,OLAP定义 联机分析处理 (OLAP，Online Analytical Processing) 是根据层次分类来预先计算汇总值的一种组织数据的方式，它符合用户检索和分析数据的习惯。 按照OLAP 组织数据可以提高检索数据的速度。因为在创建或更改报表时，OLAP 服务器

22、（而不是 Microsoft Excel）会计算汇总值，并且只有汇总的数据（而非详细数据）送到Excel中，减少了数据量，提高了Excel的运算速度。,多维数据分析的定义（续）,数据字段和维 OLAP多维数据集中包含： 汇总值，被称为数据字段，即汇总字段 。 有关这些值的说明，即分类字段， 则被组织成不同级别的明细数据，被称为维。 有了数据字段和维，就可以很容易地在报表中显示较高级别的汇总以及低级别的汇总。,多维数据分析的定义（续）,确定数据字段 数据字段是用户希望在报表中出现的汇总字段 销售额、销售量、库存量 多维数据集可按四种方式汇总数据字段，即求总计、计数、最小和最大值,多维数据分析的定

23、义（续）,确定维 可以在分类层次上进行组织的说明字段构成了报表中的维，它包含数据某个方面的一组级别（如：地理位置） 。,多维数据分析的定义（续）,指定源数据 多维数据集中的所有字段都必须是数据字段或维。若要使查询或选定的源数据能更高效地运行，并且使采用这些数据的报表更快更新，则应该只包含那些必须在多维数据集中使用的字段。,通过关系数据库创建多维数据集,利用Microsoft Query调用“OLAP多维数据集向导”，创建多维数据集。 【例3-17】针对Northwind Trader公司的销售数据，利用Microsoft Query创建多维数据集文件，并通过数据透视表浏览按照客户所在地理位置汇

24、总的销售额。 利用Excel调用“OLAP多维数据集向导”，创建多维数据集。 【例3-18】为Northwind Trader 公司创建脱机多维数据集文件，并通过数据透视表显示销售额，该销售额需按照产品的类型维（包括类型和产品名称字段）、时间维、供应商地理位置维（包括国家、地区、城市）来汇总。,通过关系数据库创建多维数据集（续）,【例3-17】针对Northwind Trader公司的销售数据，利用Microsoft Query创建多维数据集文件，并通过数据透视表浏览按照客户所在地理位置汇总的销售额。 第一步，利用Microsoft Query查询数据集中应包含的数据； 第二步，创建OLAP多维数据集； 第三步，利用数据透视表生成报表，浏览数据。,通过关系数据库创建多维数据集（续）,通过关系数据库创建多维数据集（续）,求和 ： 销售额,类别名称,产品名称,点心,点心 汇总,调味品,谷类/ 麦片,海鲜,年,季度,月,饼干,蛋糕,19