第3章Excel2020常用函数课件

1、第3章Excel2020常用函数2022/7/25第3章Excel2020常用函数第三章 常用函数 本章内容：介绍Excel 2007函数的基本概念及常用函数的使用方法，并举例加以说明。常用函数包括：数学和三角函数、逻辑函数、日期和时间函数、文本函数、查找和引用函数 教学目标：通过本章的学习，使同学们对Excel 2007的函数有个大致的了解，能够使用函数解决一些实际问题2第3章Excel2020常用函数第三章 常用函数 函数的概述 数学和三角函数 逻辑函数 日期和时间函数 文本函数查找和引用函数3第3章Excel2020常用函数第1节 函数的概述 函数的基本概念 函数的分类 使用函数 使用函

2、数的优势 获得Excel 函数的帮助预先定义，执行计算、分析等处理数据任务的特殊公式分为12类、356个函数直接使用、利用“插入函数”对话框简化公式、实现特殊运算、实现智能判断、提高工作效率单元格内的工具提示使用“函数参数”对话框使用Excel帮助4第3章Excel2020常用函数如果函数以公式的形式出现，请在函数名称前面键入等号 = 函数的结构 SUM(A10,B5:B10,50,37)1.1 函数的基本概念函数名参数，可以是常量、逻辑值、数组、错误值或单元格引用等 Excel 2007的函数是指预先定义好，执行计算、分析等处理数据任务的特殊公式5第3章Excel2020常用函数 函数与公式

3、的区别 函数与公式既有区别又互相联系。函数是Excel预先定义好的特殊公式，而公式由用户自行设计对工作表进行计算和处理的计算式 如：“=SUM(E1:H1)*A1+26”1.1 函数的基本概念 Excel 2007的函数是指预先定义好，执行计算、分析等处理数据任务的特殊公式函数6第3章Excel2020常用函数 89个ATP(分析工具包)函数，这些函数现在是Excel默认安装的一部分5个新的通用函数7个新的多维数据集函数，这些函数有助人们连接多维数据库 1.2 函数的分类 Excel 2007提供了356个函数。它还包含下面的内容7第3章Excel2020常用函数1.2 函数的分类 Excel

4、 2007提供了的函数。可分为12类：分 类功 能 简 介数据库函数对数据清单中的数据进行分析、查找、计算等日期与时间对日期和时间进行计算、设置及格式化处理工程函数用于工程数据分析与处理信息函数对单元格或公式中数据类型进行判定财务函数进行财务分析及财务数据的计算逻辑函数进行逻辑判定、条件检查统计函数对工作表数据进行统计、分析查找函数查找特定的数据或引用公式中的特定信息文本函数对公式、单格中的字符、文本进行格式化或运算数学函数进行数学计算等外部函数进行外部函数调用及数据库的链接查询等功能自定义函数用户用vba编写，用于完成特定功能的函数8第3章Excel2020常用函数直接使用函数 在单元格中直

5、接使用函数，是以“=”开始，然后输入函数名及函数所需要的参数，函数的参数用括号括起来，各参数之间用逗号分隔 例如：现有我国不同地区的西红柿价格数据，如果计算天津地区的西红柿平均价格 1.3 使用函数9第3章Excel2020常用函数 使用AVERAGE (number1, number2,.)函数 1.3 使用函数在要得到结果的单元格内直接输入“=AVERAGE(B3:B7)”10第3章Excel2020常用函数计算所有地区的西红柿平均价格 1.3 使用函数11第3章Excel2020常用函数利用“插入函数”对话框使用函数 1.3 使用函数选中要显示结果的单元格在“公式”选项卡“函数库”组，单

6、击“插入函数”12第3章Excel2020常用函数利用“插入函数”对话框使用函数 1.3 使用函数在“或选择类别”下拉列表中选择要使用函数所在的类别，这里选择“常用函数”，然后再“选择函数”列表中找到求平均值的函数AVERAGE13第3章Excel2020常用函数利用“插入函数”对话框使用函数 1.3 使用函数14第3章Excel2020常用函数简化公式 函数可以使一些复杂的公式更易于使用，使复杂的数学表达式输入更加简化。比如，要计算B3:D7区域中数据的平均值时，使用公式计算量大，需要写成：“=(B3+B4+B5+B6+B7+ C3+C4+C5+C6+C7+D3+D4+D5+D6+D7)/1

7、5”而使用函数将相当简单，只要写成：“=AVERAGE(B3:D7)” 1.4 使用函数的优势15第3章Excel2020常用函数实现特殊运算 在日常的工作中，有一些计算使用公式不但复杂，而且不具有特定的专业知识还无法表达，那么就可以使用现有的函数进行特殊运算 例如，按揭贷款的等额分期偿还额的计算必须用到高等数学的知识，而使用PMT函数(计算在固定利率下，贷款的等额分期偿还额)就相当简单 PMT(rate,nper,pv,fv,type) 1.4 使用函数的优势贷款利率付款期总数未来值现值付款时间16第3章Excel2020常用函数实现特殊运算 1.4 使用函数的优势贷款利率付款期总数现值17

8、第3章Excel2020常用函数实现智能判断 用函数还可以实现智能判断 例如，在学校，老师统计学生的考试成绩，如果，其成绩大于等于80则为“优秀”,如果成绩大于等于59且小于80则为“及格”,否则为“不及格”。这类问题用人工计算很不方便，可以使用函数来进行自动化处理 1.4 使用函数的优势18第3章Excel2020常用函数实现智能判断 1.4 使用函数的优势19第3章Excel2020常用函数提高工作效率 在学校的期末总结中，常会需要按学生的考试成绩进行排位的问题，当然可以使用“排序”按钮来完成，但是这样做比较麻烦，如果使用排位函数RANK就比较方便，尤其是纪录很多、相同分数很多的情况下，不

9、用RANK函数其工作量不可想像 RANK(number,ref,order)1.4 使用函数的优势需要排位的数字引用的区域，绝对地址排位的方式20第3章Excel2020常用函数提高工作效率1.4 使用函数的优势21第3章Excel2020常用函数 由于Excel 2007的函数众多，功能很强，在课中无法全面的介绍这些函数，所以我们要使用Excel 的帮助文件。Excel 2007 提供了很好的帮助文件。Excel有三个级别的帮助：单元格内的工具提示、“函数参数”对话框和Excel的帮助。函数参数对话框是寻找帮助的最好办法。使用单元格内的工具提示 在任何单元格内，都可以输入等号、函数名称和左括

10、号。Excel的工具提示将显示预期的参数1.5 获得Excel 函数的帮助22第3章Excel2020常用函数使用“函数参数”对话框 “函数参数”对话框可以逐步帮助你建立函数1.5 获得Excel 函数的帮助每个独立参数的结果当前参数的介绍函数的描述要求的参数函数的初步结果帮助的链接23第3章Excel2020常用函数使用Excel帮助 有关函数的帮助非常全面。在每个函数的帮助主题中都可以找到下面的内容 函数的语法 “注释”提供了函数的疑难解答 实例 相关函数1.5 获得Excel 函数的帮助24第3章Excel2020常用函数 数学函数 基本数学函数 舍人和取整函数 求和函数 三角函数第2节

11、 数学和三角函数25第3章Excel2020常用函数2.1 数学函数-基本数学函数ABS计算数值的绝对值COMBIN计算指定元素数目的组合数FACT计算给定正整数的阶乘MOD计算两数相除的余数，其结果的正负号与除数相同PI 计算圆周率，精确到小数点后14位POWER计算给定数字的乘幂。PRODUCT 计算所有参数的乘积RAND产生一个大于等于0小于1的随机数RANDBETWEEN产生位于两个指定数值之间的一个随机数 SIGN 返回数值的符号。正数返回1，零返回0，负数时返回-1SQRT计算某一正数的算术平方根EXP计算e的n次幂LN、LOG、LOG10 计算对数值MDETERM 计算一个矩阵行

12、列式的值26第3章Excel2020常用函数2.1 数学函数-基本数学函数 举例：用函数RAND函数为学生随机排号 学校组织考试时，经常需要将学生的学号秩序打乱，并随机编排考号，使用RAND随机函数可以方便的实现以上功能。 RAND函数的格式： RAND()在单元格D2内输入公式“=RAND()”并向下填充，这样在D列各单元格内生存了0-1之间的任意数27第3章Excel2020常用函数2.1 数学函数-基本数学函数 举例：用函数RAND函数为学生随机排号 按列D “排序”,可以看到B、C二列已无序排列,再在A2单元格中输入第一个考号,并按序列向下填充,将D列删除或清空数据28第3章Excel

13、2020常用函数2.1 数学函数-舍人和取整函数INT 将数值向下舍入计取整ODD、EVEN 将数值舍入为奇或偶数TRUNC 将数值的小数部分截去，返回整数ROUND 按指定位数四舍五入数值ROUNDUP 、ROUNDDOWN 按指定位数向上或向下四舍五入数值29第3章Excel2020常用函数2.1 数学函数-舍人和取整函数 举例: odd, even 功能：将数据舍入为奇数或偶数 odd(n) : 将n向绝对值增大方向舍入为最接近的奇数 even(n): 将n向绝对值增大方向舍入为最接近的偶数 odd(31.3) 产生结果 33 even(-5.4) 产生结果 -630第3章Excel20

14、20常用函数2.1 数学函数-求和函数SUBTOTAL返回数据清单或数据库中的分类汇总SUM计算某一单元格区域中所有数值之和SUMIF根据指定条件对若干单元格、区域或引用求和SUMPRODUCT计算数组间对应的元素相乘，并返回乘积之和SUMSQ计算所有参数的平方和SUMX2MY2计算两数组中对应数值的平方差之和SUMX2PY2计算两数组中对应数值的平方和的总和SUMXMY2计算两数组中对应数值之差的平方和31第3章Excel2020常用函数2.1 数学函数-求和函数 举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和32第3章Excel2020常用函数2.1 数学函数-求和函数 举例：某

15、学校的考试成绩统计表，要统计女同学的英语成绩总和。此例问题可以使用SUMIF函数 SUMIF(range，criteria，sum_range)条件判断的单元格区域判定条件需要求和的单元格、区域或引用33第3章Excel2020常用函数2.1 数学函数-求和函数 举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和。此例问题可以使用SUMIF函数 在求和的单元格输入公式： “=SUMIF(B2:B4,”=女”,F2:F9)” 即得到结果34第3章Excel2020常用函数2.1 数学函数-求和函数 举例：COUNTIF 功能：为满足指定条件的单元计数 格式：Countif( 计数区域, “

16、计数条件” ) = countif（D2:D9,“80”）35第3章Excel2020常用函数2.2 三角函数DEGREES 将弧度转换为角度RADIANS 角度转换为弧度SIN 求正弦值 ASIN 求反正弦值 COS 求余弦值 ACOS 求反余弦值 TAN 求正切值 ATAN 求反正切值 36第3章Excel2020常用函数 逻辑函数是用来判断真假值，或者进行复合检验的Excel函数。在Excel中提供了七种逻辑函数第3节 逻辑函数AND 当AND的参数全部为TRUE时，返回结果为TRUE，否则为FALSE OR 当OR的参数中，如果任一参数为TRUE，返回结果 TRUE，否则为FALSE

17、NOT NOT函数用于对参数值求反TRUE 返回逻辑值 TRUE FALSE 返回逻辑值 FALSE IF 对指定的条件计算结果为 TRUE 或 FALSE，返回不同的结果IFERROR 如果公式计算出错误则返回您指定的值；否则返回公式结果37第3章Excel2020常用函数第3节 逻辑函数 举例：IF 在学校的管理工作中，常常要对学生的考试成绩进行统计分析。假定现有某班级的考试成绩，我们按照各科平均分对学生的考试成绩进行综合评定。评定规则：如果平均分超过90时，评定为优秀，如果平均分小于90并且超过60则认为是合格的，否则记作不合格38第3章Excel2020常用函数第3节 逻辑函数 举例：

18、根据规则，我们在综合评定中使用IF函数IF(logical_test,value_if_true,value_if_false)logical_test 为 TRUE 时返回的值测试表达式logical_test 为 FALSE时返回的值39第3章Excel2020常用函数第3节 逻辑函数 举例：根据规则，我们在综合评定中使用IF函数 (以B7为例）： “ =IF(B660,IF(B690,”优秀“,”合格“),”不合格“)”40第3章Excel2020常用函数第3节 逻辑函数不及格E260E280E290E270及格中等良好优秀为英语成绩建立五级分转换1、if(E260,if(E270,if

19、(E280, if(E290,优秀，良好)，中等)，及格)，不及格）41第3章Excel2020常用函数第3节 逻辑函数 举例：OR AND OR (logical1,logical2,logicaln) 功能：当只要有一个参数的计算结果为TRUE时,OR函数的计算结果为TRUE, 否则，计算结果为FALSE AND (logical1,logical2,logicaln) 功能：当所有参数的计算结果都是TRUE时,AND函数的计算结果为TRUE, 否则，计算结果为FALSE42第3章Excel2020常用函数第3节 逻辑函数 举例：OR AND43第3章Excel2020常用函数第3节 逻辑

20、函数 举例：OR AND44第3章Excel2020常用函数第4节日期和时间函数DATA返回当前日期和时间的序列号NOW返回当前日期和时间的序列号TODAY返回当前日期的序列号YEAR返回某日期对应的年份MONTH返回以序列号表示的日期中的月份DAY返回以序列号表示的某日期的天数HOUR返回时间值的小时数45第3章Excel2020常用函数第4节日期和时间函数 举例:显示当前的日期与时间46第3章Excel2020常用函数第5节 文本函数LOWER将一个文字串中的所有大写字母转换为小写字母，但不改变文本中的非字母的字符UPPER将文本转换成大写形式PROPER 将文本字符串的首字母及任何非字母

21、字符之后的首字母转换成大写。将其余的字母转换成小写LEFT根据所指定的字符数，返回文本字符串中左边的几个字符MID返回文本字符串中从指定位置开始的特定数目的字符RIGHT根据所指定的字符数，返回文本字符串中右边的几个字符 Trim清除字符串中的空白。 EXACT 比较两个字符串，如果它们完全相同，则返回 TRUE；否则，返回 FALSE47第3章Excel2020常用函数第5节 文本函数 举例：LEFT RIGHT MID LEFT功能：返回文本字符串中左边的几个字符语法：LEFT(text,num_chars)参数：text是包含要提取字符的文本串。num_chars指定要由 LEFT 所提取的字符数 MID功能：返回文本字符串中从指定位置开始的特定数目的字符语法：MID(text,start_num,num_chars)参数：text是包含要提取字符的文本串。start_num是文本中要提取的第一个字符的位置。num_chars指定从文本串中返回字符的个数48第3章Excel2020常用函数第5节 文本函数 举例：LEFT RIGHT MID RIGHT功能：返回文本字符串中右边的几个字符语法：RIGHT(text,num_chars)参数：text是包含要提取字符的文本串。num