第5章
Excel表格处理





教学时间： 学习3周，督学1周
教学目标
知识目标： 
 理解Excel工作簿、工作表、单元格的概念。
 理解数据类型的概念和作用，掌握根据数据的内容确定数据类型的方法。
 理解公式、运算符的概念和用法，理解公式复制填充的原理。
 理解绝对引用、相对引用、混合引用的概念，能根据实际情况选择正确的引用方式。
 理解函数的概念，掌握常用函数的使用方法。
 了解图表的常用类型和作用，能选择合适的图表表达数据。
 理解图表和数据的对应关系，掌握常用图表的创建方法，了解图表元素的设置方法。
 理解筛选的工作原理，掌握自动筛选条件的设置，掌握高级筛选中条件区域的构成规则。
 理解分类汇总的方法和原理，能根据需要确定分类字段、汇总方式和汇总项。
 理解数据透视表的行、列、筛选器、值的作用，了解数据透视表的切片和分组。

能力目标： 
 掌握在Excel中新建工作簿、保存数据的基本方法。
 掌握Excel数据类型设置方法，能快速准确输入各种类型数据。
 掌握数据编辑的方法和技巧。
 掌握单元格格式设置方法。
 掌握公式的使用方法，能运用公式对数据进行计算。
 掌握常用函数的使用方法，能灵活选用各种函数解决实际问题。
 了解函数帮助的用法，能利用函数向导和帮助系统快速掌握新函数。
 掌握图表的创建方法，能根据需要正确选择数据创建图表。
 了解图表中各种元素的属性设置方法，能通过修改各种属性使图表能更加形象直观的表达信息。
 掌握单关键字、多关键字排序的方法。
 掌握在数据列表中建立筛选的方法，能熟练设置筛选条件筛选数据。
 掌握高级筛选的方法，能熟练构建高级筛选条件，灵活运用高级筛选功能筛选数据。
 掌握分类汇总的操作方法，能灵活运用分类汇总对数据进行分组统计和分级显示。
 了解数据透视表和数据透视图的使用方法，能利用数据透视表对数据进行多维度交互分析统计，并通过数据透视图展示分析结果。
单元测试题型： 





选择题填空题判断题Excel操作

题量分值题量分值题量分值题量分值
202010201010450



测试时间： 40分钟
教学内容概要
5.1表格基本概念(基础)
 工作簿、工作表与单元格
 工作簿基本操作
 工作表操作
5.2数据的基本编辑
 数字格式(基础)
 输入数据(基础)
 填充数据(基础)
 数据验证(进阶)
5.3设置表格格式
 设置单元格格式(基础)
 套用表格格式(高阶)
 条件格式(进阶)
 页面设置（基础）
5.4编辑表格(基础)
 查找与替换
 插入、删除与清除
 单元格移动、复制与选择性粘贴
5.5公式与函数(本章重点)
 公式的基本概念(基础)
 公式的输入与编辑(基础)
 单元格引用方式(基础)
 常用函数(基础)
 数学和三角函数(进阶)
 文本函数(进阶)
 日期函数与时间函数(进阶)
 逻辑函数(进阶)
 统计函数(高阶)
 查找和引用函数(高阶)
 函数嵌套(进阶)
5.6图表
 插入图表(基础)
 编辑图表(进阶)
5.7数据管理与分析(本章重点)
 排序(基础)
 筛选和高级筛选(进阶)
 分类汇总(进阶)
 数据透视表与数据透视图(高阶)
5.1表格基本概念
生活中我们常常需要处理或呈现大量数据，例如学生成绩、职工工资、产品销售情况等。这些数据以表格的形式保存在计算机中，通过电子表格软件进行处理和分析。目前在各种操作系统环境下或在线使用的电子表格软件有很多，例如Office办公软件中的Excel、WPS表格、Numbers 表格、OpenOffice Calc、Google表格、腾讯文档在线表格等。
微软Office办公软件中的Excel是一种功能强大、使用广泛的电子表格应用软件，能方便迅速地处理大量数据，包括数据的输入输出、基本编辑排版、完成复杂的计算和统计、制作图表、数据管理等。本章以Excel 2016为例介绍电子表格的基本知识和操作技术。
5.1.1工作簿、工作表与单元格





视频名称工作簿、工作表与单元格二维码



主讲教师向华
视频地址

Excel的窗口组成如图5.1所示，除了包括与Word的窗口类似的快速访问工具栏、功能区、滚动条、状态栏以外，还包括Excel特有的表格元素。


图5.1Excel窗口界面


1. 工作簿
工作簿是Excel用来保存处理电子表格数据的文件，文件默认扩展名为.xlsx。新建工作簿时，工作簿默认名字为“工作簿1”，显示在Excel窗口的标题栏中。新建的工作簿默认包含一张工作表，如果需要，可以在工作簿中添加工作表。在Excel中，一个工作簿最多可以包含255张工作表。单击“文件”选项卡“选项”命令，在“Excel选项”对话框的“常规”选项“新建工作簿时”组“包含的工作表数”中可以设置新建工作簿时包含的工作表数。
2. 工作表
Excel工作表是一个由行和列组成的二维表，显示在工作簿窗口中。在工作表中可以输入、显示、统计、分析数据。工作表左下角的工作表标签用于显示工作表的名称。当工作簿中包含多张工作表时，白色标签的工作表为当前正在编辑的活动工作表，单击工作表标签可以切换活动工作表。工作表名是工作簿中识别工作表的唯一依据，所以同一个工作簿中不能有两个同名的工作表。
3. 单元格
在工作表中某一行与某一列交叉点的方格称为单元格，用来存放一个数据或一个公式。单元格是Excel编辑处理数据的最小单位，不可以拆分。

一张工作表包含1048576×16384个单元格。通常以单元格所在列的列标与所在行的行号组合作为单元格的名称，也称单元格地址。列标使用字母编号，依次为A，B，…，Z，AA，AB，…，AZ，BA，BB，…，XFD。行号使用数字编号，依次从1到1048576。例如第一行第一列单元格的地址为A1。为了区分同一工作簿中不同工作表里的单元格，可以在单元格地址中加上工作表名称，例如Sheet1!A1表示Sheet1工作表中的A1单元格。
4. 名称框与编辑栏
单击选定某一个单元格时，工作表的左上角的名称框中将显示单元格的地址。也可以直接在名称框中输入单元格地址，按Enter键快速定位单元格。
名称框右侧是单元格的编辑栏，用于显示和修改当前单元格的数据和公式。选定单元格以后，单击编辑栏，可以修改单元格中的数据和公式。编辑完成后单击编辑栏左侧的“输入”或“取消”按钮确认或取消修改。
工作簿、工作表与单元格后测习题
(1) 以下正确的选项是。

A. 工作簿和工作表都是文件B. 工作簿是文件，含多个工作表
C. 工作簿和工作表都不是文件D. 工作表是文件，含多个工作簿
(2) 在Excel 2016中，一个工作表有。
A. 1048576行，16384列B. 1048576行，256列
C. 360行，26列D. 任意多个行与列
(3) 在Excel的某工作表中采用系统默认的列表行号命名，如果某个单元格位于136行BA列，则该单元格的名称为。
A. BA136B. 136BA
C. B1A36D. AB136
5.1.2工作簿基本操作





视频名称工作簿基本操作二维码



主讲教师向华
视频地址

1. 新建工作簿
启动Excel时，系统会
在窗口中询问是打开最近使用的文档、其他工作簿，还是新建一个工作簿。如果选择新建空白工作簿，系统
在内存中为用户新建一个工作簿，工作簿名字默认为“工作簿1”，显示在窗口标题栏中。
单击“文件”选项卡 “新建”命令，可以选择新建空白工作簿或从联机模板新建工作簿，如图5.2所示。


图5.2新建工作簿


2. 保存工作簿
单击“文件”选项卡 “保存”命令或单击快速访问工具栏上的“保存”按钮，可保存工作簿文件。
如果工作簿是第一次保存，窗口中将显示“另存为”菜单，可以选择将工作簿保存在本机或OneDrive个人云存储空间中。如果选择将工作簿保存到本机，系统将弹出“另存为”对话框，在对话框中设置好文件保存的位置、文件名和保存类型三要素后，单击“保存”按钮保存工作簿。
对于已经保存过的工作簿，如果需要再另外保存一份，可选择“文件”选项卡中的“另存为”命令，在“另存为”菜单中进行操作。
除了可以将工作簿保存为.xlsx格式以外，在“另存为”对话框的“保存类型”列表中可以选择将工作簿保存为启用宏的工作簿(*.xlsm)、XML数据(*.xlm)、网页(*.htm; *.html)、文本文件(*.txt)、CSV逗号分隔(*.csv)等其他多种文件格式，使工作簿中的数据能在其他软件中打开或导入。
工作簿基本操作后测习题
(1) 在Excel 2016中新建一个工作簿，将该工作簿保存，则保存文件的扩展名是。
A. docxB. xlsxC. pptxD. txt
(2) Excel 2016新建的工作簿默认包含张工作表。
A. 1B. 2C. 3D. 4
5.1.3工作表操作





视频名称工作表操作二维码



主讲教师向华
视频地址

一个工作簿文件中可以包括多张工作表。对于工作表，也可以进行选定、插入、移动、复制、重命名、删除等操作。
1. 选定工作表
单击工作表标签，可以选定工作表。
如果要选择多张相邻的工作表，可以先单击要选择的第一张工作表的标签，然后按住 Shift 键再单击最后一张工作表的标签。如果要选定多张不相邻的工作表，可以先单击要选择的第一张工作表的标签，然后按住 Ctrl 键单击其他工作表的标签。如果要选定工作簿中所有工作表，可用鼠标右击工作表标签，单击快捷菜单中的“选定全部工作表”命令。
2. 插入工作表
单击工作表标签栏右侧的“新工作表”按钮，可以在当前工作表右侧插入一张新工作表。

单击“开始”选项卡“单元格”组中“插入”命令中的“新工作表”，或者右击工作表标签，在快捷菜单中选择“插入”，在“插入”对话框中选择“工作表”，都可以在当前工作表的左侧插入一张新工作表。
3. 工作表重命名
新插入的工作表默认名字为“Sheet n”。双击工作表标签，或右击工作表标签，在快捷菜单中选择“重命名”，工作表名将反相显示，此时可以输入新的工作表名称，重命名当前工作表。

图5.3“移动或复制工作表”对话框

为工作表重命名时应注意，在同一个工作簿中不能有两个同名的工作表。
4. 移动和复制工作表
要移动或复制工作表，需要先选定要操作的工作表，在“开始”选项卡的“单元格”组中单击“格式”按钮，执行“移动或复制工作表”命令，在“移动或复制工作表”对话框中设置移动或复制到的目标工作簿名称和目标位置，如图5.3所示，单击“确定”按钮完成操作。如果要复制工作表，还需要选中“建立副本”复选框。
在工作表标签上按下鼠标左键，在各工作表标签之间拖动，也可以快速移动工作表。

5. 删除工作表
要删除工作表，可以选定工作表后，单击“开始”选项卡“单元格”组“删除”命令中的“删除工作表”，或者右击工作表标签，在快捷菜单中选择“删除”。
注意： 对工作表进行的插入、删除、重命名、移动或复制等操作，不能通过单击快速访问工具栏中的“撤销”按钮撤销操作。所以进行工作表操作，特别是删除工作表时要慎重操作。
工作表操作后测习题
(1) 在Excel中，要选定多个连续工作表，可以。

A. 单击各个工作表
B. 单击第一个工作表，然后按住Shift键，再单击最后一个工作表
C. 单击第一个工作表，然后按住Ctrl键，再单击最后一个工作表
D. 单击第一个工作表，然后拖动到最后一个工作表
(2) Excel窗口底部有一行工作表标签，双击其中某个标签可以。
A. 实现工作表间的切换B. 为工作表重新命名
C. 删除工作表D. 复制一个新的工作表
(3) 以下操作中，可以挽回刚被误删的一个工作表的是。
A. 单击“撤销”按钮B. 单击“恢复”按钮
C. 按Ctrl+Z键D. 关闭文件不保存，然后再打开
5.2数据的基本编辑
5.2.1数字格式





视频名称数字格式二维码



主讲教师向华
视频地址

Excel单元格中的数据可以根据需要设置数字格式，例如文本、数字、货币、百分比、日期、时间等。设置数字格式后，单元格中的数据将按照指定格式显示，在计算、处理时也将采用不同的处理规则。
选定单元格或单元格区域后，在“开始”选项卡的“数字”组中可以设置数字格式。也可以单击“开始”选项卡“数字”组右下角的对话框启动器，打开“设置单元格格式”对话框，在“数字”选项卡中设置数字格式，如图5.4所示。


图5.4“设置单元格格式”对话框


Excel包括以下12种数字格式。

(1) 常规： 常规格式是Excel的默认数字格式。常规格式单元格不包含任何特定的数字格式。在常规格式单元格中输入数字时，多数情况下数字按输入的方式显示，并靠右对齐。如果单元格宽度不够显示整个数字，系统将对数字的小数部分四舍五入，并自动加大列宽以显示整数部分。如果常规格式单元格中的数字超过12位，将自动采用科学记数形式显示。在常规格式单元格中输入非数字字符时，字符靠左对齐显示。
(2) 数值： 用于表示一般数字。数值类型单元格可以指定小数位数，选择是否使用千位分隔符，设置负数的显示方式。数值类型单元格可以进行各种算术运算。
(3) 货币： 用于表示货币值，采用千位分隔符显示。货币类型单元格可以指定货币符号、小数位数，设置负数的显示方式。
(4) 会计专用： 用于表示货币值，采用千位分隔符显示。会计专用类型可以指定货币符号、小数位数。会计格式在显示数值时，分别将货币符号和小数点对齐。
(5) 日期： 用于将日期和时间系列数值显示为日期值。在日期类型设置中，可以将日期设置为使用“/”“”“年月日”分隔的各种数字、汉字、英文缩写等显示格式，其中带星号(*)开头的日期格式与操作系统中日期和时间的区域格式设置相关。
(6) 时间： 用于将日期和时间系列数值显示为时间值。在时间类型设置中，可以将时间设置为使用“:”“时分秒”分隔的各种数字或汉字等显示格式，其中带星号(*)开头的时间格式与操作系统中日期和时间的区域格式设置相关。
(7) 百分比： 用于将数值显示为百分比形式。在百分比设置中，可以设置百分比中的小数位数。
(8) 分数： 用于将数值显示为分数形式。在分数设置中，可以设置分母的位数，或将分母指定为2、4、8、16。
(9) 科学记数： 使用科学记数法aEn形式显示数字。其中a为包含一个整数位的小数，E表示指数，n表示10的n次幂，设置中的小数位数指a中显示的小数位数。例如123.456采用科学记数格式，保留两位小数显示为1.23E+02。
(10) 文本： 文本数据主要是一些称谓性字符或描述性文字。设置为文本格式后，单元格显示的内容与输入内容完全一致。文本单元格中，输入的数字也会作为文本处理，不能进行算术运算。例如编号、身份证号、电话号码等数字通常设置为文本格式。Excel默认文本格式单元格中的数据靠左对齐，其他格式单元格数据靠右对齐。
(11) 特殊： 根据不同国家和地区的设置，将数字显示为邮政编码、规定类型的数字等。
(12) 自定义： 使用户能根据需要自定义数字格式。通常先选择一个与需要设置的格式相近的内置格式，然后到自定义各种中更改格式的描述代码段，创建自定义格式。
在设置单元格数字格式时应该注意： 
 为了能够简便、准确的输入数据，建议先选定要输入数据的单元格区域，设置数字格式后，再逐个单元格输入数据。
 对于数值、货币、会计专用、百分数、科学计数格式，Excel 最多只保留 15 位数字精度。如果数字长度超出了 15 位，Excel 会将多余的数字位转换为 0。
 设置数字格式后，如果单元格宽度不足以显示整个数据，将出现 “#####”提示。此时拖动单元格列标右侧的分隔线，加大列宽，即可正常显示数据。
数字格式后测习题
(1) 向某单元格输入的数值数据或文本数据，在默认情况下的对齐方式是。
A. 数值靠左，文本靠右B. 两者均靠左
C. 数值靠右，文本靠左D. 两者均靠右
(2) 一个工作表的同一列中单元格的数字类型。
A. 必须相同B. 必须设置为常规型
C. 可以不同D. 必须设置为文本型
(3) 未设置数据类型时，单元格默认的数据类型是。
A. 常规型B. 文本型
C. 数值型D. 科学计数型
(4) 将E2单元格设置为分母为一位数的分数，在E2单元格中输入“3/4”，确定后选定E2单元格，则。
A. 单元格和编辑栏中都显示3/4
B. 单元格和编辑栏中都显示0.75
C. 单元格中显示3/4，编辑栏中显示0.75
D. 单元格中显示0.75，编辑栏中显示3/4
5.2.2输入数据





视频名称输入数据二维码



主讲教师向华
视频地址

在Excel里，数据存放在单元格中，要输入数据，或者修改单元格中已有的数据，需要先选定单元格，在单元格输入或在编辑栏中输入。
1. 选定单元格
在Excel中，可以单击选定单个单元格、单行、单列，也可以配合Shift键或Ctrl键，选择多行、多列、多重区域。
选定单个单元格： 直接单击要选择的单元格，或者使用方向键移动将当前单元格移动到要选定的单元格上，或者在名称框中输入单元格地址后回车，都可以选定相应单元格。选定一个单元格后，单元格四周将用加粗的边框标识，这个单元格就是活动单元格，地址显示在名称框中。
选定单一单元格区域： 将鼠标指向要选择的单元格区域角上的单元格，按下并向四周拖动出一个矩形区域，松开鼠标，矩形区域内的所有单元格将都被选定。也可以先单击选定区域某个角上的一个单元格，然后按住Shift键，单击选定单元格对角线另一端的单元格，对角线对应的矩形区域都被选定。选定单元格区域后，整个区域四周有粗边框标识，选择区域用深色显示，而选择过程中选定的第一个单元格颜色不变，是活动单元格，地址显示在名称框中。
选定多重区域： 选定一个单元格或单元格区域后，按住Ctrl键，同时再选定其他单元格或单元格区域，可以选择多重单元格区域。选定的多重区域用深色显示，但不显示加粗边框，活动单元格为最后一次选择单元格区域时选定的第一个单元格。
选定单行或单列： 直接单击工作表中的行号或列标。
选定相邻的行或列： 在行号或列标上拖动鼠标选择，也可以单击选定一行或列后，按住Shift键，再单击选择要选定的最后一行或列。
选定不相邻的行或列： 先选择要选的某行或列，按住Ctrl键，再选择要选的其他行或列。
选定工作表中的所有单元格： 单击行号和列标交叉处的全选按钮，可以选定工作表中的所有单元格。
选定数据区域内的所有单元格： 选定数据区内的任意一个单元格，按下Ctrl+A组合键，可以选定数据区内的所有单元格。
取消选择： 单击工作表中的任意一个单元格，可以取消已选的单元格或单元格区域。
2. 编辑单元格数据
在Excel中，单元格是存放数据的基本单位。要在一个单元格中输入数据，首先要选定该单元格，再输入数据。输入完成后按Enter键、Tab键或方向键确定。
要修改单元格中已有的内容，可以双击单元格进入单元格编辑状态，在单元格内移动光标，对内容进行插入、删除。修改完成后按Enter键、Tab键或方向键确定。
选定单元格时，编辑栏中显示单元格中的内容。进入编辑栏可以修改单元格内容，修改完成后单击输入框左侧的“输入”按钮确认，或单击“取消”按钮取消编辑。
在输入数据之前，如果不设置单元格的数字类型，Excel默认所有空单元格都是常规型。如果要在常规类型单元格中输入编号、电话号码之类的数字字符，可以先输入一个英文单引号，再输入数字，系统就会将数字字符按文本处理，靠左对齐。
在单元格中输入货币、日期、时间、百分比、科学计数等类型数据时，系统将根据输入的数据中的“$”“/”“:”“%”“E”等符号自动识别数字类型并设置相应格式。
为了避免将分数类型数据中的分数线“/”识别为日期数据中的分隔符号，在输入分数时需要先输入一个0和一个空格，再输入分数，系统会将单元格自动识别为分数类型。
输入数据后测习题
(1) 在Excel中按从上到下、从左到右的次序依次选取了三个不连续单元格区域A3:D5、C6:E9、D8:G11后，则活动单元格是。

A. D5B. C6C. D8D. G11
(2) 在Excel中，选择多个不连续的单元格区域的方法是： 先拖动选取第一个单元格区域，然后按住键再拖动选取第二个及以后的单元格区域。
A. ShiftB. AltC. F1D. Ctrl
(3) 单击或双击某一单元格时，所对应的“设置单元格格式”对话框。
A. 内容完全相同B. 毫无相同之处
C. 后者的内容多D. 后者的内容少
(4) 在Excel中输入分数2/3时，可以采用加前缀的形式(0 2/3)方式输入，以免与格式相混。
A. 日期B. 货币C. 数值D. 文本
(5) 向某单元格输入数据时，如果要取消已输入的数据，应该按键。
A. EnterB. EscC. InsertD. 左移箭头
5.2.3填充数据





视频名称填充数据二维码



主讲教师向华
视频地址

如果要在某一行或某一列输入相同的数据或有一定规律的数据，可以使用Excel自动填充或快速填充功能快速输入。


图5.5填充柄

1. 自动填充
选定一个单元格或者单元格区域时，单元格或单元格区域右下角的小方块称为填充柄，如图5.5所示。拖动或双击填充柄可以自动填充数据。

拖动填充柄时，Excel根据源数据单元格的内容进行复制填充或序列填充。具体填充规则如表5.1所示。


表5.1单元格自动填充规则



源数据单元格类型填 充 方 式

数值

非数字文本

大于4294967295的数字文本复制填充
星期、月份、日期、季度

时间

小于4294967295的数字文本序列填充
两个连续的数值型单元格序列填充(默认为等差数列)

拖动填充柄填充数据后，填充的单元格右下角将显示如图5.6所示自动填充选项，展开自动填充选项列表，可以选择填充方式。

在填充序列时，数值、日期、文本数据可以设置不同的序列填充方式。选定源数据单元格和相邻的待填充单元格区域后，单击“开始”选项卡“编辑”组中的“填充”下拉列表，选择“序列”，在“序列”对话框中可以设置数值和日期的序列填充方式，如图5.7所示。

单击“文件”选项卡中的“选项”命令，在“Excel选项”对话框左侧列表中选择“高级”，单击“常规”分组中的“编辑自定义列表”按钮，在“自定义序列”对话框中可以添加、修改、删除



图5.6设置自动填充方式




图5.7“序列”对话框




自定义序列，如图5.8所示。设置好自定义序列后，在单元格中输入序列中的一个值，拖动填充柄，就可以按设定的序列内容自动填充。


图5.8“自定义序列”对话框


2. 快速填充
快速填充可以自动感知要填充的单元格与
其四周单元格中已有数据的差异，根据差异确定填充方式并
对单元格进行快速填充。使用快速填充(Ctrl+E组合键)可以快速在单元格数据中添加、修改字符，提取部分字符，将单元格内容

图5.9快速填充效果
合并。例如在如图5.9所示工作表中，A列为学生姓名，B2、C2单元格是A2单元格学生的姓和名，使用快速填充可以快速在B列和C列填入其他学生姓和名。在D列已填写学生电话号码，E2单元格为对D2单元格电话号码的中间部分打码，使用快速填充可以快速对其他学生电话号码打码。

填充数据后测习题
(1) 填充柄位于选定单元格区域的加粗边框的角。

A. 左上 B. 右上C. 左下D. 右下
(2) 当前选定了一个单元格C2，其数据为12，拖动填充柄至单元格C9，则单元格C6的值为。
A. 12B. 16C. 19D. 0
(3) 对Excel填充功能描述正确的是。
A. 填充就是复制数值
B. 填充可以实现快速输入等差和等比数列
C. 填充时可以自动跳过填充区域中已有数据的单元格
D. 填充只能按列方向而不能按行方向进行
(4) 对Excel某一单元格的数据进行编辑时，可以使它进入编辑状态。
A. 左键单击单元格B. 将鼠标指向单元格
C. 左键双击单元格D. 右击单元格

5.2.4数据验证





视频名称数据验证二维码



主讲教师向华
视频地址

为了保证数据的正确合理，Excel使用数据验证来限制单元格的数据类型或输入单元格的值。
选定单元格或单元格区域后，单击“数据”选项卡“数据工具”组 “数据验证”按钮，在如图5.10所示“数据验证”对话框中

图5.10“数据验证”对话框
设置单元格中允许的数据类型、数据取值范围、输入信息和出错警告信息，完成数据验证设置。

在单元格中设置数据验证后，选定单元格时，单元格右下角将显示输入提示信息。如果单元格中输入的数据不是允许的数据类型或超出数据范围，系统将显示出错警告中设置的出错提示，并要求取消或重新输入数据。
要取消单元格中的数据验证，可以选定单元格后，单击“数据验证”按钮，在“数据验证”对话框“设置”选项卡中单击“全部清除”按钮。
要注意的是，数据验证只在输入数据时进行检查，对于已包含不满足验证条件数据的单元格，Excel不进行警告或提示。设置数据验证条件后，单击“数据”选项卡“数据工具”组“数据验证”按钮“圈释无效数据”命令可以标识无效数据。标识无效数据后，如果更正了无效输入，圆圈会自动消失。
数据验证后测习题
(1) 为了防止在单元格中输入不恰当的数据，应进行设置。
A. 选择性粘贴B. 条件格式
C. 套用表格格式D. 数据验证
(2) 设B2单元格中已有数值-5，在B1至B10单元格区域中设置数据验证条件为允许大于0的整数，则。
A. 弹出警告对话框，提示数据错误B. B2单元格数据显示为红色
C. B2单元格填充显示为红色D. 不出现任何提示
5.3设置表格格式





视频名称设置单元格格式二维码



主讲教师向华
视频地址


在Excel中，对单元格可以设置字体、对齐、填充、边框等各种格式，对表格外观进行修饰，使表格中的数据整齐、鲜明、美观。
5.3.1设置单元格格式
选定单元格或单元格区域后，单击“开始”选项卡“字体”组或“对齐方式”组右下角的对话框启动器，打开“设置单元格格式”对话框，在对话框中可以对单元格的字体、对齐方式、边框、填充等格式进行设置。
1. 字体
在“设置单元格格式”对话框“字体”选项卡中，可以对选定单元格的字体、字形、字号、下画线、颜色等属性进行设置。选定单元格区域后，单击“开始”选项卡“字体”组中的相应按钮，也可以快速设置字体相关属性。
2. 对齐方式
在Excel中，默认文本型单元格左对齐，数值、货币、日期、时间等类型单元格右对齐，逻辑值和错误值居中对齐。如果要修改单元格的对齐方式，可以在“开始”选项卡“对齐方式”组中设置对齐方式，也可以在“设置单元格格式”对话框的“对齐”选项卡中设置，如图5.11所示。


图5.11“设置单元格格式”对话框“对齐”选项卡


除了设置水平和垂直对齐方式，“对齐方式”组中还有“自动换行”“合并后居中”“方向”等按钮对单元格中文本进行显示控制，也可以选择“设置单元格格式”对话框的“对齐”选项卡中“文本控制”的相关复选框进行设置。

如果单元格中的数据长度超过了单元格宽度，而单元格列宽又不能调整时，单击“开始”选项卡“对齐方式”组中的“自动换行”按钮，使单元格中的数据根据单元格宽度自动换行。
选择相邻的单元格，单击“开始”选项卡“对齐方式”组中的“合并后居中”按钮，选定的单元格将合并为一个单元格，将合并区域中左上角单元格的内容显示在合并单元格中并居中显示。合并单元格时，如果选定区域中有多个单元格包含数据，系统将只保留左上角单元格的内容，合并单元格地址为合并区左上角单元格地址。
单击“合并后居中”按钮右侧的三角形，执行“取消单元格合并”命令，可以取消合并的单元格。
Excel单元格默认文字方向是从左向右。如果需要将文字方向设置为竖排或旋转一定角度，可以单击“对齐方式”组中的“方向”按钮，或在“设置单元格格式”对话框的“对齐”选项卡中“方向”选项中进行设置。
3. 边框与填充
在编辑单元格时，Excel默认在单元格之间显示灰色网格线，但这些网格线在打印时不会出现。为表格设置边框，可以使表格打印时有合适的边框线，也能使表格的显示更加清晰明了。
选定单元格或单元格区域后，单击“开始”选项卡“字体”组“边框”按钮后的三角形，在弹出的列表中选择相应命令。如果单击“边框”列表中的“其他边框”命令，将打开“单元格格式”对话框，进入“边框”选项卡，设置边框细节
，如图5.12所示。


图5.12“设置单元格格式”对话框“边框”选项卡


填充用来设置选定单元格或单元格区域的背景颜色，对表格的不同单元格区域填充不同颜色，能使单元格中的内容更加突出。
选定单元格或单元格区域后，单击“开始”选项卡“字体”组“填充”按钮后的三角形，可以在弹出的颜色板中选择填充的颜色。单击颜色板下方的“其他颜色”命令，还可以在“颜色”对话框中选择标准颜色或自定义颜色填充。
另外，在“设置单元格格式”对话框的“填充”选项卡中还可以设置单元格的填充效果、图案颜色和图案样式。
4. 行高与列宽
将鼠标指向表格行号之间或列标之间的分隔线，当光标变成带双向箭头的十字型时按下左键拖动，可以调整行高或列宽。当光标变成带双向箭头的十字型时双击左键，系统将根据单元格内容设置最合适的行高或列宽。
单击行号或列标选定一行或一列，在选定的行或列内
右击，选择快捷菜单中的“行高”或“列宽”命令，可以在行高或列宽对话框中设置行高或列宽的具体值。
如果选定多行或多列后设置行高或列宽，所有选定的行或列将采用相同的行高或列宽。

选定行、列或单元格后，单击“开始”选项卡“单元格”组中“格式”按钮，选择“行高”或“列宽”命令，也可以调整选定的行、列、单元格的行高和列宽。
在“格式”按钮中，还有一个“隐藏和取消隐藏”选项，单击其中的“隐藏和取消隐藏”相关命令，可以隐藏或显示选定的行、列或工作表。
5. 格式刷
Excel的格式刷功能和Word格式刷类似。对一个单元格区域设置好格式后，单击格式刷，十字光标左侧将出现格式刷标记。此时在要设置格式的单元格区域中按下并拖动鼠标，可以将已设定的格式应用到对应单元格区域中。格式刷实际是从选定单元格或区域复制格式到其他单元格区域中。
单击格式刷时，复制的格式只能应用一次。如果选定已设置格式的单元格或区域后，双击格式刷，再在多个单元格区域中拖动，可以将设置好的格式多次应用到单元格区域中。
设置单元格格式后测习题
(1) 在Excel的“设置单元格格式”对话框的“”选项卡中可以进行合并单元格操作。

A. 数字B. 对齐C. 字体D. 边框
(2) Excel默认在单元格周围显示的灰色网格线，网格线。
A. 也都必然有 B. 打印时有预览时无
C. 预览时有打印时无D. 经设置才会有
(3) 以下选项中不能使用格式刷复制的是。
A. 合并单元格B. 边框和填充
C. 数字类型D. 数据验证
5.3.2套用表格格式





视频名称套用表格格式二维码



主讲教师向华
视频地址

在Excel中，已经内置了很多表格样式，选定单元格区域，单击“开始”选项卡中的“套用表格格式”按钮，在弹出的“套用表格式”对话框中确认表数据来源区域，并选择表格是否包含标题行后，可以将选择的表格样式快速套用在数据区域中
，如图5.13所示。
套用表格样式后，选定的数据区域将转变为结构化表格，并在标题行上自动增加筛选按钮。选择表格中的任意一个单元格，工具栏中将显示表格工具“设计”选项卡。在“设计”选项卡中，可以设置表格的名称、表格显示样式等。 如果要取消筛选按钮，可以单击“设计”选项卡“表格样式选项”中的“筛选按钮”复选框，如图5.14所示。
套用表格格式后的表格是结构化二维表。结构化二维表中，单元格不能再合并，Excel自动为表格和表格中的每一列指定名称。表格的表名称可以在“设计”选项卡“表名称”文本框中修改，列名为每列标题行的名称。在公式中引用表格中的单元格时，公式将使用相应单元格所在的表名和列名的结构化引用，而不使用列标和行号。这种方式在大型工作簿中引用单元格更加方便。
如果要去除套用的表格格式，可以选定整个表格，单击“开始”选项卡“编辑”组中的“清除”按钮中的“清除格式”命令。这时仅仅是清除掉表格区域中的边框、填充等格式设置，表格仍然是结构化二维表。选定表格中的任意一个单元格，单击“设计”选项卡中的“转换为区域”命令，表格才转换为数据区域。



图5.13“套用表格样式”选项




图5.14“表格工具”选项


套用表格格式后测习题
(1) 套用表格格式后，选定其中的一个单元格区域，不能执行的操作是。
A. 修改填充颜色B. 设置字体
C. 设置边框D. 合并单元格
(2) 以下说法中错误的是。
A. 套用表格格式后，数据区域转换为结构化二维表
B. 套用表格格式后，表格中将自动添加筛选
C. 套用表格格式后，可以设置表格的名称
D. 清除已套用格式的表格的格式后，表格自动转换为数据区域
5.3.3条件格式





视频名称条件格式二维码



主讲教师向华
视频地址

条件格式用于帮助用户更加直观地查看和分析数据。在单元格中设置条件格式后，根据单元格的值采用不同的显示效果，可以帮助用户了解数据的对比情况或变化趋势，及时发现异常数据。

选定要设置条件格式的单元格区域后，单击“开始”选项卡“样式”组中的“条件格式”按钮，在条件格式命令中选择并设置条件格式规则后，满足条件的单元格将按指定格式显示，如图5.15所示。
Excel的条件规则包括突出显示单元格规则、项目选取规则、数据条、色阶和图标集等多种形式，使用时可以根据需要选择。突出显示单元格规则和项目选取规则能将符合条件规则的数据采用特殊形式显示，方便用户快速查看特殊数据。数据条、色阶和图

图5.15“条件格式”设置命令
标集规则可以使数据图形化、可视化，使数据显示更直观、清晰。
如果在同一单元格区域中，既设置了条件格式，又设置了单元格格式，则满足条件格式中条件的单元格按条件格式设置显示，不满足条件的单元格按单元格格式设置显示。

在条件格式按钮中，除了可以直接设置各类条件规则以外，还可以新建格式规则、清除规则和管理已有的规则。
1. 新建规则
选定单元格或单元格区域，单击“开始”选项卡“条件格式”按钮中的“新建规则”命令，在“新建格式规则”对话框中选择规则类型并编辑规则说明，可以添加条件规则，如图5.16所示。


图5.16“新建格式规则”对话框


2. 编辑规则
选定已设置条件格式的单元格或单元格区域，单击“开始”选项卡“条件格式”按钮中的“管理规则”命令，在“条件格式规则管理器”中选择要修改的规则，单击“编辑规则”按钮，可以在“编辑格式规则”对话框中修改格式规则，如图5.17所示。


图5.17“条件格式规则管理器”对话框


3. 清除规则
选定已设置条件格式的单元格或单元格区域，单击“开始”选项卡“条件格式”按钮中的“清除规则”，执行“清除所选单元格的规则”或“清除整个工作表的规则”命令，可以清除选定单元格或单元格区域的条件格式或整个工作表中的条件格式。
条件格式后测习题
(1) 某单元格中存放的数据为学生成绩80。设置单元格填充色为红色，再设置条件格式为大于60的单元格填充色为蓝色。则该单元格显示的颜色为。
A. 蓝色B. 红色C. 紫色D. 白色
(2) 如果要将学生成绩列中的前三名成绩加粗显示，需要使用条件格式中的。
A. 突出显示单元格规则B. 项目选取规则
C. 数据条D. 图标集
5.3.4页面设置





视频名称页面设置二维码



主讲教师向华
视频地址


如果制作完成的表格需要打印，可以在打印之前先对表格进行页面设置。

选定工作表，在“页面设置”选项卡“页面设置”组中单击“页边距”“纸张方向”“纸张大小”“打印区域”“打印标题”等按钮，可以直接进行相关页面设置。单击“页面设置”组右下角的对话框启动器，打开“页面设置”对话框，可以对页面、页边距、页眉/页脚、工作表等选项进行进一步设置。
在“页面设置”对话框的“页眉/页脚”选项卡中，单击“打印区域”右侧的“折叠对话框”按钮将对话框缩小，然后在工作表中拖动鼠标选择要打印的单元格区域，再单击对话框右侧的“展开对话框”按钮，选定的单元格区域将自动填写在打印区域后面的文本框中。确定后，选定单元格区域内的内容将打印。
如果要打印的表格内容比较多，需要分多页打印，通常在“页面设置”对话框“工作表”选项卡中设置顶端标题行和左端标题列，使表格的标题行和标题列能跨页显示，如图5.18所示。


图5.18“页面设置”对话框


在“页面设置”对话框的“页眉/页脚”选项卡中，单击“页眉”或“页脚”下拉列表，可以在固定的页眉页脚模板中选择页眉或页脚。单击“自定义页眉”或“自定义页脚”按钮，可以在“页眉”或“页脚”对话框的“左”“中”“右”编辑框中插入自定义的页眉或页脚，如图5.19所示。



图5.19“页眉”对话框


完成页面设置后，单击“页面设置”对话框中的“打印预览”按钮，或者单击“文件”选项卡“打印”命令，先预览打印效果后，再单击“打印”按钮打印表格。
页面设置后测习题
(1) 设置表格的页眉页脚时，不能在页眉、页脚中加入。
A. 页码和页数B. 指定单元格内容
C. 当前工作表名D. 当前工作簿名
(2) 如果一张表格打印时跨页，需要在每页表格的第一行显示标题行，应该。
A. 将标题行复制到每页的开头
B. 选定表格的第一行数据，设置标题行重复
C. 在“页面设置”中设置打印标题
D. 将表格的第一行数据设置为页眉
5.4编 辑 表 格
对表格的编辑指对工作表中的各种基本组成元素进行操作，包括在单元格中查找与替换数据，单元格、行、列的插入、移动、复制、删除、清除等。
5.4.1查找与替换





视频名称查找与替换二维码



主讲教师向华
视频地址

单击“开始”选项卡“编辑”组“查找和选择”按钮，选择“查找”或“替换”命令，打开“查找和替换”对话框，能快速在工作表中查找或替换指定数据，如图5.20所示。



图5.20“查找和替换”对话框


在进行查找和替换之前，如果在工作表中已选定单元格区域，查找和替换将在选定单元格区域中进行，操作从选定单元格区域中的左上角第一个单元格开始，按照从左到右、从上到下的顺序进行，直到选定区域的右下角最后一个单元格。
如果在查找和替换之前没有选定单元格区域，查找和替换默认在当前工作表中进行，操作从活动单元格开始，按照先行后列的顺序进行，查找到工作表的最后一个单元格后，再从A1单元格开始，直至回到活动单元格结束。
在进行查找和替换时，默认查找范围是当前工作表，搜索方式按行进行。在“查找和替换”对话框的选项中可以修改查找和替换默认设置。
查找与替换后测习题
(1) 在Excel中进行查找数据时，以下说法正确的是。

A. 查找只能按行进行
B. 查找不能跨工作表
C. 查找前必须先选定单元格区域，设定查找范围
D. 如果没有选定查找区域，查找将在整个当前工作表中进行
（2） 设B2、C2、E2、B3单元格的内容均为99，当前单元格D2的内容为66，在“查找和替换”对话框的“查找”选项卡中查找99，单击“查找下一个”按钮，当前单元格变为。
A. B2B. C2C. E2D. B3
5.4.2插入、删除与清除





视频名称插入、删除与清除二维码



主讲教师向华
视频地址

要在表格中插入或删除行、列和单元格，可以选定单元格后，单击“开始”选项卡“单元格”组中的“插入”或“删除”按钮，选择对应命令。单击“开始”选项卡“单元格”组中的“清除”按钮，则可以清除单元格的内容、格式等。
1. 插入
执行“插入工作表行”“插入工作表列”命令时，活动单元格的上方或左侧将插入一行或一列。
执行“插入单元格”命令时，需要在“插入”对话框中的“活动单元格右移”“活动单元格下移”“整行”“整列”四种插入方式中选择一种，如图5.21所示。插入单元格后，选定单元格及其右侧或下方的所有单元格数据都将移动。
另外，右击单元格，在快捷菜单中选择“插入”命令，也将打开“插入”对话框，可以选择插入单元格、整行或整列。
2. 删除
删除是将选定的单元格从工作表中去除。删除单元格后，其右侧或下方的单元格将填补单元格删除后的位置。
执行“删除工作表行”或“删除工作表列”命令时，当前选定的行、列，或单元格所在的行、列被删除，下方的行号或右边的列标自动填补删除的行列并重新依次编号。
执行“删除单元格”命令时，需要在“删除”对话框中的“右侧单元格左移”“下方单元格上移”“整行”“整列”四种删除方式中选择一种，如图5.22所示。删除选定单元格后，其右侧或下方的所有单元格数据都将移动。而不论在工作表中插入或删除了多少单元格，工作表的总行列数保持不变。


图5.21“插入”对话框




图5.22“删除”对话框


3. 清除
清除用于去除选定单元格区域中的内容、格式、批注、超链接等。清除各种表格元素后，单元格仍保留在工作表中。
选定单元格或单元格区域后，单击“开始”选项卡“编辑”组中的“清除”按钮，选择其中一个清除命令，即可清除对应内容。
选定单元格或单元格区域后，按下Backspace或Del键，可以清除选定单元格内容。
插入、删除与清除后测习题
(1) 选定单元格后，按下Del或Backspace键，相当于执行操作。
A. 删除单元格 B. 清除内容C. 清除格式D. 清除全部
(2) 选定某单元格后，以下操作中可能使表格中数据错位的操作是。
A. 删除单元格B. 删除行C. 清除内容D. 清除全部
5.4.3单元格移动、复制与选择性粘贴





视频名称单元格移动、复制与选择性粘贴二维码



主讲教师向华
视频地址

在Excel中，单元格的移动和复制也是通过剪贴板进行，剪贴板中可以存放最近24次复制的内容。

选定单元格或单元格区域后，在“开始”选项卡“剪贴板”组中执行“复制”或“剪切”命令，选定单元格的内容将进入剪贴板。选定目标单元格后，执行“粘贴”命令，最近一次“复制”或“剪切”的内容将粘贴到目标单元格中。在进行移动或复制时，如果目标区域中已有数据，粘贴进目标区域的数据将覆盖原有数据。复制完成后，按Esc键或双击任一单


图5.23“选择性粘贴”对话框
元格可取消源数据区域上的动态虚线框。

Excel中，默认的复制、粘贴操作会将源单元格或区域中的所有内容，包括数值、格式、公式、批注、数据验证等都粘贴到目标单元格中。如果只需要复制单元格中的部分元素，或在复制时对数据进行一些变换，可以进行选择性粘贴。
复制单元格或单元格区域后，选定目标单元格，在“开始”选项卡“剪贴板”组中单击“粘贴”按钮下方的三角形，执行“选择性粘贴”命令，在“选择性粘贴”对话框中选择要粘贴的内容完成粘贴，如图5.23所示。

单元格移动、复制与选择性粘贴后测习题
(1) 配合Ctrl键选定A1、B2、C3三个单元格，执行复制操作，则。
A. A1单元格复制进剪贴板
B. C3单元格复制进剪贴板
C. A1、B2、C3三个单元格复制进剪贴板
D. 错误，不能对多重选择区域进行复制
(2) 选定单元格后按下Ctrl+C组合键，再选择目标单元格，按下Ctrl+V组合键，相当于粘贴源单元格的。

A. 全部B. 数值C. 格式D. 批注
5.5公式与函数
在处理电子表格时，经常要对表格中的数据进行计算。Excel具有强大的计算能力，使用公式和函数，可以对单元格中的数据进行复杂的计算，快速得到计算结果。
5.5.1公式的基本概念





视频名称公式的基本概念二维码



主讲教师向华
视频地址



图5.24公式基本组成

Excel使用公式进行计算。公式以等号开头，由运算符、常量、单元格引用和函数组成，如图5.24所示。在单元格中正确输入公式后，Excel将自动进行计算，并将计算结果显示在单元格中。
1. 运算符
运算符的作用是对公式中的各元素进行运算。公式中的运算符包括算术运算符、比较运算符、文本运算符和引用运算符。
算术运算符用来完成基本的算术运算，包括+、-、*、/、^(乘方)。
比较运算符用来比较两个值，比较结果为逻辑值TRUE或FALSE。比较运算符包括=、
>、<、>=、<=、<>。
文本运算符只有&，用来将两个文本字符串连接成一段文本。例如公式="butter"&"fly"的结果为"butterfly"。
引用运算符用来指示运算数据所在的区域，有
冒号（:）、逗号（，）和空格三种。
(1) “:”(冒号)为区域运算符，表示以冒号两边两个单元格为对角线构成的矩形区域中的所有单元格。例如“B2:D3”表示以B2为左上角、D3为右下角的矩形区域中的6个单元格。
(2) “，”(逗号)为联合运算符，表示将多个引用合并后的所有单元格。例如“B5:C7，C6:D8”表示“B5:C7”区域和“C6:D8”区域中的所有单元格(重叠部分计算两次)。


图5.25三种引用运算符对应区域

(3) (空格)为交集运算符，表示两个单元格区域的共有部分的所有单元格。例如“F5:G7 G6:H8”表示“F5:G7”区域和“G6:H8”交叉部分即“G6:G7”区域中的所有单元格，如图5.25所示。
在Excel公式中，运算符同样有优先级，运算符优先级从高到低依次为引用运算符、算术运算符、文本运算符、比较运算符，如图5.26所示。如果公式中包含相同优先级的运算符，则自左向右进行计算。要改变运算的优先级，需要使用圆括号将要先计算的部分括起来。
2. 常量
常量是在公式中始终不变的量，是不经过计算直接具有的值。例如如图5.24所示公式中的0.5、10。对于数值型常量，在公式中直接输入数值。对于字符常量，在公式中需要使用英文双引号将字符括起来，例如在公式="butter"&"fly"中，"butter" "fly"两个字符串必须加双引号，表示是两个字符常量，否则单元格中将显示“#NAME?”(无效名称)错误。


图5.26运算符优先级


3. 单元格引用
如果要在公式中对一些可能变化的数据进行计算，可以将数据放在单元格中，在公式中引用这些单元格进行计算。当引用单元格中的数据发生变化时，公式的运算结果会自动更新。
要引用公式所在的工作表内的单元格，可以直接使用单元格地址和引用运算符表示单元格或单元格区域。例如“B3”“A2:C3”。要引用整行或整列，可以使用行号或列标。例如“3:5”表示第3行到第5行的全部单元格，“A:C”表示A列到C列的全部单元格。
要引用同一工作簿其他工作表中的单元格或单元格区域，需要在单元格引用前面加上工作表名，并用“!”将工作表名与单元格引用隔开。例如“工资!A2”表示工资工作表中的A2单元格。
要引用其他工作簿中的数据，则需要在“工作表名!单元格引用”前面再加上“［工作簿文件名］”。例如“［职工.xlsx］工资!$A$1”表示职工工作簿工资工作表中的A1单元格。
4. 函数
函数是预定义的公式，通过调用参数来执行计算。Excel内置十二大类函数，为用户提供各种计算功能。
函数由函数名称和参数组成，参数必须写在一对小括号中。一个函数可能包括多个参数，各参数之间使用逗号隔开。函数参数可以是常量、单元格或单元格区域、公式或其他函数。
公式的基本概念后测习题
(1) Excel中,“<>”运算符表示。
A. 小于或大于B. 不等于C. 不小于D. 不大于
(2) 在一个常规型单元格中输入： =12>24，确认后，此单元格显示的内容为。

A. FALSEB. =12>24C. TRUED. 12>24
(3) 在一个常规型单元格输入： ="12"&"34"，确认后，此单元格显示的内容为。
A. 46B. 12+34C. 1234D. ="12"&"34"
5.5.2公式的输入与编辑





视频名称公式的输入和编辑二维码



主讲教师向华
视频地址

在单元格中输入公式的步骤如下： 
(1) 选定要输入公式的单元格。
(2) 输入等号“=”，表示单元格中输入的是公式，确认输入后系统会自动对公式进行计算。
(3) 输入公式内容。对公式中包含的单元格或单元格区域的引用，可以直接单击要引用的单元格或用鼠标拖动选定单元格区域，也可以直接输入。
(4) 公式输入完毕，单击编辑栏左侧的“输入”按钮或按Enter键确认。确认输入后，公式单元格中显示计算结果，编辑栏中显示公式。
如果要修改单元格中的公式，可以单击选定单元格，在编辑栏中修改公式。也可以双击单元格，进入单元格编辑状态，对其中的公式进行修改。
注意： 
(1) 单元格的内容为公式时，其显示的结果是经过系统计算得到的。在默认设置下，每次打开工作簿或者单元格中的数据发生变化时，公式都会自动重新计算，并将结果显示在单元格中。如果要进行手动计算，可以在“公式”选项卡的“计算”组中进行相关设置。
(2) 公式的计算精度为15个有效数字，显示计算结果时，按照单元格数字类型的设置显示保留的小数位。

公式输入与编辑后测习题
(1) 设当前工作表的C2单元格已完成了公式的输入，下列说法中错误的是。
A. 未选定C2时，C2显示的是计算结果
B. 单击C2时，C2显示的是计算结果
C. 双击C2时，C2显示的是计算结果
D. 双击C2时，C2显示的是公式
(2) 在单元格中输入，可以计算A1和A2单元格的和。
A. ="A1"+"A2"B. "A1"+"A2"
C. =A1+A2D. A1+A2
5.5.3单元格引用方式





视频名称单元格引用方式二维码



主讲教师向华
视频地址


在公式中引用单元格时，有三种引用方式： 相对引用，绝对引用和混合引用。
1. 相对引用

相对引用直接使用列标和行号对单元格进行引用，始终保持公式所在单元格和被引用单元格在行和列上的相对位置不变。如果将公式所在单元格复制到其他位置，相对引用的单元格位置也随之发生改变。将公式
填充到其他单元格时，公式中引用的单元格也会随填充单元格位置的变化自动调整。在默认情况下，公式采用相对引用方式。
2. 绝对引用
在绝对引用中，列标和行号前面分别添加一个$符号，表示引用单元格使用的是特定的列号和行号。公式复制或填充到其他位置时，绝对引用的单元格也始终保持不变。
3. 混合引用
混合引用指在引用单元格时，列标和行号中只有一个前面使用了$符号，也就是绝对引用和相对引用两种方式混合使用。混合引用又分为相对引用列绝对引用行(列标$行号)，和绝对引用列相对引用行($列标行号)两种情况。当使用混合引用单元格的公式复制或填充到另一个单元格时，相对引用的列标或行号会自动发生变化，而绝对引用的列标或行号保持不变。
4. 切换引用方式
在输入公式时，要引用单元格或单元格区域，一般使用鼠标单击或拖动选取，单元格默认采用相对引用。要修改公式中单元格的引用方式，可以在引用单元格的行号或列标前面输入$符号。也可在编辑栏中或在单元格编辑状态将光标定位在引用单元格，按下F4键，单元格引用方式将在相对引用、绝对引用、绝对引用行相对引用列、绝对引用列相对引用行四种引用方式中轮流切换。
5. 使用名称作绝对引用
对于某个被引用的单元格或单元格区域，如果公式中要采用绝对引用，可以在输入公式之前先选定该单元格或单元格区域，在名称框中为该单元格或单元格区域命名，命名后在公式中直接使用命名的名称对该单元格或单元格区域进行绝对引用。
6. 单元格的引用特性
无论是绝对引用，还是相对引用，都有两个十分有用的特性。
(1) 引用对象跟踪特性。指无论引用对象移动到什么位置，都仍然是引用对象。
(2) 引用者可移动特性。指无论将包含引用公式的单元格移动到什么位置，公式都不会改变。
这两个特性保证了计算中的因果关系不会因为单元格位置的变化而产生错误。

单元格引用后测习题
(1) Excel中,若单元格C1中公式为=A1+B2,将其复制到单元格E5,则E5中的公式是。

A. =A1+B2B. =C5+D6C. =C3+D4D. =E5+F6
(2) 在Excel中,位于工作表第8行和H列相交的单元格的绝对地址表示为。
A. $8$HB. 8$HC. H$8D. $H$8
(3) 以下单元格地址中不正确的是。
A. C$66B. $C66C. C6$6D. $C$66
(4) 设一个工作表的A2:B9单元格区域中，每一行依次记载的是一种商品的品名、原价，在C2单元格采用下列公式之一，求第二行的商品的八折价，其中不能复制到C3至C9单元格用以求其他商品的八折价。
A. $B2*80%B. B$2*80%C. B2*80%D. B2*0.8
5.5.4常用函数





视频名称常用函数二维码



主讲教师向华
视频地址

在Excel函数库中内置数学和三角函数、文本、日期和时间、查找与引用、财务、统计、逻辑等十二大类函数，可以完成各种常用计算。
Excel函数由函数名称和参数组成，参数必须写在一对小括号中，格式如下： 

函数名称(参数1，［参数2］，…)

一个函数可能包括多个参数，其中用［］括起来的参数是可选参数。各参数之间使用逗号隔开。参数可以是常量、单元格或单元格区域、公式或其他函数。
要使用函数进行计算，可以在单元格或编辑栏中编辑公式，直接输入函数。在公式中输入函数名时，单元格或编辑栏下方将提示相关函数名及参数列表。
单击“公式”选项卡“函数库”组中的“插入函数”按钮，在如图5.27所示“插入函数”对话框中选择函数类别和函数，单击“确定”按钮后在“函数参数”对话框中输入各个参数，如图5.28所示，再单击“确定”按钮即可完成函数输入。单击“公式”选项卡“函数库”组中的各个函数类别按钮，选择相应函数，也可以直接进入“函数参数”对话框设置参数，完成函数输入。



图5.27“插入函数”对话框




图5.28“函数参数”对话框


在“函数参数”对话框中输入函数参数时，可以单击函数参数文本框右侧的“折叠对话框”按钮，到工作表中选择参数所在的单元格或单元格区域，选择完毕后单击文本框右侧的“展开对话框”按钮，返回“函数参数”对话框，单元格或单元格区域地址将自动填入参数文本框中。
在“插入函数”对话框中选择函数时，“选择函数”列表框下方将显示所选函数的功能。
“插入函数”对话框底部有“有关该函数的帮助”超链接，单击超链接可以查看对应函数的帮助。
在“函数参数”对话框中，在每个参数文本框中输入参数时，参数文本框右侧将显示参数的值，并在所有参数下方显示函数的结果、功能，以及当前参数的作用。单击对话框左下角的“有关该函数的帮助”超链接，也可以查看对应函数的帮助。

初学者可以利用“插入函数”和“函数参数”两个对话框的提示和帮助输入函数，熟悉函数用法后，再直接在单元格或编辑栏中输入函数。
在“公式”选项卡“函数库”组中有一个“自动求和”按钮，单击按钮上的三角形，可以选择“求和”“求平均值”“计数”“最大值”“最小值”命令。这些命令对应常用的五个函数SUM、AVERAGE、COUNT、MAX、MIN。
1. SUM求和

格式： SUM(number1, ［number2］, ...)
功能： 计算各参数数值的和。
参数： number1必需。参与求和的第一个数字、单元格或单元格区域。
 number2可选。参与求和的第二个数字、单元格或单元格区域。最多可以指定255个参数。
例： SUM(D2:D5,F2:F5)表示对从D2到D5以及从F2到F5所有单元格求和。
说明： 
(1) SUM函数可以对数字、货币、会计专用、百分比、分数、科学计数类型数据以及常规类型中的数值数据求和。如果参数中有文本常量，例如SUM(1, "A")，系统将显示#VALUE!值错误。
(2) 如果SUM函数参数引用的单元格或单元格区域中有文本数据或空单元格，文本数据和空单元格在求和时将被忽略，只计算数字单元格的和。
2. AVERAGE求平均值
格式： AVERAGE(number1, ［number2］,…)
功能： 计算所有参数的平均值。
参数： number1必需。参与求平均值的第一个数字、单元格或单元格区域。
number2可选。参与求平均值的第二个数字、单元格或单元格区域。最多可以指定255个参数。
例： 如果A1:A5单元格区域的数值分别为5，6，7，8，9，那么AVERAGE(A1:A5)的结果为7。
说明： 
(1) AVERAGE函数可以对数字、货币、会计专用、百分比、分数、科学记数类型数据以及常规类型中的数值数据求平均值。如果参数中有文本常量，例如AVERAGE(1, "A")，系统将显示#VALUE!值错误。
(2) 如果AVERAGE函数参数引用的单元格或单元格区域中有文本数据或空单元格，文本数据和空单元格不参与求平均值。如果参与求平均值的所有单元格中都是文本或空单元格，求平均值的结果将显示#DIV/0!除零错。
3. COUNT计数
格式： COUNT(value1, ［value2］, …)
功能： 计算包含数字的单元格个数以及参数列表中数字的个数。
参数： value1 必需，表示参与计数的第一个数字、单元格或单元格区域。
value2可选。参与计数的第二个数字、单元格或单元格区域。最多可以指定255个参数。
例： COUNT(1, 2, 3)的结果为3。
如果A1:A3单元格区域的数值分别为5，6，7，那么COUNT(A1:A3)的结果为3。
说明： 
(1) 如果参数为数字、日期、逻辑值，或由数字组成的文本，都将计入计数值。例如COUNT(1, 2, "3", TRUE)的结果为4。

(2) 如果参数为单元格或单元格区域，单元格中的空单元格、逻辑值、文本及错误值都不计入计数值。例如，如果单元格A1:A4中的数据分别为1，2，"3"，TRUE，COUNT(A1:A4)的结果为2。
4. MAX/MIN求最大值/最小值
格式： MAX (number1, ［number2］, …) / MIN (number1, ［number2］, …)
功能： 返回所有参数中的最大值/最小值。
参数： number1 必需。参与求最大值/最小值的第一个数字、单元格或单元格区域。
number2可选。参与求最大值/最小值的第二个数字、单元格或单元格区域。最多可以指定255个参数。
例： 如果A1:A5单元格区域的数值分别为5，6，7，8，9，那么MAX(A1:A5) 的结果为9。
说明： 
(1) 如果参数为数字、逻辑值、由数字组成的文本，都将参与计算，逻辑值TRUE作为1处理，FALSE作为0处理。例如MAX(-2, "3", TRUE)的结果为3。
(2) 如果参数中有非数字组成的文本，例如MAX(-2, "ABC", TRUE)，结果为#VALUE!值错误。
(3) 如果参数为单元格或单元格区域，单元格中的逻辑值、文本均不参与计算。例如，如果单元格A1:A3中的数据分别为-2，
"3"，TRUE，MAX(A1:A3)的结果为-2。
(4) 如果所有参数对应的单元格或单元格区域中都是逻辑值、文本或空单元格，MAX的结果为0。

常用函数后测习题
(1) 单击Excel功能区“”选项卡“函数库”组的“插入函数”按钮，将弹出“插入函数”对话框，从中查到Excel的全部函数。

A. 开始B. 插入
C. 公式D. 数据
(2) 求C2:E7与D5:F9相并的单元格区域内数值型数据的个数，采用相对地址引用,其函数应该表示为。
A. SUM(C2:E7 D5:F9)B. COUNT(C2:E7 D5:F9)
C. SUM(C2:E7,D5:F9)D. COUNT(C2:E7,D5:F9)
(3) 已知E2至E4单元格中存放了3位学生的考试成绩，以下公式中不能计算所有学生平均分的是。
A. =AVERAGE(E2:E4)
B. =SUM(E2:E4)/COUNT(E2:E4)
C. =AVERAGE("E2:E4")
D. =AVERAGE(E2,E3,E4)

5.5.5数学和三角函数





视频名称数学和三角函数二维码



主讲教师向华
视频地址

数学和三角函数用来进行各种数学和三角计算。
1. INT取整
格式： INT(number)
功能： 将数字向下舍入到最接近的整数。
参数： number必需。要进行向下舍入取整的实数或单元格。
例： INT(8.9) 的结果为8； INT(-8.1)的结果为-9。
说明： 
(1) 将参数加上0.5后再取整可以对小数点右侧第一位小数四舍五入。
(2) 参数不能是单元格区域，也不能是文本，否则将显示#VALUE!值错误。

2. ROUND四舍五入
格式： ROUND(number, num_digits)
功能： 将数字四舍五入到指定的位数。
参数： number必需。要进行四舍五入的实数或单元格。
num_digits 必需。四舍五入要保留的位数。
如果num_digits大于0，将数字四舍五入到指定的小数位数。
如果num_digits等于0，将数字四舍五入到整数。
如果num_digits小于0，将数字四舍五入到小数点左边相应的位数。
例： ROUND(123.456, 1)的结果为123.5； ROUND(123.456, 0)的结果为123； ROUND(123.456, -1)的结果为120。
说明： 参数不能是单元格区域，也不能是文本，否则将显示#VALUE!值错误。
3. RAND产生随机实数
格式： RAND()
功能： 产生一个大于等于0且小于1的平均分布的随机实数。
参数： 无
例： 设a，b为两个实数，a<b，
RAND()*(b-a)+a将产生［a, b)之间的随机实数。
设m，n为两个整数，m<n，
ZNT（RAND（）*（n-m+1)+m）
将产生［m, n］之间的随机整数。
说明： 
(1) RAND函数在每次计算工作表时都会产生一个新的随机实数，精度为15位数字。
(2) 虽然RAND函数没有参数，但是使用时函数名后面仍然要加上()，否则将显示#NAME?无效名称错误。
4. RANDBETWEEN产生指定范围随机整数
格式： RANDBETWEEN(bottom, top)
功能： 产生位于指定最小值、最大值之间的随机整数。
参数： bottom必需。指定返回随机整数的最小值。
top必需。指定返回随机整数的最大值。
例： RANDBETWEEN(1, 10)将产生一个［1, 10］之间的随机整数。
说明： RANDBETWEEN函数在每次计算工作表时都会产生一个新的随机整数。
5. MOD取余
格式： MOD(number, divisor)
功能： 返回两数相除的余数。 结果的符号与除数相同。
参数： number必需。 要计算余数的被除数。
divisor必需。 要计算余数的除数。
例： MOD(10, 7)的结果为3，MOD(10, -7)的结果为-4。
说明： divisor除数不能为0，否则将显示#DIV/0!除零错。
数学和三角函数后测习题
(1) 以下公式中，能判断单元格C2的值是否是奇数的公式是。

A. =MOD(C2,2)=1 B. =MOD(2,C2)=1
C. =C2/2=1 D. =INT(C2/2)=C2/2
(2) 以下函数中，能与函数RANDBETWEEN(11,30)产生相同范围随机整数的函数是。
A. =INT(RAND(20)+11)B. =INT(RAND()*20+11)
C. =INT(RAND())*20+11D. =INT(RAND()*20)
5.5.6文本函数





视频名称文本函数二维码



主讲教师向华
视频地址

文本函数用于对字符常量或单元格中的文本进行处理，例如取文本中的子字符串，求文本中的字符个数等。
在使用文本函数处理字符串时应注意，Excel对字符计数时，一个英文字母、数字、符号或汉字的字符个数都是1。
1. LEFT提取左边字符
格式： LEFT(text, ［num_chars］)
功能： 从字符串的第一个字符开始返回指定个数的字符。
参数： text必需。要提取的字符的字符串。
num_chars可选。要提取的字符个数。
例： LEFT("abcde", 3)结果为"abc"，LEFT("大学计算机", 2)结果为"大学"。
说明： num_chars应大于等于0。如果num_chars大于字符串长度，LEFT函数将返回整个字符串。如果省略num_chars，默认返回字符串左边第一个字符。
2. RIGHT提取右边字符
格式： RIGHT(text, ［num_chars］)
功能： 从字符串的最后一个字符开始向左返回指定个数的字符。
参数： text必需。要提取的字符的字符串。
num_chars可选。要提取的字符个数。
例： RIGHT("abcde", 3)结果为"cde"，RIGHT("大学计算机", 3)结果为"计算机"。
说明： num_chars应大于等于0。如果num_chars大于字符串长度，RIGHT函数将返回整个字符串。如果省略num_chars，默认返回字符串右边第一个字符。
3. MID提取中间字符
格式： MID(text, start_num, num_chars)
功能： 返回字符串中从指定位置开始的特定数目的字符。
参数： text必需。要提取字符的字符串。
start_num 必需。要提取的第一个字符在字符串中的位置。
num_chars必需。要提取的字符个数。
例： MID("abcde", 3, 2)结果为"cd"，MID("大学计算机", 3, 2)结果为"计算"。
说明： 
(1) start_num应大于等于0。如果start_num大于字符串长度，MID函数返回""(空字符串)。
(2) num_chars应大于等于0。如果start_num加num_chars大于字符串长度，MID函数返回从start_num开始到字符串结尾的所有字符。
4. LEN统计字符个数
格式： LEN(text)
功能： 返回字符串中的字符个数。
参数： text必需。要计算字符个数的字符串。
例： LEN("abcde")结果为5，LEN("大学计算机")结果为5。
说明： 
(1) 空格也是字符，也将计入字符个数。例如LEN("大学 计算机")结果为6。
(2) 空字符串""中没有字符，LEN("")结果为0。
文本函数后测习题
(1) 已知身份证号的第7~10位是出生年份，要从B2单元格存放的身份证号中提取出生年份，应使用的函数是。

A. MID(B2,7,10)B. MID(B2,7,4)
C. MID(B2,7:10)D. MID(B2,7:4)
(2) 下列函数中与函数RIGHT("Excel函数",2)结果相同的是。
A. LEFT("Excel函数",2)B. MID("Excel函数",6,2)
C. LEN("Excel函数")D. MID("Excel函数",2)
5.5.7日期函数与时间函数





视频名称日期函数与时间函数二维码



主讲教师向华
视频地址


日期与时间函数用于处理日期和时间数据。在Excel中，日期和时间以序列号的形式存储。序列号中，整数部分表示日期，小数部分表示时间。默认1900年1月1日0时0分0秒的序列号为1，2021年1月1日12时0分0秒的序列号为44197.5，表示距1900年1月1日有44197天，距0时0分0秒12小时。
在单元格中输入日期时间数据后，如果将单元格格式设置为常规或数字类型，将显示序列号，如果将单元格格式设置为日期或时间类型，将按日期或时间格式显示结果。
1. TODAY取当前日期
格式： TODAY()
功能： 返回当前日期。
参数： 无。
例： TODAY()+3结果为从当前日期起，3天以后的日期。
说明： 
(1) 当前日期和时间指计算机的系统时钟当前的日期和时间。
(2) TODAY函数的结果将在打开工作簿或重新计算工作表时自动更新。

2. NOW取当前日期和时间
格式： NOW()
功能： 返回当前日期和时间。
参数： 无。
例： NOW()+0.5结果为从当前时间起，12小时以后的日期和时间。
说明： 
NOW函数的结果将在打开工作簿或重新计算工作表时自动更新。
3. YEAR取日期所在年份
格式： YEAR(serial_number)
功能： 返回日期对应的年份。
参数： serial_number 必需。要计算年份的日期。
例： 如果A1单元格中存放的是日期2021年9月1日，YEAR(A1)的结果为2021。
说明： 
(1) serial_number 应该是日期类型数据或序列号，例如日期型单元格，结果为日期的函数等。
(2) YEAR函数的结果为数值。假设C2单元格存放的是出生日期，通常使用YEAR(TODAY())-YEAR(C2)来计算年龄。
日期时间函数后测习题
(1) 以下公式中错误的是。

A. =YEAR(TODAY())B. =YEAR("2020/1/1")
C. =YEAR(NOW())D. =YEAR(2020/1/1)
(2) 设F2单元格存放的是职工的入职日期，能计算该职工工龄的公式是。
A. =TODAY()-F2
B. =YEAR(F2)-YEAR(TODAY())
C. =F2-TODAY()
D. =YEAR(TODAY())-YEAR(F2)
5.5.8逻辑函数





视频名称逻辑函数二维码



主讲教师向华
视频地址



逻辑函数用于进行各种逻辑判断或逻辑运算。

1. IF条件判断函数
格式： IF(logical_test, value_if_true, ［value_if_false］)
功能： 对值进行逻辑测试，根据逻辑测试值的真假返回不同的结果。
参数： logical_test 必需。要进行测试的条件。
value_if_true 必需。logical_test的结果为TRUE时，函数返回的值。
value_if_false 可选。logical_test的结果为FALSE时，函数返回的值。
例： 设E2单元格有学生成绩，函数IF(E2>=60, "及格", "不及格")表示如果E2单元格的值大于或等于60，则显示"及格"，否则显示为"不及格"，见图5.29。



图5.29使用IF函数判断是否及格


说明： 
(1) logical_test测试条件的结果应该是TRUE或FALSE。如果测试条件为数值，系统将0作为FALSE，非0值作为TRUE。如果测试条件的结果为文本，将显示#VALUE!值错误。


图5.30使用IF函数嵌套判断成绩等级


(2) 在IF函数的value_if_true和value_if_false参数中可以嵌套IF函数，实现多分支判断。例如IF(B2>=85, "优秀", IF(B2>=60, "及格", "不及格"))可以将学生成绩分成“优秀”“及格”“不及格”三档，见图5.30。Excel最多允许嵌套64级IF函数。但IF嵌套层数过多会使公式判断逻辑复杂，也容易出错。对于分支较多的条件判断，建议使用VLOOKUP或其他函数实现。

2. AND与函数
格式： AND(logical1, ［logical2］, …)
功能： 判断所有参数中条件判断的结果是否均为TRUE，如果是，返回TRUE，否则返回FALSE。
参数： logical1必需。第一个要判断的条件。
logical2可选。其他要进行判断的条件。最多可以指定255个条件。
例： 设B2单元格的值为0.3，函数AND(B2>0, B2<1)表示判断B2单元格的值是否在0到1之间，结果为TRUE。
说明： 
(1) logical1、logical2等测试条件返回的结果应该为逻辑值TRUE或FALSE。如果测试条件的结果为文本，将显示#VALUE!值错误。
(2) AND函数经常嵌套在IF函数的logical_test测试条件参数中，先计算多个条件进行“与”运算的结果，再进行条件判断。

3. OR或函数
格式： OR(logical1, ［logical2］, …)
功能： 判断所有参数中是否有条件判断的结果为TRUE，如果有，返回TRUE，否则返回FALSE。
参数： logical1必需。第一个要判断的条件。
logical2可选。其他要进行判断的条件。最多可以指定255个条件。
例： 设B2单元格的值为0.3，函数OR(B2>1, B2<0)表示判断B2单元格的值是否小于0或大于1，结果为FALSE。
说明： 
(1) logical1、logical2等测试条件返回的结果应该为逻辑值TRUE或FALSE。如果测试条件的结果为文本，将显示#VALUE!值错误。
(2) OR函数经常嵌套在IF函数的logical_test测试条件参数中，先计算多个条件进行“或”运算的结果，再进行条件判断。

逻辑函数后测习题
(1) 已知B3单元格的数据值为50，C3的内容为“=IF(B3>40,B3*0.8,B3*1.2)”,该公式运算后将在C3单元格显示。

A. 40B. 50C. 60D. TRUE
(2) 已知B2单元格存放的是某同学数学成绩，C2单元格存放的是英语成绩。要判断该同学两门课是否都及格，应采用的公式是。
A. =AND(B2>=60,C2>=60)B. =OR(B2>=60,C2>=60)
C. =B2>=60 AND C2>=60D. =B2>=60 OR C2>=60
(3) 已知B2单元格存放的是某同学数学成绩，C2单元格存放的是英语成绩。要判断该同学是否有课程不及格，应采用的公式是。
A. =AND(B2<60,C2<60)B. =OR(B2<60,C2<60)
C. =AND(B2,C2)<60D. =OR(B2,C2)<60
5.5.9统计函数





视频名称统计函数二维码



主讲教师向华
视频地址

统计函数用于对数据进行统计分析。
1. RANK.EQ 排位函数
格式： RANK.EQ(number, ref, ［order］)
功能： 返回一个数值在指定单元格区域中的排位。如果单元格区域中有多个值相同，这些值取最高排位。
参数： number必需。要计算排位的数字。
ref必需。参与排序的数据所在的单元格区域。
order可选。排位方式，0表示降序，1表示升序。省略时默认排位方式为降序。
例： 设A1:A5单元格区域中的值依次为81、82、83、83、84，函数RANK.EQ(82，A1:A5)的结果为4，函数RANK.EQ(A3, A1:A5, 1)的结果为3。
说明： 
(1) 在计算排位时，number应该是数字，如果number为文本，将显示#VALUE!值错误。如果number的值在ref区域中不存在，将显示#N/A值不可用错误。
(2) ref单元格区域中如果有文本，文本将被忽略。
2. COUNTIF 条件计数函数
格式： COUNTIF(range, criteria)
功能： 统计指定区域中满足某个条件的单元格的数量。
参数： range必需。要按条件计数的单元格区域。
criteria必需。进行计数的单元要满足的条件。
例： 设D2:D17单元格区域中存放的是学生的专业，F2:F17单元格区域存放的是学生的听力成绩，函数COUNTIF(D2:D17, "计算机")用来统计计算机专业的学生人数。函数COUNTIF(F2:F17, ">=60")用来统计听力及格的学生人数。
说明： 
(1) criteria 条件要使用""括起来。如果要在条件中表示等于一个值，直接在""中写要等于的值。如果要表示大于、小于某个值等条件，则应写成">值"或"<值"。

(2) COUNTIF函数只能统计满足一个条件的单元格个数。如果要统计满足多个条件的单元格个数，应使用COUNTIFS函数。

3. COUNTIFS 多条件计数函数
格式： COUNTIFS(criteria_range1, criteria1, ［criteria_range2, criteria2］, 
…)
功能： 统计跨多个区域的单元格中，满足所有条件的次数。

参数： criteria_range1 必需。要计数的单元格区域。
criteria1必需。进行计数的单元要满足的条件。
criteria_range2，criteria2 可选。附加的条件计数区域及其关联条件。最多可以设置127个条件区域及关联条件。
例： 设D2:D17单元格区域中存放的是学生的专业，G2:G17单元格区域存放的是学生口语成绩，函数COUNTIFS(D2:D17，"计算机"，G2:G17，">=60")用来统计计算机专业口语及格的学生人数。
说明： 
(1) 多条件计数时，多个条件之间是“与”关系，即同一行的所有处于条件区域的单元格都满足指定条件，才进行计数。
(2) 附加条件区域和第一个条件区域可以不相邻，但必须和第一个条件区域具有相同的行数和列数。
4. SUMIF条件求和函数
格式： SUMIF(range, criteria, ［sum_range］)
功能： 对指定区域中满足某个条件的值求和。
参数： range必需。要按条件求和的单元格区域。
criteria必需。进行求和的单元格要满足的条件。
sum_range可选。条件区域和求和区域不一致时指定求和区域。如果省略，表示求和区域和条件区域相同。

例： 设D2:D17单元格区域中存放的是学生的专业，F2:F17单元格区域存放的是学生的听力成绩，函数SUMIF(D2:D17, "计算机", F2:F17)用来统计计算机专业学生的听力总分。函数SUMIF(F2:F17, ">=60")用来统计听力及格学生的总分。
说明： 
(1) criteria条件要使用""括起来。如果要在条件中表示等于一个值，直接在""中写要等于的值。如果要表示大于、小于某个值等条件，则应写成">值"或"<值"。
(2) sum_range 求和区域的大小和形状应该和range条件区域相同。
(3) SUMIF函数只能统计满足一个条件的单元格之和。如果要统计满足多个条件的单元格之和，应使用SUMIFS函数。

5. SUMIFS多条件求和函数
格式： SUMIFS(sum_range, criteria_range1, criteria1, ［criteria_range2，criteria2］，…)
功能： 对指定区域中满足多个条件的值求和。
参数： sum_range 必需。要求和的单元格区域。
criteria_range1必需。求和条件数据对应的单元格区域。
criteria1必需。条件区域关联的条件。

criteria_range2，criteria2可选。附加的条件求和区域及其关联条件。最多可以设置127个条件区域及关联条件。
例： 设D2:D17单元格区域中存放的是学生的专业，E2:E17单元格区域存放的是学生的大学英语成绩，函数SUMIFS(E2:E17, D2:D17, "计算机", E2:E17, ">=60")用来统计计算机专业大学英语1及格学生的总分。
说明： 
(1) 多条件求和时，多个条件之间是“与”关系，即与求和单元格同一行的所有处于条件区域的单元格都满足指定条件，才进行求和。
(2) 条件区域和求和区域必须具有相同的行数和列数。
在Excel统计函数中，AVERAGEIFS函数功能是对指定区域中满足多个条件的值求平均值。函数用法与SUMIFS类似，读者可以试试使用AVERAGEIFS函数求上例中计算机专业大学英语
及格学生的平均分。
统计函数后测习题
(1) 在Excel成绩单工作表中包含了20个同学的成绩，C列为成绩值，第一行为标题行，在不改变行列顺序的情况下，在D列统计每位学生的成绩排名，应在D2单元格中填入公式，再向下填充至D21单元格。

A. =RANK.EQ(C2,C2:C21)
B. =RANK.EQ(C2,$C$2:$C$21)
C. =RANK.EQ(C2,$C2:$C21)
D. =RANK.EQ($C$2,$C$2:$C$21)
(2) 在Excel工作表中存放了某年级300个学生的考试成绩，A列到D列分别对应“学号”“班级”“性别”“成绩”，第一行为标题行。利用公式计算3班男生的平均分，正确的公式是。
A. =SUMIF(D2:D301,B2:B301,"3班",C2:C301,"男")
B. =SUMIFS(D2:D301,B2:B301,"3班",C2:C301,"男")
C. =AVERAGEIFS(D2:D301,B2:B301,"3班",C2:C301,"男")
D. =AVERAGEIF(D2:D301,B2:B301,"3班",C2:C301,"男")
5.5.10查找和引用函数





视频名称查找和引用函数二维码



主讲教师向华
视频地址


查找和引用函数用来在数据列表或表格中查找特定数值，或者返回某些指定引用。
1. LOOKUP查找
格式： LOOKUP (lookup_value, lookup_vector, ［result_vector］)
功能： 在
单行、单列或数组中查找值，返回另一行或列中与查找到的值位置相匹配的数据。
参数： lookup_value 必需。要在lookup_vector中查找的值，可以是数字、文本、逻辑值、单元格引用。
lookup_vector 必需。查找区域，应为只包含单行或单列的单元格区域，值为文本、数字或逻辑值。行或列必须按升序排序。
result_vector 可选。查找结果所在区域，应为只包含单行或单列的单元格区域，大小应于lookup_vector区域相同。
例： 在“成绩”表中，A2:A17单元格区域中的学号已按升序排序。函数LOOKUP(C19, A2:A17, B2:B17)表示在A2:A17区域中查找与C19单元格相同的学号，返回B2:B17区域中对应行的结果，即在学号中找到C19单元格中指定的学号，返回该学号对应的学生姓名，如图5.31所示。



图5.31LOOKUP查找指定学号对应的学生姓名


说明： 
(1) 使用LOOKUP查找时，查找区域必须先按升序排序，否则会返回错误结果。
(2) LOOKUP查找如果没有找到匹配的数据，系统将显示与查找区域中比查找值小的最大值所对应的查找结果。例如，如果图5.31中C19单元格的学号为01603006，返回的姓名仍为“陈瑞”。

2. VLOOKUP纵向查找
格式： VLOOKUP (lookup_value, table_array, col_index_num, ［range_lookup］)
功能： 在指定区域中纵向查找值，返回区域的指定列中与查找到的值相匹配的数据。
参数： lookup_value必需。查找目标，可以是数字、文本、单元格引用。
table_array必需。查找范围。其中第一列为查找的数据所在列。
col_index_num必需。返回数据在查找区域的列号。
range_lookup可选。查找方式，取值为TRUE(或1)/FALSE(或0)，分别表示近似匹配或精确匹配。当range_lookup省略时，系统默认为近似匹配。
精确匹配时，如果找到查找目标，返回该数据所在行对应返回列的值； 如果有多个数据与查找目标匹配，返回第一个数据对应返回列的值； 如果没有找到数据，则显示#N/A值不可用错误。
近似匹配时，如果找到查找目标，返回该数据所在行对应返回列的内容，如果没有找到数据，则显示与查找数据最接近的数据(小于查找目标的最大值)对应返回列的内容。使用近似匹配查找之前，要对查找范围的第一列升序排序。
例： 
(1) 在“成绩”表中，函数=VLOOKUP(C19,B2:G17,5,FALSE)表示要查找的数据是C19单元格的值“陈瑞”，查找区域为B2:G17，其中第1列B2:B17为要查找的姓名所在区域。要返回的听力成绩在查找区域的第5列，查找方式为精确匹配。查找时，系统在B2:B17区域中找到“陈瑞”在第9行，返回该行对应查找区域的第5列的单元格数据，即陈瑞的听力成绩，如图5.32所示。


图5.32VLOOKUP精确查找陈瑞的听力成绩


(2) 在“分数等级换算”表中，函数VLOOKUP(B8, B2:C6, 2, 1)表示要查找B8单元格中的数据78，查找范围为B1:C6单元格区域，

图5.33VLOOKUP近似匹配确定分数等级
要返回查找区域中的第2列分数等级，查找方式为近似匹配。查找时，78不在B2:B6中，返回比78小的最大数值70对应的等级C，如图5.33所示。
说明： 
(1) 在精确匹配时，如果未找到匹配的数据，系统将显示#N/A值不可用错误。如果要在未找到匹配数据时显示“未找到”或相关提示，可以将VLOOKUP和IFERROR函数配合使用。例如前面例题中查找某学生听力成绩的公式可以写为： =IFERROR(VLOOKUP(C19,B2:G17,5,FALSE),"未找到")。
(2) VLOOKUP函数必须将查找对象所在的列作为查找区域的第1列。如果返回值所在的列在查找对象所在列的左侧，可以调整表格中列的顺序再查找，也可以使用公式改变查找范围中的数据次序再查找。
(3) 在进行近似匹配时，必须先对查找范围的第1列升序排序，否则可能返回错误的查找结果。
在Excel统计函数中，HLOOUP函数用法与VLOOKUP类似，功能是在指定区域中横向查找值，返回区域的指定行中与查找到的值相匹配的数据。例如函数HLOOKUP("
听力"，A1:G17，3，FALSE)可以返回“成绩”表中第3行赵飞跃同学的听力成绩。

查找和引用函数后测习题
(1) 在使用LOOKUP函数查找时，以下说法正确的是。

A. 查找前，查找区域必须先升序排序
B. 查找前，查找结果区域必须先升序排序
C. 查找前，查找区域和查找结果区域必须都升序排序
D. 查找可以直接进行，不需要先排序
(2) 在使用VLOOKUP函数时，要查找的值必须处于查找区域中的列。
A. 第1B. 第2C. 最后D. 任意
(3) VLOOKUP函数查找时，第四个参数值为时表示查找方式为精确匹配。
A. TRUEB. FALSEC. 1D. 省略
5.5.11函数嵌套





视频名称函数嵌套二维码



主讲教师向华
视频地址

在Excel中，一个函数可以作为另一个函数的参数，形成函数嵌套。例如公式=INT(AVERAGE(E2:E17))，将AVERAGE函数求平均值的结果作为参数，嵌套在INT函数中，对计算的平均值再取整。
一个公式里最多可以嵌套64个级别的函数。系统在计算时，先计算内层函数的结果，再将结果作为参数代入外层函数进行计算。
如果公式中函数嵌套层数较多，或中间的计算过程较复杂，理解和检查公式会比较困难。单击“公式”选项卡“公式审核”组中的“公式求值”按钮，在“公式求值”对话框中可以按照公式的计算次序查看每一步运算的中间结果。
例如： 在“成绩”表中，E18单元格中的公式=INT(AVERAGE(E2:E17)*10+0.5)/10的结果为对所有学生数学成绩求平均值以后，再使用INT函数实现四舍五入保留一位小数。选择E18单元格后，单击“公式”选项卡“公式审核”组中的“公式求值”按钮，在“公式求值”对话框中单击“求值”按钮可以看到公式每一步计算时得到的中间值。


图5.34“公式求值”对话框


在嵌套函数作为参数时还应该注意，内层嵌套函数返回数字类型必须与参数要求的数字类型一致。例如INT函数参数应该是数值，INT内的AVERAGE函数返回的结果也是数值。如果INT函数内嵌套的函数返回值是文本，系统将显示#VALUE!值错误。
函数嵌套后测习题
(1) 一个Excel公式里最多可以嵌套级函数。
A. 3B. 8C. 10D. 64
(2) 如果公式中有函数嵌套，计算次序为。
A. 从左向右B. 从右向左C. 由内向外D. 由外向内
5.6图表
图表是工作表中数值类数据的图形化表示，是数据可视化工具。图表能形象、直观地反映数据的对比关系或趋势，让用户一目了然。
5.6.1插入图表





视频名称插入图表二维码



主讲教师向华
视频地址

图表是依据工作表中的数据生成的，制作图表时需要先分析数据，选择合适的图表类型，然后选择数据区域生成图表，最后根据需要对图表的细节进行调整。
Excel提供15种不同类型的图表，不同类型的图表表达数据的侧重点不一样。例如柱形图和条形图适合表现不同类别数值的对比，饼图和圆环图适合表现部分和总体的关系，折线图和面积图多用来表现随时间推移数据的变化趋势，散点图和气泡图常用于显示和比较数值。所以在制作图表之前需要先根据数据要表达的信息选择合适的图表类型。例如，如果要展示学生学习成绩的对比情况，可以用柱形图； 要分析消费支出比例，应该使用饼图； 要展示各地气温变化趋势，可以使用折线图； 游戏中展示角色战斗能力，则经常使用雷达图，如图5.35所示。如果不确定该使用哪种图表表现数据，可以选择数据后，单击“插入”选项卡“图表”组中的“推荐的图表”按钮，Excel将根据数据提出选择建议。


图5.35选择合适的图表表现数据


1. 选择图表数据区域
一张图表关联的数据通常分为两部分，一部分是承载数据的各个对象，在图表中被称为类别，在类别轴上列出，另一部分是承载数据的对象在某项目上的具体数值，称为系列值，在值轴方向展示。不同类型的图表，类别轴、值轴和数据系列的选取和显示不一样。
在插入图表时，首先要选择与图表关联的数据区域，即确定要在图表中展现的数据类别、数据系列。
选择数据区域时应该注意： 
(1) 图表中类别和数据系列对应数据的标题行必须选择。
(2) 如果类别所在的区域与系列所在的区域不连续，则应该先拖动鼠标选择类别中的第一个区域，再按住Ctrl键，依次选

图5.36选择计算机专业学生姓名及大学

英语1成绩

择类别中的其他区域以及系列中的各个区域。系列区域选择时的拖动动作要和类别区域动作一致，避免造成图表的混乱。
例如，要在“学生成绩”工作表中制作计算机专业学生大学英语1成绩的柱形图，应该选择的数据区域包括姓名(类别)、大学英语1(数据系列)，如图5.36所示。选区顺序建议先选择B1单元格，然后按住Ctrl键，依次选择B6:B10、E1、E6:E10区域。
2. 插入图表
选择数据区域后，单击“插入”选项卡“图表”组中的“推荐的图表”按钮或某一图表分类按钮，选择要插入的图表类型，即可在当前工作表内插入相应图表，如图5.37所示。这种方式插入的图表为嵌入式图表，拖动图表对象可以在工作表内移动图表，拖动图表边框四角及边框中间的尺寸控制柄，可调整图表的大小。


图5.37插入二维簇状柱形图



在当前工作表内插入嵌入式图表后，选定图表，单击“设计”选项卡“位置”组中的“移动图表”按钮，或右击图表，在快捷菜单中选择“移动图表”命令，可以在如图5.38所示“移动图表”对话框中设置图表对象位于的工作表，也可以选择创建新工作表，将图表作为独立图表放置在新工作表中。独立图表占满整张工作表，图表区的位置和大小不能改变。


图5.38“移动图表”对话框


选定要制作图表的数据区域后，按下F11键，可以快速生成一个二维簇状柱形图的图表工作表，如图5.39所示。


图5.39快速生成的独立图表工作表


插入图表后测习题
(1) 柱形图横轴上列出的是。
A. 系列名称B. 数值
C. 类别名称D. 图例
(2) 选定单元格区域后，按键可以快速生成
放置在独立图表工作表中的二维
簇状柱形图。
A. F4B. F5
C. F10D. F11
(3) 为图表选择数据区域时，该区域。
A. 必须是连续区域B. 可以是不连续的区域
C. 不能包含非数值数据D. 只能包括两列数据
5.6.2编辑图表





视频名称编辑图表二维码



主讲教师向华
视频地址

插入图表后，选定图表区，图表区的右上角将出现“图表元素” “图表样式”“图表筛选器”按钮，单击“图表元素”按钮，可以选择是否在图表中显示各种图表元素，例如坐标轴、图表标题、数据标题、图例等。单击“图表样式”按钮，可以在样式库里选择图表样式，改变图表配色。单击“图表筛选器”按钮，则可以选择在图表中显示的数据系列和类别，相当于对图表中展示的数据进行筛选。
创建图表后，可以利用“图表工具” 功能区和“格式”任务窗格对表格进行编辑。
1. “图表工具”功能区
选定图表后，Excel的“图表工具”功能区将出现“设计”“格式”两个选项卡，利用这些工具，可对图表进行编辑。
“设计”选项卡如图5.40所示，其主要功能是对图表的布局、颜色、样式、数据、图表类型、位置等属性进行设置。



图5.40“图表工具”功能区的“设计”选项卡


“格式”选项卡如图5.41所示，其主要功能是设置图表中选定对象的形状样式、排列、大小等。


图5.41“图表工具”功能区的“格式”选项卡


2. “设置格式”任务窗格
右击图表中的图表元素，例如坐标轴、数据系列、图表标题等，在快捷菜单中选择“设置<图表元素>格式”命令，窗口右侧将出现“设置<图表元素>格式”任务窗格。在窗格中将显示所选图表元素对应的“填充与线条”“效果”“大小与属性”“选项”等各类设置图标，单击图标，在下方的选项中可以对选定图表元素的细节进行设置。
图5.42为“设置坐标轴格式”任务窗格。


图5.42“设置坐标轴格式”任务窗格


编辑图表后测习题
(1) 生成柱形图后，可以直接在图表上修改内容的项目是。
A. 图表标题B. 图例中的文字
C. 分类轴中的分类名称D. 数据标签中的值
(2) 创建图表后，如果修改图表数据区域中的数据，图表内容。
A. 不更新B. 自动同步更新
C. 需要执行“刷新”命令才更新D. 关闭工作簿再重新打开时才更新
5.7数据管理与分析
在Excel中，将由一个标题行和若干数据行组成的矩形单元格区域称为数据列表。数据列表中的每一行数据称为一条记录，每一列称为一个字段，每一列对应的标题称为字段名。对于数据列表中的数据，Excel可以进行排序、筛选、分类汇总及数据透视等各种数据管理和分析操作，帮助用户从大量数据中快速得到有价值的信息。
在进行排序、筛选、分类汇总、数据透视等操作时，系统能够自动检测当前单元格周围的数据，识别数据列表区域，再进行操作。
5.7.1排序





视频名称排序二维码



主讲教师向华
视频地址

在工作表中输入数据后，数据列表中某些列上的数据通常是杂乱无序的。为了更直观地显示数据、方便对数据进行查找、分类，做出更有效的决策，经常要对表中的某一列或某几列数据进行排序。
排序有升序和降序两种次序，升序指数据自上而下按照从小到大次序排列，降序则按照从大到小次序排列。对数值型字段排序时，数值的大小决定排序次序。对文本型的字段排序时，英文按字母表顺序决定数据值的大小，汉字默认按拼音的字母顺序决定数据值大小，汉字排在英文字母后面。对日期型的数据进行排序时，按日期先后决定数据值大小。
进行排序的列称为关键字。排序时，可以对一个关键字排序，也可以对多个关键字排序。关键字中的单元格调整顺序时，数据列表中的每条记录将随之移动。
1. 单关键字排序
选定数据列表中要排序的关键字列中的任意一个单元格，单击“数据”选项卡“排序和筛选”分组中的“升序/降序”按钮，或者单击“开始”选项卡“编辑”组里“排序和筛选”按钮中的“升序/降序”命令完成排序。
2. 多关键字排序
多关键字排序可以设置一个主要关键字和多个次要关键字。排序时，首先对数据列表中的数据按主要关键字进行排序，主要关键字相同的行则依次按次要关键字排序。在Excel中，最多可以按64列进行排序。
要进行多关键字排序，需要先选定数据列表中的任意一个单元格，再单击“数据”选项卡“排序和筛选”组中的“排序”按钮，或者单击“开始”选项卡“编辑”组中“排序和筛选”按钮中的“自定义排序”命令，在“排序”对话框中设置关键字，单击“确定”按钮完成排序，如图5.43所示。


图5.43“排序”对话框


Excel排序操作默认是对关键字按列排序，字母不区分大小写，汉字按拼音的字母顺序判断大小。如果需要修改这些设置，可以单击“排序”对话框中的“选项”按钮，在“排序选项”对话框中进行设置，如图5.44所示。



图5.44“排序选项”对话框


排序后测习题
(1) 对某一工作表中的数据,按多个关键字进行排序,可以单击“数据”选项卡“排序和筛选”组中的“”按钮开始操作。

A. 排序B. 升序C. 降序D. 高级
(2) 在Excel的“排序”对话框中,最多可以设定个关键字。
A. 1B. 2C. 64D. 128
(3) 按两个关键字进行排序时,操作效果是:。
A. 主要关键字不同时, 再按次要关键字排序
B. 主要关键字相同时, 再按次要关键字排序
C. 次要关键字不同时, 再按主要关键字排序
D. 次要关键字相同时, 再按主要关键字排序
5.7.2筛选和高级筛选





视频名称筛选和高级筛选二维码



主讲教师向华
视频地址


在处理数据时，数据列表中可以容纳的数据量可能非常大。利用筛选可以从大量记录中找出满足指定条件的记录并显示，快速获得有价值的信息。
1. 筛选
1) 建立自动筛选
选定数据列表中的任意一个单元格，单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，或单击“开始”选项卡“编辑”组“排序和筛选”按钮中的“筛选”命令，可以在数据列表中建立自动筛选。
建立自动筛选后，数据列表中每个字段名右侧会出现一个黑色三角形，单击三角形打开筛选菜单，可以设置筛选类型和筛选条件。
自动筛选包括三种筛选类型： 按值列表筛选、按条件筛选和按颜色筛选，如图5.45所示。三种筛选类型中可以任选一种对某列数据进行自动筛选。
(1) 按值列表筛选。
值列表筛选中显示筛选字段中所有的数据值，清除列表顶部的“全选”复选框后，在列表中选择要作为筛选依据的数据值，单击“确定”按钮，表格中将只显示等于选定数据值的记录。
（2） 按条件筛选。
对于文本型数据、数值型数据、日期型数据，建立筛选后，筛选菜单中将分别显示不同的条件筛选子菜单，单击对应选项，将打开“自定义自动筛选方式”对话框，如图5.46所示。设置筛选条件后，单击“确定”按钮筛选数据。


图5.45自动筛选的类型




图5.46“自定义自动筛选方式”对话框



在“自定义自动筛选方式”对话框中，可以设置两个筛选条件，两个筛选条件之间需要指定是“与”运算还是“或”运算。“与”运算表示同时满足两个条件的数据才符合筛选条件，“或”运算表示只要满足两个条件中的一个就符合筛选条件。

（3） 按颜色筛选。
如果单元格设置了字体颜色或填充颜色，还可以在颜色筛选菜单中设置按字体颜色或填充颜色进行筛选。
执行筛选操作后，Excel显示满足指定条件的记录，隐藏不满足条件的记录。对于筛选得到的数据子集，可以直接进行复制、查找、编辑、设置格式、制作图表和打印等。
如果在数据列表的多个字段中分别设置筛选条件，各条件之间是“与”关系，只有同时满足各列筛选条件的记录才会被筛选出来。自动筛选无法筛选字段之间为“或”关系的条件，如果要筛选字段之间的关系为“或”的条件，则需要使用高级筛选。
2) 取消自动筛选
设置自动筛选条件后，如果要显示所有数据，可以清除筛选条件或取消自动筛选。
单击已设置筛选条件的字段右侧的“筛选”按钮，在菜单中选择“在筛选列中清除筛选”命令，可以清除选定字段的筛选条件，显示这一列的所有数据。
选定筛选结果中的任意一个单元格，单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，可以取消自动筛选。取消自动筛选时，将清除所有的筛选条件。
2. 高级筛选
如果筛选条件比较复杂，存在字段之间的“或”运算，则必须使用高级筛选解决问题。
1) 建立高级筛选
进行高级筛选之前，需要先在工作表的空白处建立筛选条件区域。条件区域的标题行由数据列表的标题行组成，下方的每个单元格可以输入一个对应条件。
条件填写规则如下： 
 等于条件直接写等于的值，大于、小于等条件写“>值”或“<值”，空单元格表示无条件。
 同一行的条件表示逻辑“与”运算，不同行的条件表示逻辑“或”运算。
例如，图5.47中表示的筛选条件为计算机专业大学英语1及格。图5.48中表示的筛选条件为听力或口语成绩90分以上。


图5.47“与”条件




图5.48“或”条件




图5.49“高级筛选”对话框

建立条件区域后，选择数据列表中的任意一个单元格，单击“数据”选项卡“排序和筛选”组中的“高级”按钮，打开
“高级筛选”对话框，如图5.49所示，在对话框中设置列表区域、条件区域，并选择筛选结果位置后，
单击“确定”按钮完成高级筛选。


如果选择在原有区域显示筛选结果，将在原数据列表中显示筛选结果，不满足高级筛选条件的记录被隐藏。如果选择将筛选结果复制到其他位置，筛选结果将复制到指定位置，原数据列表不变。
2) 清除高级筛选
建立高级筛选后，如果要取消在原有区域的高级筛选，可以单击“数据”选项卡“排序和筛选”分组中的“清除”按钮，显示全部数据。对于复制到指定位置的筛选结果，则可以直接删除。
筛选后测习题
(1) 建立自动筛选后，在某一列设置了筛选条件A后，又在另一列设置了筛选条件B，筛选结果将。
A. 同时满足A和BB. 满足A或者满足B
C. 只满足A，不满足BD. 只满足B，不满足A
(2) 在Excel中进行数据的自动筛选或高级筛选操作，系统会。
A. 删除掉所有未被筛选的数据
B. 显示筛选出来的数据
C. 自动将筛选出的数据生成一个新工作表
D. 自动将筛选出的数据复制为一个新的工作簿
(3) 在Excel中进行高级筛选时，条件区域中各行筛选条件的逻辑运算是。
A. 同行为“或”,异行为“与”B. 同行为“与”,异行为“或”
C. 隔行为“与”,同行为“或”D. 多行为“与”,一行为“或”
5.7.3分类汇总





视频名称分类汇总二维码



主讲教师向华
视频地址

在对数据进行统计分析时，经常需要进行分类汇总。分类汇总对数据列表中的数据进行分类，再对每一类数据进行求和、求平均值、计数等操作，得到各个类别之间的对比数据。
1. 设置分类汇总
在进行分类汇总之前，首先要先确定分类字段并对分类字段排序。排序后，选定数据列表中的任意一个单元格，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，在“分类汇总”对话框中设置分类字段、汇总方式和汇总项
，如图5.50所示，单击“确定”按钮完成分类汇总。

例如，如果要统计学生成绩表中各专业学生的口语平均分，要先对分类字段“专业”排序，再进行分类汇总。在“分类汇总”对话框中，应设置分类字段是“专业”，汇总方式为“平均值”，汇总项是“口语”字段。
分类汇总完成后，在每一个分类下方将增加一行，显示分类的汇总结果，工作表左边将出现分级显示符号，如图5.51所示。单击其中的减号，可以将对应级别数据隐藏，隐藏数据后，单击分级显示符号中的加号显示。单击分级显示符号中的1、2、3，可以显示对应级别数据，隐藏下级数据。


图5.50“分类汇总”对话框




图5.51分类汇总的分级显示


2. 嵌套分类汇总
在进行分类汇总时，每次在“分类汇总”对话框中只能设置一个分类字段、一种汇总方式。如果需要对多个字段进行分类，或者在同一分类字段下要使用多种汇总方式，都需要进行多次分类汇总，也就是将分类汇总嵌套。
例如要统计学生成绩表中的各专业学生人数和口语平均分，分类字段为专业，汇总方式要分别进行计数和求平均值，所以分类汇总要进行两次。要统计学生成绩表中各专业男女生的口语平均分，分类字段有专业和性别两个字段，分类汇总也需要进行两次。
对于一个分类字段，多种汇总方式的分类汇总，对分类字段排序后，进行两次分类汇总，每次汇总分别设置不同的汇总方式即可。
对于有多个分类字段的分类汇总，先要对数据列表进行多关键字排序，第一次汇总的分类字段为主关键字，第二次汇总的分类
字段为次要关键字。排序后，依次进行两次分类汇总。
要特别注意的是，在进行第二次分类汇总时，要取消“分类汇总”对话框中的“替换当前分类汇总”复选框，才能使两次分类汇总嵌套。两次嵌套分类汇总完成后，汇总结果将分为4级显示，如图5.52所示。


图5.52嵌套的分类汇总结果


3. 删除分类汇总
要删除建立的分类汇总，可以选定已建立分类汇总的数据列表中的任意单元格，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，在“分类汇总”对话框中单击“全部删除”按钮，删除分类汇总。
分类汇总后测习题
(1) 在Excel中要正确地进行分类汇总操作，必须先对字段排序。

A. 分类B. 任意C. 所有D. 要计算的字段
(2) Excel中，没有先对数据列表作相应的排序，分类汇总的操作。
A. 不能进行
B. 会自动按汇总字段排序并得出正确结果
C. 可以进行,但是结果一般不符合实际需求
D. 会自动按分类字段排序并得出正确结果
(3) 进行分类汇总操作后,正确的效果应该是。
A. 每条记录得出一行汇总B. 每行数据得出一行汇总
C. 每类数据得出一行汇总D. 每列数据得出一列汇总
(4) 设“新学员”工作表中有学号、姓名、专业、入学成绩等字段，已按专业升序排序，如果要求各专业入学成绩的平均值，在“分类汇总”对话框的“分类字段”下拉列表框中，应选择字段。
A. 学号B. 姓名C. 专业D. 入学成绩
5.7.4数据透视表与数据透视图





视频名称数据透视表与数据透视图二维码



主讲教师向华
视频地址


Excel中还有另一种功能和分类汇总类似，能灵活进行交互式数据统计的工具——数据透视表和数据透视图。数据透视表能以三维分类的方式对工作表数据或外部数据进行统计计算，汇总结果可以同时在数据透视图中显示。生成数据透视表和数据透视图后，用户随时能对数据透视表和透视图的结构进行修改，实时观察数据的变化情况，从不同的角度查看、比较数据，并做出明智决策。
1. 数据透视表
数据透视表由“筛选器”“行标签”“列标签”“值”四部分组成，如图5.53所示。“筛选器”用于选择表中的部分数据进行分析。“行标签”和“列标签”用于对数据进行分类，两种标签都可以设置多个分类字段。“数值”用于显示汇总数据。在创建数据透视表之前，需要分析创建数据透视表的目的，确定行标签、列标签、筛选条件和数值。

1) 插入数据透视表
确定数据透视表中的各类对象后，选定数据列表中的任意一个单元格，单击“插入”选项卡“表格”组“数据透视表”按钮，在“创建数据透视表”对话框中选择要分析的数据，设置数据透视表放置的位置，如图5.54所示，单击“确定”按钮，创建数据透视表。


图5.53数据透视表的结构



图5.54“创建数据透视表”对话框

新创建的数据透视表是空的。单击数据透视表，工作表窗口右侧将显示“数据透视表字段”窗格
，如图5.55所示。将窗格里字段列表中的字段分别拖动到窗格下方的“筛选器”“列”“行”“值”等对应区域，即可完成数据透视表设置。



图5.55空数据透视表及“数据透视表字段”窗格



单击“数据透视表字段”窗格“值”区域中的某个值字段，执行“值字段设置”命令，在“值字段设置”对话框
中可以进一步设置值汇总方式和显示方式，如图5.56所示。


图5.56“值字段设置”对话框


在设置数据透视表时，字段列表中的任意字段都可以添加到“筛选器”“列”“行”“值”四个区域的任何一个中。通常将非数值字段添加到“行”区域，将数值字段添加到“值”区域，将日期和时间添加到“列”区域。
2) 修改数据透视表
在“数据透视表字段”窗格中，如果将区域中的字段拖出区域，则数据透视表中对应标签中的字段将删除。如果将区域中的字段拖动到其他区域，数据透视表中的标签也随之动态改变。
选定数据透视表时，工具栏中将显示数据透视表工具，包括“分析”和“设计”选项卡。
“分析”选项卡如图5.57所示，包括字段的展开和折叠、组选择、更改数据源等关于数据透视表结构的设置。


图5.57数据透视表“分析”选项卡


“设计”选项卡如图5.58所示，其中包括“布局”“样式”等分组，主要对数据透视表的外观进行设置。


图5.58数据透视表“设计”选项卡


3) 分组
在数据透视表中，对于行标签或列标签中的数值、日期、文本型数据，都可以设置分组。分组后，数据透视表中的数据被划分为指定的子集，进行分级统计和展示。
选择数据透视表行标签或列标签中的单元格或单元格区域，单击“分析”选项卡“分组”组中的“组选择”按钮，可以在数据透视表的行或列中设置分组，例如对学生入学成绩设置分组，统计各分数段学生人数，效果如图5.59所示。


图5.59数值型字段分组效果


要取消创建的分组，可以选定行标签或列标签中的分组单元格，单击“分析”选项卡“分组”组中的“取消组合”按钮。取消分组后，数据透视表中的分级显示同时消失。
4) 筛选和切片器
如果要在数据透视表中只显示部分满足条件的数据，可以在行标签或列标签的“标签筛选”或“值筛选”菜单中设置筛选条件。

切片器是一组可以筛选数据的按钮。选定数据透视表中的任意一个单元格，单击“分析”选项卡“筛选”组中的“插入切片器”按钮，在“插入切片器”对话框中选择要添加切片器的字段，如图5.60所示，单击“确定”按钮即可插入切片器。
插入切片器后，切片器将显示在数据透视表所在的工作表中，切片器中的按钮对应切片器字段的取值。选择切片器中的按钮，数据透视表中将只显示切片器中选择的项目。单击按下切片器上方的“多选”按钮，可以在切片器中选择多个项目。单击切片器右上角的“清除筛选器”按钮，将取消该切片器中的筛选，显示对应字段中的所有数据，如图5.61所示。


图5.60“插入切片器”对话框




图5.61使用切片器进行筛选


一个数据透视表中可以插入多个字段的切片器，在多个切片器中选择值时，系统只显示满足所有切片器条件的数据。

要删除切片器，选定切片器后按Del键即可。删除切片器后，切片器中已经设置的筛选条件仍保留在数据透视表中。如果要清除对应筛选条件，需要在行标签或列标签的筛选菜单中处理。
5) 删除数据透视表
对于在新工作表中建立的数据透视表，可以直接删除数据透视表所在的工作表。
对于在工作表内创建的数据透视表，可以先选择数据透视表中所有数据，单击“开始”选项卡“编辑”组“清除”按钮中的“全部清除”命令，或按Del键清除数据透视表。
选择数据透视表中的任意单元格，单击“分析”选项卡“操作”组中的“清除”按钮，执行“全部清除”命令，将清除数据透视表中的所有数据，将数据透视表还原为空透视表。
2. 数据透视图
数据透视图以图表的形式显示数据透视表的分析结果，使数据透视表统计的结果可视化，以便用户更加直观的了解数据的对比和变化趋势。
1) 创建数据透视图
数据透视图依赖于数据透视表。可以在创建数据透视表的同时插入数据透视图，也可以先创建数据透视表后，再根据数据透视表的数据创建数据透视图。

选择数据列表中的任意一个单元格，单击“插入”选项卡“图表”组中的“数据透视图”按钮。在“创建数据透视图”对话框中设置数据列表区域和数据透视图放置的位置，单击 “确定”按钮，可以插入一张空数据透视表及对应的数据透视图
。选择空数据透视图，在工作表窗口右侧的“数据透视图字段”窗格中设置“筛选器”“图例(系列)”“轴(类别)”“值”等区域中的字段，完成数据透视图设置，如图5.62所示。数据透视图中的“轴(类别)”对应数据透视表中的“行”区域，“图例(系列)”对应数据透视表中的“列”区域。



图5.62插入并设置数据透视图


对于已经插入的数据透视表，选择数据透视表中的任意单元格，单击“分析”选项卡“工具”组中的“数据透视图”按钮，在弹出的“插入图表”对话框中选择图表类型，单击“确定”按钮插入指定类型的数据透视图。
2) 编辑数据透视图
选择数据透视图，在工作表窗口右侧的“数据透视图字段”窗格“筛选器”“图例(系列)”“轴(类别)”“值”等区域拖动字段，数据透视图和对应数据透视表的内容将同步修改。

选择数据透视图时，Excel功能区将显示数据透视图工具，包含“分析”“设计”和“格式”选项卡。
“分析”选项卡如图5.63所示，用于更改数据透视图结构，例如更改数据源，为数据透视图插入切片器和日程表，移动图表位置，显示或隐藏字段按钮。


图5.63数据透视图“分析”选项卡


“设计”选项卡如图5.64所示，用于设置数据透视图的外观，例如图表样式、图表类型、配色等。


图5.64数据透视图“设计”选项卡


“格式”选项卡如图5.65所示，用于设置数据透视图中对象的细节。在“格式”选项卡的左上角的下拉列表中选择图表中的对象，例如数据系列、图例、水平轴、垂直轴等，可以对选定对象的形状样式、大小等细节进行设置。


图5.65数据透视图“格式”选项卡


3) 删除数据透视图
要删除数据透视图，可以选定数据透视图后按下Del键。这时数据透视图将被删除，对应的数据透视表仍然保留。
如果选定数据透视图后单击“分析”选项卡“操作”组中的“清除”按钮，执行“全部清除”命令，将清除数据透视表和数据透视图中的所有数据，将数据透视表和数据透视图还原为空透视表和空透视图。
数据透视表与数据透视图后测习题
(1) 以下关于数据透视表和数据透视图的说法中错误的是。

A. 数据透视表的数据来源发生变化时，数据透视表会自动更新
B. 数据透视图是数据来源于数据透视表的图表
C. 数据透视表的行标签和列标签都可以设置筛选条件
D. 数据透视表可以和数据源放置在同一个工作表中
(2) 数据透视表的值区域中对数值型字段默认的汇总方式是。
A. 计数B. 求和C. 平均值D. 最大值