O mulțime de funcționalități Excel sunt, de asemenea, disponibile pentru a fi utilizate în VBA - și în Autofiltru metoda este o astfel de funcționalitate.
Dacă aveți un set de date și doriți să îl filtrați utilizând un criteriu, îl puteți face cu ușurință folosind opțiunea Filtrare din panglica Date.
Și dacă doriți o versiune mai avansată a acesteia, există și un filtru avansat în Excel.
Atunci de ce să folosim chiar și AutoFilter în VBA?
Dacă trebuie doar să filtrați date și să faceți câteva lucruri de bază, vă recomand să rămâneți la funcționalitatea de filtrare încorporată oferită de interfața Excel.
Ar trebui să utilizați VBA Autofilter atunci când doriți să filtrați datele ca parte a automatizării dvs. (sau dacă vă ajută să economisiți timp, facilitând filtrarea datelor).
De exemplu, să presupunem că doriți să filtrați rapid datele pe baza unei selecții derulante, apoi să copiați aceste date filtrate într-o nouă foaie de lucru.
În timp ce acest lucru se poate face folosind funcționalitatea filtrului încorporat împreună cu o copiere-lipire, vă poate lua mult timp să faceți acest lucru manual.
Într-un astfel de scenariu, utilizarea VBA Autofilter poate accelera lucrurile și poate economisi timp.
Notă: Voi acoperi acest exemplu (despre filtrarea datelor bazate pe o selecție drop-down și copierea într-o foaie nouă) mai târziu în acest tutorial.
Sintaxa autofiltrului Excel VBA
Expresie. AutoFilter (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)
- Expresie: Acesta este intervalul pe care doriți să aplicați filtrul automat.
- Camp: [Argument opțional] Acesta este numărul coloanei pe care doriți să o filtrați. Acest lucru este numărat din stânga în setul de date. Deci, dacă doriți să filtrați datele pe baza celei de-a doua coloane, această valoare ar fi 2.
- Criterii1: [Argument opțional] Acesta este criteriul pe baza căruia doriți să filtrați setul de date.
- Operator: [Argument opțional] În cazul în care utilizați și criteriile 2, puteți combina aceste două criterii pe baza operatorului. Următorii operatori sunt disponibili pentru utilizare: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
- Criterii2: [Argument opțional] Acesta este al doilea criteriu pe baza căruia puteți filtra setul de date.
- VisibleDropDown: [Argument opțional] Puteți specifica dacă doriți ca pictograma drop-down a filtrului să apară sau nu în coloanele filtrate. Acest argument poate fi ADEVĂRAT sau FALS.
În afară de Expression, toate celelalte argumente sunt opționale.
În cazul în care nu utilizați niciun argument, acesta ar aplica pur și simplu și va elimina pictogramele filtrului pe coloane.
Sub FilterRows () Foi de lucru ("Filter Data"). Range ("A1"). AutoFilter End Sub
Codul de mai sus ar aplica pur și simplu metoda Autofilter coloanelor (sau dacă este deja aplicat, îl va elimina).
Acest lucru înseamnă pur și simplu că, dacă nu puteți vedea pictogramele de filtrare din antetele coloanei, veți începe să le vedeți când este executat acest cod de mai sus și, dacă îl puteți vedea, atunci acesta va fi eliminat.
În cazul în care aveți date filtrate, acesta va elimina filtrele și vă va arăta setul de date complet.
Acum, să vedem câteva exemple de utilizare a autofiltrului Excel VBA, care vor face clar utilizarea sa.
Exemplu: Filtrarea datelor pe baza unei condiții Text
Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să îl filtrați pe baza coloanei „Element”.
Codul de mai jos ar filtra toate rândurile în care elementul este „Imprimantă”.
Sub FilterRows () Foi de lucru ("Sheet1"). Range ("A1"). Câmpul AutoFilter: = 2, Criteria1: = "Printer" End Sub
Codul de mai sus se referă la Sheet1 și, în cadrul acestuia, se referă la A1 (care este o celulă din setul de date).
Rețineți că aici am folosit Câmpul: = 2, deoarece coloana element este a doua coloană din setul nostru de date din stânga.
Acum, dacă vă gândiți - de ce trebuie să fac asta folosind un cod VBA. Acest lucru se poate face cu ușurință folosind funcționalitatea filtrului încorporat.
Ai dreptate!
Dacă aceasta este tot ceea ce doriți să faceți, utilizați mai bine funcționalitatea de filtrare încorporată.
Dar pe măsură ce citiți tutorialul rămas, veți vedea că acest lucru poate fi combinat cu un cod suplimentar pentru a crea o automatizare puternică.
Dar, înainte de a vă arăta acelea, permiteți-mi să vă acoper mai întâi câteva exemple pentru a vă arăta ce poate face toată metoda AutoFilter.
Click aici pentru a descărca fișierul de exemplu și a continua.
Exemplu: Criterii multiple (ȘI / SAU) în aceeași coloană
Să presupunem că am același set de date și de data aceasta vreau să filtrez toate înregistrările în care elementul este fie „Imprimantă”, fie „Proiector”.
Codul de mai jos ar face acest lucru:
Sub FilterRowsOR () Foi de lucru ("Sheet1"). Range ("A1"). Câmpul AutoFilter: = 2, Criteria1: = "Printer", Operator: = xlOr, Criteria2: = "Proiector" End Sub
Rețineți că aici am folosit xlOR operator.
Acest lucru îi spune VBA să utilizeze ambele criterii și să filtreze datele dacă oricare dintre cele două criterii sunt îndeplinite.
În mod similar, puteți utiliza și criteriile ȘI.
De exemplu, dacă doriți să filtrați toate înregistrările în care cantitatea este mai mare de 10, dar mai mică de 20, puteți utiliza codul de mai jos:
Sub FilterRowsAND () Foi de lucru ("Sheet1"). Range ("A1"). Câmpul AutoFilter: = 4, Criteria1: = "> 10", _ Operator: = xlAnd, Criteria2: = "<20" End Sub
Exemplu: Criterii multiple cu coloane diferite
Să presupunem că aveți următorul set de date.
Cu Autofilter, puteți filtra mai multe coloane în același timp.
De exemplu, dacă doriți să filtrați toate înregistrările în care articolul este „Printer” și reprezentantul de vânzări este „Mark”, puteți utiliza codul de mai jos:
Sub FilterRows () With Worksheets ("Sheet1"). Range ("A1"). Câmpul AutoFilter: = 2, Criteria1: = "Printer". Câmpul AutoFilter: = 3, Criteria1: = "Mark" End With End Sub
Exemplu: Filtrați primele 10 înregistrări folosind metoda AutoFilter
Să presupunem că aveți setul de date de mai jos.
Mai jos este codul care vă va oferi primele 10 înregistrări (pe baza coloanei cantității):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Câmpul AutoFilter: = 4, Criteria1: = "10", Operator: = xlTop10Items End Sub
În codul de mai sus, am folosit ActiveSheet. Puteți utiliza numele foii, dacă doriți.
Rețineți că, în acest exemplu, dacă doriți să obțineți primele 5 articole, schimbați doar numărul Criteriul 1: = ”10 ″ de la 10 la 5.
Deci, pentru primele 5 articole, codul ar fi:
Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). Câmpul AutoFilter: = 4, Criteria1: = "5", Operator: = xlTop10Items End Sub
Poate părea ciudat, dar indiferent de câte elemente de top doriți, valoarea Operator rămâne întotdeauna xlTop10Items.
În mod similar, codul de mai jos vă va oferi primele 10 elemente:
Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). Câmpul AutoFilter: = 4, Criteria1: = "10", Operator: = xlBottom10Items End Sub
Și dacă doriți primele 5 elemente, schimbați numărul în Criteriul 1: = ”10 ″ de la 10 la 5.
Exemplu: Filtrați Top 10 procente folosind metoda AutoFilter
Să presupunem că aveți același set de date (ca în exemplele anterioare).
Mai jos este codul care vă va oferi primele înregistrări de 10% (pe baza coloanei cantității):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Câmpul AutoFilter: = 4, Criteria1: = "10", Operator: = xlTop10Percent End Sub
În setul nostru de date, deoarece avem 20 de înregistrări, acesta va returna primele 2 înregistrări (care reprezintă 10% din totalul înregistrărilor).
Exemplu: Utilizarea caracterelor wildcard în Autofilter
Să presupunem că aveți un set de date așa cum se arată mai jos:
Dacă doriți să filtrați toate rândurile în care numele articolului conține cuvântul „Board”, puteți utiliza codul de mai jos:
Sub FilterRowsWildcard () Foi de lucru ("Sheet1"). Range ("A1"). Câmpul AutoFilter: = 2, Criteria1: = "* Board *" End Sub
În codul de mai sus, am folosit caracterul wildcard * (asterisc) înainte și după cuvântul „Board” (care este criteriul).
Un asterisc poate reprezenta orice număr de caractere. Deci, acest lucru ar filtra orice element care conține cuvântul „bord”.
Exemplu: Copiați rândurile filtrate într-o foaie nouă
Dacă doriți să nu numai să filtrați înregistrările pe baza criteriilor, ci să copiați și rândurile filtrate, puteți utiliza macro-ul de mai jos.
Copiază rândurile filtrate, adaugă o nouă foaie de lucru și apoi lipeste aceste rânduri copiate în foaia nouă.
Sub CopyFilteredRows () Dim rng As Range Dim ws As Worksheet if Worksheets ("Sheet1"). AutoFilterMode = False Then MsgBox "Nu există rânduri filtrate" Exit Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") End Sub
Codul de mai sus ar verifica dacă există sau nu rânduri filtrate în Sheet1.
Dacă nu există rânduri filtrate, va apărea o casetă de mesaj care să precizeze că.
Și dacă există rânduri filtrate, acesta le va copia, va insera o foaie de lucru nouă și va lipi aceste rânduri pe foaia de lucru nou inserată.
Exemplu: filtrați datele pe baza unei valori a celulei
Folosind Autofilter în VBA împreună cu o listă derulantă, puteți crea o funcționalitate în care imediat ce selectați un element din meniul derulant, toate înregistrările pentru acel element sunt filtrate.
Ceva așa cum se arată mai jos:
Click aici pentru a descărca fișierul de exemplu și a continua.
Acest tip de construcție poate fi util atunci când doriți să filtrați rapid datele și apoi să le utilizați în continuare în munca dvs.
Mai jos este codul care va face acest lucru:
Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). AutoFilter Else Range ("A5"). Câmpul AutoFilter : = 2, Criterii1: = Interval ("B2") End If End If End Sub
Acesta este un cod de eveniment al foii de lucru, care se execută numai atunci când există o modificare în foaia de lucru și celula țintă este B2 (unde avem meniul derulant).
De asemenea, se folosește o condiție If Then Else pentru a verifica dacă utilizatorul a selectat „All” din meniul derulant. Dacă este selectat Tot, este afișat întregul set de date.
Acest cod NU este plasat într-un modul.
În schimb, trebuie plasat în backend-ul foii de lucru care conține aceste date.
Iată pașii pentru a introduce acest cod în fereastra codului foii de lucru:
- Deschideți Editorul VB (comandă rapidă de la tastatură - ALT + F11).
- În panoul Project Explorer, faceți dublu clic pe numele Foaiei de lucru în care doriți această funcționalitate de filtrare.
- În fereastra codului foii de lucru, copiați și lipiți codul de mai sus.
- Închideți Editorul VB.
Acum, când utilizați lista derulantă, aceasta va filtra automat datele.
Acesta este un cod de eveniment al foii de lucru, care se execută numai atunci când există o modificare în foaia de lucru și celula țintă este B2 (unde avem meniul derulant).
De asemenea, se folosește o condiție If Then Else pentru a verifica dacă utilizatorul a selectat „All” din meniul derulant. Dacă este selectat Tot, este afișat întregul set de date.
Porniți / dezactivați filtrul automat Excel folosind VBA
Când aplicați Autofilter pe o gamă de celule, este posibil să existe deja unele filtre la locul lor.
Puteți utiliza codul de mai jos pentru a dezactiva orice filtre auto pre-aplicate:
Sub TurnOFFAutoFilter () Foi de lucru ("Sheet1"). AutoFilterMode = Sub-End False
Acest cod verifică întreaga foaie și elimină orice filtre care au fost aplicate.
Dacă nu doriți să dezactivați filtrele din întreaga foaie, ci doar dintr-un anumit set de date, utilizați codul de mai jos:
Sub TurnOFFAutoFilter () If Worksheets ("Sheet1"). Range ("A1"). AutoFilter Then Worksheets ("Sheet1"). Range ("A1"). AutoFilter End If End Sub
Codul de mai sus verifică dacă există deja filtre în loc sau nu.
Dacă filtrele sunt deja aplicate, acesta îl elimină, altfel nu face nimic.
În mod similar, dacă doriți să activați AutoFilter, utilizați codul de mai jos:
Sub TurnOnAutoFilter () If Not Worksheets ("Sheet1"). Range ("A4"). AutoFilter Then Worksheets ("Sheet1"). Range ("A4"). AutoFilter End If End Sub
Verificați dacă Filtrul automat este deja aplicat
Dacă aveți o foaie cu mai multe seturi de date și doriți să vă asigurați că știți că nu există filtre deja instalate, puteți utiliza codul de mai jos.
Sub CheckforFilters () Dacă ActiveSheet.AutoFilterMode = Adevărat, atunci MsgBox "Există filtre deja în loc" Else MsgBox "Nu există filtre" Încheie dacă se termină Sub
Acest cod utilizează o funcție de casetă de mesaje care afișează un mesaj „Există filtre deja la locul lor” când găsește filtre pe foaie, altfel afișează „Nu există filtre”.
Afișați toate datele
Dacă ați aplicat filtre setului de date și doriți să afișați toate datele, utilizați codul de mai jos:
Sub ShowAllData () Dacă ActiveSheet.FilterMode, apoi ActiveSheet.ShowAllData End Sub
Codul de mai sus verifică dacă FilterMode este TRUE sau FALSE.
Dacă este adevărat, înseamnă că a fost aplicat un filtru și folosește metoda ShowAllData pentru a afișa toate datele.
Rețineți că acest lucru nu elimină filtrele. Pictogramele de filtrare sunt încă disponibile pentru a fi utilizate.
Utilizarea filtrului automat pe foi protejate
În mod implicit, când protejați o foaie, filtrele nu vor funcționa.
În cazul în care aveți deja filtre la locul lor, puteți activa AutoFilter pentru a vă asigura că funcționează chiar și pe foi protejate.
Pentru a face acest lucru, bifați opțiunea Utilizare autofiltru în timp ce protejați foaia.
În timp ce acest lucru funcționează când aveți deja la dispoziție filtre, în cazul în care încercați să adăugați filtre automate folosind un cod VBA, acesta nu va funcționa.
Deoarece foaia este protejată, nu ar permite nicio macro să ruleze și să facă modificări la Autofilter.
Deci, trebuie să utilizați un cod pentru a proteja foaia de lucru și a vă asigura că filtrele automate sunt activate în ea.
Acest lucru poate fi util atunci când ați creat un filtru dinamic (ceva ce am tratat în exemplu - „Filtrarea datelor bazate pe o valoare a celulei”).
Mai jos este codul care va proteja foaia, dar, în același timp, vă permite să utilizați filtrele, precum și macrocomenzile VBA în ea.
Private Sub Workbook_Open () With Worksheets ("Sheet1") .EnableAutoFilter = True .Protect Password: = "parola", Conținut: = True, UserInterfaceOnly: = True End With End Sub
Acest cod trebuie plasat în fereastra de cod ThisWorkbook.
Iată pașii pentru a pune codul în fereastra de cod ThisWorkbook:
- Deschideți Editorul VB (comandă rapidă de la tastatură - ALT + F11).
- În panoul Explorator de proiecte, faceți dublu clic pe obiectul ThisWorkbook.
- În fereastra de cod care se deschide, copiați și lipiți codul de mai sus.
De îndată ce deschideți registrul de lucru și activați macrocomenzile, acesta va rula macro-ul automat și va proteja Sheet1.
Cu toate acestea, înainte de a face acest lucru, va specifica „EnableAutoFilter = True”, ceea ce înseamnă că filtrele ar funcționa și în foaia protejată.
De asemenea, setează argumentul „UserInterfaceOnly” la „True”. Aceasta înseamnă că, în timp ce foaia de lucru este protejată, codul de macrocomenzi VBA ar continua să funcționeze.
Vă pot plăcea, de asemenea, următoarele tutoriale VBA:
- Bucle VBA Excel.
- Filtrează celulele cu formatarea fonturilor în gras.
- Înregistrarea unei macro.
- Sortează datele folosind VBA.
- Sortați filele foii de lucru în Excel.
