FIC-Excel 篇九:掌握这个函数,能够实现Vlookup无法完成的多重匹配!

微信公众号:Funincode

难度:Excel高级函数

适用场景:返回多重匹配

阅读时间:5分钟


我们都很清楚如何使用VLookup进行单一的匹配查询,那么有没有办法实现多重匹配查询呢?,比如下图所示:我们需要在F2:F4的区域分别返回查询A2:B6区域,对应产品A在B列的第1,2,3个值。

元数字需要匹配元数字需要匹配



那么怎么实现呢?我们接下来进行一步步分解!

最外层Index函数:Index函数可以返回一个序列中指定位置的数据。如图,我们可以返回在B2到B6区域,排序第1的数。

Index 函数使用Index 函数使用



第2个A出现在第4行,因此我们继续输入函数,将之前的1换成4,就能返回222了!

Index 示例Index 示例



那么接下来的问题就转化为:如何智能的返回A在序列A2:A6的序号1,4,5呢?  接下来就要隆重推出Aggregate函数了!它于数组和ROW函数结合就能实现这个目的!

Aggregate 函数Aggregate 函数



这个看似复杂的函数,我们怎么理解它呢?

AGGREGATE 函数的参数AGGREGATE 函数的参数



它的第一个参数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 哦!

推荐关注:
经验
标签:经验 +关注
办公软件
分类:办公软件 +关注
#FIC-Excel#
系列:FIC-Excel +关注

提示

鼠标移到标签上方,

尝试关注标签~

评论32

发表评论请 登录
  • 最新
  • 最热
评论举报

请选择举报理由

654 32

关注数量超出限制,请先删除部分内容再尝试

关注数量超出限制,
请先删除部分内容再尝试

登录
注册
用户名/邮箱
密码
验证码
看不清?点击更换
看不清?点击更换 忘记密码?

已收藏
去我的收藏夹 >

已取消收藏
去我的收藏夹 >

小提示