Excel VBA automatikus szűrő: Teljes útmutató példákkal

A VBA -ban számos Excel funkció is használható - és a Automatikus szűrő A módszer egy ilyen funkció.

Ha rendelkezik adatkészlettel, és azt egy feltétel segítségével szeretné szűrni, akkor könnyen megteheti az Adatszalag Szűrő opciójával.

Ha pedig egy fejlettebb verziót szeretne, akkor van egy speciális szűrő az Excelben is.

Akkor miért érdemes még használni az automatikus szűrőt a VBA -ban?

Ha csak adatokat kell szűrnie, és néhány alapvető dolgot kell tennie, azt javaslom, hogy tartsa be az Excel felület által kínált beépített szűrési funkciót.

Használnia kell a VBA automatikus szűrőt, ha az adatokat az automatizálás részeként szeretné szűrni (vagy ha ez időt takarít meg, mivel gyorsítja az adatok szűrését).

Tegyük fel például, hogy gyorsan le szeretné szűrni az adatokat egy legördülő lista alapján, majd másolja át ezeket a szűrt adatokat egy új munkalapra.

Bár ezt megteheti a beépített szűrőfunkció és néhány másolás-beillesztés segítségével, sok időbe telhet, amíg ezt manuálisan elvégzi.

Ilyen esetben a VBA automatikus szűrő használata felgyorsíthatja a dolgokat és időt takaríthat meg.

jegyzet: Ezt a példát (az adatok szűrését egy legördülő menü alapján és új lapra másolás) ismertetem később ebben az oktatóanyagban.

Excel VBA automatikus szűrő szintaxisa

Kifejezés. Automatikus szűrő (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)

• Kifejezés: Ez az a tartomány, amelyre alkalmazni szeretné az automatikus szűrőt.
• Terület: [Választható érv] Ez az oszlop száma, amelyet szűrni szeretne. Ezt balról számolják az adatkészletben. Tehát ha a második oszlop alapján szeretné szűrni az adatokat, ez az érték 2 lenne.
• Kritériumok1: [Választható érv] Ez az a feltétel, amely alapján szűrni szeretné az adatkészletet.
• Operátor: [Választható érv] Abban az esetben, ha Ön a 2. feltételt is használja, ezt a két feltételt kombinálhatja az Üzemeltető alapján. A következő operátorok használhatók: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Feltételek2: [Választható érv] Ez a második kritérium, amely alapján szűrheti az adatkészletet.
• VisibleDropDown: [Választható érv] Megadhatja, hogy a szűrő legördülő ikonja megjelenjen-e a szűrt oszlopokban. Ez az érv lehet IGAZ vagy HAMIS.

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

Ha nem használ argumentumokat, akkor egyszerűen alkalmazza vagy eltávolítja a szűrőikonokat az oszlopokról.

Sub FilterRows () Munkalapok ("Adatok szűrése"). Tartomány ("A1"). AutoFilter End Sub

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

Ez egyszerűen azt jelenti, hogy ha nem látja a szűrőikonokat az oszlopfejlécekben, akkor ezt a fenti kód végrehajtásakor látni fogja, és ha látja, akkor eltávolítja.

Abban az esetben, ha van szűrt adata, eltávolítja a szűrőket, és megjeleníti a teljes adatkészletet.

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

Példa: Adatok szűrése szövegfeltétel alapján

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és az „Elem” oszlop alapján szeretné szűrni.

Az alábbi kód kiszűri az összes sort, ahol az elem „Nyomtató”.

Sub FilterRows () Munkalapok ("Sheet1"). Tartomány ("A1"). AutoFilter Field: = 2, Criteria1: = "Printer" End Sub

A fenti kód az 1. lapra vonatkozik, és azon belül az A1 -re (amely az adathalmaz cellája).

Vegye figyelembe, hogy itt a Field: = 2 mezőt használtuk, mivel az elem oszlop a bal oldali adatkészletünk második oszlopa.

Ha most gondolkodik - miért kell ezt tennem egy VBA kód használatával? Ez könnyen elvégezhető a beépített szűrő funkcióval.

Igazad van!

Ha csak ennyit szeretne tenni, használja jobban a beépített szűrő funkciót.

De ahogy elolvassa a fennmaradó oktatóanyagot, látni fogja, hogy ez kombinálható néhány extra kóddal, hogy hatékony automatizálást hozzon létre.

Mielőtt azonban ezeket megmutatnám, hadd mutassak be néhány példát, hogy megmutassam, mire képes az összes AutoFilter módszer.

Kattints ide töltse le a mintafájlt, és kövesse.

Példa: Több feltétel (ÉS/VAGY) ugyanabban az oszlopban

Tegyük fel, hogy ugyanazzal az adatkészlettel rendelkezem, és ezúttal minden olyan rekordot szeretnék szűrni, ahol az elem „Nyomtató” vagy „Kivetítő”.

Az alábbi kód ezt tenné:

Sub FilterRowsOR () Munkalapok ("Sheet1"). Tartomány ("A1"). AutoFilter Field: = 2, Criteria1: = "Printer", Operator: = xlOr, Criteria2: = "Projector" End Sub

Vegye figyelembe, hogy itt a xlOR operátor.

Ez azt mondja a VBA -nak, hogy használja a feltételeket és szűrje az adatokat, ha a két feltétel bármelyike ​​teljesül.

Hasonlóképpen használhatja az ÉS feltételeket is.

Például, ha minden olyan rekordot szűrni szeretne, ahol a mennyiség több mint 10, de kevesebb, mint 20, akkor használja az alábbi kódot:

Sub FilterRowsAND () Munkalapok ("Sheet1"). Tartomány ("A1"). Automatikus szűrőmező: = 4, Criteria1: = "> 10", _ Operator: = xlAnd, Criteria2: = "<20" End Sub

Példa: Több feltétel különböző oszlopokkal

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

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

Ha például minden olyan rekordot szűrni szeretne, ahol az elem „Nyomtató”, és az értékesítési képviselő „Megjelölés”, akkor használja az alábbi kódot:

Sub FilterRows () Munkalapokkal ("Sheet1"). Tartomány ("A1") .AutoFilter mező: = 2, Criteria1: = "Printer" .AutoFilter mező: = 3, Criteria1: = "Mark" End with End Sub

Példa: Szűrje a 10 legjobb rekordot az AutoFilter módszerrel

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

Az alábbiakban az a kód található, amely megadja a 10 legjobb rekordot (a mennyiség oszlop alapján):

AlszűrőRowsTop10 () ActiveSheet.Range ("A1"). Automatikus szűrőmező: = 4, Kritérium1: = "10", Operátor: = xlTop10Items End Sub

A fenti kódban ActiveSheet -et használtam. Ha szeretné, használhatja a lap nevét.

Ne feledje, hogy ebben a példában, ha meg szeretné kapni az első 5 elemet, csak módosítsa a számot 1. kritérium: = "10" 10 -től 5 -ig.

Tehát a top 5 termék esetében a kód a következő lenne:

AlszűrőRowsTop5 () ActiveSheet.Range ("A1"). Automatikus szűrőmező: = 4, Kritérium1: = "5", Operátor: = xlTop10Items End Sub

Furcsának tűnhet, de nem számít, hány felső elemet szeretne, az Operátor értéke mindig megmarad xlTop10Items.

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

AlszűrőRowsBottom10 () ActiveSheet.Range ("A1"). Automatikus szűrőmező: = 4, Kritérium1: = "10", Operátor: = xlBottom10Items End Sub

És ha az alsó 5 elemet szeretné, módosítsa a számot 1. kritérium: = ”10” 10 -től 5 -ig.

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

Tegyük fel, hogy ugyanaz az adathalmaza van (mint az előző példákban).

Az alábbiakban látható a kód, amely megadja a legjobb 10 százalékos rekordokat (a mennyiség oszlop alapján):

AlszűrőRowsTop10 () ActiveSheet.Range ("A1"). Automatikus szűrőmező: = 4, Kritérium1: = "10", Operátor: = xlTop10Percent End Sub

Adatkészletünkben, mivel 20 rekordunk van, visszaadja a legjobb 2 rekordot (ami az összes rekord 10% -a).

Példa: helyettesítő karakterek használata az automatikus szűrőben

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:

Ha minden olyan sort ki szeretne szűrni, ahol az elem neve tartalmazza a „Board” szót, akkor használja az alábbi kódot:

AlszűrőRowsWildcard () Munkalapok ("Sheet1"). Tartomány ("A1"). Automatikus szűrő mező: = 2, Criteria1: = "*Board*" Sub

A fenti kódban a helyettesítő karaktert * (csillag) használtam a „Board” szó előtt és után (ez a kritérium).

A csillag tetszőleges számú karaktert jelenthet. Tehát ez minden olyan elemet kiszűr, amelyben a „tábla” szó szerepel.

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

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

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

Sub CopyFilteredRows () Dim rng tartományként Dim ws munkalapként, ha munkalapok ("Sheet1"). AutoFilterMode = False then MsgBox "Nincsenek szűrt sorok" Kilépés az alvégből, ha beállítva rng = Munkalapok ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") End Sub

A fenti kód ellenőrzi, hogy vannak -e szűrt sorok az 1. lapon.

Ha nincsenek szűrt sorok, akkor egy üzenetdoboz jelenik meg, amely ezt jelzi.

És ha vannak szűrt sorok, akkor azokat lemásolja, új munkalapot szúr be, és beilleszti ezeket a sorokat az újonnan beszúrt munkalapra.

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

A VBA automatikus szűrőjének és egy legördülő listájának használatával olyan funkciót hozhat létre, amelyben amint kiválaszt egy elemet a legördülő menüből, az adott elem összes rekordja szűrésre kerül.

Valami az alábbiak szerint:

Kattints ide töltse le a mintafájlt, és kövesse.

Ez a konstrukció akkor lehet hasznos, ha gyorsan szeretné szűrni az adatokat, majd tovább használni a munkájában.

Az alábbiakban látható a kód, amely ezt megteszi:

Privát almunkalap_csere (ByVal -cél tartományként) Ha Target.Address = "$ B $ 2" Then If Range ("B2") = "All" then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, Kritérium1: = Tartomány ("B2") Vége Ha vége Ha vége Vége al

Ez egy munkalap eseménykódja, amely csak akkor hajtódik végre, ha a munkalapon változás következik be, és a célcella B2 (ahol a legördülő menü található).

Ezenkívül egy Ha akkor más feltételt is használnak annak ellenőrzésére, hogy a felhasználó az „Összes” lehetőséget választotta -e a legördülő menüből. Ha az Összes lehetőséget választja, a teljes adatkészlet megjelenik.

Ez a kód NEM helyezkedik el a modulban.

Ehelyett el kell helyezni a munkalap háttérrendszerébe, amely tartalmazza ezeket az adatokat.

Az alábbi lépésekkel teheti be ezt a kódot a munkalap kódablakába:

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

Most, amikor a legördülő listát használja, automatikusan szűri az adatokat.

Ez egy munkalap eseménykódja, amely csak akkor hajtódik végre, ha a munkalapon változás következik be, és a célcella B2 (ahol a legördülő menü található).

Ezenkívül egy Ha akkor más feltételt is használnak annak ellenőrzésére, hogy a felhasználó az „Összes” lehetőséget választotta -e a legördülő menüből. Ha az Összes lehetőséget választja, a teljes adatkészlet megjelenik.

Kapcsolja BE/KI az Excel automatikus szűrőt a VBA használatával

Amikor az automatikus szűrőt cellatartományra alkalmazza, előfordulhat, hogy már vannak szűrők.

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

Sub TurnOFFAutoFilter () feladatlapok ("Sheet1"). AutoFilterMode = False End Sub

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

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

Sub TurnOFFAutoFilter () Ha munkalapok ("Sheet1"). Tartomány ("A1"). AutoFilter then Worksheets ("Sheet1"). Range ("A1"). AutoFilter End If End Sub

A fenti kód ellenőrzi, hogy vannak -e már szűrők a helyükön.

Ha már alkalmaznak szűrőket, akkor eltávolítja, különben nem tesz semmit.

Hasonlóképpen, ha be szeretné kapcsolni az Automatikus szűrőt, használja az alábbi kódot:

Sub TurnOnAutoFilter () Ha nem munkalapok ("Sheet1"). Tartomány ("A4"). AutoFilter then Worksheets ("Sheet1"). Range ("A4"). AutoFilter End If End Sub

Ellenőrizze, hogy az automatikus szűrő már alkalmazva van -e

Ha több adatkészlettel rendelkező lapja van, és meg szeretné győződni arról, hogy nincsenek már szűrők, akkor használja az alábbi kódot.

Sub CheckforFilters () Ha ActiveSheet.AutoFilterMode = Igaz, akkor MsgBox "Már léteznek szűrők" Más MsgBox "Nincsenek szűrők" Vége, ha vége al

Ez a kód egy üzenetdoboz -funkciót használ, amely megjeleníti a „Már léteznek szűrők” üzenetet, amikor szűrőket talál a lapon, ellenkező esetben a „Nincsenek szűrők” üzenetet jeleníti meg.

Összes adat megjelenítése

Ha szűrőket alkalmazott az adatkészletre, és meg szeretné jeleníteni az összes adatot, használja az alábbi kódot:

Sub ShowAllData () Ha ActiveSheet.FilterMode Majd ActiveSheet.ShowAllData End Sub

A fenti kód ellenőrzi, hogy a FilterMode IGAZ vagy HAMIS.

Ha ez igaz, az azt jelenti, hogy szűrőt alkalmaztak, és a ShowAllData metódust használja az összes adat megjelenítésére.

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

Az AutoFilter használata védett lapokon

Alapértelmezés szerint, amikor védi a lapot, a szűrők nem működnek.

Ha már rendelkezik szűrőkkel, engedélyezheti az AutoFilter alkalmazást, hogy megbizonyosodjon arról, hogy védett lapokon is működik -e.

Ehhez jelölje be az Automatikus szűrő használata opciót, miközben védi a lapot.

Bár ez akkor működik, ha már vannak szűrők, de ha VBA -kód használatával próbálja hozzáadni az automatikus szűrőket, nem fog működni.

Mivel a lap védett, nem engedi, hogy bármely makró fusson és módosítsa az automatikus szűrőt.

Tehát kódot kell használnia a munkalap védelmére, és győződjön meg arról, hogy az automatikus szűrők engedélyezve vannak benne.

Ez akkor lehet hasznos, ha létrehozott egy dinamikus szűrőt (amit a példában leírtam - „Adatok szűrése cellaérték alapján”).

Az alábbiakban látható a lap, amely megvédi a lapot, ugyanakkor lehetővé teszi a szűrők és a VBA makrók használatát is.

Private Sub Workbook_Open () Munkalapokkal ("Sheet1") .EnableAutoFilter = True .Protect Password: = "password", Contents: = True, UserInterfaceOnly: = True End With End Sub

Ezt a kódot a ThisWorkbook kódablakában kell elhelyezni.

Az alábbi lépésekkel teheti be a kódot a ThisWorkbook kódablakába:

1. Nyissa meg a VB szerkesztőt (billentyűparancs - ALT + F11).
2. A Project Explorer ablaktáblán kattintson duplán a ThisWorkbook objektumra.
3. A megnyíló kódablakban másolja ki és illessze 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 lapot.

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ödnek.

Ezenkívül a „UserInterfaceOnly” argumentumot „True” értékre állítja. Ez azt jelenti, hogy amíg a munkalap védett, a VBA makrókód továbbra is működik.

A következő VBA oktatóanyagok is tetszhetnek:

• Excel VBA hurkok.
• A cellák szűrése vastag betűformázással.
• Makró rögzítése.
• Adatok rendezése a VBA segítségével.
• A munkalap lapjainak rendezése Excelben.