跳到主要內容

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

在 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中,單擊 Office 按鈕> 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 一樣。