V dnešním videu se podíváme na několik triků s průřezy, což jsou externí filtry, které můžete napojit buď na kontingenční nebo excelovou tabulku. Ukážeme si, jak vložit do průřezu ikony nebo jak zajistit, že se položky v průřezu zobrazí vedle sebe nebo jak přenést výběr v průřezu do nadpisu v grafu.
Excelový soubor ke stažení:
Ikony v průřezu
V prvním příkladu máme ze zdrojových dat vytvořenou kontingenční tabulku, na kterou máme napojený průřez, pomocí kterého můžeme produkty filtrovat buď pro dámské nebo pánské produkty. Věděli jste ale, že do průřezů můžete vložit i ikony? Řekněme, že bychom v průřezu nechtěli slova dámské a pánské, ale třeba ikony panenky a panáčka. K tomu vám stačí přidat pomocný sloupec do zdrojových dat.
Vrátíme se ke zdrojové tabulce, do které vložíme nový sloupec, který nazveme třeba Ikona. V tomto pomocném sloupci použijeme funkci KDYŽ / IF. Ve funkci KDYŽ ověříme podmínku, že se nejprve typ produktu rovná dámské módě. Pokud bude podmínka splněná, tak chceme vrátit ikonu panenky. Ikony vyvoláme stisknutím klávesové kombinace Windows a tečka.
Vybrané ikony vložíme do funkce a nezapomeneme, že ikony jsou textová hodnota, takže je musíme uvést do uvozovek.
Vrátíme se ke kontingenční tabulce a obnovíme spojení na zdrojovou tabulku. Tím se přidá sloupec Ikona mezi pole kontingenční tabulky a my teď můžeme vložit nový průřez, do kterého vybereme jako pole sloupec Ikona. A máme ikony v průřezu.
Ikony můžete dokonce spojit s textem. Řekněme, že bychom v průřezu chtěli jak ikonu, tak text. I to můžeme spojit. K tomu použijeme druhý pomocný sloupec, kde spojíme ikonu s textem.
Vrátíme se ke zdrojové tabulce a upravíme podmínku KDYŽ. Když bude podmínka splněná, tak chceme kromě ikony vrátit za ikonou i slovo Dámské, takže za ikonu napíšeme mezeru a slovo Dámské. Celé sousloví musí být v uvozovkách. To samé uděláme pro pánskou módu.
Opět se vrátíme ke kontingenční tabulce a obnovíme spojení a průřez se aktualizuje a do průřezu se přidá jak ikona, tak text.
Vodorovné rozložení položek v průřezu
Klasicky se položky v průřezu vždy vloží pod sebou. Co když ale chceme položky vedle sebou? To uděláme tak, že klikneme na průřez a tím aktivujeme kartu Průřez.
Zde máme sekci Tlačítka, kde si můžeme nastavit počet sloupců. V průřezu máme tři tlačítka, takže nastavíme tři sloupce. Tím se položky v průřezu seřadí vede sebe. Kdybychom měli v průřezu dvě položky, tak nastavíme dva sloupce a kdybychom měli 4 položky, tak nastavíme 4 sloupce. A tím se položky seřadí vedle sebe.
Neměnící se poloha průřezu
V základním nastavení, když máme na kontingenční tabulku navázaný průřez, který máme umístěný třeba nad tabulkou, se mění poloha průřezu v závislosti na velikosti buněk v kontingenční tabulce. V našem případě, když přepneme mezi typem produktu, tak se poloha průřezu mění v závislosti na šířce sloupce A. Pokud chcete, aby se průřez přestal přizpůsobovat podkladovým buňkám, tak na něj kliknete pravým tlačítkem myši a vyberte Velikost a Vlastnosti.
V sekci Vlastnosti změníme základní nastavení z Přesouvat a měnit velikost s buňkami na Nepřesouvat a neměnit velikost s buňkami. Tím zajistíte, že se u změny velikosti kontingenční tabulky průřez neposune.
Pouze aktivní položky v závislém průřezu
V dalším příkladu máme na jedné kontingenční tabulce navázané dva průřezy. Tyto průřezy jsou na sobě závislé, takže když vybereme druh produktu, tak se ve druhém průřezu tmavě obarví produkty, které spadají pod daný druh produktu a ostatní produkty zesvětlají. Nicméně v průřezu jsou stále viditelné. Když změníme druh produktu, tak se nahoru posunou aktivní položky spadající pod vybraný druh produktu a ostatní produkty, které spadají pod jiný druh zesvětlají, ale opět v průřezu zůstanou.
Pokud se těchto neaktivních položek v závislém průřezu chcete zbavit, tak na průřez klikněte pravým tlačítkem myši a vyberte Nastavení průřezu.
V nastavení průřezu najdete možnost Skrýt položky neobsahující data, kterou zaškrtnete.
Po potvrzení zůstanou v průřezu pouze aktivní položky, které spadají pod kategorii vybranou v prvním průřezu.
Výběr z průřezu v nadpisu excelového grafu
V dalším příkladu máme z excelové tabulky vytvořený graf vývoje tržeb a na tabulku napojený průřez, pomocí kterého filtrujeme produkty podle typu. Přičemž nadpis grafu se mění v závislosti na tom, co máme vybraného v průřezu. Když vybereme pánské produkty, tak se nadpis změní na tržby pro pánské produkty a když naopak vybereme dámské produkty, tak se nadpis změní na tržby pro dámské produkty. Tohoto efektu docílíme pomocí několika excelových funkcí. Základem jsou funkce FILTER a UNIQUE, které využijí pomocného sloupce ve zdrojové tabulce.
Takže do zdrojové tabulky přidáme pomocný sloupec, ve kterém použijeme funkci AGGREGATE. Ve funkci AGGREGATE použijeme jako funkci POČET2, jelikož chceme počítat viditelné hodnoty. V možnostech vybereme 5, ignorovat skryté hodnoty. A jako matici označíme třeba sloupec s produkty. Tato funkce AGGREGATE vrátí na viditelných řádcích jedničku. Nicméně pokud nějaké položky z tabulky odfiltrujeme, tak tato funkce u skrytých/odfiltrovaných hodnot vrátí nuly.
Vrátíme se ke grafu a vytvoříme funkci, která zobrazí správný nadpis. Základem je funkce FILTER, ve které budeme filtrovat sloupec typ produktu ze zdrojové tabulky a jako podmínku zahrnuje je sloupec filter, který se musí rovnat jedničce. Nicméně tato funkce vrátí všechny viditelné položky, my chceme ale jen jedinečný seznam, takže funkci FILTER zabalíme do funkce UNIQUE. Aby se text spojil, tak to ještě zabalíme do funkce TEXTJOIN, kde oddělovačem textu je čárka a chceme skrýt prázdné hodnoty. Tato funkce vrátí viditelné položky oddělené čárkou. A teď to stačí spojit s textem Tržby pro a za funkcí produkty. Pokud nechcete aby se slova pánské a dámské vracela s velkými počátečními písmeny, tak funkci TEXTJOIN zabalíme ještě do funkce MALÁ / LOWER.
Po potvrzení se vrátí správný název, který bude základem nadpisu grafu. Teď stačí kliknout jednou na nadpis v grafu, kliknout na hranu nadpisu, napsat rovná se a propojit nadpis s buňkou, kde máme nadpis.
A teď máme nadpis grafu, který se mění v závislost na výběru v průřezu.
