Excel 2016数据处理与分析应用教程 课件 第6章 数据管理

第6章数据管理主要内容6.1数据验证6.2数据排序6.3数据筛选6.4分类汇总6.1数据验证数据验证的目的是限定单元格中输入数据的类型和范围，并且能够在数据录入操作过程中及时地给出提示信息。数据验证的最常见用法之一是创建下拉列表。主要内容：6.1.1数据验证设置6.1.2限制数据录入的下拉列表设置6.1.1数据验证设置主要包括三部分内容：1.验证条件设置

设定单元格内容的数据类型和范围。2.输入信息设置

用户数据录入过程中的信息提示。3.出错警告设置

用户录入错误数据时，向用户显示的提示值息。6.1.1数据验证设置【例6-1】对工作表中的“成绩”所在列设置数据验证，限定只能输入范围在0~100之间的整数；在数据录入过程中，系统提示标题为“成绩录入”内容为“有效范围：0~100”的输入信息；若用户录入错误，弹出标题为“成绩录入错误”内容为“成绩范围：0~100”的出错警告信息。1.验证条件设置：0~100之间的整数2.输入信息设置：标题为“成绩录入”内容为“有效范围：0~100”3.出错警告设置：标题为“成绩录入错误”内容为“成绩范围：0~100”1.验证条件设置：0~100之间的整数2.输入信息设置：标题为“成绩录入”内容为“有效范围：0~100”效果展示3.出错警告设置：标题为“成绩录入错误”内容为“成绩范围：0~100”效果展示6.1.2限制数据录入的下拉列表设置通过下拉列表实现单元格内容的选择录入，以限定录入内容的范围。1.固定内容的下拉列表

例如性别，内容固定只有“男”和“女”两个选择。2.可变内容的下拉列表

例如学院，根据不同学校的具体情况，录入内容会有所变化。1.固定内容的下拉列表性别的下拉列表设置：

将“允许”下拉框中的数据类型

指定为“序列”；在“来源”文本框中输入：

男,女

（注意必须是英文逗号）1.固定内容的下拉列表性别的下拉列表设置：

将“允许”下拉框中的数据类型

指定为“序列”；在“来源”文本框中输入：

男,女

（注意必须是英文逗号）效果展示2.可变内容的下拉列表学院的下拉列表录入效果：首先需要建立下拉列表中的数据（F1:F3区域）然后再进行下拉列表设置2.可变内容的下拉列表学院的下拉列表设置

将“允许”下拉框中的数据类型

指定为“序列”；在“来源”文本框中设置下拉列表

数据区域（F1:F3）6.2数据排序主要内容：6.2.1排序规则6.2.2单字段排序6.2.3多字段排序6.2.4自定义序列排序6.2.1排序规则①数值按照值大小排序，升序按从小到大排序，降序按从大到小排序。②英文按照字母顺序排序，升序按A～Z排序，降序按Z～A排序。③汉字可以按照拼音字母的顺序排序，升序按A～Z排序，降序按Z～A排序；

或者按照笔画的顺序排序。④文本中的数字<英文字母<汉字。⑤日期和时间按日期、时间的先后顺序排序，升序按从前到后的顺序排序，降序按从后向前的顺序排序。⑥自定义序列在定义时所指定的顺序是从小到大。⑦必须设置了单元格颜色或字体颜色后，才能按颜色进行排序。同样，只有使用条件格式创建了图标集，才能按图标进行排序。6.2.2单字段排序操作步骤如下。①

选中要排序的字段，或者选中该字段的任意一个单元格。②通过执行下列操作之一来完成排序：在“数据”选项卡的“排序和筛选”选项组中，若要按照升序排序，单击“升序”按钮；若要按照降序排序，单击“降序”按钮。在“开始”选项卡的“编辑”选项组中，单击“排序和筛选”按钮，在出现的选项中进行“升序”或“降序”的选择。6.2.2单字段排序“班级”升序排序6.2.2单字段排序“总成绩”降序排序6.2.3多字段排序操作步骤如下。①

选择数据区域中任意一个单元格。②

在“数据”选项卡“排序和筛选”选项组中，单击“排序”按钮，打开“排序”对话框。③

按顺序指定主要关键字、次要关键字。6.2.3多字段排序【例6-2】对学生成绩数据按照班级升序，总成绩降序排序。6.2.4自定义序列排序不按系统默认的排序规则，自己指定排列顺序。例如：按照教师职称级别（助教，讲师，副教授，教授）排序按照成绩等级（优秀、良好、中等、及格、不及格）排序6.2.4自定义序列排序如何自己指定排列顺序？——自定义序列6.2.4自定义序列排序按照自定义序列（助教，讲师，副教授，教授）升序排序①

在工作表中选择数据区域中的任意一个单元格。②

在“数据”选项卡“排序和筛选”选项组中，单击“排序”按钮，打开“排序”对话框。

③

指定“职称”作为主要关键字，次序为“自定义序列”，并添加自定义序列：助教，讲师，副教授，教授。6.2.4自定义序列排序按照自定义序列（助教，讲师，副教授，教授）升序排序6.3数据筛选数据筛选就是在数据表中仅仅显示满足筛选条件的数据记录，把不满足筛选条件的数据记录暂时隐藏起来。主要内容：6.3.1自动筛选6.3.2高级筛选6.3.3删除重复记录6.3.1自动筛选创建自动筛选的步骤：①

选择数据区域中的任意一个单元格。②

在“数据”选项卡“排序和筛选”选项组中，单击“筛选”按钮，

切换到自动筛选状态。③

此时工作表第一行中的每个列标题旁都会出现三角下拉箭头按钮，通过单击相应列的三角下拉箭头按钮，完成筛选条件的设置即可。6.3.1自动筛选【例6-3】利用自动筛选筛选出“财务01”班学生。6.3.1自动筛选【例6-3】在筛选出“财务01”班学生基础上，继续筛选出“高等数学”成绩高于平均值的学生。6.3.1自动筛选【例6-3】在筛选出“财务01”班学生基础上，继续筛选出“高等数学”成绩高于平均值的学生。6.3.2高级筛选1.创建高级筛选条件区域，然后才能实施高级筛选。①

在工作表的空白位置输入要指定筛选条件的字段名称。②

在字段名称下方相应行中输入该字段的筛选条件表达式。

不同行之间的筛选条件是“或”的关系；

同一行中不同列之间的筛选条件是“与”的关系。6.3.2高级筛选【例6-4】利用高级筛选功能，筛选出“高等数学”“英语”和“物理”三门课程的成绩均在90分（含90）以上的学生。6.3.2高级筛选2.创建高级筛选。①选择数据区域中的任意一个单元格。②在“数据”选项卡的“排序和筛选”选项组中，单击“高级”筛选按钮，打开“高级筛选”对话框。③“高级筛选”对话框的“列表区域”自动选择了需要进行筛选的整个数据区域；在“条件区域”选择已经创建好的筛选条件区域。④默认情况下“在原有区域显示筛选结果”，只隐藏不符合条件的记录。6.3.2高级筛选【例6-4】利用高级筛选功能，筛选出“高等数学”“英语”和“物理”三门课程的成绩均在90分（含90）以上的学生。筛选结果6.3.2高级筛选【例6-5】利用高级筛选功能，筛选出“高等数学”、“英语”和“物理”三门课程中有一门成绩在90分（含90）以上的学生。6.3.2高级筛选【例6-5】利用高级筛选功能，筛选出“高等数学”、“英语”和“物理”三门课程中有一门成绩在90分（含90）以上的学生。筛选结果6.3.3删除重复记录重复记录是指所有字段值均相同，或者某几个字段值相同的记录。6.3.3删除重复记录1.利用“删除重复项”删除重复记录①

选择数据区域中的任意一个单元格，在“数据”选项卡的“数据工具”选项组中，单击“删除重复项”按钮，打开“删除重复项”对话框。②

在对话框中选择重复数据所在的列。如果要求删除所有字段内容都完全相同的记录，就要把所有列都勾选上。6.3.3删除重复记录1.利用“删除重复项”删除重复记录6.3.3删除重复记录2、利用“高级筛选”删除重复记录①

选择数据区域中的任意一个单元格。②

在“数据”选项卡的“排序和筛选”选项组中，单击“高级”筛选按钮，打开“高级筛选”对话框。③

筛选方式一般选择“将筛选结果复制到其他位置”，以方便删除重复记录以后的处理操作；注意必须勾选“选择不重复的记录”。6.3.3删除重复记录2、利用“高级筛选”删除重复记录6.4分类汇总主要内容：6.4.1创建分类汇总6.4.2分级显示分类汇总6.4.3复制分类汇总结果6.4.4删除分类汇总6.4.1创建分类汇总操作步骤如下。①

按分类字段列进行排序，使相同的记录集中在一起。②

在“数据”选项卡的“分级显示”选项组中，单击“分类汇总”按钮，

打开“分类汇总”对话框。③

在“分类汇总”对话框中，完成相应设置。④

若要进行多级分类汇总，重复①~③操作，但一定注意不要选中“替换当前分类汇总”复选框。6.4.1创建分类汇总【例6-6】对学生成绩数据，要求按班级和性别分别汇总高等数学、英语和物理成绩的平均值。分析：①

按照班级和性别排序②

按班级分类汇总③

按性别分类汇总，不勾选“替换当前分类汇总”6.4.1创建分类汇总【例6-6】对学生成绩数据，要求按班级和性别分别汇总高等数学、英语和物理成绩的平均值。分析：①

按照班级和性别排序6.4.1创建分类汇总【例6-6】对学生成绩数据，要求按班级和性别分别汇总高等数学、英语和物理成绩的平均值。分析：①

按照班级和性别排序②

按班级分类汇总6.4.1创建分类汇总【例6-6】对学生成绩数据，要求按班级和性别分别汇总高等数学、英语和物理成绩的平均值。分析：①

按照班级和性别排序②

按班级分类汇总③

按性别分类汇总，不勾选“替换当前分类汇总”6.4.1创建分类汇总【例6-6】分类汇总结果6.4.2分级显示分类汇总1级显示效果6.4.2分级显示分类汇总2级显示效果6.4.2分级显示分类汇总3级显示效果6.4.2分级显示分类汇总4级显示效果6.4.3复制分类汇总结果操作步骤如下①

显示出要复制的汇总数据，掩藏其他数据，然后选取要复制的数据区域。②

在“开始”选项卡的“编辑”选项组中，单击“查找和选择”按钮，选择“定位条件”，打开“定位条件”对话框。③