最近在群里发了一个公式练习题,按照姓名查找工号,效果如图所示:
当ID都是数字时,这个问题基本上没什么难度,很多朋友都知道用SUMIF可以实现所需结果,公式为:=SUMIF(B2:J7,A10,A2:I7)
红色框为条件区域,蓝色框为求和区域,利用SUMIF按照指定姓名对ID求和即可实现匹配的效果。
PS:当有重名的时候,这个方法就不行了。
当题目难度升级后,将ID变成了带字母的形式,这个问题一下子火了起来:
此时再用SUMIF显然不行了,于是大家纷纷开动脑筋,这个问题最后一共收集到了六个公式。
以下逐一为大家分享这些公式和思路。
公式1
=INDEX(A2:I7,MAX((B2:J7=A10)*ROW(B1:B6)),MAX((B2:J7=A10)*COLUMN(A2:I2)))
这算是一个比较常规的思路,要找的数据区域是A2:I7,使用INDEX函数进行查找,需要确定的就是行位置与列位置。
公式的核心部分是MAX的应用,(B2:J7=A10)是用区域中的每个值与要找的姓名做比较,结果是一组9列6行的逻辑值,与ROW(B1:B6)相乘后得到姓名所在的行号,利用F9键可以看到这个结果:
数组中只有一个不为0的数字,再利用MAX函数得到这个数字,就是工号在区域中的行号。
同理利用MAX和COLUMN可以得到列号,INDEX就可以找到对应的ID。
这个公司是一种比较常规的套路,考察的是对数组运算的应用能力,再就是几个函数INDEX、MAX、ROW和COLUMN的基础用法了。
公式2
=INDEX(OFFSET(A2:A7,,SUM(MMULT(IF(A10=A2:J7,1,),ROW(1:10)))-2),MATCH(A10,OFFSET(A2:A7,,SUM(MMULT(IF(A10=A2:J7,1,),ROW(1:10)))-1),))
这个公式是老菜鸟的班学员无悔提供的,公式里用到了INDEX、OFFSET、MMULT、MATCH、SUM和ROW等6个函数,由于OFFSET和MMULT的加入,要详细解释公式就比较费劲,大概思路是用OFFSET(A2:A7,,SUM(MMULT(IF(A10=A2:J7,1,),ROW(1:10)))-2)确定出指定姓名的ID所在的列区域,再用MATCH定位出姓名所在的行,最后用INDEX得到结果。
需要注意的是公式中两个OFFSET的第五个参数的区别,分别是-2和-1,对应就是姓名区域和ID区域。
这个公式可以看做是学员对自己使用函数的综合测试,经常用一些不同的函数解决问题提高会很快的。
除了这个看起来非常复杂的公式之外,无悔还提供了一个比较取巧的公式。
公式3
="A"&SUM(--SUBSTITUTE(IF(A10=B2:J7,A2:I7,),"A",))
之所以说这个公式取巧,是因为发现了所有ID都是字母A和四个数字构成的,所以公式中用了SUBSTITUTE这个替换函数,将与姓名对应的ID中的字母A全部去掉,然后用SUM求和(这有点类似与第一个例子中SUMIF的原理),最后再用&在数字前面连接字母A,还原ID。
以上这三个都是数组公式,都算是比较常规的思路,然而,我们的学霸雪神直接将公式进行了大翻盘,完全改了思路,一起看下学霸的公式长什么样子。
公式4
=MID(PHONETIC(2:7),FIND(A10,PHONETIC(2:7))-5,5)
相信这个公式很多朋友都能看明白,首先用PHONETIC函数将2到7行所有的ID和姓名全部合并到一起,变成了一个长长的字符串,然后用MID-FIND的组合提前套路就把姓名对应的ID搞出来了。
估计大家都会有这种感慨:公式很简单,问题就是想不到!
学霸的最强大脑果然不简单。
学生的表现都如此抢眼,老师们是不是该做点什么呢?
看下鸟神老师的思路,相信会有很多收获的。
公式5
=INDIRECT(TEXT(-SMALL(-IF(B2:J7=A10,ROW(2:7)&-COLUMN(A:I)),1),"rmcd"),)
公式用到了INDIRECT、TEXT、SMALL、ROW、COLUMN和IF。
关键是TEXT函数的妙处,公式的点睛之笔在格式代码"rmcd",INDIRECT函数省略第二参数时(保留逗号)表示使用RC引用模式,也就是用R4C3(第4行第3列)表示C4单元格。因此需要构造出符号RC格式的地址代码,"rmcd"中的rc就是这个意思,至于m和d,那是日期中的月和日。
怎么会扯出来日期呢,ROW(2:7)&-COLUMN(A:I)就可以看作是一种类似与日期的形式(月-日),至于细节问题,就是用减号,IF,逻辑值,SMALL这些去处理。
这个公式完全又是另一种思路,同时对于函数的运用需要达到一定的深度才行。
至于这个问题的最后一个公式,肯定也有人想到了,请继续看。
公式6
=TextJoin("",1,IF(B2:J7=A10,A2:I7,""))
这里用到了一个新版本的office才有的函数TextJoin。
函数可以使用指定的分隔符合并文本字符串,函数格式为:TextJoin(分隔符,是否忽略空值,要合并的内容1,要合并的内容2,……)
本例中不需要分隔符,直接使用两个双引号;忽略空值,第二参数填TRUE或者1,要合并的内容还是使用IF加数组的方式得出。
小结
今天只是通过一个公式练习题为大家分享了各种不同的思路,没有对涉及的函数做细致的讲解,如果需要对某个函数做介绍可以留言。
同时也欢迎喜欢函数,想学习函数的朋友加入我们一起学习,会不定期的发布各种练习题,大家互相讨论交流,将学习函数变成一种乐趣,只有兴趣才是最好的老师!
也有朋友问到,这个题目里选择姓名的哪个下拉选项是用有效性做的,但是自己试了没办法实现动画中的效果;另外就是数据源的颜色跟着选项的结果发生变化是怎么实现的,关于这两个问题,会在后续的文章进行介绍,请保持关注。
联系客服