数据分类汇总分析

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