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