1、1第第3 3章章 通过通过 Excel Excel 创建并处理电子表创建并处理电子表格格23.3 Excel3.3 Excel公式和函数公式和函数本节案例背景：Excel 提供大量实用函数满足各类计算的需要。通过公式和函数计算出的结果不但正确率有保证，而且在原始数据发生改变后，计算结果能够自动更新。在人事档案管理和工资表的创建过程中，公式和函数给书娟的工作提供了相当的便利，极大地提高了工作的效率和效果。本节将通过对员工档案表员工档案表和员工工资表员工工资表的处理，帮助书娟实现以下工作目标：了解公式和函数的基本作用，了解在公式中各种引用的含义及方法。掌握公式的构成、输入方法，学会构建公式以帮助计
2、算。了解 Excel 提供了哪些类型的函数，掌握函数的基本输入方法。掌握 Exce l中常用、重要函数的使用方法，并在实际工作中应用它们。了解公式及函数应用过程中各种常见问题及解决方法。33章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.1 3.3.1 使用公式的基本方法使用公式的基本方法1.1. 认识公式认识公式公式是一组表达式，由单元格引用、常量、运算符、括号组成，复杂的公式是一组表达式，由单元格引用、常量、运算符、括号组成，复杂的公式还可以包括函数，用于计算生成新的值。公式还可以包括函数，用于计算生成新的值。在在ExcelExcel中，公式总是以中，公式总是
3、以等号等号“= =”开始开始。p单元格引用：即单元格地址，用于表示单元格在工作表上所处位置的坐标。例如，显示在第B列和第3行交叉处的单元格，其引用形式为“B3”。p常量：固定的数值或文本。例如，数字“210”和文本“姓名”均为常量。p运算符：运算符用于连接常量、单元格引用，从而构成完整的表达式。常用的运算符有：算术运算符，字符连接符，关系运算符。43章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.1 3.3.1 使用公式基本方法使用公式基本方法2.2. 公式的输入与编辑公式的输入与编辑1 1）输入公式）输入公式= =常量常量/ /单元格引用单元格引用/ /表达式表
4、达式必须以等号“=”开始，在公式中所使用的运算符都必须是西文的半角字符例如：=30，=B3，=A5*10%，=C8&C953章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.1 3.3.1 使用公式基本方法使用公式基本方法2 2）修改公式）修改公式用鼠标双击公式进入编辑状态，在单元格或者编辑栏中均可对公式进行修改即可。删除公式：按 Del 键。3.3. 公式的复制与填充公式的复制与填充拖动公式单元格右下角的填充柄“开始”选项卡“编辑”组“填充”按钮。进行公式的复制填充时，填充的实际上是公式而非数据本身，填充时公式中对单元格的引用采用的是相对引用。63章 通过 Exc
5、el 创建并处理电子表格3.3 Excel 公式和函数3.3.1 3.3.1 使用公式基本方法使用公式基本方法4.4. 单位格引用单位格引用在公式中最常用的是单元格引用。可以在单元格中引用一个在公式中最常用的是单元格引用。可以在单元格中引用一个单元格、一个单元格区域、引用另一个工作表或工作簿中的单元格、一个单元格区域、引用另一个工作表或工作簿中的单元格或区域。单元格或区域。单元格引用方式分为以下几类：相对引用：相对引用：如“=A1”。绝对引用：绝对引用：如“=$A$1” 。混合引用：混合引用：如“=A$1”、“=$A1”、 “=$A$1” 73章 通过 Excel 创建并处理电子表格3.3 E
6、xcel 公式和函数3.3.2 3.3.2 名称的定义与引用名称的定义与引用为单元格或区域指定一个名称，是实现绝对引用的方法之一。为单元格或区域指定一个名称，是实现绝对引用的方法之一。可以定义为名称的对象包括：常量、单元格或单元格区域、公可以定义为名称的对象包括：常量、单元格或单元格区域、公式。式。1.1. 了解名称的语法规则了解名称的语法规则p唯一性原则唯一性原则：名称在其适用范围内必须始终唯一，不可重复。p有效字符有效字符：名称中第一个字符必须是字母、下划线 (_) 或反斜杠 ()，名称中不能使用大小写字母“C”、“c”、“R”或“r”。p不能与单元格地址相同不能与单元格地址相同：例如，名
7、称是A1、B$2等。p不能使用空格不能使用空格：可选用下划线 (_) 和句点 (.) 作为单词分隔符。p名称长度有限制名称长度有限制：一个名称最多可以包含 255 个西文字符。p不区分大小写不区分大小写：例如，Sales与SALES视为同名。83章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.2 3.3.2 名称的定义与引用名称的定义与引用2.2. 为单元格或单元格区域定义名称为单元格或单元格区域定义名称定义好的名称将会在公式及函数中被引用。1 1）快速定义名称）快速定义名称p选择要命名的单元格或单元格区域，在编辑栏的“名称名称框框”中输入名称后按 Enter键确
8、认。p在案例工作簿文档“3.3 员工档案及工资表”的“员工档案表”工作表中，将数据列表区域A3:N38的名称定义为“全体员工资料”。93章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.2 3.3.2 名称的定义与引用名称的定义与引用2 2）将现有行和列标题转换为名称）将现有行和列标题转换为名称p选择要命名的区域，必须包括行或列标题“公式”选项卡“定义的名称”组“从所选内容创建”按钮在对话框中，通过选中“首行”、“左列”、“末行”或“右列”复选框来指定包含标题的位置。p在案例工作簿文档“3.3 员工档案及工资表”的“员工档案表”中，将“基本工资”和“工龄工资”两列的
9、首行转换为相应列数据的名称。103章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.2 3.3.2 名称的定义与引用名称的定义与引用3 3）使用）使用“新名称新名称”对话框定义名称对话框定义名称p“公式”选项卡“定义的名称”组“定义名称”按钮在“名称”文本框中输入名称 在“范围”下设定名称的适用范围在“备注”说明性批注。 “引用位置”框中修改命名对象（可以是单元格区域、常量、公式），可选择下列操作之一。p在案例工作簿文档“3.3 员工档案及工资表”的“员工档案表”中，将工龄工资常量“50”元定义为名称“工龄工资_每年”。113章 通过 Excel 创建并处理电子表格
10、3.3 Excel 公式和函数3.3.2 3.3.2 名称的定义与引用名称的定义与引用3.3. 引用名称引用名称名称可直接用来快速选定已命名的区域，更重要的是可以在公式中引用名称以实现精确引用。1 1）通过）通过“名称框名称框”引用引用单击编辑栏中“名称框”右侧的黑色箭头，打开“名称”下拉列表单击选择某一名称提示：通过名称框打开的“名称”下拉列表中不包括常量和公式的名称。123章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.2 3.3.2 名称的定义与引用名称的定义与引用2 2）在公式中引用）在公式中引用“公式”选项卡“定义的名称”组“用于公式”按钮选择名称4.4
11、. 更改或删除名称更改或删除名称如果更改了某个已定义的名称，则工作簿中所有已引用该名称的位置均会自动随之更新。p更改名称：“公式”选项卡“定义的名称”组“名称管理器”按钮在名称列表中选择要更改的名称“编辑”按钮 在“编辑名称”对话框中修改名称属性。p删除名称： “公式”选项卡“定义的名称”组“名称管理器”按钮在名称列表中选择名称“删除”按钮。提示：若公式中已引用的某个名称被删除，可能导致公式出错。133章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.3 3.3.3 使用函数的基本方法使用函数的基本方法1.1. 认识函数认识函数函数实际上特殊的公式，主要是为解决那些
12、复杂计算需求而提供的一种预置算法，如求和函数SUM，平均值函数EVERAGE，条件函数 IF等。函数通常表示为：函数名（参数1, 参数2,）函数中的参数可以是常量、单元格地址、数组、已定义的名称、公式、函数等。输入函数时必须以等号“=”开始。2.2. Excel Excel 函数分类函数分类Excel 提供大量工作表函数，并按其功能进行分类。Excel 2010 目前默认提供的函数类别共13大类，见表3.3中所列。14表 3.3 Excel 2010函数类别函数类别常用函数示例及说明财务函数NPV(rate,value1,value2,.) 返回一项投资的净现值。日期和时间函数YEAR(ser
13、ial_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) 返回文
14、本字符串中从指定位置开始的特定数目的字符逻辑函数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,membe
15、r_expression1,member_expression2) 从多维数据集中返回汇总值与加载项一起安装的用户自定义函数如果在系统中安装了某一包含函数的应用程序，该程序做为Excel的加载项，其所包含的函数作用自定义函数显示在这里以供选用。3.3 Excel 公式和函数153章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.3 3.3.3 使用函数的基本方法使用函数的基本方法3.3. 函数的输入与编辑函数的输入与编辑函数的输入方式与公式类似，可以直接在单元格中输入“=函数名(所引用的参数)”，但是要想记住每一个函数名并正确输入所有参数是有相当困难的。因此，通常情
16、况采用参照的方式输入一个函数。1 1）通过）通过“函数库函数库”组插入组插入“公式”选项卡“函数库”组中的某一函数类别 从函数列表中单击函数在“函数参数”对话框中输入或选择参数163章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.3 3.3.3 使用函数的基本方法使用函数的基本方法2 2）通过）通过“插入函数插入函数”按钮插入按钮插入“公式”选项卡 “函数库”组 “插入函数”按钮，打开“插入函数”对话框 在“选择类别”下拉表中选择函数类别，或者在“搜索函数”框中输入函数的简单描述后单击“转到”按钮 在“选择函数”列表中选择函数在“函数参数”对话框中输入参数。3 3
17、）修改函数）修改函数在包含函数的单元格的双击鼠标，进入编辑状态，对函数参数进行修改后按Enter键确认。173章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel 3.3.4 Excel 中常用函数的应用中常用函数的应用1.1. ExcelExcel中常用函数简介中常用函数简介1 1）求和函数）求和函数 SUM(number1,number2,.)功能：将指定的参数number1、number2相加求和。例如：例如：=SUM(A1:A5)=SUM(A1:A5) 是将单元格是将单元格A1A1至至A5A5中的所有数值相加中的所有数值相加=SUM(A1, A3
18、, A5) =SUM(A1, A3, A5) 是将单元格是将单元格A1A1、A3A3和和A5A5中的数字相中的数字相加。加。183章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel 3.3.4 Excel 中常用函数的应用中常用函数的应用2 2）条件求和函数）条件求和函数SUMIF(range, criteria, sum_range)功能：对指定单元格区域中符合指定条件的值求和。提示：在函数中任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 () 括起来。如果条件为数字，则无需使用双引号。例如：=SUMIF(B2:B25,5) 表示对B2:
19、B25区域大于5的数值进行相加；=SUMIF(B2:B5, John, C2:C5)，表示对单元格区域C2:C5中与单元格区域B2:B5中等于“John”的单元格对应的单元格中的值求和。193章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel3.3.4 Excel中常用函数的应用中常用函数的应用3 3）多条件求和函数）多条件求和函数SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .)功能：对指定单元格区域中满足多个条件的单元格求和。例如：=SUMIFS(
20、A1:A20, B1:B20, 0, C1:C20, =60,及格,不及格) 表示，如果单元格A2中的值大于等于60，则显示“及格”字样，否则显示“不及格”字样；p=IF(A2=90,优秀,IF(A2=80,良好, IF(A2=60,及格 ,不及格) 表示下列对应关系：单元格单元格A2A2中的值中的值公式单元格显示的内容公式单元格显示的内容A2=90优秀90A2=80良好80A2=60及格A260不及格263章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel3.3.4 Excel中常用函数的应用中常用函数的应用1010）当前日期和时间函数）当前日期和时
21、间函数 NOW()NOW()功能：返回当前计算机系统的日期和时间。当将数据格式设置为数值时，将返回当前日期和时间所对应的序列号，该序列号的整数部分表明其与1900年1月1日之间的天数。1111）函数）函数 YEAR(serial_number)YEAR(serial_number) 功能：返回指定日期对应的年份。返回值为 1900 到 9999 之间的整数例如：=YEAR(A2) 当在A2单元格中输入日期2008/12/27时，该函数返回年份2008。注意：公式所在的单元格不能是日期格式。273章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel3.3.
22、4 Excel中常用函数的应用中常用函数的应用1212）当前日期函数）当前日期函数 TODAY()功能：返回今天的日期。通过该函数，可以实现无论何时打开工作簿时工作表上都能显示当前日期；该函数也可以用于计算时间间隔，可以用来计算一个人的年龄。例如：=YEAR(TODAY()-1963 假设一个人出生在1963年，该公式使用TODAY函数作为YEAR函数的参数来获取当前年份，然后减去1963，最终返回对方的年龄。283章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel3.3.4 Excel中常用函数的应用中常用函数的应用1313）平均值函数）平均值函数A
23、VERAGE(number1, number2, .)功能：求指定参数number1、number2的算术平均值，最多可包含255个参数。例如：=AVERAGE(A2:A6) 表示对单元格区域A2到A6中的数值求平均值=AVERAGE(A2:A6, C6) 表示对单元格区域A2到A6中数值与C6中的数值求平均值。293章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel3.3.4 Excel中常用函数的应用中常用函数的应用1414）条件平均值函数）条件平均值函数AVERAGEIF(range, criteria, average_range) 功能：对指
24、定区域中满足给定条件的所有单元格中的数值求算术平均值例如：=AVERAGEIF(A2:A5,5000,B2:B5) 表示对单元格区域B2:B5中与单元格区域A2:A5中大于5000的单元格所对应的单元格中的值求平均值。303章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel3.3.4 Excel中常用函数的应用中常用函数的应用1515）多条件平均值函数）多条件平均值函数AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, .) 功能：对指定区域
25、中满足多个条件的所有单元格中的数值求算术平均值例如：=AVERAGEIFS(A1:A20,B1:B20,70,C1:C20, 55) 表示统计单元格区域B2到B5中值大于55的单元格的个数。333章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel3.3.4 Excel中常用函数的应用中常用函数的应用1919）多条件计数函数）多条件计数函数COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2) 功能：统计指定区域内符合多个给定条件的单元格的数量。可以将条件应用于跨多个区域的单元格，
26、并计算符合所有条件的次数。例如：=COUNTIFS(A2:A7, 80,B2:B7,0,I4-J4-3500,0)”；也可以通过绝对引用基础数据表中的减除标准构建函数“=IF(I5-J5-基础数据!$F$12)0,I5-J5-基础数据!$F$12,0)”463章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel3.3.4 Excel中常用函数的应用中常用函数的应用 计算个人所得税计算个人所得税通过多级IF函数嵌套，可构建出个人所得税计算公式，并通过ROUND函数对计算结果保留2位小数。个人所得税税率表可参见“基础数据”表中所列。=ROUND(IF(K4=
27、1500,K4*0.03,IF(K4=4500,K4*0.1-105,IF(K4=9000,K4*0.2-555,IF(K4=35000,K4*0.25-1005,IF(K4=55000,K4*0.3-2755,IF(K4=80000,K4*0.35-5505,K4*0.45-13505),2) 计算实付工资计算实付工资实付工资应付工资合计-扣除社保-应交个人所得税。473章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 3.3.5 公式与函数常见问题公式与函数常见问题在输入公式或函数的过程中，当输入有误时，单元格中常常会出现各种不同的错误结果。对这些提示的含义
28、有所了解，有助于更好地发现并修正公式或函数中的错误。1.1. 常见错误值列表常见错误值列表48表3.4 公式或函数中的常见错误列表错误显示错误显示说说 明明#当某一列的宽度不够而无法在单元格中显示所有字符时，或者单元格包含负的日期或时间值时，Excel将显示此错误。 #DIV/0!当一个数除以零 (0) 或不包含任何值的单元格时，Excel将显示此错误。#N/A当某个值不允许被用于函数或公式但却被其引用时，Excel 将显示此错误。#NAME? 当Excel无法识别公式中的文本时，将显示此错误。例如，区域名称或函数名称拼写错误，或者删除了某个公式引用的名称。#NULL!当指定两个不相交的区域的
29、交集时，Excel将显示此错误。交集运算符是分隔公式中的两个区域地址间的空格字符。例如，区域A1:A2和C3:C5不相交，因此，输入公式 =SUM(A1:A2 C3:C5) 将返回 #NULL! 错误。#NUM!当公式或函数包含无效数值时，Excel将显示此错误。#REF!当单元格引用无效时，Excel将显示此错误。例如，如果删除了某个公式所引用的单元格，该公式将返回 #REF! 错误。#VALUE!如果公式所包含的单元格有不同的数据类型，则Excel将显示此错误。如果启用了公式的错误检查，则屏幕提示会显示“公式中所用的某个值是错误的数据类型”。通常，通过对公式进行较少更改即可修复此问题。3.
30、3 Excel 公式和函数493章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 3.3.5 公式与函数常见问题公式与函数常见问题2.2. 审核和更正公式中的错误审核和更正公式中的错误1 1）打开或关闭错误检查规则）打开或关闭错误检查规则“文件”选项卡“选项”命令打开“Excel选项”对话框从左侧类别列表中单击“公式”选项在“错误检查规则”区域中，选中或清除某一检查规则的复选框2 2）检查并依次更正常见公式错误）检查并依次更正常见公式错误“公式”选项卡“公式审核”组“错误检查”按钮 检查并处理错误。503章 通过 Excel 创建并处理电子表格3.3 Excel
31、 公式和函数3.3.5 3.3.5 公式与函数常见问题公式与函数常见问题3 3）通过）通过“监视窗口监视窗口”监视公式及其结果监视公式及其结果使用“监视窗口”可以方便地在大型工作表中检查、审核或确认公式计算及其结果，而无需反复滚动或定位到工作表的不同部分。“公式”选项卡“公式审核”组“监视窗口”按钮“添加监视”按钮“添加”按钮，增加监视点。513章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 3.3.5 公式与函数常见问题公式与函数常见问题3.3. 公式中的循环引用公式中的循环引用如果公式引用了自己所在的单元格，则无论是直接引用还是间接引用，该公式都会创建循环
32、引用。默认情况下，如果发生循环引用，Excel就会报错。1 1）定位并更正循环引用）定位并更正循环引用发生循环引用现象“公式”选项卡“公式审核”组“错误检查”按钮右侧的黑色箭头“循环引用”命令查看当前工作表中所有发生循环引用的单元格位置523章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 3.3.5 公式与函数常见问题公式与函数常见问题2 2）更改）更改 Excel Excel 迭代公式的次数使循环引用起作用迭代公式的次数使循环引用起作用如果想要保留循环引用，则可以启用迭代计算，并确定公式重新计算的次数。在发生循环引用的工作表中，依次单击“文件”选项卡“选项”
33、“公式” 在“计算选项”区域中，选中“启用迭代计算”复选框 在“最多迭代次数”框中输入进行重新计算的最大迭代次数。 “最大误差”框中输入两次计算结果之间可以接受的最大差异值。533章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 3.3.5 公式与函数常见问题公式与函数常见问题4.4. 追踪单元格以显示公式与单元格之间的关系追踪单元格以显示公式与单元格之间的关系有时，当公式中包含引用单元格（被其他单元格中的公式引用的单元格）或从属单元格（包含引用其他单元格的公式的单元格）时，检查公式的准确性或查找错误的根源会很困难。为了帮助检查公式，可以通过“追踪引用单元格”和
34、“追踪从属单元格”功能以图形方式显示或追踪这些单元格与包含追踪箭头的公式之间的关系。543章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 3.3.5 公式与函数常见问题公式与函数常见问题1 1）显示某个单元格中公式的引用与被引用）显示某个单元格中公式的引用与被引用提 示 ： 需 要 保 证 “ 文 件 ” 选 项 卡 “ 选 项 ” “ 高级”“此工作簿的显示选项”下“对于对象，显示”“全部”单选项被选中，才可以执行追踪单元格操作。p追踪引用单元格：“公式”选项卡“公式审核”组“追踪引用单元格”p追踪从属单元格： “公式”选项卡“公式审核”组“追踪从属单元格”553章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 3.3.5 公式与函数常见问题公式与函数常见问题2 2）查看工作表中的全部引用关系）查看工作表中的全部引用关系打开要查看的工作表，在一个空单元格中输入等号“=”。单击工作表左上角的“全选”按钮，按回车键Enter确认。 单击选择该单元格，在“公式”选项卡的“公式审核”组中，单击两次“追踪引用单元格”。谢谢观看！
