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

2018-09-10 13:00:03 63点赞 666收藏 35评论

微信公众号: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 哦!

展开 收起

Office 365 家庭版

Office 365 家庭版

269元起

《Agenda》Mac数字版软件

《Agenda》Mac数字版软件

暂无报价

《1Password》 ios密码管理软件

《1Password》 ios密码管理软件

暂无报价

《vegas pro 14 edit》视频编辑软件

《vegas pro 14 edit》视频编辑软件

132元起

《Money Pro》 记账理财软件

《Money Pro》 记账理财软件

6元起

用友软件 好会计财务软件专业版追加会计角色1用户/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

用友软件 好会计财务软件专业版追加会计角色1用户/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

301元起

ps cc2019 零基础自学 视频教程

ps cc2019 零基础自学 视频教程

5元起

用友软件 好会计财务软件普及版追加1账套/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

用友软件 好会计财务软件普及版追加1账套/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

25元起

《Lossless Photo Squeeze(无损图片瘦身)》

《Lossless Photo Squeeze(无损图片瘦身)》

暂无报价

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

499元起

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

898元起

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

1999元起
35评论

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

请选择举报理由

相关文章推荐

更多精彩文章
更多精彩文章
最新文章 热门文章
666
扫一下,分享更方便,购买更轻松