Vytvořte rozevírací seznam aplikace Excel s návrhy vyhledávání

Všichni používáme Google jako součást naší každodenní rutiny. Jednou z jeho funkcí je návrh vyhledávání, kdy Google jedná chytře a při psaní nám dává seznam návrhů.

V tomto kurzu se naučíte, jak v aplikaci Excel vytvořit rozevírací seznam, který lze prohledávat-tj. Rozevírací seznam, který bude při psaní zobrazovat odpovídající položky.

Níže je video z tohoto tutoriálu (v případě, že dáváte přednost sledování videa před čtením textu).

Prohledávatelný rozevírací seznam v aplikaci Excel

Pro účely tohoto tutoriálu používám data z 20 nejlepších zemí podle HDP.

Záměrem je vytvořit rozevírací seznam aplikace Excel s mechanismem návrhu hledání, aby při zadávání do vyhledávacího pole zobrazoval rozevírací seznam s možnostmi shody.

Něco, jak je uvedeno níže:

Chcete -li pokračovat, stáhněte si ukázkový soubor odtud

Vytvoření rozevíracího seznamu prohledávatelného v aplikaci Excel by bylo třídílným procesem:

1. Konfigurace vyhledávacího pole.
2. Nastavení dat.
3. Napište krátký kód VBA, aby fungoval.

Krok 1 - Konfigurace vyhledávacího pole

V tomto prvním kroku použiji pole se seznamem a nakonfiguruji ho tak, aby se při psaní text v buňce zobrazoval také v reálném čase.

Zde jsou kroky, jak toho dosáhnout:

1. Přejděte na kartu Vývojář -> Vložit -> Ovládací prvky ActiveX -> Kombinované pole (Ovládací prvek ActiveX).
   • Je možné, že na pásu karet kartu vývojáře nenajdete. Ve výchozím nastavení je skrytý a musí být povolen. Kliknutím sem zjistíte, jak získat kartu vývojáře na pásu karet v aplikaci Excel.
2. Přesuňte kurzor do oblasti listu a klikněte kamkoli. Vloží pole se seznamem.
3. Klepněte pravým tlačítkem na pole se seznamem a vyberte Vlastnosti.
4. V dialogovém okně vlastností proveďte následující změny:
   • Vyberte AutoWord: Nepravdivé
   • LinkedCell: B3
   • ListFillRange: DropDownList (v kroku 2 vytvoříme pojmenovaný rozsah s tímto názvem)
   • MatchEntry: 2 - fmMatchEntryNone

(Buňka B3 je propojena se seznamem se seznamem, což znamená, že vše, co do pole se seznamem zadáte, se zadá do pole B3)

4. Přejděte na kartu Vývojář a klikněte na Režim návrhu. To vám umožní zadat text do pole se seznamem. Protože je buňka B3 propojena se seznamem, jakýkoli text, který zadáte do pole se seznamem, se také projeví v B3 v reálném čase.

Krok 2 - Nastavení dat

Nyní, když je vyhledávací pole nastaveno, potřebujeme dostat data na místo. Jde o to, že jakmile cokoli napíšete do vyhledávacího pole, zobrazí se pouze ty položky, které v sobě mají daný text.

K tomu použijeme

• Tři pomocné sloupy.
• Jeden dynamický pojmenovaný rozsah.

Pomocný sloupec 1

Vložte následující vzorec do buňky F3 a přetáhněte ji pro celý sloupec (F3: F22)

=-ISNUMBER (IFERROR (HLEDAT ($ B $ 3, E3,1), ""))

Tento vzorec vrátí 1, když je text v poli se seznamem v názvu země vlevo. Pokud například zadáte UNI, pak pouze hodnoty pro Unistáty a Unikrálovství království je 1 a všechny zbývající hodnoty jsou 0.

Sloupec pomocníka 2

Vložte následující vzorec do buňky G3 a přetáhněte ji pro celý sloupec (G3: G22)

= IF (F3 = 1, COUNTIF ($ F $ 3: F3,1), "") 

Tento vzorec vrátí 1 pro první výskyt, kde text pole se seznamem odpovídá názvu země, 2 pro druhý výskyt, 3 pro třetí atd. Pokud například zadáte UNI, buňka G3 zobrazí 1, protože odpovídá Spojeným státům, a G9 zobrazí 2, protože odpovídá Spojenému království. Zbytek buněk bude prázdný.

Sloupec pomocníka 3

Vložte následující vzorec do buňky H3 a přetáhněte ji pro celý sloupec (H3: H22)

= IFERROR (INDEX ($ E $ 3: $ E $ 22, MATCH (ROWS ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "") 

Tento vzorec shromažďuje všechna odpovídající jména dohromady bez mezer mezi nimi. Pokud například zadáte UNI, tento sloupec bude zobrazovat 2 a 9 společně a ostatní buňky budou prázdné.

Vytvoření dynamického pojmenovaného rozsahu

Nyní, když jsou pomocné sloupce na místě, musíme vytvořit dynamický pojmenovaný rozsah. Tento pojmenovaný rozsah bude odkazovat pouze na ty hodnoty, které odpovídají textu zadanému do pole se seznamem. Tento dynamický pojmenovaný rozsah použijeme k zobrazení hodnot v rozevíracím seznamu.

Poznámka: V kroku 1 jsme do možnosti ListFillRange zadali DropDownList. Nyní vytvoříme pojmenovaný rozsah se stejným názvem.

Zde jsou kroky k jeho vytvoření:

1. Přejděte na Vzorce -> Správce jmen.
2. V dialogovém okně Správce názvů klikněte na Nový. Otevře se dialogové okno Nové jméno.
3. Do pole Název zadejte DropDownList
4. Do pole Odkazuje na zadejte vzorec: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Krok 3 - Uvedení kódu VBA do provozu

Už tam skoro jsme.

Poslední částí je napsat krátký kód VBA. Tento kód činí rozevírací seznam dynamickým, takže zobrazuje odpovídající položky/názvy při psaní do vyhledávacího pole.

Chcete -li přidat tento kód do sešitu:

1. Klikněte pravým tlačítkem na kartu sešit a vyberte Zobrazit kód.
2. V okně VBA zkopírujte a vložte následující kód:

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

A je to!!

Všichni máte nastavený vlastní vyhledávací panel typu Google, který zobrazuje odpovídající položky při psaní.

Pro lepší vzhled a dojem můžete buňku B3 zakrýt pomocí pole se seznamem a skrýt všechny pomocné sloupce. Nyní se můžete s tímto úžasným trikem v Excelu trochu pochlubit.

Chcete -li pokračovat, stáhněte si soubor odtud

Co myslíš? Dokázali byste ve své práci použít tento rozevírací seznam návrhů vyhledávání? Sdělte mi své myšlenky zanecháním komentáře.

Pokud se vám tento návod líbil, jsem si jistý, že by se vám líbily i následující tutoriály k Excelu:

• Dynamický filtr - extrahujte odpovídající data během psaní.
• Extrahujte data na základě výběru z rozevíracího seznamu.
• Vytváření závislých rozevíracích seznamů v aplikaci Excel.
• The Ultimate Guide to using Excel VLOOKUP Function.
• Jak provést více výběrů v rozevíracím seznamu v aplikaci Excel.
• Jak vložit a používat zaškrtávací políčko v aplikaci Excel.