V dnešním videu ze série Jak na excelové grafy si ukážeme, jak využít trochu podmíněného formátování v excelových grafech, a to v podobě znázornění trojúhelníků nad sloupcovým grafem, které znázorňují procentuální změnu za určité období. Vložení trendu do excelového grafu si ukážeme dvěma způsoby – pomocí podmínkové funkce KDYŽ a pomocí vlastního formátu.
Excelový soubor ke stažení
Jak vytvořit sloupcový graf s trendem
Pro dnešní ukázku nám poslouží jednoduchá zdrojová tabulka dat s výnosy za dva roky za jednotlivé měsíce. Tato prodejní data bychom rádi zobrazili ve sloupcovém grafu a provedli jejich srovnání, ale nechceme použít skupinový sloupcový graf. Vytvoříme graf, který bude zobrazovat pouze hodnoty pro každý měsíc za rok 2021 a místo základních popisků dat vložíme do grafu procentuální změnu oproti předchozímu období. A aby byla meziroční změna patrná okamžitě, vložíme do grafu ještě trojúhelníky, které vyjadřují růst nebo pokles.
V prvním kroku si připravíme vše, co budeme k tvorbě grafu potřebovat. Chceme zobrazovat meziroční změnu, takže si v tabulce meziroční změnu nejdříve dopočítáme. Vytvoříme pomocný sloupec, který nazveme například Rozdíl a v něm spočítáme procentuální meziroční změnu.
V dalším kroku vytvoříme z našich zdrojových dat jednoduchý sloupcový graf. Následně do grafu vložíme popisky dat. Klikneme na graf a zmáčkneme pravé tlačítko myši a vybereme Přidat popisky dat. Jako popisky dat se vložily automaticky popisky ze sloupce Výnosy 2021. My ale máme v našem původním grafu procenta a u nich symboly, které označují, zda se jedná o meziroční pokles nebo růst.
Základní popisky dat tak musíme nahradit procentuálním vyjádřením změny, kterou jsme dopočítali ve sloupci Rozdíl. Toho docílíme tak, že klikneme pravým tlačítkem myši na popisky a vybereme možnost Formát popisků dat. Zde vybereme záložku Možnosti popisků. Abychom k popiskům prodejních dat dostali popisky z jiného sloupce, tak musíme vybrat možnost Hodnota z buněk. Vybereme tedy tuto možnost a označíme procentuální hodnoty, které chceme zobrazit u popisků. Jelikož nechceme, aby se nám v popiskách zároveň zobrazovaly i původní hodnoty, tak ještě odškrtneme možnost Hodnota. Takto jednoduše nahradíte původní popisky, popisky novými.
Zbývá nám vložit do grafu trojúhelníky jako symboly poklesu nebo růstu. Nejprve musíme tyto trojúhelníky vložit do excelového sešitu. Symboly trojúhelníků najdeme na kartě Vložení, kde najdeme možnost symboly. Zde najdeme symboly trojúhelníků. Klikneme na vybraný trojúhelník a potvrdíme vložení. To samé uděláme i pro druhý trojúhelník. Symboly se vložily do jedné buňky.
Vložení trendu do grafu pomocí funkce KDYŽ
Vrátíme se ke zdrojové tabulce a přidáme do ní další pomocný sloupec, který nazveme například Rozdíl %. Začneme tedy funkcí KDYŽ a napíšeme, funkce KDYŽ, otevřeme závorku a napíšeme naši podmínku, když je tato buňka větší než 0, tak chceme vrátit trojúhelník směřující vzhůru. Nesmíme zapomenout na uvozovky, jelikož z pohledu funkce je symbol textová hodnota, a pokud podmínka splněná nebude, tedy hodnota bude nižší než 0%, tak chceme vrátit trojúhelník směřující dolů, opět v uvozovkách. Funkci stáhneme pro všechny řádky dolů.
Trojúhelníky se vložily, ale ještě bychom k nim potřebovali vložit i procenta. Pokud bychom teď k trojúhelníkům vložily procenta pomocí ampersandu, tak se změní formát procent na formát desetinného čísla. I kdybyste se snažili změnit formát čísla opět na procenta, tak se vám to kvůli tomuto trojúhelníku nepovede.
Způsob, jak dostat k trojúhelníkům procenta, včetně formátu procent, je tak následující. Vrátíme se k původní podmínce KDYŽ, ukončíme ji závorkou a napíšeme ampersand pro spojení a funkci HODNOTA.NA.TEXT, kde označíme procenta, napíšeme středník a definujeme formát, který chceme u procent mít. Tedy 0,00%. Nesmíme zapomenout uvést formát do uvozovek. Ukončíme závorku a potvrdíme. Funkce HODNOTA.NA.TEXT udělala přesně to, co má v názvu, převedla procentuální vyjádření na text a zachovala formát, který jsme definovali v druhé části funkce.
Základ popisků máme hotový. Teď už zbývá se jen vrátit k popiskům dat, vybrat znovu formát popisků dat a místo původního sloupce označit nový pomocný sloupec. Takto jednoduše se vám procenta včetně symbolů dostanou do grafu.
Vložení trendu do grafu pomocí vlastního formátu
Druhou možností, kterou máte je, že použijete vlastní formát. Rozdíl je v tom, že u vlastního formátu nepotřebujete pomocný sloupec. Nejprve si zkopírujeme vložené symboly. Označíme si hodnoty meziroční změny a klikneme pravým tlačítkem myši a vybereme formát buněk a zde vlastní formát. Nejprve vložíme zkopírované symboly a upravíme formát. I zde musí být trojúhelníky v uvozovkách. Začneme kladnými hodnotami. Pokud bude meziroční změna vyšší než 0%, tedy kladná hodnota, tak chceme zobrazit trojúhelník směřující vzhůru, trojúhelník v uvozovkách a formát pro procenta 0,00%. Středník a pokud budou hodnoty nižší než 0%, ve vlastním formátu tedy pozice pro záporné hodnoty, tak chceme zobrazit trojúhelník směřující dolů, opět v uvozovkách a formát pro procenta 0,00%. Hotovo.
Potvrdíme a do první buňky se k procentům vložil požadovaný symbol. Teď stačí zkopírovat formát a aplikovat ho na ostatní buňky ve sloupci. Teď už stačí opět jen vložit tyto hodnoty do popisků dat. Označíme zdrojová data, vložíme sloupcový graf, označíme graf, klikneme pravým tlačítkem myši, vybereme přidat popisky dat a zde vybereme možnost Hodnota z buněk. Označíme sloupec s procenty a trojúhelníky a potvrdíme.
Výsledek je stejný jako když použijeme funkci KDYŽ. Teď už stačí graf zformátovat dle potřeby a úkol je hotový. Vytvořili jsme sloupcový graf, ve kterém se vám nad sloupci zobrazují nejen meziroční změny hodnot, ale i grafické vyjádření toho, zda se jedná o pokles nebo růst.
3 komentáře
Super! Díky moc
Pusto
Rád bych vyzkoušel: Sloupcový graf s trendem, bohužel soubor, který je ke stažení je zaheslovaný.
Dobrý den, otevřený Excel jsem vám poslala na email, asi byla chyba na webu, jelikož Excel by měl být volně přístupný bez hesla.