第3章 3.3_Excel公式和函数

第3章 通过 Excel 创建 并处理电子表格
NCRE
2
第3章 通过 Excel 创建并处理电子表格
3.1 3.2 3.3
• Excel 制表基础 • 工作簿与多工作表操作 • Excel 公式和函数 • 在 Excel 中创建图表 • Excel 数据分析与处理
3.4
3.5 3.6
• Excel 与其他程序的协同与共享
4. 更改或删除名称
如果更改了某个已定义的名称，则工作簿中所有已引用该名称的 位置均会自动随之更新。

更改名称：“公式”选项卡→“定义的名称”组→“名称管 理器”按钮→在名称列表中选择要更改的名称→“编辑”按 钮→ 在“编辑名称”对话框中修改名称属性。 删除名称： “公式”选项卡→“定义的名称”组→“名称管 理器”按钮→在名称列表中选择名称→“删除”按钮。

常量：固定的数值或文本。例如，数字“210”和文本“姓名” 均为常量。 运算符：运算符用于连接常量、单元格引用，从而构成完整的表 达式。常用的运算符有：算术运算符，字符连接符，关系运算符。
第3章 通过 Excel 创建并处理电子表格

3.3 Excel 公式和函数
5
3.3.1 使用公式基本方法
2. 公式的输入与编辑
本节将通过对员工档案表和员工工资表的处理，帮助书娟实现以 下工作目标：


了解公式和函数的基本作用，了解在公式中各种引用的含义及方法。 掌握公式的构成、输入方法，学会构建公式以帮助计算。 了解 Excel 提供了哪些类型的函数，掌握函数的基本输入方法。 掌握 Exce l中常用、重要函数的使用方法，并在实际工作中应用它们。 了解公式及函数应用过程中各种常见问题及解决方法。
3.3.4 Excel 中常用函数的应用
2）条件求和函数
SUMIF(range, criteria, [sum_range])
功能：对指定单元格区域中符合指定条件的值求和。 提示：在函数中任何文本条件或任何含有逻辑或数学符号 的条件都必须使用双引号 (") 括起来。如果条件为数字， 则无需使用双引号。
CUBEVALUE(connection,member_expression1,member_expression2…) 从多维 数据集中返回汇总值 如果在系统中安装了某一包含函数的应用程序，该程序做为Excel的加载项， 其所包含的函数作用自定义函数显示在这里以供选用。
3.3 Excel 公式和函数
3
3.3 Excel公式和函数
本节案例背景：

Excel 提供大量实用函数满足各类计算的需要。通过公式和函数计 算出的结果不但正确率有保证，而且在原始数据发生改变后，计 算结果能够自动更新。

在人事档案管理和工资表的创建过程中，公式和函数给书娟的工 作提供了相当的便利，极大地提高了工作的效率和效果。
第3章 通过 Excel 创建并处理电子表格

3.3 Excel 公式和函数
9
3.3.2 名称的定义与引用
2. 为单元格或单元格区域定义名称
定义好的名称将会在公式及函数中被引用。 1）快速定义名称

选择要命名的单元格或单元格区域，在编辑栏的“名 称框”中输入名称后按 Enter键确认。 在案例工作簿文档“3.3 员工档案及工资表”的“员工 档案表”工作表中，将数据列表区域 A3:N38的名称定 义为“全体员工资料”。
1）通过“名称框”引用
单击编辑栏中“名称框”右侧的黑色箭头，打开“名
称”下拉列表→单击选择某一名称 提示：通过名称框打开的“名称”下拉列表中不包括常
量和公式的名称。
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
Байду номын сангаас
13
3.3.2 名称的定义与引用
2）在公式中引用
“公式”选项卡→“定义的名称”组→“用于公式”按钮→选择名称

第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
10
3.3.2 名称的定义与引用
2）将现有行和列标题转换为名称

选择要命名的区域，必须包括行或列标题 → “公式” 选项卡 → “定义的名称”组 → “从所选内容创建”按 钮 → 在对话框中，通过选中“首行”、“左列”、 “末行”或“右列”复选框来指定包含标题的位置。 在案例工作簿文档“3.3 员工档案及工资表”的“员工 档案表”中，将“基本工资”和“工龄工资”两列的 首行转换为相应列数据的名称。

第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
11
3.3.2 名称的定义与引用
3）使用“新名称”对话框定义名称

“公式”选项卡→“定义的名称”组→“定义名称”按钮 → 在“名称”文本框中输入名称 → 在“范围”下设定名 称的适用范围 → 在“备注”说明性批注。 → “引用位置” 框中修改命名对象（可以是单元格区域、常量、公式）， 可选择下列操作之一。

3. 公式的复制与填充


拖动公式单元格右下角的填充柄
“开始”选项卡→“编辑”组→“填充”按钮。 进行公式的复制填充时，填充的实际上是公式而非数据本 身，填充时公式中对单元格的引用采用的是相对引用。
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
7
3.3.1 使用公式基本方法
3.3 Excel 公式和函数
8
3.3.2 名称的定义与引用
为单元格或区域指定一个名称，是实现绝对引用的方法之一。 可以定义为名称的对象包括：常量、单元格或单元格区域、公 式。 1. 了解名称的语法规则

唯一性原则：名称在其适用范围内必须始终唯一，不可重复。 有效字符：名称中第一个字符必须是字母、下划线 (_) 或反 斜杠 (\)，名称中不能使用大小写字母“ C”、“c”、“R” 或“r”。 不能与单元格地址相同：例如，名称是A1、B$2等。 不能使用空格：可选用下划线 (_) 和句点 (.) 作为单词分隔符。 名称长度有限制：一个名称最多可以包含 255 个西文字符。 不区分大小写：例如，Sales与SALES视为同名。
查找和引用函数
数据库函数 文本函数 逻辑函数 信息函数 工程函数 兼容性函数 多维数据集函数 与加载项一起安装 的用户自定义函数
CONVERT(number, from_unit, to_unit) 将数字从一个度量系统转换到另一个 度量系统中。 RANK(number,ref,[order]) 返回一个数字在数字列表中的排位
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
17
3.3.3 使用函数的基本方法
2）通过“插入函数”按钮插入
“公式”选项卡→ “函数库”组→ “插入函数”按钮， 打开“插入函数”对话框→ 在“选择类别”下拉表中选择 函数类别，或者在“搜索函数”框中输入函数的简单描述 后单击“转到”按钮→ 在“选择函数”列表中选择函数→
4. 单位格引用
在公式中最常用的是单元格引用。可以在单元格中引用一个 单元格、一个单元格区域、引用另一个工作表或工作簿中的 单元格或区域。 单元格引用方式分为以下几类：


相对引用：如“=A1”。
绝对引用：如“=$A$1” 。 混合引用：如“=A$1”、“=$A1”、 “=$A$1”
第3章 通过 Excel 创建并处理电子表格

在案例工作簿文档“3.3 员工档案及工资表”的“员工档 案表”中，将工龄工资常量“50”元定义为名称“工龄工 资_每年”。
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
12
3.3.2 名称的定义与引用
3. 引用名称
名称可直接用来快速选定已命名的区域，更重要的是 可以在公式中引用名称以实现精确引用。

提示：若公式中已引用的某个名称被删除，可能导致公式出错。
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
14
3.3.3 使用函数的基本方法
1. 认识函数
函数实际上特殊的公式，主要是为解决那些复杂计算需求 而提供的一种预置算法，如求和函数 SUM ，平均值函数 EVERAGE，条件函数 IF等。 函数通常表示为：函数名（[参数1], [参数2],……） 函数中的参数可以是常量、单元格地址、数组、已定义的 名称、公式、函数等。输入函数时必须以等号“=”开始。

例如：
=SUMIF(B2:B25,">5") 表示对B2:B25区域大于5的数值进 行相加； =SUMIF(B2:B5, "John", C2:C5) ， 表 示 对 单 元 格 区 域 C2:C5 中与单元格区域 B2:B5中等于“ John ”的单元格 对应的单元格中的值求和。
2. Excel 函数分类
Excel 提供大量工作表函数，并按其功能进行分类。Excel 2010 目前默认提供的函数类别共13大类，见表3.3中所列。
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
15
表 3.3 Excel 2010函数类别
函数类别 财务函数 日期和时间函数 数学和三角函数 统计函数 常用函数示例及说明 NPV(rate,value1,[value2],...) 返回一项投资的净现值。 YEAR(serial_number) 返回某日期对应的年份 INT(number) 将数字向下舍入到最接近的整数。 AVERAGE(number1, [number2], ...) 返回参数的算术平均值 VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 搜索某 个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值 DCOUNTA(database, field, criteria) 返回满足指定条件的非空单元格的个数。 MID(text, start_num, num_chars) 返回文本字符串中从指定位置开始的特定 数目的字符 IF(logical_test, [value_if_true], [value_if_false]) 若指定条件的计算结果为 TRUE，将返回某个值；若该条件的计算结果为 FALSE，则返回另一个值。 ISBLANK(value) 检验单元格值是否为空，若为空则返回 TRUE