Excel VBA automatikus szűrő: A Complete Guide with Examples

Egy csomó Excel-funkció VBA-ban is használható – és az Autofilter módszer is egy ilyen funkció.

Ha van egy adathalmazunk, és azt egy kritérium alapján szeretnénk szűrni, akkor ezt könnyen megtehetjük az Adat szalag Szűrő opciójával.

És ha ennek egy fejlettebb változatát szeretnénk, akkor van egy speciális szűrő is az Excelben.

Miért használja egyáltalán az automatikus szűrőt a VBA-ban?

Ha csak adatokat kell szűrni és néhány alapvető dolgot elvégezni, akkor azt javaslom, hogy maradjon a beépített szűrőfunkciónál, amelyet az Excel felülete kínál.

A VBA automatikus szűrőt akkor érdemes használni, ha az adatokat az automatizálás részeként szeretné szűrni (vagy ha az adatok gyorsabb szűrésével segít időt megtakarítani).

Tegyük fel például, hogy gyorsan szeretné szűrni az adatokat egy legördülő kiválasztás alapján, majd ezt a szűrt adatot átmásolni egy új munkalapra.

Míg ez elvégezhető a beépített szűrőfunkcióval és némi másolás-beillesztéssel együtt, sok időt vehet igénybe, ha ezt kézzel végzi.

Egy ilyen forgatókönyvben a VBA Autofilter használata felgyorsíthatja a dolgokat és időt takaríthat meg.

Megjegyzés: Ezt a példát (az adatok legördülő kiválasztáson alapuló szűréséről és az új lapra történő másolásról) a bemutató későbbi részében fogom tárgyalni.

Excel VBA Autofilter szintaxis

Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• Kifejezés:
• Mező: Ez az oszlop száma, amelyet szűrni szeretne. Ezt balról számoljuk az adathalmazban. Tehát ha a második oszlop alapján szeretné szűrni az adatokat, akkor ez az érték 2.
• Criteria1: Ez az a kritérium, amely alapján szűrni szeretné az adathalmazt.
• Operator: Operator: Abban az esetben, ha a 2. kritériumot is használja, akkor ezt a két kritériumot kombinálhatja az Operator alapján. A következő operátorok használhatók: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Criteria2: Ez a második kritérium, amely alapján szűrheti az adatállományt.
• VisibleDropDown: Megadhatja, hogy a szűrő legördülő ikonja megjelenjen-e a szűrt oszlopokban vagy sem. Ez az argumentum lehet TRUE vagy FALSE.

Az Expressionon kívül az összes többi argumentum opcionális.

Ha egyik argumentumot sem használja, a program egyszerűen alkalmazza vagy eltávolítja a szűrő ikonokat az oszlopokra.

Sub FilterRows()Worksheets("Filter Data").Range("A1").AutoFilterEnd Sub

A fenti kód egyszerűen alkalmazza az Autofilter módszert az oszlopokra (vagy ha már alkalmazta, akkor eltávolítja).

Ez egyszerűen azt jelenti, hogy ha nem látja a szűrő ikonokat az oszlopok fejlécében, akkor a fenti kód végrehajtásakor elkezdi látni, és ha látja, akkor eltávolítja.

Ha vannak szűrt adatok, akkor eltávolítja a szűrőket és megmutatja a teljes adathalmazt.

Most nézzünk néhány példát az Excel VBA Autofilter használatára, amelyek egyértelművé teszik a használatát.

Példa:

Az alábbi kód az összes olyan sort szűrné, ahol az elem a ‘Printer’.

Sub FilterRows()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer"End Sub

A fenti kód a Sheet1-re hivatkozik, azon belül pedig az A1-re (ami az adatkészlet egyik cellája).

Megjegyezzük, hogy itt Field:=2-t használtunk, mivel az Item oszlop a második oszlop az adatkészletünkben balról.

De mielőtt ezeket megmutatnám, először néhány példán keresztül mutatom meg, hogy mi mindenre képes az AutoFilter módszer.

Kattintson ide a példa fájl letöltéséhez és kövesse végig.

Példa: Több kritérium (AND/OR) ugyanabban az oszlopban

Tegyük fel, hogy ugyanazzal az adathalmazzal rendelkezem, és ezúttal az összes olyan rekordot szeretném szűrni, ahol az elem vagy ‘Printer’ vagy ‘Projector’.

Az alábbi kód ezt tenné:

Sub FilterRowsOR()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector"End Sub

Megjegyzem, hogy itt az xlOR operátort használtam.

Ez azt mondja a VBA-nak, hogy mindkét kritériumot használja, és szűrje az adatokat, ha a két kritérium bármelyike teljesül.

Hasonlóképpen használhatja az ÉS kritériumot is.

Ha például az összes olyan rekordot szeretné szűrni, ahol a mennyiség több mint 10, de kevesebb mint 20, akkor az alábbi kódot használhatja:

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

Példa: Több kritérium különböző oszlopokkal

Tegyük fel, hogy a következő adatkészlettel rendelkezik.

Az automatikus szűrővel egyszerre több oszlopot is szűrhet.

Ha például az összes olyan rekordot szeretné szűrni, ahol a tétel “Nyomtató” és az értékesítési képviselő “Mark”, akkor az alábbi kódot használhatja:

Sub FilterRows()With Worksheets("Sheet1").Range("A1").AutoFilter field:=2, Criteria1:="Printer".AutoFilter field:=3, Criteria1:="Mark"End WithEnd Sub

Példa: Top 10 rekord szűrése az AutoFilter módszerrel

Tegyük fel, hogy az alábbi adatkészlettel rendelkezik.

Az alábbi kód a top 10 rekordot adja meg (a mennyiség oszlop alapján):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10ItemsEnd Sub

A fenti kódban az ActiveSheet. Ha akarja, használhatja a lap nevét is.

Megjegyezzük, hogy ebben a példában, ha a top 5 elemet szeretné megkapni, csak változtassa meg a Criteria1:=”10″ számot 10-ről 5-re.

A top 5 elemhez tehát a kód a következő:

Sub FilterRowsTop5()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10ItemsEnd Sub

Ez furcsának tűnhet, de nem számít, hogy hány top elemet szeretne, az Operator értéke mindig xlTop10Items marad.

Hasonlóképpen, az alábbi kód az alsó 10 elemet adná:

Sub FilterRowsBottom10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10ItemsEnd Sub

És ha az alsó 5 elemet szeretné, akkor a Criteria1:=”10″ értékben a számot 10 helyett 5-re változtassa.

Példa: A felső 10 százalék szűrése az AutoFilter módszerrel

Tegyük fel, hogy ugyanazzal az adathalmazzal rendelkezünk (mint amit az előző példákban használtunk).

Az alábbi kód a felső 10 százalékos rekordokat fogja megadni (a mennyiség oszlop alapján):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10PercentEnd Sub

A mi adathalmazunkban, mivel 20 rekordunk van, a felső 2 rekordot fogja visszaadni (ami az összes rekord 10%-a).

Példa: Joker karakterek használata az automatikus szűrőben

Tegyük fel, hogy van egy adathalmazunk az alábbiak szerint:

Ha az összes olyan sort szeretnénk szűrni, ahol az elem neve tartalmazza a ‘Board’ szót, akkor az alábbi kódot használhatjuk:

Sub FilterRowsWildcard()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*"End Sub

A fenti kódban a ‘Board’ szó előtt és után (ami a kritérium) a * (csillag) joker karaktert használtam.

A csillag tetszőleges számú karaktert jelölhet. Ez tehát minden olyan elemet kiszűr, amelyben a ‘board’ szó szerepel.

Példa: Szűrt sorok másolása egy új lapba

Ha nem csak szűrni szeretné a rekordokat a kritériumok alapján, hanem a szűrt sorokat másolni is szeretné, akkor használhatja az alábbi makrót.

Másolja a szűrt sorokat, új munkalapot ad hozzá, majd ezeket a másolt sorokat beilleszti az új lapba.

Sub CopyFilteredRows()Dim rng As RangeDim ws As WorksheetIf Worksheets("Sheet1").AutoFilterMode = False ThenMsgBox "There are no filtered rows"Exit SubEnd IfSet rng = Worksheets("Sheet1").AutoFilter.RangeSet ws = Worksheets.Addrng.Copy Range("A1")End Sub

A fenti kód megvizsgálja, hogy vannak-e szűrt sorok a Sheet1-ben vagy sem.

Ha nincsenek szűrt sorok, akkor ezt egy üzenőmezőben jelzi.

Ha pedig vannak szűrt sorok, akkor azokat lemásolja, új munkalapot illeszt be, és ezeket a sorokat beilleszti az újonnan beillesztett munkalapra.

Példa: Adatok szűrése egy cella értéke alapján

A VBA automatikus szűrőjének használatával egy legördülő listával együtt létrehozhat egy olyan funkciót, amelyben amint kiválaszt egy elemet a legördülő listából, az összes rekordot megszűri az adott elemhez.

Az alábbiak szerint:

Kattintson ide a példa fájl letöltéséhez, és kövesse végig.

Ez a fajta konstrukció hasznos lehet, ha gyorsan szeretnénk adatokat szűrni, majd a továbbiakban felhasználni a munkánk során.

Az alábbiakban az ezt megvalósító kód látható:

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$B" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2") End IfEnd IfEnd Sub

Ez egy munkalap eseménykód, amely csak akkor hajtódik végre, ha változás történik a munkalapon, és a célcella a B2 (ahol a legördülő ablak van).

Ezeken kívül egy If Then Else feltétel segítségével ellenőrizzük, hogy a felhasználó a legördülő ablakból kiválasztotta-e a ‘Mind’ lehetőséget. Ha az All van kiválasztva, akkor a teljes adathalmaz megjelenik.

Ezt a kódot NEM egy modulban kell elhelyezni.

Ehelyett az adatokat tartalmazó munkalap hátoldalán kell elhelyezni.

Az alábbi lépésekkel helyezheti el ezt a kódot a munkalap kódablakában:

1. Nyissa meg a VB-szerkesztőt (billentyűparancs – ALT + F11).
2. A Projektfelügyelő ablaktáblában kattintson duplán a munkalap nevére, amelyben ezt a szűrési funkciót szeretné.
3. A munkalap kódablakában másolja be a fenti kódot.
4. Zárja be a VB-szerkesztőt.

Most a legördülő lista használatakor automatikusan szűrni fogja az adatokat.

Ez egy munkalap eseménykód, amely csak akkor hajtódik végre, ha változás történik a munkalapon, és a célcella B2 (ahol a legördülő lista van).

Még egy If Then Else feltétel segítségével ellenőrizzük, hogy a felhasználó a legördülő listából kiválasztotta-e az ‘All’ lehetőséget. Ha az All van kiválasztva, akkor a teljes adathalmaz jelenik meg.

Az Excel automatikus szűrő be- és kikapcsolása VBA segítségével

Az automatikus szűrő alkalmazása során egy cellatartományra már lehet, hogy van néhány szűrő a helyén.

Az alábbi kóddal kikapcsolhatja az előre alkalmazott automatikus szűrőket:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = FalseEnd Sub

Ez a kód ellenőrzi a teljes lapot, és eltávolítja a már alkalmazott szűrőket.

Ha nem a teljes lapról, hanem csak egy adott adathalmazról szeretné kikapcsolni a szűrőket, akkor használja az alábbi kódot:

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

A fenti kód ellenőrzi, hogy vannak-e már alkalmazott szűrők vagy sem.

Ha vannak már alkalmazott szűrők, akkor eltávolítja őket, egyébként nem csinál semmit.

Hasonlóképpen, ha be akarja kapcsolni az automatikus szűrést, használja az alábbi kódot:

Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End IfEnd Sub

Check if AutoFilter is Already Applied

Ha egy lapon több adatkészlet van, és meg akar győződni arról, hogy nincsenek-e már szűrők, használhatja az alábbi kódot.

Sub CheckforFilters()If ActiveSheet.AutoFilterMode = True ThenMsgBox "There are Filters already in place"ElseMsgBox "There are no filters"End IfEnd Sub

Ez a kód egy üzenetdoboz funkciót használ, amely megjeleníti a ‘There are Filters already in place’ üzenetet, ha szűrőket talál a lapon, egyébként a ‘There are no filters’ üzenetet jeleníti meg.

Összes adat megjelenítése

Ha az adatállományra szűrőket alkalmaztunk, és szeretnénk az összes adatot megjeleníteni, használjuk az alábbi kódot:

Sub ShowAllData()If ActiveSheet.FilterMode Then ActiveSheet.ShowAllDataEnd Sub

A fenti kód ellenőrzi, hogy a FilterMode értéke TRUE vagy FALSE.

Ha true, akkor ez azt jelenti, hogy egy szűrő lett alkalmazva, és a ShowAllData metódust használja az összes adat megjelenítéséhez.

Megjegyezzük, hogy ez nem távolítja el a szűrőket. A szűrőikonok továbbra is használhatóak.

Automatikus szűrő használata védett lapokon

Alapértelmezés szerint, ha egy lapot véd, a szűrők nem működnek.

Ha már vannak szűrők, akkor engedélyezheti az automatikus szűrést, hogy az védett lapokon is működjön.

Ehhez jelölje be az Automatikus szűrő használata opciót a lap védelme közben.

Míg ez működik, ha már vannak szűrők a helyén, abban az esetben, ha VBA-kóddal próbál Autofiltert hozzáadni, az nem fog működni.

Mivel a lap védett, nem engedné, hogy bármilyen makró lefusson és változtasson az Autofilteren.

Ezért kóddal kell védenie a munkalapot, és gondoskodnia kell arról, hogy az automatikus szűrők engedélyezve legyenek benne.

Ez akkor lehet hasznos, ha dinamikus szűrőt hozott létre (amit a példában – “Adatok szűrése egy cella értéke alapján” – tárgyaltam).

Az alábbi kód védi a lapot, ugyanakkor lehetővé teszi a szűrők, valamint a VBA-makrók használatát benne.

Private Sub Workbook_Open()With Worksheets("Sheet1").EnableAutoFilter = True.Protect Password:="password", Contents:=True, UserInterfaceOnly:=TrueEnd WithEnd Sub

Ezt a kódot a ThisWorkbook kódablakban kell elhelyezni.

Az alábbi lépésekkel helyezheti el a kódot a ThisWorkbook kódablakban:

1. Nyissa meg a VB-szerkesztőt (billentyűkombináció – ALT + F11).
2. A Projektfelügyelő ablaktáblában kattintson duplán a ThisWorkbook objektumra.
3. A megnyíló kódablakba másolja be a fenti kódot.

Amint megnyitja a munkafüzetet és engedélyezi a makrókat, a makró automatikusan lefut és védi a Sheet1-et.

Mielőtt azonban ezt megtenné, megadja az ‘EnableAutoFilter = True’ értéket, ami azt jelenti, hogy a szűrők a védett lapon is működnének.

Még a ‘UserInterfaceOnly’ argumentumot is ‘True’ értékre állítja. Ez azt jelenti, hogy amíg a munkalap védett, a VBA makrók kódja továbbra is működne.

Az alábbi VBA oktatóanyagok is tetszhetnek:

• Excel VBA hurkok.
• Cellák szűrése vastag betűs formázással.
• Makró rögzítése.
• Adatok rendezése VBA segítségével.
• Munkalap lapok rendezése Excelben.