精通Excel數組公式14:使用INDEX函數和OFFSET函數創建動態單元格區域

學習Excel技術，關注微信公眾號：

excelperfect

動態單元格區域是指當添加或刪除源數據時，或者隨著包含單元格區域的公式被向下複製時根據某條件更改，可以自動擴展或收縮的單元格區域，可以用於公式、圖表、數據透視表和其他位置。

 

那麼，如何創建動態單元格區域呢？可以使用INDEX函數或者OFFSET函數。許多人傾向於使用INDEX函數，因為OFFSET函數是一個易失性函數。

 

什麼是易失性函數？

每當Excel重新計算電子表格時，無論其引用的單元格有無變化，易失性函數都會重新計算。許多操作都會觸發重新計算，例如在單元格中輸入數據、插入行等。這樣，易失性函數會增加公式的計算時間。下面列出了一些觸發重新計算的操作：

1.輸入新的數據

2.刪除/插入行/列

3.執行自動篩選

4.雙擊行列分隔線

5.重命名工作表

6.改變工作表的位置

 

下面列出了一些易失性函數：CELL函數，INDIRECT函數，INFO函數，NOW函數，OFFSET函數，RAND函數，TODAY函數。

 

INDEX：查找行或列的公式

創建動態單元格區域的最基本的公式類型是基於條件來查找整行或整列值，可以使用INDEX函數實現。

 

INDEX函數有3個參數：

=INDEX(array,row_num,column_num)

 

通常，給參數row_num指定行號，給參數column_num指定列號，INDEX函數執行雙向查找返回行列號交叉處的值。如果要獲取整列，那麼只需要給INDEX函數指定代表列號的參數column_num的值，忽略參數row_num（為空）或者指定其值為0。通過指定參數row_num為空或0，告訴Excel返回所選列的所有行。

 

同理，想要獲取整行，則需要指定參數row_num的值代表行號，將參數column_num指定為空或0。這告訴Excel需要返回所選行的所有列。

 

圖1：查找並求2月份的數值之和

 

注意，圖1所示的公式並不需要按Ctrl+Shift+Enter組合鍵，雖然INDEX函數返回的是一個單元格區域，其原因是沒有執行直接數組操作。下面兩種情況需要按Ctrl+Shift+Enter組合鍵：

1.如果放置需要Ctrl +Shift + Enter進入公式的直接數組操作，則需要使用Ctrl +Shift + Enter。

2.如果想要傳遞多個值到多個單元格，則必須使用Ctrl +Shift + Enter。

 

用於處理擴大和縮小單元格區域的動態單元格區域公式

在創建動態單元格區域公式之前，必須問清楚下列問題：

1.是垂直單元格區域（一列）嗎？

2.是水平單元格區域（一行）嗎？

3.是雙向單元格區域（行列）嗎？

4.是數字、文本，還是混合數據？

5.是否存在空單元格？

 

對這些問題的答案決定可能使用哪種公式。

 

MATCH：確定數據集中的最後一個相對位置

下圖2展示了4列不同的數據類型：單元格區域A5:A10在最後一項前包含混合數據，其中沒有空單元格；單元格區域A16:A21在最後一項前包含帶有空單元格的混合數據；單元格區域C5:C10在最後一項前包含帶有空單元格的數字數據；單元格區域C16:C21在最後一項前包含帶有空單元格的文本數據。在所有這4種情形下，要使用公式創建在添加或減少數據時擴充或縮減的動態單元格區域，需要確定該列中最後一個相對位置。圖2中展示了6種可能的公式。

圖2：對於不同數據類型查找最後一行

 

在圖2所示的公式[2]至[6]中，展示了一種近似查找值的技術：當要查找的值比單元格區域中的任何值都大且執行近似匹配（即MATCH函數的第3個參數為空）時，將總是獲取列表中最後一個相對位置，即便存在空單元格。

 

INDEX和MATCH函數：獲取單元格區域中的最後一項

下圖3和圖4展示了如何使用MATCH和INDEX函數在單元格區域中查找最後一項。

圖3：當有4條記錄時查找單元格區域中的最後一項

 

圖4：當有6條記錄時查找單元格區域中的最後一項

 

使用INDEX和MATCH函數創建可以擴展和縮小的動態單元格區域

如下圖5所示，在單元格E2中是一個數據有效性下拉列表，其內容來源於單元格區域A2:A5，在單元格F2中的VLOOKUP公式從單元格區域A2:C5中查找並返回相應的數據。

圖5：下拉列表和VLOOKUP公式

 

問題是，當在單元格區域A2:C5的下方添加更多的數據時，數據有效性下拉列表和VLOOKUP公式中的相應單元格區域都不會更新。當前，在「成本」列中的最後一項是單元格C5，如果添加新記錄，在「成本」列中最新的最後一項應該是單元格C6，這意味著在VLOOKUP公式中的查找區域需要從$A$2:$C$5改變為$A$2:$C$6。注意到，這兩個區域都開始於相同的單元格$A$2。我們現在的任務，就是找到一種方法，當添加或刪除記錄時，其最後一個單元格引用能夠相應更新。此時，可以使用INDEX函數。

 

靜態的單元格區域如下：

$A$2:$C$5

 

創建的動態單元格區域如下：

$A$2:INDEX($C$2:$C$8,MATCH(9.99E+307,$C$2:$C$8))

 

注意，由於INDEX函數位於一個起始單元格引用和冒號之後，因此不再獲取該區域中的最後一項，而是獲取該區域中最後一項的單元格地址（單元格引用）。

 

此時，你在圖5的數據區域中添加或刪除記錄，創建的動態單元格區域會自動更新。

 

下面是創建動態單元格區域公式的關鍵點：

1.足夠的行以容納所有潛在數據。

（1）如果含有數字的數據集在列C中並決不會超過50條記錄，可使用：

=$A$2:INDEX($C$2:$C$51,MATCH(9.99E+307,$C$2:$C$51))

（2）如果含有數字的數據集在列C中並決不會超過500條記錄，可使用：

=$A$2:INDEX($C$2:$C$501,MATCH(9.99E+307,$C$2:$C$501))

（3）如果含有數字的數據集在列C中並且不確定有多少條記錄，可使用：

=$A$2:INDEX($C:$C,MATCH(9.99E+307,$C:$C))

2.不要在公式使用的單元格區域的下方輸入無關數據，因為會導致公式創建不正確的區域。例如，如果公式使用潛在單元格區域$C$2:$C$50，並且最後一個數據位於單元格C25，那麼不要再在單元格C49中輸入數據，因為公式會將其考慮為該列的最後一個單元格。

註：本文為電子書《精通Excel數組公式（學習筆記版）》中的一部分內容節選。你可以到知識星球App的完美Excel社群下載這本電子書的完整中文版。

歡迎在下面留言，完善本文內容，讓更多的人學到更完美的知識。

歡迎到知識星球：完美Excel社群，進行技術交流和提問，獲取更多電子資料。