(3.6)-7（1）数据管理Excel数据处理与分析

7(1)数据管理-排序、筛选、分类汇总本节学习目标1、掌握EXCEL排序的方法2、理解条件区域3、掌握数据筛选的方法4、掌握工作表数据的分类汇总方法7.1数据排序

没有经过排序的数据是杂乱无章的，很难快速地查找或理解数据，排序有助于快速直观地显示、组织和查找所需数据，有助于决策报表的快速制作和更好地理解数据，是数据分析中不可缺少的常用操作。7.1数据排序1、排序规则数字排序规则：数值大小字母排序规则：字母表顺序逻辑值排序规则：FALSE(0)，TRUE(1)汉字排序规则汉语拼音序按笔画排序2、排序方式升序降序7.1.2数值排序3、排序案例【例7.1】某班某次期末考试成绩如图（a）所示，已经计算出了每位同学的平均分，现需得出一个名次表，即从高分到低分的成绩表，以便为前3名同学发奖学金。7.1.2数值排序按日期排序7.1.3汉字与字符排序按汉字笔画排序1、选中要排序的数据区域后,单击”排序和筛选”按钮2、单击”自定义排序”按钮，弹出“排序”对话框3、单击”选项”按钮，弹出“选项”对话框3、指定”笔画序”7.1.4自定义排序次序自定义排序有时需要按人为指定的次序对某此资料排序。【例7.3】某大学有7个学院，每个学院的基本情况如图所示。要求按以下次序排序此表：计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院。7.1.4自定义排序次序方法单击Office按钮→“Excel选项”→“常用”→“使用Excel时采用的首选项”→“编辑自定义列表”，Excel会弹出“选项”对话框按前面的方式显示出“排序”对话框中，通过“序列”下面的“自定义序列”指定建立的序列为排序依据。在此输入自定义的排序序列7.1.5账号、零件号等排序账号、零件号排序的困难由于账号、零件号常采用不同的区段进行编码，为排序带来了困难。账号、零件号排序的解决方法一种方法是确保同类编号中的任何部分都有相同的长度。例如在上述例子中，将“ZK-999-10”输入为“ZK-0999-10”，这样在比较排序时就不会出错了。另一种方法是把编号的不同部分输入在不同列的单元格中，如上面两个编号用图7.6所示的方式输入。在排序时，对编号所涉及到的三列同时进行排序，排序结果就不会出错，最后再用“&”运算符把排序后的各部分组合起来。7.1.6多关键字排序多关键字排序就是对数据表中的数据按两个或两个以上的关键字进行排序。多关键字排序可使数据在第一关键字相同的情况下，按第二关键字排序，在第一、第二关键字都相同的情况下，数据按第三关键字有序，其余的以此类推。在Excel2007中，最多允许有64个排序关键字，但不管有多少关键字，排序之后的数据总是按第一关键字有序的。7.1.6多关键字排序【例7.4】2004年某杂货店各雇员的销售数据如图7.7（a）的A1:F7所示，以第一季度为第一关键字、第二季度为第二关键字、第三季度为第三关键字的排序结果如图7.7（a）的A9:F15区域所示，排序的方式是递增。1、源数据表2、单击“开始”→“排序和筛选”→“自定义排序”命令，弹出“排序”对话框3、依次指定排序关键字3、排序结果7.2

数据筛选1、概念数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行。2、Excel的数据筛选方式1、自动筛选自动筛选提供了快速查找工作表数据的功能，只需通过简单的操作，就能够筛选出需要的数据。2、高级筛选高级筛选能够完成自动筛选所不能实现的复杂筛选。7.2.1

数据筛选3、自动筛选案例【例7.5】某公司的人员档案是按招聘报到的先后次序建立的，如图7.8（a）所示。可以看出，图7.8的档案比较混乱，公司的管理人员可能需要这样的信息：已婚人员的名单，各部门的人员档案信息，工资高于1200的人员有哪些，奖金高于1000的人员有哪些，各种学历的人员档案……（1）已婚人员的名单。（2）各部门的人员档案信息。（3）工资高于1200的人员有哪些。（4）奖金高于1000的人员有哪些。（5）各种学历的人员档案……如此等等7.2.1

数据筛选原数据表具有自动筛标记的数据表单击“数据”|“筛选”，Excel会在表格的标题边显示出自动筛选标志。可借此完成各种自动筛选！7.2.1

数据筛选自动筛选的结果示例——筛选出运维部的工作人员。7.2.1

数据筛选如果筛选关键字对应的列是数值型数据，可通过“数字筛选”命令指定自定义筛选条件，实现灵活的数据查询。【例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）“或”条件（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】对于前面讨论的职工档案数据表，现要查找运维部和信息资源部中工资高于1000且奖金高于500的人员档案7.2.3

高级筛选4、使用计算条件的高级筛选什么是计算条件不能直接用一个简单的字段值与常量比较构造出来的条件。计算条件的构造方法计算条件中的标题可以是任何文本或都是空白，但不能与数据清单中的任一列标相同，这一点正好与前面讨论的条件区域相反。必须以绝对引用的方式引用数据清单或数据库中之外的单元格。必须以相对引用的方式引用数据清单或数据库中之内的单元格。7.2.3

高级筛选基于计算条件的高级筛选案例【例7.11】某商场2003年2月1号到8号的销售记录如图7.18的区域A2:F10所示，找出其中销售额高于中间值的销售记录。7.3数据的分类与汇总1、概述分类汇总就是对工作表中指定的行或列中的数据进行汇总统计，它通过折叠或展开原工作表中的行、列数据及汇总结果，从汇总和明细两种角度显示数据，可以快捷地创建各种汇总报告。分类汇总的数据折叠层次可达8层。2、分类汇总能够完成以下任务：在数据表中显示一组或多组数据的分类汇总及总和。在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）等。7.3数据的分类与汇总案例右图是某公司的职工工资表。完成以下事情：每部门有多少人；每部门平均工资；哪个部门的总评奖金最高，应分发给各部门的总金额各是多少

通过函数能够完成这些事件，但分类汇总简单多了。7.3数据的分类与汇总3、分类汇总的准备工作①分类汇总表的每个数据列都有列标题；②必须对要进行分类汇总的列排序。排序的列标题称为分类汇总关键字，在进行分类汇总时，只能指定排序后的列标题为汇总关键字。按部门排序后的工作表7.3数据的分类与汇总4、建立分类汇总单击“数据”选项卡中的“分类汇总”命令,显示“分类汇总”对话框。

1、指定分类字段2、指定汇总方式3、指定汇总字段4、指定汇总结果的保存方式7.3数据的分类与汇总5、汇总结果的保存方式替换当前分类汇总。选择这种方式时，最后一次的汇总会取代以前的分类汇总。每组数据分页。选择这种方式时，各种不同的分类数据将被分页保存。汇总结果显示在数据下方。选择这种方式时，原数据的下方会显示汇总计算的结果。上述3种方式可同时选中。Excel的默认选择是第1和第3项。7.3数据的分类与汇总6、分类汇总结果示例7.3.3高级分类与汇总7、多次分类汇总Excel可以对同一分类进行多重汇总，若要在同一汇总表中显示两个以上的汇总数据，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行。在前面的汇总结果基础上，统计每部门的人数。7.3.4嵌套分类汇总1、嵌套分类汇总在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总，两次的分类汇总关键字不同，这种分类汇总方式称为嵌套分类汇总。2、嵌套分类汇总的排序方式建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余以此类推。有几层嵌套汇总就需要进行几次分类汇总操作，第二次汇总在第一次的结果集上操作，第三次在第二次的结果是操作，其余以此类推。在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总，两次的分类汇总关键字不同，这种分类汇总方式称为嵌套分类汇总。建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余以此类推。7.3.4嵌套分类汇总以部门为第一汇总关键字、婚否为第二汇总关键字