Tweet

FILTER (FILTER) - vyfiltrovat data v Excel

Úvodem do funkce FILTER

Funkce poslouží na vyfiltrování dat. K filtropvání pomocí funkce FILTER (v angličtině FILTER). V totmo článku se na tuto funkci zaměřím jednak její použití v listu, možné komplikace, tipy až po využití funkce ve VBA (stejně tak ukáži možnosti jak použít klasické funkce a filtrovat datá také, nebo-li bez použití funkce FILTER).

Pro přehlednost je rozdělen na kapitoly:

• Teorie použití FILTER
• Základní použití FILTER
• FILTER - více podmínek
• FILTER Excel vs Google
• FILTER ruční výpočet - když není k dispozici
• Chyby v FILTER
• Použití s funkce FILTER ve VBA
• Související články
• Ke stažení pdf o funkci FILTER
• Ke stažení - praktické ukázky

Teorie použití FILTER

Funkce FILTER (v angličtině FILTER) vyfiltruje data v Excel na základě podmínky (případně podmínek).

Syntaxe česky:

=FILTER(matice;zahrnuje;pokud_prázdné)

Syntaxe anglicky:

=FILTER(array,where,[if_empty])

Popis argumentů:

• matice (array) - povinný argument - Oblast kterou potřebujete vyfiltrovat
• zahrnuje (where) - povinný argument - podmínka pro filtr, nebo podmínky za využití operátoru + (and) * (or)
• pokud_prázdné (if_empty) - nepovinný argument - co v případě že výsledek vyfiltrování bude prázdný, ať neskončí chybou

Poznámka

U Google tabulek funkce FILTER funguje jinak, popsáno o pár kapitol dále v tomto článku.

Základní použití FILTER

Potřebuji vyfiltrovat z tabulky:

• ženy neboli F
• marťany neboli M a pokud není žádný vypsat text nenalezeno

=FILTER(B6:D12;D6:D12="F") =FILTER(B6:D12;D6:D12="M";"nenalezeno")

FILTER více podmínek

Ze zdrpjové tabulky potřebuji vyfiltrovat (do separatní tabulky) například:

• ženy s platem větším než 18.000,-Kč)
• muže nebo ženy

ženy s platem větším než 18.000,-Kč)

Ve funkci má jen jeden argument pro podmínku, ale můžete využít operátor OR ve fromě krát (neboli hvězdičky *). Neboli musí jít o ženu i musí mít plat nad 18.000,-Kč. Možná jste se setkali s podobnou interpretaci u funkce SKALÁRNÍ.SOUČIN. U funkce FILTER funguje naprosto stejně.

=FILTER(B8:G13;(D8:D13="F")*(E8:E13>18000))

muže nebo ženy

Jde o podobný úkol jako předchozí, ale musí jít o operátor OR. Nebo-li chcete muže nebo ženy (M/F). Nechcete X (třeba někdo neodpověděl ;) ), nebo tento údaj zatím v tabulce nemáte.

Or, neboli nebo nakradíte znaménkem plus (+).

=FILTER(B8:G13;(D8:D13="F")+(D8:D13="M"))

Pozor při použití krát (*) nenajde nic, nemáte žádnou osobu, která je zároveň muž a zároveň i žena (to technicky nejde, pokud jste biolog, či filozof, tak v Excel to nejde!).

Pokud nic nenajde a nemáte ošetřeno třetím argumentem vrátí funkce chybovou hodnotu #VÝPOČET!

=FILTER(B8:G13;(D8:D13="F")*(D8:D13="M"))

FILTER Excel vs Google

Pozor podobá se funkci FILTER v Google tabulkách. Soudruzi v Microsoftu ale vytvořili funkci FILTER trochu jinak :( Má jiný název a trochu jinak deklarované argumenty pro řazení!

Více o funkci SORT v Google tabulkách.

pro Google tabulky FILTER(range, condition1, [condition2])

• range - povinný - oblast pro třídění
• condition1 - povinný - oblast s podmínkou1
• condition2 - nepovinný - oblast s podmínkou2

Poznámka

Stejně jako u funkci SORT, SORTBY se funkce neshodují (takže pokud tvoříte i v Google tabulkách musíte se učit funkci 2x).

Ruční výpočet funkce FILTER

Pro ruční výpočet, když ještě funkci FILTER v Excelu nemáte a nechcete použít klasický filtr nebo kontingenční tabulku. Dva postupy

pro EN pokud potřebujete například data do grafu a chyba #N/A vám nevadí =VLOOKUP($B6:$B12;IF($C$6:$C$12="F";$B$6:$D$12;"");1;FALSE) pro CZ jako výše uvedené =SVYHLEDAT($B6:$B12;KDYŽ($C$6:$C$12="F";$B$6:$D$12;"");1;NEPRAVDA)

Když ale potřebujete filtrovat a nezobrazovat data, které do výběru nepatří, samozřejmě, že použijete jiné spojení funkcí vkládáné přes maticový vzorec a rozkopírujete (je jasné, že pro každý sloupec bude jiný). Pro jednoduchost jdeme již od druhého řádku (používám pro nějaké výpočty, tak nebudu měnit ;) ). Podmínka, že hledám ženy ;) jako v prvním ukazkovém příkladě.

- pro první sloupec v EN =IF(ROWS(E$2:E2)>COUNTIF($B$2:$B$8;"F");"";INDEX(A$2:A8;SMALL(IF($B$2:$B$8="F";ROW($B$2:$B$8)-ROW($B$1));ROWS(E$2:E2)))) - pro druhý =IF(ROWS(F$2:F2)>COUNTIF($B$2:$B$8;"F");"";INDEX(B$2:B$8;SMALL(IF($B$2:$B$8="F";ROW($B$2:$B$8)-ROW($B$1));ROWS(F$2:F2)))) - atd. nezapomenout Ctrl + Shift + Enter - pro první sloupec v CZ =KDYŽ(ŘÁDKY(E$2:E2)>COUNTIF($B$2:$B$8;"F");"";INDEX(A$2:A8;SMALL(KDYŽ($B$2:$B$8="F";ŘÁDEK($B$2:$B$8)-ŘÁDEK($B$1));ŘÁDKY(E$2:E2)))) -atd. pro další sloupce a nezapomenout Ctrl + Shift + Enter

Poznámka

Pokud má někdo lepší trik na filtr, za využití funkcí (bez nové FILTER ;) můžete doplnit do komentáře.

Chyby v FILTER

Jaké chyby mohou v Excel nastat. Máte stenou zdrojovou tabulku.

Co může nezafungovat:

• Nic nenalezeno.
• Omezená dynamická oblast.
• Neexistuje hledaný sloupec, nebo je posunut
• Způsob filtrování.

Nic nenalezeno.

Obdržíte chybovou hodnotu:

=FILTER(B8:G13;D8:D13="x")

Ošetření chyby je jednoduché použít třetí argument:

=FILTER(B8:G13;D8:D13="x")

Omezená dynamická oblast.

Pokud Excel nemůže vypsat celou oblast nebo jsou v oblasti sloučené buňky, obdržíte chybu: #PRESAH! (pro pokročilé, číslo chyby je 9).

=FILTER(B8:G13;D8:D13="F";"nenelezeno")

Neexistuje hledaný sloupec, nebo je posunut

Pokud sloupec neexistuje může funkce vrátit chybu VYPOČET, případně pokud je rozhozen rozsah hledání tak vrací chybu #HODNOTA!

=FILTER(B8:G13;A9:A12="F")

Další chyby

Další chyby a komplikace v přípravě.

Použití s funkce FILTER ve VBA

V přípravě.

Související články s FILTER

Další články související se funkci. Jelikož se jedná o nové funkce, články sepisují postupně. Následně doplním.

• v přípravě FILTER, SORT
• FILTER - aneb co napsal o této funkci Microsoft

Jde o nové funkce postupně návody doplním o praktická použití (prostě klasika co mám u ostatních funkcí) až mi budou fungovat v mém Excel.

Pfd dokument o funkci FILTER (FILTER)

Soubor Jak na funkci FILTER (FILTER) pro Excel v pdf ke stažení zdarma.

Ke stažení ukázka FILTER

Soubor v přípravě.

Závěrem

Využíváte nějaký trik s funkci FILTER, můžete zmínit v komentářích.

Článek byl aktualizován: 11.03.2023 14:03

Pavel Lasák - autor webu

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ů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.