深入剖析Excel万金油筛选公式“指数-小if-行”

本文分享了excel使用公式筛选完成一对多搜索，这是一个经典的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)))

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

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

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

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

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

看上图。销售部门有四条记录，分别在数据区的第5、8、9、16行(数据区从第二行开始)。

所以我们希望在公式下拉的时候，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个数据，所以IF的第三个参数用99就够了。如果数据量很大，可以用9 ^ 9，也就是9的9次方。反正够了。

搞清楚了这个IF，再来看看这个小(if ($ c $2: $ c $21 = $ f $2，row ($1: $20)，99)，row (a1))是不是没那么晕。

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

万金油的核心是指数，小，如果和行。请反复思考这部分原理。还有很重要的一点需要强调，万金油公式是一个数组公式，我们需要在输入前按住Ctrl和shift。

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

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

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

相关学习推荐:excel教程以上是深入分析Excel万金油筛选公式“INDEX-SMALL-IF-ROW”的详细内容。更多请关注主机参考其他相关文章！

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

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

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