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

详细介‍绍使用OFFSET函数、COUNTA函数、INDEX函数和MATCH函数相结合而创建动态命名区域的公式。...



‍

微信公众号：excelperfect

什么是动态命名区域？

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

下面，介绍创建动态命名区域的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公式找到相应列的最后一个数据单元格，作为结束单元格。

应用

在某些情形下，动态命名区域非常有用，例如与数据验证结合使用，创建动态的下拉列表；作为图表系列，创建动态图表。有兴趣的朋友，可以参阅完美Excel微信公众号（excelperfect）的相关应用文章。

本文属原创文章，转载请联系我（xhdsxfjy@163.com）或者注明出处。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎关注[完美Excel]微信公众号：

方法1—在微信“添加朋友”或者通讯录中搜索“完美Excel”或者“excelperfect”后点击关注。

方法2—扫一扫下面的二维码

    关注 完美Excel