1、2022-8-5第3章Excel2020常用函数第第3章章Excel2020常用函常用函数数第3章Excel2020常用函数第三章第三章 常用函数常用函数u 本章内容：本章内容：介绍Excel 2007函数的基本概念及常用函数的使用方法，并举例加以说明。常用函数包括：数学和三角函数、逻辑函数、日期和时间函数、文本函数、查找和引用函数 u 教学目标：教学目标：通过本章的学习，使同学们对Excel 2007的函数有个大致的了解，能够使用函数解决一些实际问题第3章Excel2020常用函数第三章第三章 常用函数常用函数u 函数的概述函数的概述u 数学和三角函数数学和三角函数u 逻辑函数逻辑函数u 日
2、期和时间函数日期和时间函数u 文本函数文本函数u查找和引用函数查找和引用函数第3章Excel2020常用函数第第1节节 函数的概述函数的概述u 函数的基本概念函数的基本概念u 函数的分类函数的分类u 使用函数使用函数u 使用函数的优势使用函数的优势u 获得获得Excel 函数的帮助函数的帮助预先定义，执行计算、分预先定义，执行计算、分析等处理数据任务的特殊公析等处理数据任务的特殊公式式分为分为1212类、类、356356个函数个函数直接使用、利用直接使用、利用“插入函数插入函数”对话对话框框简化公式、实现特殊运算、实简化公式、实现特殊运算、实现智能判断、提高工作效率现智能判断、提高工作效率单元
3、格内的工具提示单元格内的工具提示使用使用“函数参数函数参数”对话框对话框使用使用ExcelExcel帮助帮助第3章Excel2020常用函数如果函数以公式的形式出现，请在函数名称前面键入等号 u 函数的结构函数的结构 SUM(A10,B5:B10,50,37)1.1 函数的基本概念函数的基本概念函数名函数名参数，可以是常量、逻辑值、参数，可以是常量、逻辑值、数组、错误值或单元格引用等数组、错误值或单元格引用等 Excel 2007的函数是指预先定义好，执行计算、分析等处理数据任务的特殊公式第3章Excel2020常用函数u 函数与公式的区别函数与公式的区别 函数与公式既有区别又互相联系。函数是
4、Excel预先定义好的特殊公式，而公式由用户自行设计对工作表进行计算和处理的计算式 如：“=SUM(E1:H1)*A1+26”1.1 函数的基本概念函数的基本概念 Excel 2007的函数是指预先定义好，执行计算、分析等处理数据任务的特殊公式函数函数第3章Excel2020常用函数u 89个ATP(分析工具包)函数，这些函数现在是Excel默认安装的一部分u5个新的通用函数u7个新的多维数据集函数，这些函数有助人们连接多维数据库 1.2 函数的分类函数的分类 Excel 2007提供了356个函数。它还包含下面的内容第3章Excel2020常用函数1.2 函数的分类函数的分类 Excel 2
5、007提供了的函数。可分为12类：分分 类类功功 能能 简简 介介数据库函数数据库函数对数据清单中的数据进行分析、查找、计算等对数据清单中的数据进行分析、查找、计算等日期与时间日期与时间对日期和时间进行计算、设置及格式化处理对日期和时间进行计算、设置及格式化处理工程函数工程函数用于工程数据分析与处理用于工程数据分析与处理信息函数信息函数对单元格或公式中数据类型进行判定对单元格或公式中数据类型进行判定财务函数财务函数进行财务分析及财务数据的计算进行财务分析及财务数据的计算逻辑函数逻辑函数进行逻辑判定、条件检查进行逻辑判定、条件检查统计函数统计函数对工作表数据进行统计、分析对工作表数据进行统计、分
6、析查找函数查找函数查找特定的数据或引用公式中的特定信息查找特定的数据或引用公式中的特定信息文本函数文本函数对公式、单格中的字符、文本进行格式化或运算对公式、单格中的字符、文本进行格式化或运算数学函数数学函数进行数学计算等进行数学计算等外部函数外部函数进行外部函数调用及数据库的链接查询等功能进行外部函数调用及数据库的链接查询等功能自定义函数自定义函数用户用用户用vba编写，用于完成特定功能的函数编写，用于完成特定功能的函数第3章Excel2020常用函数u直接使用函数直接使用函数 在单元格中直接使用函数，是以“=”开始，然后输入函数名及函数所需要的参数，函数的参数用括号括起来，各参数之间用逗号分
7、隔 例如：现有我国不同地区的西红柿价格数据，如果计算天津地区的西红柿平均价格 1.3 使用函数使用函数第3章Excel2020常用函数 使用AVERAGE(number1,number2,.)函数 1.3 使用函数使用函数在要得到结果的单元格内直接输入“=AVERAGE(B3:B7)”第3章Excel2020常用函数计算所有地区的西红柿平均价格 1.3 使用函数使用函数第3章Excel2020常用函数u利用利用“插入函数插入函数”对话框使用函数对话框使用函数 1.3 使用函数使用函数选中要显示结果的单元格在“公式”选项卡“函数库”组，单击“插入函数”第3章Excel2020常用函数u利用利用“
8、插入函数插入函数”对话框使用函数对话框使用函数 1.3 使用函数使用函数在“或选择类别”下拉列表中选择要使用函数所在的类别，这里选择“常用函数”，然后再“选择函数”列表中找到求平均值的函数AVERAGE第3章Excel2020常用函数u利用利用“插入函数插入函数”对话框使用函数对话框使用函数 1.3 使用函数使用函数第3章Excel2020常用函数u简化公式简化公式 函数可以使一些复杂的公式更易于使用，使复杂的数学表达式输入更加简化。比如，要计算B3:D7区域中数据的平均值时，使用公式计算量大，需要写成：“=(B3+B4+B5+B6+B7+C3+C4+C5+C6+C7+D3+D4+D5+D6+
9、D7)/15”而使用函数将相当简单，只要写成：“=AVERAGE(B3:D7)”1.4 使用函数的优势使用函数的优势第3章Excel2020常用函数u实现特殊运算实现特殊运算 在日常的工作中，有一些计算使用公式不但复杂，而且不具有特定的专业知识还无法表达，那么就可以使用现有的函数进行特殊运算 例如，按揭贷款的等额分期偿还额的计算必须用到高等数学的知识，而使用PMT函数(计算在固定利率下，贷款的等额分期偿还额)就相当简单 PMT(rate,nper,pv,fv,type)1.4 使用函数的优势使用函数的优势贷款利贷款利率率付款期总付款期总数数未来未来值值现值现值付款时付款时间间第3章Excel2
10、020常用函数u实现特殊运算实现特殊运算 1.4 使用函数的优势使用函数的优势贷款利贷款利率率付款期总数付款期总数现值现值第3章Excel2020常用函数u实现智能判断实现智能判断 用函数还可以实现智能判断 例如，在学校，老师统计学生的考试成绩，如果，其成绩大于等于80则为“优秀”,如果成绩大于等于59且小于80则为“及格”,否则为“不及格”。这类问题用人工计算很不方便，可以使用函数来进行自动化处理 1.4 使用函数的优势使用函数的优势第3章Excel2020常用函数u实现智能判断实现智能判断 1.4 使用函数的优势使用函数的优势第3章Excel2020常用函数u提高工作效率提高工作效率 在学
11、校的期末总结中，常会需要按学生的考试成绩进行排位的问题，当然可以使用“排序”按钮来完成，但是这样做比较麻烦，如果使用排位函数RANK就比较方便，尤其是纪录很多、相同分数很多的情况下，不用RANK函数其工作量不可想像 RANK(number,ref,order)1.4 使用函数的优势使用函数的优势需要排位的数字需要排位的数字引用的区域，绝对地址引用的区域，绝对地址排位的方式排位的方式第3章Excel2020常用函数u提高工作效率提高工作效率1.4 使用函数的优势使用函数的优势第3章Excel2020常用函数 由于Excel 2007的函数众多，功能很强，在课中无法全面的介绍这些函数，所以我们要使
12、用Excel 的帮助文件。Excel 2007 提供了很好的帮助文件。Excel有三个级别的帮助：单元格内的工具提示、“函数参数”对话框和Excel的帮助。函数参数对话框是寻找帮助的最好办法。u使用单元格内的工具提示使用单元格内的工具提示 在任何单元格内，都可以输入等号、函数名称和左括号。Excel的工具提示将显示预期的参数1.5 获得获得Excel 函数的帮助函数的帮助第3章Excel2020常用函数u使用使用“函数参数函数参数”对话框对话框 “函数参数”对话框可以逐步帮助你建立函数1.5 获得获得Excel 函数的帮助函数的帮助每个独立参数的结果每个独立参数的结果当前参数的介绍当前参数的介
13、绍函数的描述函数的描述要求的参数要求的参数函数的初步结果函数的初步结果帮助的链接帮助的链接第3章Excel2020常用函数u使用使用Excel帮助帮助 有关函数的帮助非常全面。在每个函数的帮助主题中都可以找到下面的内容 函数的语法 “注释”提供了函数的疑难解答 实例 相关函数1.5 获得获得Excel 函数的帮助函数的帮助第3章Excel2020常用函数u 数学函数数学函数 基本数学函数 舍人和取整函数 求和函数 u 三角函数三角函数第第2节节 数学和三角函数数学和三角函数第3章Excel2020常用函数2.1 数学函数数学函数-基本数学函数基本数学函数ABS计算数值的绝对值COMBIN计算指
14、定元素数目的组合数FACT计算给定正整数的阶乘MOD计算两数相除的余数，其结果的正负号与除数相同PI 计算圆周率，精确到小数点后14位POWER计算给定数字的乘幂。PRODUCT 计算所有参数的乘积RAND产生一个大于等于0小于1的随机数RANDBETWEEN产生位于两个指定数值之间的一个随机数 SIGN 返回数值的符号。正数返回1，零返回0，负数时返回-1SQRT计算某一正数的算术平方根EXP计算e的n次幂LN、LOG、LOG10 计算对数值MDETERM 计算一个矩阵行列式的值第3章Excel2020常用函数2.1 数学函数数学函数-基本数学函数基本数学函数 举例：举例：用函数RAND函数
15、为学生随机排号排号 学校组织考试时，经常需要将学生的学号秩序打乱，并随机编排考号，使用RAND随机函数可以方便的实现以上功能。RAND函数的格式：RAND()在单元格D2内输入公式“=RAND()”并向下填充，这样在D列各单元格内生存了0-1之间的任意数第3章Excel2020常用函数2.1 数学函数数学函数-基本数学函数基本数学函数 举例：举例：用函数RAND函数为学生随机排号 按列D“排序”,可以看到B、C二列已无序排列,再在A2单元格中输入第一个考号,并按序列向下填充,将D列删除或清空数据第3章Excel2020常用函数2.1 数学函数数学函数-舍人和取整函数舍人和取整函数INT 将数值
16、向下舍入计取整ODD、EVEN 将数值舍入为奇或偶数TRUNC 将数值的小数部分截去，返回整数ROUND 按指定位数四舍五入数值ROUNDUP、ROUNDDOWN 按指定位数向上或向下四舍五入数值第3章Excel2020常用函数2.1 数学函数数学函数-舍人和取整函数舍人和取整函数 举例举例:odd,even 功能：将数据舍入为奇数或偶数功能：将数据舍入为奇数或偶数 odd(n):将将n向绝对值增大方向舍入为最接近的奇数向绝对值增大方向舍入为最接近的奇数 even(n):将将n向绝对值增大方向舍入为最接近的偶数向绝对值增大方向舍入为最接近的偶数 odd(31.3)产生结果产生结果 33 eve
17、n(-5.4)产生结果产生结果 -6第3章Excel2020常用函数2.1 数学函数数学函数-求和函数求和函数SUBTOTAL返回数据清单或数据库中的分类汇总SUM计算某一单元格区域中所有数值之和SUMIF根据指定条件对若干单元格、区域或引用求和SUMPRODUCT计算数组间对应的元素相乘，并返回乘积之和SUMSQ计算所有参数的平方和SUMX2MY2计算两数组中对应数值的平方差之和SUMX2PY2计算两数组中对应数值的平方和的总和SUMXMY2计算两数组中对应数值之差的平方和第3章Excel2020常用函数2.1 数学函数数学函数-求和函数求和函数 举例：举例：某学校的考试成绩统计表，要统计女
18、同学的英语成绩总和第3章Excel2020常用函数2.1 数学函数数学函数-求和函数求和函数 举例：举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和。此例问题可以使用SUMIF函数 SUMIF(range，criteria，sum_range)条件判断的单元格区域条件判断的单元格区域判定条件判定条件需要求和的单元格、区域或引用需要求和的单元格、区域或引用第3章Excel2020常用函数2.1 数学函数数学函数-求和函数求和函数 举例：举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和。此例问题可以使用SUMIF函数 在求和的单元格输入公式：在求和的单元格输入公式：“=SUMIF(
19、B2:B4,”=女女”,F2:F9)”即得到结果即得到结果第3章Excel2020常用函数2.1 数学函数数学函数-求和函数求和函数 举例：举例：COUNTIF 功能：为满足指定条件的单元计数功能：为满足指定条件的单元计数 格式：格式：Countif(计数区域计数区域,“计数条件计数条件”)=countif（D2:D9,“80”）第3章Excel2020常用函数2.2 三角函数三角函数DEGREES 将弧度转换为角度RADIANS 角度转换为弧度SIN 求正弦值 ASIN 求反正弦值 COS 求余弦值 ACOS 求反余弦值 TAN 求正切值 ATAN 求反正切值 第3章Excel2020常用函
20、数 逻辑函数是用来判断真假值，或者进行复合检验的Excel函数。在Excel中提供了七种逻辑函数第第3节节 逻辑函数逻辑函数AND 当AND的参数全部为TRUE时，返回结果为TRUE，否则为FALSE OR 当OR的参数中，如果任一参数为TRUE，返回结果 TRUE，否则为FALSE NOT NOT函数用于对参数值求反TRUE 返回逻辑值 TRUE FALSE 返回逻辑值 FALSE IF 对指定的条件计算结果为 TRUE 或 FALSE，返回不同的结果IFERROR 如果公式计算出错误则返回您指定的值；否则返回公式结果第3章Excel2020常用函数第第3节节 逻辑函数逻辑函数 举例举例：I
21、F 在学校的管理工作中，常常要对学生的考试成绩进行统计分析。假定现有某班级的考试成绩，我们按照各科平均分对学生的考试成绩进行综合评定。评定规则：如果平均分超过90时，评定为优秀，如果平均分小于90并且超过60则认为是合格的，否则记作不合格第3章Excel2020常用函数第第3节节 逻辑函数逻辑函数 举例举例：根据规则，我们在综合评定中使用IF函数IF(logical_test,value_if_true,value_if_false)logical_test 为 TRUE 时返回的值测试表达式测试表达式logical_test 为 FALSE时返回的值第3章Excel2020常用函数第第3节节
22、 逻辑函数逻辑函数 举例举例：根据规则，我们在综合评定中使用IF函数 (以B7为例）：“=IF(B660,IF(B690,”优秀“,”合格“),”不合格“)”第3章Excel2020常用函数第第3节节 逻辑函数逻辑函数E260E280E290E270为英语成绩建立五级分转换为英语成绩建立五级分转换1、if(E260,if(E270,if(E280,if(E290,优秀，良好优秀，良好)，中等，中等)，及格，及格)，不及格），不及格）第3章Excel2020常用函数第第3节节 逻辑函数逻辑函数 举例举例：OR AND OR(logical1,logical2,logicaln)功能功能：当只要有
23、一个参数的计算结果为TRUE时,OR函数的计算结果为TRUE,否则，计算结果为FALSE AND(logical1,logical2,logicaln)功能功能：当所有参数的计算结果都是TRUE时,AND函数的计算结果为TRUE,否则，计算结果为FALSE第3章Excel2020常用函数第第3节节 逻辑函数逻辑函数 举例举例：OR AND第3章Excel2020常用函数第第3节节 逻辑函数逻辑函数 举例举例：OR AND第3章Excel2020常用函数第第4节日期和时间函数节日期和时间函数DATA返回当前日期和时间的序列号NOW返回当前日期和时间的序列号TODAY返回当前日期的序列号YEAR返
24、回某日期对应的年份MONTH返回以序列号表示的日期中的月份DAY返回以序列号表示的某日期的天数HOUR返回时间值的小时数第3章Excel2020常用函数第第4节日期和时间函数节日期和时间函数 举例举例:显示当前的日期与时间第3章Excel2020常用函数第第5节节 文本函数文本函数LOWER将一个文字串中的所有大写字母转换为小写字母，但不改变文本中的非字母的字符UPPER将文本转换成大写形式PROPER 将文本字符串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写LEFT根据所指定的字符数，返回文本字符串中左边的几个字符MID返回文本字符串中从指定位置开始的特定数目的字符
25、RIGHT根据所指定的字符数，返回文本字符串中右边的几个字符 Trim清除字符串中的空白。EXACT 比较两个字符串，如果它们完全相同，则返回 TRUE；否则，返回 FALSE第3章Excel2020常用函数第第5节节 文本函数文本函数 举例：举例：LEFT RIGHT MID LEFT功能：返回文本字符串中左边的几个字符语法：LEFT(text,num_chars)参数：text是包含要提取字符的文本串。num_chars指定要由 LEFT 所提取的字符数 MID功能：返回文本字符串中从指定位置开始的特定数目的字符语法：MID(text,start_num,num_chars)参数：text
26、是包含要提取字符的文本串。start_num是文本中要提取的第一个字符的位置。num_chars 指定从文本串中返回字符的个数第3章Excel2020常用函数第第5节节 文本函数文本函数 举例：举例：LEFT RIGHT MID RIGHT功能：返回文本字符串中右边的几个字符语法：RIGHT(text,num_chars)参数：text是包含要提取字符的文本串。num_chars指定希望 RIGHT 提取的字符数 第3章Excel2020常用函数第第5节节 文本函数文本函数举例：举例：从字符串 http:/ http:/“/“的具体函数写法为:LEFT(http:/ 第3章Excel2020常
27、用函数u查找函数 u引用函数第第6节节 查找和引用函数查找和引用函数第3章Excel2020常用函数第第6节节 查找和引用函数查找和引用函数-查找函数查找函数 LOOKUP返回一行或一列区域中或者数组中的某个值HLOOKUP在表格数组首行查找指定的值，并返回表格数组当前行中指定行的值 VLOOKUP在表格数组首列查找指定的值，并返回表格数组当前行中指定列的值CHOOSE可以根据给定的索引值，从多达254个待选参数中选出相应的值第3章Excel2020常用函数第第6节节 查找和引用函数查找和引用函数-查找函数查找函数 举例：举例：现有学生信息表 如果我想查找某个学生的年龄，可以用LOOKUP函数
28、 LOOKUP(lookup_value,lookup_vector,result_vector)指定查找对象指定查找对象指定定位区域指定定位区域指定查找返回结果区指定查找返回结果区域域要求lookup_vector和result_vector的区域必须对等第3章Excel2020常用函数第第6节节 查找和引用函数查找和引用函数-查找函数查找函数 举例：举例：现有学生信息表 如果我想查找某个学生的年龄，公式“=LOOKUP(A5,A3:A8,B3:B8)”返回22(张雷的年龄)公式“=LOOKUP(A5,A3:A8,c3:c8)”返回男(张雷的性别)2022-8-5第3章Excel2020常用函数
