excelperfect
動態(tài)單元格區(qū)域是指當添加或刪除源數據時，或者隨著包含單元格區(qū)域的公式被向下復制時根據某條件更改，可以自動擴展或收縮的單元格區(qū)域，可以用于公式、圖表、數據透視表和其他位置。
那么，如何創(chuàng)建動態(tài)單元格區(qū)域呢？可以使用INDEX函數或者OFFSET函數。許多人傾向于使用INDEX函數，因為OFFSET函數是一個易失性函數。
什么是易失性函數？
每當Excel重新計算電子表格時，無論其引用的單元格有無變化，易失性函數都會重新計算。許多操作都會觸發(fā)重新計算，例如在單元格中輸入數據、插入行等。這樣，易失性函數會增加公式的計算時間。下面列出了一些觸發(fā)重新計算的操作：
1.輸入新的數據
2.刪除/插入行/列
3.執(zhí)行自動篩選
4.雙擊行列分隔線
5.重命名工作表
6.改變工作表的位置
下面列出了一些易失性函數：CELL函數，INDIRECT函數，INFO函數，NOW函數，OFFSET函數，RAND函數，TODAY函數。
INDEX：查找行或列的公式
創(chuàng)建動態(tài)單元格區(qū)域的最基本的公式類型是基于條件來查找整行或整列值，可以使用INDEX函數實現(xiàn)。
INDEX函數有3個參數：
=INDEX(array,row_num,column_num)
通常，給參數row_num指定行號，給參數column_num指定列號，INDEX函數執(zhí)行雙向查找返回行列號交叉處的值。如果要獲取整列，那么只需要給INDEX函數指定代表列號的參數column_num的值，忽略參數row_num（為空）或者指定其值為0。通過指定參數row_num為空或0，告訴Excel返回所選列的所有行。
同理，想要獲取整行，則需要指定參數row_num的值代表行號，將參數column_num指定為空或0。這告訴Excel需要返回所選行的所有列。
圖1：查找并求2月份的數值之和
注意，圖1所示的公式并不需要按Ctrl+Shift+Enter組合鍵，雖然INDEX函數返回的是一個單元格區(qū)域，其原因是沒有執(zhí)行直接數組操作。下面兩種情況需要按Ctrl+Shift+Enter組合鍵：
1.如果放置需要Ctrl +Shift + Enter進入公式的直接數組操作，則需要使用Ctrl +Shift + Enter。
2.如果想要傳遞多個值到多個單元格，則必須使用Ctrl +Shift + Enter。
用于處理擴大和縮小單元格區(qū)域的動態(tài)單元格區(qū)域公式
在創(chuàng)建動態(tài)單元格區(qū)域公式之前，必須問清楚下列問題：
1.是垂直單元格區(qū)域（一列）嗎？
2.是水平單元格區(qū)域（一行）嗎？
3.是雙向單元格區(qū)域（行列）嗎？
4.是數字、文本，還是混合數據？
5.是否存在空單元格？
對這些問題的答案決定可能使用哪種公式。
MATCH：確定數據集中的最后一個相對位置
下圖2展示了4列不同的數據類型：單元格區(qū)域A5:A10在最后一項前包含混合數據，其中沒有空單元格；單元格區(qū)域A16:A21在最后一項前包含帶有空單元格的混合數據；單元格區(qū)域C5:C10在最后一項前包含帶有空單元格的數字數據；單元格區(qū)域C16:C21在最后一項前包含帶有空單元格的文本數據。在所有這4種情形下，要使用公式創(chuàng)建在添加或減少數據時擴充或縮減的動態(tài)單元格區(qū)域，需要確定該列中最后一個相對位置。圖2中展示了6種可能的公式。
圖2：對于不同數據類型查找最后一行
在圖2所示的公式[2]至[6]中，展示了一種近似查找值的技術：當要查找的值比單元格區(qū)域中的任何值都大且執(zhí)行近似匹配（即MATCH函數的第3個參數為空）時，將總是獲取列表中最后一個相對位置，即便存在空單元格。
INDEX和MATCH函數：獲取單元格區(qū)域中的最后一項
下圖3和圖4展示了如何使用MATCH和INDEX函數在單元格區(qū)域中查找最后一項。
圖3：當有4條記錄時查找單元格區(qū)域中的最后一項
圖4：當有6條記錄時查找單元格區(qū)域中的最后一項
使用INDEX和MATCH函數創(chuàng)建可以擴展和縮小的動態(tài)單元格區(qū)域
如下圖5所示，在單元格E2中是一個數據有效性下拉列表，其內容來源于單元格區(qū)域A2:A5，在單元格F2中的VLOOKUP公式從單元格區(qū)域A2:C5中查找并返回相應的數據。
圖5：下拉列表和VLOOKUP公式
問題是，當在單元格區(qū)域A2:C5的下方添加更多的數據時，數據有效性下拉列表和VLOOKUP公式中的相應單元格區(qū)域都不會更新。當前，在“成本”列中的最后一項是單元格C5，如果添加新記錄，在“成本”列中最新的最后一項應該是單元格C6，這意味著在VLOOKUP公式中的查找區(qū)域需要從$A$2:$C$5改變?yōu)?A$2:$C$6。注意到，這兩個區(qū)域都開始于相同的單元格$A$2。我們現(xiàn)在的任務，就是找到一種方法，當添加或刪除記錄時，其最后一個單元格引用能夠相應更新。此時，可以使用INDEX函數。
靜態(tài)的單元格區(qū)域如下：
$A$2:$C$5
創(chuàng)建的動態(tài)單元格區(qū)域如下：
$A$2:INDEX($C$2:$C$8,MATCH(9.99E+307,$C$2:$C$8))
注意，由于INDEX函數位于一個起始單元格引用和冒號之后，因此不再獲取該區(qū)域中的最后一項，而是獲取該區(qū)域中最后一項的單元格地址（單元格引用）。
此時，你在圖5的數據區(qū)域中添加或刪除記錄，創(chuàng)建的動態(tài)單元格區(qū)域會自動更新。
下面是創(chuàng)建動態(tài)單元格區(qū)域公式的關鍵點：
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.不要在公式使用的單元格區(qū)域的下方輸入無關數據，因為會導致公式創(chuàng)建不正確的區(qū)域。例如，如果公式使用潛在單元格區(qū)域$C$2:$C$50，并且最后一個數據位于單元格C25，那么不要再在單元格C49中輸入數據，因為公式會將其考慮為該列的最后一個單元格。
聯(lián)系客服
