Izradite padajući popis programa Excel s prijedlozima pretraživanja

Svi koristimo Google kao dio svoje dnevne rutine. Jedna od njegovih značajki je prijedlog za pretraživanje, gdje se Google ponaša pametno i daje nam popis prijedloga dok tipkamo.

U ovom ćete vodiču naučiti kako stvoriti padajući popis koji se može pretraživati ​​u Excelu-tj. Padajući popis koji će prikazati odgovarajuće stavke dok upisujete.

Ispod je videozapis ovog vodiča (u slučaju da radije gledate video umjesto čitanja teksta).

Padajući popis u Excel koji se može pretraživati

Za potrebe ovog vodiča koristim se podacima 20 najboljih zemalja prema BDP -u.

Namjera je stvoriti Excel padajući popis s mehanizmom prijedloga pretraživanja, tako da prikazuje padajući izbornik s odgovarajućim opcijama dok upisujem u traku za pretraživanje.

Nešto kao što je prikazano ispod:

Da biste nastavili, preuzmite datoteku primjera odavde

Izrada padajućeg popisa koji se može pretraživati ​​u Excelu bio bi trodijelni proces:

1. Konfiguriranje okvira za pretraživanje.
2. Postavljanje podataka.
3. Pisanje kratkog VBA koda kako bi funkcioniralo.

Korak 1 - Konfiguriranje okvira za pretraživanje

U ovom prvom koraku koristit ću kombinirani okvir i konfigurirati ga tako da se prilikom upisivanja tekst u realnom vremenu reflektira i u ćeliji.

Evo koraka za to:

1. Idite na karticu Developer -> Insert -> ActiveX Controls -> Combo Box (ActiveX Control).
   • Postoji mogućnost da na vrpci nećete pronaći karticu razvojnog programera. Prema zadanim postavkama skriveno je i potrebno ga je omogućiti. Kliknite ovdje da biste saznali kako dobiti karticu programera na vrpci u Excelu.
2. Pomaknite pokazivač na područje radnog lista i kliknite bilo gdje. Umetnut će kombinirani okvir.
3. Desnom tipkom miša kliknite Combo Box i odaberite Svojstva.
4. U dijaloškom okviru svojstava napravite sljedeće promjene:
   • Automatski odabir riječi: Netočno
   • LinkedCell: B3
   • ListFillRange: DropDownList (u 2. koraku ćemo stvoriti imenovani raspon s ovim imenom)
   • MatchEntry: 2 - fmMatchEntryNone

(Ćelija B3 povezana je s kombiniranim okvirom, što znači da se sve što upišete u kombinirano polje unosi u B3)

4. Idite na karticu Developer i kliknite na Design Mode. To će vam omogućiti unos teksta u kombinirani okvir. Također, budući da je ćelija B3 povezana s kombiniranim okvirom, svaki tekst koji unesete u kombinirani okvir također će se odražavati u B3 u stvarnom vremenu.

Korak 2 - Postavljanje podataka

Sada kada je okvir za pretraživanje sve postavljen, moramo postaviti podatke na mjesto. Ideja je da čim upišete bilo što u okvir za pretraživanje, prikazuje samo one stavke koje u sebi imaju taj tekst.

Za to ćemo koristiti

• Tri pomoćne kolone.
• Jedan dinamički imenovani raspon.

Kolona pomoćnika 1

Stavite sljedeću formulu u ćeliju F3 i povucite je za cijeli stupac (F3: F22)

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

Ova formula vraća 1 kada se tekst u kombiniranom okviru nalazi u nazivu zemlje s lijeve strane. Na primjer, ako upišete UNI, tada samo vrijednosti za United State i United Kingdom su 1, a sve preostale vrijednosti 0.

Kolona pomoćnika 2

Stavite sljedeću formulu u ćeliju G3 i povucite je za cijeli stupac (G3: G22)

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

Ova formula vraća 1 za prvu pojavu gdje tekst Combo Box odgovara imenu zemlje, 2 za drugu pojavu, 3 za treću itd. Na primjer, ako upišete UNI, ćelija G3 prikazat će 1 kao što odgovara Sjedinjenim Državama, a G9 će prikazati 2 kao što odgovara Ujedinjenom Kraljevstvu. Ostatak ćelija bit će prazan.

Kolona pomoćnika 3

Stavite sljedeću formulu u ćeliju H3 i povucite je za cijeli stupac (H3: H22)

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

Ova formula slaže sva odgovarajuća imena zajedno bez praznih ćelija između njih. Na primjer, ako upišete UNI, ovaj stupac prikazuje 2 i 9 zajedno, a ostatak će sve ćelije biti prazan.

Stvaranje dinamičkog imenovanog raspona

Sada kada su pomoćni stupci postavljeni, moramo stvoriti dinamički imenovani raspon. Ovaj imenovani raspon odnosit će se samo na one vrijednosti koje odgovaraju tekstu unesenom u kombinirani okvir. Ovaj dinamički imenovani raspon koristit ćemo za prikaz vrijednosti u padajućem okviru.

Bilješka: U koraku 1 unijeli smo DropDownList u opciju ListFillRange. Sada ćemo stvoriti imenovani raspon s istim imenom.

Evo koraka za njegovo stvaranje:

1. Idite na Formule -> Upravitelj imena.
2. U dijaloškom okviru upravitelja imena kliknite Novo. Otvorit će se dijaloški okvir Novo ime.
3. U polje za ime unesite DropDownList
4. U polje Refers to Field unesite formulu: = $ H $ 3: INDEKS ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Korak 3 - Uvođenje VBA koda u funkciju

Skoro smo tamo.

Posljednji dio je pisanje kratkog VBA koda. Ovaj kôd čini padajući izbornik dinamičkim tako da prikazuje odgovarajuće stavke/nazive dok upisujete u okvir za pretraživanje.

Da biste dodali ovaj kôd u svoju radnu knjigu:

1. Desnom tipkom miša kliknite karticu Radni list i odaberite Prikaz koda.
2. U prozoru VBA kopirajte i zalijepite sljedeći kod:

   Privatni pomoćni ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Ja.ComboBox1.DropDown Kraj podm

To je to!!

Sve vam je postavljeno s vlastitom trakom za pretraživanje tipa Google koja prikazuje odgovarajuće stavke dok je upisujete.

Za bolji izgled i dojam možete pokriti ćeliju B3 kombiniranim okvirom i sakriti sve pomoćne stupce. Sada se možete malo pohvaliti ovim nevjerojatnim Excelovim trikom.

Da biste nastavili, preuzmite datoteku odavde

Što misliš? Biste li mogli koristiti ovaj padajući popis prijedloga pretraživanja u svom radu? Javite mi svoja razmišljanja ostavljajući komentar.

Ako ste uživali u ovom vodiču, siguran sam da biste voljeli i sljedeće Excel vodiče:

• Dinamički filtar - izdvajajte odgovarajuće podatke dok upisujete.
• Izdvajanje podataka na temelju odabira padajućeg popisa.
• Stvaranje ovisnih padajućih popisa u Excelu.
• Vrhunski vodič za korištenje Excel VLOOKUP funkcije.
• Kako napraviti više odabira na padajućem popisu u Excelu.
• Kako umetnuti i koristiti potvrdni okvir u Excelu.