KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中创建动态命名区域？

作者小阳修改日期

通常，命名区域对 Excel 用户非常有用——您可以为某一列中的一系列值定义一个名称，之后即可通过该名称（而非单元格引用）来引用该区域。但大多数情况下，您后续会添加新数据以扩展所引用区域的范围，此时必须返回公式> 名称管理器，重新定义区域以包含新增的数据。为避免这一繁琐操作，您可以创建动态命名区域，这样每次向列表中添加新行或新列时，都无需手动调整单元格引用。

通过创建表格在 Excel 中创建动态命名区域

通过函数在 Excel 中创建动态命名区域

通过 VBA 代码在 Excel 中创建动态命名区域

通过创建表格在 Excel 中创建动态命名区域

如果您使用的是 Excel 2007 或更高版本，创建动态命名区域最简单的方法就是创建一个命名的 Excel 表格。

假设您有一组如下所示的数据，需要将其转换为动态命名区域。

doc-dynamic-range1

1. 首先，我将为此区域定义单元格名称：选中区域 A1:A6，在名称框中输入名称名称框，然后按 Enter 键。同样方法，为区域 B1:B6 定义名称 Saleprice。接着，在任意空白单元格中输入公式 =sum(Saleprice)，效果如截图所示：

doc-dynamic-range2

2. 选择该区域，然后单击插入> 表格，见截图：

doc-dynamic-range3

3. 在创建表格对话框中，勾选表包含标题（若区域不含标题，请取消勾选），然后单击确定按钮，即可将区域数据转换为表格。见截图：

doc-dynamic-range4-2doc-dynamic-range5

4. 当您在数据后输入新值时，命名区域将自动调整，所创建的公式也会随之更新。详见以下截图：

doc-dynamic-range6-2doc-dynamic-range7

注意：

1. 您新输入的数据必须紧邻上述数据，中间不能留有任何空白行或列。

2. 在表格中，您可轻松在现有值之间插入新数据。

通过函数在 Excel 中创建动态命名区域

在 Excel 2003 或更早版本中，第一种方法不可用，因此这里提供另一种替代方案。OFFSET( )函数可帮您实现此功能，但操作稍显繁琐。假设您有一组已定义名称的单元格区域，例如将 A1:A6 命名为 Date，B1:B6 命名为 Saleprice，并已为 Saleprice 创建了相应公式。效果如下图所示：

doc-dynamic-range2

您可以通过以下步骤将单元格名称更改为动态单元格名称：

1. 单击公式> 名称管理器，见截图：

doc-dynamic-range8

2. 在名称管理器对话框中，选择要使用的项目，然后点击编辑按钮。

doc-dynamic-range9

3. 在弹出的编辑名称对话框中，将公式 =OFFSET(Sheet 1!$A$1, 0, 0, COUNTA($A:$A), 1) 输入到引用位置文本框中，见截图：

doc-dynamic-range10

4. 然后单击确定，并重复步骤 2 和步骤 3，将公式 =OFFSET(Sheet 1!$B$1, 0, 0, COUNTA($B:$B)，1) 复制到引用位置文本框中，用于 Saleprice 单元格名称。

5. 动态命名区域现已创建完成！当您在数据后新增内容时，命名区域将自动扩展，相关公式也会即时更新。效果如图所示：

doc-dynamic-range6-2doc-dynamic-range7

注意：如果所选区域中存在空单元格，公式结果将出错。这是因为空单元格未被统计，导致区域范围比实际更短，从而遗漏区域末尾的单元格。

提示：此公式的说明：

  • =OFFSET（引用，行偏移量，列偏移量，【高度】，【宽度】）
  • -1
  • =OFFSET(Sheet 1!$A$1, 0, 0, COUNTA($A:$A), 1)
  • 引用对应起始单元格位置，本例中为 Sheet 1!$A$1；
  • 行表示相对于起始单元格向下移动的行数（若使用负值则向上移动），本例中 0 表示列表将从第一行开始向下扩展
  • 列表示相对于起始单元格向右移动的列数（使用负值则向左移动），上述公式中的 0 表示不向右扩展列。
  • [高度】对应从调整后位置开始的区域高度（即行数）。$A：$A 将统计 A 列中所有已输入的内容。
  • [宽度】表示从调整后位置开始的区域宽度（即列数）。上述公式中，列表宽度为 1 列。

您可以根据实际需求灵活调整这些参数。

通过 VBA 代码在 Excel 中创建动态命名区域

如果您有多列数据，可以为其余各列逐一输入单独的公式，但这将是一个冗长且重复的过程。为简化操作，您可使用一段代码自动创建动态命名区域。

1. 激活您的工作表。

2. 按住 ALT + F11 键，即可打开 Microsoft Visual Basic for Applications 窗口。

3. 单击插入> 模块，并将以下代码粘贴到模块窗口中。

VBA 代码：创建动态命名区域

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. 然后按下 F5 键运行代码，系统将自动生成多个以首行值命名的动态命名区域，并创建一个名为 MyData 的动态区域，覆盖全部数据。

5. 当您在行或列后输入新值时，区域会自动扩展。见截图：

doc-dynamic-range12
-1
doc-dynamic-range13

注意：

1. 使用此代码时，单元格名称不会显示在名称框中。为便于查看和使用单元格名称，我已安装 Kutools for Excel，其导航窗格会列出所有已创建的动态单元格名称。

2. 使用此代码时，整个数据区域可垂直或水平扩展，但请注意：输入新值时，数据之间不得留有空白行或列。

3. 使用此代码时，请确保您的数据区域从单元格 A1 开始。

相关文章：

如何在 Excel 中输入新数据后让图表自动更新？

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱