打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
详述Excel中“一对多”查询的两种方式,孰优孰劣一看便知

Excel中的“一对多”查询指的是根据目标值,一次性查询出所有匹配条件的记录。比如,有图1所示的一张数据表,我们要根据D2单元格的姓名,查找出该学生所有的选课结果。

图1

有两种方法可以得到最终的结果,第一种是用VLOOKUP函数,第二种是用INDEX+MATCH函数组合,我们下面来逐一介绍它们的操作方式,并比较二者在查询方式上的共同点和区别,由此总结出“一对多”查询的规律,找出最佳的“一对多”查询方法。

用VLOOKUP函数实现“一对多”查询

我们都知道VLOOKUP函数是按从左至右的顺序查询的,需要将查询区域的第一列和查询条件作匹配(图1的查询条件为D2单元格的值,查询范围为A2:B15)。因此我们需要在A列之前插入辅助列,用以统计D2单元格值第几次出现。

我们在A2单元格输入如下公式,然后向下复制到A15。=(B2=$E$2)+N(A1)

公式中的(B2=$E$2)是为了将B2与$E$2作一对比。B2采用相对引用,因为向下复制的过程中,要将B2、B3...B15逐个与$E$2做对比;而$E$2采用绝对引用,是因为在向下复制的过程中,它的引用位置是要被锁定的。如果对比成功则为TRUE,否则为FALSE,TRUE和FALSE在参与运算的时候会转换成1和0。

公式中的N(A1)是Excel中的信息函数,也是excel中最短的函数之一,它的作用是将非数值形式的值转化为数字,将数值转换成数字,日期转换成序列值,TRUE转换成1,其它对象转换成0,因此N(A1)=0。

因此公式(B2=$E$2)+N(A1)可以统计出当前行对应的姓名是条件值(这里指“袁绍环”)第几次出现。匹配结果如图2所示。

图2

然后我们在F2单元格输入如下公式,然后向下复制到F15。=IFERROR(VLOOKUP(ROW(1:1),A:C,3,0),'')

因为VLOOKUP函数如果查询不到结果会返回“#NA”错误信息,所以这里用IFERROR函数来屏蔽错误信息。ROW(1:1)返回的结果是第1行的行序号,即1,这里之所以要用1:1(即第一行的引用),是因为在公式向下复制的过程中会自动变成ROW(2:2)、ROW(3:3)...ROW(14:14),对应返回的值为2、3...14,当然此函数也可以换成ROW(A1)或者ROW(B1)等第一行单元格的引用,其效果是一样的。

VLOOKUP会返回匹配条件的第一条记录,它的格式是这样的:LOOKUP(查找区域,匹配条件,返回查找区域的第几列,精确匹陪0/模糊匹配1)

因此,上述公式在向下复制的过程中,可以精确匹配E2单元格第1到n次的所有记录。最终完成的查询结果如图3所示。

图3用INDEX+MATCH函数组合实现“一对多”查询

INDEX+MATCH函数组合对于数据查询,并没有要求查询区域的第一列必须是条件匹配的一列,这是它比VLOOUP函数高明的地方。这对函数组合的实质是先通过MATCH函数定位到查询条件在其查询区域的索引,然后用INDEX定位数据区域的对应索引位置。

我们只需要将F2单元格中的公式,换成如下所示,即可得到查询结果。=IFERROR(INDEX(C:C,MATCH(ROW(1:1),A:A,0)),'')

关于INDEX+MATCH函数进行“一对多”查询的具体细节,大家可以参考我的另一篇文章:Excel中的一对多查询,INDEX+MATCH黄金组合的另一种高级用法

图4

我们甚至可以将E2单元格做成下拉菜单,通过不同姓名匹配不同的查询结果,如图5所示。

图5

关于下拉菜单如何制作大家可以参考我的另一篇文章:Excel中制作下拉菜单的2种方法,1分钟快速掌握总结

通过上述两种“一对多”查询方式的对比分析,不难发现二者的操作步骤非常类似,都需要先添加辅助列,统计出查询条件第几次出现,然后使用VLOOUP函数或者MATCH函数,将对姓名的查询,转化成了对序列数字1,2...n的查询。

二者最大的区别是VLOOKUP函数要求查询区域的第一列必须是和查询条件做匹配的一列,而INDEX+MATCH函数组合则没有这样的要求,因此我们甚至可以将辅助列移动到任意一列!

因此,一般情况下我们要进行“一对多”查询的话,最好还是选择INDEX+MATCH函数组合吧!

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
你知道如何用VLOOKUP提取同一条件的多个结果吗?
Match函数 | 完美Excel
excel函数技巧:一对多查找的典型案例分析
一篇文章带你全面掌握Excel中的各种数据查询知识与技巧
Excel中的VLOOKUP函数,8种使用技巧与你分享
如何用EXCEL制作出带导航条的图片查询工具?
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服