头部广告

职场必学的9个Excel实用函数,实例图解,简单易学可直接套用,快速提高工作效率!

今天就跟大家分享在WPS中职场必学的9个Excel实用函数,实例图解,简单易学,大家遇到类似场景可直接套用,快速提高工作效率。

一、IF函数:条件判断

使用逻辑函数 IF函数时,如果条件为真,该函数将返回一个值;如果条件为假,函数将返回另一个值。

语法=IF(测试条件, 真值, [假值])

测试条件:必须,要测试的条件。

真值:必需,要测试的条件的结果为 TRUE 时,您希望返回的值。

假值:可选,要测试的条件的结果为 FALSE 时,您希望返回的值。

实例图解:

如下图所示,当员工考核成绩大于等于80时,奖金为800,否则为空

使用公式=IF(C4>=80,"800","")

1.jpg


2.gif


二、SUM函数:用于简单无条件求和

SUM函数主要用于简单无条件求和,常用于计算单元格区域中所有数值的和

语法=SUM(数值1,[数值2],...)

实例图解:

如下图所示,我们需要对所有员工工资进行求和计算,使用公式=SUM(C5:C9)

3.jpg


三、SUMIFS函数:多条件求和函数

SUMIFS函数:它是一个多条件求和函数。

语法:=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2……)

实例图解:

如下图所示,计算【业务部且考核成绩大于等于80】的奖金之和,使用公式=SUMIFS(D3:D12,B3:B12,"业务部",C3:C12,">=80")

4.jpg


四、XLOOKUP函数:查找函数

XLOOKUP函数是一个查找函数,在某个范围或数组中搜索匹配项,并通过第二个范围或数组返回相应的项,默认情况下使用精准匹配。

语法:=XLOOKUP(查找值,查找数组,返回数组,未找到值,匹配模式,搜索模式)。

XLOOKUP函数参数虽然比较多,但是第四、第五、第六参数都是可以省略的,我们在平时使用这个函数时一般只需设置前三个函数即可。

实例图解:

如下图所示,我们想查询赵飞的基本工资,我们可以直接使用公式=XLOOKUP(G3,A2:A8,D2:D8),在这里我们可以把函数的第四、第五、第六参数都省略掉,我们在平时使用这个函数时一般只需设置前三个函数即可。

5.jpg


五、SUMPRODUCT函数(最新版本):相应元素需要进行相乘并求和

SUMPRODUCT函数的作用是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

语法=SUMPRODUCT(array1,array2,array3, ...)

Array1,array2,array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。

说明

■数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。

■函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

实例图解:

就以下图为例,下图是客户购买产品数据统计表,需要根据客户购买产品的数量、单价统计出合计金额。这是我们可以使用大神级SUMPRODUCT函数快速实现合计金额的统计。

6.jpg


操作步骤:

1、首先,在合计金额下方的第一个单元格输入公式=SUMPRODUCT() ,如下图所示

7.jpg


2、为SUMPRODUCT函数选择第一个数组参数,就是所有产品单价,现在公式是=SUMPRODUCT(B3:G3),如下图所示

8.jpg


3、用英文状态下的逗号隔开,选择第二个数组参数,就是客户每个商品购买数量,现在公式是=SUMPRODUCT(B3:G3,B4:G4),如下图所示

9.jpg


4、对公式第一个参数就是商品单据需要进行绝对引用,选中B3:G3按一次F4键进行绝对引用,最终公式=SUMPRODUCT($B$3:$G$3,B4:G4),如下图所示

10.jpg


11.gif


5、最后下拉公式,填充下面的数据即可,如下图所示

12.gif


六、MID函数:提取字符

MID函数 返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。

语法=MID(字符串,开始位置,字符个数)

实例图解:

如下图所示,从身份证号中提取出出生年份,使用公式=MID(D2,7,4)

13.jpg


七、COUNTIFS:条件计数

COUNTIFS 函数将条件应用于跨多个区域的单元格,然后统计满足所有条件的次数。

语法=COUNTIFS(条件区域1,条件1,[条件区域2,条件2],…)

实例图解:

如下图所示,统计业务部考核成绩大于等于80的人数,使用公式=COUNTIFS(C2:C8,"业务部",D2:D8,">=80")

14.jpg


八、DATE函数:日期

DATE函数返回代表特定日期的序列号,这个函数是一个基础函数,常与运用到日期的函数组合使用。

语法=DATE(year,month,day)

实例图解:

如下图所示,根据给定的年月日组合成日期,使用公式=DATE(D4,D5,D6)

15.jpg


九、TEXTJOIN函数:高级用法-多对多查询

语法=TEXTJOIN(分隔符, 忽略空白单元格, 字符串1…)

大家对TEXTJOIN函数第一印象应该是觉得是强大的合并文本函数,其实我们可以借助IF函数,实现开多对多查询功能。

实例图解:

如下图所示,我们需要在左侧的表格中找出【业务部、运营部、财务部】所对应的员工名称,把这些名称在右侧表格中统计出来,并且每个员工名称之间用逗号隔开。想要实现多对多查询功能,我们需要借助IF函数,先使用IF函数从左侧表格中找到【业务部、运营部、财务部】对应的所有员工名称,然后将其放在TEXTJOIN的第三参数中即可。

16.jpg


使用公式:=TEXTJOIN(",",TRUE,IF(A2:$A$10=D2,B2:$B$10,""))

17.jpg


其实,这个公式的关键就是IF函数,通过IF函数判断所属部门,如果条件成立就返回对应的员工名称,如果条件不成立就返回空值

IF公式:IF(A2:$A$10=D2,B2:$B$10,"")

特别注意事项:

1、IF函数中的判断条件和返回真值引用的单元格都是要决定引用,就是选择单元格后按一次F4键。

2、在WPS中输入公式后,一定要按Ctrl+Shift+回车键才可以,如果只按回车键会返回所有的员工名称。我们可以看到这个公式跟以往的公式有所不同,公式外面有{},这个可不是手工写的,而是输入公式后,按Ctrl+Shift+Enter三个组合键结束,这就是传说中的数组公式。


底部广告