Potřebujete vyfiltrovat tabulku podle seznamu hodnot v jiném listu? Tedy například vybrat jen určité produkty, zákazníky nebo zaměstnance a zobrazit je v samostatné tabulce? Pokud máte nejnovější Excel, je řešení velmi jednoduché díky funkci FILTER. Ale co když pracujete ve starším Excelu 2016 nebo 2019 a tuto funkci nemáte?
V tomto videu si ukážeme hned několik způsobů řešení. Začneme moderní funkcí FILTER a podíváme se dokonce na dva různé způsoby, jak s ní filtrovat podle seznamu hodnot. Potom si ukážeme jednoduchý filtr, který funguje i ve starších verzích Excelu.
A nakonec se podíváme na zajímavé dynamické řešení ve starších verzích Excelů – vytvoříme si vlastní alternativu funkce FILTER pomocí kombinace funkcí INDEX, AGGREGATE a IFERROR, která bude fungovat i v Excelu 2016 a bude reagovat na změny ve zdrojové tabulce úplně stejně jako moderní dynamické funkce.
Excelový soubor ke stažení:
FILTER funkce
Začneme nejjednodušší možností filtru a to je filtrování pomocí funkce FILTER, která je dostupná pro verze Excelu novější než 2021 nebo předplatitele Microsoft 365. Pokud k této funkci nemáte přístup, tak vydržte a za chvilku si ukážeme, jak stejného filtru docílit i ve starších verzích Excelů.
V příkladu máme jednoduchou tabulku a z této tabulky chceme vyfiltrovat záznamy do vedlejší tabulky pro zvolené produkty. Máme tedy více položek, podle kterých chceme filtrovat. Nicméně tyto položky jsou z jednoho sloupce ve zdrojové tabulce. Budeme tedy filtrovat podle logiky NEBO.
Ve funkci FILTER je několik možností, jak můžete tento příklad vyřešit. My si ukážeme dva. Nejprve napíšeme funkci FILTER, kde nejprve označíme oblast, tedy oblast, kterou chceme filtrovat. Chceme do vedlejší tabulky filtrovat celou tabulku, takže ji celou označíme. A následuje logická podmínka. My v tuto chvíli máme dvě logické podmínky. Nejprve potřebujeme vyfiltrovat záznamy pro první produkt a následně pro druhý. Takže podmínek bude více a pravidlo je, že každá podmínka musí být v samostatných závorkách. První podmínka je, že se produkt ve sloupci bude rovnat prvnímu produktu v listu. A jelikož pracujeme s logickým vyjádřením NEBO, tak musíme použít znaménko plus. A druhá podmínka je, že se sloupec s produkty ve zdrojové tabulce bude rovnat druhému produktu. Ukončíme všechny závorky a potvrdíme a funkce FILTER vrátí záznamy ze zdrojové tabulky pouze pro vybrané produkty. Pokud produkt v seznamu změníme, tak funkce FILTER reaguje a vrátí správné záznamy.
Ve funkci FILTER můžete ale kromě několika podmínek, které mezi sebou sečtete použít i funkci COUNTIFS. Napíšeme funkci FILTER, kde opět označíme oblast pro filtrování, což je celá zdrojová tabulka. A do logické podmínky napíšeme funkci COUNTIFS, kde v oblasti kritérií označíme seznam, podle kterého chceme filtrovat a v kritériu označíme sloupec ze zdrojové tabulky. Ukončíme funkce a než funkci potvrdíme, tak se podíváme, co funkce COUNTIFS vrací. Označíme funkci a vidíme, že se vrací série jedniček a nul. Jednička se vrací na řádku, kde je produkt ze seznamu, jelikož jednička v Excelu znamená pravda. A nula se vrací na řádcích, kde jsou jiné produkty než ty ze seznamu. A jelikož funkce FILTER v logickém parametru zahrnuje filtruje pravdu, tak se po potvrzení této funkce vrátí pouze řádky, na kterých byly jedničky.
Stejně jako předchozí způsob, i zde bude funkce dynamicky reagovat na změnu ve výběru v listu.
Toto byly dva způsoby, jak filtrovat podle listu pomocí funkce FILTER. Ale co když tuto funkci nemáte?
Rozšířený filtr
Pokud nemáte funkci FILTER a chcete vyfiltrovat hodnoty mimo zdrojovou tabulku podle seznamu, tak můžete použít rozšířený filtr. Klikneme do zdrojové tabulky a na kartě Data najdeme v sekci Seřadit a filtrovat možnost Upřesnit.
Otevře se okno rozšířeného filtru. Zde nejprve vybereme oblast seznamu, což je celá zdrojová tabulka a pokud jste před kliknutím na filtr měli myš v tabulce, tak se tabulka automaticky označí včetně záhlaví. Oblast kritérií je seznam, podle kterého chcete filtrovat. Opět ho označíme včetně záhlaví. A jelikož chceme data vyfiltrovat mimo zdrojovou tabulku, tak vybereme Kopírovat jinam a ještě označíme buňku, kam chceme vyfiltrovaná data vykopírovat. Potvrdíme a rozšířený filtr vykopíruje data podle seznamu na místo, kam jsme určili. Všimněte si, že se tabulka vrátila i se záhlavím.
Jedná se o velmi jednoduchý způsob, jak rychle vyfiltrovat data mimo tabulku. Nicméně nevýhodou je, že se jedná o kopírovaná data, takže tato tabulka nebude reagovat na změny v seznamu, ani ve zdrojové tabulce. Pokud výběr změníme, tak musíme celý proces opakovat.
FILTER funkce ve starších verzích Excelu
Rozšířený filtr je sice jednoduchý, ale co když potřebujete ve starších verzích Excelů podobnou dynamiku jako u funkce FILTER? Tedy aby filtr reagoval na změny ve zdrojové tabulce nebo v seznamu hodnot? Můžete použít kombinaci několika excelových funkcí. Celou funkci složíme postupně spolu.
Začneme tím, že vytvoříme podmínku, podle které budeme filtrovat zdrojovou tabulku. A jelikož podmínky máme dvě, tak opět každou uvedeme do samostatných závorek. První podmínka je, že se sloupec s produkty bude rovnat prvnímu produktu. Jelikož ale pracujeme ve starších verzích Excelu a funkci budeme stahovat dolů, tak pole nesmíme zapomenout zafixovat. Tato podmínka vrátí stejně jako logická podmínka ve funkci FILTER sérii jedniček a nul, podle toho zda se na řádku vyskytuje produkt ze seznamu.
To je první podmínka. Máme ale i druhý produkt, takže k první podmínce musíme přičíst druhou podmínku, kde ověříme, že se ve sloupci s produkty nachází druhý produkt ze seznamu. Opět nezapomeneme pole zafixovat.
Pro filtr hodnot použijeme za chvíli funkci AGGREGATE. Funkce AGGREGATE umí ignorovat chybové hodnoty, což je přesně to, co budeme za chvíli potřebovat. Musíme tedy docílit toho, že na řádcích, kde jsou hodnoty ze seznamu zůstanou jedničky a na ostatních řádcích, kde jsou teď nuly budou chybové hodnoty. Toho nejjednodušeji docílíme tak, že tyto funkce vydělíme jedničkou. Nejprve obě podmínky zabalíme do závorky a následně před ně napíšeme jedničku a děleno. Když totiž jedničku vydělíme jedničkou, tak se pořád vrátí jednička. Když ale jedničku vydělíme nulou, tak se vrátí chyba.
Teď máme tedy na řádcích, kde jsou vybrané produkty jedničky. Na tyto řádky, ale potřebujeme místo jedniček dostat pořadová čísla řádků ze zdrojové tabulky. Na to existuje jednoduchý trik s kombinací funkcí ŘÁDEK. Celou tuto podmínku vynásobíme a v závorce bude funkce ŘÁDEK, kde označíme jakýkoli sloupec ze zdrojové tabulky. A nezapomeneme rozsah zafixovat. Tato funkce vrátí pořadová čísla řádků podle listu v Excelu. Abychom ale docílili toho, že se na prvním řádku vrátí jednička a nikoli trojka, tak od této funkce ŘÁDEK odečteme druhou funkci ŘÁDEK, ve které označíme buňku záhlaví, kterou plně zafixujeme.
Toto je základ podmínky pro funkci AGGREGATE. A proč funkce AGGREGATE? Tato funkce je skvělá pro toto řešení ze dvou důvodů. Umí ignorovat chybové hodnoty a tím pádem odfiltruje hodnoty, které nemáme v seznamu a za druhé pro její potvrzení starší verze Excelu nepotřebují klávesy CTRL+SHIFT a ENTER.
Takže to teď celé zabalíme do funkce AGGREGATE. Ve funkci AGGREGATE chceme funkci SMALL, takže číslo 15. Chceme ignorovat chyby, takže číslo šest. Následuje celá naše podmínka a jelikož pracujeme s funkcí SMALL, tak musíme určit i parametr k. A opět ho chceme určit dynamicky, což uděláme pomocí funkce ŘÁDKY. Pozor, zatímco pořadová čísla v tabulce jsme určovali pomocí funkce ŘÁDEK, tak zde se jedná o funkci ŘÁDKY. A v této funkci vytvoříme dynamické rozpětí z aktuální buňky, ve které se právě nacházíme.
Funkci stáhneme dolů a funkce funguje. Teď musíme přiřadit hodnoty.
To uděláme pomocí funkce INDEX. Ve funkci INDEX označíme sloupec, ze kterého chceme vrátit hodnoty. Buňky musíme správně zafixovat, jelikož hodnoty potáhneme doprava, tak sloupec zafixujeme pro řádky.
Nakonec to zabalíme do funkce IFERROR abychom se zbavili chybových hlášek.
Výhodou této funkce je, že bude stejně jako funkce FILTER reagovat na změny v seznamu a ve zdrojové tabulce. Je to sice o dost složitější než funkce FILTER, ale pokud funkci FILTER nemáte a chcete dosáhnout stejného výsledku, tak toto je způsob, jak toho docílit.
Všechny tyto způsoby řešení, až na rozšířený filtr, reagují na změny v seznamu i ve zdrojové tabulce. Nereagují ale na nově přidaný seznam do listu. Pokud byste chtěli, aby funkce reagovaly i na nově přidané produkty do seznamu nebo nově přidané řádky do zdrojové tabulky, tak by bylo nejlepší změnit zdrojovou tabulku i seznam na excelovou tabulku. Tím zajistíte, že po přidání nových položek funkce zahrnou tyto nové položky do filtru.
