#Excel函数#2.“威力强大”的Index函数-英雄云拓展知识分享

以我的观点看，INDEX函数是Excel中最重要的一个工作表函数。

现在看来，斟酌该函数单调的名字是使人惊讶的。那末，甚么使INDEX函数如此强大呢？它是非易失性的、明快的、灵活的并且用处广泛。INDEX可以返回一个值或一组值，可以返回对某个单元格的援用或单元格区域的援用。INDEX可以很好地结合3个援用操作符（即冒号、空格和逗号）使用。

INDEX的使用思想是为它提供一块区域（或一个数组），然后指定要返回的元素。因此，这种情况下，

=INDEX(A1:A5,2)

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

并且，

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

将返回值99。

到现在为止还没有甚么了不起的。但关键是要理解，因此再说明一下，上面的第一个例子返回一个援用，而第2个例子返回一个值。

同时，注意上述两个例子都演示了INDEX在传递一个矢量时的行动。一个矢量是指一个一维数组或单元格区域。当传递一个矢量时，INDEX不会关心该矢量是垂直的或水平的。INDEX的第2个参数指明行号。但这在传递矢量时是不正确的。第2个参数输入成为矢量元数号，而不是行号，正由于如此，水平矢量使用这类符号工作得很好。在上面的第2个例子中，5不是行号，它是元素号！

但是可以强迫INDEX依照其正常的2维方式工作：

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

也返回值99。

并且，这类2维方式使得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组合键。

因此，这种情况下，使用下面的技术返回2维数组的整行或整列：

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

结果是65，即输入数组的全部第3行的和。

查找

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

假定在单元格区域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))

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

Country: =$A$2:INDEX($A:$A,COUNTA($A:$A))

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

d: =$A$2:INDEX($1:$65535,COUNTA($A:$A),COUNTA($1:$1))

这个动态方式使用d来定义内涵，依然可以履行上面展现的精彩的行和列援用，例如：

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

说明，当在3个援用操作符的任意一个的一侧或两侧使用函数时，在工作簿打开时总会重新计算结果公式。因此虽然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）！