Excel万金油筛选公式“指数-小IF-行”深度解析

本文分享excel使用公式过滤完成一对多搜索，这是一个经典的excel过滤函数公式自动查找公式数据。

总听专家说有万能公式，但是什么是万能公式，这个Excel公式能干什么？我们来看看下面这张效果图:

这个例子是一个典型的一对多搜索。搜索条件是部门，数据源中的每个部门对应多个数据。万能公式的主要目的是解决一些相对复杂的问题，比如一对多搜索。上面动画中的公式是:

=IFERROR(INDEX($A$2:$D$21，SMALL(IF($C$2:$C$21=$F$2，ROW($1:$20)，99)，ROW(A1))，MATCH(F$3，$A$1:$D$1，0))，& quot")

看到这个公式，或许很多朋友会惊叹:这么长的公式看不懂！

今天就和大家一起破解这个看不懂却很厉害的公式套路，耐心往下看...

上面的公式使用了六个函数:IFERROR、INDEX、SMALL、IF、ROW和MATCH，其中IFERROR和MATCH是本例中的两个辅助函数，另外四个INDEX-SMALL-IF-ROW是万能公式。

所以我们先来学习一下这个核心部分的原理:

F4细胞的公式是:

=INDEX($A$2:$A$21，SMALL(IF($C$2:$C$21=$F$2，ROW($1:$20)，99，ROW(A1)))

从INDEX开始，这个函数的基本作用是给出一个区域，然后根据对应的行列位置返回搜索结果。上图中按索引搜索的数据区域就是名称所在的区域:$A$2:$A$21。

INDEX函数的基本结构是:INDEX(搜索范围，哪一行哪一列)。如果范围是单行或单列，则可以省略最后两个参数中的一个。一般来说，拿着电影票找座位的话，整个厅的座位就是面积，第一排的座位就是公式中的后两个参数。这样，你就可以准确地找到目标位置。

在上面的例子中，范围在一列中，所以我们只需要确定每个数据在哪一行。

知道了这一点，我们应该关注指数的第二个参数:

小(IF($C$2:$C$21=$F$2，ROW($1:$20)，99)，ROW(A1))

注意上图。营业部有四条记录，分别在数据区的第五行、第八行、第九行、第十六行(数据区从第二行开始)。

所以我们希望在公式下拉的时候，INDEX的第二个参数分别是5，8，9，16这四个数字(这个一定要理解)。

注意，接下来，我们将接触到万金油的核心部分。请保持高度集中...

小函数的基本结构:小(一组数，最小的数)

建议您模拟一个简单的数据来全面理解这个函数。该方法如下:

在A列输入一些数字，公式的意思是这一列最小的数字，结果是2，很好理解吧？将公式的第二个参数更改为2，然后查看结果:

第二小的是4。

如果想继续得到第三小的数，我想大家都可以想到怎么做，但是会有一个问题。我们只能手动修改第二个参数，不能通过下拉来改变这个参数。如果我们想把它拉下来，我们需要对第二个参数使用ROW函数，就是这样修改它:

ROW函数很简单，获得参数的行号。通过这个公式，我们把A列的数据从小到大排序。你认为它有趣吗？

回到我们的万能药公式，5、8、9、16这四个数字分别代表什么？记住，我们需要使用小函数来依次获得这四个数字。思路是判断C列和F2是否一致，如果一致，我们会得到一个大于最大行数的数(为了防止被发现):

要达到这个目的，就需要IF功能的介入，所以有:

If ($ c $2: $ c $21 = $ f $2，row ($1: $20)，99)，用这段作为SMALL的第一个参数。

关于这个如果，就比较容易理解了。我们可以借助F9来看看这个公式的结果:

因为我们的数据只有20，所以用99作为IF的第三个参数就足够了。如果数据量比较大，可以用9 ^ 9，也就是9的9次方，反正也够用了。

如果我们理解了这个IF，我们来看看这个小(IF ($ c $2: $ c $21 = $ f $2，row ($1: $20)，99)和row (a1))是不是没那么晕。

关于小部分，我们必须明白，当公式被拉下来的时候，我们可以一个一个的得到我们想要的数字，然后把这些数字作为索引的第二个参数，得到最终想要的结果。

万金油的核心是指数，小，如果和行。请仔细考虑这部分原则。还有很重要的一点需要强调，万金油公式是一个数组公式，我们需要按Ctrl和shift再输入。

至于初始公式，考虑到要查找多列的内容，INDEX的数据区使用$A$2:$D$21，当有多列时，需要列位置来查找目标值，所以使用MATCH(F$3，$A$1:$D$1，0)来确定数据在哪一列。

每个部门的数据不一样，需要把公式往下拉几行，会产生一些错误的值。在公式的最外层使用IFERROR函数来屏蔽错误的值，使得查询结果看起来非常干净。

今天我只是用一个一对多搜索的例子来解释万金油的配方原理。其实万金油的套路还是很多的。如果你愿意，以后可以继续分享相关的例子。当然，如果你看完这篇文章后能自己解读一些复杂的公式就更好了。

相关学习推荐:excel教程

这几篇文章你可能也喜欢：

• 如何使用Excel控件（Excel控件）
• 在Excel中按名称查询照片很简单（如何在Excel中按名称搜索照片）
• 使用Excel网格实现项目进度图表（如何使用Excel创建项目进度条）
• 在Excel中轻松查看任意条件下的数据（如何在Excel中根据条件搜索数据）
• 下面讲一下Excel函数学习中的多重求和函数（Excel中如何对多个数据求和）

本文由主机参考刊发，转载请注明：Excel万金油筛选公式“指数-小IF-行”深度解析 https://zhujicankao.com/115482.html