《Excel-2016数据处理与分析》教学课件03数据的排序、筛选与分类

模块3数据的排序、筛选与分类汇总通过本模块的学习，熟练掌握Excel的3大数据管理功能：数据排序、数据筛选和数据分类汇总。同时，知道排序、筛选和分类汇总对数据的要求。学习目标本模块共有12个知识点，重点学习的对象有10个（除去3.2.1和3.2.5的两个知识点），因为它们属于Excel数据管理中必备的技能，同时也是办公中最常用到的。学习重点3.1

排序、筛选与分类汇总对数据的要求排序、筛选与分类汇总是数据管理和分析中常用的3种手段。初学者在学习和使用它们之前，很有必要了解它们各自对数据的要求，以免产生错误。3.1.1

排序对数据的要求排序对数据是有一定要求的，确切地说，是对表格结构有要求。我们根据多年Excel的使用经验，总结了如下几种不适合或不需要排序的表格。（1）穿插小计行或合计行的数据表格，导致排序后达不到预期效果，如图3-1所示。3.1.1排序对数据的要求（2）表格中带有不统一的合并单元格，导致数据单元格大小不同，排序无法正常进行，如图3-2所示。3.1.1排序对数据的要求（3）一些特有结构的表格无法排序或不需要排序，如图3-3所示。3.1.1排序对数据的要求（4）一些固有顺序的表格数据不能随意排序，否则会使效果大打折扣，如图3-4所示。3.1.2

筛选对数据的要求筛选对数据的要求主要有两个：单元格大小要统一、非报表值区域。其中，单元格大小要统一，要求表格中不能出现大小不同的合并单元格（偶尔有一个或多个合并单元格，而不是统一合并，造成单元格大小不同），否则，筛选结果不全，如图3-5所示。3.1.2筛选对数据的要求非报表值区域是指数据透视表中不是值区域字段，因为这些字段都不具备直接筛选的功能，而是要通过字段标签筛选，如图3-6所示。3.1.3

分类汇总对数据的要求分类汇总对数据有一个硬性要求：数据要有相同项（同类数据），如多条相同产品数据、多条相同部门、多条相同区域数据等，如图3-7所示。3.2

排序为了使表格数据更加便于查看或更加有条理，通常要对表格数据进行排序。其中，最快捷的方式是单字段排序，其次是多字段排序，最后是自定义排序。3.2.1

单字段排序单字段排序是对单个字段进行排序，表格中其他字段数据对应排列。常见的单字段排序包括快速排序、按颜色排序和按图标排序。1.快速排序快速排序是指对指定字段按拼音首字母或数字大小进行升序、降序排列，而且只需1~2步操作。例如，对订单表格中的订单日期进行升序排列（打开素材文件中的“素材\模块3\3.2\订单.xlsx”文件），可直接在A2单元格上右击，在弹出的快捷菜单中选择“排序”→“升序”命令，如图3-8所示。3.2.1单字段排序3.2.1单字段排序或选中A2单元格，单击“数据”选项卡“排序和筛选”组中的“升序”按钮，如图3-9所示。3.2.1单字段排序技巧：排序后，若标题行的位置发生了变化（如图3-10所示，原因是排序时包含了标题行），只需在排序前打开“排序”对话框，取消选中“数据包含标题”复选框即可，如图3-11所示。3.2.1单字段排序2.按颜色排序在对数据进行分析时，若按底纹颜色或字体颜色排列，可通过以下两种方法轻松实现。一是在颜色底纹单元格上右击（以该颜色作为排序依据），在弹出的快捷菜单中选择“排序”→“将所选单元格颜色放在最前面”命令，如图3-12所示。3.2.1单字段排序3.2.1单字段排序提示：若按字体颜色排序，在弹出的右键快捷菜单中选择“排序”→“将所选字体颜色放在最前面”命令；若底纹颜色是渐变色，不建议对单元格颜色进行排序，因为排序效果不明显，不利于数据分析，如图3-13所示。3.2.1单字段排序二是通过“排序”对话框进行排序，操作方法如下。步骤1打开素材文件中的“素材\模块3\3.2\生产报表.xlsx”文件，选择任意数据单元格，切换到“数据”选项卡，在“排序和筛选”组中单击“排序”按钮，如图3-14所示。步骤2打开“排序”对话框，在“主要关键字”下拉列表框中选择“任务”选项，如图3-15所示。3.2.1单字段排序3.2.1单字段排序步骤3在“排序依据”下拉列表框中选择“单元格颜色”选项，如图3-16所示。步骤4在“次序”下拉列表框中选择绿色色块选项，单击“确定”按钮完成操作，如图3-17所示。实现步骤视频3.2.1单字段排序3.按图标排序在对数据进行分析时，若要让表格数据按照指定的图标排序，可通过两种方法轻松实现。一是在图标所在的单元格上右击（以该图标作为排序依据），在弹出的快捷菜单中选择“排序”→“将选定格式的图标置顶”命令，如图3-18所示。3.2.1单字段排序二是通过“排序”对话框进行排序，操作方法如下。步骤1打开素材文件中的“素材\模块3\3.2\订单1.xlsx”文件，选择任意数据单元格，在“数据”选项卡的“排序和筛选”组中单击“排序”按钮，如图3-19所示。步骤2打开“排序”对话框，在“主要关键字”下拉列表框中选择“金额”选项，如图3-20所示。3.2.1单字段排序3.2.1单字段排序步骤3在“排序依据”下拉列表框中选择“条件格式图标”选项，如图3-21所示。步骤4在“次序”下拉列表框中选择图标选项，单击“确定”按钮完成操作，如图3-22所示。实现步骤视频3.2.2

多字段排序在工作中很多时候需要按两个或两个以上的关键字排序，也就是多字段排序，如部门和工资双字段排序、季度和区域双字段排序等。【实例3-1】对订单数据进行多条件排序在零售订单表格中，店主想让店铺中的销售数据由少到多显示，同时，让日期由远到近显示。店员小林以“下单日期”和“数量”为关键字段进行排序，操作步骤如下。步骤1打开素材文件中的“素材\模块3\3.2\订单2.xlsx”文件，选择任意有数据的单元格，单击“数据”选项卡“排序和筛选”组中的“排序”按钮，如图3-23所示。步骤2打开“排序”对话框，选择“主要关键字”为“下单日期”，单击“添加条件”按钮，如图3-24所示。3.2.2多字段排序3.2.2多字段排序步骤3选择“次要关键字”为“数量”，单击“确定”按钮，如图3-25所示。3.2.2多字段排序步骤4多条件排序效果如图3-26所示。实现步骤视频3.2.3

自定义排序数据表格中的排序方式有多种，若以上两种排序方式不能满足需求，则可以进行自定义排序。【实例3-2】对职务高低进行排序某科技公司的老总觉得开发部是最重要的部门，人事部次之，最后是财务部，所以，他要求制作表格的小林按部门重要性排列部门季度考核数据。小林问清楚部门的重要性排列顺序后，直接使用自定义排序操作。例如，需要对公司职员职位的高低进行排序，这时就需要运用自定义排序来进行，操作步骤如下。3.2.3自定义排序步骤1打开素材文件中的“素材\模块3\3.2\季度考核.xlsx”文件，选择B3单元格，单击“数据”选项卡“排序和筛选”组中的“排序”按钮，如图3-27所示。步骤2在打开的“排序”对话框中选择“主要关键字”为“部门”，在“次序”下拉列表框中选择“自定义序列”选项，如图3-28所示。3.2.3自定义排序3.2.3自定义排序步骤3打开“自定义序列”对话框，在“输入序列”文本框中依次换行输入“开发部人事部销售部行政部企划部财务部”，单击“添加”按钮，再单击“确定”按钮，如图3-29所示。步骤4返回“排序”对话框，单击“确定”按钮，如图3-30所示。3.2.3自定义排序技巧：“自定义序列”对话框左侧的“自定义序列”列表框中有多款定义的排列方式，可以根据需要选择调用。步骤5完成对各部门季度考核数据进行自定义排序，如图3-31所示。实现步骤视频3.2.4

按行排序在Excel数据表格中，除了可以按列排序外，还可以按行进行排序。【实例3-3】对业务销售总计行数据排序在1—8月的业务销售表中，主管要求小张将业务总计行数据从少到多进行排列，以直观地看出销售走势。小张对此很有经验，他没有选择手动比较总计数据，然后移动列的相对位置，而是进行了如下操作。步骤1打开素材文件中的“素材\模块3\3.2\业务销售.xlsx”文件，选择B1：I15单元格区域，在“数据”选项卡的“排序和筛选”组中单击“排序”按钮，如图3-32所示。步骤2打开“排序”对话框，单击“选项”按钮，如图3-33所示。3.2.4按行排序3.2.4按行排序步骤3打开“排序选项”对话框，选中“按行排序”单选按钮，单击“确定”按钮，如图3-34所示。步骤4返回“排序”对话框，在“主要关键字”下拉列表框中选择“行15”选项，单击“确定”按钮，如图3-35所示。3.2.4按行排序3.2.4按行排序提示：设置排序方向或排序方法后，需要将其更改回来，否则后面的排序可能会按新近设置的方式进行。另外，如果要按笔划排序，可直接选中“笔划排序”单选按钮。步骤5返回表格中，在第15行（“合计”行）的业务总计数据按照由小到大的顺序横向排列，如图3-36所示。3.2.4按行排序3.2.4按行排序提示：排序时必须选择指定区域（不能选择任意数据单元格然后进行按行排序），否则A列的业务员数据也会参与到行排序中，导致“业务员”列数据排列到数据区域的最后一列，如图3-37所示。实现步骤视频3.2.5

对指定列排序默认情况下，我们是对整个数据区域的数据按照单个关键字或多个关键字进行排序的，但有时只需要对单个列数据进行排序，这时可以使用Excel提供的对指定列排序功能。【实例3-4】对员工编号重新排序公司人员流动和岗位变化是常有的事情，员工编号也需要随之变动。作为HR的小罗在信息表中调动人员的岗位后，没有手动重新编号，而是对编号列进行了重新排序，瞬间达到了预期效果，他的操作步骤如下。3.2.5对指定列排序步骤1打开素材文件中的“素材\模块3\3.2\人员信息表.xlsx”文件，选择A2：A31单元格区域，单击“数据”选项卡“排序和筛选”组中的“升序”按钮，如图3-38所示。步骤2在打开的“排序提醒”对话框中选中“以当前选定区域排序”单选按钮，单击“排序”按钮，如图3-39所示。3.2.5对指定列排序3.2.5对指定列排序步骤3在“编号”列中可以看到编号重新排序，其他列的数据没有任何变化，如图3-40所示。实现步骤视频3.3

筛选数据筛选是处理数据时一个常见的功能，它可以快速地从数据表中挑选出指定数据，供用户查看和分析。3.3.1

自动筛选自动筛选是比较便捷的一种筛选方式，通常运用于数据较为简单的工作表中。【实例3-5】筛选指定日期的产品生产任务进度情况生产部李主任为了保证生产任务准时完成，需要对产品生产进度进行抽查。他常用的方法是在表格中按阶段日期筛选已记录在案的数据，具体操作方法如下。步骤1打开素材文件中的“素材\模块3\3.3\生产报表2.xlsx”文件，选择“生产记录表”工作表，选择C2单元格，在“数据”选项卡下，单击“排序和筛选”组中的“筛选”按钮，如图3-41所示。步骤2单击“工作日”筛选按钮，在弹出的下拉列表中选择“日期筛选”→“之前”选项，如图3-42所示。3.3.1自动筛选3.3.1自动筛选步骤3打开“自定义自动筛选方式”对话框，单击文本框的下拉按钮，在弹出的下拉列表中选择“2018/1/29”选项，单击“确定”按钮，如图3-43所示。步骤4表格中自动筛选出2018/1/29之前的产品生产任务进度数据，如图3-44所示。3.3.1自动筛选3.3.1自动筛选实现步骤视频3.3.1自动筛选【实例3-6】筛选同一款产品生产部的李主任在查看同一款产品的生产进度时，没有像其他主任一样手动查找同类数据或是排序，而是通过列表框进行快速筛选，他的操作步骤如下。步骤1打开素材文件中的“素材\模块3\3.3\生产报表3.xlsx”文件，选择“生产记录表”工作表，选中B1单元格，在“数据”选项卡下，单击“排序和筛选”组中的“筛选”按钮，如图3-45所示。步骤2单击“产品”筛选按钮，在弹出的下拉列表中取消选中“全选”复选框，选中“螺帽”复选框，单击“确定”按钮，如图3-46所示。3.3.1自动筛选3.3.1自动筛选步骤3在表格中可以查看到筛选出“螺帽”产品的数据，如图3-47所示。3.3.1自动筛选技巧：要在表格中快速筛选出同类型数据（或前部分数据相同），在名称框中筛选是最简便的。打开下拉筛选列表，在搜索框中输入同类型的部分数据，这里输入“特勤”，单击“确定”按钮，如图3-48所示。实现步骤视频3.3.1自动筛选【实例3-7】快速筛选销售前5名的业务数据某销售公司每月都会奖励销售业绩前5名的业务员，作为销售助理的小张，直接使用快速筛选功能，将销售总计排名前5的业务员数据一次性筛选出来，操作方法如下。步骤1打开素材文件中的“素材\模块3\3.3\一季度销售表.xlsx”文件，选择任意数据单元格，按Alt+A+T组合键进入筛选状态，单击“总计”筛选按钮，在弹出的下拉列表中选择“数字筛选”→“前10项”选项，如图3-49所示。步骤2打开“自动筛选前10个”对话框，在最左侧的下拉列表框中选择“最大”选项，在数字框中输入“5”，在最右侧的下拉列表框中选择“项”选项，单击“确定”按钮，如图3-50所示。3.3.1自动筛选3.3.1自动筛选步骤3在表格中可以看到筛选出总计前5项的数据，如图3-51所示。实现步骤视频3.3.2

自定义筛选若是前面已有的快速筛选方法无法满足实际需要，则用户可以自定义筛选，也就是在一定程度上自己设置筛选条件，筛选出想要的数据。例如，在销售报表中筛选出4500~5000之间的数据，操作方法如下。步骤1打开素材文件中的“素材\模块3\3.3\一季度销售报表1.xlsx”文件，选择任意数据单元格，按Alt+A+T组合键进入筛选状态，单击“总计”筛选按钮，在弹出的下拉列表中选择“数字筛选”→“自定义筛选”选项，如图3-52所示。步骤2打开“自定义自动筛选方式”对话框，单击第一个筛选条件下拉按钮，选择“大于或等于”选项，如图3-53所示。3.3.2自定义筛选3.3.2自定义筛选步骤3在右侧文本框中输入“4500”，设置第二个筛选条件为“小于或等于”，在右侧文本框中输入“5000”，单击“确定”按钮，如图3-54所示。步骤4Excel自动筛选出4500~5000的业务总计数据，如图3-55所示。3.3.2自定义筛选3.3.2自定义筛选实现步骤视频3.3.3高级筛选高级筛选可以满足多个条件的筛选，突破筛选条件的个数限制，更加灵活地满足工作需要。【实例3-8】筛选指定产品在特定日期前的销售额数据市场部的老李想要在季度销售明细表中只显示8月15日以前售出的电视机数据，且销售额大于20299。他试了很多遍自定义筛选都无法实现，只有向同事老罗请教。老罗的操作方法如下。步骤1打开素材文件中的“素材\模块3\3.3\销售统计.xlsx”文件，在D21：F22单元格区域中输入筛选条件，选中任意数据单元格，在“数据”选项卡的“排序和筛选”组中单击“高级”按钮，如图3-56所示。步骤2在打开的“高级筛选”对话框中单击“条件区域”文本框右侧的按钮，折叠对话框，如图3-57所示。3.3.3

高级筛选3.3.3高级筛选步骤3在工作表中的D21：F22单元格区域，单击文本框右侧的按钮，打开“高级筛选”对话框，如图3-58所示。步骤4返回“高级筛选”对话框，选中“将筛选结果复制到其他位置”单选按钮，将鼠标指针定位在“复制到”文本框中，在表格中选择A27单元格，单击“确定”按钮，确认设置并返回工作表，如图3-59所示。3.3.3高级筛选3.3.3高级筛选步骤5Excel自动将8月15日前售出、销售额大于20299的电视机销售数据筛选出来，结果如图3-60所示。3.3.3高级筛选技能拓展：高级筛选条件“或”与“与”，条件在同一行表示“与”关系，条件不在同一行表示“或”关系，如图3-61所示。实现步骤视频3.4

分类汇总顾名思义，分类汇总就是将同类数据按照某种指定的方式计算汇总，如区域销售额数据汇总、采购金额季度汇总等。需要说明的是：在分类汇总前必须对分类字段进行排序处理，将同类数据归类在一起，否则，得出的分类汇总统计数据毫无意义。3.4分类汇总【实例3-9】单重分类汇总采购总额数据采购部会在规定时间内对同类产品的采购金额进行汇总统计，长期与表格打交道的小王几分钟就完成了统计工作，他的操作方法如下。步骤1打开素材文件中的“素材\模块3\3.3\采购报表.xlsx”文件，选中A2单元格，单击“数据”选项卡“排序和筛选”组中的“升序”按钮，如图3-62所示。步骤2单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，如图3-63所示。3.4分类汇总3.4分类汇总步骤3在打开的“分类汇总”对话框中选择“分类字段”为“产品名称”，“汇总方式”为“求和”，“选定汇总项”为“采购总额”，单击“确定”按钮，如图3-64所示。步骤4在表格中即可查看到各类产品的分类汇总情况，如图3-65所示。提示：要删除表格中已有的分类汇总，只需在“分类汇总”对话框中单击“全部删除”按钮。若要将分类汇总结果放置在数据的上方，只需取消选中“汇总结果显示在数据下方”复选框，单击“确定”按钮。3.4分类汇总实现步骤视频3.4分类汇总【实例3-10】多重分类汇总部门开支费用数据在采购报表中虽然已有分类汇总，但是主管老罗希望再将每一产品的采购数量进行求和汇总并显示在表格中，他的操作方法如下。步骤1打开素材文件中的“素材\模块3\3.3\采购报表1.xlsx”文件，选择A2单元格，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，如图3-66所示。步骤2打开“分类汇总”对话框，取消选中“采购总额”复选框，选中“采购数量”复选框，取消选中“替换当前分类汇总”复选框，单击“确定”按钮，如图3-67所示。3.4分类汇总3.4分类汇总提示：创建多重分类汇总的操作关键之一，是不让新创建的分类汇总数据覆盖原有的分类汇总数据，否则表格中只会存在一重分类汇总。步骤3在表格中可以看到对产品采购金额求和的分类汇总，如图3-68所示。3.4分类汇总实现步骤视频3.4分类汇总技能拓展：若简单地将表格数据进行分类，可直接使用分组功能，也就是手动分组。方法为：选择需要划分为同一组的行数据，单击“数据”选项卡“分级显示”组中的“组合”按钮，如图3-69所示。3.4分类汇总3.5综合实例采购明细表中的数据项目很多，也很复杂，当只需要查看、管理或分析某一个月的采购情况时，使用排序、筛选和分类汇总功能可以快速实现。3.5综合实例下面以采购明细表为例讲解如何快速获取2018年11月的采购情况。步骤1打开素材文件中的“素材\模块3\综合实例\采购明细.xlsx”文件，选中B4单元格，单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，如图3-70所示。3.5综合实例3.5综合实例步骤2单击“采购日期”筛选按钮，打开筛选器，取消选中“全选”复选框，选中“十一月”复选框，单击“确定”按钮，如图3-71所示。步骤3单击“排序”按钮，打开“排序”对话框，如图3-72所示。3.5综合实例步骤4设置“主要关键字”为“供应商”，“排序依据”为“单元格值”，“次序”为“升序”，如图3-73所示。步骤5单击“添加条件”按钮，添加“次要关键字”，设置“次要关键字”为“产品名称”，“排序依据”为“单元格值”，“次序”为“升序”，单击“确定”按钮，如图3-74所示。3.5综合实例步骤6单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，打开“分类汇总”对话框，如图