近来,利用空闲时间整理了全国计算机等级考试(OFFICE二级) Excel 函数知识点,方便大家共享。如需电子文档,可私信联系,留下个人电子邮箱。 一、求和函数 1、求和函数 SUM 功能:求各参数之和。例:=SUM(C1:C20) 2、单条件求和函数 SUMIF 功能:对指定单元格区域中满足一个条件....的单元格求和。 =SUMIF(条件区域,指定的求和条件,求和的区域) 3、多条件求和函数 SUMIFS 功能:对指定单元格区域中满足多组条件....的单元格求和。 =SUMIFS(求和区域,条件 1 区域,条件 1,条件 2 区域,条件 2,……,条 件 n 区域,条件 n) n 最大值为 127 。 注意:求和区域要写在最开始的位置。 二、求平均值函数 1、平均值函数 AVERAGE 功能:求各参数的算术平均数。例:=AVERAGE(B2:B10) 2、单条件平均值函数 AVERAGEIF 功能:对指定单元格区域中满足一组条件....的单元格求平均值。 =AVERAGEIF(条件区域,求值条件,求值区域) 3、多条件平均值函数 AVERAGEIFS 功能:对指定单元格区域中满足多组条件....的单元格求平均值。 =AVERAGEIFS(求值区域,条件 1 区域,条件 1,条件 2 区域,条件 2,……, 条件 n 区域,条件 n) 三、计数函数 1、统计数值型数据个数的计数函数 COUNT 功能:求各参数中数值型数据.....的个数。例:=COUNT(C2:C15) 2、统计非真空单元格个数的计数函数 COUNTA 功能:求各参数中'非空'单元格的个数。例:=COUNTA(D3:D15) 3、单条件计数函数 COUNTIF 功能:计算指定区域中符合指定一组条件....的单元格个数。 =COUNTIF(条件区域,指定的条件) 例:=COUNTIF(B2:B16, '男' ) =COUNTIF(B2:B10, '>60') 4、多条件计数函数 COUNTIFS 功能:计算指定区域中符合指定多组..条件..的单元格个数。 =COUNTIFS(区域 1,条件 1,区域 2,条件 2,……,区域 n,条件 n) N 最大值为 127 。 例:=COUNTIFS(B2:B10, '>=60',C2:C10, '>=60') 统计双科及格人数 四、逻辑判断函数 1、逻辑函数 OR 功能:在其参数组中,任何一个参数逻辑值为 TRUE,即返回 TRUE;所有 参数的逻辑值为 FALSE,才返回 FALSE。最多可以有 30 个条件。 公式:=OR(参数 1,参数 2,参数 3,……,参数 30) 例:单元格 A3 内容为 98,B3 内容为 89,则 =OR(A3<100,B3>90)=TRUE =OR(A3>100,B3>90)=FALSE 2、逻辑函数 AND 功能:所有参数的逻辑值为真时,返回为 TRUE;只要有一个参数为假时, 即返回 FALSE 公式:=AND(参数 1,参数 2,参数 3,……,参数 30) 例:单元格 A3 内容为 98,B3 内容为 89,则 =AND(A3<100,B3<90)=TRUE =AND(A3>100,B3<90)=FALSE 例:=AND(12>3,4<7)=TRUE =AND(9<5,13>10)=FALSE 3、逻辑函数 IF 公式:=IF(判断条件,符合条件时返回的值,不符合条件时返回的值) 例:=IF(A,B,C)= 如果 A 成立,就返回结果 B,否则就返回结果 C 。 例:A2 中输入 85,A3 中输入 59,A4 中输入 70,则 =IF(A2<60,'不及格',IF(A2<80,'及格','优秀'))=优秀 =IF(A3<60,'不及格',IF(A3<80,'及格','优秀'))=不及格 =IF(A4<60,'不及格',IF(A4<80,'及格','优秀'))=及格 =IF(A2>80,'优秀',IF(A2>60,'及格','不及格'))=优秀 =IF(A3>80,'优秀',IF(A3>60,'及格','不及格'))=不及格 =IF(A4>80,'优秀',IF(A4>60,'及格','不及格'))=及格 五、查找与引用函数 1、垂直查询函数 VLOOKUP 功能:搜索指定单元格区域的第一列,然后返回该区域相同一行上指定单 元格的值。 函数 VLOOKUP 有 4 个参数。 =VLOOKUP(查找目标,查找范围,返回值的列数,精确查找) 翻译:函数要查找什么数据,在哪个区域中查找,查找到后返回第几列, 按什么方式进行查找 注意点:要查找的目标必须作为查找区域的第 1列。第 4个参数输入 0 。 精确查找为 0。 例:=VLOOKUP(G2,B2:E2,4,0) 2、隐藏错误值的函数 IFERROR,给错误值穿上一件隐身衣。 功能:使用 iferror 函数可以用来判断某些内容的正确与否,正确则返回正 确结果,错误则返回需要显示的信息 计算得到的错误类型有:#N/A、#VLLUE!、#REF!、#DIV/0、#NUM!、#NAME?、 #NULL! 例:=IFERROR(3/0,'错误')=错误 =IFERROR(3/2,'错误')=1.5 =IFERROR(VLOOKUP(F2,A:E,3,0) '查找不到此人')=查找不到此人 3、查找函数 LOOKUP =LOOKUP(查找值,查找范围,返回值范围) 翻译:函数要查找什么数据,在哪个区域中查找,返回值在哪个区域。 注意点:第 2个参数'查找范围'必须按升序排列。 例:=LOOKUP(B1,B4:G4,B7:G7) 第 2 个参数 B1:G4 中数据按升序排 列。 六、排名函数 1、排名函数 RANK 功能:求指定区域中的数据排名。函数 RANK 共 3 个参数。 =RANK(排名的数据,排名数据的区域+F4,排名的方式 0 或 1) 注意点:排名数据的区域为绝对引用地址。排名的方式有两种:0 为降序, 1 为升序。第 3参数为 0时,可省略。 例:=RANK(A2,A$2:A$10,0) 2、实际排位函数 RANK.EQ 此函数与 RANK 用法相同,作用相同。 功能:返回一列数字的数字排位。使用函数 RANK.EQ 返回的是实际排位。 =RANK.EQ(要排位的数据,排位数据的区域+F4,排位的方式 0 或 1) 注意点:排位数据的区域为绝对引用地址。排位的方式有两种:0 为降序, 1 为升序。第 3参数为 0时,可省略。 3、平均排位函数 RANK.AVG 功能:求一个数值在指定数值列中的排位。多个..相同的...值具有相同的排位........, 使用函数 RANK.AVG 将返回平均排位。 =RANK.AVG(要排位的数据,排位数据的区域+F4,排位的方式 0 或 1) 注意点:排位数据的区域为绝对引用地址。排位的方式有两种:0 为降序, 1 为升序。第 3参数为 0时,可省略。 七、时间和日期类函数 1、年份 YEAR 函数 功能:返回指定日期对应的年份。 公式: =YEAR(日期)=年份 例:单元格 B1 中输入 2017/8/8,提取年份 =YEAR(B1)=2017 2、月份 MONTH 函数 功能:返回一年中的某月。 公式: =MONTH(日期)=月份 例:单元格 B1 中输入 2017/8/8,提取月份 =MONTH(B1)=8 3、日期 DATE 函数 公式:=DATE(年,月,日) 例: =DATE(2016,9,25)=2016/9/25 4、工作日函数 WEEKDAY 此函数有两个参数 =WEEKDAY(日期,返回值类型的数字) 第 2 参数为数字 1 或省略,结果 1 至 7 代表星期日到星期六; 第 2 参数为数字 2,结果 1 至 7 代表星期一至星期日; 第 2 参数为数字 3,结果 0 至 6 代表星期一到星期日。 例:=WEEKDAY('2017/8/7',1)=2 ,这一天为星期一 。 5、日期的间隔计算函数 DATEDIF 。此函数是 EXCEL 的隐藏函数。 功能:日期的间隔计算。 =Datedif(开始日期,结束日期.'y') 间隔的年数 =Datedif(开始日期,结束日期.'M') 间隔的月份 =Datedif(开始日期,结束日期.'D') 间隔的天数 【例】 A1 是开始日期(2011-12-1),B1 是结束日期(2013-6-10)。计算: 相隔多少天?=datedif(A1,B1,'d') 结果:557 相隔多少月? =datedif(A1,B1,'m') 结果:18 相隔多少年? =datedif(A1,B1,'Y') 结果:1 不考虑年相隔多少月?=datedif(A1,B1,'Ym') 结果:6 不考虑年相隔多少天?=datedif(A1,B1,'YD') 结果:192 不考虑年月相隔多少天?=datedif(A1,B1,'MD') 结果:9 6、当前日期函数 TODAY() 括号内无参数,输入时不能忘记一对空括号。 功能:返回当天的日期(当前计算机系统的日期) 例:=TODAY()=2017/8/7 7、当前日期和时间函数 NOW() 括号内无参数,输入时不能忘记一对空括号。 功能:返回当天的日期和时间(当前计算机系统的日期和时间) 例:=NOW()=2017/8/17 17:10 八、文本类函数 1、文本合并函数 CONCATENATE [kɒn'kætɪneɪt] 功能:计几个文本项合并成一个文本项 例 =CONCATENATE('天职师大','电子工程学院','1512 班')=天职师大电子工程学院 1512 班 2、中间截取字符串函数 MID 功能:从文本字符串中的指定位置开始截取指定个数的字符。 =MID(要截取的字符串,从第几个字符开始,要截取的字符数) 例: =MID('空心管_35mm',5,2)=35 3、左侧截取字符串函数 LEFT 功能:从文本字符串最左边开始返回指定个数的字符。 =LEFT(要截取的字符串,从最左边开始要截取的字符数) 例: =LEFT('空心管_35mm',3)=空心管 4、右侧截取字符串函数 RIGHT 功能:从文本字符串最左边开始返回指定个数的字符。 =RIGHT(要截取的字符串,从最右边开始要截取的字符数) 例: =RIGHT('空心管_35mm',4)=35mm 5、统计字符个数函数 LEN 功能:统计文本字符串中字符的个数。 公式 =LEN(指定的文本字符串) 例:=LEN('中国')=2 =LENB('中国')=4 =LEN('English')=7 =LENB('English')=7 6、统计字符串字节个数函数 LENB 功能:统计指定字符串字节个数。 公式 =LENB(指定的文本字符串) 7、查找函数 FIND (共 3 个参数,第 3 个参数省略时默认为 1,表示从第一个字符开始查找。) 功能:FIND 函数进行定位时,总是从指定位置开始,返回找到第一个匹配字符串的位置, 而不管其后是否还有相匹配的字符串。 公式 =FIND(要查找的文本,文本所在的单元格,从第几个字符开始查找) 例:单元格 A1 中输入 594823166@qq.com,查找@的位置或者提取 qq 号 =FIND('@',A1,1)=10 =LEFT(A1,FIND('@',A1,1)-1)=594823166 8、删除空格函数 TRIM [trɪm] 功能:删除指定文本或区域中的空格。 =TRIM('中 国')=中国 =TRIM('你好 ')=你好 9、文本函数 TEXT 功能:将数值转换为按指定数字格式表示的文本 公式:=TEXT(数值,格式) 例:=TEXT(20071231,'0000-00-00')=2007-12-31 =TEXT(MID('320924197811123431',7,8),'0 年 00 月 00 日')=1978 年 11 月 12 日 =TEXT(MID('320924197811123431',7,8),'0000-00-00')=1978-11-12 九、数学与三角函数 1、取整函数 TRUNC 功能:将小数部分截去,第 2 个参数为 0 时返回整数。 (共 2 个参数,第 2 个参数为 0 时可省略。) 公式: =TRUNC(要截尾取整的数字,取整精度的数字) 例: =TRUNC(14.889)=14 =TRUNC(9.633)=9 2、向下取整函数 INT 功能:将参数一的数值向下舍入到最接近的整数。 例:=INT(158.76)=158 =INT(5.98)=5 3、四舍五入函数 ROUND (共 2 个参数,第 2 个参数为保留小数的位数。参数为 0 时保 留整数) 功能:按参数二指定的位数对参数一进行四舍五入。 公式:=ROUND(需四舍五入的数字,保留的小数位数) 例:=ROUND(158.6403,0)=159 =ROUND(158.6403,2)=158.64 =ROUND(158.6403,1)=158.6 =ROUND(158.6403,-1)=160 4、向上舍入函数 ROUNDUP (第 2 个参数为向上舍入到指定的位数) 功能:向上舍入。 注意点:ROUNDUP 总是向上舍入数字,即使要舍去的首数小于 4 也要进数加 1。如果第 2 个参数大于 0,则向上舍入到指定的小数位。如果第 2 个参数等于 0,则向上舍入到最接近的整数。 公式:=ROUNDUP(需向上舍入的数字,保留的小数位数) 例: =ROUNDUP(158.4403,0)=159 =ROUNDUP(158.6403,1)=158.7 =ROUNDUP(158.6403,2)=158.65 =ROUNDUP(158.4403,2)=158.45 5、向下舍入函数 ROUNDDOWN 功能:向下舍入数字 注意点:ROUNDDOWN 总是向下舍入数字。如果第 2 个参数大于 0,则向下舍入到指定的 小数位。如果第 2 个参数等于 0,则向下舍入到最接近的整数。 公式:=ROUNDUP(需向下舍入的数字,保留的小数位数) =ROUNDDOWN(158.6403,0)=158 ; =ROUNDDOWN(158.6403,1)=158.6 =ROUNDDOWN(158.6403,2)=158.64 =ROUNDDOWN(158.4403,0)=158 6、求参数乘积的函数 PRODUCT [ˈprɒdʌkt] (用于计算参数中所有数字的乘积) 功能:将所有以参数形式给出的数字相乘,并返回乘积值。 公式: =PRODUCT(参数 1,参数 2,参数 3,……,参数 30)=参数 1×参数 2×参数 3×……×参数 30 例:=PRODUCT(50,13,25)=16250 =PRODUCT(50,15,TRUE)=750 =PRODUCT(65,42,FALSE)=0 7、求余数函数 MOD [ˌeməʊˈdiː] 功能:求两数相除的余数。 公式:=MOD(被除数,除数) 例:=MOD(3,2)=1 =MOD(6,3)=0 =MOD(10,7)=3 例:根据身份证号的第 17 位数字判断性别。(单数为男,双数为女) =IF(MOD(MID('320924197811123431',17,1),2)=0,'女','男')=男 =IF(MOD(MID('320924195405033466',17,1),2)=0,'女','男')=女 十、信息函数 1、判断参数是否是奇数的函数 ISODD ODD [ɒd] 功能:判断其参数是不是奇数,如果是奇数就返回 TRUE,否则返回 FALSE 或错误值。 例:=ISODD(15)=TRUE =ISODD(16)=FALSE 2、判断参数是否是偶数的函数 ISEVEN EVEN [ˈi:vn] 功能:判断其参数是不是偶数,如果是偶数就返回 TRUE,否则返回 FALSE 或错误值。 例:=ISEVEN(15)=FALSE =ISEVEN(16)=TRUE 例:根据身份证号的第 17 位数字判断性别。(单数为男,双数为女) =IF(ISODD(MID('320924195405033466',17,1)),'男','女')=女 =IF(ISEVEN(MID('320924195405033466',17,1)),'女','男')=女 =IF(ISODD(MID('320924197811123431',17,1)),'男','女')=男 =IF(ISEVEN(MID('320924197811123431',17,1)),'女','男')=男 3、检验单元格值是否为空函数 ISBLANK BLANK [blæŋk] 功能:单元格为无数据的空白时,ISBLANK 函数将返回 TRUE,否则将返 回 FALE 例:单元格 A1 没有任何内容,用此函数检查是否为空,结果是什么? =IF(ISBLANK(A1),'空','有内容')=空 补充: 1、最大值函数 MAX 求一组数中的最大值 例:=MAX(10,7,9,27,5)=27 2、最小值函数 MIN 求一组数中的最小值 例:=MIN(10,7,9,27,5)=5 |
|
来自: 昵称58195209 > 《办公文档》