首页 > Excel专区 > Excel教程 >

#Excel函数#2.“威力强大”的Index函数

Excel教程 2021-12-31 21:42:37

以我的观点看，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，因为INDEX函数不像OFFSET函数可以跨过提供的区域，它必须从传递的单元格区域或数组里返回一个元素—— 行（或列）在区域外。因此，在这里的设置中或空值意味着什么呢？

如果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)

并且，如果人口在第4列，那么可以创建命名公式：

Population: =INDEX(d,,4)

现在，假设想查找Scotland的人口，我们只需要使用下面的公式：

=INDEX(Population,MATCH(“Scotland”,Country, ))

如果仅执行一次查找的话，这种查找方法在速度方面和VLOOKUP函数执行的速度看齐。但是比VLOOKUP具有优势。它不会像VLOOKUP一样限制查找右侧的列。如果查找国家列表并且以数组方式在整个输出列中输入一个公式，那么它的速度明显高于VLOOKUP。

如果需要返回国家度量列表的多个列，那么速度的优势是相当大的。假设GDP在数据的第2列，资本（Capital）在第11列。

如果在一个新工作表中，想为每个国家输出数据中的3列，人口（Population）、GDP和资本（Capital），那么最有效的方式是在一列中创建共同的索引，然后以数组形式输入INDEX公式到输出的3列中。

在本例中，列B是国家列表，列A将放置公共的索引。在A2中，输入：

=MATCH(B2,Country, )

然后，在列B中向下复制该公式至国家列表的最后一列。

接着，在列C、D和E中以数组形式输入公式到整个单元格区域，一直向下到国家列表的最后一列：

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

更清楚地说，这意味着如果在列B中有99个国家，那么将选择单元格区域C2:E1 ，然后在公式栏中输入上面的公式，接着按Ctrl+Shift+Enter组合键完成输入。

最终的结果是一个公式只执行一次，并且在模型中非常快速地留下很轻便的、非易失性的“脚印”。

求和

结合SUMPRODUCT，INDEX的这种用途是一种极好的方式。例如：

=SUMPRODUCT( (Left(Country,1)=”U”)*Population )

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

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

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

动态区域

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

动态区域通常与OFFSET或INDIRECT函数一起构造。

不巧的是，这两个函数都是易失性函数，这意味着每次工作表中有改变时，包含这些函数的公式都将重新计算。例如，在任意空单元格中添加值将导致所有易失性公式（以及任何依赖它们的其它公式）重新计算。取决于模型设计和大小，可能对模型的扩展性和响应能力产生较大的影响。

INDEX函数可以用于创建动态区域，不仅仅是非易失性，而且比OFFSET或INDIRECT更快。事实上，性能方面的改进是如此大，以致于INDEX是专业模型中所有动态区域的基础。

创建动态区域的方式与使用OFFSET或INDIRECT的方式不同。对于那些函数，在函数里创建动态区域。而使用INDEX，在区域操作符（冒号）的一侧（有时是两侧）产生动态区域。

例如，考虑下面普通的区域引用：

=A2:A1

引用是对国家列表中国家名称的硬引用。假设在列表中没有空，并且在A1中有列标题，可能是“Country”，下面的公式返回列表中最后一个国家的名字：

=INDEX(A:A,COUNTA(A:A))

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

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

=A2:A1

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

但是有实质上的不同。第一个公式是硬编码、静态的引用；第二个公式是非易失的、动态的区域，随着列表中国家数量的变化而扩展或收缩。注意，代替“指向”使用INDEX建立的动态区域的命名公式（Country）的值之前，需要使用绝对引用。如下所示：

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是非易失性的，但是当用于动态区域时它变成半易失性的－但这比易失性更好，因此迄今为止该函数是最好的动态区域基础。

作为对这部分关于动态区域的备注，你可能会问为什么不使用Excel 2 7以后的结构化的表引用？虽然STR是一种强大的选择，但比较笨重。如果需要速度，没有什么比INDEX更好。

不连续的区域

当处理单元格区域时，INDEX提供了第4个参数来从提供的非连续的输入区域中选择区域块。这些区域块通过整数按在输入单元格区域中出现的顺序来引用。例如：

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

返回对data2的引用。并且

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

返回data3的第1列。使用一点小技巧，这对于图表（和许多其它操作）来说是相当有用的。注意，指定区域的数量没有硬性限制，并且这些非连续的区域不需要有相同的大小！

更多

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

在我的词库中，“imposing”这个词的一些同义词是令人印象深刻的（impressive）、特别的（august）、指挥的（commanding）、有效的（effective）、令人激动的（exciting）、华丽的（magnificent）以及令人兴奋的（mind-blowing）。INDEX工作表函数真是“威力无比”（imposing）！


标签： Excel常用函数excel常见问题excel技巧excel教程

上一篇：删除excel空白页的两种方法 下一篇：Excel里Ctrl+F/Ctrl+H使用技巧

相关文章

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.