分享

全国计算机等级考试(OFFICE二级) Excel 函数知识点

 昵称58195209 2019-09-21

近来,利用空闲时间整理了全国计算机等级考试(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

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多