Excel 2016高级应用 　　Excel 2016不仅具有丰富的电子表格功能，而且具有强大的数据分析与数据处理能力。本章将进一步学习Excel 2016的相关应用，包括数据管理分析、图表及常见函数的应用等内容。 5.1 Excel数据管理与分析 　　在Excel 2016中，具有二维表特性的电子表格被称为数据列表或数据清单，如图5.1所示，A2:F14单元格区域就是数据列表。数据列表与数据库表类似，其中行表示记录，列表示字段。通常数据表的第1行是文本类型，即相应列（字段）的名称。下方是连续的数据区域，每1列均包含相同类型的数据。针对数据列表，Excel 2016提供了一套功能强大的命令，利用这些命令可以完成对数据的排序、筛选、分类汇总、合并计算等操作。 　　 图5.1 数据列表实例 5.1.1 数据排序 1．简单排序 　　如果对图5.1中的入学成绩按数值排序，可首先单击排序列中的任意单元格，然后单击“数据”→“排序和筛选”→“升序”命令按钮和“降序”命令按钮。之后，数值按照由小到大的升序或由大到小的降序排序，文字默认按照待排数据首字的字母顺序排序。 2．高级排序 　　在本例中，对入学成绩进行排序后，结果显示有几组相同的成绩。如果还需要在数据列表中选择其他字段作为排序的依据，如成绩相同按籍贯排序，则可采用高级排序的方法。首先选中数据列表中的任意单元格，然后单击“数据”→“排序和筛选”→“排序”命令按钮，打开图5.2所示的“排序”对话框。 　　 图5.2 “排序”对话框 　　其中，主要关键字为排序的基础字段，可以根据要求选择主关键字数值、单元格颜色、字体颜色或单元格图标作为排序依据，然后对所选项进行“升序”或“降序”的排列。如果指定的主要关键字中出现相同值，还可以根据需要单击“添加条件”按钮添加次要关键字。在Excel 2016中，最多可以指定63个次要关键字。同时可以通过单击“删除条件”按钮对多余的次要关键字进行删除。 　　在数据列表排序的过程中，默认情况下，数据列表中的各字段名为数据列表的标题行。若选中“数据包含标题”复选框，则排序时标题行不参与排序。 　　本例将入学成绩作为主要关键字，入学成绩相同的记录按照籍贯的升序原则进一步排序，排序条件的设置如图5.3所示。 　　 图5.3 排序条件的设置 5.1.2 数据筛选 　　数据筛选就是从数据列表中选取满足条件的数据并显示，而将不满足条件的数据暂时隐藏起来。数据筛选分为自动筛选和高级筛选，自动筛选可以实现单个字段简单条件的筛选和多个字段简单条件的筛选；高级筛选可以实现多个字段复杂条件的筛选。 1．自动筛选 　　以图5.1中的数据列表为例，若要筛选出表中所有的男同学，具体操作步骤如下。 　　（1）单击数据列表区域的任一单元格。 　　（2）单击“数据”→“排序和筛选”→“筛选”命令按钮。 　　（3）单击性别字段的下拉按钮，在弹出的列表仅选择“男”复选框，如图5.4所示。 　　（4）数据列表仅显示男同学的记录，不符合条件的记录自动隐藏，如图5.5所示。 　　 图5.4 自动筛选条件项 图5.5 自动筛选出“男”同学的记录 　　（5）单击“数据”→“排序和筛选”→“清除”命令按钮，可以重新显示列表中的所有记录。 　　（6）单击“数据”→“排序和筛选”→“筛选”命令按钮，可以取消筛选。 　　如果要筛选性别为男并且入学成绩大于500的学生，可以单击性别字段的下拉按钮，在弹出的列表中选中“男”复选框，取消“女”复选框的选择；再单击入学成绩字段的下拉按钮，在弹出的列表中单击“数字筛选”→“大于或等于”命令按钮，打开“自定义自动筛选方式”对话框，按图5.6所示进行设置即可。 2．高级筛选 　　高级筛选可以一次性筛选满足多个条件的数据，筛选条件涉及多个字段。若要使用高级筛选，需要建立条件区域。 　　使用高级筛选时，常见的条件区域书写有两种形式，如图5.7所示。 A B A B A1 B1 A1 B1 （a）筛选出字段A中符合条件A1并且字段B中符合条件B1的所有记录 （b）筛选出字段A中符合条件A1或字段B中符合条件B1的所有记录 图5.7 常用的条件区域书写形式 　　假设要在图5.1中的数据列表中一次性筛选出性别为男且入学成绩大于等于500的学生记录。可考虑采用高级筛选。首先在工作表中建立条件区域。本例的筛选条件是性别为男，并且入学成绩要大于等于500，在工作表的空白区域采用条件区域的书写形式，如图5.8所示。 　　高级筛选的具体操作步骤如下。 　　（1）单击数据列表中的任意单元格，单击“数据”→“排序和筛选”→“高级筛选”命令按钮，打开“高级筛选”对话框。 　　（2）选择筛选结果的显示位置。若单击选择“在原有区域显示筛选结果”单选按钮会覆盖原数据区域的内容；若单击选择“将筛选结果复制到其他位置”单选按钮，则需要将筛选结果显示的单元格地址或区域填写到指定文本框中。 　　（3）设置数据清单的列表区域、筛选条件区域和定义筛选结果存放位置。 　　在本例中，“高级筛选”对话框的设置如图5.9所示。为了保证原数据列表的完整性，应在“高级筛选”对话框中单击选中“将筛选结果复制到其他位置”单选按钮，设置条件区域为$B$16:$C$17，筛选结果显示在以A19单元格为左上角的起始区域，如图5.10所示。 　　 图5.9 “高级筛选”对话框的设置 图5.10 “高级筛选”的结果 　　使用高级筛选时需要注意以下几个问题。 　　（1）高级筛选必须建立一个条件区域，它可以与数据列表在一张工作表上（必须与数据之间有空白行隔开），也可以与数据列表不在一张工作表上。 　　（2）条件区域中的字段名必须与数据列表中的字段名完全一致。 　　（3）条件区域可以定义多个条件，以便筛选符合多个条件的记录。需要注意根据条件选择正确的书写形式。 5.1.3 分类汇总及分级显示 1．分类汇总 　　分类汇总是在已排序的基础上，将相同类别的数据进行统计汇总。没有排序的分类汇总没有意义。Excel可以对工作表中选定的列进行分类汇总，并将分类汇总结果显示在数据下方。 　　分类汇总的汇总方式包括求和、计数、求平均值、求最大值等运算。 　　假设要在图5.1所示的数据列表中，分别计算男女生入学成绩的平均值。这里需要使用分类汇总，先按性别进行排序，然后对入学成绩进行分类汇总，汇总的方式为平均值。具体步骤如下。 　　（1）在数据列表中，先按字段“性别”排序。 　　（2）单击数据列表中的任意单元格，单击“数据”→“分级显示”→“分类汇总”命令按钮，打开“分类汇总”对话框，如图5.11所示。 　　（3）在“分类字段”下拉列表框中选择“性别”字段。 　　注意：这里选择的字段即是数据列表中的排序字段。 　　（4）在“汇总方式”下拉列表框中选择“平均值” 方式。 　　（5）在“选定汇总项”列表框中选中“入学成绩”复选框。 　　（6）如果要求将汇总结果分页显示，则选中“每组数据分页”复选框。 　　（7）单击“确定”按钮。 　　分类汇总结果如图5.12所示。 　　如果在分类汇总前未排序或操作过程有误，需要撤销该分类汇总，则可以选择数据表中的数据再次进入分类汇总页面，重新修改字段的设置或单击“全部删除”按钮，即可恢复原来的数据表状态。 2．分级显示 　　从分类汇总结果可以看出，数据采用分级显示，工作表的左边为分级显示区，列出各级分级符和分级按钮。 　　（1）默认情况下，分级显示区分为三级，从左到右分别表示最高级、次高级和第三级，如图5.13所示。 　　 图5.12 分类汇总结果 图5.13 分级显示 　　（2）在这个分级显示图中，级别按钮代表单击该按钮时，只显示总的汇总结果，在本例中为入学成绩的总计结果。级别按钮代表单击该按钮时，只显示部分数据及其汇总结果，在本例中为男同学入学成绩的总计结果和女同学入学成绩的总计结果。级别按钮代表单击该按钮时显示全部数据及其汇总结果。隐藏细节按钮可以隐藏分级显示信息，显示细节按钮可以显示分级显示信息。 5.1.4 数据透视表和数据透视图 　　数据透视表是对大量数据快速汇总的交互式表格，可以通过行、列交叉的数据查看汇总后的不同结果，可以设置不同的显示页面来筛选数据，操作简单、易学。只需要对字段进行适当的拖曳操作，即可在数据表中重新组织和统计数据，是集筛选、分类汇总于一体的多元化表格。 1．创建数据透视表 　　假设要在图5.1所示的数据列表中，对同一地区学生的入学成绩进行平均分汇总。具体步骤如下。 　　（1）选定数据列表中的任意单元格。 　　（2）单击“插入”→“表格”→“数据透视表”命令按钮，在“创建数据透视表”对话框中选择数据源内容和要放置数据透视表的位置。本例放置数据透视表的位置为“现有工作表”，单击“位置”文本框右侧的拾取按钮，然后单击表中待放置数据透视表的单元格位置，文本框中即显示该单元格地址，如图5.14所示，单击“确定”按钮。 　　 图5.14 “创建数据透视表”对话框 　　（3）在界面右侧的“数据透视表字段”任务窗格中，将“籍贯”字段拖入“行”列表框中；将“入学成绩”字段拖入“值”列表框中，单击下拉按钮，在弹出的列表中单击“值字段设置”命令，将值汇总方式设置为“平均值”，可在选定位置处产生符合要求的数据透视表，如图5.15所示。 　　 图5.15 数据透视表的设置 2．编辑数据透视表 　　拖入“列”或“行”中的字段名可以在区域间任意调整位置，可以单击字段名右侧的下拉按钮进行切换，也可删除该字段名，重新拖曳其他字段名。“值”列表框中默认以字段求和的方式进行统计，可以单击字段名右侧的下拉按钮，在弹出的列表中选择其他的汇总方式。 3．数据透视图 　　数据透视图是在数据透视表的基础上，以图表的方式更直观地显示数据。在最终图表生成之前，仍需要在字段列表框中拖曳相关的字段名，数据透视图的结果如图5.16所示。 　　 图5.16 数据透视图 5.1.5 合并计算 　　在实际工作中，经常会遇到要将分部门、分月份或者分地区等制作的多张工作表进行汇总的情况。使用Excel 2016的“合并计算”命令就可以轻松完成多张格式相同的数据表格的汇总处理。 　　“合并计算”主要功能是将多个区域的值合并到一个新区域，多个区域可以在一个工作表，也可以在相同或不同工作簿的多个工作表中。 　　例如，对两个部门领取的办公用品总数进行合并计算。给定的工作表如图5.17和图5.18所示。 　　从图5.17和图5.18中不难看出，两个工作表中行标题和列标题大致相同，现在需要把两个工作表中的数据合并到一个表中，需要采用“合并计算”。 　　 图5.17 部门1领取办公用品情况 图5.18 部门2领取办公用品情况 　　具体操作步骤如下。 　　（1）选择合并后放置数据的工作表。本例将新建一个工作表“合并计算”。 　　（2）单击该工作表的A1单元格，然后单击“数据”→“数据工具”→“合并计算”命令按钮，打开“合并计算”对话框，如图5.19所示。 　　 图5.19 “合并计算”对话框 　　（3）“函数”下拉列表框中包含了求和、计数、平均值、最大值、最小值等一系列函 数。本例中，要计算合计的数量，因此选择求和函数。 　　（4）“引用位置”是选择合并计算涉及的数据区域。先选择包含合并数据的工作表，再选择整个数据区域(包括行标题和列标题)。在本例中，分别选择“部门1!$A$1:$E$13”和“部门2!$A$1:$E$12”两个数据区域，单击“添加”按钮，将这两个数据区域加入所有引用位置列表中，如图5.20所示。 　　（5）在操作后的工作表中若要保留原列标题和行标题，则应选中“首行”复选框和“最左列”复选框，单击“确定”按钮。 　　操作完成后，效果如图5.21所示。 　　 图5.20 合并计算中引用位置的添加 图5.21 合并计算完成效果图 5.2 图表的使用 　　图表可以将枯燥的数据更加清晰地表现出来，更加便于数据分析。Excel?2016提供了丰富的图表功能，可以利用这些功能方便地绘制不同的图表。除了常用的图表类型，如柱形图、折线图、饼图外，Excel 2016还引入树状图、旭日图、直方图、箱形图等6种新的图表类型。 5.2.1 创建图表 　　Excel 2016创建图表的方式有两种：一种是嵌入式图表，即在原工作表中创建图表，图表作为原工作表的一部分；另一种是单独式图表，即在空白工作表中创建图表，图表单独占用一个工作表，可以单独打印。两种方式的图表都是依据工作表中的数据创建的，当工作表中的数据改变时，图表也将做相应的变化，以反映出图表数据的变动情况。 　　创建图表，首先必须选择数据的来源，即数据源。这些数据要求以列或行的方式存放在工作表固定的区域中。下面以图5.22所示的数据进行图表的创建。 1．嵌入式图表 　　以图5.22所示的“姓名”列和“总分”列数据作为数据源，建立嵌入式图表，具体操作步骤如下。 　　（1）选定建立图表的数据区域部分。本例中只用到“姓名”列和“总分”列数据，先选中姓名列数据，按住Ctrl键的同时选中总分列数据，然后单击“插入”→“图表”组右下角的对话框启动器，打开“插入图表”对话框，如图5.23所示。