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

" ; // echo "kompletni_cesta :" ; // echo $adresar_pro_vkladani ; // echo "
" ; // echo " Tady bude možnost přidání komentáře" ; // echo "
" ; // echo "
" ; $kompletni_cesta = "komentare"; $adresar_pro_vkladani = $kompletni_cesta; ?> \n"; reset($polozky); while (list($key, $val) = each($polozky)) { if ($val != "." && $val != "..") { if (!is_dir($val)) { // echo "$val
\n"; $fp = FOpen ($adresar_pro_vkladani."/".$val, "r"); //otevře soubor book.dat pro čtení $data = FRead ($fp, FileSize($adresar_pro_vkladani."/".$val)); //přečte data ze souboru a uloží do proměnné "data" - kvůli tomu aby se zobrazoval poslední příspěvek nahoře FClose($fp); echo "$data"; } } } ?>





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 - 2020 |