自从用上这些Excel 2016新增函数, 工作就像开了挂! Excel公式的威力有多强大,只有你想不到,没有她做不到的! 但实际运用中,很多复杂情况需要使用数组公式,但80%以上的人根本写不出数组公式,所以Excel的威力大打折扣,虽然有好办法但用不上,最后大部分人还是用笨办法手动折腾。 这一切窘境,都随着Excel 2016的到来改变了!很多以前很棘手的问题,现在在新版本中都可以迎刃而解,用到的利器就是今天的几位主角。 1、IFS函数 2、MINIFS函数 3、MAXIFS函数 4、CONCAT函数 5、TEXTJOIN函数 一、IFS函数 先来看个实际问题,大家看看你会怎么做? 按照成绩划分所属等级 规则如下: 100分:满分 大于等于90分:优秀 大于等于80分:良好 大于等于60分:及格 小于60分:不及格 要在C列输入公式,根据以上规则判断等级。 我知道80%以上的同学都会用这个公式: =IF(B2=100,"满分",IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=60,"及格","不及格")))) 看到这一群括号,80%的人已经晕了 在Excel 2016里,IF可以下场休息了,换IFS上场 =IFS(B2=100,"满分",B2>=90,"优秀",B2>=80,"良好",B2>=60,"及格",B2<60,"不及格") 这种公式写起来是不是简单多了? IFS 函数检查是否满足一个或多个条件,且是否返回与第一个 TRUE 条件对应的值。IFS 可以轻松取代复杂的多层嵌套 IF 语句。 用一句话说清楚它的语法: =IFS(条件1判断语句,条件1结果,条件2判断语句,条件2结果,条件3判断语句,条件3结果,......) 说明:IFS 函数允许测试最多 127 个不同的条件。 条件越多,用这个公式的优势就更加明显,你懂的! 如果你以为IFS就这点本事就小瞧他啦,当IFS与MIN合体时,会发生什么?往下看吧~~ 二、MINIFS函数 介绍这个函数之前,还是先看实际应用场景 下面的表格中,左侧是数据源,要求找到女子1组的最好成绩。 把这个实际问题拆分开,就变成3个条件下找同时满足的数据了 1、第一个条件是性别为女 2、第二个条件是小组为1组 3、第三个条件是满足前两条下百米成绩最快 小白遇到这种情况直接就放弃了...... 高手则会抛出下面这个数组公式,要按<Ctrl+Shfit+Enter>输入。 =MIN(IF((B2:B11="女")*(C2:C11="1组"),D2:D11)) 有了Excel 2016,小白也照样可以轻松搞定这类问题 在F2单元格输入以下公式即可 =MINIFS(D:D,B:B,"女",C:C,"1组") 注意哦,这是个普通公式,直接按Enter即可,不必三键。 MINIFS 函数返回一组给定条件或标准指定的单元格中的最小值。 用一句话说清楚它的语法: =MINIFS(结果所在区域,条件区域1,条件1,条件区域2,条件2,......) 拥有Excel高级版本的优势就在于,这类复杂的多条件统计也可以轻松搞定! MINIFS还有个好兄弟,他就是MAXIFS 三、MAXIFS函数 不寒暄,直接上案例。 下图左侧数据源,要求黄色区域输入公式,计算C产品外销渠道的最高销量。 常规的数组公式如下。(需三键结束输入) =MAX(IF((C2:C11="C")*(B2:B11="外销"),D2:D11)) 有了新函数,在F2单元格输入以下公式即可 =MAXIFS(D:D,C:C,"C",B:B,"外销") 一个公式轻松搞定,小白也毫无压力! MAXIFS 函数返回一组给定条件或标准指定的单元格中的最大值。 他的语法结构跟前面讲过的MINIFS一致,不再赘述。 除了多条件判断、多条件统计极值,在文本合并方面Excel 2016也有惊喜带给你,往下看~ 四、CONCAT函数 说到合并数据,工作中需要的太多了 比如下面表格中,要把左边的多列数据合并到一起,黄色区域写公式。 在老版本中,常见的合并做法是以下这两种公式。 老方法1: =A2&B2&C2&D2&E2&F2&G2&H2&I2&J2 老方法2: =CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2,J2) 当需要合并的数据更多时,公式长度继续增加,当然这种公式写起来很费劲! 有了新版2016,直接用CONCAT函数就简单了,以K2单元格为例公式如下 =CONCAT(A2:J2) CONCAT的本事不止如此,如果你想纵向合并,照样搞定 A8输入以下公式,即可快速合并A列数据 =CONCAT(A2:A6) 如果你想合并多行多列区域,也可以一个公式搞定 =CONCAT(A2:J6) 这些问题在Excel 2016出现以前,处理方法非常麻烦,要么是手动输入长长的公式,要么是需要使用数组公式甚至VBA编程,现在一个CONCAT统统搞定。 看完上面,你已经可以在工作中搞定80%以上的问题啦! 但今天我再多送你点干货,让你赚个盆满钵满,觉得有用,记得去底部点赞! 有时候,工作需要让合并数据的时候中间加个分隔符,这个,可以满足你一下! 这里输入的是一个数组公式,需要按<Ctrl+Shift+Enter>组合键输入以下公式。 =CONCAT(A2:C4&" ") 公式中引号里面是个空格,这样就在合并数据的时候用空格间隔每个数据了,贴心吧! 不但如此,CONCAT还支持条件筛选后的数据合并,看看下面这个动态演示 点击gif可见演示过程 F2用的是以下这个数组公式,需要按<Ctrl+Shift+Enter>输入 =CONCAT(IF((B2:B13=E2)*(C2:C13="是"),A2:A13&"、","")) 它可以帮你做到条件筛选后的数据合并,而且实时动态更新结果哦! 你见识了CONCAT的强大,我悄悄告诉你下面要讲的那个函数可以替代CONCAT的功能,而且还可以完成更多,比如这个案例中返回结果中不想显示最后那个顿号。 五、TEXTJOIN函数 这个函数从名字一看就是专业干文本连接的对吧,呵呵~ 用一句话说明他的语法就是 =TEXTJOIN(间隔符,1,需要合并的数据或区域) 看个案例加深理解 要想把下图左侧的表格,按照所属部门将人员姓名列示在一起,并以顿号间隔,你该怎么做呢? E2输入数组公式后,按<Ctrl+Shift+Enter>组合键输入,将公式向下填充。 =TEXTJOIN("、",1,IF(B$2:B$15=D2,A$2:A$15,"")) 效果演示如下,点击gif可见动态演示过程。 讲了这么多给力的函数,你不是大开眼界呢?原来这么多好用的函数都藏在2016版里!今天就先到这里吧,觉得有用就点赞+分享吧! 我在公众号里专门留了最新版Excel 2016的安装包。 如果你觉得有用,就分享给朋友们看看吧~ |
|