Hello,大家好!今天和大家分享,职场中常用的16个Excel函数。

1、SUM函数求和

如下图所示,要求计算每个人的总分。

在E2单元格输入公式:=SUM(B2:D2)

工作中常用函数大全(职场常用的16个函数)(1)

2、IF函数条件判断

如下图所示,以60分作为及格线。要求判断B2:B8的分数是否及格。

在C2单元格输入公式:

=IF(B2>=60,"及格","不及格")

工作中常用函数大全(职场常用的16个函数)(2)

IF函数的语法为IF(判断条件,符合条件时返回的结果,不符合条件时返回的结果)。

3、IF AND函数且条件判断

如下图所示,科目一、科目二分数均超过60,则及格。

在D4单元格输入以下公式:

=IF(AND(B4>=60,C4>=60),"及格","不及格")

工作中常用函数大全(职场常用的16个函数)(3)

当AND函数的所有参数均满足条件时,返回True,否则返回False。

4、IF OR函数或条件判断

如果科目一、科目二任意一科超过60,则及格。在D2单元格输入公式:

=IF(OR(B4>=60,C4>=60),"及格","不及格")

工作中常用函数大全(职场常用的16个函数)(4)

当OR函数的任意一个参数满足条件时,返回True,均不满足条件时,返回False。

5、SUMIFS函数条件求和

如下图所示,要求统计姓名为“张1”,产品为“品B”的总销售额。

在H2单元格输入公式:

=SUMIFS($D$2:$D$8,$B$2:$B$8,F2,$C$2:$C$8,G2)

工作中常用函数大全(职场常用的16个函数)(5)

6、COUNTIFS函数条件计数

如下图所示,要求统计姓名为“皮卡球”、出差目的地为“北京”的次数。

在G2单元格输入公式:

=COUNTIFS($B$2:$B$10,E2,$C$2:$C$10,F2)

工作中常用函数大全(职场常用的16个函数)(6)

7、VLOOKUP函数查找

如下图所示,根据F2单元格的工号查找姓名。

在G2单元格输入公式:

=VLOOKUP(F2,$B$2:$C$8,2,FALSE)

工作中常用函数大全(职场常用的16个函数)(7)

该公式表示,在B2:C8区域查找F2单元格的值,并返回查找区域第2列(即“姓名”列)的值,参数False表示精确查找。

在使用VLOOKUP函数时需要注意,VLOOKUP函数通常用于正向查找,即从左到右查找。如果使用VLOOKUP函数逆向查找,比如根据工号查找部门,则需要配合IF函数一起使用。

此外,查找值所在的列必须在查找区域的第1列。本例中,查找值所在的列为B列,则查找区域为B2:C8。如果查找区域为A2:C8,VLOOKUP函数会返回错误值。

工作中常用函数大全(职场常用的16个函数)(8)

8、INDEX MATCH函数组合查找

如下图所示,根据工号查找部门。VLOOKUP函数不能直接进行逆向查找,因此使用INDEX MATCH函数组合进行查找。

在G2单元格输入公式:

=INDEX($A$2:$A$8,MATCH(F2,$B$2:$B$8,0))

工作中常用函数大全(职场常用的16个函数)(9)

MATCH(F2,$B$2:$B$8,0)函数用于查找F2单元格的工号在B2:B8中的位置。

INDEX函数则返回A列中与F2单元格的工号在同一行的部门。

9、LEFT LEN LENB函数组合提取字符

如下图所示,要求提取A列的汉字。

在B2单元格输入公式:

=LEFT(A2,LENB(A2)-LEN(A2))

工作中常用函数大全(职场常用的16个函数)(10)

LEFT函数表示从文本字符串的第一个字符开始提取指定个数的字符。

LENB(A2)-LEN(A2)用于计算A2单元格中汉字的个数。

10、RIGHT LEN LENB函数组合提取字符

如下图所示,要求提取A列的数字。

在B2单元格输入公式:

=RIGHT(A2,2*LEN(A2)-LENB(A2))

工作中常用函数大全(职场常用的16个函数)(11)

RIGHT函数表示从文本字符串的最后字符开始提取指定个数的字符。

2*LEN(A2)-LENB(A2)用于计算A2单元格中数字的个数。

11、MID函数提取字符

如下图所示,根据B列身份证号提取出生日期。

在C2单元格输入公式:

=--TEXT(MID(B2,7,8),"0-00-00")

工作中常用函数大全(职场常用的16个函数)(12)

MID函数表示从文本字符串的指定位置起提取指定长度的字符。身份证号的第7位至第14位共8位数字,代表出生日期。MID(B2,7,8)提取的出生日期为“19850307”。

使用Text函数并在Text函数返回的结果前加上“--”,是为了将MID函数提取的数字转化为日期类型数据。

12、SUBSTITUTE函数替换字符

如下图所示,将A列中的“开心”替换为“happy”。

在B2单元格输入公式:

=SUBSTITUTE(A2,"开心","happy")

该公式表示,将A2单元格的字符“开心”替换为“happy”。

工作中常用函数大全(职场常用的16个函数)(13)

13、REPLACE函数替换字符

如下图所示,要求将B列号码中间四位以“*”代替。

在C2单元格输入公式:

=REPLACE(B2,4,4,"****")

该公式表示,将B2单元格的字符串,从第4个字符开始,共4个字符,将其替换为“****”。

工作中常用函数大全(职场常用的16个函数)(14)

14、IFERROR函数屏蔽错误值

如下图所示,当VLOOKUP函数查找不到值时,返回错误值。

工作中常用函数大全(职场常用的16个函数)(15)

此时可以使用IFERROR函数屏蔽错误值,避免错误值影响表格美观。

如下图所示,在G2单元格输入公式:

=IFERROR(VLOOKUP(F2,$B$2:$C$8,2,FALSE),"查找不到")

当VLOOKUP函数返回错误值时,使用IFERROR函数将错误值屏蔽,并返回文本“查找不到”。

工作中常用函数大全(职场常用的16个函数)(16)

15、ROUND函数四舍五入

如下图所示,将B列数值进行四舍五入,并且保留一位小数。

在C2单元格输入公式:=ROUND(B2,1)

工作中常用函数大全(职场常用的16个函数)(17)

16、RANDBETWEEN函数生成随机数

如下图所示,在B2单元格输入公式:=RANDBETWEEN(50,100)

该公式表示生成50至100的随机数。当按F9键时,RANDBETWEEN函数可以更新生成的随机数。

工作中常用函数大全(职场常用的16个函数)(18)

,