很多人在Excel中用函数公式做查询的时候，都必然会遇到的一个大问题，那就是一对多的查/查询公式应该怎么写？大多数人都是从VLOOKUP、INDEX+MATCH中入门的，纵然你把全部的多条件查方法都学会了而且运用娴熟，如VLOOKUP和&、SUMPRODUCT、LOOKUP(1,0/....，但仍然只能对这种一对多的查询望洋兴叹。
 
这里讲的INDEX+SMALL+IF+ROW的函数组合，就是解决一对多查询的一种通式，如果你能掌握，那在Excel里基本上就没有什么查询你是实现不了的了（除了INDIRECT+RC引用）。
 
下面，我们先来看看示例数据和查询要求：
 
由于VLOOKUP、INDEX+MATCH、LOOKUP(1,0/都只是一对一的查询，有的是只查询第
一个，有的是只查最后一个，所以这种组合对于我们这里的要求完全无用武之地。所以，你也别把精力都花在这个上面，虽然也是可以构造出来的，但今天我们要讲的这个组合，是最基本，也是最容易理解的通式，所以请把精力花在这上面。
 
问题1，解答：
=IF(ROW(A1)>COUNTIF($B:$B,"Sam"),"",INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",ROW($2:$20)),ROW(A1))))     [公式一]
 
=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",ROW($2:$20)),ROW(A1))),"")    [公式二]
 
=INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",ROW($2:$20),4^8),ROW(A1)))&""    [公式
三]

 
首先，这三个都是数组公式，什么是“数组公式”呢？数组公式最特殊也最直接的表现，就是你在单元格里输入完公式之后，要按Ctrl+Shift+Enter三键结束，跟“普通公式”只按Enter结束有明显的区别。countif函数怎么查重复
为什么有三个公式呢？其实这三个公式的作用和核心是一样的，只是应对不同Excel版本、不同数据类型所用到的屏蔽错误值的手法有所差异而已。
下面我们着重讲讲[公式三]，因为这个结构里函数要素最齐全，而组合也是非常符合我们标题所讲到的。
首先我们来简化和分解一下这个公式：
从上图我们不难看出，这个组合就是由INDEX作为主体函数，第一参数就是我们要查询并返回的数据区域，第二参数就是由SMALL构造的一个公式，而SMALL构造的函数，无非就是由IF判断生成的一个内存array。
如果你没有函数基础的话，估计还是理解不了，那我们先返回IF结构的计算结果，也就是判断B2:B20区域，如果等于“Sam”，就返回对应的所在行号，不相等的话，就返回4^8，就是4的8次方幂，即65,536，这在xls格式文档中，相当于最大行号，在xlsx格式则不然。
OK，就我们图中的数据，抹黑SMALL函数的array参数，再按F9，不难返回一个内存数组如下：
 
{65536;65536;65536;65536;6;65536;65536;65536;65536;11;65536;13;65536;65536;65536;65536;18;65536;65536}
 
简化一下，我们用“极大”来表示65536，那结果就是：
{ 极大;极大;极大;极大; 6 ;极大;极大;极大;极大; 11 ;极大; 13 ;极大;极大;极大;极大; 18 ;极大;极大 }
这个内存数组也就是这个公式组合里最关键的，你可以看到SMALL函数的第二参数是RO
W(A1)，这个是返回A1单元格所在的行号，也就是1，当我们整个公式下拉填充之后，就可以得到ROW(A2)、ROW(A3)、ROW(A4)这样的变化，也就是1、2、3、4 …… 这样的自然数序列，从而可以把上面简化了的内存数组里的6、11、13、18给提取出来，因为6是最小值、11是倒数第二小、13是倒数第三小、18是倒数第四小的值，如果还不明白，那请在单元格里输入“=SMALL(”然后按F1查阅SMALL函数的语法和功能说明。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。