Dinamično iskalno polje filtra Excel (izvleči podatke med tipkanjem)

Excel Filter je ena najpogosteje uporabljenih funkcij pri delu s podatki. V tem prispevku v spletnem dnevniku vam bom pokazal, kako ustvarite iskalno polje za dinamični Excelov filter, tako da filtrira podatke glede na to, kaj vnesete v iskalno polje.

Nekaj, kot je prikazano spodaj:

Za to obstaja dvojna funkcija - na spustnem seznamu lahko izberete ime države ali pa ročno vnesete podatke v iskalno polje in prikazali vam bodo vse ujemajoče se zapise. Ko na primer vnesete "I", se prikažejo vsa imena držav z abecedo I.

Oglejte si video - Ustvarjanje polja za iskanje dinamičnega filtra Excel

Ustvarjanje iskalnega polja za dinamični Excelov filter

Ta dinamični Excelov filter lahko ustvarite v treh korakih:

1. Pridobivanje edinstvenega seznama predmetov (v tem primeru države). To bi bilo uporabljeno pri ustvarjanju spustnega menija.
2. Ustvarjanje iskalnega polja. Tukaj sem uporabil Combo Box (ActiveX Control).
3. Nastavitev podatkov. Tu bi uporabil tri pomožne stolpce s formulami za ekstrakcijo ujemajočih se podatkov.

Tako izgledajo neobdelani podatki:

KORISTNI NASVET: Skoraj vedno je dobra ideja pretvoriti podatke v Excelovo tabelo. To lahko storite tako, da izberete poljubno celico v naboru podatkov in uporabite bližnjico na tipkovnici Control + T.

1. korak - Pridobite edinstven seznam predmetov

1. Izberite vse države in jih prilepite v nov delovni list.
2. Izberite seznam držav -> Pojdi na Podatki -> Odstrani dvojnike.
3. V pogovornem oknu Odstrani podvojene datoteke izberite stolpec, v katerem imate seznam, in kliknite V redu. S tem boste odstranili dvojnike in dobili edinstven seznam, kot je prikazano spodaj:
4. Dodaten korak je ustvarjanje poimenovanega obsega za ta edinstven seznam. Storiti to:
   • Pojdite na zavihek Formula -> Določi ime
   • V pogovornem oknu Določite ime:
     • Ime: CountryList
     • Področje uporabe: Delovni zvezek
     • Nanaša se na: = UniqueList! $ A $ 2: $ A $ 9 (seznam imam na ločenem zavihku z imenom UniqueList v A2: A9. Lahko se sklicujete povsod, kjer je vaš edinstven seznam)

OPOMBA: Če uporabite metodo »Odstrani podvojene podatke« in razširite podatke, da dodate več zapisov in novih držav, boste morali ta korak ponoviti. Druga možnost je, da naredite formulo, da bo ta proces dinamičen.

2. korak - Ustvarjanje iskalnega polja za dinamični Excelov filter

Da bi ta tehnika delovala, bi morali ustvariti »iskalno polje« in jo povezati s celico.

Za ustvarjanje tega filtra iskalnega polja lahko uporabimo kombinirano polje v Excelu. Na ta način bi se vsakič, ko vnesete karkoli v Combo Box, to odražalo tudi v celici v realnem času (kot je prikazano spodaj).

Tu so naslednji koraki:

1. Pojdite na zavihek Razvijalec -> Kontrolniki -> Vstavi -> Kontrolniki ActiveX -> Kombinirano polje (Kontrolniki ActiveX).
   • Če zavihek Razvijalec ni viden, ga lahko omogočite tukaj.
2. Kliknite kjer koli na delovnem listu. Vstavil bo Combo Box.
3. Z desno tipko miške kliknite Combo Box in izberite Properties.
4. V oknu Lastnosti naredite naslednje spremembe:
   • Povezana celica: K2 (lahko izberete katero koli celico, v kateri želite, da prikazuje vhodne vrednosti. To celico bomo uporabili pri nastavljanju podatkov).
   • ListFillRange: CountryList (to je imenovani obseg, ki smo ga ustvarili v 1. koraku. To bi prikazalo vse države v spustnem meniju).
   • MatchEntry: 2-fmMatchEntryNone (to zagotavlja, da se beseda med tipkanjem ne dokonča samodejno)
5. Ko je izbrano kombinirano polje, pojdite na zavihek Razvijalec -> Kontrolniki -> Kliknite Način oblikovanja (s tem izstopite iz načina oblikovanja, zdaj pa lahko v polje Combo vnesete karkoli. Zdaj se bo vse, kar vnesete, odrazilo v celici K2 v realnem času)

Korak 3 - Nastavitev podatkov

Nazadnje vse povežemo s pomožnimi stolpci. Za filtriranje podatkov uporabljam tri pomožne stolpce.

Pomočni stolpec 1: Vnesite zaporedno številko za vse zapise (v tem primeru 20). Za to lahko uporabite formulo ROWS ().

Pomočni stolpec 2: V pomožnem stolpcu 2 preverimo, ali se besedilo, vneseno v iskalno polje, ujema z besedilom v celicah v stolpcu država.

To lahko storite s kombinacijo funkcij IF, ISNUMBER in SEARCH.

Tukaj je formula:

= IF (ISNUMBER (ISKANJE ($ K $ 2, D4)), E4, "")

Ta formula bo iskala vsebino v iskalnem polju (ki je povezano s celico K2) v celici z imenom države.

Če obstaja ujemanje, ta formula vrne številko vrstice, sicer vrne prazno. Na primer, če ima kombinirano polje vrednost »ZDA«, bi imeli vsi zapisi z državo kot »ZDA« številko vrstice, ostali pa prazni (»«)

Pomočni stolpec 3: V pomožnem stolpcu 3 moramo zbrati vse številke vrstic iz stolpca 2 pomočnika. Če želite to narediti, lahko uporabite kombinacijo formule IFERROR in SMALL. Tukaj je formula:

= NAPAKA (MALO ($ F $ 4: $ F $ 23, E4), "")

Ta formula združuje vse ustrezne številke vrstic. Na primer, če ima kombinirano polje vrednost US, se vse številke vrstic z "US" v njem zložijo.

Ko imamo skupaj zložene številke vrstic, moramo samo izvleči podatke iz te številke vrstice. To lahko preprosto storite z uporabo indeksne formule (vstavite to formulo tja, kjer želite izvleči podatke. Kopirajte jo v zgornjo levo celico, kamor želite izvleči podatke, in jo povlecite navzdol in desno).

= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")

Ta formula ima 2 dela:
KAZALO - S tem izvlečete podatke na podlagi številke vrstice.
NAPAKA - To vrne prazno, če ni podatkov.

Tukaj je posnetek, kaj končno dobite:

Combo Box je spustni meni in iskalno polje. Izvirne podatke in pomožne stolpce lahko skrijete in prikažete samo filtrirane zapise. Neobdelane podatke in pomožne stolpce imate lahko tudi na kakšnem drugem listu in ustvarite ta dinamični filter Excel na drugem delovnem listu.

Bodite ustvarjalni! Poskusite nekaj različic

Lahko ga poskusite prilagoditi svojim zahtevam. Namesto enega lahko ustvarite več filtrov Excel. Morda boste na primer želeli filtrirati zapise, kjer je prodajni predstavnik Mike, država pa Japonska. To lahko storite natančno po istih korakih z nekaj spremembami v formuli v pomožnih stolpcih.

Druga možnost je lahko filtriranje podatkov, ki se začnejo z znaki, ki jih vnesete v kombinirano polje. Na primer, ko vnesete "I", boste morda želeli izvleči države, ki se začnejo z I (v primerjavi s sedanjim konstruktom, kjer bi vam dala tudi Singapur in Filipine, saj vsebuje abecedo I).

Kot vedno je večina mojih člankov navdihnjena z vprašanji/odgovori mojih bralcev. Rad bi dobil vaše povratne informacije in se učil od vas. Pustite svoje misli v oddelku za komentarje.

Opomba: Če uporabljate Office 365, lahko s funkcijo FILTER hitro filtrirate podatke med tipkanjem. To je lažje od metode, prikazane v tej vadnici.