欢迎大家继续学习Excel技巧1001系列，第21期
说说那人见人爱，使用了数年的“万金油”公式-INDEX(SMALL(IF(),ROW())))一对多查找根据学校名称，查询所有学生的成绩和排名，典型的1对多查找一个复杂的公式，我们要想解读，得有“法”，得其法，方能解其意解读公式，我们的套路是从内到外，逐层分解，内部结果是外部函数的参数，这才我们的"刀"
=IFERROR(INDEX($A:$E,SMALL(IF($C$2:$C$21=$H$1,ROW($2:$21)),ROW(A1)),MATCH(G$3,$1:$1,0)),"")这里涉及到6个函数，好在这几个函数的难度在Excel都不是很大，解读起来也比较简单第一步：IF($C$2:$C$21=$H$1,ROW($2:$21) )- 获取满足条件的行号
IF函数语法：IF(条件判断，满足条件返回值，不满足返回值)最内层就是我们的IF函数，和一般常规IF函数使用不同，我们这里不是IF(A1=3,1,2)，这种简单的判断，而是一组和单个比较是否相等，而且也没有第三参数，也就是False的值。具体来看看这个部分返回什么：$C$2:$C$21=$H$1
{FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}
这一串什么意思？晕了吧，不用怕，其实就是用学习全称中每一个名称和我们选择的比较，如果一样就返回TRUE，否则就返回FALSE，这点和一般IF一致，只是这么的结果是多个，所以我们的返回值也是多个，ROW函数语法：ROW(单元格或区域) -返回对应的行号，区域为多个行号结果ROW($2:$21)对应的结果是{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}也就是我们数据区域的行号，实际我们自己写的时候肯定和我们的案例中行数不一样大家只要保证$C$2:$C$21 这里2就是数据的起始行，21就是结束行这样的话，我们还有一个疑问就是IF没有使用第三参数？怎么回事？根据官方文档描述，如果我们省略第三参数，默认返回FALSE，而逻辑值在Excel中比数值大，这里会在SMALL部分再讲解，知道默认FALSE即可{FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}二者一一对应，TRUE就返回行号，否则就返回FALSE{FALSE;3;4;5;FALSE;FALSE;FALSE;9;FALSE;11;FALSE;FALSE;FALSE;FALSE;16;FALSE;FALSE;FALSE;20;FALSE}和我们$C$2:$C$21=$H$1 ，判断是否相等对应的ROW($2:$21)结果同步这样我们就得到了和选择的学校一样对应的数据所在行的行号，下一步我们就是把他们逐个取出来以上{}这种返回可以理解成一组数，其实是Excel中的数组，后续可以慢慢学习。SMALL(IF($C$2:$C$21=$H$1,ROW($2:$21)),ROW(A1))第一步我们已经解读过，就简化一下，SMALL(第一步,ROW(A1))这里涉及到两个函数，一个是SMALL，一个是ROW。ROW第一步我们已经涉及，只是讲了一下结果，这里通过案例我们再解释一样一组数中最小的，也就是第一小的，我们可以通过MIN来处理，也可以使用SMALL也就是本案例中的，按上面的语法可以写成我们如果要依次取出 第一小，第二小，第……，这样一个一个写比较麻烦，如果有一个函数，能自动生成1,2,3,4,5……，那就好了，这个时候ROW就出来了，他说他可以……ROW语法参考第一步，这里我们通过，把上面的1换成ROW(A1)，ROW(A1)，当我们公式下拉是就会变成ROW(A2)，依次递增，他们是返回行号，所以也就是会返回我们需要的1,2,3,4,5……{FALSE;3;4;5;FALSE;FALSE;FALSE;9;FALSE;11;FALSE;FALSE;FALSE;FALSE;16;FALSE;FALSE;FALSE;20;FALSE}有了行号了，如果我们再把对一个的列号，也就是 成绩和排名在数据源中的列号获取到，那么行列交叉我们肯定可以获取到对应的数据有的同学肯定就说，就他们两个还是固定的，直接自己输入就行，当然这里可以手动自己输入，但是我们考虑通用性和字段多的情况，我们还是希望可以自动匹配，这样当我们想获取其他字段的数据，只要修改一样名称，就可以自动匹配了函数语法：MATCH(查找数据,单行或者单列,匹配模式) 这里下面还有一个6，我们只返回首次出现的位置，这里我们可能和行号混淆结果这么几个案例，我们应该大体知道MATCH在本案例中的含义了，但是离学会MATCH还很远，MATCH第三参数有三种模式，我们这里使用精确模式，也是最常用的模式。MATCH(G$3,$1:$1,0)，表示，我们使用G3在第一行中查找，出现的位置，也就是在数据源中的位置（列），往右拉，我们可以依次获取到姓名，成绩和排名的列通过以上三步，我们拿到了满足条件的所有行号和列号，下面我们就需要根据二者交叉把数据取出，这个时候取数函数INDEX正式上场INDEX($A:$E,SMALL(IF($C$2:$C$21=$H$1,ROW($2:$21)),ROW(A1)),MATCH(G$3,$1:$1,0))简化：INDEX($A:$E,第一步处理，第二步处理）以上四部，基本完成了我们数据的提取，但是我们选择不同的学校，所返回的结果行数是不同的，这个时候，如果我们再SMALL部分，取的数据比提供的多，就会报错，所以我们需要加上，IFERROR来屏蔽错误加上IFRROR，把错误的部分显示为空，这样就非常完美了以上设计了一组数，我们前面提到过在Excel叫做数组，对应有就有数组公式，数组公式的录入方式和我们一般公式 ，直接按回车有些不一样，需要Ctrl+Shift+Enter三键录入，具体 一手按住CTRL和SHIFT，另一手按下回车键，这细致了吧……如果检查自己录入的方式是否正确，如果你录入后两遍自动多了{} 说明正确，否则没有录入成功
