1、IFS函数
2、MAXIFS函数
3、MINIFS函数
4、CONCAT函数
5、TEXTJOIN函数
一、IFS函数
IFS 函数检查是否满足一个或多个条件,且是否返回与第一个 TRUE 条件对应的值。IFS 可以轻松取代复杂的多层嵌套 IF 语句。
用一句话说清楚它的语法:
=IFS(条件1判断语句,条件1结果,条件2判断语句,条件2结果,条件3判断语句,条件3结果,......)
说明:IFS 函数允许测试最多 127 个不同的条件。
光说不练假把式,咱来个实际问题让IFS露露脸:
要在C列输入公式,根据以上规则判断等级,以往常用的IF公式如下:
=IF(B2=100,"满分",IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=60,"及格","不及格"))))
看到这一群括号,80%的人已经晕了,下面IF可以下场休息了,换IFS上场:
打开UC浏览器 查看更多精彩图片
=IFS(B2=100,"满分",B2>=90,"优秀",B2>=80,"良好",B2>=60,"及格",B2<60,"不及格")
这种公式写起来是不是简单多了?
条件越多,用这个公式的优势就更加明显,你懂的!
如果你以为IFS就这点本事就小瞧他啦,当IFS与MAX合体时,会发生什么?往下看吧~~
二、MAXIFS函数
MAXIFS 函数返回一组给定条件或标准指定的单元格中的最大值。
用一句话说清楚它的语法:
=MAXIFS(结果所在区域,条件区域1,条件1,条件区域2,条件2,......)
结合一个实际案例来加深理解。
在下图所示的表格中,要同时满足3个条件统计最高销量
1、第一个条件是产品为C
2、第二个条件是渠道为外销
3、第三个条件是满足前两条下销量最高
以前遇到这种情况,需要用到以下这个数组公式,而且要按输入才能返回正确结果。
=MAX(IF((C2:C15="C")*(B2:B15="外销"),D2:D15))
现在有了MAXIFS,一切都变的超简单:
打开UC浏览器 查看更多精彩图片
在F2单元格输入以下公式即可
=MAXIFS(D:D,C:C,"C",B:B,"外销")
有了Excel ,就连Excel小白也可以轻松搞定这类复杂的多条件统计问题了!
MAXIFS还有个好兄弟,下面轮到他出场。
三、MINIFS函数
MINIFS 函数返回一组给定条件或标准指定的单元格中的最小值。
他的语法结构跟MAXIFS一致,不再赘述,直接上案例。
打开UC浏览器 查看更多精彩图片
在F2单元格输入以下公式即可
=MINIFS(D:D,B:B,"女",C:C,"1组")
一个公式轻松搞定,小白也毫无压力!
除了多条件判断、多条件统计极值,在文本合并方面Excel 也有惊喜带给你,往下看~
四、CONCAT函数
下表中的多列数据需要合并,即需要把A2:J2的数据合并放置在K2单元格,依此类推。
打开UC浏览器 查看更多精彩图片
在老版本中,常见的合并做法是以下这种公式。
老方法1:
=A2&B2&C2&D2&E2&F2&G2&H2&I2&J2
当需要合并的数据更多时,公式长度继续增加,很机械很重复的码公式是个苦差事!
用CONCAT函数就简单了,以K2单元格为例公式如下:
=CONCAT(A2:J2)
打开UC浏览器 查看更多精彩图片
CONCAT不但可以横向合并,还可以纵向合并。
比如要把A列数据合并,只需输入以下公式:
=CONCAT(A2:A7)
打开UC浏览器 查看更多精彩图片
除了横向合并,纵向合并外,CONCAT还支持区域内数据合并。
以下这个很简短的公式,可以帮你把整个区域的数据都合并在一起
=CONCAT(A2:J7)
打开UC浏览器 查看更多精彩图片
有时候,工作需要让合并数据的时候中间加个分隔符,这个,可以满足你一下:
打开UC浏览器 查看更多精彩图片
这里输入的是一个数组公式,需要按组合键输入以下公式。
=CONCAT(A2:C4&" ")
公式中引号里面是个空格,这样就在合并数据的时候用空格间隔每个数据了,贴心吧?
不但如此,CONCAT还支持条件筛选后的数据合并,看看下面这个动态演示
F2用的是以下这个数组公式,需要按输入:
=CONCAT(IF((B2:B13=E2)*(C2:C13="是"),A2:A13&"、",""))
它可以帮你做到条件筛选后的数据合并,而且实时动态更新结果哦!
你见识了CONCAT的强大,我悄悄告诉你下面要讲的那个函数可以替代CONCAT的功能,而且还可以完成更多,比如这个案例中返回结果中不想显示最后那个顿号。
五、TEXTJOIN函数
这个函数从名字一看就是专业干文本连接的对吧,呵呵~
用一句话说明他的语法就是:
=TEXTJOIN(间隔符,1,需要合并的数据或区域)
看个案例加深理解
要想把下图左侧的表格,按照所属部门将人员姓名列示在一起,并以顿号间隔,你该怎么做呢?
打开UC浏览器 查看更多精彩图片
E2输入数组公式后,按组合键输入,将公式向下填充。
=TEXTJOIN("、",1,IF(B$2:B$15=D2,A$2:A$15,""))
打开UC浏览器 查看更多精彩图片