Skip to main content

Kutools for Office — 一套工具,五種功能。完成更多工作。

在 Excel 中創建動態依賴下拉列表(逐步指南)

Author: Sun Last Modified: 2025-08-23

在本教程中,我們將逐步介紹如何創建一個依賴下拉列表,該列表根據第一個下拉列表中選擇的值顯示選項。換句話說,我們將根據另一個列表的值來創建一個 Excel 數據驗證列表。

創建動態依賴下拉列表
10秒鐘使用便捷工具創建依賴下拉列表
在 Excel 2021、Excel 365 和更新版本中創建動態依賴下拉列表
您可能對本教程提出的問題

A screenshot showing a dependent drop-down list setup in Excel

免費下載範例文件 An icon for downloading the sample file for creating dependent drop-down lists in Excel


視頻:創建 Excel 依賴下拉列表

 

創建動態依賴下拉列表

 

步驟 1:輸入下拉列表的條目

1. 首先,輸入您希望出現在下拉列表中的條目,每個列表放在不同的列中。

請注意,第一列(產品)中的項目將作為後續依賴列表的 Excel 名稱。例如,這裡的水果和蔬菜將分別成為 B2:B5 和 C2:C6 的名稱。

見截圖:

A screenshot showing entries for drop-down lists in Excel, each list in a separate column

2. 然後為每個數據列表創建表格。

選擇 A1:A3 列範圍,點擊「插入」>「表格」,然後在「創建表格」對話框中勾選「我的表格有標題」複選框。點擊「確定」。

A screenshot showing how to create a table in Excel for drop-down list entries

然後重複此步驟為其他兩個列表創建表格。

您可以在名稱管理器中查看所有表格和範圍引用(按「Ctrl」+「F3」打開)。

A screenshot showing the Name Manager with table references in Excel

步驟 2:創建範圍名稱

在此步驟中,您需要為主列表和每個依賴列表創建「名稱」。

1. 選擇主列表中出現的項目("A2:A3")。

2. 然後進入公式欄旁邊的「名稱框」。

3. 輸入名稱,這裡命名為「產品」。

4. 按下「Enter」鍵完成。

A screenshot showing how to create a range name for the main drop-down list in Excel

然後重複上述步驟,分別為每個依賴列表創建名稱。

這裡將第二列(B2:B5)命名為水果,第三列(C2:C6)命名為蔬菜。

A screenshot showing how to create range names for the fruit list

A screenshot showing how to create range names for the vegetable list

您可以在名稱管理器中查看所有範圍名稱(按「Ctrl」+「F3」打開)。

A screenshot showing range names for dependent drop-down lists in the Name Manager in Excel

步驟 3:添加主下拉列表

接下來,添加主下拉列表(產品),這是一個普通的數據驗證下拉列表,而不是依賴下拉列表。

1. 首先,創建一個表格。

選擇一個單元格("E1"),並輸入第一列標題("產品"),然後移動到下一列單元格("F1"),輸入第二列標題("項目")。這個表格將包含下拉列表。

然後選擇這兩個標題("E1" 和 "F1"),點擊「插入」選項卡,並在表格組中選擇「表格」。

在「創建表格」對話框中,勾選「我的表格有標題」框,然後點擊「確定」。

A screenshot showing the creation of a table for drop-down list usage in Excel

2. 選擇要插入主下拉列表的單元格 "E2",點擊「數據」選項卡,然後進入「數據工具」組,點擊「數據驗證」>「數據驗證」。

A screenshot showing how to insert a main drop-down list in Excel using Data Validation

3. 在「數據驗證」對話框中,

  • 在「允許」部分選擇「列表」,
  • 在「源」欄中輸入以下公式,產品是主列表的名稱,
  • 點擊「確定」。
=Product

A screenshot showing the Data Validation dialog for the main drop-down list in Excel

您可以看見主下拉列表已經創建完成。

A screenshot showing the main drop-down list created in Excel

步驟 4:添加依賴下拉列表

1. 選擇要添加依賴下拉列表的單元格 "F2",點擊「數據」選項卡,然後進入「數據工具」組,點擊「數據驗證」>「數據驗證」。

2. 在「數據驗證」對話框中,

  • 在「允許」部分選擇「列表」,
  • 在「源」欄中輸入以下公式,E2 是包含主下拉列表的單元格。
  • 點擊「確定」。
=INDIRECT(SUBSTITUTE(E2," ","_"))

A screenshot showing how to add a dependent drop-down list in Excel using Data Validation

如果 E2 為空(您未在主下拉列表中選擇任何項目),您會看到如下消息彈出,點擊「是」繼續。

A screenshot showing a warning message when the main drop-down list is empty in Excel

現在依賴下拉列表已創建完成。

A screenshot showing a completed dependent drop-down list in Excel

步驟 5:測試依賴下拉列表。

1. 在主下拉列表("E2")中選擇「水果」,然後轉到依賴下拉列表("F2")點擊箭頭圖標,檢查水果項目是否在列表中,然後從依賴下拉列表中選擇一個項目。

2. 按下「Tab」鍵在數據輸入表中開始新的一行,選擇「蔬菜」,然後移動到右側的下一個單元格,檢查蔬菜項目是否在列表中,然後從依賴下拉列表中選擇一個項目。

An animation demonstrating how to use the dependent drop-down list in Excel

注意:

10秒鐘使用便捷工具創建依賴下拉列表

 

「Kutools for Excel」提供了一個強大的工具,使創建依賴下拉列表變得更容易、更快捷:

An animation showing how to create a dependent drop-down list in Excel using Kutools

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

步驟 1:輸入下拉列表的條目

首先,按照以下截圖所示排列您的數據:

A screenshot showing how to arrange data for creating a dependent drop-down list

步驟 2:應用 Kutools 工具

1. 選擇您創建的數據,點擊「Kutools」選項卡,然後點擊「下拉列表」以顯示子菜單,點擊「動態下拉列表」。

A screenshot showing the Kutools Drop-down List menu in Excel

2. 在「依賴下拉列表」中:

  • 勾選與您的數據模式匹配的「模式 B」,
  • 選擇「輸出範圍」,輸出範圍列數必須等於數據範圍列數,
  • 點擊「確定」。

A screenshot showing the Dependent Drop-down List dialog

現在依賴下拉列表已創建完成。

A screenshot showing a completed dependent drop-down list created with Kutools

提示:
  • 「模式 B」支持在下拉列表中創建第三級或更多級別:
    A screenshot showing Mode B in Kutools for creating a multi-level dependent drop-down list
  • 如果您的數據按照以下截圖所示排列,則需要使用「模式 A」,它僅支持創建二級依賴下拉列表。
    A screenshot showing Mode A in Kutools for creating a 2-level dependent drop-down list
  • 有關如何使用 Kutools 創建依賴下拉列表的更多詳細信息,請訪問本教程

Kutools for Excel

全功能免費試用 30 天,無需信用卡。

超過 300 個強大高級功能和函數適用於 Excel。

不需要任何特殊技能,每天節省數小時時間。

在 Excel 2021、Excel 365 和更新版本中創建動態依賴下拉列表

 

如果您使用的是 Excel 365、Excel 2021 或更新版本,還有一種方法可以快速創建動態依賴下拉列表,使用新函數「UNIQUE」和「FILTER」。

假設您的源數據如截圖所示排列,請按照以下步驟創建動態下拉列表。

A screenshot showing source data arranged for creating dependent drop-down lists in Excel

步驟 1:使用公式獲取主下拉列表的項目

選擇一個單元格,例如 G3 單元格,並使用 UNIQUE 和 FILTER 函數從「產品」列表中提取唯一值,這些值將作為主下拉列表的源,然後按下「Enter」鍵。

=UNIQUE(FILTER(A3:A20, A3:A20<>""))
注意:由於產品位於 A3:A12,我們在數組中額外增加了 8 個單元格以適應可能的新條目。此外,我們將 FILTER 函數嵌套在 UNIQUE 中以提取不含空白的唯一值。

A screenshot showing the UNIQUE and FILTER formula used to extract items for the main drop-down list in Excel

步驟 2:創建主下拉列表

1. 選擇要放置主下拉列表的單元格,例如 D3 單元格,點擊「數據」選項卡,然後進入「數據工具」組,點擊「數據驗證」>「數據驗證」。

2. 在「數據驗證」對話框中,

  • 在「允許」部分選擇「列表」,
  • 在「源」欄中輸入以下公式,
  • 點擊「確定」。
=$G$3#
注意:這被稱為溢出範圍引用,此語法無論範圍擴展或收縮多少都指整個範圍。

A screenshot showing the Data Validation dialog for creating the main drop-down list in Excel

現在主下拉列表已創建完成。

A screenshot showing the created main drop-down list in Excel

步驟 3:使用公式獲取依賴下拉列表的項目

選擇一個單元格,例如 H3 單元格,使用 FILTER 函數根據 D3 單元格中的值(主下拉列表中選擇的項目)過濾項目,按下「Enter」鍵。

=FILTER(B3:B20, A3:A20=D3)
注意:如果主下拉列表中有空白,公式將返回零。

A screenshot showing the FILTER formula used to extract dependent items in Excel

步驟 4:創建依賴下拉列表

1. 選擇要放置依賴下拉列表的單元格,例如 E3 單元格,點擊「數據」選項卡,然後進入「數據工具」組,點擊「數據驗證」>「數據驗證」。

2. 在「數據驗證」對話框中,

  • 在「允許」部分選擇「列表」,
  • 在「源」欄中輸入以下公式,
  • 點擊「確定」。
=$H$3#
注意:這被稱為溢出範圍引用,此語法無論範圍擴展或收縮多少都指整個範圍。

A screenshot showing the Data Validation dialog for creating the dependent drop-down list in Excel

現在依賴下拉列表已成功創建。

A screenshot showing the completed dependent drop-down list in Excel

當您新增項目或在 A3:A20 中進行一些更改時,下拉列表將自動更新。

提示:

按字母順序排序下拉列表

如果您希望按字母順序排列下拉列表中的項目,可以使用以下公式準備表格。

對於主下拉列表(G3 單元格中的公式):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

對於依賴下拉列表(H3 單元格中的公式):

=SORT(FILTER(B3:B20, A3:A20=D3))

現在兩個下拉列表均已按字母順序 A 到 Z 排序。

A screenshot showing the sorted dependent drop-down lists alphabetically in Excel

若要按字母順序 Z 到 A 排序,請使用以下公式:

對於主下拉列表(G3 單元格中的公式):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

對於依賴下拉列表(H3 單元格中的公式):

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)

您可能會問的一些問題:

1. 為什麼要為每個數據列表插入表格?

為數據列表插入表格將幫助您根據數據列表的變化自動更新下拉列表。例如,在第一個數據列表中添加“其他”,那麼主下拉列表也會自動添加“其他”。

A screenshot showing how a table automatically updates a drop-down list when new data is added

2. 為什麼使用表格來放置下拉列表?

當您按下 Tab 鍵向表格中添加新行時,下拉列表也會自動添加到新行中。

3. INDIRECT 函數是如何工作的?

INDIRECT 函數用於將文本字符串轉換為有效的引用。

4. 公式 INDIRECT(SUBSTITUTE(E2&F2," ","")) 是如何工作的?

首先,SUBSTITUTE 函數用另一個文本替換文本。這裡它用於刪除合併名稱(E2 和 F2)中的空格。然後 INDIRECT 函數將文本字符串(由 E2 和 F2 合併的內容)轉換為有效的引用。

最佳 Office 生產力工具

🤖 Kutools AI 助手:以智能執行為基礎,革新數據分析 生成程式碼 創建自訂公式 分析數據並生成圖表 調用 Kutools 增強函數
熱門功能查找、選取項目的背景色或標記重複值刪除空行合併列或單元格且不遺失數據四捨五入(免公式)...
高級 LOOKUP多條件 VLookup多值 VLookup多表查找模糊查找...
高級下拉列表快速創建下拉列表 依賴型下拉列表 多選下拉列表...
列管理器添加指定數量的列移動列切換隱藏列的顯示狀態比較區域及列...
精選功能網格聚焦 設計檢視 增強編輯欄 工作簿及工作表管理器 資源庫(快捷文本) 日期提取器 合併資料 加密/解密儲存格 按列表發送電子郵件 超級篩選 特殊篩選(篩選粗體/傾斜/刪除線...)...
15 大工具集12 項文本工具添加文本刪除特定字符…)50+ 儀表 類型甘特圖等)40+ 實用 公式基於生日計算年齡等)19 項插入工具插入QR码根據路徑插入圖片等)12 項轉換工具金額轉大寫匯率轉換等)7 項合併與分割工具高級合併行分割儲存格等)...及更多
使用 Kutools,語言任你選 — 支援英語、西班牙語、德語、法語、中文及超過40 種語言!

運用 Kutools for Excel,全面提升您的 Excel 技能,體驗前所未有的高效。 Kutools for Excel 提供超過300 項進階功能,讓您提升工作效率、節省時間。 點此尋找您最需要的功能...


Office Tab 為 Office 帶來分頁介面,讓您的工作更加輕鬆簡單

  • 在 Word、Excel、PowerPoint 中啟用分頁編輯與閱讀
  • 在同一視窗的新分頁中打開與創建多份文件,而非開啟新視窗。
  • 提升您的生產力50%,每日可幫您減少數百次鼠標點擊!

所有 Kutools 外掛,一次安裝

Kutools for Office 套裝整合了 Excel、Word、Outlook 和 PowerPoint 的外掛,外加 Office Tab Pro,非常適合需要跨 Office 應用程式協同作業的團隊。

Excel Word Outlook Tabs PowerPoint
  • 全合一套裝 — Excel、Word、Outlook及 PowerPoint 外掛 + Office Tab Pro
  • 一鍵安裝,一份授權 — 幾分鐘完成設置(支援 MSI)
  • 協同運作更順暢 — Office 應用間無縫提升生產力
  • 30 天全功能試用 — 無需註冊、無需信用卡
  • 最超值 — 一次購買,節省單獨外掛費用
 
{# article_7317_afterDisplayContent #}