十五个常用excel公式（彻底搞定-Excel万金油公式）

欢迎大家继续学习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($2:$21)

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就是结束行和ROW中的数值同步一样即可

这样的话，我们还有一个疑问就是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从小到大逐个提取-行号

SMALL(IF($C$2:$C$21=$H$1,ROW($2:$21)),ROW(A1))

第一步我们已经解读过，就简化一下，SMALL(第一步,ROW(A1))

这里涉及到两个函数，一个是SMALL，一个是ROW。ROW第一步我们已经涉及，只是讲了一下结果，这里通过案例我们再解释一样

先来看SMALL函数，一个案例搞定

SMALL函数语法：SMALL(一组数,第几小）

一组数中最小的，也就是第一小的，我们可以通过MIN来处理，也可以使用SMALL也就是本案例中的，按上面的语法可以写成

=SMALL($A$1:$A$9,1)

我们如果要依次取出 第一小，第二小，第……，这样一个一个写比较麻烦，

如果有一个函数，能自动生成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}

SMALL(第一步,ROW(A1))也就是把数据从小到大这个提取出来，配合下一步使用3;4;5;9;11;16;20

有了行号了，如果我们再把对一个的列号，也就是 成绩和排名在数据源中的列号获取到，那么行列交叉我们肯定可以获取到对应的数据

有的同学肯定就说，就他们两个还是固定的，直接自己输入就行，当然这里可以手动自己输入，但是我们考虑通用性和字段多的情况，我们还是希望可以自动匹配，这样当我们想获取其他字段的数据，只要修改一样名称，就可以自动匹配了

第三步：结果需要的列号如何匹配

这里我们要学习一下MATCH函数

函数语法：MATCH(查找数据,单行或者单列,匹配模式) 结果：返回查找值在区域中首次出现的位置

两个小案例看一下效果这里下面还有一个6，我们只返回首次出现的位置，这里我们可能和行号混淆

再看一例：

这里的位置是相对于区域开始的位置来的，不是行列

列使用案例

结果这么几个案例，我们应该大体知道MATCH在本案例中的含义了，但是离学会MATCH还很远，MATCH第三参数有三种模式，我们这里使用精确模式，也是最常用的模式。

回到主题中MATCH(G$3,$1:$1,0)，表示，我们使用G3在第一行中查找，出现的位置，也就是在数据源中的位置（列），往右拉，我们可以依次获取到姓名，成绩和排名的列

通过以上三步，我们拿到了满足条件的所有行号和列号，下面我们就需要根据二者交叉把数据取出，这个时候取数函数INDEX正式上场

第四步：INDEX上场-行列交叉取数都靠他

函数语法：INDEX(区域,行,列)

结果返回：行列交叉的值

案例：第2行 第3列交叉的值

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,第一步处理，第二步处理）这样我们就可以拿到全部的值了

第五步：容错处理，IFERROR修补

函数语法：IFERROR(原公式,出错后显示)

以上四部，基本完成了我们数据的提取，但是我们选择不同的学校，所返回的结果行数是不同的，这个时候，如果我们再SMALL部分，取的数据比提供的多，就会报错，所以我们需要加上，IFERROR来屏蔽错误

加上IFRROR，把错误的部分显示为空，这样就非常完美了

以上设计了一组数，我们前面提到过在Excel叫做数组，对应有就有数组公式，数组公式的录入方式和我们一般公式 ，直接按回车有些不一样，需要

Ctrl Shift Enter三键录入，具体 一手按住CTRL和SHIFT，另一手按下回车键，这细致了吧……

如果检查自己录入的方式是否正确，如果你录入后两遍自动多了{} 说明正确，否则没有录入成功

不是手工添加，是自动产生

打完收工……

更多精彩，欢迎关注Excel办公实战

,