如何在 Excel 中建立動態命名範圍？

作者曉陽修改日期2024-07-30

一般而言，命名範圍對 Excel 使用者極具實用價值！您可以為某欄中的一系列數值指定專屬名稱，日後便能直接透過名稱（而非儲存格參照）來引用該範圍。然而，多數情況下，您未來勢必會新增資料以擴充該範圍。此時，您就得返回公式＞ 名稱管理器，手動重新定義範圍以納入新資料。為免反覆調整，建議您建立「動態命名範圍」——只要在清單中新增列或欄，範圍就會自動更新，無需再手動修改儲存格參照！

透過建立表格在 Excel 中建立動態命名範圍

透過函數在 Excel 中建立動態命名範圍

透過 VBA 程式碼在 Excel 中建立動態命名範圍

透過建立表格在 Excel 中建立動態命名範圍

太神奇了！在 Excel 中使用高效 Tabs，就像 Chrome、Edge、Firefox 和 Safari 一樣！
每天為您節省 50% 的時間，並減少數千次滑鼠點擊！

若您使用 Excel 2007 或更新版本，建立動態命名範圍最簡單的方式就是建立命名的 Excel 表格。

假設您有一組如下資料，需要轉換為動態命名範圍。

doc-dynamic-range1

1. 首先，我將為此範圍定義儲存格名稱：選取範圍 A1:A6，在名稱方塊中輸入名稱 Date，再按下 Enter 鍵。同樣地，為範圍 B1:B6 定義名稱 Saleprice。接著，在空白儲存格中輸入公式 =SUM(Saleprice)，請參閱截圖：

doc-dynamic-range2

2. 選取範圍後，點擊插入＞表格，請參閱截圖：

doc-dynamic-range3

3. 在建立表格提示方塊中，若範圍包含標題，請勾選我的表格包含標題（若不含標題，請取消勾選），然後點擊確定按鈕，即可將範圍資料轉換為表格。請參閱截圖：

4. 當您在資料後方輸入新數值時，命名範圍會自動調整，且已建立的公式也會隨之更新。請參閱下列截圖：

注意事項：

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. 動態命名範圍已建立完成！當您在資料後方輸入新數值時，命名範圍將自動調整，且既有公式也會同步更新。請參閱截圖：

注意：若您的範圍中間包含空白儲存格，公式結果將不正確。這是因為非空白儲存格未被計入，導致範圍長度短於應有值，進而使範圍末尾的儲存格遭到排除。

提示：此公式說明：

=OFFSET（參照，列數，欄數，【高度】，【寬度】）
=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. 當您在列或欄後方輸入新數值時，範圍也會自動擴展。請參閱截圖：

注意事項：

1. 使用此程式碼時，儲存格名稱不會顯示在名稱方塊中。為方便檢視與使用儲存格名稱，我已安裝 Kutools for Excel，透過其導航功能，所有建立的動態儲存格名稱皆會清晰列出，輕鬆掌握！

2. 使用此程式碼時，資料範圍可依需求垂直或水平擴展，但請注意：輸入新數值時，資料之間不得留有空白列或欄位。

3. 使用此程式碼時，請確保您的數據區域從儲存格 A1 開始。

相關文章：

如何在 Excel 中輸入新資料後，讓圖表自動更新？

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多