V dnešním videu se po dlouhé době vrátíme k excelovým dashboardům neboli přehledům a ukážeme si, jak vytvořit tento přehled tržeb. Na přehledu máme možnost filtrovat dle externích filtrů, na základě kterých se mění zobrazení celého přehledu. V dnešním videu si ukážeme spoustu triků, jak můžete oživit vaše grafy nebo udělat přehled zajímavější. Ukážeme si třeba, jak vytvořit tuto mapu s podmíněným formátováním nebo jak v bublinovém grafu zvýraznit bublinu s nejvyšší hodnotou nebo jak v grafech vytvořit tyto přechodové výplně. A i když možná neplánujete podobný přehled tvořit, tak techniky a triky v tomto videu určitě využijete i ve své každodenní praxi nebo prezentaci dat.
Excelový soubor ke stažení:
Zdrojová data
Ve cvičném excelovém souboru, který je ke stažení na našem webu, a jehož odkaz najdete v popisku tohoto videa, máme zdrojovou tabulku. Ve zdrojové tabulce jsou údaje za tři roky o prodejích několika produktů. Kromě produktu máme v tabulce i rozdělení, zda produkt spadá do dámské nebo pánské módy, na jaké pobočce se prodej uskutečnil a zda se uskutečnil přímo na pobočce nebo přes online portál. Kromě toho máme v tabulce údaje o počtu prodaných kusů produktů a tržbách.
Než se pustíme do tvorby kontingenčních tabulek, které budou zdrojem pro přehled, tak tuto zdrojovou tabulku změníme na excelovou tabulku. Klikneme do tabulky, klávesová zkratka CTRL+T nebo na kartě Vložení vybereme Tabulka. Potvrdíme, že tabulka má záhlaví a z obyčejného rozsahu dat se stala excelová tabulka. Tabulku rovnou pojmenujeme, třeba jako Zdroj. Pokud nechcete v tabulce toto typické proužkování, tak klikněte do tabulky, a na kartě Návrh tabulky vyberte ve stylech žádný styl. Tím se toto typické proužkování odeberete a zůstane původní styl tabulky.
Klikneme do tabulky a na kartě Vložení vybereme Kontingenční tabulka a z tabulky nebo oblasti. Kontingenční tabulku vložíme na nový list. List s kontingenčními tabulkami pojmenujeme jako Tabulky.
Příprava přehledu
Připravíme si list Přehled. Jediné co musíme udělat je změnit barvu buněk na černou barvu. Pro klasické rozměry obrazovky bude stačit obarvit buňky do AD a zhruba do řádku 42.
Kontingenční tabulka
Na přehledu začneme první kontingenční tabulkou, což je tabulka zobrazující tržby po produktech a pobočkách, včetně procentuálního vyjádření tržeb z celkové tržby. Na řádky této tabulky vložíme nejprve pobočky, pod pobočky vložíme produkty a do hodnot vložíme tržby. A jelikož chceme v tabulce vidět i procentuální vyjádření tržeb, tak vložíme pole tržby do tabulky ještě jednou.
U tohoto druhého sloupce s tržbami rovnou změníme výpočet. Klikneme na pole a vybereme Nastavení polí hodnot. Vybereme Zobrazit hodnoty jako a jako výpočet vybereme % z celkového součtu. Tržby se změnily na vyjádření v procentech z celkové tržby. Rovnou změníme i formát u tržeb v prvním sloupci. Klikneme do sloupce a vybereme Formát čísla. Vybereme měnu a odebereme desetinná čísla.
Teď můžeme kontingenční tabulku vyjmout a vložit ji na přehled. Aby byla tabulka dobře viditelná, tak roztáhneme sloupce. A jelikož má kontingenční tabulka ve zvyku měnit šířku sloupců při aktualizaci, tak rovnou tuto vlastnost vypneme. Klikneme pravým tlačítkem do kontingenční tabulky a vybereme Možnosti kontingenční tabulky. Zde odškrtneme Při aktualizaci automaticky upravit šířky sloupců.
Teď je to jen o formátování kontingenční tabulky. Vytvoříme si vlastní styl kontingenční tabulky. Potřebujeme vytvořit tabulku s černým pozadím, bílým písmem a zvýrazněnými řádky s pobočkami. Vybereme si styl, který nejvíce připomíná to, co se snažíme vytvořit a duplikujeme ho. Pojmenujeme styl jako MůjStyl.
Průřezy
Abychom rovnou viděli, zda vše na přehledu funguje, tak vložíme rovnou průřezy neboli externí filtry. Klikneme do KT a vybereme vložit průřez. Vybereme pole, která chceme do průřezu vložit. Průřezy musíme rovněž naformátovat, takže opět vytvoříme vlastní styl průřezu. Nejprve vložíme všechna pole v průřezu vedle sebe. Vypneme záhlaví průřezu.
Bublinový graf
Prvním grafem na přehledu je bublinový graf. Máme na výběr, jak zpracujeme data do bublinového grafu. Buď můžeme jako základ použít kontingenční tabulku, nicméně z kontingenční tabulky nelze bublinový graf vytvořit, takže se následně na data budeme muset odkázat mimo tabulku a nebo si zdrojová data připravíme pomocí funkcí. Nejprve vytvoříme kontingenční tabulku.
Na řádky vložíme produkty a do pole hodnoty vložíme počet prodaných kusů. Počet prodaných kusů bude totiž určovat velikost bubliny. Když byste se teď pokoušeli z této tabulky vytvořit bublinový graf, tak se vám to nepovede, jelikož tento graf kontingenční tabulky nepodporují.
Můžeme se ale v pomocné tabulce na hodnoty z kontingenční tabulky odkázat.
Navíc do tabulky přidáme dva sloupce, sloupec X a Y. Bublinový nebo bodový graf má totiž na ose x i y hodnoty. Jelikož nechci, aby mi bubliny měnily pozici v rámci grafu, nebo aby se překrývaly, ale chci aby měnily jen barvu a velikost, tak si určím libovolné hodnoty na ose x a y. Vedle do sloupce počet propojím ke správnému produktu počet prodaných kusů.
Teď mám tabulku, ze které mohu vytvořit bublinový graf.
Bublinový graf je hotový.
Sloupcový graf
Pro vytvoření sloupcového grafu musíme vytvořit kontingenční tabulku, kdy na řádky vložíme produkty a typy produktů. Do pole hodnoty vložíme tržby.
Z kontingenční tabulky vytvoříme klasický sloupcový graf.
Plošný a spojnicový graf
Dalším grafem na přehledu je plošný graf spojený se spojnicovým grafem. Nejprve musíme vytvořit kontingenční tabulku, kde na řádcích budou měsíce a v poli hodnoty tržby. Jelikož potřebujeme zkombinovat dva grafy, potřebujeme dvě pole tržby v hodnotách.
Klikneme do kontingenční tabulky a vytvoříme spojnicový graf. Následně klikneme do grafu a vybereme Změnit typ grafu. U první datové sady vybereme spojnicový graf a u druhé datové řady vybereme plošný graf. To nám dovolí naformátovat oba dva grafy zvlášť.
Mapa
Efekt mapy vytvoříme v Power Pointu.
V mapě musíme vytvořit efekt podmíněného formátování. Nicméně v Excelu nelze propojit tvary s podmíněným formátováním. Musíme to obejít trikem. Trik spočívá v propojení hodnot mimo kontingenční tabulku, na které uplatníme podmíněné formátování. Následně hodnoty skryjeme pomocí vlastního formátu. Tyto buňky zkopírujeme a vložíme je jako propojený obrázek. Tím docílíme efektu podmíněného formátu ve tvarech.
Následně do tvarů vložíme textová pole s názvy poboček a propojíme je s tržbami z kontingenční tabulky.
2 komentáře
Ve videu je zmíněno (čas 37:40), že na webu lze stáhnout obrázek mapy ČR pro další práci s grafem. Ovšem mapu ke stažení nikde nevidím, takže dál je nutno pokračovat s náhradním obrázkem odněkud z netu.
https://www.akademieexcelu.cz/2025/10/12/moderni-excelovy-prehled-excelove-prehledy/
Dobrý deň Terezka,
ďakujem pekne za inšpiráciu na dashboard, výborný nápad. Ja som si vytvoril svoj vlastný dashboard s KT a grafmi s týmto farebným prevedením. Mám dve otázky:
1) Pri kombi grafoch – spojnicový (u nás čiarový) + plošný by som chcel dostať na X-ovú os názvy mesiacov napr. prvé 3 písmená (jan, feb, mar..) namiesto čísiel a chcem sa spýtať či sa to dá zmeniť už v KT, alebo až v grafe?
2) Doplnil som si tam namiesto mapy – Predajné metriky firmy – ale keď chcem ochrániť bunky so vzorcami heslom proti prepísaniu a nastavím bunku na zamknutú + zabezpečím hárok heslom, tak sa mi vypnú slicery/filtre, grafy, KT skrátka celý dashboard. Ako sa dá vyriešiť, aby sa nedali zmeniť pri nešikovnej manipulácii vzorce a zároveň interaktivita prehľadu nebola vypnutá.
Ďakujem za odpoveď
Patrik