INDEX函數(shù)的可處理的一些實(shí)例使用方法

INDEX函數(shù)是Excel函數(shù)中最重要的一個(gè)工作表函數(shù)。

現(xiàn)在看來，考慮該函數(shù)單調(diào)的名字是令人驚訝的。那么，什么使INDEX函數(shù)如此強(qiáng)大呢?它是非易失性的、明快的、靈活的并且用途廣泛。INDEX可以返回一個(gè)值或者一組值，可以返回對某個(gè)單元格的引用或者單元格區(qū)域的引用。INDEX可以很好地結(jié)合三個(gè)引用操作符(即冒號、空格和逗號)使用。

INDEX函數(shù)的使用思想是為它提供一塊區(qū)域(或者一個(gè)數(shù)組)，然后指定要返回的元素。因此，

=INDEX(A1:A5,2)

將返回對單元格A2的引用。并且，

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

將返回值99。

到現(xiàn)在為止還沒有什么了不起的。但關(guān)鍵是要理解，因此再說明一下，上面的第一個(gè)例子返回一個(gè)引用，而第二個(gè)例子返回一個(gè)值。

同時(shí)，注意上述兩個(gè)例子都演示了INDEX在傳遞一個(gè)矢量時(shí)的行為。一個(gè)矢量是指一個(gè)一維數(shù)組或單元格區(qū)域。當(dāng)傳遞一個(gè)矢量時(shí)，INDEX不會(huì)關(guān)心該矢量是垂直的或水平的。INDEX函數(shù)的第二個(gè)參數(shù)指明行號。但這在傳遞矢量時(shí)是不正確的。第二個(gè)參數(shù)輸入成為矢量元數(shù)號，而不是行號，正因?yàn)槿绱?，水平矢量使用這種符號工作得很好。在上面的第二個(gè)例子中，5不是行號，它是元素號!

但是可以強(qiáng)制INDEX按照其正常的二維方式工作：

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

也返回值99。

并且，這種二維方式使得INDEX開始真正展示其強(qiáng)大。顯然，

=INDEX(A1:C5,1,3)

將返回對C1的引用。但是令大多數(shù)用戶驚訝的是，下面兩個(gè)公式的結(jié)果相同：

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

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

返回整行或整列

事實(shí)上，除非用戶理解這里發(fā)生了什么，否則他們可能會(huì)認(rèn)為該函數(shù)存在一個(gè)Bug，因?yàn)镮NDEX函數(shù)不像OFFSET函數(shù)可以跨過提供的區(qū)域，它必須從傳遞的單元格區(qū)域或數(shù)組里返回一個(gè)元素——0行(或0列)在區(qū)域外。因此，在這里的設(shè)置中0或空值意味著什么呢?

如果row_num參數(shù)為0或者缺失，那么這指示INDEX返回由column_num參數(shù)指定的整列!

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

將返回對單元格區(qū)域B1:B5的引用。

并且相反的情況也成立，即將column_num參數(shù)設(shè)置為0或缺失將返回整行。此時(shí)，缺失的column_num參數(shù)必須包括逗號，例如：

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

將返回對單元格區(qū)域A2:C2的引用。

但是，記住當(dāng)提供特定的單元格給多單元格區(qū)域或者該單元格設(shè)置成等于某數(shù)組時(shí)，只有第一個(gè)元素顯示在輸出單元格中——因此上面兩個(gè)INDEX公式返回的大多數(shù)行或列都將隱藏。然而，可以以數(shù)組輸入該公式到一個(gè)單元格區(qū)域來查看完整的輸出。首先選擇輸出單元格區(qū)域，在公式欄中輸入公式，然后按Ctrl+Shift+Enter組合鍵。

因此，使用下面的技術(shù)返回二維數(shù)組的整行或整列：

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

結(jié)果是65，即輸入數(shù)組的整個(gè)第三行的和。

查找

從更大的單元格區(qū)域或數(shù)組中返回整列或整行的功能是相當(dāng)有用的。

假設(shè)在單元格區(qū)域A1:M200中有一個(gè)國家度量表格或列表，可以創(chuàng)建引用該區(qū)域的命名公式——簡單地以d代表數(shù)據(jù)區(qū)域。然后，可以非常順利地命名該數(shù)據(jù)區(qū)域的單個(gè)列。如果國家名字在第一列，那么可以創(chuàng)建命名公式：

Country: =INDEX(d,,1)

并且，如果人口在第4列，那么可以創(chuàng)建命名公式：

Population: =INDEX(d,,4)

現(xiàn)在，假設(shè)想查找Scotland的人口，我們只需要使用下面的公式：

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

如果僅執(zhí)行一次查找的話，這種查找方法在速度方面和VLOOKUP函數(shù)執(zhí)行的速度看齊。但是比VLOOKUP具有優(yōu)勢。它不會(huì)像VLOOKUP一樣限制查找右側(cè)的列。如果查找國家列表并且以數(shù)組方式在整個(gè)輸出列中輸入一個(gè)公式，那么它的速度明顯高于VLOOKUP。

如果需要返回國家度量列表的多個(gè)列，那么速度的優(yōu)勢是相當(dāng)大的。假設(shè)GDP在數(shù)據(jù)的第2列，資本(Capital)在第11列。

如果在一個(gè)新工作表中，想為每個(gè)國家輸出數(shù)據(jù)中的3列，人口(Population)、GDP和資本(Capital)，那么最有效的方式是在一列中創(chuàng)建共同的索引，然后以數(shù)組形式輸入INDEX公式到輸出的3列中。

在本例中，列B是國家列表，列A將放置公共的索引。在A2中，輸入：

=MATCH(B2,Country,0)

然后，在列B中向下復(fù)制該公式至國家列表的最后一列。

接著，在列C、D和E中以數(shù)組形式輸入公式到整個(gè)單元格區(qū)域，一直向下到國家列表的最后一列：

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

更清楚地說，這意味著如果在列B中有99個(gè)國家，那么將選擇單元格區(qū)域C2:E100，然后在公式欄中輸入上面的公式，接著按Ctrl+Shift+Enter組合鍵完成輸入。

最終的結(jié)果是一個(gè)公式只執(zhí)行一次，并且在模型中非常快速地留下很輕便的、非易失性的“腳印”。

求和

結(jié)合SUMPRODUCT函數(shù)，INDEX的這種用途是一種極好的方式。例如：

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

返回以字母U開頭的所有國家的總?cè)丝凇?/p>

當(dāng)命名公式后，下面的公式也很好：

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

動(dòng)態(tài)區(qū)域

可以更改命名公式d以便結(jié)果在動(dòng)態(tài)區(qū)域而不是在固定區(qū)域。此時(shí)，INDEX函數(shù)是至高無上的。

動(dòng)態(tài)區(qū)域通常與OFFSET函數(shù)或INDIRECT函數(shù)一起構(gòu)造。

不巧的是，這兩個(gè)函數(shù)都是易失性函數(shù)，這意味著每次工作表中有改變時(shí)，包含這些函數(shù)的公式都將重新計(jì)算。例如，在任意空單元格中添加值將導(dǎo)致所有易失性公式(以及任何依賴它們的其它公式)重新計(jì)算。取決于模型設(shè)計(jì)和大小，可能對模型的擴(kuò)展性和響應(yīng)能力產(chǎn)生較大的影響。

INDEX函數(shù)可以用于創(chuàng)建動(dòng)態(tài)區(qū)域，不僅僅是非易失性，而且比OFFSET或INDIRECT更快。事實(shí)上，性能方面的改進(jìn)是如此大，以致于INDEX是專業(yè)模型中所有動(dòng)態(tài)區(qū)域的基礎(chǔ)。

創(chuàng)建動(dòng)態(tài)區(qū)域的方式與使用OFFSET或INDIRECT的方式不同。對于那些函數(shù)，在函數(shù)里創(chuàng)建動(dòng)態(tài)區(qū)域。而使用INDEX，在區(qū)域操作符(冒號)的一側(cè)(有時(shí)是兩側(cè))產(chǎn)生動(dòng)態(tài)區(qū)域。

例如，考慮下面普通的區(qū)域引用：

=A2:A100

引用是對國家列表中國家名稱的硬引用。假設(shè)在列表中沒有空，并且在A1中有列標(biāo)題，可能是“Country”，下面的公式返回列表中最后一個(gè)國家的名字：

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

這里，INDEX函數(shù)真正返回帶有國家名的列A中最后一個(gè)單元格的引用，等于符號強(qiáng)制返回單元格的值。在我們舉的例子中，INDEX公式真正地返回引用A100。

因此，下面的兩個(gè)公式指向完全相同的區(qū)域：

=A2:A100

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

但是有實(shí)質(zhì)上的不同。第一個(gè)公式是硬編碼、靜態(tài)的引用;第二個(gè)公式是非易失的、動(dòng)態(tài)的區(qū)域，隨著列表中國家數(shù)量的變化而擴(kuò)展或收縮。注意，代替“指向”使用INDEX建立的動(dòng)態(tài)區(qū)域的命名公式(Country)的值之前，需要使用絕對引用。如下所示：

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

可以使用相同的技術(shù)創(chuàng)建二維動(dòng)態(tài)區(qū)域，因此列數(shù)也是動(dòng)態(tài)的：

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

這個(gè)動(dòng)態(tài)方式使用d來定義，仍然可以執(zhí)行上面展示的精彩的行和列引用，例如：

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

說明，當(dāng)在三個(gè)引用操作符的任意一個(gè)的一側(cè)或兩側(cè)使用函數(shù)時(shí)，在工作簿打開時(shí)總會(huì)重新計(jì)算結(jié)果公式。因此雖然INDEX是非易失性的，但是當(dāng)用于動(dòng)態(tài)區(qū)域時(shí)它變成半易失性的-但這比易失性更好，因此迄今為止該函數(shù)是最好的動(dòng)態(tài)區(qū)域基礎(chǔ)。

作為對這部分關(guān)于動(dòng)態(tài)區(qū)域的備注，你可能會(huì)問為什么不使用Excel 2007以后的結(jié)構(gòu)化的表引用?雖然STR是一種強(qiáng)大的選擇，但比較笨重。如果需要速度，沒有什么比INDEX更好。

不連續(xù)的區(qū)域

當(dāng)處理單元格區(qū)域時(shí)，INDEX提供了第4個(gè)參數(shù)來從提供的非連續(xù)的輸入?yún)^(qū)域中選擇區(qū)域塊。這些區(qū)域塊通過整數(shù)按在輸入單元格區(qū)域中出現(xiàn)的順序來引用。例如：

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

返回對data2的引用。并且

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

返回data3的第1列。使用一點(diǎn)小技巧，這對于圖表(和許多其它操作)來說是相當(dāng)有用的。注意，指定區(qū)域的數(shù)量沒有硬性限制，并且這些非連續(xù)的區(qū)域不需要有相同的大小!

更多

所有這些都只是觸及到這個(gè)關(guān)鍵的工作表函數(shù)的表面。

在我的詞庫中，“imposing”這個(gè)詞的一些同義詞是令人印象深刻的(impressive)、特別的(august)、指揮的(commanding)、有效的(effective)、令人激動(dòng)的(exciting)、華麗的(magnificent)以及令人興奮的(mind-blowing)。INDEX工作表函數(shù)真是“威力無比”(imposing)!