Přejít k hlavnímu obsahu

Dokonalý průvodce rozbalovacím seznamem s možností vyhledávání v Excelu

Vytváření rozevíracích seznamů v Excelu zjednodušuje zadávání dat a minimalizuje chyby. Ale s většími datovými sadami se procházení dlouhými seznamy stává těžkopádným. Nebylo by jednodušší jen napsat a rychle najít vaši položku? A "prohledávatelný rozevírací seznam" tuto vymoženost nabízí. Tato příručka vás provede čtyřmi způsoby, jak takový seznam v Excelu nastavit.


Video


Prohledávatelný rozevírací seznam v Excelu 365

Excel 365 zavedl do svých rozevíracích seznamů ověřování dat velmi očekávanou funkci: možnost vyhledávat v seznamu. S funkcí vyhledávání mohou uživatelé rychle najít a vybrat položky efektivněji. Po vložení rozevíracího seznamu jako obvykle stačí kliknout na buňku s rozevíracím seznamem a začít psát. Seznam se okamžitě vyfiltruje, aby odpovídal zadanému textu.

V tomto případě píšu San v buňce a rozevírací seznam odfiltruje města, která začínají hledaným výrazem San, Jako San Francisco a San Diego. Poté můžete vybrat výsledek pomocí myši nebo pomocí kláves se šipkami a stisknout Enter.

Poznámky:
  • Projekt vyhledávání je zahájeno od prvního písmene každého slova v rozevíracím seznamu. Pokud zadáte znak, který neodpovídá počátečnímu znaku žádného slova, seznam nezobrazí odpovídající položky.
  • Tato funkce je k dispozici pouze v nejnovější verzi Excelu 365.
  • Pokud vaše verze Excelu tuto funkci nepodporuje, zde doporučujeme Prohledávatelný rozevírací seznam vlastnost Kutools pro Excel. Neexistuje žádné omezení verze Excelu a po aktivaci můžete snadno vyhledat požadovanou položku v rozevíracím seznamu pouhým zadáním příslušného textu. Podívejte se na podrobné kroky.

Vytvořit prohledávatelný rozevírací seznam (pro Excel 2019 a novější)

Pokud používáte Excel 2019 nebo novější verze, metodu v této části lze také použít k tomu, aby bylo možné prohledávat rozevírací seznam v Excelu.

Za předpokladu, že jste vytvořili rozevírací seznam v buňce A2 Listu2 (obrázek vpravo) pomocí dat v rozsahu A2:A8 Listu1 (obrázek vlevo), postupujte podle těchto kroků, aby bylo možné v seznamu vyhledávat.

Krok 1. Vytvořte pomocný sloupec se seznamem hledaných položek

Zde potřebujeme pomocný sloupec pro seznam položek, které odpovídají vašim zdrojovým datům. V tomto případě vytvořím pomocný sloupec v sloupec D of Sheet1.

  1. Vyberte první buňku D1 ve sloupci D a zadejte záhlaví sloupce, například "Výsledky vyhledávání" v tomto případě.
  2. Do buňky D2 zadejte následující vzorec a stiskněte vstoupit.
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
Poznámky:
  • V tomto vzorci, A2: A8 je rozsah zdrojových dat. List2!A2 je umístění rozevíracího seznamu, což znamená, že rozevírací seznam je umístěn v A2 listu 2. Změňte je prosím podle vlastních údajů.
  • Pokud není vybrána žádná položka z rozevíracího seznamu v A2 listu 2, vzorec zobrazí všechny položky ze zdrojových dat, jak je znázorněno na obrázku výše. Naopak, pokud je vybrána položka, D2 zobrazí tuto položku jako výsledek vzorce.
Krok 2: Překonfigurujte rozevírací seznam
  1. Vyberte buňku rozevíracího seznamu (v tomto případě vyberu buňku A2 List2) a poté přejděte k výběru Data > Ověření dat > Ověření dat.
  2. v Ověření dat dialogovém okně, musíte nakonfigurovat následovně.
    1. Pod Nastavení klepněte na kartu tlačítko v Zdroj box.
    2. Projekt Ověření dat dialogové okno se přesměruje na List1, vyberte buňku (např. D2) se vzorcem z kroku 1, přidejte # symbol a klikněte na zavřít .
    3. Přejít na Chybové upozornění , zrušte zaškrtnutí políčka Po zadání neplatných údajů zobrazit chybové hlášení zaškrtávací políčko a nakonec klikněte na OK pro uložení změn.
Výsledek

V rozevíracím seznamu v buňce A2 List2 je nyní možné vyhledávat. Zadejte text do buňky, kliknutím na rozevírací šipku rozbalte rozevírací seznam a uvidíte, že seznam bude okamžitě filtrován tak, aby odpovídal napsanému textu.

Poznámky:
  • Tato metoda je k dispozici pouze pro Excel 2019 a novější verze.
  • Tato metoda funguje vždy pouze na jedné buňce rozevíracího seznamu. Aby bylo možné prohledávat rozevírací seznamy v buňkách A3 až A8 v Listu2, musí se výše uvedené kroky opakovat pro každou buňku.
  • Když zadáte text do buňky rozevíracího seznamu, rozevírací seznam se nerozbalí automaticky, musíte kliknout na šipku rozevíracího seznamu, abyste jej rozbalili ručně.

Snadno vytvořte prohledávatelný rozevírací seznam (pro všechny verze Excelu)

Vzhledem k různým omezením výše uvedených metod je zde pro vás velmi účinný nástroj - Kutools pro Excel's Prohledávatelný rozevírací seznam Vlastnosti. Tato funkce je dostupná ve všech verzích aplikace Excel a funguje nejen pro všechny rozevírací seznamy v zadaném rozsahu, ale také pro všechny rozevírací seznamy v aktuálním listu nebo aktuálním sešitu, což vám umožňuje snadno vyhledávat požadované položky v rozevírací seznamy.

Po stažení a instalace Kutools pro Excelvyberte Kutools > Rozbalovací seznam > Prohledávatelný rozevírací seznam pro aktivaci této funkce. V Prohledávatelný rozevírací seznam dialogové okno, musíte:

  1. Zadejte požadovanou možnost v Nastavit rozsah rozbalovací seznam.
    V tomto případě potřebuji, aby bylo možné prohledávat rozevírací seznam v určeném rozsahu, takže vyberu Určete rozsah možnost a vyberte rozsah.
  2. klikněte OK.
Výsledek

Když klepnete na buňku rozevíracího seznamu v zadaném rozsahu, zobrazí se vpravo pole se seznamem. Chcete-li seznam okamžitě filtrovat, zadejte text, poté vyberte položku nebo pomocí kláves se šipkami a stisknutím klávesy Enter ji přidejte do buňky.

Poznámky:
  • Tato funkce nabízí tři užitečné možnosti:
    • Určete rozsah: Pokud vyberete tuto možnost a určíte rozsah buněk, bude možné prohledávat pouze rozevírací seznamy v tomto určeném rozsahu.
    • Aktuální sešit: Pokud vyberete tuto možnost, bude možné prohledávat všechny rozevírací seznamy v aktuálním sešitu.
    • Aktuální list: Pokud vyberete tuto možnost, bude možné prohledávat pouze rozevírací seznamy v aktuálním listu.
  • Tato funkce podporuje vyhledávání z libovolné pozice ve slovech. To znamená, že i když zadáte znak, který je uprostřed nebo na konci slova, odpovídající položky budou stále nalezeny a zobrazeny, což nabízí komplexnější a uživatelsky přívětivější vyhledávání.
  • Chcete-li se o této funkci dozvědět více, prosím navštívit tuto stránku.
  • Chcete-li použít tuto funkci, prosím stáhnout a nainstalovat Kutools pro Excel jako první.

Vytvořte prohledávatelný rozevírací seznam pomocí Combo boxu a VBA (složitější)

Pokud chcete jednoduše vytvořit prohledávatelný rozevírací seznam bez zadání konkrétního typu rozevíracího seznamu. Tato část poskytuje alternativní přístup: použití Combo boxu s kódem VBA k dosažení úkolu.

Předpokládejme, že máte ve sloupci A seznam názvů zemí, jak je znázorněno na snímku obrazovky níže, a nyní je chcete použít jako zdrojová data rozbalovacích seznamů vyhledávání, můžete to provést následovně.

Namísto rozevíracího seznamu ověření dat do listu musíte vložit pole Combo.

  1. V případě, že Vývojka karta se na pásu karet nezobrazuje, můžete povolit Vývojka záložka následovně.
    1. V aplikaci Excel 2010 nebo novějších verzích klepněte na Soubor > možnosti. A v Možnosti aplikace Excel , klepněte na tlačítko OK Přizpůsobit pás karet v levém panelu. Přejděte do seznamu Přizpůsobit pás karet a zaškrtněte políčko Vývojka a poté klikněte na OK knoflík. Viz screenshot:
    2. V aplikaci Excel 2007 klikněte na Office tlačítko> Možnosti aplikace Excel. V Možnosti aplikace Excel , klepněte na tlačítko OK Oblíbené v levém podokně zkontrolujte Zobrazit kartu Vývojář na pásu karet a nakonec klikněte na OK .
  2. Po předvedení Vývojka klepněte na kartu Vývojka > Vložit > Rozbalovací seznam.
  3. Nakreslete pole Combo v listu, klikněte na něj pravým tlačítkem a vyberte Nemovitosti z nabídky pravého tlačítka myši.
  4. v Nemovitosti dialogové okno, musíte:
    1. vybrat Falešný v AutoWordSelect pole;
    2. Zadejte buňku v Propojená buňka pole. V tomto případě zadáme A12;
    3. vybrat 2-fmMatchEntryNone v MatchEntry pole;
    4. Styl Rozbalovací seznam do ListFillRange pole;
    5. Zavři Nemovitosti dialogové okno. Viz snímek obrazovky:
  5. Nyní kliknutím vypněte režim návrhu Vývojka > Režim návrhu.
  6. Vyberte prázdnou buňku, například C2, zadejte níže uvedený vzorec a stiskněte vstoupit. Přetáhnou jeho rukojeť automatického vyplňování dolů do buňky C9, aby se buňky automaticky vyplnily stejným vzorcem. Viz snímek obrazovky:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    Poznámky:
    1. $ A $ 12 je buňka, kterou jste zadali jako Propojená buňka v kroku 4;
    2. Po dokončení výše uvedených kroků můžete nyní testovat: do pole se seznamem zadejte písmeno C a pak uvidíte, že buňky vzorce, které odkazují na buňky obsahující znak C, jsou vyplněny číslem 1.
  7. Vyberte buňku D2, zadejte níže uvedený vzorec a stiskněte vstoupit. Potom přetáhněte jeho úchyt automatického vyplňování dolů do buňky D9.
    =IF(C2=1,COUNTIF($C$2:C2,1),"")
  8. Vyberte buňku E2, zadejte níže uvedený vzorec a stiskněte vstoupit. Potom přetáhněte jeho rukojeť automatického vyplňování dolů na E9 a použijte stejný vzorec.
    =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
  9. Nyní musíte vytvořit rozsah jmen. prosím klikněte Vzorec > Definujte jméno.
  10. v Nové jméno dialogové okno, zadejte Rozbalovací seznamPříjmení zadejte níže uvedený vzorec do pole Odkazuje na a poté klikněte na OK .
    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    
  11. Nyní kliknutím zapněte režim návrhu Vývojka > Režim návrhu. Poté dvojitým kliknutím na Combo box otevřete Microsoft Visual Basic pro aplikace okno.
  12. Zkopírujte a vložte níže uvedený kód VBA do editoru kódu.
    Kód VBA: umožňuje vyhledávat v rozevíracím seznamu
    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub
  13. Stiskněte Další + Q klávesy pro zavření Microsoft Visual Basic pro aplikace okno.

Od této chvíle, když je znak zadán do pole se seznamem, provede fuzzy vyhledávání a poté vypíše příslušné hodnoty v seznamu.

Poznámka: Tento sešit musíte uložit jako soubor sešitu Excel s podporou maker, abyste si uchovali kód VBA pro budoucí použití.

Nejlepší kancelářské nástroje produktivity

Kutools pro Excel - pomůže vám vyniknout před davem

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...


Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)

  • Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
  • Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
  • Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a 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?
This comment was minimized by the moderator on the site
I have around 80000 data while running excel is hang
This comment was minimized by the moderator on the site
Sir How to use this in excel userform combobox....? plz help
This comment was minimized by the moderator on the site
Hi Sourav Singha,
Can't use it in a userform combobox. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Is there a way to make it call up a hyperlink? My email is
This comment was minimized by the moderator on the site
Hi Josh,
Sorry can;t help you with that yet.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Hi Vrezh,
Sorry this kind of problem can't be solved yet. Thank you for your comment.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Hi Steve Olah,
Sorry can't help you with that. Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations