Ustvarite spustni seznam Excel s predlogi za iskanje

Vsi uporabljamo Google kot del svoje vsakodnevne rutine. Ena od njegovih značilnosti je predlog za iskanje, pri katerem Google deluje pametno in nam med tipkanjem poda seznam predlogov.

V tej vadnici se boste naučili, kako ustvariti spustni seznam, ki ga je mogoče iskati, v Excelu-to je spustni seznam, ki bo med tipkanjem prikazal ujemajoče se elemente.

Spodaj je videoposnetek te vadnice (če si želite ogledati videoposnetek, ne pa brati besedilo).

Spustni seznam, ki ga je mogoče iskati v Excelu

Za namene te vadnice uporabljam podatke 20 najboljših držav po BDP.

Namen je ustvariti spustni seznam excel z mehanizmom za iskanje, tako da prikaže spustni meni z možnostmi ujemanja, ko vtipkam v iskalno vrstico.

Nekaj, kot je prikazano spodaj:

Če želite nadaljevati, prenesite vzorčno datoteko od tukaj

Ustvarjanje spustnega seznama za iskanje v Excelu bi bil tridelni postopek:

1. Konfiguriranje iskalnega polja.
2. Nastavitev podatkov.
3. Pisanje kratke kode VBA, da bo delovalo.

1. korak - Konfiguriranje iskalnega polja

V tem prvem koraku bom uporabil kombinirano polje in ga konfiguriral tako, da se besedilo ob vnosu vanj v realnem času odraža tudi v celici.

Tu so naslednji koraki:

1. Pojdite na zavihek Razvijalec -> Vstavi -> Kontrolniki ActiveX -> Kombinirano polje (nadzor ActiveX).
   • Obstaja možnost, da na traku morda ne najdete zavihka razvijalca. Privzeto je skrit in ga je treba omogočiti. Kliknite tukaj, če želite izvedeti, kako priti do zavihka razvijalca na traku v Excelu.
2. Premaknite kazalec na območje delovnega lista in kliknite kjer koli. Vstavil bo kombinirano polje.
3. Z desno tipko miške kliknite Combo Box in izberite Properties.
4. V pogovornem oknu lastnosti naredite naslednje spremembe:
   • AutoWordSelect: Napačno
   • LinkedCell: B3
   • ListFillRange: DropDownList (v 2. koraku bomo ustvarili imenovano območje s tem imenom)
   • MatchEntry: 2 - fmMatchEntryNone

(Celica B3 je povezana s kombiniranim poljem, kar pomeni, da je vse, kar vnesete v kombinirano polje, vneseno v B3)

4. Pojdite na zavihek Razvijalec in kliknite Način oblikovanja. To vam bo omogočilo vnos besedila v kombinirano polje. Ker je celica B3 povezana s kombiniranim poljem, bi se vsako besedilo, ki ga vnesete v kombinirano polje, v realnem času odrazilo tudi v B3.

2. korak - nastavitev podatkov

Zdaj, ko je iskalno polje vse nastavljeno, moramo vnesti podatke. Zamisel je, da takoj, ko kaj vnesete v iskalno polje, se prikažejo samo tisti elementi, ki vsebujejo to besedilo.

Za to bomo uporabili

• Trije pomožni stolpci.
• Eno dinamično imenovano območje.

Stolpec za pomoč 1

V celico F3 vstavite naslednjo formulo in jo povlecite za celoten stolpec (F3: F22)

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

Ta formula vrne 1, ko je besedilo v kombiniranem polju v imenu države na levi. Če na primer vnesete UNI, potem samo vrednosti za Unitedne države in United Kingdom so 1, vse preostale vrednosti pa 0.

Pomočni stolpec 2

V celico G3 vstavite naslednjo formulo in jo povlecite za celoten stolpec (G3: G22)

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

Ta formula vrne 1 za prvi pojav, kjer se besedilo kombiniranega polja ujema z imenom države, 2 za drugi pojav, 3 za tretje itd. Če na primer vnesete UNI, bo celica G3 prikazala 1, kot se ujema z Združenimi državami, G9 pa 2, kot se ujema z Združenim kraljestvom. Preostale celice bodo prazne.

Pomočni stolpec 3

V celico H3 vstavite naslednjo formulo in jo povlecite za celoten stolpec (H3: H22)

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

Ta formula združuje vsa ujemajoča se imena skupaj brez praznih celic med njimi. Če na primer vnesete UNI, bo v tem stolpcu prikazano skupaj 2 in 9, preostala celica pa bo prazna.

Ustvarjanje dinamičnega imenovanega območja

Zdaj, ko so pomožni stolpci na mestu, moramo ustvariti dinamični poimenovani obseg. Ta imenovani obseg se bo skliceval samo na tiste vrednosti, ki se ujemajo z besedilom, vnesenim v kombinirano polje. Ta dinamični poimenovani obseg bomo uporabili za prikaz vrednosti v spustnem polju.

Opomba: V prvem koraku smo v opcijo ListFillRange vnesli DropDownList. Zdaj bomo ustvarili imenovani obseg z istim imenom.

Tukaj so koraki za njegovo ustvarjanje:

1. Pojdite na Formule -> Upravitelj imen.
2. V pogovornem oknu upravitelja imen kliknite Novo. Odprlo se bo pogovorno okno Novo ime.
3. V polje za ime vnesite DropDownList
4. V polje Refers to Field vnesite formulo: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

3. korak - Uveljavitev kode VBA

Skoraj smo že tam.

Zadnji del je napisati kratko kodo VBA. Ta koda naredi spustni meni tako dinamičen, da prikaže ustrezne elemente/imena, ko vnašate v iskalno polje.

To kodo dodate v delovni zvezek:

1. Z desno miškino tipko kliknite zavihek Delovni list in izberite Ogled kode.
2. V oknu VBA kopirajte in prilepite naslednjo kodo:

   Zasebno pomožno ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub

To je to !!

Vse je nastavljeno z lastno vrstico za iskanje vrste Google, ki prikazuje ujemajoče se elemente, ko vnesete vnjo.

Za boljši videz in občutek lahko celico B3 prekrijete s kombiniranim poljem in skrijete vse pomožne stolpce. S tem neverjetnim trikom v Excelu se lahko zdaj malo pokažete.

Če želite nadaljevati, prenesite datoteko od tukaj

Kaj misliš? Ali bi lahko ta spustni seznam predlogov za iskanje uporabili pri svojem delu? Sporočite mi svoje misli tako, da pustite komentar.

Če ste uživali v tej vadnici, sem prepričan, da bi vam bile všeč tudi naslednje vaje za Excel:

• Dinamični filter - med tipkanjem izvlecite ujemajoče se podatke.
• Izvlecite podatke na podlagi izbire spustnega seznama.
• Ustvarjanje odvisnih spustnih seznamov v Excelu.
• Končni vodnik po uporabi funkcije Excel VLOOKUP.
• Kako narediti več izbir na spustnem seznamu v Excelu.
• Kako vstaviti in uporabiti potrditveno polje v Excelu.