数据分类汇总分析.ppt

内容简介基础篇数据分类汇总分析的意义和作用数据分类汇总的四种方法数据透视表D函数和模拟运算表提高篇利用DSUM函数和控件控制分类汇总结果分类汇总数据的应用Excel中的多维数据分析OLAP第一节 数据分类汇总分析的意义和作用获得销售额分类统计值第一节 数据分类汇总分析的意义和作用（续）获得各类销售额排行榜销售额排行榜第一节 数据分类汇总分析的意义和作用（续）获得各类销售额的时间序列销售额排行榜第一节 数据分类汇总分析的意义和作用（续）获得各经济量之间的相关性销售额排行榜第一节 数据分类汇总分析的意义和作用（续）获得各种产品需求量的频率分布第二节 数据分类汇总的四种方法数据查询的统计值功能数据清单功能数据透视表D函数与模拟运算表第二节 数据分类汇总的四种方法汇总字段：待汇总的变量或字段。销售额、销售数量、工作时间等。分类字段：对数据进行汇总时，以某个变量或字段的不同值为参考来对汇总字段进行汇总。参考不同产品类别(分类字段)汇总销售额（汇总字段）参考不同的生产人员姓名（分类字段）汇总工作时间 （汇总字段）销售额排行榜第二节 数据分类汇总的四种方法（续）ABC公司1995各省份、各类别商品净销售额总计值（图3-7）一、数据查询的统计值功能从Excel中启动Microsoft Query，利用Query的统计值功能，将数据汇总后返回Excel【例3-1】ABC公司销售数据.dbf中保存着公司从1994年到1996年三年的销售数据，利用Query软件的统计值功能汇总该公司的销售数据，并按照图3-7的形式，汇总出1995年不同省份、不同类别商品的净销售额。销售额排行榜一、数据查询的统计值功能（续）二、Excel数据清单功能数据清单数据清单是包含相关数据的一系列工作表数据行。数据清单的首行为字段名，首行下的各行是各个记录，数据清单中不能出现空行。数据清单的基本功能排序筛选分类总计销售额排行榜二、Excel数据清单功能（续）利用Excel数据清单处理功能进行分类汇总的一般步骤：获得数据清单；设定筛选条件；筛选数据，将筛选结果复制到工作表其他位置；对经过筛选的数据按分类字段进行排序；对排序后的数据进行分类汇总。销售额排行榜二、Excel数据清单功能（续）【例3-2】利用Excel的数据清单功能，将ABC公司的销售数据按照图3-7的形式，汇总出1995年不同省份、不同类别商品的净销售额总计值。第三节 数据透视表数据透视表的基本功能数据透视表结构的灵活性数据透视图的灵活性利用数据透视表生成时间序列统计不同规模销量的发生次数以及概率分布计算占同列数据总和的百分比销售额排行榜一、数据透视表的基本功能数据透视表分类汇总的两种方法先将数据导入Excel成为数据清单，利用数据透视表汇总对数据清单进行汇总利用数据透视表直接从数据库中查询、并汇总数据销售额排行榜一、数据透视表的基本功能（续）【例3-3】利用Excel获取外部数据功能，获取ABC公司1995年的销售数据，使用Excel数据透视表功能制作如图3-7所示的分类汇总表。【例3-4】直接利用Excel数据透视表功能从数据库中查询数据，并根据查询结果汇总，制作出如图3-7所示的分类汇总表。一、数据透视表的基本功能（续）销售额排行榜一、数据透视表的基本功能（续）二、数据透视表结构的灵活性改变数据透视表的行列结构利用页域对数据进行分页汇总新增、删除和修改行域、列域或页域字段（分类字段）新增、删除和修改汇总字段改变汇总字段的汇总方式同一个字段既作分类字段又作汇总字段二、数据透视表结构的灵活性（续）改变分类字段值的位置对分类字段的值加以合并隐藏行域、列域的字段值展开汇总值，显示详细数据三、数据透视图的灵活性变换分类字段的位置将行、列分类字段换成页分类字段新增、删除分类字段新增、删除汇总字段改变被汇总方式显示分类字段的部分值四、利用数据透视表生成时间序列时间序列:按照一定时间间隔汇总的数据序列【例3-5】利用数据透视表，对Northwind Trader公司的销售数据按月汇总种各产品销售额。五、统计不同规模销量的发生次数及频率分布【例3-6】利用数据透视表，对Northwind公司的销售数据按照不同产品、不同时间段，统计不同规模销售数量的发生次数，并计算其频率分布。五、统计不同规模销量的发生次数及频率分布六、计算占同列数据总和的百分比【例3-7】Northwind Trader公司根据客户总销售额将客户划分为不同级别，20000元以上为黄金客户，10000元20000元为重要客户，10000以下为普通客户。按照客户的不同级别汇总各级客户的总销售额、销售额占总销售额的百分比，以及该级客户销售次数占总销售次数的比重，如图3-69所示，并绘制数据透视图。第四节 D函数和模拟运算表D函数模拟运算表生成时间序列统计不同规模销量的发生次数以及概率分布一、D函数D函数：对数据清单或数据库中的数据进行分析有 12个数据库内建函数。常用的D函数包括：DSUM()DAVERAGE()DCOUNT()DCOUNTA()一、D函数（续）D函数名称(数据清单,统计字段,条件范围)数据清单：包含相关数据的工作表中的一系列数据行统计字段：汇总字段。可以使用字段名、字段名所在的单元格、或者字段在数据清单中所在列的序号。条件范围：规定函数的匹配条件的一个工作表区域。D函数只对满足该条件范围所规定条件的记录进行汇总。一、D函数（续）【例3-8】利用ABC公司的销售数据，应用DSUM()函数，生成一个与图3-7相同的数据分类汇总表，列出ABC公司不同省份不同类别净销售额总计值。一、D函数（续）二、模拟运算表模拟运算表用于计算一系列自变量给定值对应的函数值。自动计算函数值。一维模拟运算表，计算一个自变量的一系列给定值的一个或若干个函数的函数值。二维模拟运算表，计算两个自变量的系列给定值的一个函数的函数值。二、模拟运算表（续）【例3-9】在例3-8的基础上，利用DSUM()函数和模拟运算表计算ABC公司不同省份不同类别净销售额总计值。二、模拟运算表（续）利用控件控制汇总条件分类字段【例3-10】在例3-9的基础上，利用DSUM()函数和模拟运算表，计算ABC公司不同省份、不同类别净销售额总计值，使用窗体中的组合框对省份进行控制，当选择组合框中的不同省份时，模拟运算表计算出该省份净销售额总计值，并以柱形图显示该结果。二、模拟运算表（续）第五节 利用DSUM函数和控件控制分类汇总结果利用控件控制汇总条件年份n n【例例例例3-113-11】在在在在例例例例 3-103-10的的的的 基基基基 础础础础上上上上，使使使使用用用用组组组组合合合合框框框框控控控控制制制制汇汇汇汇总总总总的的的的年年年年份份份份，并并并并以以以以柱柱柱柱形形形形图图图图形形形形式式式式显显显显示示示示各各各各个个个个年年年年份份份份各各各各个个个个类类类类别别别别的的的的净净净净销销销销售售售售额额额额总总总总计计计计值值值值，如如如如图图图图3-903-90所示。所示。所示。所示。利用控件控制汇总字段第五节 利用DSUM函数和控件控制分类汇总结果（续）n n【例例例例 3-123-12】在在在在例例例例3-113-11的的的的基基基基础础础础上上上上，使使使使用用用用列列列列表表表表框框框框控控控控制制制制汇汇汇汇总总总总字字字字段段段段为为为为“净净净净销销销销售售售售额额额额”或或或或“毛毛毛毛销销销销售售售售额额额额”，并并并并以以以以柱柱柱柱形形形形图图图图形形形形式式式式显显显显示示示示结结结结果果果果，如如如如图图图图3-923-92所示所示所示所示 。【例3-13】在例3-12的基础上，汇总特定省份和各省平均的净销售额，并以图形显示结果，如图3-94所示。第五节 利用DSUM函数和控件控制分类汇总结果（续）【例3-14】使用Northwind Trader 公司的销售数据，利用DSUM()和模拟运算表生成净销售额时间序列，创建可以调节的产品列表框，并绘制特定产品销售额的时间序列图形。第五节 利用DSUM函数和控件控制分类汇总结果（续）第六节 分类汇总数据的应用企业经常在“观测板”上显示企业最关心的信息：销售额排在前十位产品的当月销售情况，最优秀员工目前的工作进度，销售额增长最快地区的销售增长率，以及销路最差产品的销售情况等等。例3-15，介绍为Northwind Trader公司发现连续2个季度销量下滑产品的“观测版”的方法。零售超市需要对那些易变质食品打折，越接近保质期的产品打折比例越高。例3-16帮助超市找到需要打折的产品，确定打折比例。第六节 分类汇总数据的应用（续）【例3-15】Northwind Trader公司将根据产品的销售情况来调整产品结构。他们决定找到那些连续两个季度销量都在下降的产品，分析产品销售量下降原因，进而决定是否停止这些产品的进货和销售。试为Northwind Trader公司找到连续两个季度销量下降的产品，并以柱形图的形式显示这些产品最近三个季度的销量，如图3-106所示。第六节 分类汇总数据的应用（续）第六节 分类汇总数据的应用（续）第一步，获得所需数据；第二步，增加新字段“订购季度”；第三步，计算不同季度和各产品的总销量；第四步，利用模拟运算表，计算97年第3季度、97年第4季度和98年第1季度各产品的销售总量；第五步，计算两个季度销量的下降数量、累计下降数量，并按照从小到大的顺序排列；第六步，找出连续两季度销量下降的产品以及其前3季度度、前2季度、前1季度的销量；第七步，绘制柱形图。第六节 分类汇总数据的应用（续）【例3-16】迅捷是一家便利店，销售日用品、饮料和食品等产品。对于部分易变质的饮料和食品，因为保质期比较短，他们总是先销售先进货的产品。他们为每种产品制订出了打折的开始时间（产品库存数据工作表中的打折提前天数），不同时间对应的打折比例（即每天打折比例，例如产品“康康原味酸奶”在到期的前3天给与15%的折扣，前2天给与30%的折扣，前1天给与45%的折扣，当天给与60%的折扣），如图3-114所示。迅捷便利店希望利用产品库存数据和进货数据，如图3-114和图3-115所示。建立模型，发现需要打折的产品、打折比例。第六节 分类汇总数据的应用（续）产产品品库库存存数数据据进进货货原原始始数数据据第六节 分类汇总数据的应用（续）第一步，在原有进货数据清单中增加新字段“产品进货次数”和“产品ID与订货次数”；第二步，计算某产品的累计进货次数；第三步，计算当前库存产品中倒数第2次进货剩余的产品；第四步，计算产品到保质期剩余天数；第五步，找到该产品打折开始时间、计算打折比例、库存总数量、打折产品数量；第六步，利用模拟运算表计算所有产品的打折开始时间、计算打折比例、库存总数量、打折产品数量；第七步，制作不同汇总方式的组合框；第八步，准备绘图数据；第九步，绘图。第七节 Excel中的多维数据分析OLAP多维数据分析的定义通过关系数据库创建多维数据集 多维数据分析的定义OLAP定义数据字段和维确定数据字段确定维指定源数据多维数据分析的定义（续）OLAP定义联机分析处理(OLAP，Online Analytical Processing)是根据层次分类来预先计算汇总值的一种组织数据的方式，它符合用户检索和分析数据的习惯。按照OLAP 组织数据可以提高检索数据的速度。因为在创建或更改报表时，OLAP 服务器（而不是 Microsoft Excel）会计算汇总值，并且只有汇总的数据（而非详细数据）送到Excel中，减少了数据量，提高了Excel的运算速度。多维数据分析的定义（续）数据字段和维OLAP多维数据集中包含：汇总值，被称为数据字段，即汇总字段。有关这些值的说明，即分类字段，则被组织成不同级别的明细数据，被称为维。有了数据字段和维，就可以很容易地在报表中显示较高级别的汇总以及低级别的汇总。多维数据分析的定义（续）确定数据字段数据字段是用户希望在报表中出现的汇总字段销售额、销售量、库存量多维数据集可按四种方式汇总数据字段，即求总计、计数、最小和最大值多维数据分析的定义（续）确定维可以在分类层次上进行组织的说明字段构成了报表中的维，它包含数据某个方面的一组级别（如：地理位置）。多维数据分析的定义（续）指定源数据多维数据集中的所有字段都必须是数据字段或维。若要使查询或选定的源数据能更高效地运行，并且使采用这些数据的报表更快更新，则应该只包含那些必须在多维数据集中使用的字段。通过关系数据库创建多维数据集利用Microsoft Query调用“OLAP多维数据集向导”，创建多维数据集。【例3-17】针对Northwind Trader公司的销售数据，利用Microsoft Query创建多维数据集文件，并通过数据透视表浏览按照客户所在地理位置汇总的销售额。利用Excel调用“OLAP多维数据集向导”，创建多维数据集。【例3-18】为Northwind Trader 公司创建脱机多维数据集文件，并通过数据透视表显示销售额，该销售额需按照产品的类型维（包括类型和产品名称字段）、时间维、供应商地理位置维（包括国家、地区、城市）来汇总。通过关系数据库创建多维数据集（续）【例3-17】针对Northwind Trader公司的销售数据，利用Microsoft Query创建多维数据集文件，并通过数据透视表浏览按照客户所在地理位置汇总的销售额。第一步，利用Microsoft Query查询数据集中应包含的数据；第二步，创建OLAP多维数据集；第三步，利用数据透视表生成报表，浏览数据。通过关系数据库创建多维数据集（续）通过关系数据库创建多维数据集（续）【例3-18】为Northwind Trader 公司创建脱机多维数据集并利用数据透视表显示销售额。该销售额需按照产品的类型维（包括类型和产品名称字段）、时间维、供应商地理位置维（包括国家、地区、城市）进行汇总。本章小结实现分类汇总的四种方法数据查询的统计值功能使用方便，效率高，但缺乏控制的灵活性 Excel的数据清单功能步骤多、烦琐，但是很多功能可以应用于其他问题 数据透视表功能使用最方便，可以把汇总表“旋转”，从不同的“角度”查看数据，还可以筛选数据、合并数据、展开详细数据、或者选择部分数据加以查看D函数和模拟运算表能够汇总数据，并可对汇总数据加以控制本章小结（续）DSUM、模拟预算表、内建函数可以汇总数据透视表无法提供的数据DSUM函数所提供汇总能力、模拟运算表的自动计算能力再配合Excel的内建函数可以为企业决策提供有利支持，比如，为企业决策者提供重要客户、产品、员工的“观测板”，以及时了解对企业发展最为关键的信息，也能够帮助零售企业发现需打折的产品、确定打折比例等。本章小结（续）联机分析处理(OLAP，Online Analytical Processing)是根据层次分类来预先计算汇总值的一种组织数据的方式：OLAP多维数据集仅提供汇总结果，不提供详细数据。OLAP多维数据集在Excel中只能通过数据透视表或数据透视图显示。