#Excel函数#2 “强大”的索引功能

在我看来，INDEX函数是Excel中最重要的工作表函数。

现在，考虑这个函数的单调名称似乎令人惊讶。那么，是什么让INDEX函数如此强大呢？它不挥发，光亮，柔韧，用途广泛。INDEX可以返回一个值或一组值，并且可以返回对单元格的引用或对单元格区域的引用。INDEX可以很好地与三个引用操作符一起使用，即冒号、空格和逗号。

使用INDEX的想法是为它提供一个区域(或数组)，然后指定要返回的元素。因此，

=INDEX(A1:A5，2)

将返回对单元格A2的引用。

而且，

=INDEX({95，96，97，98，99}，5)

将返回值99。

到目前为止，没有什么值得注意的。但关键是要理解，所以再解释一下，上面第一个例子返回一个引用，第二个例子返回一个值。

同时，请注意上面两个例子演示了INDEX在传递向量时的行为。向量是指一维数组或单元区域。传递向量时，INDEX不关心向量是垂直的还是水平的。INDEX的第二个参数表示行号。然而，当转移向量时，这是不正确的。第二个参数的输入变成了矢量元素号，而不是行号，这就是为什么水平矢量可以很好地处理这个符号。在上面的第二个例子中，5不是行号，而是元素号！

但是您可以强制INDEX以正常的二维方式工作：

=INDEX({95，96，97，98，99}，1，5)

还返回值99。

而且，这种二维的方式让INDEX真正展现了它的威力。很明显，

=INDEX(A1:C5，1，3)

将返回对C1的引用。但令大多数用户惊讶的是，以下两个公式的结果是一样的：

{=INDEX(A1:C5，3)}

{=INDEX(A1:C5，3)}

返回整行或整列。

事实上，除非用户理解这里发生了什么，否则他们可能会认为这个函数中有Bug，因为与OFFSET函数不同，INDEX函数可以跨越提供的区域，并且它必须从传递的单元格区域或区域外有——行(或列)的数组中返回一个元素。那么，在这里的设置中，空值是什么意思呢？

如果row_num参数为或缺失，则表示INDEX返回column_num参数指定的整个列！

{=INDEX(A1:C5，2)}

将返回对单元格区域B1:B5的引用。

反之亦然，即设置或缺少column_num参数将返回整行。在这种情况下，缺失的column_num参数必须包含逗号，例如：

{=INDEX(A1:C5，2，)

将返回对单元格区域A2:C2的引用。

但是，请记住，当特定单元格被提供给多单元格区域或单元格被设置为等于数组时，输出单元格中仅显示第一个元素，因此上面两个INDEX公式返回的大多数行或列都将被隐藏。但是，您可以将数组中的公式输入单元格区域，以查看完整的输出。首先选择输出单元格区域，在公式栏中输入公式，然后按Ctrl+Shift+Enter。

因此，使用以下技术返回二维数组的整行或整列：

=SUM(INDEX({1，2，3，4，5；6,7,8,9,1 ;11,12,13,14,15},3,))

结果是65，这是输入数组的整个第三行的总和。

寻求

从更大的单元格区域或数组中返回整个列或行的能力非常有用。

假设单元格区域A1:M2中有一个国家测量表或列表，可以创建一个引用该区域的命名公式——，简单地用d表示数据区域，然后可以非常流畅地命名数据区域的各个列。如果国家名称位于第一列，您可以创建一个命名公式：

Country:=INDEX(d，1)

并且，如果人口在第四列，您可以创建一个命名公式：

Population:=INDEX(d，4)

现在，假设我们想找到苏格兰的人口，我们只需要使用以下公式：

=INDEX(人口，匹配(“苏格兰”，国家，)

如果只进行一次搜索，这种搜索方式符合VLOOKUP函数的执行速度。但是它比VLOOKUP有优势。它不像VLOOKUP那样限制找到正确的列。如果查找国家列表，在数组的整个输出列中输入一个公式，比VLOOKUP快很多。

如果需要返回多个列的国家公制列表，速度的优势是相当可观的。假设国内生产总值在第2栏，资本在第11栏。

在新的工作表中，如果要为每个国家输出三列数据，即人口、GDP和资本，最有效的方法是在一列中创建一个公共的index，然后将INDEX公式以数组形式输入到输出的三列中。

在这个例子中，列b是一个国家列表，列a将放置一个公共索引。在A2中，输入：

=MATCH(B2，国家，)

然后，将b列中的公式复制到国家列表的最后一列。

然后，在c、d和e列中以数组的形式输入公式，一直到国家列表的最后一列：

{=INDEX(d，A2:A1，{4，2，11})}

更具体地说，这意味着如果b列有99个国家，将选择单元格区域C2:E1，然后在公式栏中输入上述公式，然后按Ctrl Shift Enter键完成输入。

最终的结果是一个公式只执行一次，一个非常轻且不挥发的“足迹”很快就留在了模型中。

求和

结合SUMPRODUCT，使用INDEX是一个很好的方法。例如：

=SUMPRODUCT((左(国家，1)=“U”)*人口)

返回以字母u开头的所有国家的总人口。

命名公式后，下面的公式也很好：

=SUMPRODUCT((Left(INDEX(d，1)，1)=“U”)* INDEX(d，4))

动态区域

您可以更改命名公式d，使结果位于动态区域，而不是固定区域。此时，INDEX是至高无上的。

动态区域通常由偏移或间接函数构成。

不幸的是，这两个函数都是不稳定的，这意味着每当工作表发生变化时，包含这些函数的公式都将被重新计算。例如，向任何空单元格添加值将导致所有易失性公式(以及依赖于它们的任何其他公式)被重新计算。根据模型的设计和大小，它可能会对模型的可伸缩性和响应性产生很大影响。

INDEX函数可用于创建动态区域，它不仅是非易失性的，而且比OFFSET或INternet更快。事实上，性能提升如此之大，以至于INDEX是专业模型中所有动态区域的基础。

创建动态区域的方式不同于使用偏移或间接的方式。对于这些函数，在函数中创建动态区域。使用INDEX，在区域操作符(冒号)的一侧(有时是两侧)生成动态区域。

例如，考虑以下公共区域参考：

=A2:A1

引用是对国家/地区列表中某个国家/地区名称的硬引用。假设列表中没有空白，A1中有一个列标题，可能是“国家”，下面的公式返回列表中最后一个国家的名称：

=INDEX(A:A，COUNTA(A:A))

在这里，INDEX函数真正返回A列中最后一个带有国家名称的单元格的引用，它等于符号强制返回的单元格的值。在我们的例子中，INDEX公式实际上返回引用A1。

因此，以下两个公式指向完全相同的区域：

=A2:A1

=A2:INDEX(A:A，COUNTA(A:A))

但是有很大的不同。第一个公式是硬编码的静态引用。第二个公式是一个不稳定的动态区域，随着列表中国家数量的变化而扩大或缩小。请注意，您需要使用绝对引用，而不是“指向”使用索引创建的动态区域的命名公式(国家)的值。如下图所示：

country :=$ A $ 2: index(A : $ A，COUNTA(A : $ A))

可以使用相同的技术创建二维动态区域，因此列数也是动态的：

d :=$ A $ 2: index($ 1: $ 65535，COUNTA($A:$A)，COUNTA($1:$1))

这种动态模式由d定义，上面显示的精彩的行和列引用仍然可以执行，例如：

{=INDEX(d，$A2，{4，2，11})}

描述，当在三个引用运算符中的任何一个的一侧或两侧使用函数时，当打开工作簿时，结果公式将始终被重新计算。因此，尽管INDEX是非易失性的，但在动态区域使用时，它会变成半易失性的——但它比volatile更好，因此该函数是迄今为止动态区域的最佳基础。

作为本节中关于动态区域的注释，您可能会问为什么在Excel 2 7之后不使用结构化表引用。虽然STR是一个强大的选择，但它很麻烦。如果你需要速度，没有什么比INDEX更好了。

不连续区域。

当处理一个单元区域时，INDEX提供第四个参数从提供的不连续输入区域中选择一个区域块。这些区域块由整数按照它们在输入单元格区域中出现的顺序引用。例如：

=INDEX((data1，data2，data3)，2)

返回对数据2的引用。和

{=INDEX((data1，data2，data3)，1，3)}

返回数据3的第一列。使用一点技巧，这对图表(和许多其他操作)非常有用。注意，指定区域的数量没有硬性限制，这些不连续的区域不需要大小相同！

更多

所有这些只是触及这个关键工作表函数的表面。

在我的词库里，“影响”这个词的一些同义词令人印象深刻，特别，居高临下，有效，令人兴奋，宏伟和令人惊叹。INDEX函数真的很“影响”！