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

第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复制分类汇总结果操作步骤如下①

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

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

在“定位条件”对话框中选择“可见单元格”。④

此时执行复制操作，将不会复制那些隐藏的明细数据。然后选定目标位置，执行粘贴操作即可。【例6-7】将按班级汇总的高等数学、英语和物理成绩的平均值复制到另一张工作表中。

复制结果6.4.3复制分类汇总结果6.4.4删除分类汇总删除分类汇总，使工作表还原成原始状态：①

单击包含分类汇总的任意一个单元格。②

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

在“分类汇总”对话框中，单击“全部删除”按钮。第7章数据透视分析主要内容7.1数据透视表7.2数据透视图7.1

数据透视表主要内容：7.1.1创建数据透视表7.1.2编辑数据透视表7.1.3更新数据透视表7.1.4筛选数据透视表7.1.1创建数据透视表①

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

在“插入”选项卡的“表格”选项组中，单击“数据透视表”按钮，打开“创建数据透视表”对话框。③

确定数据区域。一般系统会自动选定整个数据区域作为数据透视表的源数据。④

选择放置数据透视表的位置，可以放置到新工作表或现有工作表中，默认选择新工作表。⑤

单击“确定”按钮，在新工作表中创建数据透视表框架。⑥

在数据透视表框架中完成字段布局即可。7.1.1创建数据透视表关键问题——设计字段布局筛选器：用该区域中的字段来筛选整个报表，

对应分页字段；行：将该区域中的字段显示在左侧的行，

对应行字段；列：将该区域中的字段显示在顶部的列，

对应列字段；值：将该区域中的字段进行汇总分析，

对应数据项。【例7-1】对成绩数据进行数据透视分析，按学院分页查看各个班级男女生成绩的平均值。分析：筛选器：学院行：班级列：性别值：成绩

平均值7.1.1创建数据透视表【例7-1】对成绩数据进行数据透视分析，按学院分页查看各个班级男女生成绩的平均值。分析：筛选器：学院行：班级列：性别值：成绩7.1.1创建数据透视表默认情况下对数值型字段总是进行“求和”运算，而对非数值型字段进行“计数”运算，因此默认生成的数据透视表对成绩进行了求和。7.1.2编辑数据透视表创建完数据透视表后，可以：1.更改数据透视表的汇总方式2.在字段布局中添加或删除字段3.删除行总计或列总计4.改变数值显示方式5.显示明细数据6.数据透视表的清除和删除7.1.2编辑数据透视表1.更改数据透视表的汇总方式①

在“数据透视表字段”窗格中单击“值”区域中的字段，选择“值字段设置”，打开“值字段设置”对话框。②

在值字段设置对话框中，选择值汇总方式。7.1.2编辑数据透视表2.在字段布局中添加或删除字段（1）添加字段右击要添加的字段名称，根据需要选择“添加到报表筛选”“添加到列标签”“添加到行标签”或“添加到值”。或者用鼠标将字段逐个拖拽到相应区域中。（2）删除字段清除勾选字段列表中需要删除字段前的复选框。或者在4个布局区域中，单击字段名称，然后选择“删除字段”命令。7.1.2编辑数据透视表3.删除行总计或列总计在数据透视表中右击要删除的“总计”，然后选择“删除总计”命令。或者在数据透视表中右击任意一个单元格，选择“数据透视表选项”命令，在对话框的“汇总和筛选”选项卡中，去掉勾选“显示行总计”和“显示列总计”。7.1.2编辑数据透视表4.改变数值显示方式默认情况下数据透视表都是按照“无计算”方式显示数值项，为了更清晰地分析数据间的相关性，可以指定数据透视表为特殊的显示方式，例如，以“差异”“百分比”“差异百分比”等方式显示数据。7.1.2编辑数据透视表4.改变数值显示方式以财务01班的平均值为基准，分析其他班级平均分的差异情况。①

在“数据透视表字段”窗格中单击“值”区域中的字段，选择“值字段设置”命令，打开“值字段设置”对话框。②

在“值显示方式”选项卡中将值显示方式设置为“差异”，然后指定差异的基准比较对象为“班级”“财务01”。7.1.2编辑数据透视表4.改变数值显示方式以财务01班的平均值为基准，分析其他班级平均分的差异情况。

差异比较结果7.1.2编辑数据透视表5.显示明细数据在数据透视表中右击要查看明细数据的单元格，在弹出的快捷菜单中选择“显示详细信息”命令。在数据透视表中双击要查看明细数据的单元格，系统会自动创建一个新工作表，显示该汇总数据的明细数据。7.1.2编辑数据透视表6.数据透视表的清除和删除

清除是指删除所有筛选器、行、列、值的设置，但是数据透视表并没有被删除，

只是需要重新设计布局。①

单击数据透视表的任意一个单元格。②

在数据透视表工具“分析”选项卡“操作”选项组中，单击“清除”按钮，然后选择“全部清除”命令。

删除不再使用的数据透视表，操作步骤如下：①

单击数据透视表的任意一个单元格。②

在数据透视表工具“分析”选项卡“操作”选项组中，单击“选择”按钮，然后选择“整个数据透视表”命令。③

按【Delete】键删除。7.1.3更新数据透视表更改数据透视表的源数据后，数据透视表中的汇总数据不会同步更新。所以需要更新数据透视表，使数据透视表重新对源数据进行汇总计算。操作步骤：①

单击数据透视表的任意一个单元格。②

在数据透视表工具“分析”选项卡“数据”选项组中，单击“刷新”按钮，然后选择“刷新”或“全部刷新”命令来重新汇总数据。7.1.4筛选数据透视表通过筛选可以在数据适视表中查看想要的数据，并隐藏不想显示的数据。1．按标签筛选2．按值筛选3．按选定内容筛选4．删除筛选7.1.4筛选数据透视表1．按标签筛选在数据透表中、单击列标签或行标签右侧的下拉箭头，选择“标签筛选”，然后根据实际需要设置筛选条件。7.1.4筛选数据透视表2．按值筛选在数据透表中、单击列标签或行标签右侧的下拉箭头，在筛选下拉列表中选择“值筛选”，然后根据实际需要设置筛选条件。7.1.4筛选数据透视表3．按选定内容筛选在数据视表中，右击相应行或列标题中的内容，在快捷菜单中选择“筛选”，然后选择“仅保留所选项目”或“隐藏所选项目”命令。7.1.4筛选数据透视表4．删除筛选要删除数据透视表中的所有筛选，操作步骤如下：①

单击数据透视表的任意一个单元格。②

在数据透视表工具“分析”选项卡“操作”选项组中，单击“清除”按钮，然后选择“清除筛选”命令。7.2

数据透视图主要内容：7.2.1通过数据透视表创建数据透视图7.2.2通过数据区域创建数据透视图7.2.3删除数据透视图7.2.4设置数据透视图7.2.1通过数据透视表创建数据透视图如果已经创建了数据透视表，可以利用数据透视表直接创建数据透视图。①

单击数据透视表中的任意一个单元格。②

在“插入”选项卡“图表”选项组中，单击“数据透视图”按钮，选择“数据透视图”命令，打开“插入图表”对话框。③

在插入图表对话框中根据实际需要选择一种图表类型，单击“确定”按钮即可得到数据透视图。7.2.1通过数据透视表创建数据透视图【例7-2】基于已有的数据透视表创建的数据透视图。7.2.2通过数据区域创建数据透视图操作步骤①

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

在“插入”选项卡的“图表