Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » vba-priklady-kodu » filtry-autofilter-excel-vba

Filtry - AutoFilter v Excel VBA

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na AutoFilter ve VBA od vložení/odstranění, zjištění stavu, nastavení filtrovacích kritérií

Dopněno: 21.11.2014

Úvodem do filtru ve VBA

Microsoft Excel VBA logo

Opět pro přehlednost je článek dělen na kapitoly tak ať si patřičný kód můžete najít rychleji:


Teorie AutoFilter

Základní teorie k metodě AutoFilter:

Syntaxe

AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
Atribut Poviné/Volitelné Popis
Field Volitelně Číslo sloupce, na kterém se provede filtrování. Začíná se zleva od jedničky
Criteria1 Volitelně Kriteria pro hledání (např. 100), doplněné zda se rovná (=) nebo je různé (<>), atd. Záleží i na hodnotě Operator (xlTop10Items)
Operator Volitelně Konstanta specifikující typ filtru XlAutoFilterOperator, viz dále
Criteria2 Volitelně Druhé kritérium
VisibleDropDown Volitelně Hodnotu TRUE (výchozí) zobrazí šipku rozevíracího seznamu filtru. Hodnota FALSE skryje rozevírací šipku automatického fil­tru

XlAutoFilterOperator

Hodnota Konstanta Popis
1 xlAnd Logical AND of Criteria1 and Criteria2
2 xlOr Logical OR of Criteria1 or Criteria2.
3 xlTop10Items Highest-valued items displayed (number of items specified in Criteria1).
4 xlBottom10Items Lowest-valued items displayed (number of items specified in Criteria1).
5 xlTop10Percent Highest-valued items displayed (percentage specified in Criteria1).
6 xlBottom10Percent Lowest-valued items displayed (percentage specified in Criteria1).
7 xlFilterValues Filter values
8 xlFilterCellColor Color of the cell
9 xlFilterFontColor Color of the font
10 xlFilterIcon Filter icon
11 xlFilterDynamic Dynamic filter

Criteria

Například věk větší nebo roven 18 ...

Criteria1:=">=18"

Chcete-li zobrazit prázdné buňky

Criteria1:="="

Případně pro zobrazení neprázdných buněk

Criteria1:="<>"

Pro zobrazení textu začínající na P, podrobněji se zmiňuji v kapitole o filtrování textu.

Criteria1:="=P*"

Buňky nesmí obsahovat x ;)

Criteria1:="<>*x*"

Jak hledat, <,>,*,?,~

Jak zapisovat hledaná kritéria:

Criteria1:="=100" Criteria1:=">=100" Criteria1:="<>*AB*" Criteria1:=RGB(255, 255, 0) Criteria1:=Array("Modrá", "Zelená", "Žlutá")

Symboly

  • * Hvězdička - nahrazuje jakýkoli počet znaku
  • ? Otazník - nahrazuje právě jeden znak
  • ~ Vlnovka

Tipy Triky

Pokud hledáte hvězdičku v textu je nutno použít znak vlnovka.

Criteria1:="*~**"

Zpět na seznam kapitol.

Zdrojová tabulka

Pro naše testy budeme používat následující zdrojovou tabulku:

Jméno Stát Počet Id Datum Čas Barva
Fantomas CZ 12 1 29.6.2014 10:10 modrá
Fantomas EN 14 2 30.6.2014 10:25 zelená
Fantomas CZ 16 3 1.7.2014 10:40 červená

Zpět na seznam kapitol.

Vkládaní / odstranění filtru

Jak pomocí VBA vkládat a odstraňovat automatické filtry

Vložení filtru

Pokud záhlaví tabulky začíná v buňce A1

ActiveSheet.Range("A1").AutoFilter

Pokud záhlaví tabulky nezačíná v buňce A1 nebo filtr nechcete aplikovat na celou tabulku:

ActiveSheet.Range("A1:D1").AutoFilter

Pokud by již byl aplikován filtr tak ať jej neodstraníte, v další kapitole se dozvíte jak zjistit, za v listě je již aplikován filtr ;)

With ActiveSheet .AutoFilterMode = False .Range("A1:D1").AutoFilter End With

Odstranění filtru

Pokud tabulka obsahuje filtr

Worksheets("List1").AutoFilterMode = False

Zpět na seznam kapitol.

Kontrola stavu filtru

Jeden z možných postupu, jak zjistit, zda tabulka obsahuje filtr a případně zda jsou data filtrovaná. Využijeme

  • .AutoFilterMode
  • .FilterMode
With ActiveSheet If .AutoFilterMode = True And .FilterMode = True Then MsgBox "Data jsou filtrovaná. Sešit obsahuje filtr ;)" ElseIf .AutoFilterMode = True Then MsgBox "K dispozici filtry - data nefiltrovaná." Else MsgBox "Sešit neobsahuje filtry." End If End With

Zpět na seznam kapitol.

Filtry na čísla

Jak vyfiltrovat například číslo 12

Range("A1:D1").AutoFilter Field:=3, Criteria1:=12

Jak vyfiltrovat například číslo 12 a 14

Range("A1:D1").AutoFilter Field:=3, Criteria1:=12, Operator:=xlOr, Criteria2:=14

Jak vyfiltrovat například čísla větší než 12

Range("A1:D1").AutoFilter Field:=3, Criteria1:=">12"

Zpět na seznam kapitol.

Filtry na text

V této kapitole jsou ukázky filtru buněk s textem. Jak je všeobecně známo tak následující znaky mají tento význam:

  • * - hvězdička - nahrazuje 0 až "nekonečně" znaků
  • ? - otazník - nahrazuje právě jeden znak

Vybere jména začinající na F

Range("B5").AutoFilter Field:=1, Criteria1:="F*"

Vybere slova která začínají na "F" a dále je jakýkoli znak a pak pokračuje "ntomas". (tj. Vybere Fantomas, Fontomas slovo Foontomas, již nevybere má dvě oo)

Range("B5").AutoFilter Field:=1, Criteria1:="F?ntomas"

Vybere slova která začinají na "F" a dále je jakýkoliv počet znaků a slovo a pak pokračuje/končí "ntomas". (tj. Vybere Foontomas, Fontomas)

Range("B5").AutoFilter Field:=1, Criteria1:="F*ntomas"

Nezobrazí slova obsahující "x"

Range("B5").AutoFilter Field:=1, Criteria1:="<>*x*"

Zobrazí slova, která obsahující "x"

Range("B5").AutoFilter Field:=1, Criteria1:="=*x*"

Zobrazí slova Pat a Mat

Range("B5").AutoFilter Field:=1, Criteria1:="Pat", Operator:=xlOr, Criteria2:="Mat"

Zpět na seznam kapitol.

Filtry na datum

Pro volbu datum využijeme funkci DateSerial.

DateSerial(year, month, day)

Vlastní kód:

Dim Date1 As Date Dim Date_str As Long ' datum můžete načíst z buňky (buněk) Date1 = DateSerial(2014, 7, 7) Date_str = Date1 ' předpkládám, že už je na listu filtr Range("B4").AutoFilter Field:=1, Criteria1:=">" & Date_str

Kód můžete doplnit o druhé kritérium, atd:

Dim Date1 As Date Dim Date2 As Date Dim Date_str1 As Long Dim Date_str2 As Long Date1 = DateSerial(2014, 7, 7) Date_str1 = Date1 Date2 = DateSerial(2014, 7, 16) Date_str2 = Date2 ' předpkládám, že už je na listu filtr Range("B4").AutoFilter Field:=1, Criteria1:=">" & Date_str1, _ Operator:=xlAnd, Criteria2:="<" & Date_str2

Zpět na seznam kapitol o tisku ve Wordu.

Filtry na čas

Vyfiltrovat čas větší než 12:10

Range("B5").AutoFilter Field:=1, Criteria1:=">12:10"

Vyfiltrovat čas větší než 12:10 využitím funkce TimeSerial

Dim db_Date As Double db_Date = TimeSerial(12, 10, 0) Range("B5").AutoFilter Range("B5").AutoFilter Field:=1, Criteria1:=">" & Format(db_Date, "hh:mm")

Čas využitím nastavení formátu

Dim Date1 As Long Range("B5").AutoFilter Field:=1, Criteria1:=">" & Format(0.506944444444444, "hh:mm")

Další v přípravě.

Zpět na seznam kapitol.

Filtry na barvu

Pokud víte číslo ve formátu RGB tak lmůžete využít:

Range("B3").AutoFilter Field:=1, Criteria1:=RGB(225, 0, 0), Operator:=xlFilterCellColor

Zpět na seznam kapitol.

Pokročilé možnosti filtrů

Pro dvě kritéria

Range("A1:D1").AutoFilter Field:=3, Criteria1:=12, _ Operator:=xlOr, Criteria2:=14

Pro několik kriterií:

ActiveSheet.Range("$A$1:$D$23").AutoFilter Field:=3, _ Criteria1:=Array("14", "16", "17"), _ Operator:=xlFilterValues)

Pro rozsah kritérií:

ActiveSheet.Range("$A$1:$D$23").AutoFilter Field:=3, _ Criteria1:="<22", _ Operator:=xlAnd, Criteria2:=">14"

Podrobnější popis a další ukázky v přípravě...

Zpět na seznam kapitol.

Autofilter excel vba - stahuj logo

Ke stažení

K dispozici několik ukázkových sešitů:

  • AutoFilter - souhrný sešit s příklady
  • AutoFilter - Pro práci s texty
  • AutoFilter - Filtr s datum

AutoFilter - souhrný sešit s příklady

AutoFilter - souhrný sešit s příklady v Excel VBA

Soubor Autofilter - praktické příklady - Excel VBA soubor ve formátu *.xlsm ke stažení zdarma. Soubor využívá makra (pro Excel 2007 a novější).


AutoFilter - Pro práci s texty

Jak na filtry jmen pomocí VBA v Excel

Soubor Jak filtrovat jména pomocí VBA Excel soubor ve formátu *.xlsm ke stažení zdarma. Soubor využívá makra.

AutoFilter - Filtr s datum

Jak na filtry datum - VBA v Excel

Soubor Jak filtrovat datum pomocí VBA Excel soubor ve formátu *.xlsm ke stažení zdarma. Soubor využívá makra.

Další související články:

Pokud už máte buňku (oblast) označenou můžete s ními dále pracovat například


Závěrem

Využíváte na filtry v Excel VBA? Máte nějaké zajímavé kódy, o které se chcete podělit? Můžete je zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:07

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře


Marianus

Přidáno: 22.07.14 12:47

... Ahoj/Dobry den - skusal som si automaticky filter a mam otazku reps priklad: - chcem na zakalde neakych kriterii urobit filter a v x- tom stlpci spocitat hodnoty napr. sum() - skusal som spocita mi aj skryte hdontoy , da sa to neako obist ? PS: neda sa tu pridat mail ench mi pride mail ked bude odpoved na moj prispevok ( pre buducnost ) Ďakujem

Pavel Lasák

Přidáno: 24.07.14 15:11

To Marianus: Využít funcki SUBTOTAL

Bohužel redakční systém na kterém web běží tohle neumí. V plánu mám jen doprogramovat možnost objednat si placenou konzultaci. Zatím, ale řeším jiné záležitosti.

LamRis

Přidáno: 09.09.14 16:37

Dobrý den, k filtru na datum bych možná doplnil zjednodušení ve tvaru: Range("B4").AutoFilter Field:=1, Criteria1:=">" & CLng(CDate("7.7.2014"))

Ivana

Přidáno: 28.01.16 12:00

Dobrý den, ráda bych se zeptala, je-li nějaká možnost, jak filtrovat data současně ve více sloupcích najednou (stejně pojmenovaných sloupcích, filtr dle stejných kritérií). Příklad: Máme sloupce rok, prijem, vydej, součet rok, prijem, vydej,... ráda bych vyfiltrovala všechny prijmy (bez ohledu na rok).. Nebo máme měřená data pro jednu jednotku (rychlost, výkon, ztráty apod) a chceme filtrovat vždy výkony pro jednotlivé jednotky.. Díky za rady..

vonv

Přidáno: 06.07.16 09:54

Dobrý den, používám excel 2013 a řeším pomocí VBA filtrování datumů. Bohužel filtr se chová tak, že skryje všechny řádky, včetně těch, které by měl zobrazit. Pokud se pak podívám na listu na zadaná kritéria filtru, tak ty jsou tam vyplněna správně a po jejich potvrzení na OK, filtr zobrazí data, tak jak má. Chová se to naprosto stejně i s daty a s kódem na této výukové stránce. Předpokládám, že tedy nemám chybu v kódu. Netušíte, kde by mohla být chyba a jak z toho ven? Pan google mi zatím nedokázal poradit. Díky.

vonv

Přidáno: 06.07.16 18:24

Tak nakonec můj problém vyřešilo použití zde uvedeného kódu od LamRis. Díky!

PETR

Přidáno: 18.09.16 05:39

Dobrý den, potrebuji poradit makro se skladovou tabulkou. Makro na vkladani dat mam hotove, ale nejde mi na vyskladeni polozky .... Dejme tomu ze v liste1 do policek B2:B5 vlozim data ty se ulozi do listu2 A1:A4.. a ted problem : potrebuji makro, ktere podle stejnych vstupnich parametru polozku najde ( vyfiltruje) a smaze ji... Dekuji za pomoc

PetrJ

Přidáno: 21.09.16 12:24

Dobrý den, zkouším jednoduché makýrko: ActiveSheet.Range("$A$8:$D$897").AutoFilter Field:=4, Criteria1:="=> (" 'List1'"!K4")" _ , Operator:=xlAnd, Criteria2:="<=49000", které nefunguje = potřebuji, aby za Criteria1:="=> mohlo být číslo z buňky K4 list1. Tzn, že bych mohl v K4 zadávat různé hodnoty a pak spusti makro= filtr, který zobrazil hodnoty mezi zadanými čísly.

Milan Valda

Přidáno: 30.09.16 18:12

wow, předem děkuji za výborný obsah webu. Nějakou chvíli jsem se trápil s automatickým filtrem na datum, který i s postupem od pana Lasáka nevracel nic (ač by měl) mám konkrétně na mysli: Dim Date1 As Date Dim Date_str As Long ' datum můžete načíst z buňky (buněk) Date1 = DateSerial(2014, 7, 7) Date_str = Date1 ' předpkládám, že už je na listu filtr Range("B4").AutoFilter Field:=1, Criteria1:=">" & Date_str filtr měl parametry doplněny na první pohled v pořádku, ale na pozadí nepracoval s atributy jako s daty (možná by něbylo od věci kdyby byla kapitola doplněna ;-). Nicméně zkusil jsem jako již kolega přede mnou postup od LamRis a světe div se, filtr funguje v naprostém pořádku, díky moc.







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |