如何在 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)

• 參考位置 對應於起始儲存格位置，在此範例中為 Sheet1!$A$1；
• 行數 指的是您將向下移動的行數，相對於起始儲存格（或者向上移動，如果您使用負值），在此範例中，0 表示列表將從第一行開始。
• 列數 對應於您將向右移動的列數，相對於起始儲存格（或者向左移動，使用負值），在上述範例公式中，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. 當您在行或列後面輸入新值時，範圍也將擴展。請參見截圖：

注意：

1. 使用此代碼，範圍名稱不會顯示在 名稱框 中，為了方便查看和使用範圍名稱，我安裝了 Kutools for Excel，使用其 導航窗格，列出創建的動態範圍名稱。

2. 使用此代碼，數據的整個範圍可以垂直或水平擴展，但要記住，當您輸入新值時，數據之間不應有空白行或列。

3. 當您使用此代碼時，您的數據範圍應從儲存格 A1 開始。

相關文章：

如何在 Excel 中輸入新數據後自動更新圖表？