V dnešním videu si ukážeme deset triků, které se vám budou hodit u práce s kontingenčními tabulkami. Ukážeme si jak v kontingenční tabulce zobrazit podrobnosti, jak filtrovat sloupce, které zdánlivě filtrovat nelze, jak vytvořit vícenásobné reporty na jedno kliknutí myši nebo jak v kontingenční tabulce použít vlastní formát a podmíněné formátování. Dejte nám v komentáři pod videem vědět, který trik máte nejradši a jaký je váš osobní tajný trik u práce s kontingenčními tabulkami.
Excelový soubor ke stažení
10 triků s kontingenčními tabulkami
A vrhneme se rovnou na první triky.
1. Zobrazit podrobnosti
V prvním příkladu máme kontingenční tabulku, kdy máme na řádcích zobrazené produkty a v oblasti hodnoty máme celkové tržby. Teď si představte, že za vámi někdo přijde a zeptá se vás, z jakých podkladových dat je tvořené libovolné číslo v kontingenční tabulce. Místo toho abyste se vraceli ke zdrojové tabulce a složitě ji filtrovali, tak vám stačí dvakrát kliknout levým tlačítkem myši na vybrané číslo v kontingenční tabulce a do excelového sešitu se přidá nový list, na kterém se zobrazí hodnoty, ze kterých je složené toto číslo. Stejného výsledku dosáhnete, když se vrátíte ke kontingenční tabulce a na vybrané číslo kliknete pravým tlačítkem myši a vyberete Zobrazit podrobnosti. Když jste s analýzou dat na pomocném listu hotovi, můžete tento list zase bez obav smazat.
2. Více reportů na jedno kliknutí
V dalším příkladu máme kontingenční tabulku, kde máme produkty a jejich celkové tržby. Navíc máme nad tabulkou filtr pro pobočku. Řekněme, že se po nás chce, abychom vytvořili report pro každou pobočku v podobě tabulky jako máme v ukázce. V praxi by to znamenalo postupně filtrovat jednotlivé pobočky nad tabulkou a tabulky někam kopírovat. Místo toho můžeme využít trik s rychlým generováním reportů. Klikneme do tabulky a na kartě Analýza kontingenční tabulky a rozklikneme Možnosti. Zde vybereme Zobrazit stránky filtru sestavy. Excel se zeptá, zda chceme report vytvořit z poboček, jelikož to je jediné pole filtru, potvrdíme a do excelového sešitu se přidá stejný počet nových listů jako bylo poboček ve filtru. Každý list je pojmenovaný jako pobočka a na každém listu se nachází stejná tabulka s produkty a celkovými tržbami pro vybranou pobočku.
3. Mnohonásobný filtr
V kontingenční tabulce máme v záhlaví tabulky na výběr ze dvou typů filtrů. Můžeme sloupec filtrovat podle popisků nebo podle hodnot. Zkusíme nejprve vyfiltrovat tabulku pro text. V záhlaví tabulky vybereme Filtr popisků a vybereme například filtr Začíná na a vybereme písmeno K. Tím se tabulka vyfiltruje jen pro produkty, které začínají písmenem k. Řekněme, že teď z těchto produktů chceme ještě odfiltrovat produkty, jejichž celkové tržby jsou nižší než 200 000 Kč. Klikneme do záhlaví ještě jednou a vybereme Filtr hodnot a zde vybereme vyšší nebo rovno 200 000 Kč. Filtr potvrdíme a vidíme, že se tabulka přefiltrovala, ale původní filtr z popisků se tímto novým filtrem zrušil. Pokud chceme naráz uplatnit oba filtry najednou, tak musíme tuto možnost nejprve v kontingenční tabulce povolit. Klikneme do tabulky a na kartě Analýza kontingenční tabulky vybereme Možnosti kontingenční tabulky a na záložce Součty a filtry vybereme Povolit více filtrů u jednoho pole. Vrátíme se k tabulce a zkusíme filtr. Momentálně máme stále aktivní filtr na hodnoty vyšší než 200 000 Kč. Zkusíme vybrat filtr popisků a opět vybereme text, který začíná písmenem k. Potvrdíme a teď už kontingenční tabulka nechala aktivní oba filtry najednou.
4. Automatická šířka sloupce
Kontingenční tabulky jsou přednastavené tak, aby se šířka sloupců vždy dynamicky přizpůsobovala šířce obsahu. To se v některých situacích může hodit, ale ve většině případů je to spíše otravná vlastnost kontingenčních tabulek, jelikož to způsobuje, že se po každé aktualizaci šířka sloupců vrátí do původního nastavení. Řekněme, že chci mít šířku sloupce nastavenou de vlastních kritérií. Když aktualizuji kontingenční tabulku, tak se šířka sloupce automaticky po aktualizaci vrátí do původní šířky. Pokud tomu chceme zabránit tak můžeme kliknout do tabulky a buď na kartě Analýza kontingenční tabulky nebo ve výběru pravého tlačítka myši vybrat Možnosti kontingenční tabulky a zde odškrtnout Při aktualizaci automaticky upravit šířky sloupců. Potvrdíme nastavení. Rozšíříme sloupec a aktualizujeme kontingenční tabulku. Šířka sloupce teď i po aktualizaci zůstala nezměněná.
5. Vlastní formát
V dalším příkladu máme v kontingenční tabulce spočítaný rozdíl mezi dvěma sloupci. Jedná se o meziroční rozdíl v tržbách. Rádi bychom zvýraznily hodnoty, které jsou kladné, což znamená, že u těchto produktů jsme zaznamenali meziroční nárůst. U kladných rozdílů chceme zobrazit fajfku. K tomuto účelu můžeme v kontingenční tabulce využít výhod vlastního formátu. Klikneme do sloupce s rozdílem a vybereme Formát čísla. V nabídce vybereme Vlastní formát. K zobrazení symbolů využijeme klávesovou kombinaci Windows a tečku. Tím se otevře okno s ikonami a symboly. Vybereme fajfku a potvrdíme. Fajfku chceme vložit jen ke kladným hodnotám, takže ji vložíme před první hodnotu, což je ve vlastním formátu formát pro kladné hodnoty. Záporné hodnoty chceme nechat v původním stavu, takže formát zkopírujeme a napíšeme před něj mínus, jelikož druhá pozice je pro záporné hodnoty. Formát potvrdíme a teď máme u kladných hodnot zobrazenou fajfku, kdežto záporné hodnoty jsou v původním formátu.
6. Podmíněné formátování
Stejně jako můžete v kontingenční tabulce používat vlastní formát, můžeme používat i podmíněné formátování. Řekněme, že chceme v této tabulce obarvit rozdílové hodnoty tržeb barevnou škálou. Klikneme do kontingenční tabulky do sloupce rozdíl a na kartě Domů vybereme Podmíněné formátování. Zde vybereme možnost barevná škála a vybereme libovolnou barevnou škálu. Tím se obarví buňka, ve které jsme byli, když jsme podmíněné formátování vybrali. Aby se podmíněné formátování přeneslo na všechny buňky ve sloupci a to i po přidání nových hodnot, tak musíme jestli kliknout na malou ikonu Možnosti formátování, kde musíme vybrat Všechny buňky zobrazující Rozdíl tržeb pro produkt a roky. Pokud vyberete možnost Všechny buňky zobrazující Součet z tržba hodnot, tak se obarví i celkový součet. Tím, že vyberete jednu z těchto dvou možností, tak uděláte podmíněné formátování v kontingenční tabulce dynamické. Podmíněný formát se tím přenese i na jakákoliv nově přidaná data.
7. Řazení hodnot
Když tvoříte kontingenční graf z kontingenční tabulky, tak se vám někdy hodnoty v grafu zobrazí v opačném pořadí, než v jakém máte seřazené hodnoty v tabulce. V tabulce máme tržby seřazené dle jejich výše od nejvyšší tržby po nejnižší. Z tabulky chceme vytvořit pruhový graf. Klikneme do tabulky a na kartě Vložení vybereme Grafy a v nabídce vybereme pruhový graf. Jak vidíme, tak hodnoty v grafu máme seřazené, ale nejvyšší hodnota je vespodu. Pokud chcete v pruhovém grafu zobrazit sloupce od nejvyššího po nejnižší, tak aby nejvyšší hodnoty byly nahoře, tak musíme hodnoty v kontingenční tabulce seřadit obráceně, tedy od nejnižší hodnoty po nejvyšší. Tím se hodnoty v kontingenčním grafu přehodí.
8. Filtr hodnot
V kontingenční tabulce můžeme pomocí záhlaví filtrovat hodnoty dle popisků nebo hodnot. Nemáme zde ale přímou možnost filtrovat dle sloupce hodnot. Pokud bychom v následující tabulce chtěli vyfiltrovat hodnoty pouze pro vybrané tržby, tak v záhlaví tabulky takovou možnost nemáme. Existuje pro to ale trik. Stačí kliknout do vedlejší buňky vedle záhlaví a zmáčknout klávesovou kombinaci pro vložení automatického filtru CTRL+SHIFT+L. Tím se vloží filtr do celého záhlaví tabulky a my teď máme možnost plného filtru i nad sloupcem hodnot.
9. Průřez a časová osa
Kontingenční tabulku můžeme filtrovat nejen podle filtru nad tabulkou, ale i pomocí průřezů a časových os. Jedná se o externí filtry, které dovolují filtrovat kontingenční tabulku a grafy. Stačí kliknout do tabulky a na kartě Analýza kontingenční tabulky vybrat Vložit průřez. V nabídce si vybereme jaký filtr chceme vložit, řekněme, že nás zajímá filtr podle typu obchodu. Potvrdíme a do excelového sešitu se vložil externí filtr s typem obchodu. Pokud vybereme nějakou možnost, tak se kontingenční tabulka přefiltruje dle zvoleného výběru. Stejně tak můžeme vložit i časovou osu. Klikneme do tabulky a na kartě Analýza kontingenční tabulky vybereme Vložit časovou osu. Jediné datumové pole máme datum, takže ho potvrdíme a do sešitu se vložil průřez s datumy, pomocí kterého teď rovněž můžeme interaktivně filtrovat zobrazená data.
10. Změna pořadí položek
Řekněme, že máme na řádcích kontingenční tabulky produkty. Produkty bychom rádi seřadili, ale záhlaví nám nabízí v podstatě jen dvě možnosti, seřadit hodnoty podle abecedy a to buď sestupně nebo vzestupně. Samozřejmě máme možnost manuálně přetahovat pole a měnit jejich umístění. Rovněž ale můžeme použít následující trik. Můžeme kliknout do libovolného pole na řádku a začít psát jméno produktu, pro potvrzení zmáčkneme ENTER a pole se nahradí zvoleným produktem a vymění se svou pozici s předchozím produktem. To samé můžeme udělat i pro další produkty. Jeden si vybereme a začneme psát jiný produkt, potvrdíme a v tu ránu se pořadí produktů změnilo.
4 komentáře
👍👌😊
Dobrý den, Terezo,
na úvod – Vaše videa k Excelu jsou super! Obsahově i podáním – dobře danou problematiku vysvětlujete a nejste uspávací. Za mě jste v dané oblasti jasná jednička!¨
Mám dotaz – Video -10 triků s kontingenčními tabulkami – cca 2 minuta – jak udělat do řádku č.3 filtr pobočka (nad kontingenční tabulku)? Nevím, jak se to dělá. Pak samozřejmě nemohu udělat následné kroky s editací dle poboček na jednotlivé listy.
Děkuji!
Andrea
Dobrý den, jsem moc ráda, že se vám videa líbí 🙂 A děkuji za vřelý komentář 🙂 K vašemu dotazu. Když vytvoříte kontingenční tabulku, tak se v pravém ovládacím panelu kontingenční tabulky zobrazí čtyři pole. Filtry, sloupce, řádky a hodnoty. Právě do pole Filtry přetáhněte pole, které chcete, aby bylo filtrem nad tabulkou. V příkladu se jedná o pole Pobočka. Takže ta kontingenční tabulka vznikla tak, že na řádcích je pole Produkt, v hodnotách jsou Tržby a ve Filtrech je Pobočka 🙂
Skvělé video, moc mi pomohl trik s přidáním filtru do sloupce. Díky