Excel VBA Autofilter: Kompletný sprievodca s príkladmi

K dispozícii je tiež množstvo funkcií Excelu, ktoré je možné použiť vo VBA - a Automatický filter metóda je jednou z týchto funkcií.

Ak máte množinu údajov a chcete ju filtrovať pomocou kritéria, môžete to ľahko vykonať pomocou možnosti Filter na páse s údajmi.

A ak chcete jeho pokročilejšiu verziu, v Exceli je aj pokročilý filter.

Prečo potom používať automatický filter vo VBA?

Ak potrebujete iba filtrovať údaje a vykonávať základné činnosti, odporučil by som držať sa vstavanej funkcie filtra, ktorú ponúka rozhranie programu Excel.

VBA Autofilter by ste mali používať, ak chcete filtrovať údaje ako súčasť svojej automatizácie (alebo ak vám to pomôže ušetriť čas zrýchlením filtrovania údajov).

Predpokladajme napríklad, že chcete rýchlo filtrovať údaje na základe rozbaľovacieho výberu a potom skopírovať tieto filtrované údaje do nového pracovného hárka.

Aj keď sa to dá urobiť pomocou vstavanej funkcie filtra spolu s kopírovaním a vkladaním, manuálne to môže trvať veľa času.

V takom prípade môže používanie automatického filtra VBA veci urýchliť a ušetriť čas.

Poznámka: Tomuto príkladu (o filtrovaní údajov na základe rozbaľovacieho výberu a kopírovaní do nového hárka) sa budem venovať neskôr v tomto návode.

Syntax automatického filtra VBA programu Excel

Výraz. Automatický filter (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)

• Výraz: Toto je rozsah, v ktorom chcete použiť automatický filter.
• Lúka: [Voliteľný argument] Toto je číslo stĺpca, ktoré chcete filtrovať. Toto sa počíta v množine údajov zľava. Ak teda chcete filtrovať údaje na základe druhého stĺpca, bude táto hodnota 2.
• Kritériá 1: [Voliteľný argument] Toto sú kritériá, na základe ktorých chcete filtrovať množinu údajov.
• Operátor: [Voliteľný argument] V prípade, že používate aj kritérium 2, môžete tieto dve kritériá skombinovať na základe operátora. K dispozícii sú nasledujúce operátory: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kritériá 2: [Voliteľný argument] Toto je druhé kritérium, podľa ktorého môžete filtrovať množinu údajov.
• VisibleDropDown: [Voliteľný argument] Môžete určiť, či sa má rozbaľovacia ikona filtra zobrazovať vo filtrovaných stĺpcoch alebo nie. Tento argument môže byť PRAVDA alebo NEPRAVDA.

Okrem výrazu sú všetky ostatné argumenty voliteľné.

V prípade, že nepoužívate žiadny argument, jednoducho by sa použili alebo odstránili ikony filtra v stĺpcoch.

Pracovné listy čiastkových filtrov () („Filtrovať údaje“). Rozsah („A1“). Koncový podradený filter

Vyššie uvedený kód by jednoducho použil metódu automatického filtra na stĺpce (alebo ak je už použitý, odstráni ho).

To jednoducho znamená, že ak nevidíte ikony filtrov v hlavičkách stĺpcov, začnú sa vám zobrazovať po spustení vyššie uvedeného kódu a ak ho vidíte, bude odstránený.

V prípade, že máte nejaké filtrované údaje, filtre sa odstránia a zobrazí sa vám celá množina údajov.

Teraz sa pozrime na niekoľko príkladov použitia automatického filtra Excel VBA, ktoré objasní jeho používanie.

Príklad: Filtrovanie údajov na základe podmienky textu

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete ju filtrovať podľa stĺpca „Položka“.

Nasledujúci kód by filtroval všetky riadky, v ktorých je položka „Tlačiareň“.

Podfiltračné riadky () Listy („List1“). Rozsah („A1“). Pole automatického filtra: = 2, kritérium1: = Koncová podpoložka „Tlačiareň“

Vyššie uvedený kód odkazuje na list 1 a v rámci neho odkazuje na A1 (čo je bunka v množine údajov).

Všimnite si toho, že sme použili Pole: = 2, pretože stĺpec položky je druhým stĺpcom v našej množine údajov zľava.

Ak teraz premýšľate - prečo to musím urobiť pomocou kódu VBA. To sa dá ľahko vykonať pomocou vstavanej funkcie filtra.

Máš pravdu!

Ak je to všetko, čo chcete urobiť, lepšie použite vstavanú funkciu filtra.

Keď si však prečítate zostávajúci návod, uvidíte, že to možno skombinovať s nejakým ďalším kódom na vytvorenie výkonnej automatizácie.

Ale skôr, ako vám ich ukážem, dovoľte mi najskôr pokryť niekoľko príkladov, aby som vám ukázal, čo všetko metóda AutoFilter dokáže.

Kliknite tu stiahnite si ukážkový súbor a postupujte podľa neho.

Príklad: viac kritérií (A/ALEBO) v rovnakom stĺpci

Predpokladajme, že mám rovnaký súbor údajov, a tentoraz chcem filtrovať všetky záznamy, kde je položka buď „Tlačiareň“ alebo „Projektor“.

Nasledujúci kód by to urobil:

Podfiltračné riadky OR () Listy („List1“). Rozsah („A1“). Pole automatického filtra: = 2, Kritériá1: = „Tlačiareň“, operátor: = xlOr, Kritériá2: = „Koncový projektor“

Všimnite si toho, že tu som použil xlOR operátor.

VBA to hovorí, aby používala obe kritériá a filtrovala údaje, ak sú splnené niektoré z týchto dvoch kritérií.

Podobne môžete použiť aj kritériá AND.

Ak napríklad chcete filtrovať všetky záznamy, kde je množstvo vyššie ako 10, ale menšie ako 20, môžete použiť nasledujúci kód:

Podfiltračné riadky a () hárky („List1“). Rozsah („A1“). Pole automatického filtra: = 4, Kritériá1: = "> 10", _ Operátor: = xlAnd, Kritériá2: = "<20" Koncový podrad

Príklad: viac kritérií s rôznymi stĺpcami

Predpokladajme, že máte nasledujúci súbor údajov.

S automatickým filtrom môžete filtrovať viac stĺpcov súčasne.

Ak napríklad chcete filtrovať všetky záznamy, kde je položka „Tlačiareň“ a obchodný zástupca je „Značka“, môžete použiť nasledujúci kód:

Podfiltračné riadky () s pracovnými listami („List1“). Rozsah („A1“). Pole AutoFilter: = 2, Kritériá1: = „Tlačiareň“. Pole automatického filtra: = 3, Kritériá1: = „Označiť“ Koniec koncovým sub

Príklad: Filtrujte 10 najlepších záznamov pomocou metódy automatického filtra

Predpokladajme, že máte nasledujúci súbor údajov.

Nasleduje kód, ktorý vám poskytne 10 najlepších záznamov (na základe stĺpca množstva):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Pole automatického filtra: = 4, kritérium1: = "10", operátor: = xlTop10Items End Sub

Vo vyššie uvedenom kóde som použil ActiveSheet. Ak chcete, môžete použiť názov hárka.

Všimnite si toho, že v tomto prípade, ak chcete získať 5 najlepších položiek, jednoducho zmeňte číslo v Kritériá1: = „10“ od 10 do 5.

Pre prvých 5 položiek by teda kód bol:

Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). Pole automatického filtra: = 4, kritérium1: = "5", operátor: = xlTop10Items End Sub

Môže to vyzerať divne, ale bez ohľadu na to, koľko vrchných položiek chcete, hodnota operátora vždy zostane xlTop10Položky.

Podobne nasledujúci kód by vám poskytol prvých 10 položiek:

Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). Pole automatického filtra: = 4, kritéria1: = "10", operátor: = xlBottom10Items End Sub

A ak chcete spodných 5 položiek, zmeňte číslo v Kritériá1: = „10“ od 10 do 5.

Príklad: Filtrujte prvých 10 percent pomocou metódy automatického filtra

Predpokladajme, že máte rovnaký súbor údajov (ako sa používa v predchádzajúcich príkladoch).

Nasleduje kód, ktorý vám poskytne 10 percent najlepších záznamov (na základe stĺpca množstva):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Pole automatického filtra: = 4, kritérium1: = "10", operátor: = xlTop10Percent End Sub

Keďže v našom súbore údajov máme 20 záznamov, vráti 2 najlepšie záznamy (čo je 10% z celkového počtu záznamov).

Príklad: Použitie zástupných znakov v automatickom filtri

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:

Ak chcete filtrovať všetky riadky, v ktorých názov položky obsahuje slovo „Board“, môžete použiť nasledujúci kód:

Podfiltračné riadkyWildcard () („List1“). Rozsah („A1“). Pole automatického filtra: = 2, Kritériá1: = "*Doska*" Koncová podskupina

Vo vyššie uvedenom kóde som použil zástupný znak * (hviezdička) pred a za slovom „Board“ (čo sú kritériá).

Hviezdička môže predstavovať ľubovoľný počet znakov. Takto by sa filtrovala každá položka, v ktorej je slovo „doska“.

Príklad: Skopírujte filtrované riadky do nového hárka

Ak chcete záznamy nielen filtrovať na základe kritérií, ale aj kopírovať filtrované riadky, môžete použiť nižšie uvedené makro.

Skopíruje filtrované riadky, pridá nový pracovný hárok a potom skopíruje tieto skopírované riadky do nového hárka.

Sub CopyFilteredRows () Dim rng As Range Dim ws as Worksheet If Worksheets ("Sheet1") AutoFilterMode = False Then MsgBox "Neexistujú žiadne filtrované riadky" Ukončiť Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") End Sub

Vyššie uvedený kód by skontroloval, či v hárku 1 sú alebo nie sú filtrované riadky.

Ak neexistujú žiadne filtrované riadky, zobrazí sa okno so správou, ktoré to uvádza.

A ak existujú filtrované riadky, skopíruje ich, vloží nový pracovný hárok a prilepí tieto riadky do tohto novo vloženého pracovného hárka.

Príklad: Filtrovanie údajov na základe hodnoty bunky

Použitím automatického filtra vo VBA spolu s rozbaľovacím zoznamom môžete vytvoriť funkciu, v ktorej sa hneď po vybratí položky z rozbaľovacej ponuky budú filtrovať všetky záznamy pre túto položku.

Niečo, ako je uvedené nižšie:

Kliknite tu stiahnite si ukážkový súbor a postupujte podľa neho.

Tento typ konštrukcie môže byť užitočný, ak chcete rýchlo filtrovať údaje a potom ich ďalej používať vo svojej práci.

Nasleduje kód, ktorý to urobí:

Súkromný čiastkový pracovný list_Zmeniť (ByVal cieľ ako rozsah) If Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, Kritérium1: = Rozsah ("B2") Koniec Ak Koniec Ak Koniec Sub

Toto je kód udalosti pracovného hárka, ktorý sa spustí iba vtedy, ak dôjde k zmene v pracovnom hárku a cieľová bunka je B2 (kde máme rozbaľovaciu ponuku).

Podmienka If Then Else sa používa aj na kontrolu, či používateľ vybral z rozbaľovacieho zoznamu možnosť „Všetko“. Ak je vybratá možnosť Všetko, zobrazí sa celý súbor údajov.

Tento kód NIE JE vložený do modulu.

Namiesto toho musí byť umiestnený na zadnej strane pracovného hárka, ktorý obsahuje tieto údaje.

Tu je postup, ako vložiť tento kód do okna kódu pracovného hárka:

1. Otvorte editor VB (klávesová skratka - ALT + F11).
2. Na table Project Explorer dvakrát kliknite na názov pracovného hárka, v ktorom chcete túto funkciu filtrovania.
3. V okne kódu pracovného hárka skopírujte a prilepte vyššie uvedený kód.
4. Zatvorte editor VB.

Keď teraz použijete rozbaľovací zoznam, údaje sa budú automaticky filtrovať.

Toto je kód udalosti pracovného hárka, ktorý sa spustí iba vtedy, ak dôjde k zmene v pracovnom hárku a cieľová bunka je B2 (kde máme rozbaľovaciu ponuku).

Podmienka If Then Else sa používa aj na kontrolu, či používateľ vybral z rozbaľovacej ponuky možnosť „Všetko“. Ak je vybratá možnosť Všetko, zobrazí sa celý súbor údajov.

Zapnite/vypnite automatický filter Excelu pomocou VBA

Pri aplikácii automatického filtra na určitý rozsah buniek už môžu byť k dispozícii niektoré filtre.

Nasledujúci kód môžete použiť na vypnutie všetkých vopred použitých automatických filtrov:

Sub TurnOFFAutoFilter () pracovné listy ("Sheet1"). AutoFilterMode = False End Sub

Tento kód skontroluje celé hárky a odstráni všetky použité filtre.

Ak nechcete vypnúť filtre z celého hárka, ale iba z konkrétnej množiny údajov, použite nasledujúci kód:

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

Vyššie uvedený kód kontroluje, či už existujú filtre alebo nie.

Ak sú filtre už použité, odstráni ich, inak nerobí nič.

Podobne, ak chcete zapnúť automatický filter, použite nasledujúci kód:

Sub TurnOnAutoFilter () Ak nie, pracovné hárky ("List1"). Rozsah ("A4"). Automatický filter potom pracovné listy ("List1"). Rozsah ("A4"). AutoFilter Koniec, ak koniec Sub

Skontrolujte, či je už použitý automatický filter

Ak máte hárok s viacerými množinami údajov a chcete sa uistiť, že už nie sú k dispozícii žiadne filtre, môžete použiť nasledujúci kód.

Sub CheckforFilters () If ActiveSheet.AutoFilterMode = True Then MsgBox "Už sú k dispozícii filtre" Else MsgBox "Neexistujú žiadne filtre" End If End Sub

Tento kód používa funkciu schránky správ, ktorá keď nájde filtre v hárku, zobrazí správu „Už sú k dispozícii filtre“, v opačnom prípade zobrazí „Neexistujú žiadne filtre“.

Zobraziť všetky údaje

Ak máte na množinu údajov aplikované filtre a chcete zobraziť všetky údaje, použite nasledujúci kód:

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

Vyššie uvedený kód kontroluje, či je FilterMode TRUE alebo FALSE.

Ak je to pravda, znamená to, že bol použitý filter a na zobrazenie všetkých údajov používa metódu ShowAllData.

Upozorňujeme, že filtre sa tým neodstránia. Ikony filtra sú stále k dispozícii na použitie.

Použitie automatického filtra na chránených listoch

V predvolenom nastavení keď chránite list, filtre nebudú fungovať.

V prípade, že už máte zavedené filtre, môžete povoliť automatický filter, aby ste zaistili, že bude fungovať aj na chránených listoch.

Za týmto účelom zaškrtnite možnosť Použiť automatický filter a zároveň chráňte hárok.

Aj keď to funguje, keď už máte nainštalované filtre, v prípade, že sa pokúsite pridať automatické filtre pomocou kódu VBA, nebude to fungovať.

Keďže je hárok chránený, nedovolil by spustenie žiadneho makra a vykonanie zmien v automatickom filtri.

Takže na ochranu pracovného hárka musíte použiť kód a uistiť sa, že sú v ňom povolené automatické filtre.

To môže byť užitočné, ak ste vytvorili dynamický filter (niečo, o čom som hovoril v príklade - „Filtrovanie údajov na základe hodnoty bunky“).

Nasleduje kód, ktorý bude hárok chrániť, ale zároveň vám umožní používať v ňom filtre aj makrá VBA.

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

Tento kód je potrebné vložiť do okna s kódom ThisWorkbook.

Tu sú kroky na vloženie kódu do okna kódu ThisWorkbook:

1. Otvorte editor VB (klávesová skratka - ALT + F11).
2. Na table Project Explorer dvakrát kliknite na objekt ThisWorkbook.
3. V okne kódu, ktoré sa otvorí, skopírujte a prilepte vyššie uvedený kód.

Hneď ako otvoríte zošit a povolíte makrá, makro sa spustí automaticky a ochráni List1.

Predtým to však určí „EnableAutoFilter = True“, čo znamená, že filtre budú fungovať aj na chránenom hárku.

Tiež nastaví argument „UserInterfaceOnly“ na „True“. To znamená, že aj keď je pracovný hárok chránený, kód makier VBA bude naďalej fungovať.

Tiež by sa vám mohli páčiť nasledujúce návody VBA:

• Slučky Excel VBA.
• Filtrujte bunky tučným písmovým formátovaním.
• Nahrávanie makra.
• Zoradenie údajov pomocou jazyka VBA.
• Zoradiť karty pracovného hárka v programe Excel.