提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中創建可搜索的下拉列表?

對於有許多值的下拉列表,找到合適的值不是一件容易的事情。 之前我們已經介紹了一個自動完成下拉列表的方法,當輸入下拉框的第一個字母。 除了自動完成功能之外,您還可以通過下拉列表搜索以提高在下拉列表中找到合適值的工作效率。 為了使下拉列表可搜索,請按照下面的步驟一步一步教程。

在Excel中創建一個可搜索的下拉列表


在所有打開的工作簿或某些工作表中輕鬆搜索(查找和替換)文本:

點擊 Kutools > 導航 > 查找和替換 快速搜索(查找和替換)所有打開的工作簿或Excel中的某些工作表中的文本或值。 立即下載Kutools for Excel的全功能60天免費試用版!

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 立即下載免費試用版!

Office選項卡在Office中啟用選項卡式編輯和瀏覽,使您的工作更輕鬆......
Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%
  • 重用任何東西: 將最常用或最複雜的公式,圖表和其他任何內容添加到您的收藏夾中,並在將來快速重複使用它們。
  • 超過20文本功能: 從文本字符串中提取數字; 提取或刪除部分文本; 將數字和貨幣轉換為英語單詞...
  • 合併工具:多個工作簿和表格合二為一; 合併多個單元格/行/列而不丟失數據; 合併重複行和總和...
  • 拆分工具:根據價值將數據拆分為多個表格; 一個工作簿到多個Excel,PDF或CSV文件; 一列到多列......
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 創建郵件列表和 通過Cell的價值發送電子郵件...
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過300強大的功能; 與Office 2007-2019和365一起使用; 支持所有語言; 在您的企業或組織中輕鬆部署。

在Excel中創建一個可搜索的下拉列表


例如,下拉列表所需的源數據的範圍是A2:A9。

此方法需要組合框而不是數據驗證下拉列表。 要創建可搜索的下拉列表,請執行以下操作。

1。 如果你找不到 開發人員 請按照以下步驟啟用“開發人員”選項卡。

1)。 在Excel 2010和2013中,單擊 文件 > 選項。 而在中 選項 對話框,單擊 自定義功能區 在右窗格中,檢查 開發人員 框,然後單擊 OK 按鈕。 看截圖:

2)。 在Outlook 2007中,單擊 Office 按鈕> Excel選項。 在 Excel選項 對話框,單擊 流行 在右欄中,然後檢查 在功能區顯示開發人員選項卡 框,最後點擊 OK 按鈕。

2。 展示之後 開發人員 標籤,請點擊 開發人員 > 插入 > 組合框。 看截圖:

3。 在工作表中繪製組合框,然後右鍵單擊它。 選擇 屬性 從右鍵菜單。

4。 在裡面 屬性 對話框中,您需要:

1)。 選擇 AutoWordSelect 領域;

2)。 在中指定一個單元格 LinkedCell 領域。 在這種情況下,我們輸入A12;

3)。 選擇 2-fmMatchEntryNoneMatchEntry 領域;

4)。 類型 下拉列表列表填充範圍 領域;

5)。 關上 屬性 對話框。 看截圖:

5。 現在點擊關閉設計模式 開發人員 > 設計模式.

6。 選擇一個空白單元格C2,然後復制並粘貼公式 = - ISNUMBER(IFERROR(SEARCH($ A $ 12,A2,1),“”)) 進入公式欄,然後按Enter鍵。 他們將其拖放到單元格C9自動填充具有相同公式的選定單元格。 看截圖:

筆記:

1。 $ A $ 12是您在步驟4的LinkedCell字段中指定的單元格;

2。 完成上述步驟後,現在可以對其進行測試。 在下拉框中輸入一個字母C,你會看到所有包含C的單元格填充了數字1。

7。 選擇單元格D2,把公式 = IF(C2 = 1,COUNTIF($ C $ 2:C2,1),“”) 進入公式欄,然後按Enter鍵。 然後將D2中的填充柄向下拖動到D9以填充範圍D3:D9。

8。 選擇單元格E2,複製並粘貼公式 =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"") 進入公式欄並按Enter鍵。 然後將E2中的填充句柄拖到E9以填充單元格。 然後你會看到下面的截圖顯示的單元格填充。

9。 現在你需要創建一個名字範圍。 請點擊 公式 > 定義名稱.

10。 在裡面 新名字 對話框中,鍵入 下拉列表名稱 框中鍵入公式 =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1) 框,然後單擊 OK 按鈕。

11。 現在,通過單擊啟用設計模式 開發人員 > 設計模式。 然後雙擊在步驟3中創建的組合框來打開 Microsoft Visual Basic for Applications 窗口。

12。 將下面的VBA代碼複製並粘貼到代碼編輯器中。

VBA代碼:使下拉列表可搜索

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

13。 關上 Microsoft Visual Basic for Applications 窗口。

從現在起,當你開始在列錶框中輸入時,它將開始模糊搜索,並在下拉列表中列出相關的值。

注意:關閉並重新打開工作表後,您在步驟12中創建的VBA代碼將自動刪除。 所以,您需要將此工作簿保存為Excel宏啟用工作簿格式。


Office Tab - 在Excel中選項卡式瀏覽,編輯和管理工作簿:

Office選項卡將Web瀏覽器(如Google Chrome,Internet Explorer新版本和Firefox)中顯示的選項卡界面帶到Microsoft Excel。 它將是一個節省時間的工具,在您的工作中無可比擬。 見下面的演示:

點擊免費試用Office Tab!

Excel的Office選項卡


相關文章:


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    ismaeel ahmad · 1 months ago
    how to use this dropdown in vba form any konw please reply
  • To post as a guest, your comment is unpublished.
    Jeroen · 1 months ago
    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?
  • To post as a guest, your comment is unpublished.
    Ajesh · 1 months ago
    I have around 80000 data while running excel is hang
  • To post as a guest, your comment is unpublished.
    Sourav Singha · 2 months ago
    Sir How to use this in excel userform combobox....? plz help
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Sourav Singha,
      Can't use it in a userform combobox. Sorry for the inconvenience.
  • To post as a guest, your comment is unpublished.
    Josh · 4 months ago
    Is there a way to make it call up a hyperlink? My email is joshuarobertdaniels@gmail.com
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Josh,
      Sorry can;t help you with that yet.
  • To post as a guest, your comment is unpublished.
    Vrezh · 5 months ago
    I have a problem. My list is in Armenian language, and I see ??????-s instead of the letters. how can I fix this problem? Thank you in advance
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi Vrezh,
      Sorry this kind of problem can't be solved yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Steve Olah · 8 months ago
    How can I use this? I have two problem
    1st I would like use ComboBox1 for a full column, so I have D column, it should see empty.
    When I click into a cell in D column example D7 or D8(etc) I should get a Combo in D7 or D8 etc cell and after select just see the result, not the combo too.

    But how can I add combobox dynamically to D2, D4, D11 etc when click or before.
    I need for I can search with typing too, so simple(not active-x) combo is wrong.

    2nd how set padding? - my combo text when I search is not see whole because itt has padding.

    3th if my source is C column, how drop empty elements from list
  • To post as a guest, your comment is unpublished.
    sigidapurnomo purnomo · 1 years ago
    I had try tutorial drodown list searchable, Some like that,. But i'am can't make searcable from list and Combo Box Search??? How to make VBA Macro Connected in Excel??
  • To post as a guest, your comment is unpublished.
    Mubashir · 1 years ago
    I want to make this drop down to work for whole column, so that with multiple entries, I have this search suggestion option available every time. Above option, just shows suggestion for one time. Please help
  • To post as a guest, your comment is unpublished.
    Min · 1 years ago
    Hi. I get many helps from your post. However, it doesn't make automatic dropdown if there are mixed language on the list (e.g: first cell is written in English, second cell is written in Korean etc.) Has anyone had solve this problem?
  • To post as a guest, your comment is unpublished.
    dan · 1 years ago
    The automatic dropdown list is not working. Everything else is working. Do you know where my snag might lie?
    • To post as a guest, your comment is unpublished.
      dan · 1 years ago
      I figured it my be with the last step. I put the VBA code in my personal.xlsb worksheet but looks like the code needs to be on the sheet of the respective workbook. hazah
  • To post as a guest, your comment is unpublished.
    michael cianci · 1 years ago
    I got this to work but for some reason excel crashes if i attempt to use the arrows to select things from the drop down. has anyone had this issue? is it even supposed to be possible?
    thanks
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Michael,
      The problem you mentioned does not appear in my case. Which Office version do you use?
      • To post as a guest, your comment is unpublished.
        yogi · 1 years ago
        Dear Crystal,

        I got the same problem as michael does, excel crashes every time i use down arrow in the drop box, i got excel 2010 on my laptop, which version do you use?
        • To post as a guest, your comment is unpublished.
          crystal · 1 years ago
          Hi yogi,
          The method has been successfully tested in Office 2010, 2013 as well as 2016. No idea for this problem. Sorry about that.
  • To post as a guest, your comment is unpublished.
    alluxxx · 1 years ago
    Is there a way to prioritize the location of a letter in a word? I used this method, but when I type in "A," for example, I get terms with "A" anywhere in the word. I would prefer if it started showing all the terms that begin with "A". Is this possible?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Sorry for reply so late. If you want to search values in drop-down list that begin with a certain character, please change the formula in column C to
      =--ISNUMBER(IFERROR(SEARCH($A$12,MID(A3,1,1),1),"")).
      Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    KATHLEEN · 1 years ago
    I may have misunderstood how this function is supposed to work, but i can only get the combo box search to populate one cell, A12 (using the example from the tutorial). If i click into A13 to populate the next cell with a different value from the drop down, it just replaces what i have in A12 and does not populate A13. I need this search to apply to any cell in column A from A12 down. Have i done something incorrect or does this combo box search only allow a single cell in the workbook to be populated with the result? Will be grateful for any help with this.
  • To post as a guest, your comment is unpublished.
    Kathleen · 1 years ago
    I may have misunderstood how this function is supposed to work, but i can only get the combo box search to populate one cell, A12 (using the example from the tutorial). If i click into A13 to populate the next cell with a different value from the drop down, it just replaces what i have in A12 and does not populate A13. I need this search to apply to any cell in column A from A12 down. Have i done something incorrect or does this combo box search only allow a single cell in the workbook to be populated with the result? Will be grateful for any help with this.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      This combo box search only allow a single cell in the workbook to be populated with the result.
      I'll try to find another method to solve your problem.
  • To post as a guest, your comment is unpublished.
    Ben Johnston · 1 years ago
    I feel dumb, but immediately after posting, I realized I probably hadn't added the 1 to DropDownList1 in the VBA, and sure enough that was the problem! Thanks anyway!
  • To post as a guest, your comment is unpublished.
    Ben Johnston · 1 years ago
    Hello, thanks for the tutorial! I'm having an issue where every time I type in the combo box, "DropDownList1" disappears from the "ListFillRange" property. So long as I don't type in the box, if I retype "DropDownList1" in the property, the box does show suggestions. I have looked everything over and could not find any errors. Is this a common problem, and is there a way to fix it? Thank you for your time!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Ben,
      I am also comfusing about the disappearing of the "DripDownList" from the "ListFillRange" property
      But it does not influence the finally rsult of making the drop-down list seachable.
  • To post as a guest, your comment is unpublished.
    dave · 2 years ago
    is there a way to have the search box put the top result if left blank? in the case of this example it would automatically put china if it was left blank
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear dave,
      Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do?
  • To post as a guest, your comment is unpublished.
    Al B · 2 years ago
    I've had an ongoing issue with all documents I've used this method on. A shadow of the drop-down box reappears underneath it each time I click into another cell within the spreadsheet and begin typing. It's beyond just a nuisance because when the shadow drops down, it prevents use of any additional searchable drop-down boxes. Please help!!! This is affecting multiple documents we use throughout our organization.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      Sorry for replying so late. The problem you methoded does not appear in my case.Would be nice if you could provide your Office verson. Thank you!
  • To post as a guest, your comment is unpublished.
    Gunawan Budianto · 2 years ago
    4. In the Properties dialog box, you need to:
    1). Select False in the AutoWordSelect field;
    2). Specify a cell in the LinkedCell field. In this case, we enter A12;

    Why A12? thank's
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi,
      This cell is optionally selected which can help to finish the whole operation. You can choose any one as you need.
  • To post as a guest, your comment is unpublished.
    Jelbin · 2 years ago
    Hi As in forum,
    I need to have this searchable dropdown for columns 2 to 500. Please let me know how i can as the second combo replicates the same in first which i dont want
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Jelbin,
      Can't handle this. Sorry about that.
  • To post as a guest, your comment is unpublished.
    Havocknox · 2 years ago
    Thank you for this breakdown to make the combo box searchable. I have even gotten three of them working on the same page. My problem I have run into is when I start typing in the search information and the info narrows down, if I hit the down arrow key to select the item in the list Excel crashes on me. Has anyone had this happen, and if so have you found a way to solve this issue.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi,
      The problemm you mentioned does not appear in my case. Would you please provide your Office version?
  • To post as a guest, your comment is unpublished.
    Heric · 2 years ago
    Hi,

    your guide is most helpful, but i still encounter one last problem.
    I am trying to do a simple invoice, and do the drop down for my customer name cell, must my customer listing be in the same worksheet as my invoice worksheet? Is is possible i have two worksheet, "invoice" & "customer name", and do the drop down list for customer name at "invoice" worksheet?

    Thank you
  • To post as a guest, your comment is unpublished.
    Jaydie · 2 years ago
    Thank you, I used above and it works perfectly....

    Until you have two combo boxes in one sheet.. When you want to type in the second combo box it highlights the text in the first combo box and does not want to search
    If I leave the first box blank, the second box works fine

    Please help
  • To post as a guest, your comment is unpublished.
    NAJMA · 2 years ago
    plz help me
    i cannt enter formula in formula bar
    when i paste this formula & paste this =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    give me error.type :(
  • To post as a guest, your comment is unpublished.
    Ashok · 2 years ago
    HI, How to do the same searchable program for contnious rwo , i tried and it is working one row only , i want to do the same for below row also for different name
  • To post as a guest, your comment is unpublished.
    Ahmed Shahin · 2 years ago
    Hi Herb,

    What if i created a drop down list from another work sheet? the formula " =--ISNUMBER(IFERROR(SEARCH($A$2,H2,1),""))" has wrong reference and when i edit it it doesn't allow to put the right cell. what do you suggest? thank you
  • To post as a guest, your comment is unpublished.
    Yesenia · 3 years ago
    I, like Cristina above, would also like to know how to make multiple combo boxes for one sheet. I tried but when I begin typing in the second combobox two things happen: 1. no drop down list appears, and 2. the simple act of typing in combobox2 activates the selection from my original combobox1 and highlights it in the drop down from combobox1. I checked to make sure all of my coding says combobox2 for combobox2 etc. for the other boxes but there is a disconnect that I can't figure out.
    • To post as a guest, your comment is unpublished.
      Jaydie · 2 years ago
      I have the exact same problem, have you managed a solution yet??
  • To post as a guest, your comment is unpublished.
    FAUZI · 3 years ago
    Thank You.. Very helpfull.. God Bless You
  • To post as a guest, your comment is unpublished.
    Maarten · 3 years ago
    Hi,

    I can't fill in 'DropDownList' in the 'ListFillRange'.... What's the catch? I don't understand the solution of imad.
    Thanks.
    • To post as a guest, your comment is unpublished.
      Herb123987 · 3 years ago
      [quote name="Maarten"]Hi,

      I can't fill in 'DropDownList' in the 'ListFillRange'.... What's the catch? I don't understand the solution of imad.
      Thanks.[/quote]

      I posted this answer above for IMAD and saw this posting down here for MAARTEN so I figured I'd post this for him too.

      I have seen this "how to make an autofill / auto suggest DDL / combo box" on a few different sites and they ALL want you to put "something" in the ListFillRange Properties field [b]BEFORE[/b] they have you [b]create a named range[/b] by clicking Formula > Define Name ....... and the [b]ListFillRange will always go blank in the Properties window[/b] UNTIL you define the name (Formula > Define Name)

      THAT is why i think IMAD, above and MAARTEN below (here) was having the problem - not 100% sure though.
      • To post as a guest, your comment is unpublished.
        Maarten · 3 years ago
        Hi there,

        Thanks a lot for your solution. I gave up already, but I'll try again.
    • To post as a guest, your comment is unpublished.
      Andone · 3 years ago
      try to put this=--ISNUMBER(IFERROR(SEARCH($A$12,$A$2,1),"")) instead =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),"")) in step 6
  • To post as a guest, your comment is unpublished.
    imad · 3 years ago
    So I Finally got it to work! I attached the linkedcell to a vlookup and got all the information pulling into a row. I was wondering if there could be any extension on the vba to actually filter the table as we type?
  • To post as a guest, your comment is unpublished.
    imad · 3 years ago
    Mine isn't working. My dropdownlist label was not working in the "properties" for the combobox. Everytime I entered it, it disappeared. So I used "test" instead. I adjusted the macro with the word test instead of dropdowmlist. Let me know if there is something else I can do? Search not working.
    • To post as a guest, your comment is unpublished.
      Herb123987 · 3 years ago
      [quote name="imad"]Mine isn't working. My dropdownlist label was not working in the "properties" for the combobox. Everytime I entered it, it disappeared. So I used "test" instead. I adjusted the macro with the word test instead of dropdowmlist. Let me know if there is something else I can do? Search not working.[/quote]

      I have seen this "how to make an autofill / auto suggest DDL / combo box" on a few different sites and they ALL want you to put "something" in the ListFillRange field BEFORE they have you create a name range by clicking Formula > Define Name and the ListFillRange will always go blank in the Properties window UNTIL you define the name (Formula > Define Name)

      THAT is why i think IMAD, above and MAARTEN below was having the problem - not 100% sure though.
  • To post as a guest, your comment is unpublished.
    MarkC · 4 years ago
    For some reason when I click a selection from the drop down list after typing a few characters the drop down main value becomes blank... any idea why this would happen and how to get it to stop?

    I have a command button that I want to click to then put the selection into the next available cell in a given range, but again the value blanks out when I click on it.
    • To post as a guest, your comment is unpublished.
      imad · 3 years ago
      I have the exact same problem. I did everything right but the dropdownlist label just goes blank everytime I press enter. If you figured it out, please do share!
  • To post as a guest, your comment is unpublished.
    Cristina · 4 years ago
    Excellent post. Could you please explain how do you copy the same drop down list to multiple cells. I want to create an expense report and I want to be able to select a different expense on each row from the same drop down list. Thank you.
  • To post as a guest, your comment is unpublished.
    Prastuti · 4 years ago
    very nicely explained. Loved it. Thank you !!