INDEX+SMALL+IF+ROW

《INDEX+SMALL+IF+ROW》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、很多人在Excel中用函数公式做查询的时候，都必然会遇到的一个大问题，那就是一对多的查找/查询公式应该怎么写？大多数人都是从VLOOKUP、INDEX+MATCH中入门的，纵然你把全部的多条件查找方法都学会了而且运用娴熟，如VLOOKUP和&、SUMPRODUCT、LOOKUP(1,0/....，但仍然只能对这种一对多的查询望洋兴叹。 这里讲的INDEX+SMALL+IF+ROW的函数组合，就是解决一对多查询的一种通式，如果你能掌握，那在Excel里基本上就没有什么查询你是实现不了的了（除了INDIRECT+RC引用

2、）。 下面，我们先来看看示例数据和查询要求： 由于VLOOKUP、INDEX+MATCH、LOOKUP(1,0/都只是一对一的查询，有的是只查询第一个，有的是只查找最后一个，所以这种组合对于我们这里的要求完全无用武之地。所以，你也别把精力都花在这个上面，虽然也是可以构造出来的，但今天我们要讲的这个组合，是最基本，也是最容易理解的通式，所以请把精力花在这上面。 问题1，解答：=IF(ROW(A1)>COUNTIF($B:$B,"Sam"),"",INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",R

3、OW($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+Ent

4、er三键结束，跟“普通公式”只按Enter结束有明显的区别。为什么有三个公式呢？其实这三个公式的作用和核心是一样的，只是应对不同Excel版本、不同数据类型所用到的屏蔽错误值的手法有所差异而已。下面我们着重讲讲[公式三]，因为这个结构里函数要素最齐全，而组合也是非常符合我们标题所讲到的。首先我们来简化和分解一下这个公式：从上图我们不难看出，这个组合就是由INDEX作为主体函数，第一参数就是我们要查询并返回的数据区域，第二参数就是由SMALL构造的一个公式，而SMALL构造的函数，无非就是由IF判断生成的一个内存arr

5、ay。如果你没有函数基础的话，估计还是理解不了，那我们先返回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;65

6、536;65536;18;65536;65536} 简化一下，我们用“极大”来表示65536，那结果就是：{极大;极大;极大;极大; 6 ;极大;极大;极大;极大; 11 ;极大; 13 ;极大;极大;极大;极大; 18 ;极大;极大}这个内存数组也就是这个公式组合里最关键的，你可以看到SMALL函数的第二参数是ROW(A1)，这个是返回A1单元格所在的行号，也就是1，当我们整个公式下拉填充之后，就可以得到ROW(A2)、ROW(A3)、ROW(A4)这样的变化，也就是1、2、3、4……这样的自然数序列，从而可以把上

7、面简化了的内存数组里的6、11、13、18给提取出来，因为6是最小值、11是倒数第二小、13是倒数第三小、18是倒数第四小的值，如果还不明白，那请在单元格里输入“=SMALL(”然后按F1查阅SMALL函数的语法和功能说明。 6、11、13、18代表什么，我们提取出来有什么用呢？回过头去看看IF函数就明白了，原来这就是那些满足条件的记录所在的行号，这样一来我们就可以把一对多的所有符合条件的记录全都提取出来了。 现在回过头来，说说这三个公式都有什么差异和优势？从上面的分解过程我们也可以看到，其实我们只能凭借下拉公式来得

8、到所有满足条件的所有记录，但具体有多少记录我们不清楚，而且不同的条件返回的记录数量也是不确定的，所以这个公式就决定了我们必须要有容错机制，保证公式下拉之后，不因为返回记录数量的不同而显示多余的0值或者错误值，最常见的如#NUM!。----------------------------------------------------------