INDEX函数的可处理的一些实例使用方法详解

INDEX函数的可处理的一些实例使用方法详解发布时间：07-15来源：未知

以我的观点看，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,0,3)}

{=INDEX(A1:C5,,3)}

返回整行或整列

事实上，除非用户理解这里发生了什么，否则他们可能会认为该函数存在一个Bug，因为INDEX函数不像OFFSET函数可以跨过提供的区域，它必须从传递的单元格区域或数组里返回一个元素——0行(或0列)在区域外。因此，在这里的设置中0或空值意味着什么呢?

如果row_num参数为0或者缺失，那么这指示INDEX返回由column_num参数指定的整列!

{=INDEX(A1:C5,,2)}

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

并且相反的情况也成立，即将column_num参数设置为0或缺失将返回整行。此时，缺失的column_num参数必须包括逗号，例如：

{=INDEX(A1:C5,2,)}

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

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

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

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

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

查找

从更大的单元格区域或数组中返回整列或整行的功能是相当有用的。

假设在单元格区域A1:M200中有一个国家度量表格或列表，可以创建引用该区域的命名公式——简单地以d代表数据区域。然后，可以非常顺利地命名该数据区域的单个列。如果国家名字在第一列，那么可以创建命名公式：

Country: =INDEX(d,,1)

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

Population: =INDEX(d,,4)

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

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

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

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

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

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

=MATCH(B2,Country,0)

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

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

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

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

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

求和

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

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

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

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

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