index+n+if函数的组合

您现在的位置 :

首页 > 资讯 > > 正文

时间 :2023-02-23 11:56:24 来源 : 卓越科技

今天要讲的内容可能是绝大多数excel使用者没有接触过的知识，它是关于index函数输出数组结果的一个特性应用。

当然，这个特性它包含了与其他函数的嵌套组合，从而彻底解决了index函数一次只能得到一个结果的缺点。

下面以实例来进行操作和讲解。

【资料图】

下图数据表是不同公司各季度的销售数据，现在需要通过类别来统计公司名称，也就是要查找并引用类别为日用百货的所有公司名称！

我们通过一个动图来展示所需的效果：

这里作者制作了一个下拉菜单，通过点击选择任意类别，右侧的公式结果区域都会实时更新。

那么重点就是公式的设置。

常规的index函数动态引用，会组合match函数来计算，如下图公式所示：

=INDEX(A2:B11,MATCH(G2,B2:B11,0),1)

match函数是返回查找值在列表中的位置，常嵌套在index、vlookup、offset等函数公式中，但它只能返回查找到的第1个值，则意味着只会输出一个结果，因此无法达到任务的需求。

因为同一个类别下可能会包含多个公司，因此作者在标题中特意写到了“一对多查找引用”的表述。

接下来作者将使用if函数来代替match函数应用到index函数的第2参数中。

如下图所示，公式为：

=INDEX(A2:B11,IF(B2:B11=G2,ROW($1:$10),""),1)

IF(B2:B11=G2,ROW($1:$10),"")，这个表达式好理解，就是当列表为G2的值时，就返回row函数对应的结果，否则返回空值。

我们按下F9来查看一下if函数表达式的结果，它是由数值和空值组成的数组。

那么按照正常的逻辑，如果index函数第2参数是一个数组，那么三键运行将执行数组运算，得到相应的多个结果。

但实际情况是，index公式仍然只会输出一个结果！

这就是下面作者要继续介绍的index+n+if函数的数组特性组合。

不过在之前我们还要进行一个操作，即使用small函数对if函数结果进行排序。

如下动图所示，small函数会将列表中的数字排序在前，其他非数值数据则显示为“#num！”

经过一系列的条件设置后，我们在表达式的外部继续组合一个N+if函数的特性组合，其公式为：

=INDEX(A2:B11,N(IF(1,SMALL(IF(B2:B11=G2,ROW($1:$10),""),ROW($1:$10)))),1)

这里N+if它到底有什么用？！我们还需要拉上index函数一起来说，Index+N+IF的组合套路，会使得index函数公式能够输出一个数组结果，就如同lookup会返回最后一个值一样，就是它们的一个特性，一个内置的规则，并不需要深究细挖，记住这个用法即可！

接下来我们按下F9，来看看N+IF函数表达式的结果，它与small函数表达式的结果是一样的。

从这个角度来讲，加上N+IF的函数组合实则是多此一举，但index加上这个组合就完全不同了。

最后我们拉取要填充公式的单元格区域，然后在公式编辑栏中添加一个容错函数——iferror，随后三键结束公式！

所以最后完整的数组公式为：

{=INDEX(A2:B11,N(IF(1,SMALL(IF(B2:B11=G2,ROW($1:$10),""),ROW($1:$10)))),1)}。

现在我们再总结一下今天的所学内容，主要是关于index函数第2参数使用if函数表达式的应用，以及index+n+if函数组合的特性应用，当然，其中出现的small函数也不容忽视，它起到了将有效结果排序在前位的作用。

从这个公式可见，函数知识一定要扎牢，至少要了解各函数的基本语法和基础用法！

最后，作者预报一下下期的内容，将在这节基础上，来计算一个包含大量数据的真实案例。

举报/反馈

标签：