Excel 中可搜索下拉列表的終極指南

在 Excel 中創建下拉列表可以簡化數據輸入並減少錯誤。但對於較大的數據集，滾動瀏覽冗長的列表會變得繁瑣。如果能夠直接輸入並快速找到所需項目不是更好嗎？「可搜索下拉列表」提供了這種便利。本指南將帶您了解在 Excel 中設置此類列表的四種方法。

• Excel 365 中的可搜索下拉列表
• 創建可搜索下拉列表（適用於 Excel 2019 及更高版本）
• 輕鬆創建可搜索下拉列表（適用於所有 Excel 版本）
• 使用組合框和 VBA 創建可搜索下拉列表（更複雜）

視頻：創建可搜索下拉列表

Excel 365 中的可搜索下拉列表

Excel 365 在其數據驗證下拉列表中引入了一個備受期待的功能：在列表內進行搜索的能力。通過可搜索功能，用戶可以快速定位並選擇項目，效率更高。按照常規方式插入下拉列表後，只需點擊包含下拉列表的單元格並開始輸入。列表將立即過濾以匹配輸入的文字。

在此情況下，我在單元格中輸入 San，下拉列表將篩選出以搜索詞 San 開頭的城市，例如舊金山和聖迭戈。然後您可以使用鼠標選擇結果，或使用方向鍵並按 Enter。

創建可搜索下拉列表（適用於 Excel 2019 及更高版本）

如果您使用的是 Excel 2019 或更高版本，本節中的方法也可以用來使 Excel 中的下拉列表可搜索。

假設您已經使用 Sheet1 中 A2:A8 範圍的數據（左圖）在 Sheet2 的 A2 單元格中創建了下拉列表（右圖），請按照以下步驟使列表可搜索。

步驟 1. 創建一個列出搜索項目的輔助列

這裡我們需要一個輔助列來列出與源數據匹配的項目。在這種情況下，我將在 Sheet1 的 D 列中創建輔助列。

1. 選擇 D 列中的第一個單元格 D1 並輸入列標題，例如本例中的「搜索結果」。
2. 在 D2 單元格中輸入以下公式並按下 Enter.

   =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"未找到")

   

步驟 2：重新配置下拉列表

1. 選擇下拉列表單元格（在這種情況下，我選擇 Sheet2 的 A2 單元格），然後轉到選擇 數據 > 數據驗證 > 數據驗證。
   
2. 在 數據驗證 對話框中，您需要如下配置。
   1. 在 設置 選項卡中，點擊 按鈕在 來源 框中。
      
   2. 數據驗證對話框將重定向到 Sheet1，選擇具有第一步公式（例如 D2）的單元格，添加 # 符號，然後點擊關閉 按鈕。
      
   3. 轉到錯誤警告選項卡，取消勾選在輸入無效數據後顯示錯誤警告複選框，最後點擊確定按鈕保存更改。
      

結果

Sheet2 的 A2 單元格中的下拉列表現在可以搜索了。在單元格中輸入文字，點擊下拉箭頭展開下拉列表，您將看到列表即時過濾以匹配輸入的文字。

輕鬆創建可搜索下拉列表（適用於所有 Excel 版本）

鑑於上述方法的各種限制，這裡有一個非常有效的工具供您使用 - Kutools for Excel 的 Make Drop-down List Searchable, Auto-popup 功能。此功能適用於所有版本的 Excel，並允許您通過簡單的設置輕鬆在下拉列表中搜索所需的項目。

下載並安裝 Kutools for Excel 後，選擇 Kutools > 下拉列表 > Make Drop-down List Searchable, Auto-popup 啟用此功能。在 Make the Drop-down List Searchable 對話框中，您需要：

1. 選擇包含需要設置為可搜索下拉列表的範圍。
2. 點擊確定完成設置。
   

結果

當您點擊指定範圍內的下拉列表單元格時，右側會出現一個列表框。輸入文字以即時過濾列表，然後選擇項目或使用方向鍵並按 Enter 將其添加到單元格中。

使用組合框和 VBA 創建可搜索下拉列表（更複雜）

如果您只是想創建一個可搜索的下拉列表而不需要指定特定的下拉列表類型。本節提供了一種替代方法：使用帶有 VBA 代碼的組合框來實現任務。

假設您在 A 列中有一系列國家名稱，如下圖截圖所示，現在您想將它們用作可搜索下拉列表的源數據，您可以按照以下步驟操作來完成。

您需要在工作表中插入一個組合框而不是數據驗證下拉列表。

1. 如果 開發者 選項卡未顯示在功能區上，您可以啟用 開發者 選項卡如下。
   1. 在 Excel 2010 或更高版本中，點擊 文件 > 選項。在 Excel 選項 對話框中，點擊左窗格中的 自定義功能區。轉到 自定義功能區 列表框，勾選 開發者 複選框，然後點擊 確定 按鈕。見截圖：
      
   2. 在 Excel 2007 中，點擊 Office 按鈕 > Excel 選項。在 Excel 選項 對話框中，點擊左窗格中的 常用，勾選 在功能區中顯示開發者選項卡 複選框，最後點擊 確定 按鈕。
      
2. 顯示開發者選項卡後，點擊 開發者 > 插入 > 組合框。
   
3. 在工作表中繪製一個組合框，右鍵單擊它，然後從右鍵菜單中選擇 屬性。
   
4. 在 屬性 對話框中，您需要：
   1. 在 AutoWordSelect 字段中選擇 False；
   2. 在 LinkedCell 字段中指定一個單元格。在這種情況下，我們輸入 A12；
   3. 在 MatchEntry 字段中選擇 2-fmMatchEntryNone；
   4. 在 ListFillRange 字段中輸入 DropDownList；
   5. 關閉 屬性 對話框。見截圖：
      
5. 現在通過點擊 開發者 > 設計模式 關閉設計模式。
6. 選擇一個空白單元格，例如 C2，輸入以下公式並按下 Enter。然後拖動其自動填充柄到 C9 單元格以自動填充相同公式的單元格。見截圖：

   =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

   
   注意：

   1. $A$12 是您在第 4 步中指定為 LinkedCell 的單元格；
   2. 完成上述步驟後，您現在可以測試：在組合框中輸入字母 C，然後您可以看到引用包含字符 C 的單元格的公式單元格填滿數字 1。
7. 選擇 D2 單元格，輸入以下公式並按下 Enter。然後拖動其自動填充柄到 D9 單元格。

   =IF(C2=1,COUNTIF($C$2:C2,1),"")

   
8. 選擇 E2 單元格，輸入以下公式並按下 Enter。然後拖動其自動填充柄到 E9 以應用相同的公式。

   =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")

   
9. 現在您需要創建一個名稱範圍。請點擊 公式 > 定義名稱。
   
10. 在 新名稱 對話框中，輸入 DropDownList 在 名稱 框中，在 引用位置 框中輸入以下公式，然後點擊 確定 按鈕。

    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    

    
11. 現在，通過點擊 開發者 > 設計模式 打開設計模式。然後雙擊組合框以打開 Microsoft Visual Basic for Applications 窗口。
12. 將以下 VBA 代碼複製並粘貼到代碼編輯器中。
    
    VBA 代碼：使下拉列表可搜索

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. 按下 Alt + Q 鍵關閉 Microsoft Visual Basic for Applications 窗口。

從現在開始，當在組合框中輸入字符時，它將進行模糊搜索，然後在列表中列出相關值。

相關文章：