Excel实战技巧2：创建动态命名区域的3个公式

‍

 

什么是动态命名区域？

指定一个名称代表某单元格区域，当在该区域中添加或者删除行或列时，这个名称代表的区域会自动调整，我们称之为动态命名区域。

 

下面，介绍创建动态命名区域的3个公式。

 

公式1：使用OFFSET函数和COUNTA函数结合的公式

为工作表Sheet1的列A中的数据创建一个名为“ProCat”的动态命名区域。

单击“公式”选项卡中的“定义名称”，在打开的“新建名称”对话框中：

• 在“名称”框中输入：ProCat

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

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

创建的动态命名区域为：从单元格A2开始一直到列A中最后一个数据单元格为止的单元格区域。

注意：使用这种方法创建的动态命名区域，前提是命名的区域内没有空单元格，否则得不到正确的范围。

 

公式2：使用INDEX函数和COUNTA函数结合的公式

为工作表Sheet2的列A至列C中的数据创建一个名为“Student”的动态命名区域。

单击“公式”选项卡中的“定义名称”，在打开的“新建名称”对话框中：

• 在“名称”框中输入：Student

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

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

其中，INDEX(Sheet2!$A:$C,COUNTA(Sheet2!$A:$A),3)返回列C中最后一个数据单元格，本例中是单元格C7。因此，创建的动态命名区域在本例中的范围为：A2:C7。

 

公式3：使用INDEX函数、MATCH函数和COUNTA函数结合的公式

下面介绍的公式将根据指定的列创建动态命名区域，各列的行数不一定相同。如下图所示，当工作表Sheet4中单元格A1内容为“水果”时，动态命名区域为工作表Sheet3中的水果列；当工作表Sheet4中单元格A1内容为“家用电器”时，动态命名区域为工作表Sheet3中的家用电器列，依此类推。

 

首先，使用上文介绍的公式在工作表Sheet3中创建一个动态命名区域：Datas。

公式为：

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

如果工作表如下图所示，则创建的动态区域为从列A开始的3列以及从第1行开始的50行的区域。

 

然后，选中工作表Sheet4的单元格B2，打开“新建名称”对话框，创建动态名称：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中的数据所在的单元格，作为起始单元格。

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

找到工作表Sheet4的单元格A1中的数据在工作表Sheet3中的列并统计该列非空单元格数量，作为外层INDEX函数的参数。整个第二部分的INDEX公式找到相应列的最后一个数据单元格，作为结束单元格。

 

应用