头部广告

12个职场必学的Excel函数公式,简单实用,一学就会!

我是【桃大喵学习记】,欢迎大家关注哟~,每天为你分享职场办公软件使用技巧干货!

——首发于微信号:桃大喵学习记

今天跟大家分享的是12个职场必学的Excel函数公式,简单实用,动图演示,一学就会!

一、IF函数公式(单条件判断函数)

功能:进行条件判断如果条件为真,该函数将返回一个值;如果条件为假,函数将返回另一个值。

语法:=IF(判断条件, 真值, [假值])

实例:

如下图所示,这是员工销售业绩完成状态信息表,当实际业绩大于目标业绩时,完成状态显示√(勾);否则,完成状态显示×(叉)。

在目标单元格中输入公式:

=IF(C2<=D2,"√","×")

点击回车,下拉填充数据即可

1.gif

当然,如果我们想使用IF函数进行多条件判断的话可以跟AND或者OR函数组合。

1、IF+AND函数组合(多条件同时满足)

功能:同时满足多个条件,返回真值,否则返回假值

语法:=IF(AND(条件1,条件2..条件N),条件成立返回值,条件不成立返回值)

或者=IF((条件1)*(条件2..)*(条件N),条件成立返回值,条件不成立返回值)

实例:

如下图所示,这是一个员工考核成绩表,需要给“技术部”并且“考核成绩”大于90的员工补贴900元,否则补贴为0。

在目标单元格中输入公式:

=IF(AND(B3="技术部",C3>90),900,0)

或者

=IF((B3="技术部")*(C3>90),900,0)

然后点击回车,下拉填充数据即可

2.gif

2、IF+OR函数组合(多条件满足任意一个条件)

功能:满足多个条件中任意一个条件,返回真值,否则返回假值

语法:=IF(OR(条件1,条件2..条件N),条件成立返回值,条件不成立返回值)

或者=IF((条件1)+(条件2..)+(条件N),条件成立返回值,条件不成立返回值)

实例:

如下图所示,这是一个员工考核成绩表,需要给“技术部”或者“考核成绩”大于90的员工补贴900元,否则补贴为0。

在目标单元格中输入公式:

=IF(OR(B3="技术部",C3>90),900,0)

或者

=IF((B3="技术部")+(C3>90),900,0)

然后点击回车,下拉填充数据即可

3.gif

二、IFS函数公式(多条件判断函数)

功能:IFS函数功能就是实现多条件判断,一个判断条件对应一个判断结果。

语法:=IFS(判断条件1,结果1,判断条件2,结果2,判断条件3,结果3....)

实例:

如下图所示,左侧是资金明细表,需要根据备注判断资金类型:付款=支出,贷款=收入,手续费=费用,网银转账=转账,最后微信、支付宝、现金=其他应收款。

4.webp.png

在目标单元格中输入公式:

=IFS(C2="付款","支出",C2="贷款","收入",C2="手续费","费用",C2="网银转账","转账",TRUE,"其他应收款")

然后点击回车,下拉填充其它数据即可

5.gif

解读:

公式中因为备注信息中的“微信、支付宝、现金”对应的类型都是“其他应收款”,如果每个都判断一次太麻烦。我们直接把条件写成TRUE,然后返回"其他应收款",含义就是当不满足前面所有的条件时,就返回"其他应收款"。

三、XLOOKUP函数公式

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

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

实例:

如下图所示,左侧是员工销售业绩和奖金比例表格,我们需要根据右侧奖金规则表格,通过每个员工的业绩来判断对应的奖金比例。遇到这种情况大家使用最多的应该是使用IFS函数进行处理,今天就大家分享一下使用XLOOKUP函数进行区间判断的方法。

6.webp.png

在目标单元格中输入公式:

=XLOOKUP(B2,E:E,F:F,,-1)

然后点击回车,下拉填充数据即可

7.gif

解读:

公式中第1参数:B2 就是查找值,是每个员工的销售业绩;

第2参数:E:E 就是要查找的数据区域,对应就是奖金规则表格中的业绩区间;

第3参数:F:F 就是返回的数组,对应就是奖金规则表格中的奖金比例;

第4参数:为空,查找不到信息返回空;

第5参数:-1,匹配模式为-1,表示精确匹配,若未找到所查找的内容返回较小值。

比如说在对诸葛亮:业绩是7182,进行查找匹配时它会从上向下查找匹配,首先找到7182在哪个数值之间,它是在6000-10000这个范围之间,然后会匹配到较小的那个数值,也就是6000,这样就查询结果奖金比例就是对应的“5%”。

9.png

总之、当XLOOKUP函数第5参数即匹配模式为-1,表示精确匹配,若未找到所查内容返回较较小值。

四、FILTER函数公式

功能:FILTER是基于定义的条件筛选一系列数据的函数,它由数组,包括,空值三个参数所构成。

语法:使用语法=FILTER(数组,包括,空值)

第一个参数【数组】:就是筛选区域

第二个参数【包括】:就是筛选列=筛选条件

第三个参数【空值】:可以忽略,这个参数就是如果出现错误值可以设置返回信息

实例:

如下图所示,左侧是员工考核成绩信息表,我们需要根据员工“名称”和“部门”查询“考核成绩”。

在目标单元格输入公式:

=FILTER(D3:D9,(B3:B9=F3)*(C3:C9=G3),"无数据")

然后点击回车键获取数据。

9.webp.png

解读:

①公式中第二参数:多条件筛选使用的是(B3:B9=F3)*(C3:C9=G3),有几个条件就用括号()和星号*连接,星号*的意思就是AND且的意义,会筛选出同时满足这几个条件的查询结果。如果查询的空值就返回第三参数:"无数据"。

②D3:D9是筛选区域,符合条件即返回数据。

使用FILTER进行多条件查询窍门在第2个参数

1、如果需要多个条件同时满足,就用*把多个条件连接

条件1*条件2*条件N

(B3:B9=F3)*(C3:C9=G3)

2、如果需要多个条件满足任意一个,就用+把多个条件连接

条件1+条件2+条件N

(B3:B9=F3)+(C3:C9=G3)

五、TEXTJOIN函数公式

作用:TEXTJOIN函数是文本连接函数,使用分隔符连接列表或文本字符串区域。

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

实例:

如下图所示,左侧是每个学员驾照考试时【科目一】-【科目四】的通过情况,我们根据姓名查找出每个学员通过的科目信息。

在目标单元格中输入公式:

=TEXTJOIN(",",TRUE,IF((A2:A7=G2)*(B2:E7="√"),B1:E1,""))

然后点击回车即可

10.gif

解读:

①上面公式同样先使用IF函数进行多条件判断,当同时满足(A2:A7=G2)*(B2:E7="√")时,返回对应学员通过的科目信息B1:E1,否则返回空值。

②最后通过TEXTJOIN函数把返回的结果通过","连接起来即可。

六、TEXTSPLIT函数公式

功能:使用分隔符将文本拆分为行和列;

语法:=TEXTSPLIT(要拆分的文本,按列拆分,按行拆分,是否忽略空单元格,是否区分大小写,异常返回值)

实例:

如下图所示,左侧是客户名称用逗号隔开的,我们想按分隔符把文本拆分成行显示。

在目标单元格输入公式:

=TEXTSPLIT(B3,",")

然后点击回车,下拉填充即可

11.gif

七、UNIQUE函数公式

功能:UNIQUE函数可以去除重复值保留唯一值

语法:=UNIQUE(数组,[按列],[仅出现一次])

第1参数:数组就是返回唯一值的数组数据区域;

第2参数:按列是可选参数,指定比较的方式,设置为TRUE将比较列并返回唯一值,设置为FALSE (或省略) 将比较行并返回唯一值;

第3参数:[仅出现一次]可选参数,一般直接省略即可。

实例:

如下图所示,左侧是一列名单,我们需要去掉重复数据

在目标单元格中输入公式:

=UNIQUE(A1:A8)

然后点击回车即可

12.gif

八、VSTACK函数公式

功能:将数组垂直堆叠到一个数组中

语法:=VSTACK(数组1,数组2,数组3,……)

实例:

如下图所示,我们想把左侧两个表格数据合并到一个表格中,只需在目标单元格中输入公式:

=VSTACK(A2:B6,D2:E7)

然后点击回车即可

13.gif

九、SORT函数公式

功能:SORT函数主要用来对某个区域或数组的内容进行排序。

语法:=SORT(数组,排序依据,排序顺序,按列)

第一参数:「数组」指的是要排序的区域或数组

第二参数:「排序依据」为以某行或列为依据进行排序

第三参数:「排序顺序」指的是所需的排序顺序,1表示升序排序,-1表示降序排序

第四参数:「按列」是一个逻辑值,输入True表示按列排序,输入False表示按行排序,默认按行排序。

实例:

如下图所示,我们想对左侧表格数据以成绩来排序,按成绩从高到低排序。

在目标单元格输入公式:

=SORT(A1:E10,2,-1)

点击回车即可获取排序数据。

14.gif

解读:

公式=SORT(A1:E10,2,-1)

①第一参数A1:E10是要排序的数据区域;

②第二参数2代表「排序依据」为第2列(成绩);

③第三参数-1代表「排序顺序」为降序排序就是按“成绩”从高到低排序。

十、DROP函数公式

功能:DROP函数可以从数组开头或者结尾删除行或列。

语法:=DROP(数组,行数,[列数])

实例:

如下图所示,这是一份参会名单,并且参会人员有可能随时添加新的人员信息,我们需要实时提取不重复数据以便于后期进行数据统计。

15.webp.png

在目标单元格中输入公式:

=DROP(UNIQUE(A:A),-1,0)

然后点击回车即可,如果参会名单变动,提取的不重复数据也会自动更新。

16.gif

解读:

上面公式使用DROP函数和UNIQUE函数组合,主要是为了实现根据参会人员变动,达到动态提取不重复数据的效果。

十一、TAKE函数公式

功能:从数组开头或结尾返回对应的行或列数据

语法:=TAKE(数组,行数,[列数])

实例:

如下图所示,要根据B列的销售业绩,使用公式得到从高到低的销售排序,然后获取前3名的的数据。

只需在目标单元格中输入公式:

=TAKE(SORT(A1:B14,2,-1),4)

然后点击回车即可

17.gif

解读:

公式中首先使用SORT函数对数据按销售页面降序排序,然后在使用TAKE函数按行获取前4条数据。因为第一行数据是表头数据,获取前3名数据需要获取4行数据。

十二、SUMPRODUCT函数公式

功能:SUMPRODUCT函数主要功能是返回相应的数据或区域乘积的和。

语法:=SUMPRODUCT(数组1,数组2,数组3, ...)。

解读:

①数组1,数组2,数组3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。

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

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

实例:

如下图所示,对“业务部”考核成绩大于85的员工销售业绩求和

在目标单元格中输入公式:

=SUMPRODUCT((C2:C6="业务部")*(E2:E6>80)*F2:F6)

然后点击回车即可

18.gif

解读:

①公式中有三个数据区域分别是(C2:C6="业务部"、E2:E6>80、F2:F6,当C2:C6部门区域内的值等于"业务部"时返回逻辑值TRUE(1),否则返回FALSE(0);当E2:E6考核成绩的值大于80时返回逻辑值TRUE(1),否则返回FALSE(0),最后三个数据对应元素先乘积,再求和,从而计算得到女性员工销售业绩求和。

②SUMPRODUCT条件求和万能公式

=SUMPRODUCT((条件数据区域1=条件1)*(条件数据区域2=条件2)*(条件数据区域N=条件N)*求和区域)

以上就是【桃大喵学习记】今天的干货分享~觉得内容对你有所帮助,别忘了动动手指点个赞哦~。大家有什么问题欢迎关注留言,期待与你的每一次互动,让我们共同成长!


底部广告