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

通常，命名区域对 Excel 用户非常有用，您可以在一列中定义一系列值，给该列起一个名称，然后您可以按名称引用该区域，而不是使用单元格引用。但大多数时候，未来您需要添加新数据以扩展所引用区域的数据值。在这种情况下，您必须返回到 公式 > 名称管理器 并重新定义该区域以包含新值。为避免这种情况，您可以创建一个动态命名区域，这意味着每次向列表中添加新行或列时，您无需调整单元格引用。

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

使用函数在 Excel 中创建动态命名区域

使用 VBA 代码在 Excel 中创建动态命名区域

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

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

假设您有以下数据范围，需要将其变为动态命名范围。

1. 首先，我将为此范围定义范围名称。选择范围 A1:A6 并在 名称框 中输入名称 Date，然后按 Enter 键。同样方式为范围 B1:B6 定义名称 Saleprice。同时，我在空白单元格中创建了一个公式 =sum(Saleprice)，请参见截图：

2. 选择范围并点击 插入 > 表格，请参见截图：

3. 在 创建表格 提示框中，勾选 我的表格有标题（如果范围没有标题，则取消勾选），点击 确定 按钮，范围数据已转换为表格。请参见截图：

4. 当您在数据后输入新值时，命名范围将自动调整，并且创建的公式也会随之更改。请参见以下截图：

注意事项：

1. 您新输入的数据必须与上方数据相邻，这意味着新数据和现有数据之间不能有空行或列。

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

使用函数在 Excel 中创建动态命名区域

在 Excel 2003 或更早版本中，第一种方法不可用，因此这里为您提供另一种方法。以下 OFFSET( ) 函数可以为您实现此功能，但有些麻烦。假设我有一个包含已定义范围名称的数据范围，例如，A1: A6 的范围名称是 Date，B1:B6 的范围名称是 Saleprice，同时，我为 Saleprice 创建了一个公式。请参见截图：

您可以按照以下步骤将范围名称更改为动态范围名称：

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

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

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

4. 然后点击 确定，然后重复步骤 2 和步骤 3，将此公式 =OFFSET(Sheet1!$B$1, 0, 0, COUNTA($B:$B), 1) 复制到 Saleprice 范围名称的 引用位置 文本框中。

5. 动态命名范围已创建。当您在数据后输入新值时，命名范围将自动调整，并且创建的公式也会随之更改。请参见截图：

注意：如果您的范围中间有空白单元格，公式的计算结果将是错误的。这是因为未计算非空单元格，因此您的范围会比应有的短，并且范围中的最后一个单元格会被遗漏。

提示：对此公式的解释：

• =OFFSET(参考,行,列,[高度],[宽度])
• 
• =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)

• reference 对应于起始单元格位置，在此示例中为 Sheet1!$A$1；
• row 指的是相对于起始单元格向下移动的行数（如果使用负值，则向上移动），在此示例中，0 表示列表将从第一行开始向下。
• column 对应于相对于起始单元格向右移动的列数（如果使用负值，则向左移动），在上述示例公式中，0 表示向右扩展 0 列。
• [height] 对应于从调整后的位置开始的范围的高度（或行数）。$A:$A，它将计算 A 列中输入的所有项。
• [width] 对应于从调整后的位置开始的范围的宽度（或列数）。在上述公式中，列表将为 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. 当您在行或列后输入新值时，范围也会扩展。请参见截图：

注意事项：

1. 使用此代码，范围名称不会显示在 名称框 中，为了方便查看和使用范围名称，我安装了 Kutools for Excel，通过其 导航窗格，列出创建的动态范围名称。

2. 使用此代码，整个数据范围可以垂直或水平扩展，但请记住，当您输入新值时，数据之间不应有空行或列。

3. 当您使用此代码时，您的数据范围应从单元格 A1 开始。

相关文章：

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