一对多查询,少不了这两刷子

转载   ExcelHome   2018-06-23   浏览量:450


我们对数据进行查询时,经?;崾褂肰LOOKUP函数。
但有时,我们会碰到这样的问题,提取符合条件的结果是多个,而不是一个,这时候VLOOKUP就犯难了。
举个例子,如下图,左侧A1:C10是一份学员名单表,现在需要根据F1单元格的“EH图班”这个指定的条件,在F2:F10单元格区域中,提取该班级全部学员名单。

今天说一个函数查询方面的万金油套路:Index+Small。
F2单元格输入以下数组公式,按住Ctrl+Shift键不放,再按回车键,然后向下填充:
=INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1))),"")
这个公式看起来可就比上面那个VLOOKUP的解法苗条养眼多了,坦白的说,很搭俺星光十年后的匪号——小清新。

1、公式讲解
IF(A$1:A$10=F$1,ROW($1:$10),4^8)
这部分,先判断A1:A10的值是否等于F1,如果相等,则返回A列班级相对应的行号,否则返回4^8,也就是65536,一般情况下,工作表到这个位置就没有数据了。
结果得到一个内存数组:
{65536;2;3;65536;65536;65536;65536;8;65536;10}

SMALL函数对IF函数的结果进行取数,随着公式的向下填充,依次提取第1、2、3……n个最小值,由此依次得到符合班级条件的行号。
随后使用INDEX函数,以SMALL函数返回的行号作为索引值,在B列中提取出对应的姓名结果。
当SMALL函数所得到的结果为65536时,意味着符合条件的行号已经被取之殆尽了,此时INDEX函数也随之返回B65536单元格的引用,结果是一个无意义的0,为了避免这个问题,可以在公式后面加上一个小尾巴&""
利用&””的方法,很巧妙的规避了无意义0值的出现,只是当查找结果为数值或日期时,这个方法会把数值转变为文本值,并不利于数据的准确呈现以及再次统计分析。

2、练手题
最后留下一道练手题,如下图,根据A1:C10区域的数据,将E列相关班级的姓名,填充到F2:I5区域。



转载请注明来源"ExcelHome"并保留原文链接。
固定链接://www.excelhome.net/lesson/article/excel/1906.html

彩票开奖查询 www.kbyp.net

转载自://www.excelhome.net/lesson/article/excel/1906.html

招聘 不方便扫码就复制添加关注:程序员招聘谷,微信号:jobs1024



excel2013中数据验证(数据有效性)的使用方法介绍
数据验证即数据有效性,那么,excel2013数据有效性在哪里?应该如何使用呢?今天小编就给大家简单介绍一下excel2013中数据验证(数据有效性)的使用方法,希望对大家有所帮助
8张动图,学会Excel中的高级筛选
一起来学习Excel中的高级筛选,看看高级筛选里藏着哪些秘密。
一组实用的自定义格式应用套路
一组非常实用的自定义格式应用技巧。
Excel中的日期时间计算,套路很全
介绍日期时间计算的套路,掌握这些套路,工作效率可以再高一点点。
拆分同一单元格中的姓名,原来这么简单??!
在这个表格内,同一个部门的人员名单都挤到一个单元格内。现在问题来了,要把这样的数据转换成数据列表,该怎么操作呢?
@全体:昨天我脱单了
介绍百分比图表的制作。
大家好,今天我分享的内容是数据有效性
数据有效性不仅能够对单元格的输入数据进行条件限制,还可以在单元格中创建下拉列表菜单方便用户选择输入。
年终总结写了吗?给你一个按年、月、季度汇总数据的妙招
大家日常工作中经?;嵊龅桨茨?、月汇总的问题吧,今天老祝就和小伙伴们一起就看看哪种方法最简单。先来看基础的数据表格,包括日期、销售区域和销售数量等几百条数据:
抱怨加班之前,先看看这13个Excel技巧会不会
在Excel中输入日期时,年月日之间可以使用短横线-或是使用斜杠/作为间隔。但是很多小伙伴输入日期时习惯使用小数点作为间隔,例如2016.5.12这样的。这样的日期输入形式,在Excel中是无法被正确识别的,如何快速转换为规范的日期呢?
就任性!批量更改工作表名称
首先,我们把该工作簿现有工作表的名称提取出来,放置在表格的A列。这样一来,我们就可以很方便的利用函数公式,比如IF、VLOOKUP函数等,根据E:F列的内容,对表名做针对性的修改。