Excel公式及函数

1、第第3章章 通过通过 Excel 创建并处理电子表格创建并处理电子表格3.1Excel 制表基础3.2工作簿与多工作表操作3.3Excel 公式和函数3.4在 Excel 中创建图表3.5Excel 数据分析与处理3.6Excel 与其他程序的协同与共享13.3 Excel公式和函数公式和函数本节案例背景：Excel 提供大量实用函数满足各类计算的需要。通过公式和函数计算出的结果不但正确率有保证，而且在原始数据发生改变后，计算结果能够自动更新。在人事档案管理和工资表的创建过程中，公式和函数给书娟的工作提供了相当的便利，极大地提高了工作的效率和效果。本节将通过对员工档案表员工档案表和员工工资表员

2、工工资表的处理，帮助书娟实现以下工作目标：了解公式和函数的基本作用，了解在公式中各种引用的含义及方法。掌握公式的构成、输入方法，学会构建公式以帮助计算。了解 Excel 提供了哪些类型的函数，掌握函数的基本输入方法。掌握 Exce l中常用、重要函数的使用方法，并在实际工作中应用它们。了解公式及函数应用过程中各种常见问题及解决方法。2第3章 通过 Excel 创建并处理电子表格3.3.1 使用公式的基本方法使用公式的基本方法1. 认识公式认识公式公式是一组表达式，由单元格引用、常量、运算符、括号组成，复杂的公式是一组表达式，由单元格引用、常量、运算符、括号组成，复杂的公式还可以包括函数，用于计

3、算生成新的值。公式还可以包括函数，用于计算生成新的值。在在Excel中，公式总是以中，公式总是以等号等号“=”开始开始。p单元格引用：即单元格地址，用于表示单元格在工作表上所处位置的坐标。例如，显示在第B列和第3行交叉处的单元格，其引用形式为“B3”。p常量：固定的数值或文本。例如，数字“210”和文本“姓名”均为常量。p运算符：运算符用于连接常量、单元格引用，从而构成完整的表达式。常用的运算符有：算术运算符，字符连接符，关系运算符。3第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.1 使用公式基本方法使用公式基本方法2. 公式的输入与编辑公式的输入与编辑1）

4、输入公式）输入公式=常量常量/单元格引用单元格引用/表达式表达式必须以等号“=”开始，在公式中所使用的运算符都必须是西文的半角字符例如：=30，=B3，=A5*10%，=C8&C94第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.1 使用公式基本方法使用公式基本方法2）修改公式）修改公式用鼠标双击公式进入编辑状态，在单元格或者编辑栏中均可对公式进行修改即可。删除公式：按 Del 键。3. 公式的复制与填充公式的复制与填充拖动公式单元格右下角的填充柄“开始”选项卡“编辑”组“填充”按钮。进行公式的复制填充时，填充的实际上是公式而非数据本身，填充时公式中对单元格的

5、引用采用的是相对引用。5第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.1 使用公式基本方法使用公式基本方法4. 单位格引用单位格引用在公式中最常用的是单元格引用。可以在单元格中引用一个在公式中最常用的是单元格引用。可以在单元格中引用一个单元格、一个单元格区域、引用另一个工作表或工作簿中的单元格、一个单元格区域、引用另一个工作表或工作簿中的单元格或区域。单元格或区域。单元格引用方式分为以下几类：相对引用：相对引用：如“=A1”。绝对引用：绝对引用：如“=$A$1” 。混合引用：混合引用：如“=A$1”、“=$A1”、 “=$A$1” 6第3章 通过 Excel

6、 创建并处理电子表格3.3 Excel 公式和函数3.3.2 名称的定义与引用名称的定义与引用为单元格或区域指定一个名称，是实现绝对引用的方法之一。为单元格或区域指定一个名称，是实现绝对引用的方法之一。可以定义为名称的对象包括：常量、单元格或单元格区域、公可以定义为名称的对象包括：常量、单元格或单元格区域、公式。式。1. 了解名称的语法规则了解名称的语法规则p唯一性原则唯一性原则：名称在其适用范围内必须始终唯一，不可重复。p有效字符有效字符：名称中第一个字符必须是字母、下划线 (_) 或反斜杠 ()，名称中不能使用大小写字母“C”、“c”、“R”或“r”。p不能与单元格地址相同不能与单元格地址

7、相同：例如，名称是A1、B$2等。p不能不能使用空格使用空格：可选用下划线 (_) 和句点 (.) 作为单词分隔符。p名称长度有限制名称长度有限制：一个名称最多可以包含 255 个西文字符。p不区分大小写不区分大小写：例如，Sales与SALES视为同名。7第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.2 名称的定义与引用名称的定义与引用2. 为单元格或单元格区域定义名称为单元格或单元格区域定义名称定义好的名称将会在公式及函数中被引用。1）快速定义名称）快速定义名称p选择要命名的单元格或单元格区域，在编辑栏的“名名称框称框”中输入名称后按 Enter键确认。

8、p在案例工作簿文档“3.3 员工档案及工资表”的“员工档案表”工作表中，将数据列表区域A3:N38的名称定义为“全体员工资料”。8第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.2 名称的定义与引用名称的定义与引用2）将现有行和列标题转换为名称）将现有行和列标题转换为名称p选择要命名的区域，必须包括行或列标题“公式”选项卡“定义的名称”组“从所选内容创建”按钮在对话框中，通过选中“首行”、“左列”、“末行”或“右列”复选框来指定包含标题的位置。p在案例工作簿文档“3.3 员工档案及工资表”的“员工档案表”中，将“基本工资”和“工龄工资”两列的首行转换为相应列数

9、据的名称。9第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.2 名称的定义与引用名称的定义与引用3）使用）使用“新名称新名称”对话框定义名称对话框定义名称p“公式”选项卡“定义的名称”组“定义名称”按钮在“名称”文本框中输入名称 在“范围”下设定名称的适用范围在“备注”说明性批注。 “引用位置”框中修改命名对象（可以是单元格区域、常量、公式），可选择下列操作之一。p在案例工作簿文档“3.3 员工档案及工资表”的“员工档案表”中，将工龄工资常量“50”元定义为名称“工龄工资_每年”。10第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3

10、.3.2 名称的定义与引用名称的定义与引用3. 引用名称引用名称名称可直接用来快速选定已命名的区域，更重要的是可以在公式中引用名称以实现精确引用。1）通过）通过“名称框名称框”引用引用单击编辑栏中“名称框”右侧的黑色箭头，打开“名称”下拉列表单击选择某一名称提示：通过名称框打开的“名称”下拉列表中不包括常量和公式的名称。11第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.2 名称的定义与引用名称的定义与引用2）在公式中引用）在公式中引用“公式”选项卡“定义的名称”组“用于公式”按钮选择名称4. 更改或删除名称更改或删除名称如果更改了某个已定义的名称，则工作簿中

11、所有已引用该名称的位置均会自动随之更新。p更改名称：“公式”选项卡“定义的名称”组“名称管理器”按钮在名称列表中选择要更改的名称“编辑”按钮 在“编辑名称”对话框中修改名称属性。p删除名称： “公式”选项卡“定义的名称”组“名称管理器”按钮在名称列表中选择名称“删除”按钮。提示：若公式中已引用的某个名称被删除，可能导致公式出错。12第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.3 使用函数的基本方法使用函数的基本方法1. 认识函数认识函数函数实际上特殊的公式，主要是为解决那些复杂计算需求而提供的一种预置算法，如求和函数SUM，平均值函数EVERAGE，条件函

12、数 IF等。函数通常表示为：函数名（参数1, 参数2,）函数中的参数可以是常量、单元格地址、数组、已定义的名称、公式、函数等。输入函数时必须以等号“=”开始。2. Excel 函数分类函数分类Excel 提供大量工作表函数，并按其功能进行分类。Excel 2010 目前默认提供的函数类别共13大类，见表3.3中所列。13第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数表 3.3 Excel 2010函数类别函数类别常用函数示例及说明财务函数NPV(rate,value1,value2,.) 返回一项投资的净现值。日期和时间函数YEAR(serial_number) 返

13、回某日期对应的年份数学和三角函数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,member_expression1

15、,member_expression2) 从多维数据集中返回汇总值与加载项一起安装的用户自定义函数如果在系统中安装了某一包含函数的应用程序，该程序做为Excel的加载项，其所包含的函数作用自定义函数显示在这里以供选用。143.3 Excel 公式和函数3.3.3 使用函数的基本方法使用函数的基本方法3. 函数的输入与编辑函数的输入与编辑函数的输入方式与公式类似，可以直接在单元格中输入“=函数名(所引用的参数)”，但是要想记住每一个函数名并正确输入所有参数是有相当困难的。因此，通常情况采用参照的方式输入一个函数。1）通过）通过“函数库函数库”组插入组插入“公式”选项卡“函数库”组中的某一函数类别

16、 从函数列表中单击函数在“函数参数”对话框中输入或选择参数15第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.3 使用函数的基本方法使用函数的基本方法2）通过）通过“插入函数插入函数”按钮插入按钮插入“公式”选项卡 “函数库”组 “插入函数”按钮，打开“插入函数”对话框 在“选择类别”下拉表中选择函数类别，或者在“搜索函数”框中输入函数的简单描述后单击“转到”按钮 在“选择函数”列表中选择函数在“函数参数”对话框中输入参数。3）修改函数）修改函数在包含函数的单元格的双击鼠标，进入编辑状态，对函数参数进行修改后按Enter键确认。16第3章 通过 Excel 创

17、建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel 中常用函数的应用中常用函数的应用1. Excel中常用函数简介中常用函数简介1）求和函数）求和函数 SUM(number1,number2,.)功能：将指定的参数number1、number2相加求和。例如例如：=SUM(A1:A5) 是将单元格是将单元格A1至至A5中的所有数值中的所有数值相加相加=SUM(A1, A3, A5) 是将单元格是将单元格A1、A3和和A5中的数字中的数字相加相加。17第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel 中常用函数的应用中常用函数的应

18、用2）条件求和函数）条件求和函数SUMIF(range, criteria, sum_range)功能：对指定单元格区域中符合指定条件的值求和。提示：在函数中任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 () 括起来。如果条件为数字，则无需使用双引号。例如：=SUMIF(B2:B25,5) 表示对B2:B25区域大于5的数值进行相加；=SUMIF(B2:B5, John, C2:C5)，表示对单元格区域C2:C5中与单元格区域B2:B5中等于“John”的单元格对应的单元格中的值求和。18第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Exc

19、el中常用函数的应用中常用函数的应用3）多条件求和函数）多条件求和函数SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .)功能：对指定单元格区域中满足多个条件的单元格求和。例如：=SUMIFS(A1:A20, B1:B20, 0, C1:C20, =60,及格,不及格) 表示，如果单元格A2中的值大于等于60，则显示“及格”字样，否则显示“不及格”字样；p=IF(A2=90,优秀,IF(A2=80,良好, IF(A2=60,及格 ,不及格) 表示下列对应关系：25第3章 通过 Excel 创建并

20、处理电子表格3.3 Excel 公式和函数单元格单元格A2A2中的值中的值公式单元格显示的内容公式单元格显示的内容A2=90优秀90A2=80良好80A2=60及格A260不及格3.3.4 Excel中常用函数的应用中常用函数的应用10）当前日期和时间函数）当前日期和时间函数 NOW()功能：返回当前计算机系统的日期和时间。当将数据格式设置为数值时，将返回当前日期和时间所对应的序列号，该序列号的整数部分表明其与1900年1月1日之间的天数。11）函数）函数 YEAR(serial_number) 功能：返回指定日期对应的年份。返回值为 1900 到 9999 之间的整数例如：=YEAR(A2)

21、 当在A2单元格中输入日期2008/12/27时，该函数返回年份2008。注意：公式所在的单元格不能是日期格式。26第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel中常用函数的应用中常用函数的应用12）当前日期函数）当前日期函数 TODAY()功能：返回今天的日期。通过该函数，可以实现无论何时打开工作簿时工作表上都能显示当前日期；该函数也可以用于计算时间间隔，可以用来计算一个人的年龄。例如：=YEAR(TODAY()-1963 假设一个人出生在1963年，该公式使用TODAY函数作为YEAR函数的参数来获取当前年份，然后减去1963，最终返回对方

22、的年龄。27第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel中常用函数的应用中常用函数的应用13）平均值函数）平均值函数AVERAGE(number1, number2, .)功能：求指定参数number1、number2的算术平均值，最多可包含255个参数。例如：=AVERAGE(A2:A6) 表示对单元格区域A2到A6中的数值求平均值=AVERAGE(A2:A6, C6) 表示对单元格区域A2到A6中数值与C6中的数值求平均值。28第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel中常用函数的应用

23、中常用函数的应用14）条件平均值函数）条件平均值函数AVERAGEIF(range, criteria, average_range) 功能：对指定区域中满足给定条件的所有单元格中的数值求算术平均值例如：=AVERAGEIF(A2:A5,5000,B2:B5) 表示对单元格区域B2:B5中与单元格区域A2:A5中大于5000的单元格所对应的单元格中的值求平均值。29第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel中常用函数的应用中常用函数的应用15）多条件平均值函数）多条件平均值函数AVERAGEIFS(average_range, criter

24、ia_range1, criteria1, criteria_range2, criteria2, .) 功能：对指定区域中满足多个条件的所有单元格中的数值求算术平均值例如：=AVERAGEIFS(A1:A20,B1:B20,70,C1:C20, 55) 表示统计单元格区域B2到B5中值大于55的单元格的个数。32第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel中常用函数的应用中常用函数的应用19）多条件计数函数）多条件计数函数COUNTIFS(criteria_range1, criteria1, criteria_range2, criter

25、ia2) 功能：统计指定区域内符合多个给定条件的单元格的数量。可以将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。例如：=COUNTIFS(A2:A7, 80,B2:B7,0,I4-J4-3500,0)”；也可以通过绝对引用基础数据表中的减除标准构建函数“=IF(I5-J5-基础数据!$F$12)0,I5-J5-基础数据!$F$12,0)”45第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.4 Excel中常用函数的应用中常用函数的应用 计算个人所得税计算个人所得税通过多级IF函数嵌套，可构建出个人所得税计算公式，并通过ROUND函数对计算结果保留2

26、位小数。个人所得税税率表可参见“基础数据”表中所列。=ROUND(IF(K4=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) 计算计算实付实付工资工资实付工资应付工资合计-扣除社保-应交个人所得税。46第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 公式与函数常见问题公式与函数常见问题在输入公式或函数的过程中，

27、当输入有误时，单元格中常常会出现各种不同的错误结果。对这些提示的含义有所了解，有助于更好地发现并修正公式或函数中的错误。1. 常见错误值列表常见错误值列表47第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数表3.4 公式或函数中的常见错误列表错误显示错误显示说说 明明#当某一列的宽度不够而无法在单元格中显示所有字符时，或者单元格包含负的日期或时间值时，Excel将显示此错误。 #DIV/0!当一个数除以零 (0) 或不包含任何值的单元格时，Excel将显示此错误。#N/A当某个值不允许被用于函数或公式但却被其引用时，Excel 将显示此错误。#NAME? 当Excel

28、无法识别公式中的文本时，将显示此错误。例如，区域名称或函数名称拼写错误，或者删除了某个公式引用的名称。#NULL!当指定两个不相交的区域的交集时，Excel将显示此错误。交集运算符是分隔公式中的两个区域地址间的空格字符。例如，区域A1:A2和C3:C5不相交，因此，输入公式 =SUM(A1:A2 C3:C5) 将返回 #NULL! 错误。#NUM!当公式或函数包含无效数值时，Excel将显示此错误。#REF!当单元格引用无效时，Excel将显示此错误。例如，如果删除了某个公式所引用的单元格，该公式将返回 #REF! 错误。#VALUE!如果公式所包含的单元格有不同的数据类型，则Excel将显示

29、此错误。如果启用了公式的错误检查，则屏幕提示会显示“公式中所用的某个值是错误的数据类型”。通常，通过对公式进行较少更改即可修复此问题。483.3 Excel 公式和函数3.3.5 公式与函数常见问题公式与函数常见问题2. 审核和更正公式中的错误审核和更正公式中的错误1）打开或关闭错误检查规则）打开或关闭错误检查规则“文件”选项卡“选项”命令打开“Excel选项”对话框从左侧类别列表中单击“公式”选项在“错误检查规则”区域中，选中或清除某一检查规则的复选框2）检查并依次更正常见公式错误）检查并依次更正常见公式错误“公式”选项卡“公式审核”组“错误检查”按钮 检查并处理错误。49第3章 通过 Ex

30、cel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 公式与函数常见问题公式与函数常见问题3）通过）通过“监视窗口监视窗口”监视公式及其结果监视公式及其结果使用“监视窗口”可以方便地在大型工作表中检查、审核或确认公式计算及其结果，而无需反复滚动或定位到工作表的不同部分。“公式”选项卡“公式审核”组“监视窗口”按钮“添加监视”按钮“添加”按钮，增加监视点。50第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数3.3.5 公式与函数常见问题公式与函数常见问题3. 公式中的循环引用公式中的循环引用如果公式引用了自己所在的单元格，则无论是直接引用还是间接引用，该公式都会创建循环引用。默认情况下，如果发生循环引用，Excel就会报错。1）定位并更正循环引用）定位并更正循环引用发生循环引用现象“公式”选项卡“公式审核”组“错