Excel VBA autofilter: Potpuni vodič s primjerima

Dosta Excel funkcija je također dostupno za upotrebu u VBA - i Autofilter metoda je jedna takva funkcionalnost.

Ako imate skup podataka i želite ga filtrirati pomoću kriterija, to možete jednostavno učiniti pomoću opcije Filtriraj na vrpci Podaci.

A ako želite njegovu napredniju verziju, postoji napredni filtar i u Excelu.

Zašto onda uopće koristiti autofilter u VBA?

Ako samo trebate filtrirati podatke i obaviti neke osnovne stvari, preporučio bih da se držite ugrađene funkcije filtra koju nudi Excel sučelje.

Trebali biste koristiti VBA Autofilter kada želite filtrirati podatke kao dio svoje automatizacije (ili ako vam pomaže uštedjeti vrijeme ubrzavajući filtriranje podataka).

Na primjer, pretpostavimo da želite brzo filtrirati podatke na temelju padajućeg odabira, a zatim kopirati te filtrirane podatke u novi radni list.

Iako se to može učiniti pomoću ugrađene funkcije filtra zajedno s nekim copy-pasteom, može vam oduzeti puno vremena da to učinite ručno.

U takvom scenariju upotreba VBA Autofiltera može ubrzati stvari i uštedjeti vrijeme.

Bilješka: Ovaj ću primjer (o filtriranju podataka na temelju padajućeg odabira i kopiranju u novi list) obraditi kasnije u ovom vodiču.

Sintaksa Excel VBA autofiltera

Izraz. Autofilter (_Polje_, _Kriteriji1_, _Operator_, _Kriteriji2_, _VisibleDropDown_)

• Izraz: Ovo je raspon na koji želite primijeniti automatski filtar.
• Polje: [Izborni argument] Ovo je broj stupca koji želite filtrirati. To se broji slijeva u skupu podataka. Dakle, ako želite filtrirati podatke na temelju drugog stupca, ova vrijednost bila bi 2.
• Kriteriji 1: [Izborni argument] Ovo su kriteriji na temelju kojih želite filtrirati skup podataka.
• Operater: [Izborni argument] U slučaju da koristite i kriterij 2, možete kombinirati ta dva kriterija na temelju operatora. Dostupni su sljedeći operatori: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kriteriji 2: [Izborni argument] Ovo je drugi kriterij prema kojem možete filtrirati skup podataka.
• VisibleDropDown: [Izborni argument] Možete odrediti želite li da se padajuća ikona filtra prikazuje u filtriranim stupcima ili ne. Ovaj argument može biti TRUE ili FALSE.

Osim Izraza, svi drugi argumenti su izborni.

U slučaju da ne koristite nijedan argument, jednostavno bi primijenio ili uklonio ikone filtra u stupce.

Radni listovi podfilterskih redaka () ("Podaci filtriranja"). Raspon ("A1"). Završni podfiltriranje automatskog filtriranja

Gornji kôd jednostavno bi primijenio metodu automatskog filtriranja na stupce (ili ako je već primijenjen, uklonit će ga).

To jednostavno znači da ako ne vidite ikone filtra u zaglavljima stupaca, počet ćete ga vidjeti kada se izvrši ovaj gornji kôd, a ako ga vidite, bit će uklonjen.

U slučaju da imate filtrirane podatke, uklonit će filtre i prikazati vam cijeli skup podataka.

Pogledajmo sada neke primjere korištenja programa Excel VBA Autofilter koji će njegovu upotrebu učiniti jasnom.

Primjer: Filtriranje podataka na temelju tekstualnog stanja

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite ga filtrirati na temelju stupca "Stavka".

Kôd u nastavku filtrirao bi sve retke u kojima je stavka "Pisač".

Sub FilterRows () Radni listovi ("List1"). Raspon ("A1"). Polje automatskog filtriranja: = 2, Kriteriji1: = "Pisač" Završni pod

Gornji kôd odnosi se na Sheet1, a unutar njega se odnosi na A1 (koja je ćelija u skupu podataka).

Imajte na umu da smo ovdje koristili polje: = 2, jer je stupac stavke drugi stupac u našem skupu podataka s lijeve strane.

Sada ako razmišljate - zašto moram to učiniti pomoću VBA koda. To se lako može učiniti pomoću ugrađene funkcije filtra.

U pravu si!

Ako je ovo sve što želite učiniti, bolje upotrijebite funkciju ugrađenog filtra.

No, dok budete čitali preostali vodič, vidjet ćete da se to može kombinirati s nekim dodatnim kodom za stvaranje moćne automatizacije.

No prije nego što vam ih pokažem, dopustite mi da prvo obuhvatim nekoliko primjera kako bih vam pokazao što sve može učiniti metoda AutoFilter.

Kliknite ovdje za preuzimanje datoteke primjera i slijedite je.

Primjer: Više kriterija (I/ILI) u istom stupcu

Pretpostavimo da imam isti skup podataka, a ovaj put želim filtrirati sve zapise u kojima je stavka ili "Pisač" ili "Projektor".

Kôd u nastavku bi ovo učinio:

Sub FilterRowsOR () Radni listovi ("List1"). Raspon ("A1"). Polje automatskog filtriranja: = 2, Kriteriji1: = "Pisač", Operater: = xlOr, Kriteriji2: = "Projektor" Završni pod

Imajte na umu da sam ovdje upotrijebio xlOR operater.

To govori VBA -i da koristi oba kriterija i filtrira podatke ako su neki od dva kriterija ispunjeni.

Slično, možete koristiti i kriterije AND.

Na primjer, ako želite filtrirati sve zapise u kojima je količina veća od 10, ali manja od 20, možete upotrijebiti donji kod:

Sub FilterRowsAND () Radni listovi ("Sheet1"). Raspon ("A1"). AutoFilter Field: = 4, Criteria1: = "> 10", _ Operator: = xlAnd, Criteria2: = "<20" End Sub

Primjer: Više kriterija s različitim stupcima

Pretpostavimo da imate sljedeći skup podataka.

Pomoću automatskog filtriranja možete filtrirati više stupaca istovremeno.

Na primjer, ako želite filtrirati sve zapise u kojima je stavka "Pisač", a prodajni predstavnik "Označi", možete upotrijebiti donji kod:

Sub FilterRows () s radnim listovima ("Sheet1"). Raspon ("A1"). Polje AutoFilter: = 2, Criteria1: = "Printer". Polje AutoFilter: = 3, Criteria1: = "Označi" završi s završetkom pod

Primjer: Filtrirajte 10 najboljih zapisa pomoću metode automatskog filtriranja

Pretpostavimo da imate niz podataka u nastavku.

Ispod je kôd koji će vam dati prvih 10 zapisa (na temelju stupca količine):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Polje automatskog filtriranja: = 4, Kriteriji1: = "10", Operator: = xlTop10Items End Sub

U gornjem kodu koristio sam ActiveSheet. Ako želite, možete koristiti naziv lista.

Imajte na umu da u ovom primjeru, ako želite dobiti prvih 5 stavki, samo promijenite broj u Kriterij1: = ”10 ″ od 10 do 5.

Dakle, za top 5 stavki kôd bi bio:

Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). Polje automatskog filtriranja: = 4, Kriteriji1: = "5", Operator: = xlTop10Items End Sub

Možda izgleda čudno, ali bez obzira koliko vrhunskih stavki želite, vrijednost Operatora uvijek ostaje xlTop10Items.

Slično, donji kôd bi vam dao donjih 10 stavki:

Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). Polje automatskog filtriranja: = 4, Kriteriji1: = "10", Operator: = xlBottom10Items End Sub

A ako želite donjih 5 stavki, promijenite broj Kriterij1: = ”10 ″ od 10 do 5.

Primjer: Top 10 posto filtriranja pomoću metode automatskog filtriranja

Pretpostavimo da imate isti skup podataka (kao što je korišten u prethodnim primjerima).

Ispod je kôd koji će vam dati prvih 10 posto zapisa (na temelju stupca količine):

Pod FilterRowsTop10 () ActiveSheet.Range ("A1"). Polje automatskog filtriranja: = 4, Kriteriji1: = "10", Operator: = xlTop10Percent End Sub

U našem skupu podataka, budući da imamo 20 zapisa, vratit će se prva 2 zapisa (što je 10% od ukupnog broja zapisa).

Primjer: Korištenje zamjenskih znakova u automatskom filtriranju

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:

Ako želite filtrirati sve retke u kojima naziv stavke sadrži riječ 'ploča', možete upotrijebiti donji kod:

Sub FilterRowsWildcard () Radni listovi ("List1"). Raspon ("A1"). Polje automatskog filtriranja: = 2, Kriteriji1: = "*Ploča*" Završi pod

U gornjem kodu koristio sam zamjenski znak * (zvjezdica) prije i poslije riječi 'ploča' (što je kriterij).

Zvjezdica može predstavljati bilo koji broj znakova. Dakle, ovo bi filtriralo svaku stavku koja u sebi ima riječ "ploča".

Primjer: Kopirajte filtrirane redove u novi list

Ako ne želite samo filtrirati zapise na temelju kriterija, već i kopirati filtrirane retke, možete upotrijebiti donju makronaredbu.

Kopira filtrirane retke, dodaje novi radni list, a zatim lijepi ove kopirane retke u novi list.

Sub CopyFilteredRows () Dim rng as Raspon Dim ws Kao radni list Ako radni listovi ("Sheet1"). AutoFilterMode = False Then MsgBox "Nema filtriranih redaka" Exit Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Radni listovi.Dodaj rng.Kopiraj raspon ("A1") Kraj podv

Gornji kôd bi provjerio postoje li filtrirani retci u Sheet1 ili ne.

Ako nema filtriranih redaka, prikazat će se okvir s porukom o tome.

A ako postoje filtrirani retci, kopirat će ih, umetnuti novi radni list i zalijepiti te retke na taj novo umetnuti radni list.

Primjer: Filtrirajte podatke na temelju vrijednosti ćelije

Koristeći Autofilter u VBA-i zajedno s padajućim popisom možete stvoriti funkcionalnost u kojoj se svi zapisi za tu stavku filtriraju čim odaberete stavku s padajućeg izbornika.

Nešto kao što je prikazano ispod:

Kliknite ovdje za preuzimanje datoteke primjera i slijedite je.

Ova vrsta konstrukcije može biti korisna kada želite brzo filtrirati podatke, a zatim ih dalje koristiti u svom radu.

Ispod je kôd koji će to učiniti:

Privatni pod -radni list_Promijeni (ByVal cilj kao raspon) If Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, Kriterij1: = Raspon ("B2") Kraj ako kraj Ako završi pod

Ovo je kôd događaja radnog lista, koji se izvršava samo kada dođe do promjene na radnom listu, a ciljna ćelija je B2 (gdje imamo padajući izbornik).

Također, uvjet If Then Else koristi se za provjeru je li korisnik s padajućeg izbornika odabrao 'Sve'. Ako je odabrano Sve, prikazuje se cijeli skup podataka.

Ovaj kod se NE stavlja u modul.

Umjesto toga, potrebno ga je postaviti u pozadinu radnog lista koji sadrži te podatke.

Evo koraka za stavljanje ovog koda u prozor koda radnog lista:

1. Otvorite VB Editor (tipkovnički prečac - ALT + F11).
2. U oknu Project Explorer dvaput kliknite naziv radnog lista u kojem želite ovu funkciju filtriranja.
3. U prozoru koda radnog lista kopirajte i zalijepite gornji kôd.
4. Zatvorite VB Editor.

Sada kada koristite padajući popis, automatski će filtrirati podatke.

Ovo je kôd događaja radnog lista, koji se izvršava samo kada dođe do promjene na radnom listu, a ciljna ćelija je B2 (gdje imamo padajući izbornik).

Također, uvjet If Then Else koristi se za provjeru je li korisnik s padajućeg izbornika odabrao 'Sve'. Ako je odabrano Sve, prikazuje se cijeli skup podataka.

Uključite/isključite Excel AutoFilter pomoću VBA

Prilikom primjene automatskog filtriranja na niz ćelija možda su već postavljeni neki filtri.

Pomoću donjeg koda možete isključiti sve prethodno primijenjene automatske filtre:

Sub TurnOFFAutoFilter () Radni listovi ("Sheet1"). AutoFilterMode = False End Sub

Ovaj kôd provjerava cijele listove i uklanja sve primijenjene filtre.

Ako ne želite isključiti filtre iz cijelog lista, već samo iz određenog skupa podataka, upotrijebite donji kod:

Sub TurnOFFAutoFilter () If Worksheets ("Sheet1"). Range ("A1"). AutoFilter then Worksheets ("Sheet1"). Range ("A1"). AutoFilter End If End Sub

Gornji kod provjerava postoje li već postavljeni filtri ili ne.

Ako su filtri već primijenjeni, uklanja ih, inače ništa ne radi.

Slično, ako želite uključiti Autofilter, upotrijebite donji kod:

Sub TurnOnAutoFilter () Ako nije Radni listovi ("List1"). Raspon ("A4"). Automatski filtriranje Zatim radni listovi ("List1"). Raspon ("A4"). Autofilter završava ako završava pod

Provjerite je li autofilter već primijenjen

Ako imate list s više skupova podataka i želite biti sigurni da znate da već nema filtera, možete upotrijebiti donji kod.

Sub CheckforFilters () If ActiveSheet.AutoFilterMode = True then MsgBox "Već postoje filteri" Ostalo MsgBox "Nema filtera" End If End Sub

Ovaj kôd koristi funkciju okvira s porukama koja prikazuje poruku "Već postoje filteri" kada pronađe filtre na listu, inače prikazuje "Nema filtera".

Prikaži sve podatke

Ako ste na skup podataka primijenili filtre i želite prikazati sve podatke, upotrijebite donji kod:

Sub ShowAllData () Ako ActiveSheet.FilterMode Zatim ActiveSheet.ShowAllData Završi pod

Gornji kod provjerava je li FilterMode TRUE ili FALSE.

Ako je istina, znači da je primijenjen filtar i koristi metodu ShowAllData za prikaz svih podataka.

Imajte na umu da se time ne uklanjaju filtri. Ikone filtera i dalje su dostupne za upotrebu.

Korištenje automatskog filtriranja na zaštićenim listovima

Prema zadanim postavkama, kada zaštitite list, filtri neće raditi.

U slučaju da već imate postavljene filtre, možete omogućiti AutoFilter kako biste bili sigurni da radi čak i na zaštićenim listovima.

Da biste to učinili, označite opciju Koristi autofilter dok štitite list.

Iako ovo funkcionira kad već imate postavljene filtre, u slučaju da pokušate dodati autofiltere pomoću VBA koda, to neće funkcionirati.

Budući da je list zaštićen, ne dopušta pokretanje nijedne makronaredbe i izmjene automatskog filtra.

Stoga morate koristiti kôd za zaštitu radnog lista i provjeriti jesu li u njemu omogućeni automatski filtri.

To može biti korisno ako ste stvorili dinamički filtar (nešto što sam opisao u primjeru - "Filtriranje podataka na temelju vrijednosti ćelije").

Dolje se nalazi kôd koji će štititi list, ali vam u isto vrijeme omogućiti korištenje filtara kao i VBA makronaredbi u njemu.

Private Sub Workbook_Open () With Worksheets ("Sheet1") .EnableAutoFilter = True. Lozinka za zaštitu: = "password", Sadržaj: = True, UserInterfaceOnly: = True End With End Sub

Ovaj kôd treba staviti u prozor koda ThisWorkbook.

Evo koraka za postavljanje koda u prozor koda ThisWorkbook:

1. Otvorite VB Editor (tipkovnički prečac - ALT + F11).
2. U oknu Project Explorer dvaput kliknite objekt ThisWorkbook.
3. U prozoru koda koji se otvori kopirajte i zalijepite gornji kôd.

Čim otvorite radnu knjigu i omogućite makronaredbe, ona će automatski pokrenuti makro i zaštititi Sheet1.

Međutim, prije nego što to učini, navest će ‘EnableAutoFilter = True’, što znači da bi filtri radili i u zaštićenom listu.

Također, postavlja argument 'UserInterfaceOnly' na 'True'. To znači da će, dok je radni list zaštićen, kod VBA makronaredbi nastaviti raditi.

Možda će vam se svidjeti i sljedeći VBA vodiči:

• Excel VBA petlje.
• Filtrirajte ćelije s podebljanim fontom.
• Snimanje makronaredbi.
• Sortiranje podataka pomoću VBA.
• Poredajte kartice radnog lista u Excelu.