跳到主要內容

Excel 中可搜尋下拉清單的終極指南

新增一名作者 最後修改時間:2024-03-26

在 Excel 中建立下拉清單可簡化資料輸入並最大限度地減少錯誤。 但對於較大的資料集,滾動瀏覽冗長的清單會變得很麻煩。 只需鍵入並快速找到您的項目不是更容易嗎? A ”可搜尋的下拉列表」提供了這種便利。本指南將引導您完成在 Excel 中設定此類清單的四種方法。


視頻資料


Excel 365 中可搜尋的下拉列表

Excel 365 在其資料驗證下拉清單中引入了一項備受期待的功能:在清單中搜尋的能力。 借助可搜尋功能,使用者可以更有效地快速定位和選擇項目。 像往常一樣插入下拉清單後,只需點擊帶有下拉清單的儲存格並開始輸入。 該清單將立即過濾以匹配鍵入的文字。

在這種情況下,我輸入 在儲存格中,下拉清單會過濾掉以搜尋字詞開頭的城市 舊金山聖地亞哥。 然後您可以使用滑鼠選擇結果或使用箭頭鍵並按 Enter 鍵。

筆記:
  • 搜尋從每個單字的第一個字母開始 在下拉清單中。 如果您輸入的字元與任何單字的起始字元都不匹配,則清單將不會顯示匹配的項目。
  • 此功能僅在最新版本的 Excel 365 中可用。
  • 如果您的Excel版本不支援此功能,這裡我們推薦 可搜索的下拉列表 的特點 Excel的Kutools。 沒有Excel版本限制,一旦啟用,您只需鍵入相關文字即可在下拉清單中輕鬆搜尋所需的項目。 查看詳細步驟.

建立可搜尋下拉清單(適用於 Excel 2019 及更高版本)

如果您使用的是Excel 2019或更高版本,也可以使用本節中的方法使下拉清單在Excel中可搜尋。

假設您已使用 Sheet2(左圖)的 A2:A2 範圍內的資料在 Sheet8(右圖)的儲存格 A1 中建立了一個下拉列表,請按照下列步驟使該列表可搜尋。

步驟 1. 建立一個列出搜尋項目的說明列

這裡我們需要一個輔助列來列出與來源資料相符的項目。 在本例中,我將在中建立輔助列 D欄 of Sheet1.

  1. 選擇第一個單元格 D1 在 D 列中輸入列標題,例如“搜尋結果” 在這種情況下。
  2. 在儲存格 D2 中輸入以下公式,然後按 Enter.
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
筆記:
  • 在這個公式中, A2:A8 是來源資料範圍。 表 2!A2 是下拉清單的位置,表示下拉清單位於Sheet2的A2處。 請根據您自己的數據進行更改。
  • 如果沒有從 Sheet2 A2 的下拉清單中選擇任何項目,則公式將顯示來源資料中的所有項目,如上圖所示。 相反,如果選擇了某個項目,D2 將將該項目顯示為公式的結果。
步驟 2:重新配置下拉列表
  1. 選擇下拉清單儲存格(本例中我選擇Sheet2的A2儲存格),然後去選擇 數據 > 數據驗證 > 數據驗證.
  2. 數據驗證 對話方塊中,您需要進行以下配置。
    1. 設定 標籤,點擊 按鈕在 資源 框。
    2. 數據驗證 對話方塊將重新導向至 Sheet1,選擇包含步驟 2 中的公式的儲存格(例如 D1),並新增 # 符號,然後單擊 關閉 按鈕。
    3. 轉到 錯誤警報 標籤,取消選中 輸入無效數據後顯示錯誤警報 複選框,最後單擊 OK 按鈕保存更改。
結果

Sheet2 儲存格 A2 中的下拉清單現在可搜尋。 在單元格中鍵入文本,單擊下拉箭頭展開下拉列表,您將看到列表立即經過篩選以匹配鍵入的文本。

筆記:
  • 此方法僅適用於Excel 2019及更高版本。
  • 此方法一次僅適用於一個下拉清單儲存格。 要使 Sheet3 中的儲存格 A8 到 A2 中的下拉清單可搜索,必須對每個儲存格重複上述步驟。
  • 當您在下拉清單儲存格中鍵入文字時,下拉清單不會自動展開,您需要按一下下拉箭頭手動展開。

輕鬆建立可搜尋的下拉清單(適用於所有 Excel 版本)

鑑於上述方法的各種局限性,這裡有一個對您來說非常有效的工具 - Excel的Kutools's 使下拉清單可搜尋、自動彈出特徵。所有版本的 Excel 中均提供此功能,您可以透過簡單的設定輕鬆地在下拉清單中搜尋所需的項目。

下載並安裝 Kutools for Excel, 選擇 庫工具 > 下拉列表 > 使下拉清單可搜尋、自動彈出 啟用此功能。 在裡面 使下拉清單可搜尋 對話框,您需要:

  1. 選擇包含需要設定為可搜尋下拉清單的下拉清單的範圍。
  2. 點擊 OK 完成設置。
結果

當您按一下指定範圍內的下拉清單儲存格時,右側會出現一個列錶框。輸入文字以立即過濾列表,然後選擇一個項目或使用箭頭鍵並點擊 Enter 將其新增至單元格。

筆記:
  • 該功能支持 從單字內的任意位置進行搜尋。 這意味著即使您輸入位於單字中間或末尾的字符,仍然會找到並顯示匹配的項目,從而提供更全面和用戶友好的搜尋體驗。
  • 要了解有關此功能的更多信息,請 訪問此頁.
  • 要應用此功能,請 下載並安裝 Kutools for Excel 第一。

使用組合方塊和 VBA 建立可搜尋下拉清單(更複雜)

如果您只想建立可搜尋的下拉清單而不指定特定的下拉清單類型。 本節提供了另一種方法:使用帶有 VBA 程式碼的組合方塊來完成任務。

假設您在 A 列中有一個國家名稱列表,如下面的屏幕截圖所示,現在您想將它們用作搜索下拉列表的源數據,您可以執行以下操作來完成它。

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

  1. 如果 開發者 選項卡不顯示在功能區上,您可以啟用 開發者 標籤如下。
    1. 在 Excel 2010 或更高版本中,按一下 文件 > 選項。 而在中 Excel 選項 對話框,單擊 自定義功能區 在左側窗格中。 轉到自定義功能區列錶框,選中 開發者 框,然後單擊 OK 按鈕。 看截圖:
    2. 在Excel 2007中,單擊 辦公 按鈕> Excel選項。 在 Excel選項 對話框,單擊 熱門 在左窗格中,檢查 在功能區中顯示“開發人員”選項卡 框,最後單擊 OK 按鈕。
  2. 顯示後 開發者 標籤,點擊 開發者 > 插入 > 組合框.
  3. 在工作表中繪製一個組合框,右鍵單擊它,然後選擇 氟化鈉性能 從右鍵單擊菜單中。
  4. 氟化鈉性能 對話框,您需要:
    1. 選擇 ,詳見 自動選詞 領域;
    2. 指定一個單元格 鏈接單元 領域。 在這種情況下,我們輸入A12。
    3. 選擇 2-fmMatchEntryNone ,詳見 匹配項 領域;
    4. 類別 下拉列表列表填充範圍 領域;
    5. 關上 氟化鈉性能 對話框。 看截圖:
  5. 現在透過點擊關閉設計模式 開發者 > 設計模式.
  6. 選擇一個空白儲存格,例如C2,輸入下面的公式,然後按 Enter. 他們將其自動填充句柄向下拖動到單元格 C9 以使用相同的公式自動填充單元格。 看截圖:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    筆記:
    1. 12澳元 是您將其指定為的單元格 鏈接單元 在步驟 4 中;
    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. 新名字 對話框中,鍵入 下拉列表 ,詳見 姓名 框,在下面的公式中輸入 框,然後單擊 OK 按鈕。
    =$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. 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

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

備註:您需要將此工作簿保存為 Excel 啟用宏的工作簿文件,以便保留 VBA 代碼以供將來使用。

最佳辦公效率工具

Kutools for Excel-幫助您從人群中脫穎而出

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項  |  刪除空白行  |  合併列或儲存格而不遺失數據  |  沒有公式的回合 ...
超VLookup: 多重標準  |  多重價值  |  跨多頁  |  模糊查詢...
副詞。 下拉清單: 簡易下拉列表  |  依賴下拉列表  |  多選下拉列表...
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  將列與 選擇相同和不同的單元格 ...
特色功能: 網格焦點  |  設計圖  |  大方程式酒吧  |  工作簿和工作表管理器 | 資源庫 (自動文字)  |  日期選擇器  |  合併工作表  |  加密/解密單元格  |  按清單發送電子郵件  |  超級濾鏡  |  特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符 ...)  |  50+ 圖表 類型 (甘特圖 ...)  |  40+ 實用 公式 (根據生日計算年齡 ...)  |  19 插入 工具 (插入二維碼, 從路徑插入圖片 ...)  |  12 轉化 工具 (數字到單詞, 貨幣兌換 ...)  |  7 合併與拆分 工具 (高級合併行, 拆分 Excel 儲存格 ...)  |  ... 和更多

Kutools for Excel 擁有超過 300 個功能, 確保只需點擊一下即可獲得您所需要的...

產品描述


Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,將您的工作效率提高 50%。
  • 為 Office(包括 Excel)帶來高效的選項卡,就像 Chrome、Edge 和 Firefox 一樣。
Comments (67)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Perfect idea for me. But I have a problem with the "ROWS" formula.
I mean point 8.
When I use your formula (in the drop-down list I have nothing entered, as you can see in point 8) in the first cell is "INDIA".
And pick up the cells with the "spilled" error. What I need to change for the formula to work properly.

E1 - India
E2 - #SPILL!
E3 -#SPILL!
E4 - #SPILL!
E5 - #SPILL!
E6 -#SPILL!
E7 - #SPILL!
E8 - India
E9 - Brazil
E10 - Italy
E11 - Japan
E12 - United State
E13 - Francy
E14 - Germany

You also see that there are more poems appearing than yours.
This comment was minimized by the moderator on the site
Hi Przamek PL,
Sory, I cannot reproduce the problem you mentioned. Can you provide us with your data for tesing? If you don't mind, upload your sample file here.
This comment was minimized by the moderator on the site
Thank you for your message.
I was able to run your example correctly.
I have a reflection now ...
How to apply your solution to the UseForm form?

I would like to select a person from the list in the form, then I would have information about the age of this person elsewhere in the form. Such a simple example. Difficult?
This comment was minimized by the moderator on the site
Hi Przemek PF,
This method does not work in UserForm. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Somehow excel will not let me fill in the ListFillRange with ANYTHING. so also not the DropDownList. I did all the steps but am not able to get a flashing cursor and when I type no drop down list appears. any solutions?
This comment was minimized by the moderator on the site
Hi Marloes, This problem can't be solved yet. Make sure the ListFillRange is on the same sheet as your list box. 
This comment was minimized by the moderator on the site
I've just purchased kutools to use this function. Is it possible to have two or more different searchable drop down lists (i.e. referncing different lists of valid entries) on the same sheet?
This comment was minimized by the moderator on the site
Hi Marc,The feature does not support two or more different searchable drop down lists on the same sheet. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
how to use this dropdown in vba form any konw please reply
This comment was minimized by the moderator on the site
Hi, I made an action list for internal use with automatic email reminders in Excel, based on macro and vba. in a cell you select which person to send the reminder to, in a next cell you select which person to CC etc. Is it a good idea to copy this dropdownlist a few 100 times to every possible entry that I supply ? And is it possible to add a rule: Per row a particular person can only be selected once?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations