第5章〓Excel 2016电子表格Excel是Office办公系列软件中一款功能完整、操作简易的电子表格软件，提供丰富的函数及强大的图表、报表制作功能，能够完成许多复杂的数据运算、分析、统计和汇总工作。本章主要学习Excel 2016中的电子表格基础、公式与常用函数的使用、数据图表化和数据管理四个方面。
5.1导学
本章结构导图如图50所示。
图50第5章结构导图5.2电子表格基础
为了能够更好地认识Excel，从而方便后面内容的学习，本节主要了解的内容有Excel的文件组成、Excel 2016工作界面、数据的输入和工作表的格式化。
5.2.1文件组成
Excel 2016的文件扩展名是.xlsx，在Excel中用工作簿来保存并处理工作数据的文件。工作簿中的每一张表称为工作表（Sheet），一个Excel文件是由多个工作表组成的。在一个工作簿中，最多可以拥有255个工作表。新建的工作簿文件系统会默认新建3张空白工作表，表名分别为Sheet1，Sheet2，Sheet3，用户可以根据需要增加或删除工作表。每个工作表由若干行和列组成，行号用数字1，2，3，…，1 048 576表示；列号用A，B，C，…，AA，AB，…，XFD表示，最多有1683列。
工作表中的每个格子称为单元格，单元格是工作表的最小单位，也是Excel用于保存数据的最小单位。单元格中输入的各种数据，可以是一组数字、一个字符串、一个公式或一个图形等。每一个单元格都可用其所在的行号和列号标识，如A5单元格表示第A列第5行的单元格。若要表示一个连续的单元格，可以用该区域左上角和右下角单元格行列位置名来表示，中间用冒号表示。
大学计算机基础（立体化教材）（第2版）第5章Excel 2016电子表格5.2.2Excel 2016工作界面
Excel 2016的工作界面如图51所示，它主要包括快速访问工具栏、选项卡及选项卡下的功能区、名称框、编辑栏、工作表编辑区、工作表标签等。
图51Excel 2016工作界面1.  快速访问工具栏
该工具栏位于工作界面的左上角，包含一组使用频率较高的工具按钮，例如“保存”“撤销”和“恢复”等按钮。可单击快速访问工具栏最右侧的倒三角按钮，在展开的下拉列表中选择要在其中显示或隐藏的工具按钮。
2. 选项卡和功能区
标题栏的下方是一个由10个选项卡组成的区域，包括“文件”“开始”“插入”“页面布局”“公式”“数据”“审阅”“视图”“帮助”和“特色功能”。Excel 2016将处理数据的命令组织在不同的选项卡中。选择不同的选项卡，可以切换至不同的功能区。在选项卡下的功能区中，每个功能群组的右下角通常都会有一个小箭头（对话框启动按钮），用来打开与该组命令相关的对话框，以方便对要进行的操作做更详细的设置。
3. 名称框
用户可以为一个或者一组单元格定义一个名称，也可以从名称框中直接选取定义过的名称，以选中相应的单元格。
4. 编辑栏
编辑栏主要用于输入和修改活动单元格中的数据。当在工作表的某个单元格中输入数据时，编辑栏会同步显示输入的内容。
5. 工作表编辑区
工作表编辑区是由多个单元格行和列组成的网状编辑区域，用于显示或编辑工作表中的数据。
6. 工作表标签
工作表标签位于工作簿窗口的左下角，默认名称为Sheet1，Sheet2，Sheet3，…单击不同区域的工作表标签，可在工作表间进行切换。
5.2.3数据输入
输入Excel单元格的资料大致可以分为两类： 一种是可计算的数字资料（包括日期、时间），另一种是不可计算的文本型资料。下面介绍一些常用的输入数据类型。
（1） 数值型。可用于算术运算，使用的字符有数字0～9及一些符号（如小数点、+、-、%、$）所组成。在默认状态下，所有数字在单元格中均右对齐。
（2） 文本型。文本可以是数字、空格和非数字字符的组合，一般以字符串显示。在默认状态下，所有文本在单元格中均左对齐。不过，数字有时会被当成文字输入，如邮递区号、电话号码、身份证号码等。可以通过数字前加上英文输入法状态下输入的单撇号“’”（如’210000）将其定义为文本格式。
（3） 日期型。时间和日期被视为数字处理，在默认状态下，在时间和日期单元格中均右对齐。用斜杠或者减号分隔日期的年、月、日部分，如2020/05/06。按“Ctrl+；”快捷键可以输入当前系统日期，按“Ctrl+Shift+；”组合键可以输入当前系统时间。
在Excel 2016中，有时需要设置项目编号、等差序列、日期等，此时手动输入非常烦琐，利用Excel 2016自动填充功能可以提高工作效率。自动填充功能可以自动填充日期、时间等本质上是数值的数据。填充柄是活动单元格右下角的小黑色方块，它的使用可以在相邻的单元格中输入相同的数据或输入有序特征的数据，具体操作如下。
（1） 填充相同的数据。首先单击有数据的源单元格，将鼠标指针移动到单元格右下角的填充柄上，鼠标指针的形状变成“+”字形状，按住鼠标左键拖动到输入的最后一个单元格后放开，即可在选中的单元格中输入相同的数据。
（2） 填充序列数据。首先在单元格中输入序列的前面两个数，选中这两个单元格，如图52所示。当鼠标指针变成“+”字形状时，拖动填充柄到指定位置，即可在选中的单元格中输入序列数据，如图53所示。
图52序列填充
图53填充结果

5.2.4工作表的格式化
选择要设置格式的单元格区域，单击“数字”命令群组中相应的格式按钮可以设置数字格式；也可以右击单元格，从弹出的快捷菜单中选择“设置单元格格式”选项，在弹出的“设置单元格格式”对话框的“数字”选项卡中选择相应的分类，设置详细的数据类型，如图54所示。在“设置单元格格式”对话框中，还包括“对齐”“字体”“边框”“填充”和“保护”选项卡，可以根据需要选择相应的选项卡来对单元格的格式进行设置。
条件格式是指可以对含有数值、公式或其他内容的单元格应用某种条件，以决定数值的显示格式。条件格式是通过“开始”选项卡中的“样式”命令群组完成的，以图55为例，要求利用条件格式将数学成绩不及格的用红色显示。具体步骤如下。
首先选中需要设定条件格式的单元区域，选择“开始”选项卡中的“条件格式”，单击其下拉列表中的“突出显示单元格规则”，选择“小于”选项，如图56所示。打开条件格式中的“小于”对话框。其次，在对话框中输入小于的值为60，并且设置为“浅红填充色深红色文本”，如图57所示。单击“确定”按钮，完成条件格式设置，显示效果如图58所示。图54“设置单元格格式”对话框中的“数字”选项卡
图55数学成绩原始数据
图56条件格式

图57“小于”对话框图58显示效果
5.3公式与函数
公式和函数是Excel软件的核心。如果需要对工作簿中的数据进行统计运算，就可以利用公式和函数进行。在单元格中输入正确的公式或函数后，会立即在单元格中显示出计算结果。如果改变了工作表中与公式有关或作为函数参数的单元格内容，Excel会自动更新计算结果。在实际工作中，往往会有许多数据项是关联的，通过运用公式，可以方便快速地对工作表中的数据进行统计分析。
5.3.1单元格地址的引用
引用是对工作表的一个或一组单元格进行标识，它告诉Excel公式使用哪些单元格的值。通过引用，可以在一个公式中使用工作表不同部分的数据，或者在几个公式中使用同一单元格中的数值。同样，可以对工作簿的其他工作表中的单元格进行引用，甚至对其他工作簿或其他应用程序中的数据进行引用。引用包括相对引用、绝对引用和混合引用。
相对引用： 用字母标识列，用数字标识行。它仅指出引用的相对位置。当把一个含有相对引用的公式复制到其他单元格式位置时，公式中的单元地址也随之改变。默认情况下，新公式使用相对引用。例如，如果将单元格B2中的相对引用复制到单元格B3，系统将自动从“=B2”调整到“=B3”。
绝对引用： 在列表和行号前分别加上“$”。如果公式所在单元格的位置改变，绝对引用的单元格始终保持不变。例如，$A$1公式在复制、移动时，绝对引用单元格将不随着公式位置变化而改变。
混合引用： 在行列的引用中，一个用相对引用，另一个用绝对引用，如$E10或C$7。公式中的相对引用部分随公式引用复制而变化，绝对引用部分不随公式复制而变化。
5.3.2公式
公式是由用户自行设计并结合常量数据、单元格地址、运算符、范围区域引用和函数等元素进行数据处理和计算的算式。用户使用公式是为了有目的地计算结果，因此Excel公式必须且只能返回值，例如“=（A2+25）  / MAX （B5:D5） ”。输入公式必须以符号“=”开始，然后是公式的表达式。
Excel包含4种类型的运算符，分别是算术运算符、比较运算符、文本运算符和引用运算符。算术运算符用于连接数字并产生计算结果，计算顺序为先乘除后加减；比较运算符用于比较两个数值并产生一个逻辑值TRUE或FALSE；文本运算符“&”将多个文本连接成组合文本，例如，“计算机&学院”的运算结果为“计算机学院”；引用运算符包括冒号、逗号、空格，用于将单元格区域合并运算。其中，“:”为区域运算符，如B5:D5表示B5到D5之间所有单元格的引用；“，”为联合运算符，如SUM（A5，B3:C4） 代表A5以及B3到C4之间的所有单元格求和；空格为交叉运算符，产生对同时隶属于两个引用单元格区域的交集的引用。
如果在某个区域使用相同的计算方法，用户不必逐个编辑公式，这是因为公式具有可复制性。若希望在连续的区域中使用相同算法的公式，可以通过“双击”或“拖动”单元格右下角的填充柄进行公式的复制。若公式所在单元格区域并不连续，还可以借助“复制”和“粘贴”功能来实现公式的复制。
Excel 2016在“开始”选项卡的“编辑”命令组中提供了“自动求和”命令按钮。若对某一行或者一列中的数据区域自动求和，则只需选择此行或此列的数据区域，单击“自动求和”按钮，求和的数据将存入到此行数据区域右侧的第一个单元格中，或是此行区域下方的第一个单元格中。单击“自动求和”按钮右侧的下三角按钮，可选择平均值、计数、最大值、最小值和其他函数等常用公式，如图59所示。
图59“自动求和”按钮
5.3.3函数
Excel中的函数是由Excel内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块。因此，Excel函数也常被人们称为“特殊公式”。使用函数可以加快数据的录入和计算速度。Excel 2016除了自身带有内置函数外，还允许用户自定义函数。
Excel函数通常是由函数名称、左括号、参数、半角逗号和右括号构成。函数的参数是函数进行计算所必需的初始值。用户把参数传递给函数，函数按照特定的指令对参数进行计算，把计算结果返回给用户，如AVERAGE （B2:B9）即表示求B2到B9所有单元格中数据的平均数。
如果需要在某个单元格中输入一个函数，需要以等号“=”开始,接着输入函数名和该函数所带的参数，一般格式为函数名（参数1，参数2，……）；或者利用编辑栏中的“插入函数”按钮实现函数的插入，如图510所示。
图510“插入函数”对话框
在Excel 2016“公式”选项卡的“函数库”组中，将函数分成了不同的类型，如图511所示。当进行函数输入的时候，也可以直接从这里选择。在打开的“函数参数”对话框中，输入或选择参数后，单击“确定”按钮即可完成函数运算。
图511Excel函数库
Excel中常用的函数如下。
（1） 求和函数SUM。对所划定的单元格或区域进行求和，参数可以是常数、单元格引用或区域引用。
（2） 最大值函数MAX。求出指定区域中最大的数。
（3） 最小值函数MIN。求出指定区域中最小的数。
（4） 求平均值函数AVERAGE。计算出指定区域中的所有数据的平均值。
（5） 计数函数COUNT。求出指定区域中包含的数据个数。
（6） 条件函数IF。一般格式为IF （条件表达式，值1，值2），根据条件表达式的满足条件取值。当条件表达式的值为真时取“值1”，否则取“值2”作为函数值。
（7） 排序函数RANK。求指定值或数据在一个特定区域范围内的排名。
（8） 随机数据函数RAND。用来生成0～1平均分布的小数随机数。
（9） 条件计数函数COUNTIF。语法结构为COUNTIF（条件范围，条件表达式），对指定区域中符合指定条件的单元格计数的一个函数，用来计算符合某个条件的个数。
（10） 条件求和函数SUMIF。语法结构为SUMIF（条件范围，条件表达式，求和范围），可以对报表范围中符合指定条件的值求和，该函数根据指定条件对若干单元格、区域或引用求和。
5.3.4函数使用实例
1） IF函数实例
统计学生分数，如果分数超过60分，则输出“通过考试”，如果分数低于60分，则输出“不通过”。具体的操作步骤如下： 首先选中单元格C2，单击编辑栏中的“插入函数”按钮，打开“插入函数”对话框，选中IF函数，如图512所示。接着单击“确定”按钮，输入函数参数，如图513所示。最后的显示结果如图514所示。
图512插入IF函数
图513设置IF函数参数
图514IF实例显示结果
2) SUMIF函数实例
一个班级中有男生也有女生，要求计算该班级中男生的总成绩。具体的操作步骤如下： 首先选中单元格D2，单击编辑栏中的“插入函数”按钮，在打开的对话框中选择SUMIF函数，如图515所示。然后单击“确定”按钮，输入函数参数，如图516所示。最后的显示结果如图517所示。
图515插入SUMIF函数
图516设置SUMIF函数参数