Velikoj većini korisnika Excela, kada im se u glavi pojavi riječ "filtriranje podataka", samo uobičajeni klasični filter s kartice Podaci – Filter (Podaci — Filter):
Takav filter je poznata stvar, bez sumnje, i u većini slučajeva će to učiniti. Međutim, postoje situacije kada trebate filtrirati prema velikom broju složenih uslova u nekoliko kolona odjednom. Uobičajeni filter ovdje nije baš zgodan i želim nešto moćnije. Takav alat bi mogao biti napredni filter, posebno uz malo "završavanje turpijom" (prema tradiciji).
Osnove
Da biste započeli, umetnite nekoliko praznih linija iznad vaše tabele sa podacima i tamo kopirajte zaglavlje tabele – ovo će biti raspon sa uslovima (označen žutom bojom radi jasnoće):
Između žutih ćelija i originalne tabele mora postojati najmanje jedna prazna linija.
Upravo u žute ćelije treba da unesete kriterijume (uslove) prema kojima će se potom izvršiti filtriranje. Na primjer, ako trebate odabrati banane u moskovskom "Auchanu" u III kvartalu, tada će uslovi izgledati ovako:
Za filtriranje odaberite bilo koju ćeliju u rasponu s izvornim podacima, otvorite karticu podaci I kliknite na Pored toga (Podaci — Napredno). U prozoru koji se otvori, opseg sa podacima bi već trebao biti automatski unet i ostaje nam samo da navedemo opseg uslova, odnosno A1:I2:
Imajte na umu da se raspon uslova ne može dodijeliti „sa marginom“, tj. ne možete odabrati ekstra prazne žute linije, jer praznu ćeliju u rasponu uslova Excel percipira kao odsustvo kriterija, a cijelu praznu kao zahtjev da se svi podaci prikazuju neselektivno.
prekidač Kopirajte rezultat na drugu lokaciju će vam omogućiti da filtrirate listu ne tamo na ovom listu (kao kod običnog filtera), već da ispraznite odabrane redove u drugi raspon, koji će zatim morati biti naveden u polju Stavite rezultat u raspon. U ovom slučaju, ne koristimo ovu funkciju, odlazimo Lista filtera na mestu i kliknite OK. Odabrani redovi će biti prikazani na listu:
Dodavanje makroa
„Pa, gde je tu pogodnost?“ pitaš i bićeš u pravu. Ne samo da morate rukama unijeti uvjete u žute ćelije, već i otvoriti dijaloški okvir, uneti opsege, pritisnuti OK. Tužno, slažem se! Ali “sve se mijenja kada dođu ©” – makroi!
Rad sa naprednim filterom može se znatno ubrzati i pojednostaviti korišćenjem jednostavnog makroa koji će automatski pokrenuti napredni filter kada se unesu uslovi, tj. menjaju bilo koju žutu ćeliju. Kliknite desnim tasterom miša na karticu trenutnog lista i izaberite komandu Izvorni tekst (Izvorni kod). U prozoru koji se otvori kopirajte i zalijepite sljedeći kod:
Privatni pod Radni list_Change(ByVal Target As Range) Ako nije Intersect(Target, Range("A2:I5")) nije ništa onda u slučaju greške Nastavi sljedeći ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPla :=Raspon("A1").CurrentRegion End If End Sub Ova procedura će se automatski pokrenuti kada se promijeni bilo koja ćelija na trenutnom radnom listu. Ako adresa promijenjene ćelije padne u žuti raspon (A2:I5), tada ovaj makro uklanja sve filtere (ako ih ima) i ponovo primjenjuje prošireni filter na tabelu izvornih podataka počevši od A7, tj. sve će biti filtrirano odmah, odmah nakon unosa sljedećeg uslova:
Dakle, sve je mnogo 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 tačnih podudaranja, možete koristiti različite zamjenske znakove (* i ?) i matematičke znakove nejednakosti u nizu uvjeta za implementaciju približnog pretraživanja. Velika i mala slova nisu bitna. Radi jasnoće, sažeo sam sve moguće opcije u tabeli:
| Kriterij | rezultat |
| gr* ili gr | sve ćelije počevši od GrIe Gruho, Grapfruit, Granat itd |
| = luk | sve ćelije tačno i samo sa rečju luk, odnosno tačno podudaranje |
| *liv* ili *liv | ćelije koje sadrže liv kako podvuci, tj Оlivda, livep, premaliv itd |
| =p*v | riječi koje počinju sa П i završava sa В ie Пprvoв, Пeterв itd |
| a*s | riječi koje počinju sa А i dalje sadrži СIe Аpelсin, Аnanaс, Asai itd |
| =*s | riječi koje se završavaju na С |
| =???? | sve ćelije sa tekstom od 4 znaka (slova ili brojevi, uključujući razmake) |
| =m??????n | sve ćelije sa tekstom od 8 znakova koji počinju sa М i završava sa НIe Мandariн, Мanksioznostн itd |
| =*n??a | sve riječi koje se završavaju sa А, gdje je 4. slovo s kraja НIe zrakнikа, premaнozа itd |
| >=e | sve reči koje počinju sa Э, Ю or Я |
| <>*o* | sve riječi koje ne sadrže slovo О |
| <>*vich | sve riječi osim onih koje se završavaju na HIV (na primjer, filtrirajte žene po srednjem imenu) |
| = | sve prazne ćelije |
| <> | sve neprazne ćelije |
| > = 5000 | sve ćelije čija je vrijednost veća ili jednaka 5000 |
| 5 ili =5 | sve ćelije sa vrednošću 5 |
| >=3 | sve ćelije sa datumom nakon 18. marta 2013. (uključivo) |
Suptilne tačke:
- Znak * označava bilo koji broj bilo kojeg karaktera, a ? – bilo koji lik.
- Logika u obradi tekstualnih i numeričkih upita je malo drugačija. Tako, na primjer, ćelija uslova sa brojem 5 ne znači da tražimo sve brojeve koji počinju sa pet, ali je ćelija uslova sa slovom B jednaka B*, odnosno tražiće bilo koji tekst koji počinje slovom B.
- Ako tekstualni upit ne počinje znakom =, onda možete mentalno staviti * na kraj.
- Datumi se moraju unijeti u američkom formatu mjesec-dan-godina i kroz razlomak (čak i ako imate Excel i regionalne postavke).
Logički spojevi AND-OR
Smatra se da su uslovi napisani u različitim ćelijama, ali u istom redu, međusobno povezani logičkim operatorom И (I):
One. filter banane za mene u trecem kvartalu, tacno u Moskvi iu isto vreme iz Auchana.
Ako trebate povezati uvjete s logičkim operatorom OR (OR), onda ih samo treba unijeti u različite redove. Na primjer, ako trebamo pronaći sve narudžbe menadžera Voline za moskovske breskve i sve narudžbe za luk u trećem kvartalu u Samari, onda se to može specificirati u nizu uslova na sljedeći način:
Ako trebate nametnuti dva ili više uvjeta na jednu kolonu, onda možete jednostavno duplicirati zaglavlje stupca u rasponu kriterija i ispod njega unijeti drugi, treći itd. uslovi. Tako, na primjer, možete odabrati sve transakcije od marta do maja:
Općenito, nakon "završetka s datotekom", napredni filter ispada sasvim pristojan alat, na nekim mjestima ništa lošiji od klasičnog autofiltera.
- Superfilter na makroima
- Šta su makroi, gde i kako umetnuti makro kod u Visual Basic
- Pametne tabele u programu Microsoft Excel