#Excel函數#2.“威力強大”的Index函數

#Excel函數#2.“威力強大”的Index函數

以我的觀點看，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）！

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。