经济管理中的计算机应用第三章数据分类汇总分析.ppt
第三章 数据分类汇总分析,上海财经大学信息管理与工程学院,2,内容简介,数据分类汇总分析的意义和作用数据分类汇总的三种方法数据透视表D函数加模拟运算表,3,第一节 数据分类汇总分析的意义和作用,获得销售额分类汇总值获得各类销售额排行榜获得各类销售额的时间序列获得各经济量之间的相关性获得各种产品需求量的频率分布,4,一.获得销售额分类汇总值,利用分类汇总，可以获得不同地区的销售额、不同类别产品的库存量、不同生产人员的次品数量、不同客户的运货费、不同类型贷款的客户数等信息,5,二.获得各类销售额排行榜,利用分类汇总，企业可以获得销售额排行榜、各种商品质量指标排行榜、销售人员完成销售任务排行榜、各种产品库存量与库存积压资金排行榜,6,三.获得各类销售额的时间序列,利用分类汇总，企业可以获得某产品销售数量的时间序列、某地区销售额的时间序列或客户订货量的时间序列,7,四.获得各经济量之间的相关性,利用分类汇总，企业可以获得获得各经济量之间的相关性信息,8,五.获得各种产品需求量的频率分布,利用分类汇总，企业可以获得获得获得各种产品需求量的频率分布,9,第二节 数据分类汇总的三种方法,Excel数据列表功能数据透视表D函数家模拟运算表,10,一.Excel数据列表功能,数据列表被定义为“包含相关数据的一系列工作表数据行”。数据列表的首行为字段名，首行下的各行是各个记录。数据列表中不能出现空行。数据列表可以像数据库中的表一样使用，行对应于表中的记录，列对应用于表中的字段。,11,一.Excel数据列表功能,排序功能可以按照某个字段的升序或降序对数据列表中的所有记录进行排序。筛选功能筛选就是通过设定条件，挑选出满足条件的记录：自动筛选功能；高级筛选功能。高级筛选需首先在工作表中设定筛选条件。分类汇总功能可自动计算汇总字段的总计值。,12,一.Excel数据列表功能,【例3-1】利用Excel的数据列表功能和SUM函数，将ABC公司的销售数据，按照图3-7的形式，汇总出2010年各省份各类别商品的净销售额总计值。利用Excel数据列表功能和SUM函数分类汇总。,13,一.Excel数据列表功能,图3-7 ABC公司2010不同省份、不同类别商品净销售额,14,一.Excel数据列表功能,【例3-1】利用Excel的数据列表功能和SUM函数，将ABC公司的销售数据，按照图3-7的形式，汇总出2010年各省份各类别商品的净销售额总计值。利用Excel数据列表的分类汇总功能进行分类汇总。,15,二.数据透视表,创建数据透视表容易，且可以对数据透视表进行旋转、变换汇总角度，选择各种汇总形式，求和、计数、求平均值等。数据透视表使用方便、汇总能力强。我们将在第三节对数据透视表进行更详细的介绍。,16,三.D函数加模拟运算表,Execl共有 12 个数据库内建函数，称为D函数，用于对数据列表或数据库中的数据分析统计。DSUM函数可以对数据列表中满足条件记录的指定字段求总计值。模拟运算表是Excel自带的工具，它能够自动计算出一系列自变量给定值对应的函数值。将在第四节介绍该方法。,17,第三节 数据透视表,数据透视表汇总数据数据透视表的灵活性数据透视图的灵活性利用数据透视表生成时间序列统计各销量组销售次数的频率分布计算百分比多重区域数据的合并汇总,18,一.数据透视表汇总数据,利用数据透视表进行汇总有两种方法：一种是先将数据导入Excel成为数据列表，再对数据列表进行汇总；另一种是利用数据透视表直接从数据库中查询并汇总数据。,19,一.数据透视表汇总数据,【例3-3】利用Excel获取外部数据功能，获取ABC公司2010年的销售数据，使用Excel数据透视表功能，制作如图3-7所示的分类汇总表，并制作如图3-30所示的数据透视图。,20,一.数据透视表汇总数据,21,一.数据透视表汇总数据,【例3-3】直接利用Excel数据透视表的获取外部数据功能，获取并汇总数据，制作出如图3-7所示的分类汇总表。,22,二.数据透视表的灵活性,【例3-4】在上一例题基础上，利用数据透视表，汇总2010年ABC公司儿童用品与服装类别、华东与华南地区、各省、各渠道的净销售额，其中安徽与山东两省按照高、中、低价位分别显示净销售额。数据需按照净销售额的总计列排序，汇总结果如图3-43所示。,23,二.数据透视表的灵活性,分类字段的调整分类字段值的调整汇总字段的调整数据透视表工具的功能,24,二.数据透视表的灵活性,分类字段的调整利用报表筛选区域筛选数据：将数据透视表字段列表中的字段直接拖至报表筛选区域；从行标签或列标签区域，拖动字段至报表筛选区域；报表筛选区域，可以放置一个或多个字段；可改变多个字段的排列位置；可挑选一个值或多个值来进行筛选；可将报表筛选区域的字段拖动到其他区域；可删除“报表筛选”区域的字段。,25,二.数据透视表的灵活性,分类字段值的调整改变分类字段值的位置；分类字段值的组合；行、列标签区域字段值的筛选；折叠与展开汇总数据。,26,二.数据透视表的灵活性,汇总字段的调整新增、删除和修改汇总字段：新增汇总字段；改变汇总字段的位置；修改汇总字段名称；删除汇总字段。改变汇总字段的汇总方式。,27,二.数据透视表的灵活性,数据透视表工具的功能数据透视表工具的两个选项卡选项设计,28,三.数据透视图的灵活性,创建数据透视图的灵活性利用数据列表创建数据透视图；直接创建数据透视图；利用数据透视表（图）向导创建数据透视图。分类字段、汇总字段调整,29,四.利用数据透视表生成时间序列,按照一定时间间隔汇总的数据序列，称为时间序列。时间序列可以帮助企业了解经营状况、预测未来的变化趋势。数据透视表能从不同的角度汇总数据，它也是生成时间序列的有效工具。,30,四.利用数据透视表生成时间序列,【例3-5】利用数据透视表，对Northwind公司的销售数据按月汇总各产品的销售额。,31,五.统计各销量组销售次数的频率分布,频率信息可以帮助管理人员预测未来情况，合理安排生产和销售活动。【例3-6】利用数据透视表，对Northwind公司的销售数据按照产品、年月汇总，统计各规模销量组销售次数的频率分布。,32,六.计算百分比,【例3-7】Northwind公司计划，根据客户总销售额，将客户划分为不同级别。销售额超过10000元的客户为重要客户，10000元以下的为普通客户。请利用如图3-92所示的数据透视表，按照客户级别，汇总销售额、销售额占总销售额的百分比、销售次数占总销售次数的的百分比，并绘制数据透视图。,33,七.多重区域数据的合并汇总,有些企业，由于经营地点比较分散，导致不同地点的各部门将数据存放在多个Excel工作簿或多个工作表中。有些企业，会将数据按照时间存放在多个工作簿或工作表中。这样，就产生了相同字段的数据分散放在多重区域的情况。存放在多重区域的数据，在汇总时需要首先将数据合并，然后再汇总。,34,七.多重区域数据的合并汇总,【例3-8】NorthLand公司2011年1月、2月与3月的销售数据，保存在工作簿中“NorthLand公司各月销售数据.xlsx”的三张工作表中，如图3-102所示。这些销售数据记录了该公司3个月中在南京、苏州、无锡等地的销售情况。请利用这些数据，汇总该公司2011年1季度各地区的销售数量与销售金额。,35,七.多重区域数据的合并汇总,36,七.多重区域数据的合并汇总,【例3-9】利用工作簿“NorthLand公司各月销售数据.xlsx”保存的NorthLand公司2011年1、2、3月的销售数据，汇总该公司2011年1季度各地区、各客户、各产品的销量与销售金额。,37,七.多重区域数据的合并汇总,38,第四节 D函数加模拟运算表,D函数模拟运算表利用控件控制汇总结果利用D函数与控件汇总时间序列利用控件控制多个汇总图表利用控件控制多个汇总相关项D函数加控件汇总方法的总结各种汇总方法的总结,39,一.D函数,Microsoft Excel 共有 12 个数据库内建函数，统一称为D函数，用于对数据列表中的数据进行分类汇总分析,40,一.D函数,D函数名称(数据列表，汇总字段，条件区域)数据列表，是包含相关数据的工作表中的一块数据区域；汇总字段，可以是字段名、字段名所在的单元格或字段在数据列表中所在列的序号；条件区域，是一个区域，它规定函数的匹配条件。只有满足条件的记录，才能够被该函数汇总；条件区域首行是字段名，以后各行是字段的值。,41,一.D函数,【例3-10】利用ABC公司的销售数据，应用DSUM函数，汇总ABC公司各省份各类别净销售额的总计值，结果如图3-7所示。,42,二.模拟运算表,模拟运算表工具，可用于计算一系列自变量给定值对应的函数值。如果已知函数关系，同时又给定该函数关系中自变量的一系列值，Excel可以自动计算所有自变量给定值对应的函数值。一维模拟运算表，可以计算一个自变量所有给定值对应的一个或多个函数的函数值二维模拟运算表可以计算两个自变量所有给定值对应的一个函数的函数值。,43,三.利用控件控制汇总结果,在D函数与模拟运算表的基础上，增加控件，可以对汇总的结果进行控制。控件可以与利用汇总结果制作的图形相结合，称为可调图形或可选图形。这些图形能随时通过调整控件，控制、改变汇总方式，直观灵活地展示汇总结果。,44,三.利用控件控制汇总结果,【例3-11】利用DSUM函数加模拟运算表，计算ABC公司各类别商品的净销售额总计值。使用控件对省份、年份、净毛销售额进行控制，并绘制可调的净销售额总计值柱形图，如图3-132所示。,45,四.利用D函数与控件汇总时间序列,【例3-12】根据Northwind公司的销售数据，利用DSUM和模拟运算表，计算生成Northwind公司销售额的时间序列。使用控件对产品进行控制，并绘制产品可调的销售额时间序列图形。,46,四.利用D函数与控件汇总时间序列,47,四.利用D函数与控件汇总时间序列,48,五.利用控件控制多个汇总图表,【例3-13】利用Northwind公司的销售数据，分析Northwind前十大客户各个季度销售额变动情况，制作一个如图3-152图所示的可调图形。当选择列表框中某客户公司时，折线图反映该公司各季度销售额的变动情况。在列表框旁的柱形图上同时显示该公司总销售额在十大客户中的位置。,49,五.利用控件控制多个汇总图表,50,六.利用控件控制多个汇总相关项,【例3-14】利用Northwind公司的销售数据，按照地区、类别、季度汇总销售额与数量的总额与平均值。制作一个如图3-152图所示的可调图形。,51,七.D函数加控件汇总方法的总结,一般D函数加控件的汇总方法包括以下步骤：第一步，获得数据列表；第二步，制作控件；第三步，设定汇总条件与汇总字段等，利用D函数汇总；第四步，制作模拟运算表，绘制图表。,52,七.D函数加控件汇总方法的总结,53,七.D函数加控件汇总方法的总结,框架：控件区，放置制作控件所需的数据；可控变量区，放置受到控件控制的条件字段的字段名、条件值、汇总字段的字段名等；D函数条件区；D函数汇总区；模拟运算表自变量准备区；模拟运算表区。,54,八.各种汇总方法的总结,查询的统计值功能Excel的数据列表功能数据透视表功能DSUM函数加模拟运算表,55,八.各种汇总方法的总结,利用查询的统计值功能，数据可在Microsoft Query中进行汇总，并将结果返回Excel。该方法使用方便，效率高，但没有进一步操作和控制的灵活性。利用Excel的数据列表功能方法，步骤最多、最烦琐，但是Excel的排序、筛选与分类汇总功能可以应用于其他场合。数据透视表使用最方便，汇总后可以进行控制，如改变浏览数据的角度、合并数据、展开详细数据、或者选择部分数据加以查看等。,56,八.各种汇总方法的总结,DSUM函数加模拟运算表方法也能够分类汇总数据，并对汇总数据加以控制，在熟练掌握这种方法之后，可以进行更为复杂，甚至数据透视表无法实现的汇总导入到Excel中的数据列表和数据透视表还保持着与原始数据库之间的联系。在数据库的数据更新之后，选中数据透视表或数据列表，右键单击鼠标，在弹出的菜单中，选择“刷新”，可将数据更新,57,本章小结,在Excel中，实现分类汇总的三种方法包括：Excel的数据列表功能、数据透视表功能、D函数加模拟运算表。利用Excel的数据列表功能方法，步骤多、烦琐。数据透视表使用最方便，可以把汇总表“旋转”，从不同的“角度”查看数据，还可以筛选数据、合并数据、展开详细数据、或者选择部分数据加以查看。数据透视表可以直接表现为数据透视图的形式，数据透视图以图表形式显示汇总结果，图表类型可选择“柱形”、“折线”等多种。数据透视表可方便地生成时间序列、以及频率分布汇总数据,58,本章小结,DSUM函数加模拟运算表方法能够汇总数据，并可对汇总数据加以控制：它可以控制汇总的条件、汇总字段等内容。本章所用技术包括数据列表的各项功能、数据透视表的建立、模拟运算表的建立、可选式图形的绘制、组合框和列表框的制作等，使用到的函数包括D函数、IF、INDEX、MATCH、YEAR、MONTH、DATE、LEFT、MID等函数。这些技术和函数还会在以后的章节中反复使用，希望读者能熟练掌握。,
