V dnešním videu si ukážeme, jak můžeme pomocí průřezu ovládat výsledky excelových funkcí a tedy i výsledky, které se zobrazí v tabulce. Jedná se o skvělý trik, který můžete využít třeba na souhrnných přehledech v Excelu. Průřezy jsou externí filtry, které se standardně vkládají buď k excelovým tabulkám nebo kontingenčním tabulkám a dovolují nám tyto tabulky filtrovat. Dnes si ukážeme malý trik, jak průřez použít i pro ovládání excelových funkcí. Kromě průřezu využijeme k řešení i několik dynamických funkcí jako FILTER, UNIQUE nebo SORT, které jsou dostupné ve verzích Excelu 2021 a Microsoft 365.
Excelový soubor ke stažení:
V sešitu máme dva listy. Na
listu zdroj máme tabulku se zdrojovými daty, z této tabulky potřebujeme na
listu Přehled vytvořit souhrnnou tabulku, kde by se sčítaly počty prodaných
kusů a tržby pro produkty. Nicméně produkty máme ve zdrojové tabulce rozdělené
podle dámské a pánské módy, a chceme si vybrat, zda se produkty v tabulce na
listu Přehled zobrazí pro dámskou nebo pánskou módu. A tento výběr právě chceme
provést pomocí průřezu, který na přehledech vypadá lépe než rozbalovací seznam.
Samozřejmě bychom pro řešení mohli použít kontingenční tabulku, ale co když kontingenční tabulku použít nechceme? Ukážeme si postup, jak takový přehled vytvořit.
Začneme tím, že zdrojovou tabulku změníme na excelovou tabulku. Klikneme do tabulky na listu Zdroj a buď na kartě Vložení vybereme Tabulka a nebo použijeme klávesovou kombinaci CTRL+T.
Odstraníme z tabulky typické proužkování a tabulku pojmenujeme jako Data.
Průřez vytvoříme ze sloupce, který máme rovněž na listu Zdroj. Abychom ze sloupce mohli vytvořit průřez, tak musíme sloupec rovněž změnit na excelovou tabulku. Když máme ze sloupce excelovou tabulku, tak ji přejmenujeme na Seznam. Klikneme do sloupce a z karty Návrh tabulky vložíme průřez do listu.
Následně průřez vyjmeme z listu Zdroj a vložíme ho na list Přehled.
Aby průřez filtroval souhrnnou tabulku, tak se musíme vrátit do sloupce Seznam a přidat do tabulky nový sloupec s funkcí SUBTOTAL. Sloupec nazveme jako Viditelný. Ve funkci SUBTOTAL použijeme funkci 103 pro výpočet počtu neprázdných řádků v tabulce, jelikož potřebujeme aby funkce POČET2 ignorovala skryté a odfiltrované řádky.
Tato funkce teď zajistí, že když v průřezu vybereme Dámskou módu, že v tabulce Seznam zůstane viditelný pouze řádek s dámskou módou, u kterého funkce SUBTOTAL vrátí jedničku. A pokud v průřezu vybereme Pánskou módu, tak v tabulce Seznam zůstane pouze Pánská móda s jedničkou. Tyto jedničky následně použijeme ve funkci FILTER pro tvorbu souhrnné tabulky.
V průřezu vyfiltrujeme jeden z typů produktů, třeba dámskou módu a začneme tvořit souhrnnou tabulku.
V prvním sloupci produkt musíme vytvořit jedinečný seznam produktů ze zdrojové tabulky, které spadají pod vybraný typ produktu. Musíme tedy nejprve určit, jaký typ produkt máme vybraný v průřezu. To uděláme pomocí funkce FILTER. Ve funkci FILTER vybereme jako pole sloupec typ produktu z tabulky Seznam a jako parametr zahrnuje bude logická podmínka, že se sloupec Viditelné má rovnat jedničce. Když funkci potvrdíme, tak tato funkce FILTER vrátí to, co je vybrané v průřezu.
Teď nám tato funkce FILTER pomůže vyfiltrovat správné produkty ze zdrojové tabulky. Takže tuto funkci FILTER zabalíme do druhé funkce FILTER. V této funkci FILTER bude pole sloupec produkty ze zdrojové tabulky Data a jako podmínka zahrnuje bude, že se sloupec typ produktu z tabulky Data má rovnat druhé funkci FILTER. Tato funkce po potvrzení vrátí celý seznam produktů ze zdrojové tabulky, které spadají pod vybraný typ produktu.
Z tohoto seznamu všech produktů, ale potřebujeme vytvořit jedinečný seznam produktů, takže funkci FILTER zabalíme do funkce UNIQUE, a pokud chceme mít produkty v tabulce ještě seřazené podle abecedy, tak funkci UNIQUE zabalíme do funkce SORT.
Teď máme ve sloupci jedinečný seznam produktů a můžeme pokračovat s výpočtem celkového počtu prodaných kusů produktu. To uděláme pomocí funkce SUMIFS.
Ve funkci SUMIFS nejprve označíme pole součtu, což je sloupec počet ze zdrojové tabulky Data. Následuje první oblast kritérií, což je sloupec produkty ze zdrojové tabulky Data a jako kritérium označíme všechny produkty ze souhrnné tabulky, které jsme vytvořili pomocí kombinace funkcí FITER. Následuje ale ještě druhá oblast kritérií, jelikož musíme sčítat pouze produkty, které spadají pod vybraný typ produktu. Takže druhou oblastí je sloupec typ produktu ve zdrojové tabulce Data a jako kritérium musí následovat funkce FILTER, ve které opět určíme, jaká položka je vybraná v průřezu.
Pro součet celkových tržeb použijeme stejný postup jako u součtu počtu prodaných kusů produktu.
Souhrnná tabulka, jejíž obsah je určený výběrem v průřezu, je hotová.
Jedna odpověď
😊