Okvir za pretraživanje dinamičkog Excel filtra (izdvaja podatke dok tipkate)

Excel filter jedna je od najčešće korištenih funkcionalnosti pri radu s podacima. U ovom postu na blogu pokazat ću vam kako stvoriti dinamički okvir za pretraživanje filtera Excel tako da filtrira podatke na temelju onoga što upišete u okvir za pretraživanje.

Nešto kao što je prikazano ispod:

To ima dvostruku funkcionalnost - možete odabrati naziv zemlje s padajućeg popisa ili možete ručno unijeti podatke u okvir za pretraživanje i on će vam pokazati sve odgovarajuće zapise. Na primjer, kada upišete "I", dobit ćete sve nazive zemalja sa abecedom I u sebi.

Gledajte video - Stvaranje okvira za pretraživanje dinamičkog filtra u Excelu

Stvaranje okvira za pretraživanje dinamičkog filtra Excel

Ovaj dinamički Excel filtar može se izraditi u 3 koraka:

1. Dobivanje jedinstvenog popisa artikala (u ovom slučaju zemlje). To bi se koristilo pri stvaranju padajućeg izbornika.
2. Izrada okvira za pretraživanje. Ovdje sam upotrijebio Combo Box (ActiveX Control).
3. Postavljanje podataka. Ovdje bih koristio tri pomoćna stupca s formulama za izdvajanje odgovarajućih podataka.

Evo kako izgledaju sirovi podaci:

KORISNI SAVJET: Gotovo je uvijek dobra ideja pretvoriti vaše podatke u Excel tablicu. To možete učiniti odabirom bilo koje ćelije u skupu podataka i pomoću prečaca na tipkovnici Control + T.

Korak 1 - Dobivanje jedinstvenog popisa stavki

1. Odaberite sve zemlje i zalijepite ih u novi radni list.
2. Odaberite popis zemalja -> Idi na Podaci -> Ukloni duplikate.
3. U dijaloškom okviru Uklanjanje duplikata odaberite stupac u kojem imate popis i kliknite U redu. Ovo će ukloniti duplikate i dati vam jedinstveni popis kao što je prikazano u nastavku:
4. Još jedan dodatni korak je stvaranje imenovanog raspona za ovaj jedinstveni popis. Uraditi ovo:
   • Idite na karticu Formula -> Definiraj naziv
   • U dijaloškom okviru Definiranje imena:
     • Naziv: CountryList
     • Opseg: Radna bilježnica
     • Odnosi se na: = UniqueList! $ A $ 2: $ A $ 9 (imam popis na zasebnoj kartici pod nazivom UniqueList u A2: A9. Možete se pozvati gdje god se nalazi vaš jedinstveni popis)

NAPOMENA: Ako koristite metodu „Ukloni duplikate“ i proširite svoje podatke kako biste dodali još zapisa i novih zemalja, morat ćete ponoviti ovaj korak. Alternativno, možete također formulom učiniti ovaj proces dinamičnim.

Korak 2 - Stvaranje okvira za pretraživanje dinamičkog Excel filtra

Da bi ova tehnika funkcionirala, morali bismo stvoriti "okvir za pretraživanje" i povezati ga s ćelijom.

Za stvaranje ovog filtra okvira za pretraživanje možemo upotrijebiti Combo Box u Excelu. Na ovaj način, kad god unesete bilo što u Combo Box, to će se također odraziti u ćeliji u stvarnom vremenu (kao što je prikazano u nastavku).

Evo koraka za to:

1. Idite na karticu Developer -> Controls -> Insert -> ActiveX Controls -> Combo Box (ActiveX Controls).
   • Ako nemate vidljivu karticu Developer, evo koraka za njeno omogućavanje.
2. Kliknite bilo gdje na radnom listu. Umetnut će Combo Box.
3. Desnom tipkom miša kliknite Combo Box i odaberite Svojstva.
4. U prozoru Svojstva napravite sljedeće promjene:
   • Povezana ćelija: K2 (možete odabrati bilo koju ćeliju u kojoj želite da prikazuje ulazne vrijednosti. Mi ćemo koristiti ovu ćeliju za postavljanje podataka).
   • ListFillRange: CountryList (ovo je imenovani raspon koji smo stvorili u 1. koraku. Ovo će prikazati sve zemlje u padajućem izborniku).
   • MatchEntry: 2-fmMatchEntryNone (to osigurava da se riječ ne dovršava automatski dok tipkate)
5. S odabranim kombiniranim okvirom, idite na karticu Razvojni programer -> Kontrole -> Kliknite na Način dizajna (ovo vas izbacuje iz načina dizajna, pa sada možete upisati bilo što u Combo Box. Sada, sve što upišete će se odraziti u ćeliji K2 u stvarnom vremenu)

Korak 3 - Postavljanje podataka

Konačno, sve povezujemo pomoćnim stupcima. Ovdje koristim tri pomoćna stupca za filtriranje podataka.

Kolona pomoćnika 1: Unesite serijski broj za sve zapise (u ovom slučaju 20). Za to možete koristiti formulu ROWS ().

Kolona pomoćnika 2: U pomoćnom stupcu 2 provjeravamo odgovara li tekst unesen u okvir za pretraživanje tekstu u ćelijama u stupcu zemlje.

To se može učiniti kombinacijom funkcija IF, ISNUMBER i SEARCH.

Evo formule:

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

Ova formula će tražiti sadržaj u okviru za pretraživanje (koji je povezan s ćelijom K2) u ćeliji koja ima naziv zemlje.

Ako postoji podudaranje, ova formula vraća broj retka, inače vraća prazno. Na primjer, ako kombinirani okvir ima vrijednost "SAD", svi zapisi s zemljom kao "SAD" imat će broj retka, a svi ostali bit će prazni ("")

Kolona pomoćnika 3: U pomoćnom stupcu 3 moramo skupiti sve brojeve redaka iz Stupaca 2 pomoćnika. Da bismo to učinili, možemo koristiti kombinaciju IFERROR i SMALL formule. Evo formule:

= IFERROR (MALI ($ F $ 4: $ F $ 23, E4), "")

Ova formula slaže sve odgovarajuće brojeve redaka zajedno. Na primjer, ako kombinirani okvir ima vrijednost US, svi brojevi redaka s "US" u njemu se slažu zajedno.

Sada, kada imamo brojeve redaka složene zajedno, samo moramo izdvojiti podatke u ove brojeve redaka. To se može lako učiniti pomoću formule indeksa (umetnite ovu formulu u mjesto gdje želite izdvojiti podatke. Kopirajte je u gornju lijevu ćeliju u koju želite izdvojiti podatke, a zatim je povucite prema dolje i udesno).

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

Ova formula ima 2 dijela:
INDEKS - Ovo izdvaja podatke na temelju broja retka.
POGREŠAK - Vraća se prazno ako nema podataka.

Evo kratkog prikaza onoga što konačno dobijete:

Combo Box je padajući izbornik kao i okvir za pretraživanje. Možete sakriti izvorne podatke i pomoćne stupce kako biste prikazali samo filtrirane zapise. Također možete imati neobrađene podatke i pomoćne stupce na nekom drugom listu i stvoriti ovaj dinamički Excel filtar na drugom radnom listu.

Budite kreativni! Isprobajte neke varijacije

Možete ga pokušati prilagoditi svojim zahtjevima. Možda ćete htjeti stvoriti više excel filtera umjesto jednog. Na primjer, možda ćete htjeti filtrirati zapise u kojima je prodajni predstavnik Mike, a zemlja Japan. To se može učiniti točno slijedeći iste korake uz neke izmjene u formuli u pomoćnim stupcima.

Druga varijacija mogla bi biti filtriranje podataka koji počinju znakovima koje unesete u kombinirani okvir. Na primjer, kada unesete "I", možda ćete htjeti izdvojiti zemlje koje počinju s I (u usporedbi s trenutnom konstrukcijom gdje bi vam također dali Singapur i Filipine jer sadrži abecedu I).

Kao i uvijek, većina mojih članaka inspirirana je pitanjima/odgovorima mojih čitatelja. Volio bih dobiti vaše povratne informacije i učiti od vas. Ostavite svoje misli u odjeljku za komentare.

Napomena: U slučaju da koristite Office 365, možete koristiti funkciju FILTER za brzo filtriranje podataka dok upisujete. Lakši je od metode prikazane u ovom vodiču.