Filtry bez filtrů

Úvodem

Cílem článku je ukázat netradiční použití funkcí, které nahradí sofistikovaný filtr v Excel. Cílem je do příslušných buněk zadat hledané veličiny a pokud se v dané tabulce nacházejí, tak budou vypsány.

Pro přehlednost je článek rozdělen:

• Předpokládané znalosti
• Zdrojová data a vyhledávací buňky
• Pomocná tabulka hledání
• Výsledná tabulka - zde budou filtrované hodnoty
• Soubor ke stažení
• Související články

Ukázka

Jak může vypadat:

Předpokládané znalosti

Při tvorbě předpokládám, že máte mírně pokročilé znalosti Excel a umíte používat řetězení funkcí a také smíšené odkazy. Předpokládám tyto znalosti:

• Relativní, absolutní a smíšené odkazování
• Funkce COUNTIF
• Funkce CONCATENATE
• Funkce SUMA
• Funkce SVYHLEDAT - hojně poptávaná ;)
• Funkce KDYŽ
• Funkce POSUN - moje oblíbená ;)
• Funkce POČET2
• Funkce IFERROR
• Funkce INDEX

Pokud umíte (zvládate), můžete se vrhnout na vyřešení, článek vám bude nápomocen (případně si můžete rovnou stáhnout hotovou ukázku, včetně řešení).

Zdrojová data a vyhledávací buňky

Zdrojová tabulka bude obsahovat například ID (pořadí), Jméno, Plat, Datum, Čas

ID	Jméno	Plat	Datum	Čas
1	Pavel	15 000	4.12.2015	15:10
2	Ivo	17 000	4.12.2015	16:20
3	Ivos	20 000	7.12.2015	11:10
4	Karel	10 000	7.6.2015	17:47
5	Karel Voršálek	15 000	1.1.2014	5:01

A doplním o "tabulku" co hledám.

ID	Jméno	Plat	Datum	Čas

Pomocná tabulka hledání

Postup řešení. Zdrojová tabulka se doplní o pomocnou tabulku, která pomůže určit, zda daný řádek patří do hledaného rozsahu (odpovídá, hledaným hodnotám).

Nejprve pro každý sloupec zdrojové tabulky samostatně určíme, zda odpovídají hledaným kritériím. Následně spočteme počet shodných hodnot v řádku. Pokud je počet v řádku shodný s počtem hledaných hodnot zapíše se hodnota daného řádku do dalšího pomocného sloupce. Následně seřadíme čísla řádku, tak ˇAt neobsahují prázdné řádky. Následně již stačí vyplnit cílovou tabulku.

Podrobněji o použitých funkcích

Pomocí této tabulky zjistíte, zda daná položka se shoduje s hledáním (hledanou hodnotou), využijete funkce COUNTIF

=COUNTIF(A18;$A$6)

V dalším pomocném sloupci sečtete (chcete zobrazit jen řádky, které ve všech ohledech splňují hledaná kritéria).

=SUMA(G18:K18)

Ve dalším pomocném sloupci pořadí zobrazíme ID sloupců, které splňují všechna kriéria.

=KDYŽ(M18=$M$6;A18;"")

Počet vyplněných sloupců je z tabulky ve které je hledání. Jelikož někdy potřebujete hledat jen podle jména, někdy podle jména a platu, atd.

=POČET2(A6:E6)

Posledním pomocným sloupcem je sumarizace pořadí, využijete funkce SVYHLEDAT a POSUN. Lze řešit i jinak, ale já zvolil funkci POSUN, mám já rád, tak proč jí nepoužít ;) šlo by doplnit o funkci, která ošetří chybové hodnoty pokud nebude nalezeno, ale to nechám už na vás, pro další postup chybová hodnota nevadí.

=SVYHLEDAT($M$6;M18:N22;2;NEPRAVDA) =SVYHLEDAT($M$6;(POSUN($M$18:$N$22;O18;0));2;NEPRAVDA)

Takže máme ID (pořadí) řádku splňující požadovaná kritéria. Teď už zbývá jen vytvořit tabulku výsledku, na to se podíváte v další kapitole.

Výsledná tabulka

Z předchozí kapitoly máte hotové pořadová čísla, teď už není nic jednoduššího než si vypsat řádky, které splňují naše požadavky. Využijeme funkci INDEX (lze i SVYLHEDAT), ale ať se procvičíte ;)

No a aby nebylo potřeba tvořit pro každý sloupce, lze využít smíšené odkazování. Funkce je doplněna o IFERROR, aby nenalezené řádky ve výsledné tabulce byly prázdné. V této tabulce obsahují mezerník.

=IFERROR(INDEX(A$18:A$22;$O18;1);" ")

Povedlo se? Pokud ne můžete si v další kapitole stáhnout hotovou ukázku.

Ke stažení

Soubor Filtry bez filtrů - Excel ke stažení zdarma.

Související články

Nehcete se mořit s vlastní imitací filtrů, můžete využít filtry zabudované přímo v Excel:

• Filtry v Excel
• Rozšířený filtr
• Porovnat dvě tabulky

Závěrem

Máte inspirativní příklad různého použití funkci? Můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 10:56

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