11.Excel条件格式

12.Excel 美化表格

13.Excel公式与单元格引用

14.Excel函数与输入

15.Excel常用函数

16.Excel常用函数实例

17.Excel数据排序

18.Excel函数与输入数据筛选

19.Excel数据分类汇总

20.Excel

电子表格软件Excel 2016

电子表格软件通常是一个工作博管理多个二推(行、列）工作表的文档形式，不仅可以快速地制作表格，而且能够对表格中的数据进行各种计算、分析，并将数据用统计图的形式形象地表达出来，是数据分析乃至辅助决策的重要办公工具，

本章将通过Excel2016讲解电子表格软件的板念和基本使用方法。

1.Excel2016概述

Excel是当今比较流行的电子表格处理软件，是Microsoft Office套装软件之一，Excel2016的一些基本操作与0fce2016中其他组件的操作相似，掌握最基本的操作是熟练使用Exeel12016的基础。

1.1工作簿的基本操作

工作簿是Excel 2016存储在磁盘上的最小独立单位，以文件的形式存储在磁盘上，工作障名称就是文件名，其扩展名为.xlsx，一个工作博可以包含多个工作表，这样可使一个文件包含多种类型的相关信息，用户可以将若干相关工作表组成一个工作簿，操作时不必打开多个文件，而直接在同一文件的不同工作表中便捷地切换。

1.新建工作簿

启动Excel2016后，用户可以根据需要创建空白工作薄（临时命名为“工作簿1”）或选择某个模板创建基于该模板的工作簿。

打开某个工作簿后，单击快速访问工具栏中的“新建”按钮或按【Ctrl+N】组合键，也可以创建新的空白工作簿。

2.保存工作簿

创建和编辑了工作薄之后，需要将其保存以便后期使用。Exccl2016在“文件”菜单中提供了“保存”命令（对应【Ctrl+S】组合键）和“另存为”命令（对应【FI2】键），用于以当前默认设置保存工作博，以及以新文件名、路径或类型保存工作簿。对于新创建的工作簿，“保存”命令与“另存为”命令的作用相同。

以Excel2016默认格式保存的工作簿在早期版本的Excel中无法打开，因此，如果要在早期版本的Excel中打开用Exeel 2016创建、编辑的.xlsx工作簿，则需要使用“另存为命令将其保存为所需的兼容格式，即在“另存为”对话框中选择保存类型为“Excel97-2003工作簿”

3.打开工作簿

启动Exccl2016后，单击快速访问工具栏中的“打开”按钮，或按【Ctrl+O】组合键，或选择“文件”菜单的“打开”命令，通过“打开”窗格可以直接打开一个已有的工作簿，包括打开“最近”列表中列出的工作簿，也可通过“浏览”按钮在“打开”对话框中指定要打开的工作簿。

1.2 Excel2016的工作环境

图5.2是创建一个空白工作簿后的Excel2016的工作窗口。一个工作簿可以包含多个工作表，每一个工作表的名称在工作簿的底部以标签形式出现。在默认情况下，一个新工作簿包含3个工作表，如图5.2中的“工作簿l”由Sheetl、Sheet2和Sheet3这3个工作表组成，用户根据实际情况可以增减工作表和选定工作表。

从下图中可以看出，Excel2016窗口中也有“快速访问工具栏”和“文件”菜单，以及其他各类选项卡及选项组，其操作方法也与Wod2016相同，Exeel2016窗口主要有以下几部分。

（1）编辑栏

在选项组的下方是编辑栏，用于显示话动单元格中的数据和公式。编辑栏由按钮工具和编辑文本枢组成。用户可在编辑栏中对该单元格输入或编辑数据，如下图3。

（2）名称框

编辑栏左边的区域称为名称框，用于显示当前活动单元格的位置。我们可以利用名称框对单元格进行命名，以使操作更加简单明了。

（3）工作表区域

工作表区域为Excel2016窗口的主体，是用于记录数据的区域，所有数据都将存放在这个区域中。

(4）工作表标签

工作表标签位于工作簿文档窗口的左下底部，用于显示工作表的名称，初始为Sheetl、Sheet2、Shcct3.单击工作表标签将激活相应工作表，用户可以通过滚动标签按钮来显示不在屏幕内的标签。

1.3 Excel 2016的基本概念

为了更好地学习Excel2016，下面对一些在Excel2016中经常用到的概念加以解释。

1.工作表

在Excel2016中，工作簿与工作表的关系就像日常的账簿和账页之间的关系，一个账薄可由多个账页组成，Excel2016工作表具有以下特点。

1)每一个工作簿可以包含多个工作表（工作表的数目受限于可用的内存和系统资源），但当前工作的工作表只能有一个，称为活动工作表。

2)工作表的名称反映在屏幕的工作表标签栏中，标签呈白色的为活动工作表。

3）单击任一工作表标签可将其激活为活动工作表。

4)双击任一工作表标签可更改工作表名称。

5)工作表标签左侧的按钮用于管理工作表标签，单击它们可以分别激活上一个工作表标签和下一个工作表标签。

2.单元格

单元格是组成工作表的最小单位。它具有以下特点，

1）一个工作表最多可以包含16384（2¹⁴)列、1048576(2²⁰)行。

2)每一列用英文字母表示，即A,B.C.…XYZAA.AB.…AZ直到XFD；每一行用数字表示，即1.23..1048576.因此，在每一个工作表中，最多能有1048576×16384个单元格。每一个单元格都处于某一行和某一列的交又位置，这也就是“引用地址”（即坐标）。例如，B列和第3行相交处的单元格是B3.在引用单元格时(如公式中)，必须使用单元格的引用地址。

3）每个工作表中只有一个单元格为当前活动的，称为活动单元格，图5.2中带粗线墨绿框的单元格就是活动单元格。

4)活动单元格名称可在名称框中反映出来。

3.单元格内容

每一个单元格中的内容可以是数字、字符、公式、日期等，如果是字符，还可以分段落。

4.单元格区城

多个相邻的呈矩形状的一片单元格称为单元格区域(范围)。每个区域有一个名称，称为区域名，区域名由区域左上角单元格名称和右下角单元格名称，中间加冒号“：”表示。例如，“C3:F8”表示左上角C3单元格到右下角F8单元格共24个单元格组成的矩形区域，如下图4所示，

若给单元格区域C3:F8定义一个名称“Score”(在名称框中输入“Score”，然后按【Enter]键即可），当需要引用该区域时，使用“Score”和使用“C3:F8”的效果是完全相同的。

2 Excel2016的基本操作

Excel2016的基本操作包括工作范围选取、数据的输入及工作表的管理等。

2.1区域选择

区域是一组选定的单元格，既可以是连续的，又可以是离散的(也可仅有一个）。在Excel 2016中，输入数据或对数据进行操作，必须在当前单元格或当前区域内进行，因此区域的选择是一项十分重要的操作。

（l）单个单元格的选择

将鼠标指针指向单元格并单击即可选定该单元格，被选定的单元格成为活动单元格，其四周以墨绿色粗线条显示。也可以利用键盘来选择或移动活动单元格。

1）使用【↑】、【↓】、【←】、【→】方向键可使相邻的单元格成为活动单元格。

2）使用【Home】键，使当前行的A列单元格成为活动单元格。

3）使用【Ctrl+Home】组合键，使Al单元格成为活动单元格。

4）使用【PageUp】、【PageDown】键，使活动单元格向上、向下移一屏。

（2）单元格区域的选择

选择单元格区域用鼠标最为方便，只要将队标指针指向该区域任一角落的单元格，指动到对角的单元格，被选定的范围背景呈深色，活动的单元格则呈白色。

（3）多个不相邻的单元格或单元格区域的选择首先选定一个单元格或单元格区域，在选定下一个单元格或单元格区域之前按住【Ctrl】健，再进行单元格或单元格区域的选择。在任一时刻，工作表中只能存在一个话动单元格。若选择了多个不连续单元格或单元格区域，最后选择的单元格或单元格区域中左上角的单元格成为活动单元格，例如，选择了3个不连候区线，先选择A1：2和C4:E最后选择D7:E9.则D7成为活动单元格，如下图5所示。

图5 选择不连续区域

（4）整行或整列的选择

单击行号（1，2.3，…）或列标(A.B.C，…）即可选定整行或整列，

（5）选择整个工作表的所有单元格

选择整个工作表的所有单元格，可以使用下面两种快捷方法。

1）单击工作表区域左上角的“全选”按钮。

2)使用【Ctrl+A】组合键，

（6)快速定位到某一单元格

要快速移动光标选择单元格或单元格区域，可单击“开始”选项卡“编辑”选项组中的“查找和选择”下拉按钮，在下拉列表中选择“转到”选项，或按【FS】键，弹出“定位”对话框，在“引用位置”文本框中输入单元格或单元格区域的地址，如下图6所示，然后单击“确定”按钮，即可快速移动到指定的单元格或单元格区域上。

图6 “定位对话框”

（7）撤销选择

如果选择错误，可将鼠标指针移开所选单元格或单元格区域，然后单击，即可搬销选择：重新选择也可以撤销上一次的选择，

2.2向工作表中输入数据

Excel2016中的数据以常量和公式两种形式出现，本节介绍常量的输入。

1.直接输入数据

用户可以直接在单元格或编辑栏输入数据，输入的数报可以是文本型，数值型、日期和时间型。

（1）输入文本

Excel 2016文本包括汉字、英文字母、数字、空格及其他键盘能键入的符号。在单元格中输入文本的方法是选定一个活动单元格，直接由键盘入文本，输入完毕按【Enter】健或单击编辑栏中的“输入”按钮。如果在输入时想取消本次张作，可以按【Esc】健或单击编辑栏中的“取消”按钮。

文本输入时向左对齐，如下图7所示。当输入的文字长度超出单元格宽度时，若右边单元格无内容，则扩展到右边列：否则，救断是示，当在一个单元格丙入的内容需要分段时，按【Alt+Enter】组合键。

图7 向工作表中输入数据

某些表示序列代码的数字，并不参加算术运算，本质上是文本，如课程编号“061021”，若直接输入，系统会默认为数值，省略前面的“0”。对于这些文本型数字，输入时应以半角单引号引导，如“061021”，输入后在单元格左上角出现绿色三角标记，选定该单元格会出现提示。从其他应用程序导入的数据有些被认为是文本数据，不参加算术运算，可在计算前先将其转换为数值，如图8所示。

图8数值与文本转换快捷菜单

（2）输入数值

数值除了包括由数字（0~9）组成的字符串外，还包括+、-、E、e、S、%%及小数点(.）和千分位号（，）等特殊字符。数值型数据在单元格中默认为靠右对齐。

Excel 2016数值输入与数值显示未必相同，如输入数字长度超出单元格宽度或超过15位时，Excel2016自动以科学计数法表示，如（7.89E+08)。如果单元格数字格式设置为两位小数，此时输入三位小数，则末位将进行四舍五入。

Excel20l6在计算时，用输入的数值参与计算，而不是显示的数值。例如，某个单元格数字格式设置为两位小数，此时输入数值4678，则单元格中显示的数值为468，但计算时仍用4678参与运算。

在输入分数(如3/5）时，应先输入“0”及一个空格，然后再输入分数：否则，Excel 2016将把它处理为日期数据(如将3/5处理为3月5日）。

（3）输入日期和时间

Exel2016内置了一些日期和时间格式，当输入数据与这些格式相匹配时，单元格的格式就会自动转换为相应的“日期”或者“时间”格式，而不需要去设置该单元格为日期或者时间格式，Excel2016可以识别的日期格式和时间格式分别如图9和图10所示。

如果在同一个单元格中同时输入日期和时间，那么日期和时间之间要用空格进行分隔，如20/2/17口8：30.

日期或时间数据在单元格中右对齐，如果输入日期或时间是Excel2016不能识别的格式，则输入的内容将当作字符数据处理。

如果要在单元格中插入当前日期，可直接按【Ctrl+；】组合键：输入当前时间可按【Ctrl+Shi+；】组合键。

（4)表中有相同内容的单元格的输入

如图11所示，在按住【Ctrl】键的同时，选择所有要输入相同内容的单元格：输入数据，按【Ctrl+Enter】组合键即可完成操作。

2.利用下拉列表输入数据

单元格中的数据如果固定为某几项，可以创建一个下拉列表来输入数据，从而避免反复手工输入相同的汉字，创建下拉列表的操作步骤如下。

1)选中需要放置下拉列表的单元格。

2）单击“数据”选项卡“数据工具”选项组中的“数据验证”按钮，弹出“数据验证”对话框，如图12所示，

3）在“设置”选项卡的“允许”下拉列表中选择“序列”选项。

4)如果序列位于同一张工作表上，可在“来源”文本框中输入对序列的引用；也可以直接在“来源”文本框中输入序列数据（序列数据之间应以半角逗号分隔），如图12所示，勾选“提供下拉箭头”复选框。

5）设置完成后单击“确定”按钮。

单击单元格右侧的下拉按但会弹由下拉列表，从中选择国要的数解项即可，如图13所示。

3.利用填光功能输入有规律的数据

（l）自动填充

自动填充是根据初始值决定以后的填充项。当选定初始值所在的单元格或区城后，所选区域边柜的右下角处会出现一个黑点，称为填充柄。当鼠标指针指向填充柄时，鼠标指针会变成一个“瘦”加号，可以将填充柄向上、下、左、右4个方向拖动，经过相邻单元格时，就会将选定区域中的数据按某种规律自动填充到这些单元中。具体有以下几种情况。

1）初始值为纯字符或纯数字，填充相当于数据复制，如图14所示。

2）初始值为文字数字的混合体，填充时文字不变，最右边的数字递增(向下或向右拖动时)或者递减(向上或向左拖动时）。例如，初始值为Al，填充为A2.A3，”，如图15

3）对于日期型数据，填充为递增或递减的日期序列。

4)初始值为Excel2016默认的自动填充序列中的一员，按预设序列填充。例如，初始值为二月，自动填充三月、四月等，如图16所示。

用上面的方法进行的自动填充，每一个相邻的单元格相差的数值只能是l，要填充的序列差值是2或2以上，则要求先输入前两个数据，以给出数据变化的趋势，然后选定两个单元格，如图17所示，沿填充方向指动原标，填以后的效果如图18所示。

利用复制方式是达不到上述效果的。

注意：对于日翔型以及具有增序或减序可能的文本型数据，如果要实现复制填克，则在沿填充方向拖动填充柄时需要按住【Ctrl】键。

（2）序列填充

序列是指有规律的数据，如数字序列和日期序列，填充这样的数据不必全部手工输入，可以使用对话框进行填充，其操作步理如日刚于外

1）在序列起始单元格A1中输入一个起始值，如“2”，选定单元格A1。

2）单击“开始”选项卡“编辑”选项组中的“填充”下拉按钮”，在下拉列表中选择“序列”选项，弹出“序列”对话框，如图19所示。

3）在该对话框中分别指定“序列产生在”为“行”，“类型”为“等差数列”，“步长值”为“2”，“终止值”为“30”。

4)单击“确定”按钮，系统会自动在Al~A15中填入2~30，

（3）自定义序列

Excel除本身提供的预定义的序列外，还允许用户自定义序列，可以把经常用到的一些序列进行定义，储存起来供以后填充时使用。自定义序列操作步骤如下。

1）单击“文件”菜单中的“选项”按钮，弹出“Excel选项”对话框，单击“高级”按钮，在“常规”选项组中单击“编辑自定义列表”按钮，弹出“自定义序列”对话框，如图20所示，

2）在“输入序列”文本框中填入新的序列，在序列成员之间用逗号(注意：一定是半角方式下的逗号）或按【Enter】键分隔。

3）输入完毕后单击“添加”按钮，新输入的序列会显示在“自定义序列”列表框中。

序列定义成功以后就可以进行自动填充了，输入初始值后使用自动填充可节省许多输入工作量，尤其是序列多次出现时。

如果用户想把已经输入到工作表中的序列定义为Excel 2016中的自定义序列，只需选定这些数据，在“自定义序列”对话框中单击“导入”按钮即可，省去了重新定义输入的麻烦。

用户可以对已经存在的自定义序列进行编辑，或者删除不再使用的自定义序列，只需在“自定义序列”对话框的“自定义序列”列表概中选择要编或删除的自定义序列，被选定的序列将显示在“输入序列”文本框中，编辑完成后单击“添加”按钮确认编辑：如果要到除序列，则单击“副除”按钮。

注意：对于Exccl2016提供的序列不能进行编料和删除，

4.数据有效性

数据有效性可自动阻止不符合预先制定规范的数据输入，从而控制和确保用户输入单元格数据的有效性，适用于需要大量人工输入数据的场合。例如，在输入学生成绩时进行有效性检验，要求学生的成绩介于0～100分，其操作步骤如下。

1）选择成绩录入区域，如C4:F9。

2）单击“数据”选项卡“数据工具”选项组中的“数据验证”按钮，弹出“数据验证”对话框，如图21所示。

3）在“设置”选项卡中设置输入数据范围。

4）设置完毕，单击“确定”按钮即可。

此外，还可以在“输入信息”和“出错警告”选项卡进行相应的设置。在输入数据时，若超出数值设置的有效范围，系统就会禁止输入。

使用批注

使用批注可为工作表中的单元格添加注释，方便对于数据进行解释说明，使工作表更易于理解。

（1）插入批注

插入批注的操作方法为：选择单元格，单击“审阅”选项卡“批注”选项组中的“新建批注”按钮：或右击该单元格，在弹出的快捷菜单中选择“插入批注”命令，在批注编辑框中输入批注内容，单击其他任一单元格即可完成批注的添加。

有批注的单元格默认隐藏批注，仅在单元格的右上角显宗一个红色三角，鼠标指针经过该单元格可显示批注。

（2）编辑批注

编辑批注的操作方法为：选择含有批注的单元格，单击“审阅”选项卡“批注”选项组中的“编辑批注”按钮：或右击该单元格，在弹出的快捷菜单中选择“编钢批注”选项，对批注内容进行编辑，

在批注编辑状态下，右击批注编辑框，在弹出的快捷菜单中选择“设置批注格式”命令，可以进行批注格式的设置。

（3）副除批注

删除批注的操作方法为：选择含有批注的单元格，单击“审阅”选项卡“批注”选项组中的“到除”按钮：或右击该单元格，在弹出的换捷菜单中选择“别降批注”奇令，即可删除批注。

2.3编辑工作表

1.选择工作表

若要对工作表进行操作，首先要选定该工作表，使其成为当前工作表。选定一个工作表，可单击位于工作窗口底部工作表标签中该工作表的名称，如果工作表过多，标签栏显示不完全时，可通过标签栏滚动按钮前后翻阅工作表名称。

若选择多个连续工作表，可先单击第一个工作表，然后按住【Shift】键单击最后一个工作表即可。若选择多个不连续工作表时，可通过按住【Ctrl】键单击选取。选定多个工作表将组成一个工作组，选定工作组的好处是在其中一个工作表的任意单元格中输入数据或设置格式，在工作组其他工作表的相同单元格中将出现相同数据或相同格式，显然，如果想在工作博的多个工作表中输入相同数据或设置相同格式，通过设置工作组可节省不少时间。

若要取消工作组，可通过单击工作组外任意一个工作表标签来进行，

如果要选中全部工作表，则可右击工作表标签，在弹出的快捷菜单中选择“选定全部工作表”命令，如图22所示。

2.插入新的工作表

插入新的工作表可以通过以下3种方法来实现。

1）单击工作表标签右侧的“新工作表”按钮，则自动在当前工作表之后插入一个新工作表。

2)右击某个工作表标签，在弹出的快捷菜单中选择“插入”命令，弹出“插入”对话框，单击“工作表”图标，然后单击“确定”按钮，则在选择的工作表前面插入了一个新工作表。

3)单击一个工作表标签，然后单击“开始”选项卡“单元格”选项组中的“插入”下拉按钮，在弹出的下拉列表中选择“插入工作表”选项，则在选择的工作表前面插入了一个新的工作表。

3.移动或复制工作表

工作表可以在工作簿内或工作簿之间移动或复制。选定要移动或复制的工作表，单击“开始”选项卡“单元格”选项组中的“格式”下拉按钮，在弹出的下拉列表中选择“移动或复制工作表”选项，弹出“移动或复制工作表”对话概，如图23所示。选择要移动或复制的目标位置，包括工作簿名和工作表名，是移动还是复制，取决于是否勾选“建立副本”复选框，如果勾选该复选框，则为复制：否则，为移动。设置完成后，单击“确定”按钮，完成操作。

如果是在当前工作簿内移动或复制工作表，还可用鼠标拖动：若要移动工作表，可以单击要移动的工作表标签，然后沿着工作表标签行将工作表标签拖动到新的位置即可：若要复制工作表，可以单击要复制的工作表标签，按住【Ctrl】键，然后沿着工作表标签行将工作表标签拖动到新的位置即可。

4.重新命名工作表

在实际应用中，一般不使用Excel默认的工作表名称。为了从工作表名称就能了解工作表的内容，需要给工作表重新命名，以图7中的成绩单为例，以下3种方法都可用于将工作表Sheetl重命名为“成绩单”，

1）选择工作表Sheetl，单击“开始”选项卡“单元格”选项组中的“格式”下拉按钮，在弹出的下拉列表中选择“重命名工作表”选项。

2）右击工作表Sheet1，在弹出的快捷菜单中选择“重命名”命令，如图22所示。

3)双击工作表Sheetl。

这3种方法都会使标签中的工作表名Sheetl反白显示，此时输入新名称“成绩单”，再按【Enter】键即可。

删除工作表

当不再需要某个工作表时，可将其副除。选定要删除的工作表，单击“开始”选项卡“单元格”选项组中的“删除”下拉按钮，在弹出的下拉列表中选择“删除工作表”选项；或右击要删除的工作表，在弹出的快捷菜单中选择“到除”命今。

6.拆分或冻结工作表

由于屏幕大小有限，若工作表很大时，往往只能看到工作表的部分数据。如果希望比较对照工作表中相距甚远的数据，可将窗口分为几部分，在不同窗口均可通过移动滚动条显示工作表的不同部分，这需要通过窗口的拆分来实现.

工作表窗口拆分分为水平拆分，垂直拆分，水平、重直同时拆分3种，

（1）水平拆分

单击水平拆分线下一行的行号或下一行最左列的单元格，如选定单元格A2，单击“视图”选项卡“窗口”选项组中的“拆分”按钮，则实现水平拆分，工作表被分为上、下两个窗口、如图24所示。

（2）垂直拆分

单击垂直拆分线右一列的列号或右一列最上方的单元格，如选定单元格Cl，单击“视图”选项卡“窗口”选项组中的“拆分”按钮，则实现垂直拆分，工作表被分为左、右两个窗口，如图25所示。

（3）水平、垂直同时拆分

单击工作表非第一行也非第一列的任一单元格，如选定单元格C2，单击“视图”选项卡“窗口”选项组中的“拆分”按钮，则活动单元格上侧和左侧分别出现拆分线，工作表被分为上、下、左、右4个窗口，如图26所示，

拖动水平拆分框或垂直拆分框都可以移动拆分线的位置，双击拆分线或单击“拆分”按钮可以取消窗口的拆分。

单击“视图”选项卡“窗口”选项组中的“冻结窗格”下拉按钮，在弹出的下拉列表中选择“冻结窗格”选项，如图27所示，可以把图26中左上、左下和右上3个区域冻结起来，即上下移动窗口时，工作表的第一行(字段名）始终不动：左右移动窗口时，第一、二两列（学号、姓名)始终不动，这样便于观察工作表中的数据。

如果在“冻结窗格”下拉列表中选择“冻结首行”和“冻结首列”选项，则可直接冻结工作表中的第1行和第A列，而无须考虑当前单元格的位置。

单击“视图”选项卡“窗口”选项组中的“冻结窗格”下拉按钮，在弹出的下拉列表中选择“取消冻结窗格”选项只取消窗口的冻结，单击“拆分”按钮，可以同时取消窗口的拆分和冻结。