3.3_Excel公式和函数 二级office官方课件

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

2、工工资表的处理，帮助书娟实现以下工作目标：了解公式和函数的根本作用，了解在公式中各种引用的含义及方法。掌握公式的构成、输入方法，学会构建公式以帮助计算。了解 Excel 提供了哪些类型的函数，掌握函数的根本输入方法。掌握 Exce l中常用、重要函数的使用方法，并在实际工作中应用它们。了解公式及函数应用过程中各种常见问题及解决方法。33.3.1 使用公式的根本方法1. 认识公式公式是一组表达式，由单元格引用、常量、运算符、括号组成，复杂的公式还可以包括函数，用于计算生成新的值。在Excel中，公式总是以等号“=开始。单元格引用：即单元格地址，用于表示单元格在工作表上所处位置的坐标。例如，显示在

3、第B列和第3行交叉处的单元格，其引用形式为“B3。常量：固定的数值或文本。例如，数字“210和文本“姓名均为常量。运算符：运算符用于连接常量、单元格引用，从而构成完整的表达式。常用的运算符有：算术运算符，字符连接符，关系运算符。43.3 Excel 公式和函数3.3.1 使用公式根本方法2. 公式的输入与编辑1输入公式=常量/单元格引用/表达式必须以等号“=开始，在公式中所使用的运算符都必须是西文的半角字符例如：=30，=B3，=A5*10%，=C8&C953.3 Excel 公式和函数3.3.1 使用公式根本方法2修改公式用鼠标双击公式进入编辑状态，在单元格或者编辑栏中均可对公式进行修改即可

4、。删除公式：按 Del 键。3. 公式的复制与填充拖动公式单元格右下角的填充柄“开始选项卡“编辑组“填充按钮。进行公式的复制填充时，填充的实际上是公式而非数据本身，填充时公式中对单元格的引用采用的是相对引用。63.3 Excel 公式和函数3.3.1 使用公式根本方法4. 单位格引用在公式中最常用的是单元格引用。可以在单元格中引用一个单元格、一个单元格区域、引用另一个工作表或工作簿中的单元格或区域。单元格引用方式分为以下几类：相对引用：如“=A1。绝对引用：如“=$A$1 。混合引用：如“=A$1、“=$A1、 “=$A$1 73.3 Excel 公式和函数3.3.2 名称的定义与引用为单元格

5、或区域指定一个名称，是实现绝对引用的方法之一。可以定义为名称的对象包括：常量、单元格或单元格区域、公式。1. 了解名称的语法规那么唯一性原那么：名称在其适用范围内必须始终唯一，不可重复。有效字符：名称中第一个字符必须是字母、下划线 (_) 或反斜杠 ()，名称中不能使用大小写字母“C、“c、“R或“r。不能与单元格地址相同：例如，名称是A1、B$2等。不能使用空格：可选用下划线 (_) 和句点 (.) 作为单词分隔符。名称长度有限制：一个名称最多可以包含 255 个西文字符。不区分大小写：例如，Sales与SALES视为同名。83.3 Excel 公式和函数3.3.2 名称的定义与引用2. 为

6、单元格或单元格区域定义名称定义好的名称将会在公式及函数中被引用。1快速定义名称选择要命名的单元格或单元格区域，在编辑栏的“名称框中输入名称后按 Enter键确认。在案例工作簿文档“3.3 员工档案及工资表的“员工档案表工作表中，将数据列表区域A3:N38的名称定义为“全体员工资料。93.3 Excel 公式和函数3.3.2 名称的定义与引用2将现有行和列标题转换为名称选择要命名的区域，必须包括行或列标题“公式选项卡“定义的名称组“从所选内容创立按钮在对话框中，通过选中“首行、“左列、“末行或“右列复选框来指定包含标题的位置。在案例工作簿文档“3.3 员工档案及工资表的“员工档案表中，将“根本工

7、资和“工龄工资两列的首行转换为相应列数据的名称。103.3 Excel 公式和函数3.3.2 名称的定义与引用3使用“新名称对话框定义名称“公式选项卡“定义的名称组“定义名称按钮在“名称文本框中输入名称 在“范围下设定名称的适用范围在“备注说明性批注。 “引用位置框中修改命名对象可以是单元格区域、常量、公式，可选择以下操作之一。在案例工作簿文档“3.3 员工档案及工资表的“员工档案表中，将工龄工资常量“50元定义为名称“工龄工资_每年。113.3 Excel 公式和函数3.3.2 名称的定义与引用3. 引用名称名称可直接用来快速选定已命名的区域，更重要的是可以在公式中引用名称以实现精确引用。1

8、通过“名称框引用单击编辑栏中“名称框右侧的黑色箭头，翻开“名称下拉列表单击选择某一名称提示：通过名称框翻开的“名称下拉列表中不包括常量和公式的名称。123.3 Excel 公式和函数3.3.2 名称的定义与引用2在公式中引用“公式选项卡“定义的名称组“用于公式按钮选择名称4. 更改或删除名称如果更改了某个已定义的名称，那么工作簿中所有已引用该名称的位置均会自动随之更新。更改名称：“公式选项卡“定义的名称组“名称管理器按钮在名称列表中选择要更改的名称“编辑按钮 在“编辑名称对话框中修改名称属性。删除名称： “公式选项卡“定义的名称组“名称管理器按钮在名称列表中选择名称“删除按钮。提示：假设公式中

9、已引用的某个名称被删除，可能导致公式出错。133.3 Excel 公式和函数3.3.3 使用函数的根本方法1. 认识函数函数实际上特殊的公式，主要是为解决那些复杂计算需求而提供的一种预置算法，如求和函数SUM，平均值函数EVERAGE，条件函数 IF等。函数通常表示为：函数名参数1, 参数2,函数中的参数可以是常量、单元格地址、数组、已定义的名称、公式、函数等。输入函数时必须以等号“=开始。2. Excel 函数分类Excel 提供大量工作表函数，并按其功能进行分类。Excel 2021 目前默认提供的函数类别共13大类，见表3.3中所列。143.3 Excel 公式和函数表 3.3 Exce

10、l 2021函数类别函数类别常用函数示例及说明财务函数NPV(rate,value1,value2,.) 返回一项投资的净现值。日期和时间函数YEAR(serial_number) 返回某日期对应的年份数学和三角函数INT(number) 将数字向下舍入到最接近的整数。统计函数AVERAGE(number1, number2, .) 返回参数的算术平均值查找和引用函数VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) 搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值数据库函数DCOUNTA(datab

11、ase, 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) 将数字从一个度量系统转换到另一个度量系

12、统中。兼容性函数RANK(number,ref,order) 返回一个数字在数字列表中的排位多维数据集函数CUBEVALUE(connection,member_expression1,member_expression2) 从多维数据集中返回汇总值与加载项一起安装的用户自定义函数如果在系统中安装了某一包含函数的应用程序，该程序做为Excel的加载项，其所包含的函数作用自定义函数显示在这里以供选用。153.3 Excel 公式和函数3.3.3 使用函数的根本方法3. 函数的输入与编辑函数的输入方式与公式类似，可以直接在单元格中输入“=函数名(所引用的参数)，但是要想记住每一个函数名并正确输入所

13、有参数是有相当困难的。因此，通常情况采用参照的方式输入一个函数。1通过“函数库组插入“公式选项卡“函数库组中的某一函数类别 从函数列表中单击函数在“函数参数对话框中输入或选择参数163.3 Excel 公式和函数3.3.3 使用函数的根本方法2通过“插入函数按钮插入“公式选项卡 “函数库组 “插入函数按钮，翻开“插入函数对话框 在“选择类别下拉表中选择函数类别，或者在“搜索函数框中输入函数的简单描述后单击“转到按钮 在“选择函数列表中选择函数在“函数参数对话框中输入参数。3修改函数在包含函数的单元格的双击鼠标，进入编辑状态，对函数参数进行修改后按Enter键确认。173.3 Excel 公式和

14、函数3.3.4 Excel 中常用函数的应用1. Excel中常用函数简介1求和函数 SUM(number1,number2,.)功能：将指定的参数number1、number2相加求和。例如：=SUM(A1:A5) 是将单元格A1至A5中的所有数值相加=SUM(A1, A3, A5) 是将单元格A1、A3和A5中的数字相加。183.3 Excel 公式和函数3.3.4 Excel 中常用函数的应用2条件求和函数SUMIF(range, criteria, sum_range)功能：对指定单元格区域中符合指定条件的值求和。提示：在函数中任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号

15、 () 括起来。如果条件为数字，那么无需使用双引号。例如：=SUMIF(B2:B25,5) 表示对B2:B25区域大于5的数值进行相加；=SUMIF(B2:B5, John, C2:C5)，表示对单元格区域C2:C5中与单元格区域B2:B5中等于“John的单元格对应的单元格中的值求和。193.3 Excel 公式和函数3.3.4 Excel中常用函数的应用3多条件求和函数SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .)功能：对指定单元格区域中满足多个条件的单元格求和。例如：=SUMIFS(

16、A1:A20, B1:B20, 0, C1:C20, =60,及格,不及格) 表示，如果单元格A2中的值大于等于60，那么显示“及格字样，否那么显示“不及格字样；=IF(A2=90,优秀,IF(A2=80,良好, IF(A2=60,及格 ,不及格) 表示以下对应关系：263.3 Excel 公式和函数单元格A2中的值公式单元格显示的内容A2=90优秀90A2=80良好80A2=60及格A260不及格3.3.4 Excel中常用函数的应用10当前日期和时间函数 NOW()功能：返回当前计算机系统的日期和时间。当将数据格式设置为数值时，将返回当前日期和时间所对应的序列号，该序列号的整数局部说明其与

17、1900年1月1日之间的天数。11函数 YEAR(serial_number) 功能：返回指定日期对应的年份。返回值为 1900 到 9999 之间的整数例如：=YEAR(A2) 当在A2单元格中输入日期2021/12/27时，该函数返回年份2021。注意：公式所在的单元格不能是日期格式。273.3 Excel 公式和函数3.3.4 Excel中常用函数的应用12当前日期函数 TODAY()功能：返回今天的日期。通过该函数，可以实现无论何时翻开工作簿时工作表上都能显示当前日期；该函数也可以用于计算时间间隔，可以用来计算一个人的年龄。例如：=YEAR(TODAY()-1963 假设一个人出生在1

18、963年，该公式使用TODAY函数作为YEAR函数的参数来获取当前年份，然后减去1963，最终返回对方的年龄。283.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中的数值求平均值。293.3 Excel 公式和函数3.3.4 Excel中常用函数的应用14条件平均值函

19、数AVERAGEIF(range, criteria, average_range) 功能：对指定区域中满足给定条件的所有单元格中的数值求算术平均值例如：=AVERAGEIF(A2:A5,5000,B2:B5) 表示对单元格区域B2:B5中与单元格区域A2:A5中大于5000的单元格所对应的单元格中的值求平均值。303.3 Excel 公式和函数3.3.4 Excel中常用函数的应用15多条件平均值函数AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, .) 功能：对指定区域中满足多

20、个条件的所有单元格中的数值求算术平均值例如：=AVERAGEIFS(A1:A20,B1:B20,70,C1:C20, 55) 表示统计单元格区域B2到B5中值大于55的单元格的个数。333.3 Excel 公式和函数3.3.4 Excel中常用函数的应用19多条件计数函数COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2) 功能：统计指定区域内符合多个给定条件的单元格的数量。可以将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。例如：=COUNTIFS(A2:A7, 80,B2:B7,0,I4-J4-3500,

21、0)；也可以通过绝对引用根底数据表中的减除标准构建函数“=IF(I5-J5-根底数据!$F$12)0,I5-J5-根底数据!$F$12,0)463.3 Excel 公式和函数3.3.4 Excel中常用函数的应用 计算个人所得税通过多级IF函数嵌套，可构建出个人所得税计算公式，并通过ROUND函数对计算结果保存2位小数。个人所得税税率表可参见“根底数据表中所列。=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

22、,IF(K4=80000,K4*0.35-5505,K4*0.45-13505),2) 计算实付工资实付工资应付工资合计-扣除社保-应交个人所得税。473.3 Excel 公式和函数3.3.5 公式与函数常见问题在输入公式或函数的过程中，当输入有误时，单元格中常常会出现各种不同的错误结果。对这些提示的含义有所了解，有助于更好地发现并修正公式或函数中的错误。1. 常见错误值列表483.3 Excel 公式和函数表3.4 公式或函数中的常见错误列表错误显示说 明#当某一列的宽度不够而无法在单元格中显示所有字符时，或者单元格包含负的日期或时间值时，Excel将显示此错误。 #DIV/0!当一个数除以

23、零 (0) 或不包含任何值的单元格时，Excel将显示此错误。#N/A当某个值不允许被用于函数或公式但却被其引用时，Excel 将显示此错误。#NAME? 当Excel无法识别公式中的文本时，将显示此错误。例如，区域名称或函数名称拼写错误，或者删除了某个公式引用的名称。#NULL!当指定两个不相交的区域的交集时，Excel将显示此错误。交集运算符是分隔公式中的两个区域地址间的空格字符。例如，区域A1:A2和C3:C5不相交，因此，输入公式 =SUM(A1:A2 C3:C5) 将返回 #NULL! 错误。#NUM!当公式或函数包含无效数值时，Excel将显示此错误。#REF!当单元格引用无效时，

24、Excel将显示此错误。例如，如果删除了某个公式所引用的单元格，该公式将返回 #REF! 错误。#VALUE!如果公式所包含的单元格有不同的数据类型，则Excel将显示此错误。如果启用了公式的错误检查，则屏幕提示会显示“公式中所用的某个值是错误的数据类型”。通常，通过对公式进行较少更改即可修复此问题。493.3 Excel 公式和函数3.3.5 公式与函数常见问题2. 审核和更正公式中的错误1翻开或关闭错误检查规那么“文件选项卡“选项命令翻开“Excel选项对话框从左侧类别列表中单击“公式选项在“错误检查规那么区域中，选中或去除某一检查规那么的复选框2检查并依次更正常见公式错误“公式选项卡“公式审核组“错误检查按钮 检查并处理错误。503.3 Excel 公式和函数3.3.5 公式与函数常见问题3通过“监视窗口监视公式及其结果使用“监视窗口可以方便地在大型工作表中检查、审核或确认公式计算及其结果，而无需反复滚动或定位到工作表的不同局部。“公式选项卡“公式审核组“监视窗口按钮“添加监视按钮“添加按钮，增加监视点。513.3 Excel 公式和函数3.3.5 公式与函数常见问题3. 公式中的循环引用如果公式引用了自己所在的单元格，那么无论是直接引用还是间接引用