分享高质量的原创Office教程、Word教程、Excel教程。
位置:主页 > excel教程 >

excel表格一对多查询利用神器index+small的操作方法

发布时间:2019-06-26 02:36:50 来源:www.roce6.com 浏览量:
excel表格已经在我国普及,但有一些新用户会遇见对excel表格一对多查询利用神器index+small进行设置的问题,其实有很多用户都反映过如何设置excel表格一对多查询利用神器index+small的问题,那么到底怎样才能快速的设置excel表格一对多查询利用神器index+small呢?其实很简单,只需要按照F2单元格输入以下数组公式,按住Ctrl+Shift键不放,再按回车键,然后向下填充:来处理就搞定了,现在详细的给大家讲下excel表格一对多查询利用神器index+small的具体步骤:

 

但有时,我们会碰到这样的问题,提取符合条件的结果是多个,而不是一个,这时候VLOOKUP就犯难了。

举个例子,如下图,左侧A1:C10是一份学员名单表,现在需要根据F1单元格的“EH图班”这个指定的条件,在F2:F10单元格区域中,提取该班级全部学员名单。

e1

今天说一个函数查询方面的万金油套路: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、如果是要做成横向的,又要怎么写函数呢?

这里是隐藏的:=INDEX($B:$B,SMALL(IF(A$1:A$10=$F2,ROW($1:$10),4^8),Column(A$1)))