Excel VBA Autofilter: Kompletní průvodce s příklady

K dispozici je také mnoho funkcí aplikace Excel, které lze použít ve VBA - a Automatický filtr metoda je jednou z takových funkcí.

Pokud máte datovou sadu a chcete ji filtrovat pomocí kritéria, můžete to snadno provést pomocí možnosti Filtr na pásu dat.

A pokud chcete jeho pokročilejší verzi, existuje pokročilý filtr i v Excelu.

Proč tedy dokonce používat automatický filtr ve VBA?

Pokud potřebujete pouze filtrovat data a dělat nějaké základní věci, doporučil bych se držet vestavěné funkce filtru, kterou rozhraní Excel nabízí.

Pokud chcete filtrovat data jako součást své automatizace (nebo pokud vám to pomůže ušetřit čas rychlejším filtrováním dat), měli byste použít automatický filtr VBA.

Předpokládejme například, že chcete rychle filtrovat data na základě rozevíracího výběru, a poté zkopírujte tato filtrovaná data do nového listu.

I když to lze provést pomocí vestavěné funkce filtru spolu s kopírováním a vkládáním, může vám to trvat hodně času, než to provedete ručně.

V takovém případě může použití VBA Autofilter vše urychlit a ušetřit čas.

Poznámka: Tomuto příkladu (o filtrování dat na základě rozevíracího výběru a kopírování do nového listu) se budu věnovat později v tomto kurzu.

Syntaxe automatického filtru Excel VBA

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

• Výraz: Toto je rozsah, na který chcete použít automatický filtr.
• Pole: [Volitelný argument] Toto je číslo sloupce, které chcete filtrovat. To se počítá zleva v datové sadě. Pokud tedy chcete filtrovat data podle druhého sloupce, bude tato hodnota 2.
• Kritéria 1: [Volitelný argument] Toto jsou kritéria, na základě kterých chcete filtrovat datovou sadu.
• Operátor: [Volitelný argument] V případě, že používáte také kritéria 2, můžete tato dvě kritéria kombinovat na základě operátora. K použití jsou k dispozici následující operátory: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kritéria 2: [Volitelný argument] Toto je druhé kritérium, podle kterého můžete filtrovat datovou sadu.
• VisibleDropDown: [Volitelný argument] Můžete určit, zda chcete, aby se ikona rozevíracího seznamu filtrů zobrazovala ve filtrovaných sloupcích, nebo ne. Tento argument může být PRAVDA nebo NEPRAVDA.

Kromě výrazu jsou všechny ostatní argumenty volitelné.

V případě, že nepoužijete žádný argument, jednoduše použije nebo odstraní ikony filtrů do sloupců.

Pracovní listy dílčích filtrů řádků () („filtrování dat“). Rozsah („A1“).

Výše uvedený kód by jednoduše použil metodu Autofilter na sloupce (nebo pokud je již použit, odstraní ji).

To jednoduše znamená, že pokud nevidíte ikony filtrů v záhlaví sloupců, začnou se vám zobrazovat po spuštění výše uvedeného kódu a pokud ho vidíte, bude odstraněn.

V případě, že máte nějaká filtrovaná data, odstraní filtry a zobrazí vám celou datovou sadu.

Nyní se podívejme na několik příkladů použití automatického filtru Excel VBA, díky kterému bude jeho použití jasné.

Příklad: Filtrování dat na základě podmínky textu

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete ji filtrovat podle sloupce ‘Položka’.

Níže uvedený kód by filtroval všechny řádky, kde je položka „Tiskárna“.

Pracovní listy dílčích filtrů ()

Výše uvedený kód odkazuje na List1 a v rámci něj odkazuje na A1 (což je buňka v datové sadě).

Zde jsme použili pole: = 2, protože sloupec položky je druhým sloupcem v naší sadě dat zleva.

Pokud teď přemýšlíte - proč to musím udělat pomocí kódu VBA. To lze snadno provést pomocí vestavěné funkce filtru.

Máš pravdu!

Pokud je to vše, co chcete udělat, použijte lépe vestavěnou funkci filtru.

Když si ale přečtete zbývající výukový program, uvidíte, že to lze zkombinovat s nějakým extra kódem a vytvořit tak výkonnou automatizaci.

Ale než vám je ukážu, dovolte mi nejprve pokrýt několik příkladů, abych vám ukázal, co všechno metoda AutoFilter umí.

Klikněte zde stáhněte si ukázkový soubor a postupujte podle něj.

Příklad: Více kritérií (A/NEBO) ve stejném sloupci

Předpokládejme, že mám stejnou datovou sadu, a tentokrát chci filtrovat všechny záznamy, kde je položka buď „Tiskárna“ nebo „Projektor“.

Následující kód by to udělal:

Podřízené pracovní listy FilterRowsOR () („List1“). Rozsah („A1“). Pole automatického filtru: = 2, Kritéria1: = „Tiskárna“, Operátor: = xlOr, Kritéria2: = Koncový díl „Projektor“

Všimněte si, že zde jsem použil xlOR operátor.

To říká VBA, aby použila obě kritéria a filtrovala data, pokud jsou splněna některá ze dvou kritérií.

Podobně můžete také použít kritéria AND.

Pokud například chcete filtrovat všechny záznamy, kde je množství větší než 10, ale menší než 20, můžete použít níže uvedený kód:

Dílčí filtrovací řádky () Listy („List1“). Rozsah („A1“). Pole automatického filtru: = 4, Kritéria1: = "> 10", _ Operátor: = xlAnd, Kritéria2: = "<20" Koncový díl

Příklad: více kritérií s různými sloupci

Předpokládejme, že máte následující datovou sadu.

S automatickým filtrem můžete filtrovat více sloupců současně.

Pokud například chcete filtrovat všechny záznamy, kde je položka „Tiskárna“ a obchodní zástupce je „Označit“, můžete použít níže uvedený kód:

Dílčí filtrovací řádky () s pracovními listy („List1“). Rozsah („A1“). Pole AutoFilter: = 2, Kritéria1: = „Tiskárna“. Pole AutoFilter: = 3, Kritéria1: = „Označit“ Konec na konci Sub

Příklad: Filtrujte 10 nejlepších záznamů pomocí metody automatického filtrování

Předpokládejme, že máte níže uvedenou datovou sadu.

Níže je uveden kód, který vám poskytne 10 nejlepších záznamů (na základě sloupce množství):

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

Ve výše uvedeném kódu jsem použil ActiveSheet. Pokud chcete, můžete použít název listu.

Všimněte si, že pokud v tomto příkladu chcete získat prvních 5 položek, změňte číslo v Kritéria 1: = ”10 ″ od 10 do 5.

Takže pro prvních 5 položek by kód byl:

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

Může to vypadat divně, ale bez ohledu na to, kolik špičkových položek chcete, hodnota Operator vždy zůstane xlTop10Položky.

Podobně vám níže uvedený kód poskytne 10 nejnižších položek:

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

A pokud chcete 5 spodních položek, změňte číslo v Kritéria 1: = ”10 ″ od 10 do 5.

Příklad: Filtrujte nejlepších 10 procent pomocí metody automatického filtru

Předpokládejme, že máte stejnou datovou sadu (jako byla použita v předchozích příkladech).

Níže je uveden kód, který vám poskytne nejlepších 10 procent záznamů (na základě sloupce množství):

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

V naší datové sadě, protože máme 20 záznamů, vrátí 2 nejlepší záznamy (což je 10% z celkového počtu záznamů).

Příklad: Použití zástupných znaků v automatickém filtru

Předpokládejme, že máte datovou sadu, jak je uvedeno níže:

Pokud chcete filtrovat všechny řádky, kde název položky obsahuje slovo „Board“, můžete použít níže uvedený kód:

Dílčí pracovní listy FilterRowsWildcard () („Sheet1“). Rozsah („A1“). Pole AutoFilter: = 2, Criteria1: = "*Board*" End Sub

Ve výše uvedeném kódu jsem použil zástupný znak * (hvězdička) před a za slovem „Board“ (což je kritérium).

Hvězdička může představovat libovolný počet znaků. Tím by se filtrovala jakákoli položka, která má v sobě slovo „deska“.

Příklad: Zkopírujte filtrované řádky do nového listu

Pokud chcete záznamy nejen filtrovat podle kritérií, ale také zkopírovat filtrované řádky, můžete použít níže uvedené makro.

Zkopíruje filtrované řádky, přidá nový list a poté zkopírované řádky vloží do nového listu.

Sub CopyFilteredRows () Dim rng As Range Dim ws As Worksheet If Worksheets ("Sheet1"). AutoFilterMode = False Then MsgBox "There are no printed lines" Exit Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") End Sub

Výše uvedený kód by zkontroloval, zda jsou v List1 nějaké filtrované řádky nebo ne.

Pokud neexistují žádné filtrované řádky, zobrazí se okno se zprávou o tom.

A pokud existují filtrované řádky, zkopíruje je, vloží nový list a vloží tyto řádky do nově vloženého listu.

Příklad: Filtrování dat na základě hodnoty buňky

Pomocí automatického filtru ve VBA spolu s rozevíracím seznamem můžete vytvořit funkci, kde jakmile vyberete položku z rozevíracího seznamu, budou filtrovány všechny záznamy pro tuto položku.

Něco, jak je uvedeno níže:

Klikněte zde stáhněte si ukázkový soubor a postupujte podle něj.

Tento typ konstrukce může být užitečný, pokud chcete rychle filtrovat data a poté je dále používat ve své práci.

Níže je uveden kód, který to provede:

Soukromý dílčí pracovní list_Změna (ByVal cíl jako rozsah) If Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, Kritéria1: = Rozsah ("B2") End If End If End Sub

Toto je kód události listu, který se spustí pouze v případě, že v listu dojde ke změně a cílová buňka je B2 (kde máme rozevírací seznam).

Podmínka If Then Else se také používá ke kontrole, zda uživatel z rozevíracího seznamu vybral možnost „All“. Pokud je vybrána možnost Vše, zobrazí se celý soubor dat.

Tento kód NENÍ umístěn v modulu.

Místo toho musí být umístěn v backendu listu, který obsahuje tato data.

Zde jsou kroky k vložení tohoto kódu do okna kódu listu:

1. Otevřete editor VB (klávesová zkratka - ALT + F11).
2. V podokně Průzkumník projektů poklepejte na název listu, ve kterém chcete tuto funkci filtrování.
3. V okně kódu listu zkopírujte a vložte výše uvedený kód.
4. Zavřete editor VB.

Když nyní použijete rozevírací seznam, automaticky data vyfiltruje.

Toto je kód události listu, který se spustí pouze v případě, že v listu dojde ke změně a cílová buňka je B2 (kde máme rozevírací seznam).

Podmínka If Then Else se také používá ke kontrole, zda uživatel z rozevíracího seznamu vybral možnost „All“. Pokud je vybrána možnost Vše, zobrazí se celý soubor dat.

Zapněte/vypněte Excel AutoFilter pomocí VBA

Při aplikaci automatického filtru na řadu buněk již mohou být nějaké filtry na místě.

Pomocí níže uvedeného kódu můžete vypnout všechny předem použité automatické filtry:

Dílčí pracovní listy TurnOFFAutoFilter () ("Sheet1"). AutoFilterMode = False End Sub

Tento kód zkontroluje celé listy a odstraní všechny použité filtry.

Pokud nechcete vypnout filtry z celého listu, ale pouze z konkrétní datové sady, použijte níže uvedený kód:

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

Výše uvedený kód kontroluje, zda již existují filtry nebo ne.

Pokud jsou filtry již použity, odstraní je, jinak nic neudělá.

Podobně pokud chcete zapnout automatický filtr, použijte následující kód:

Sub TurnOnAutoFilter () Pokud ne Pracovní listy („List1“). Rozsah („A4“). Automatický filtr pak pracovní listy („List1“). Rozsah („A4“). AutoFilter Konec Pokud Konec Sub

Zkontrolujte, zda je již použit automatický filtr

Pokud máte list s více datovými sadami a chcete se ujistit, že již nejsou k dispozici žádné filtry, můžete použít níže uvedený kód.

Sub CheckforFilters () If ActiveSheet.AutoFilterMode = True Then MsgBox "Už jsou k dispozici filtry" Else MsgBox "Neexistují žádné filtry" End If End Sub

Tento kód používá funkci schránky zpráv, která zobrazí zprávu „Už jsou k dispozici filtry“, když najde filtry na listu, jinak zobrazí „Neexistují žádné filtry“.

Zobrazit všechna data

Pokud máte na datovou sadu aplikované filtry a chcete zobrazit všechna data, použijte níže uvedený kód:

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

Výše uvedený kód kontroluje, zda je FilterMode PRAVDA nebo NEPRAVDA.

Pokud je to pravda, znamená to, že byl použit filtr a k zobrazení všech dat používá metodu ShowAllData.

Všimněte si, že to neodstraní filtry. Ikony filtrů jsou stále k dispozici k použití.

Použití automatického filtru na chráněných listech

Ve výchozím nastavení při ochraně listu filtry nebudou fungovat.

V případě, že již máte zavedené filtry, můžete povolit AutoFilter, abyste se ujistili, že funguje i na chráněných listech.

Chcete -li to provést, zaškrtněte při ochraně listu možnost Použít automatický filtr.

I když to funguje, když již máte zavedené filtry, v případě, že se pokusíte přidat automatické filtry pomocí kódu VBA, nebude to fungovat.

Protože je list chráněný, nedovolí spuštění žádného makra a provádění změn v automatickém filtru.

Je tedy nutné použít kód k ochraně listu a zajistit, aby v něm byly povoleny automatické filtry.

To může být užitečné, když jste vytvořili dynamický filtr (něco, co jsem popsal v příkladu - „Filtrování dat na základě hodnoty buňky“).

Níže je kód, který bude list chránit, ale zároveň vám umožní používat v něm filtry i makra 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 třeba umístit do okna kódu ThisWorkbook.

Zde jsou kroky k vložení kódu do okna kódu ThisWorkbook:

1. Otevřete editor VB (klávesová zkratka - ALT + F11).
2. V podokně Průzkumník projektů poklepejte na objekt ThisWorkbook.
3. V okně kódu, které se otevře, zkopírujte a vložte výše uvedený kód.

Jakmile otevřete sešit a povolíte makra, automaticky spustí makro a ochrání List1.

Než to však provedete, zadá „EnableAutoFilter = True“, což znamená, že filtry budou fungovat i na chráněném listu.

Také nastaví argument „UserInterfaceOnly“ na „True“. To znamená, že zatímco je list chráněný, kód maker VBA bude nadále fungovat.

Také by se vám mohly líbit následující výukové programy VBA:

• Smyčky VBA aplikace Excel.
• Filtrujte buňky tučným formátováním písma.
• Záznam makra.
• Třídit data pomocí VBA.
• Seřadit karty pracovního listu v aplikaci Excel.