Velikoj većini korisnika Excela, kada u glavi padne riječ "filtriranje podataka", samo uobičajeni klasični filter s kartice Podaci – Filtar (Podaci — Filtar):
Takav je filtar bez sumnje poznata stvar i u većini slučajeva poslužit će. Međutim, postoje situacije kada trebate filtrirati prema velikom broju složenih uvjeta u nekoliko stupaca odjednom. Uobičajeni filter ovdje nije baš zgodan i želim nešto snažnije. Takav bi alat mogao biti napredni filtar, pogotovo uz malo “dorade turpijom” (po tradiciji).
Temelj
Da biste započeli, umetnite nekoliko praznih redaka iznad tablice s podacima i tamo kopirajte zaglavlje tablice – to će biti raspon s uvjetima (označeno žutom bojom radi jasnoće):
Mora postojati barem jedan prazan redak između žutih ćelija i izvorne tablice.
Upravo u žuta polja potrebno je unijeti kriterije (uvjete) prema kojima će se zatim vršiti filtriranje. Na primjer, ako trebate odabrati banane u moskovskom "Auchan" u III kvartalu, tada će uvjeti izgledati ovako:
Za filtriranje odaberite bilo koju ćeliju u rasponu s izvornim podacima, otvorite karticu Datum I kliknite gumb Dodatno (Podaci — Napredno). U prozoru koji se otvori trebao bi već biti automatski upisan raspon s podacima i ostaje nam samo da navedemo raspon uvjeta, tj. A1:I2:
Imajte na umu da se raspon uvjeta ne može dodijeliti "s marginom", tj. ne možete odabrati dodatne prazne žute linije, jer praznu ćeliju u rasponu uvjeta Excel percipira kao nepostojanje kriterija, a čitavu praznu liniju kao zahtjev za prikaz svih podataka bez razlike.
Prekidač Kopirajte rezultat na drugo mjesto omogućit će vam da filtrirate popis ne na ovom listu (kao kod običnog filtra), već da učitate odabrane retke u drugi raspon, koji će tada morati biti specificiran u polju Stavite rezultat u raspon. U ovom slučaju ne koristimo ovu funkciju, odlazimo Popis filtera na mjestu i kliknite OK. Odabrani redovi bit će prikazani na listu:
Dodavanje makronaredbe
"Pa, gdje je tu pogodnost?" pitaš i bit ćeš u pravu. Ne samo da trebate rukama unijeti uvjete u žute ćelije, već i otvoriti dijaloški okvir, unijeti raspone tamo, pritisnuti OK. Žalosno, slažem se! Ali “sve se mijenja kad dođu ©” – makronaredbe!
Rad s naprednim filtrom može se znatno ubrzati i pojednostaviti korištenjem jednostavnog makronaredbe koji će automatski pokrenuti napredni filtar kada se unesu uvjeti, odnosno promijeni bilo koje žuto polje. Desnom tipkom miša kliknite karticu trenutnog lista i odaberite naredbu Izvorni tekst (Izvorni kod). U prozoru koji se otvori kopirajte i zalijepite sljedeći kod:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Raspon("A1").CurrentRegion End If End Sub Ovaj će se postupak automatski pokrenuti kada se promijeni bilo koja ćelija na trenutnom radnom listu. Ako adresa promijenjene ćelije padne u žuti raspon (A2:I5), tada ova makronaredba uklanja sve filtre (ako ih ima) i ponovno primjenjuje prošireni filtar na izvornu tablicu podataka počevši od A7, tj. sve će biti trenutno filtrirano, odmah nakon unosa sljedećeg uvjeta:
Dakle, sve je puno bolje, zar ne? 🙂
Implementacija složenih upita
Sada kada se sve filtrira u hodu, možemo ići malo dublje u nijanse i rastaviti mehanizme složenijih upita u naprednom filteru. Osim unosa točnih podudaranja, možete koristiti različite zamjenske znakove (* i ?) i matematičke znakove nejednakosti u nizu uvjeta za provedbu približnog pretraživanja. Velika i mala slova nisu bitna. Radi jasnoće, sažeo sam sve moguće opcije u tablici:
| Kriterij | Proizlaziti |
| gr* ili gr | sve ćelije koje počinju s GrIe Gruho, Grmajkolica, Granat i tako dalje |
| = luk | sve ćelije točno i samo s riječju Luk, tj. točno podudaranje |
| *liv* ili *liv | stanice koje sadrže Liv kako podcrtati, tj ОLivjer je, Livep, PremaLiv i tako dalje |
| =p*v | riječi koje počinju sa П a završava s В ie Пprviв, Пeterв i tako dalje |
| kao | riječi koje počinju sa А i dalje sadrži СIe АPELсin, Аnanaс, Asai i tako dalje |
| =*s | riječi koje završavaju na С |
| =???? | sve ćelije s tekstom od 4 znaka (slova ili brojke, uključujući razmake) |
| =m??????n | sve ćelije s tekstom od 8 znakova koji počinju s М a završava s НIe Мandariн, Мanksioznostн i tako dalje |
| =*n??a | sve riječi koje završavaju sa А, gdje je 4. slovo od kraja НIe Snopнikа, Premaнozа i tako dalje |
| >=e | sve riječi koje počinju sa Э, Ю or Я |
| <>*o* | sve riječi koje ne sadrže slovo О |
| <>*vich | sve riječi osim onih koje završavaju na HIV (na primjer, filtrirajte žene prema srednjem imenu) |
| = | sve prazne ćelije |
| <> | sve neprazne ćelije |
| > = 5000 | sve ćelije s vrijednošću većom ili jednakom 5000 |
| 5 ili =5 | sve ćelije s vrijednošću 5 |
| >=3 | sve ćelije s datumom nakon 18. ožujka 2013. (uključivo) |
Suptilne točke:
- Znak * označava bilo koji broj bilo kojih znakova, a ? – bilo koji znak.
- Logika obrade tekstualnih i numeričkih upita malo je drugačija. Tako npr. uvjetna ćelija s brojem 5 ne znači tražiti sve brojeve koji počinju s pet, ali uvjetna ćelija sa slovom B jednaka je B*, tj. tražit će bilo koji tekst koji počinje slovom B.
- Ako tekstualni upit ne počinje znakom =, tada možete mentalno staviti * na kraju.
- Datumi se moraju unijeti u američkom formatu mjesec-dan-godina i kroz razlomak (čak i ako imate Excel i regionalne postavke).
Logički veznici I-ILI
Uvjeti napisani u različitim ćelijama, ali u istom retku, smatraju se međusobno povezanima logičkim operatorom И (I):
Oni. filter banane za mene u trećem kvartalu, upravo u Moskvi iu isto vrijeme iz Auchana.
Ako trebate povezati uvjete s logičkim operatorom OR (ILI), tada ih samo treba unijeti u različite retke. Na primjer, ako trebamo pronaći sve narudžbe upravitelja Voline za moskovske breskve i sve narudžbe za luk u trećem tromjesečju u Samari, tada se to može specificirati u nizu uvjeta kako slijedi:
Ako trebate nametnuti dva ili više uvjeta na jedan stupac, tada možete jednostavno duplicirati zaglavlje stupca u rasponu kriterija i unijeti drugi, treći itd. ispod njega. Pojmovi. Tako, na primjer, možete odabrati sve transakcije od ožujka do svibnja:
Općenito, nakon "završetka s datotekom", napredni filtar postaje sasvim pristojan alat, na nekim mjestima ništa lošiji od klasičnog autofiltera.
- Superfilter na makroima
- Što su makronaredbe, gdje i kako umetnuti makro kod u Visual Basic
- Pametne tablice u Microsoft Excelu