如何在 Excel 中自動更新下拉式清單？

作者Sun修改日期2025-07-21

doc-auto-update-dropdown-list-1

下拉式清單在 Excel 中廣泛用於標準化資料輸入並提升效率，特別適合日常報表、庫存選項與資料分類等應用。然而，許多使用者常遇到一個限制：當您在原始來源區域下方新增項目時，下拉式清單並不會自動納入這些新項目。這是因為 Excel 預設僅鎖定最初設定的範圍，導致範圍外的新資料無法自動顯示於清單中。為解決此問題，Excel 提供多種方法，可建立能隨資料新增而自動擴充的動態下拉式清單。

本指南介紹幾種實用方法，在 Excel 中建立自動更新的下拉式清單，有效減少維護負擔與潛在輸入錯誤，特別適合用於經常擴充的表格與清單。

Excel 公式－使用公式自動更新下拉式清單
Excel 表格－使用表格作為下拉式清單來源（新增項目時自動擴充）
VBA 程式碼－使用 VBA 自動更新下拉式清單來源區域

使用公式自動更新下拉式清單

在多種情境下，您都需要下拉式清單能自動更新——例如維護產品清單、管理報名表中的成員，或追蹤經常變動的專案任務。透過 OFFSET 函數建立動態範圍，即可讓下拉式清單在您於欄位中新增項目時，自動涵蓋所有內容。

1. 選取要插入下拉式清單的儲存格，然後依序前往資料 > 資料驗證 > 資料驗證。請參閱截圖：

功能區「資料」索引標籤上的「資料驗證」按鈕

2. 在資料驗證對話方塊中，切換至「設定」頁籤，從允許選項中選擇清單，並在「來源」框中輸入以下動態範圍公式：
=OFFSET($A$2,0,0,COUNTA(A:A)-1)

資料驗證對話框

參數說明與實用技巧：

A2 是您預期資料區域的第一個儲存格，請依實際清單的起始儲存格進行調整。
A:A 指的是包含清單資料的整欄。此設定確保當您在該欄新增更多項目時，函數會自動動態調整並重新計算範圍大小。
若欄位中包含空白儲存格或使用了子標題，請調整公式或確保資料位置一致，以避免下拉式清單出現空白項目。
處理大型資料集時，請留意 OFFSET 等易變函數可能對效能造成輕微影響，因為每次變更都會觸發重新計算。

3. 按一下確定。您現在已成功建立一個下拉式清單，當原始欄位輸入新資料時，清單將自動更新！當您在預期範圍內新增更多項目，這些項目會立即出現在下拉式清單中供您選擇。

原始清單更新後的清單

疑難排解與提示：

若下拉式清單出現非預期的空白項目，請檢查來源欄位是否含有額外空格或隱藏列。
若公式傳回錯誤，請確認您的資料未包含不連續的範圍，或完全空白的列。
若您的清單起始列不是第 2 列，請務必調整來源公式，並同步修正儲存格參照與 COUNTA(A:A）的設定。

使用表格作為下拉式清單來源（新增項目時自動擴充）

使用 Excel 表格作為下拉式清單的來源區域，是一種高效且適合初學者的方法。當您在表格中新增項目時，Excel 表格會自動擴充範圍，無需手動調整參照或公式，即可確保下拉式清單始終保持最新狀態。

此方法特別適合管理經常擴充或變動的清單，例如員工名冊、庫存或活動報名表。其主要優點在於操作簡便，並能可靠地維持清單的最新狀態；但請注意，此方法最適用於源數據位於同一工作表或活頁簿的情況，因為資料驗證中的表格不支援跨活頁簿參照。

1. 框選您的原始資料範圍（例如）A2:A6）。

2. 前往插入索引標籤，然後選擇表格。若清單包含標題，請勾選「我的表格有標題」核取方塊。

3。Excel 會將您的範圍格式化為表格，預設名稱可能是 Table 1（您可透過）表格設計索引標籤左側的表格名稱方塊確認或重新命名）。

4. 點選需要下拉式清單的儲存格，然後前往資料> 資料驗證。

5. 從「允許」下拉式清單中選擇「清單」選項，並在來源框中輸入表格欄位的參照，例如：

=INDIRECT("Table1[Column1]")

將 Table 1 替換為您的實際表格名稱，並將 Column 1 替換為表格標題。

6. 按一下確定。此後，每當您於表格下方新增資料時，欄位與下拉式清單都會自動更新，包含新項目！

注意事項與提示：

Excel 表格提供結構化的範圍，能隨資料變動自動擴充或收縮，非常適合用於經常更新的清單。
若需在其他工作表中引用下拉式清單，請使用 =INDIRECT("Table1[Column1]")，因為某些 Excel 版本中，資料驗證對表格的直接參照僅限於目前工作表。
只要清單僅包含非空白項目，此方法即可有效避免下拉式清單中出現空白值。

使用 VBA 自動更新下拉式清單來源區域

對於進階且高度自動化的應用場景（特別是處理長清單或自動化活頁簿維護任務），您可運用 VBA 程式碼，在新增資料時自動更新下拉式清單所依據的範圍。此方法適用於複雜的解決方案，例如多個下拉式清單需即時反映來源清單的變動，或為多位使用者集中管理下拉式清單。

1. 按下 Alt+F11 以開啟 VBA 編輯器，並在 VBAProject 中雙擊包含資料驗證的工作表。

2. 將下列程式碼複製並貼到模組中。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sourceColumn As Range
    Dim validationCell As Range
    Dim lastRow As Long
    Set sourceColumn = Me.Range("A:A") ' Change to your source column
    If Not Intersect(Target, sourceColumn) Is Nothing Then
        Application.EnableEvents = False
        lastRow = Me.Cells(Me.Rows.Count, sourceColumn.Column).End(xlUp).Row
        Set validationCell = Me.Range("D1:D100") ' Change to your validation cell  
        With validationCell.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                 Formula1:="=$A$1:$A$" & lastRow
        End With
        
        Application.EnableEvents = True
    End If
End Sub

3. 關閉程式碼視窗。此後，每當您向來源區域新增資料，下拉式清單都會自動更新。

修改程式碼中的參數：

來源欄位（「A:A」，即您新增資料的位置）
驗證儲存格／範圍（「D1:D100」，即下拉式清單所在位置）

注意事項：

程式碼會在工作表發生變更時自動執行
它會找出含資料的最後一行，並據此更新驗證範圍
請務必啟用巨集，此功能才能正常運作

請將檔案另存為 .xlsm 格式，以儲存程式碼。

透過 KUTOOLS AI 解鎖 Excel 的神奇功能

智慧執行：透過簡易指令，輕鬆執行儲存格操作、分析資料，並建立圖表！
自訂公式：打造專屬公式，讓您的工作流程更順暢！
VBA 編碼：輕鬆撰寫並套用 VBA 程式碼，立即提升工作效率！
公式解析：輕鬆掌握複雜公式！
文字翻譯：輕鬆打破試算表中的語言隔閡！

運用 AI 驅動的工具強化您的 Excel 能力。立即下載體驗前所未有的高效！

建立動態下拉式清單

最佳 Office 生產力工具

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