✅ Vytvorte rozbaľovací zoznam programu Excel s návrhmi na vyhľadávanie

Obsah

Prehľadateľný rozbaľovací zoznam v programe Excel

Všetci používame Google ako súčasť našej každodennej rutiny. Jednou z jeho funkcií je návrh vyhľadávania, kde Google jedná múdro a počas písania nám dáva zoznam návrhov.

V tomto návode sa naučíte, ako vytvoriť v programe Excel rozbaľovací zoznam, v ktorom sa dá vyhľadávať-t. J. Rozbaľovací zoznam, ktorý bude pri zadávaní zobrazovať zodpovedajúce položky.

Nasleduje video s týmto návodom (v prípade, že dávate prednosť sledovaniu videa pred čítaním textu).

Prehľadateľný rozbaľovací zoznam v programe Excel

Na účely tohto tutoriálu používam údaje z 20 najlepších krajín podľa HDP.

Cieľom je vytvoriť rozbaľovací zoznam programu Excel s mechanizmom navrhovania vyhľadávania tak, aby pri zadávaní textu do vyhľadávacieho panela zobrazoval rozbaľovací zoznam s možnosťami zhody.

Niečo, ako je uvedené nižšie:

Ak chcete pokračovať, stiahnite si tu ukážkový súbor

Vytvorenie rozbaľovacieho zoznamu, v ktorom sa dá vyhľadávať, v programe Excel bude pozostávať z troch častí:

Konfigurácia vyhľadávacieho poľa.
Nastavenie údajov.
Napíšte krátky kód VBA, aby fungoval.

Krok 1 - Konfigurácia vyhľadávacieho poľa

V tomto prvom kroku použijem kombinované pole a nakonfigurujem ho tak, aby sa pri písaní text v bunke zobrazoval aj v reálnom čase.

Tu sú kroky, ako to urobiť:

Prejdite na kartu Vývojár -> Vložiť -> Ovládacie prvky ActiveX -> Kombinované pole (Ovládací prvok ActiveX).
- Je možné, že kartu vývojára na páse s nástrojmi nenájdete. Štandardne je skrytý a musí byť povolený. Kliknutím sem získate informácie o tom, ako získať kartu vývojára na páse s nástrojmi v programe Excel.
Presuňte kurzor na oblasť pracovného hárka a kliknite kdekoľvek. Vloží kombinované pole.
Kliknite pravým tlačidlom myši na pole so zoznamom a vyberte položku Vlastnosti.
V dialógovom okne vlastností vykonajte nasledujúce zmeny:
- AutoWordSelect: Falošné
- LinkedCell: B3
- ListFillRange: DropDownList (v kroku 2 vytvoríme pomenovaný rozsah s týmto názvom)
- MatchEntry: 2 - fmMatchEntryNone

(Bunka B3 je prepojená so kombinovaným poľom, čo znamená, že všetko, čo do poľa so zoznamom zadáte, sa zadá do B3)

Prejdite na kartu Vývojár a kliknite na Režim návrhu. To vám umožní zadávať text do kombinovaného poľa. Pretože je bunka B3 prepojená so zoznamom, akýkoľvek text, ktorý zadáte do poľa so zoznamom, sa v reálnom čase prejaví aj v B3.

Krok 2 - Nastavenie údajov

Teraz, keď je vyhľadávacie pole nastavené, musíme údaje zadať. Ide o to, že akonáhle do vyhľadávacieho poľa niečo napíšete, zobrazí sa iba tie položky, ktoré obsahujú daný text.

Na to použijeme

Tri pomocné stĺpce.
Jeden dynamický pomenovaný rozsah.

Pomocník, stĺpec 1

Do bunky F3 vložte nasledujúci vzorec a presuňte ho do celého stĺpca (F3: F22)

=-ISNUMBER (IFERROR (VYHĽADÁVANIE ($ B $ 3, E3,1), ""))

Tento vzorec vráti hodnotu 1, keď je text v poli so zoznamom v názve krajiny vľavo. Ak napríklad zadáte UNI, potom iba hodnoty pre Uništátov a UniKráľovstvo je 1 a všetky zostávajúce hodnoty sú 0.

Pomocník, stĺpec 2

Do bunky G3 vložte nasledujúci vzorec a presuňte ho do celého stĺpca (G3: G22)

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

Tento vzorec vráti 1 pre prvý výskyt, kde sa text v poli so zoznamom zhoduje s názvom krajiny, 2 pre druhý výskyt, 3 pre tretí a podobne. Ak napríklad zadáte UNI, bunka G3 zobrazí 1, pretože sa zhoduje s USA, a G9 zobrazí 2, ako zodpovedá Spojenému kráľovstvu. Ostatné bunky budú prázdne.

Pomocník, stĺpec 3

Do bunky H3 vložte nasledujúci vzorec a presuňte ho do celého stĺpca (H3: H22)

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

Tento vzorec zoskupuje všetky zodpovedajúce názvy bez prázdnych buniek medzi nimi. Ak napríklad zadáte UNI, v tomto stĺpci sa zobrazia 2 a 9 spoločne a ostatné bunky budú prázdne.

Vytvorenie dynamického pomenovaného rozsahu

Teraz, keď sú pomocné stĺpce na mieste, musíme vytvoriť dynamický pomenovaný rozsah. Tento pomenovaný rozsah bude odkazovať iba na hodnoty, ktoré sa zhodujú s textom zadaným do poľa so zoznamom. Tento dynamický pomenovaný rozsah použijeme na zobrazenie hodnôt v rozbaľovacom poli.

Poznámka: V kroku 1 sme zadali DropDownList do možnosti ListFillRange. Teraz vytvoríme pomenovaný rozsah s rovnakým názvom.

Tu sú kroky na jeho vytvorenie:

Prejdite na Vzorce -> Správca mien.
V dialógovom okne Správca mien kliknite na položku Nový. Otvorí sa dialógové okno Nové meno.
Do poľa Názov zadajte DropDownList
Do poľa Odkaz na pole zadajte vzorec: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Krok 3 - Uvedenie kódu VBA do prevádzky

Už sme skoro tam.

Posledná časť je napísať krátky kód VBA. Tento kód robí rozbaľovaciu ponuku dynamickou tak, že zobrazuje zodpovedajúce položky/názvy pri písaní do vyhľadávacieho poľa.

Ak chcete pridať tento kód do zošita:

Pravým tlačidlom myši kliknite na kartu Pracovný list a zvoľte Zobraziť kód.

V okne VBA skopírujte a prilepte nasledujúci kód:

Súkromný pod ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Koniec pod

To je ono !!

Všetci máte nastavený vlastný vyhľadávací panel typu Google, ktorý zobrazuje zodpovedajúce položky počas písania.

Pre lepší vzhľad a dojem môžete bunku B3 zakryť kombinovaným boxom a skryť všetky pomocné stĺpce. Teraz sa môžete trochu predviesť s týmto úžasným trikom v Exceli.

Ak chcete pokračovať, stiahnite si súbor odtiaľto

Co si myslis? Vedeli by ste tento rozbaľovací zoznam návrhov na vyhľadávanie použiť vo svojej práci? Dajte mi vedieť svoje myšlienky zanechaním komentára.

Ak sa vám tento návod páčil, som si istý, že by ste chceli aj nasledujúce návody pre Excel: