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

Jste zde: Úvodní stránka » excel » funkce-nove » FILTER-vyfiltrovat-data-v-Excel
Microsoft Excel logo

FILTER (FILTER) - vyfiltrovat data v Excel

Videokurzy Excel

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

Jak vyfiltrovat data využitím funkce FILTER v Excel.

Pozor, tato nová funkce se podobá stejně nazvané funkci FILTER v Google tabulkách (jen jsou použity jiné argumenty).

Od Excel 2016 - v Office 365.

Úvodem do funkce FILTER

Jedná se o funkci dostupnou v Insider. Stav aktualizovaný k 07/2019.

Pozor u této funkce jde o nový tip jejiž velikost se dynamicky mění (díky tomu jsou i nové chybové hodnoty).

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

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") Excel funkce FILTER - základ

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
Excel funkce FILTER - více podmínek - zdrojová tabulka

ž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)) Excel funkce FILTER - více podmínek - and

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")) Excel funkce FILTER - více podmínek - and

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) Excel funkce FILTER - základ

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 Excel funkce FILTER - základ

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.

Excel funkce FILTER - více podmínek - zdrojová tabulka

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") Excel funkce FILTER - chyba výpočet

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") Excel funkce FILTER - chyba výpočet

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.

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.


Microsoft Excel VBA - stahuj logo

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: 10.07.2019 20:26

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 zatím nejsou

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







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 - 2019 | 642

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.