Excel 中可搜尋下拉清單的終極指南
在 Excel 中建立下拉清單可簡化資料輸入並最大限度地減少錯誤。 但對於較大的資料集,滾動瀏覽冗長的清單會變得很麻煩。 只需鍵入並快速找到您的項目不是更容易嗎? A ”可搜尋的下拉列表」提供了這種便利。本指南將引導您完成在 Excel 中設定此類清單的四種方法。
- Excel 365 中可搜尋的下拉列表
- 建立可搜尋下拉清單(適用於 Excel 2019 及更高版本)
- 輕鬆建立可搜尋的下拉清單(適用於所有 Excel 版本)
- 使用組合方塊和 VBA 建立可搜尋下拉清單(更複雜)
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.
- 選擇第一個單元格 D1 在 D 列中輸入列標題,例如“搜尋結果” 在這種情況下。
- 在儲存格 D2 中輸入以下公式,然後按 Enter.
=FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
- 在這個公式中, A2:A8 是來源資料範圍。 表 2!A2 是下拉清單的位置,表示下拉清單位於Sheet2的A2處。 請根據您自己的數據進行更改。
- 如果沒有從 Sheet2 A2 的下拉清單中選擇任何項目,則公式將顯示來源資料中的所有項目,如上圖所示。 相反,如果選擇了某個項目,D2 將將該項目顯示為公式的結果。
步驟 2:重新配置下拉列表
- 選擇下拉清單儲存格(本例中我選擇Sheet2的A2儲存格),然後去選擇 數據 > 數據驗證 > 數據驗證.
- 在 數據驗證 對話方塊中,您需要進行以下配置。
- 下 設定 標籤,點擊 按鈕在 資源 框。
- 數據驗證 對話方塊將重新導向至 Sheet1,選擇包含步驟 2 中的公式的儲存格(例如 D1),並新增 # 符號,然後單擊 關閉 按鈕。
- 轉到 錯誤警報 標籤,取消選中 輸入無效數據後顯示錯誤警報 複選框,最後單擊 OK 按鈕保存更改。
結果
Sheet2 儲存格 A2 中的下拉清單現在可搜尋。 在單元格中鍵入文本,單擊下拉箭頭展開下拉列表,您將看到列表立即經過篩選以匹配鍵入的文本。
- 此方法僅適用於Excel 2019及更高版本。
- 此方法一次僅適用於一個下拉清單儲存格。 要使 Sheet3 中的儲存格 A8 到 A2 中的下拉清單可搜索,必須對每個儲存格重複上述步驟。
- 當您在下拉清單儲存格中鍵入文字時,下拉清單不會自動展開,您需要按一下下拉箭頭手動展開。
輕鬆建立可搜尋的下拉清單(適用於所有 Excel 版本)
鑑於上述方法的各種局限性,這裡有一個對您來說非常有效的工具 - Excel的Kutools's 使下拉清單可搜尋、自動彈出特徵。所有版本的 Excel 中均提供此功能,您可以透過簡單的設定輕鬆地在下拉清單中搜尋所需的項目。
後 下載並安裝 Kutools for Excel, 選擇 庫工具 > 下拉列表 > 使下拉清單可搜尋、自動彈出 啟用此功能。 在裡面 使下拉清單可搜尋 對話框,您需要:
- 選擇包含需要設定為可搜尋下拉清單的下拉清單的範圍。
- 點擊 OK 完成設置。
結果
當您按一下指定範圍內的下拉清單儲存格時,右側會出現一個列錶框。輸入文字以立即過濾列表,然後選擇一個項目或使用箭頭鍵並點擊 Enter 將其新增至單元格。
- 該功能支持 從單字內的任意位置進行搜尋。 這意味著即使您輸入位於單字中間或末尾的字符,仍然會找到並顯示匹配的項目,從而提供更全面和用戶友好的搜尋體驗。
- 要了解有關此功能的更多信息,請 訪問此頁.
- 要應用此功能,請 下載並安裝 Kutools for Excel 第一。
使用組合方塊和 VBA 建立可搜尋下拉清單(更複雜)
如果您只想建立可搜尋的下拉清單而不指定特定的下拉清單類型。 本節提供了另一種方法:使用帶有 VBA 程式碼的組合方塊來完成任務。
假設您在 A 列中有一個國家名稱列表,如下面的屏幕截圖所示,現在您想將它們用作搜索下拉列表的源數據,您可以執行以下操作來完成它。
您需要在工作表中插入一個組合框而不是數據驗證下拉列表。
- 如果 開發者
選項卡不顯示在功能區上,您可以啟用 開發者
標籤如下。
- 在 Excel 2010 或更高版本中,按一下 文件 > 選項。 而在中 Excel 選項 對話框,單擊 自定義功能區 在左側窗格中。 轉到自定義功能區列錶框,選中 開發者 框,然後單擊 OK 按鈕。 看截圖:
- 在Excel 2007中,單擊 Office 按鈕> Excel選項。 在 Excel選項 對話框,單擊 熱門 在左窗格中,檢查 在功能區中顯示“開發人員”選項卡 框,最後單擊 OK 按鈕。
- 顯示後 開發者 標籤,點擊 開發者 > 插入 > 組合框.
- 在工作表中繪製一個組合框,右鍵單擊它,然後選擇 氟化鈉性能 從右鍵單擊菜單中。
- 在 氟化鈉性能 對話框,您需要:
- 選擇 假 ,在 自動選詞 領域;
- 指定一個單元格 鏈接單元 領域。 在這種情況下,我們輸入A12。
- 選擇 2-fmMatchEntryNone ,在 匹配項 領域;
- 類別 下拉列表 到 列表填充範圍 領域;
- 關上 氟化鈉性能 對話框。 看截圖:
- 現在透過點擊關閉設計模式 開發者 > 設計模式.
- 選擇一個空白儲存格,例如C2,輸入下面的公式,然後按 Enter. 他們將其自動填充句柄向下拖動到單元格 C9 以使用相同的公式自動填充單元格。 看截圖:
=--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
筆記:- 12澳元 是您將其指定為的單元格 鏈接單元 在步驟 4 中;
- 完成上述步驟後,現在可以測試:在組合框中輸入字母C,然後可以看到引用包含字元C的儲存格的公式儲存格填充了數字1。
- 選擇儲存格D2,輸入下面的公式,然後按 Enter. 然後將其自動填充句柄向下拖動到單元格 D9。
=IF(C2=1,COUNTIF($C$2:C2,1),"")
- 選擇儲存格E2,輸入下面的公式,然後按 Enter. 然後將其自動填充手柄向下拖動到 E9 以應用相同的公式。
=IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
- 現在您需要建立一個名稱範圍。 請點選 公式 > 定義名稱.
- 在 新名字 對話框中,鍵入 下拉列表 ,在 姓名 框,在下面的公式中輸入 指 框,然後單擊 OK 按鈕。
=$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
- 現在,透過點擊開啟設計模式 開發者 > 設計模式. 然後雙擊組合框打開 Microsoft Visual Basic for Applications 窗口。
- 將下面的 VBA 程式碼複製並貼上到程式碼編輯器中。
VBA代碼:使下拉列表可搜索
Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub
- 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。
從現在開始,當在組合框中輸入一個字符時,它會進行模糊搜索,然後在列表中列出相關值。
相關文章:
在Excel下拉列表中鍵入時自動完成
如果您有一個包含大值的數據驗證下拉列表,則需要在列表中向下滾動以查找合適的列表,或直接在列錶框中鍵入整個單詞。 如果在下拉列表中鍵入第一個字母時有允許自動完成的方法,一切將變得更加容易。 本教程提供了解決問題的方法。
在Excel中從另一個工作簿創建下拉列表
在工作簿中的工作表之間創建數據驗證下拉列表非常容易。 但是,如果數據驗證所需的列表數據位於另一個工作簿中,您將怎麼辦? 在本教程中,您將詳細了解如何從Excel中的另一個工作簿創建拖放列表。
在Excel中創建可搜索的下拉列表
對於具有眾多價值的下拉列表,找到合適的價值並非易事。 以前,我們已經介紹了一種在下拉框中輸入第一個字母時自動完成下拉列表的方法。 除了自動完成功能之外,您還可以使下拉列表可搜索,以提高在下拉列表中查找適當值時的工作效率。 為了使下拉列表可搜索,請嘗試本教程中的方法。
在Excel下拉列表中選擇值時自動填充其他單元格
假設您已經根據單元格區域B8:B14中的值創建了一個下拉列表。 在下拉列表中選擇任何值時,都希望在選定單元格中自動填充單元格範圍C8:C14中的相應值。 為了解決該問題,本教程中的方法將對您有所幫助。
最佳辦公效率工具
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 一樣。