Superfilter u VBA

Standardni AutoFilter za odabir sa lista je svakako poznata i pouzdana stvar. Ali da biste stvorili složene uvjete, morate izvršiti dosta radnji. Na primjer, da biste filtrirali vrijednosti koje spadaju u raspon od 100 do 200, potrebno je mišem proširiti listu AutoFilter, odabrati opciju stanje (Prilagođeno)i u novijim verzijama Excela: Numerički filteri – prilagođeni filter (Filteri brojeva — Prilagođeni filter). Zatim, u dijaloškom okviru, navedite dva operatora poređenja, vrijednosti i logičku vezu (AND-OR) između njih:

Superfilter u VBA

Ne predugo, neki će reći. Da, ali ako ovu proceduru morate ponavljati nekoliko desetina puta dnevno? Postoji izlaz – alternativni filter pomoću makroa koji će uzimati vrijednosti kriterija odabira direktno iz ćelija lista, gdje ih jednostavno unosimo s tastature. U suštini, biće sličan naprednom filteru, ali će raditi u realnom vremenu. Da bismo takvu stvar implementirali, potrebno je poduzeti samo dva koraka:

Korak 1. Imenovani opseg za uslove

Prvo morate kreirati imenovani raspon u koji ćemo unijeti uslove i odakle će ih makro preuzeti. Da biste to učinili, možete umetnuti nekoliko praznih linija odmah iznad tablice, zatim odabrati ćelije za buduće kriterije (na slici je A2: F2) i dati im ime uslovitako što ćete ga upisati u polje za ime u gornjem lijevom kutu i pritisnuti tipku Enter. Radi jasnoće, označio sam ove ćelije žutom bojom:

Korak 2. Dodavanje makronaredbe za filtriranje

Sada morate dodati makronaredbu filtera trenutnom listu prema kriterijima iz kreiranog raspona uslovi. Da biste to učinili, desnom tipkom miša kliknite karticu lista i odaberite naredbu Izvorni tekst (Izvorni tekst). U prozoru uređivača Visual Basica koji se otvori, morate kopirati i zalijepiti tekst sljedećeg makroa:

Privatni pod Radni list_Change(ByVal Target As Range) Dim FilterCol As Integer Dim FilterRange As Range Dim CondtitionString kao varijanta Dim Condition1 kao string, Condition2 kao string Ako Intersect(Target, Range("Conditions")) nije ništa, zatim nastavite sa pod sljedeći na Err Application.ScreenUpdating = False 'Definirajte raspon podataka liste Postavite FilterRange = Target.Parent.AutoFilter.Range 'Pročitajte uslove iz svih ažuriranih ćelija raspona uslova za svaku ćeliju u Target.Cells FilterCol = ćelija.Column - FilterRange.Columns(1).Column + 1 Ako je IsEmpty(cell) Onda Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol Inače Ako InStr(1, UCase(cell.Value), " OR ") > 0 Tada LogicOperator = xlOr ConditionArray = Split (UCase(cell.Value), " OR ") Inače Ako je InStr(1, UCase(cell.Value), " AND ") > 0 Tada je LogicOperator = xlAnd ConditionArray = Split(UCase(cell.Value), " AND ") Else ConditionArray = Array(cell.Text) End If End If 'formirajte prvi uslov If Left(ConditionArray(0), 1) = "<" Ili Left( ConditionA rray(0), 1) = ">" Tada Condition1 = ConditionArray(0) Else Condition1 = "=" & ConditionArray(0) End If 'formirajte drugi uslov - ako postoji Ako je UBound(ConditionArray) = 1 Onda ako je lijevo ( ConditionArray(1), 1) = "<" Ili Left(ConditionArray(1), 1) = ">" Tada Condition2 = ConditionArray(1) Drugo Condition2 = "=" & ConditionArray(1) End If End Ako 'Enable filtriranje Ako je UBound(ConditionArray) = 0 Tada Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol, Criteria1:=Condition1 Else Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol, Criteria1 = Condition1, _ Operator:=LogicOperator, Criteria2:=Condition2 End If End If Sljedeća ćelija Postavite FilterRange = Ništa Application.ScreenUpdating = True End Sub

Svi.

Sada, kada unesete bilo koji uvjet u žute ćelije našeg imenovanog raspona, filtriranje će odmah raditi, prikazujući samo linije koje su nam potrebne i skrivajući nepotrebne:

Kao i u slučaju klasičnog Autofilter (Filter) и napredni filter (Napredni filter), u našem makro filteru, možete bezbedno koristiti džoker znakove:

  • * (zvjezdica) – zamjenjuje bilo koji broj bilo kojeg karaktera
  • ? (znak pitanja) – zamjenjuje bilo koji pojedinačni znak

i logički konektivni operatori:

  • И – ispunjenost oba uslova
  • OR – ispunjenje najmanje jednog od dva uslova

i svi simboli matematičke nejednakosti (>,<,=,>=,<=,<>).

Prilikom brisanja sadržaja ćelija žutog raspona uslovi filtriranje se automatski uklanja iz odgovarajućih kolona.

PS

  • Ako imate Excel 2007 ili 2010, ne zaboravite da sačuvate datoteku sa omogućenom makronaredbom (u xlsm formatu), inače će dodati makro nestati.
  • Ovaj makro ne radi sa "pametnim stolovima"

  • Šta su makroi, gde ubaciti makro kod u VBA, kako ih koristiti?
  • Pametne tabele Excel 2007/2010
  • Napredni filter i malo magije

 

Ostavite odgovor