?

什么是動態(tài)命名區(qū)域？

指定一個名稱代表某單元格區(qū)域，當在該區(qū)域中添加或者刪除行或列時，這個名稱代表的區(qū)域會自動調整，我們稱之為動態(tài)命名區(qū)域。

下面，介紹創(chuàng)建動態(tài)命名區(qū)域的3個公式。

公式1：使用OFFSET函數(shù)和COUNTA函數(shù)結合的公式

為工作表Sheet1的列A中的數(shù)據(jù)創(chuàng)建一個名為“ProCat”的動態(tài)命名區(qū)域。

單擊“公式”選項卡中的“定義名稱”，在打開的“新建名稱”對話框中：

• 在“名稱”框中輸入：ProCat

• 在“引用位置”框中輸入：

=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

創(chuàng)建的動態(tài)命名區(qū)域為：從單元格A2開始一直到列A中最后一個數(shù)據(jù)單元格為止的單元格區(qū)域。

注意：使用這種方法創(chuàng)建的動態(tài)命名區(qū)域，前提是命名的區(qū)域內沒有空單元格，否則得不到正確的范圍。

公式2：使用INDEX函數(shù)和COUNTA函數(shù)結合的公式

為工作表Sheet2的列A至列C中的數(shù)據(jù)創(chuàng)建一個名為“Student”的動態(tài)命名區(qū)域。

單擊“公式”選項卡中的“定義名稱”，在打開的“新建名稱”對話框中：

• 在“名稱”框中輸入：Student

• 在“引用位置”框中輸入：

=Sheet2!$A$2:INDEX(Sheet2!$A:$C,COUNTA(Sheet2!$A:$A),3)

其中，INDEX(Sheet2!$A:$C,COUNTA(Sheet2!$A:$A),3)返回列C中最后一個數(shù)據(jù)單元格，本例中是單元格C7。因此，創(chuàng)建的動態(tài)命名區(qū)域在本例中的范圍為：A2:C7。

公式3：使用INDEX函數(shù)、MATCH函數(shù)和COUNTA函數(shù)結合的公式

下面介紹的公式將根據(jù)指定的列創(chuàng)建動態(tài)命名區(qū)域，各列的行數(shù)不一定相同。如下圖所示，當工作表Sheet4中單元格A1內容為“水果”時，動態(tài)命名區(qū)域為工作表Sheet3中的水果列；當工作表Sheet4中單元格A1內容為“家用電器”時，動態(tài)命名區(qū)域為工作表Sheet3中的家用電器列，依此類推。

首先，使用上文介紹的公式在工作表Sheet3中創(chuàng)建一個動態(tài)命名區(qū)域：Datas。

公式為：

=Sheet3!$A$2:INDEX(Sheet3!$1:$50,50,COUNTA(Sheet3!$1:$1))

如果工作表如下圖所示，則創(chuàng)建的動態(tài)區(qū)域為從列A開始的3列以及從第1行開始的50行的區(qū)域。

然后，選中工作表Sheet4的單元格B2，打開“新建名稱”對話框，創(chuàng)建動態(tài)名稱：DynamicList。

公式為：

=INDEX(Datas,1,MATCH(Sheet4!A1,Sheet3!$1:$1,0)):INDEX(Datas,COUNTA(INDEX(Datas,,MATCH(Sheet4!A1,Sheet3!$1:$1,0))),MATCH(Sheet4!A1,Sheet3!$1:$1,0))

注意：由于要想對引用當前單元格左側的單元格，因此在定義名稱時，一定要選擇工作表Sheet4的單元格B1。

上面的公式比較復雜，以冒號為界，分為兩個部分。

第一部分：INDEX(Datas,1,MATCH(Sheet4!A1,Sheet3!$1:$1,0))

在工作表Sheet3中找到工作表Sheet4的單元格A1中的數(shù)據(jù)所在的單元格，作為起始單元格。

在第二部分中，公式：COUNTA(INDEX(Datas,,MATCH(Sheet4!A1,Sheet3!$1:$1,0)))

找到工作表Sheet4的單元格A1中的數(shù)據(jù)在工作表Sheet3中的列并統(tǒng)計該列非空單元格數(shù)量，作為外層INDEX函數(shù)的參數(shù)。整個第二部分的INDEX公式找到相應列的最后一個數(shù)據(jù)單元格，作為結束單元格。

應用