FIC-Excel 篇九:掌握这个函数,能够实现Vlookup无法完成的多重匹配!
微信公众号:Funincode
难度:Excel高级函数
适用场景:返回多重匹配
阅读时间:5分钟
我们都很清楚如何使用VLookup进行单一的匹配查询,那么有没有办法实现多重匹配查询呢?,比如下图所示:我们需要在F2:F4的区域分别返回查询A2:B6区域,对应产品A在B列的第1,2,3个值。
那么怎么实现呢?我们接下来进行一步步分解!
最外层Index函数:Index函数可以返回一个序列中指定位置的数据。如图,我们可以返回在B2到B6区域,排序第1的数。
第2个A出现在第4行,因此我们继续输入函数,将之前的1换成4,就能返回222了!
那么接下来的问题就转化为:如何智能的返回A在序列A2:A6的序号1,4,5呢? 接下来就要隆重推出Aggregate函数了!它于数组和ROW函数结合就能实现这个目的!
这个看似复杂的函数,我们怎么理解它呢?
它的第一个参数function_num, 通过输入不同的值,我们可以选取不同的计算逻辑,这里因为我们要按顺序从小到大选择匹配的值,因此我们选择SMALL函数,它的参数为15
第二个参数option,可以让我们选择是否忽略隐藏和错误值,此时我们选择3输入。
第三个参数需要录入分析的数组,我们在这里使用了一个很复杂的数组公式,其中($A$2:$A$6=$E$2)会产生一个(TRUE,FALSE,FALSE,TRUE,TRUE)的数组,将其除以自身,会转换成一个 (1, #div0, #div0, 1, 1)的新数组,让我们再看(ROW($A$2:$A$6)-ROW($A$1)这个数组,它会形成一个从1开始的顺序序列 (1,2,3,4,5),与之前的数组相乘,便得到了下图H2:H6的数组序列了!
将这个顺序序列嵌入到之前的Aggregate函数中作为第3个参数,这三个参数就很好的确定了需要分析的集合为(1, #div0, #div0, 4, 5),从当中从小到大选择排位第k的数据,且忽略隐藏和错误!那么这里的k就是我们的第4个参数!
这里的k我们使用ROWS($A$2:A2)函数,rows函数能返回所选区域的行数,通过锁定$A$2, 我们通过下拉可以增加区域的行数,进而让k能够从1变成2,变成3。。。。。。
整合以后就是完整版的Aggregate函数了!
最后我们把aggregate函数替换之前Index函数的第二个参数,就形成了最终公式!怎么样,快来练习吧!
在Excel2016里,还有一种更为简单的办法,让我们在下期节目介绍! 请关注我们 FuninCode 哦!
tomy227
校验提示文案
明月镇魂
校验提示文案
unsteady
校验提示文案
1024树新蜂
校验提示文案
相濡以沫灬
校验提示文案
切尔西伟伟
校验提示文案
missingmoonx
校验提示文案
值友8116882023
校验提示文案
schrislowv
校验提示文案
岁月抹不去的爱念
校验提示文案
茕茕白兔的小屋
校验提示文案
睡个觉真不容易
校验提示文案
mediomid2019
校验提示文案
家乡味
校验提示文案
热心市民yang大哥
校验提示文案
Ted_Y
校验提示文案
值友9380077420
校验提示文案
[已注销]
校验提示文案
值友2646088095
校验提示文案
竹棚雨
校验提示文案
tonyarei
校验提示文案
多剁躲
校验提示文案
unsteady
校验提示文案
挣扎的小强
校验提示文案
119麻烦
校验提示文案
竹棚雨
校验提示文案
明月镇魂
校验提示文案
值友2646088095
校验提示文案
[已注销]
校验提示文案
值友9380077420
校验提示文案
Ted_Y
校验提示文案
热心市民yang大哥
校验提示文案
tomy227
校验提示文案
家乡味
校验提示文案
mediomid2019
校验提示文案
睡个觉真不容易
校验提示文案
茕茕白兔的小屋
校验提示文案
岁月抹不去的爱念
校验提示文案
schrislowv
校验提示文案
值友8116882023
校验提示文案