Skip to main content

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

Author: Sun Last Modified: 2025-05-12

在本教程中,我們將逐步介紹如何創建一個依賴下拉列表,該列表根據第一個下拉列表中選擇的值顯示選項。換句話說,我們將根據另一個列表的值來創建一個 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 合併的內容)轉換為有效的引用。

最佳辦公效率工具

🤖 Kutools AI 助手:基於智能執行方式革新數據分析:智能執行   |  生成代碼  |  創建自訂公式  |  分析數據並生成圖表  |  調用 Kutools 函數
熱門功能查找、標記重複值或識別重複項   |  刪除空行   |  合併列或單元格而不丟失數據   |   四捨五入無需公式 ...
高級 LOOKUP多條件 VLookup    多值 VLookup  |   多表查找   |   模糊查找 ....
高級下拉列表快速創建下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
列管理器添加特定數量的列  |  移動列  |  切換隱藏列的可見狀態  |  比較區域和列 ...
特色功能網格聚焦   |  設計檢視   |   增強編輯欄    工作簿與工作表管理器   |  資源庫(自動文本)   |  日期提取器   |  合併資料   |  加密/解密儲存格    按列表發送電子郵件   |  超級篩選   |   特殊篩選(篩選粗體/斜體/刪除線...) ...
頂級 15 種工具集12 個文本工具添加文本刪除特定字符、...)   |   50+ 圖表 類型甘特圖、...)   |   40+ 實用 公式基於生日計算年齡、...)   |   19 個插入工具插入QR碼根據路徑插入圖片、...)   |   12 個轉換工具金額轉大寫匯率轉換、...)   |   7 個合併與分割工具高級合併行分割儲存格、...)   |   ... 還有更多

使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。  點擊這裡獲取您最需要的功能...


Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
  • 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
  • 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!