3.3_Excel公式和函数

3.3 Excel 公式和函数
5
3.3.1 使用公式基本方法
2. 公式的输入与编辑
1）输入公式
=常量/单元格引用/表达式
必须以等号“ =”开始，在公式中所使用的运算符都
必须是西文的半角字符
例如：=30，=B3，=A5*10%，=C8&C9
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数

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


功能：将指定的参数 number1 、 number2…… 相加求 和。
例如：

=SUM(A1:A5) 是将单元格A1至A5中的所有数值相加

=SUM(A1, A3, A5) 是将单元格A1、A3和A5中的数字
相加。
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
16
3.3.4 Excel 中常用函数的应用
3.3 Excel 公式和函数
10
3.3.2 名称的定义与引用
2）在公式中引用
“公式”选项卡→“定义的名称”组→“用于公式”按钮→选择名称
4. 更改或删除名称
如果更改了某个已定义的名称，则工作簿中所有已引用该名称的 位置均会自动随之更新。

更改名称：“公式”选项卡→“定义的名称”组→“名称管 理器”按钮→在名称列表中选择要更改的名称→“编辑”按 钮→ 在“编辑名称”对话框中修改名称属性。 删除名称： “公式”选项卡→“定义的名称”组→“名称管 理器”按钮→在名称列表中选择名称→“删除”按钮。
功能：对指定单元格区域中满足多个条件的单元格求和。
例如：

=SUMIFS(A1:A20, B1:B20, “>0”, C1:C20, “<10”) 表示
对 区 域 A1:A20 中 ， B1:B20 中 的 相 应 数 值 大 于 零 、 且
C1:C20中的相应数值小于10。
第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 与其他程序的协同与共享
采用参照的方式输入一个函数。
1）通过“函数库”组插入
“公式”选项卡→“函数库”组中的某一函数类别→ 从函
数列表中单击函数→在“函数参数”对话框中输入或选择
参数
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
14
3.3.3 使用函数的基本方法
2）通过“插入函数”按钮插入
“公式”选项卡→ “函数库”组→ “插入函数”按钮， 打开“插入函数”对话框→ 在“选择类别”下拉表中选择 函数类别，或者在“搜索函数”框中输入函数的简单描述 后单击“转到”按钮→ 在“选择函数”列表中选择函数→ 在“函数参数”对话框中输入参数。

选择要命名的单元格或单元格区域，在编辑栏的“名 称框”中输入名称后按 Enter键确认。 在案例工作簿文档“3.3 员工档案及工资表”的“员工 档案表”工作表中，将数据列表区域 A3:N38的名称定 义为“全体员工资料”。

第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
为8；=INT(-8.9) 表示将 -8.9 向下舍入到最接近的整数，结果
为 -9。
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
19
3.3.4 Excel中常用函数的应用
2）条件求和函数
SUMIF(range, criteria, [sum_range])
功能：对指定单元格区域中符合指定条件的值求和。 提示：在函数中任何文本条件或任何含有逻辑或数学符号 的条件都必须使用双引号 (") 括起来。如果条件为数字， 则无需ቤተ መጻሕፍቲ ባይዱ用双引号。

例如：
=SUMIF(B2:B25,">5") 表示对B2:B25区域大于5的数值进 行相加； =SUMIF(B2:B5, "John", C2:C5) ， 表 示 对 单 元 格 区 域 C2:C5 中与单元格区域 B2:B5中等于“ John ”的单元格 对应的单元格中的值求和。



了解公式和函数的基本作用，了解在公式中各种引用的含义及方法。 掌握公式的构成、输入方法，学会构建公式以帮助计算。 了解 Excel 提供了哪些类型的函数，掌握函数的基本输入方法。


掌握 Exce l中常用、重要函数的使用方法，并在实际工作中应用它们。
了解公式及函数应用过程中各种常见问题及解决方法。
名称长度有限制：一个名称最多可以包含 255 个西文字符。 不区分大小写：例如，Sales与SALES视为同名。
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
8
3.3.2 名称的定义与引用
2. 为单元格或单元格区域定义名称
定义好的名称将会在公式及函数中被引用。 1）快速定义名称
置的坐标。例如，显示在第B列和第3行交叉处的单元格，其引用 形式为“B3”。

常量：固定的数值或文本。例如，数字“210”和文本“姓名”
均为常量。

运算符：运算符用于连接常量、单元格引用，从而构成完整的表 达式。常用的运算符有：算术运算符，字符连接符，关系运算符。
第3章 通过 Excel 创建并处理电子表格
9
3.3.2 名称的定义与引用
3. 引用名称
名称可直接用来快速选定已命名的区域，更重要的是 可以在公式中引用名称以实现精确引用。
1）通过“名称框”引用
单击编辑栏中“名称框”右侧的黑色箭头，打开“名
称”下拉列表→单击选择某一名称
提示：通过名称框打开的“名称”下拉列表中不包括常
量和公式的名称。
第3章 通过 Excel 创建并处理电子表格
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
4
3.3.1 使用公式的基本方法
1. 认识公式
公式是一组表达式，由单元格引用、常量、运算符、括号组成，复杂 的公式还可以包括函数，用于计算生成新的值。在Excel中，公式总是 以等号“=”开始。

单元格引用：即单元格地址，用于表示单元格在工作表上所处位
3
3.3 Excel公式和函数
本节案例背景：

Excel 提供大量实用函数满足各类计算的需要。通过公式和函数计 算出的结果不但正确率有保证，而且在原始数据发生改变后，计 算结果能够自动更新。 在人事档案管理和工资表的创建过程中，公式和函数给书娟的工 作提供了相当的便利，极大地提高了工作的效率和效果。 本节将通过对员工档案表和员工工资表的处理，帮助书娟实现以 下工作目标：
2. Excel 函数分类
Excel 提供大量工作表函数，并按其功能进行分类。Excel 2010 目前默认提供的函数类别共13大类，见表3.3中所列。
第3章 通过 Excel 创建并处理电子表格
3.3 Excel 公式和函数
12
表 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 CONVERT(number, from_unit, to_unit) 将数字从一个度量系统转换到另一个 度量系统中。 RANK(number,ref,[order]) 返回一个数字在数字列表中的排位 CUBEVALUE(connection,member_expression1,member_expression2…) 从多维 数据集中返回汇总值 如果在系统中安装了某一包含函数的应用程序，该程序做为Excel的加载项， 其所包含的函数作用自定义函数显示在这里以供选用。