数据管理与数据透视表Excel2007版教材.ppt

1、第7章 数据管理与数据透视表,Excel与数据处理 第3版,本章学习目标,1、掌握数字与汉字排序的区别 2、掌握EXCEL排序的方法 3、理解条件区域 4、掌握数据筛选的方法 5、掌握工作表数据的分类汇总方法 6、掌握数据透视表的功能及用法 7、应用数据链接管理多表数据,7.1 数据排序,1、排序规则 数字排序规则 字母排序规则 逻辑值排序规则 汉字排序规则 汉语拼音序 按笔画排序 2、排序方式 升序 降序,7.1.2 数值排序,3、排序案例 【例7.1】某班某次期末考试成绩如图（a）所示，已经计算出了每位同学的平均分，现需得出一个名次表，即从高分到低分的成绩表，以便为前3名同学发奖学金,7.

2、1.2 数值排序,按日期排序,7.1.3 汉字与字符排序,按汉字笔画排序,1、选中要排序的数据区域后,单击”排序和筛选”按钮,2、单击”自定义排序”按钮，弹出“排序”对话框,3、单击”选项”按钮，弹出“选项”对话框,3、指定”笔画序,7.1.4 自定义排序次序,自定义排序 有时需要按人为指定的次序对某此资料排序。 【例7.3】某大学有7个学院，每个学院的基本情况如图所示,要求按以下次序排序此表：计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院,7.1.4 自定义排序次序,方法 单击Office 按钮 “Excel 选项”“常用”“使用 Excel 时采用的首选项”“编辑自

3、定义列表”，Excel会弹出“选项”对话框 按前面的方式显示出“排序”对话 框中，通过“序序”下面的“自定义序列”指定建立的序列为排序依据,在此输入自定义的排序序列,7.1.5 账号、零件号等排序,账号、零件号排序的困难 由于账号、零件号常采用不同的区段进行编码，为排序带来了困难。 账号、零件号排序的解决方法 一种方法是确保同类编号中的任何部分都有相同的长度。例如在上述例子中，将“ZK-999-10”输入为“ZK-0999-10”，这样在比较排序时就不会出错了。 另一种方法是把编号的不同部分输入在不同列的单元格中，如上面两个编号用图7.6所示的方式输入。在排序时，对编号所涉及到的三列同时进行排

4、序，排序结果就不会出错，最后再用“&”运算符把排序后的各部分组合起来,7.1.6 多关键字排序,多关键字排序 就是对数据表中的数据按两个或两个以上的关键字进行排序。多关键字排序可使数据在第一关键字相同的情况下，按第二关键字排序，在第一、第二关键字都相同的情况下，数据按第三关键字有序，其余的以此类推。 在Excel 2007中，最多允许有64个排序关键字，但不管有多少关键字，排序之后的数据总是按第一关键字有序的,7.1.6 多关键字排序,例7.4】 2004年某杂货店各雇员的销售数据如图7.7（a）的A1:F7所示，以第一季度为第一关键字、第二季度为第二关键字、第三季度为第三关键字的排序结果如图

5、7.7（a）的A9:F15区域所示，排序的方式是递增,1、源数据表,2、单击“开始”“排序和筛选”“自定义排序”命令，弹出“排序”对话框,3、依次指定排序关键字,3、排序结果,7.2 数据筛选,1、概念 数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行 。 2、Excel的数据筛选方式 1、自动筛选 自动筛选提供了快速查找工作表数据的功能，只需通过简单的操作，就能够筛选出需要的数据 。 2、高级筛选 高级筛选能够完成自动筛选所不能实现的复杂筛选,7.2.1 数据筛选,3、自动筛选 案例 【例7.5】 某公司的人员档案是按招聘报到的先后次序建立的，如图7.8（

6、a）所示。可以看出，图7.8的档案比较混乱，公司的管理人员可能需要这样的信息：已婚人员的名单，各部门的人员档案信息，工资高于1 200的人员有哪些，奖金高于1000的人员有哪些，各种学历的人员档案 （1）已婚人员的名单。 （2）各部门的人员档案信息。 （3）工资高于1 200的人员有哪些。 （4）奖金高于1000的人员有哪些。 （5）各种学历的人员档案 如此等等,7.2.1 数据筛选,原数据表,具有自动筛标记的数据表,单击“数据”|“筛选”，Excel会在表格的标题边显示出自动筛选标志。可借此完成各种自动筛选,7.2.1 数据筛选,自动筛选的结果示例筛选出运维部的工作人员,7.2.1 数据筛选

7、,如果筛选关键字对应的列是数值型数据，可通过“数字筛选”命令指定自定义筛选条件，实现灵活的数据查询。 【例7.6】 在图7.8(a)所示的职工档案表中，筛选年龄最大的3名职工,1、单击“数据”！“筛选”为数据表显示出自动筛选标志,2、单击“年龄”右边的自动筛选下拉箭头,3、单击“10个最大的值”，弹出对话框,4、将“10”改为3，即可筛选出年龄最大的3位员工,例7.7】查看图7.10中职工档案表中工资高于2800元的人员,1、指定工作表的“筛选”标志,2、单击“工资筛选”中的“大于”命令,3、在弹出的对话框中输入工资数据,5.2 数据筛选,4、高级筛选 高级筛选的条件区域 的类型 （1）“或”

8、条件 （2）“与”条件 （3）多列的“或”条件 （4）用“或”连接的“与”条件,7.2.2 条件区域,条件区域的构建方法,7.2.3 高级筛选,1、条件“与筛选” 【例7.8】 查看例7.5（图7.8）中“运维部”工资和奖金都高于500元的人员,1、单击“高级”按钮，弹出高级筛选设置对话框,2、指定筛选结果的保存方式,2、指定筛选条件和数据区域,7.2.3 高级筛选,2条件“或”的高级筛选 【例7.9】 对于图7.8，现要找出其中奖金小于500或已经结婚的工作人员,7.2.3 高级筛选,3与条件同或条件的组合应用 【例7.10】 对于前面讨论的职工档案数据表，现要查找运维部和信息资源部中工资高

9、于1000且奖金高于500的人员档案,7.2.3 高级筛选,4、使用计算条件的高级筛选 什么是计算条件 计算条件的构造方法 计算条件中的标题可以是任何文本或都是空白，但不能与数据清单中的任一列标相同，这一点正好与前面讨论的条件区域相反。 必须以绝对引用的方式引用数据清单或数据库中之外的单元格。 必须以相对引用的方式引用数据清单或数据库中之内的单元格,7.2.3 高级筛选,基于计算条件的高级筛选案例 【例7.11】某商场2003年2月1号到8号的销售记录如图7.18的区域A2:F10所示，找出其中销售额高于平均值的销售记录,7.3 数据的分类与汇总,1、概述 分类汇总就是对工作表中指定的行或列中

10、的数据进行汇总统计，它通过折叠或展开原工作表中的行、列数据及汇总结果，从汇总和明细两种角度显示数据，可以快捷地创建各种汇总报告。分类汇总的数据折叠层次可达8层。 2、分类汇总能够完成以下事性 在数据表中显示一组或多组数据的分类汇总及总和。 在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）等,7.3 数据的分类与汇总,案例 右图是某公司的职工工资表。 完成以下事情： 每部门有多少人； 每部门平均工资； 哪个部门的总评奖金最高， 应分发给各部门的总金额各是多少,通过函数能够完成这些事件，但分类汇总简单多了,7.3 数据的分类与汇总,3、分类汇总的准备工作 分类汇总表的每个数

11、据列都有列标题； 必须对要进行分类汇总的列排序。 排序的列标题称为分类汇总关键字，在进行分类汇总时，只能指定排序后的列标题为汇总关键字,按部门排序后的工作表,7.3 数据的分类与汇总,4、建立分类汇总 单击“数据”选项卡中的“分类汇总”命令,显示“分类汇总”对话框,1、指定分类字段,2、指定汇总方式,3、指定汇总字段,4、指定汇总结果的保存方式,7.3 数据的分类与汇总,5、汇总结果的保存方式 替换当前分类汇总。选择这种方式时，最后一次的汇总会取代以前的分类汇总。 每组数据分页。选择这种方式时，各种不同的分类数据将被分页保存。 汇总结果显示在数据下方。选择这种方式时，原数据的下方会显示汇总计算

12、的结果。 上述3种方式可同时选中。Excel的默认选择是第1和第3项,7.3 数据的分类与汇总,6、分类汇总结果示例,7.3.3 高级分类与汇总,7、多次分类汇总 Excel可以对同一分类进行多重汇总，若要在同一汇总表中显示两个以上的汇总数据，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行。 在前面的汇总结果基础上，统计每部门的人数,7.3.4 嵌套分类汇总,1、嵌套分类汇总 在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总，两次的分类汇总关键字不同，这种分类汇总方式称为嵌套分类汇总。 2、嵌套分类汇总的排序方式 建立嵌套分类汇总的前提仍然是要对每个分类

13、汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余以此类推。 有几层嵌套汇总就需要进行几次分类汇总操作，第二次汇总在第一次的结果集上操作，第三次在第二次的结果是操作，其余以此类推。在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总，两次的分类汇总关键字不同，这种分类汇总方式称为嵌套分类汇总。 建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余以此类推,7.3.4 嵌套分类汇总,以部门为第一汇总关键字、婚否为第二汇总关键字进行的嵌套分类汇总,7.3.5 删除

14、分类汇总,8、删除分类汇总 对于刚建立的分类汇总，如果要将其恢复为原始数据表，可以选择“编辑”中的“撤消”菜单项取消分类汇总，或单击撤消按钮。 但若在建立分类汇总之后，又进行了其他操作，上述方法就无效了，可按如下方法删除分类汇总。 （1）单击分类汇总表中的任一单元格。 （2）选择“数据”选项卡中的“分类汇总”命令 （3）单击“分类汇总”对话框中的“全部删除”按钮,7.4 数据透视表,数据透视表的功能 对数值数据进行分类汇总和聚合；展开或折叠要关注结果的数据级别，查看感兴趣区域摘要数据的明细；将行移动到列或将列移动到行（或“透视”），以查看源数据的不同汇总；对最有用和最关注的数据子集进行筛选、排

15、序、分组和有条件地设置格式，突出显示重要信息；提供简明、带有批注的联机报表或打印报表,7.4 数据透视表,2、源数据表与数据透视表的对应关系,7.4.2 建立数据透视表,3、案例（注：数据透视表的全部案例都在Ch7-15.Xlsx工作薄的各个工作表中） 【例7.15】假设某电视销售商场有6位职工，分别是劳得诺、令狐冲、陆大安、任我行、韦小宝、向问天，销售的电视产品有TCL、长虹、康佳、创维、熊猫几种品牌。每天的销售数据都记录在Excel的一个工作表中，如图所示，该数据表有300多行数据,7.4.2 数据透视表,完成以下统计分析工作 统计各种品牌的电视的总销售额； 统计每位职工的总销售额； 统计

16、每位职工销售的各种类型的电视机的销售额； 统计各种运输方式的总数。 数据透视表能够很方便地完成这些工作,7.4.2 数据透视表,4、建立数据透视表的过程,1、建立源数据表后，单击“插入”选项卡中的“数据透视表”弹出“创建数据透视表”对话框,1、 “创建数据透视表”对话框,1、 指定数据透视表的数据区域后，单击“确定”按钮，将弹出数据透视表的布局设置对话框,2、根据第1步设置创建的数据透视表,1、用鼠标将数据字段拖放到行、列标签和数值汇总对应的文本框中,7.4.2 数据透视表,其中数据透视表布局中的内容 行标签 拖放到行中的数据字段，该字段中的第一个数据项将占据透视表的一行。 列标签 与行相对应

17、，放置在列中的字段，该字段中的每个项将占一列。 报表筛选 行和列相当于X轴和Y轴，由它们确定一个二维表格，页则相当于Z轴。拖放在页中的字段，Excel将按该字段的数据项对透视表进行分页。 数值 进行计数或汇总的字段名称,7.4.2 数据透视表,统计各销售员销售数据的透视表布局,7.4.3 查看透视表中数据的具体来源,统计各销售员销售数据的透视表,双击任一单元格，会得到这些数据的来源明细数据,7.4.3 查看透视表中数据的具体来源,双击上表E5单元格得到的明细数据表,7.4.4 利用报表筛选创建分页显示,利用报表筛选（即页字段）创建分页显示,页字段,用鼠标单击它，从下拉列表中选择某个日期，可以查

18、看此日期的销售数据,5.4 数据透视表,5 修改数据透视表 增加或减少行、列中的数据项 重新组织数据透视表 行、列置换 在字段内移动个别项,7.4.5 建立多字段的数据透视表,6、使用多重数据字段建立透视表 【例7.16】在前面所讨论的电视销售数据表中，要查看每位职工销售了多少种产品，每种产品的数量各是多少，而且还要查看其销售的产品中，每种运输方式各运输了多少数量,在行标签中放置了”销售员姓名“和”运输方式“两字段,7.4.6 修改数据透视表的汇总函数,7、修改数据透视表的汇总函数 数据透视表中使用的透视函数称为汇总函数，在默认情况下，数据透视表采用SUM函数对其中的数值项进行汇总，用COUN

19、T函数对表中的文本类型字段项进行计数。 Excel提供了许多汇总函数，如下图所示,单击”求和项“的下拉箭头，从弹出的菜单中选择”值字段设置“，弹出值字段设置对话框,在此选择需要的”汇总函数,7.4.7 修改透视表数据的显示方式,8、修改透视表数据的显示方式,以百分比显示数据,单击”求和项“的下拉箭头，从弹出的菜单中选择”值字段设置“，弹出值字段设置对话框,单击”此“下拉箭头，从弹出的菜单中选择”占总和的百分比,7.4.8 显示数据项的明细数据,例7.19】 查看图7.32（a）中创为电视各种运输方式明细数据的百分比，如图7.33(a)所示,用鼠标右击某一产品名称，从弹出的菜单中选择”展开/折叠

20、“，显示出”显示明细数据“对话框,从中选择要显示明细数据的数据项名称,7.4.9 对日期进行分组透视,在日常工作中，常常需要制作月报表、季度报表或年度报表之类与日期密切相关的报表，这类报表的制作往往需要对源数据表中的日期和时间进行分组计算，才能制作出来。 例如，对于前面介绍的电视销售记录，要制作出每种产品在每个月的员工销报表，其制作方法是把源数据中的产品名称字段放在数据透视表的“报表筛选”中，把销售员姓名字段放在“行标签”中，把日期字段放在“列标签”中，得到的数据透视表如图7.34（a）所示。很显然，图7.34(a)不是我们需要的数据透视表，需要的是图7.34(b)所示的透视报表,1、以”销售

21、员姓名“为行标签，“日期”为列标签制作数据透视表,2、单击列标签下面的任何一个日期，如“1997年2月27日”，然后单击“数据透视表工具”“选项”“将所选内容分组”，Excel会弹出“分组”设置对话框,3、选择一种分组的时间方式，如月,对日期进行分组透视的过程,7.4.10 制作数据透视图表,9、制作数据透视图表,7.5 数据链接、嵌入,1、链接概述 链接就是指两个不同工作表或不同文档（可以是不同应用程序建立的文档，如WORD和EXCEL之间）存在的数据连接。 链接让一个工作簿可以共享另一个工作薄中的数据，可以链接单元格、单元格区域、命名公式、常量或工作表 。 总的说来，链接具有以下优点： 在

22、不同的工作簿和工作表之间，可以进行数据共享。 小工作簿比大工作簿的运行效率更高。 分布在不同地域中的数据管理可以在不同的工作簿中完成，通过超级链接可以进行远程数据采集、更新和汇总。 可以在不同的工作薄中修改、更新数据，大家可以同时工作。如果所有的数据都存于一个工作表中，就增加了多人合作办公的难度,7.5.1 建立链接,2、建立EXCEL工作薄之间的链接 【例7.20】 某茶厂生产的茶叶销售于西南地区，每个季度的销售统计分别在不同的工作簿中，下图就是第一、二季度的茶叶销售统计工作薄示例,7.5.1 建立链接,建立一新工作薄，将这前面两不同工作薄中的工作表连接在该工作薄,本工作薄中的两个工作表是从其它工作薄链接过来的。修改原工作表中的数据