V dnešním videu se podíváme na tři způsoby, jak můžeme v excelovém grafu názorně zobrazit odchylku hodnot od průměrné hodnoty. Pohrajeme si s formátováním grafu, tak aby bylo pro ostatní na první pohled srozumitelné, které hodnoty převyšují průměrnou hodnotu a které hodnoty jsou naopak podprůměrné. Využijeme u toho přitom kombinace několik typů grafů, stejně jako podmínky KDYŽ a formátování grafů.
Excelový soubor ke stažení
Jak v Excelu zobrazit odchylky od průměru
A začneme hned prvním příkladem.
V dnešním cvičném Excelu máme tabulku, kde máme na řádcích uvedené měsíce a v tabulce hodnoty. Hodnoty chceme zobrazit ve sloupcovém grafu, kde chceme zvýraznit nejen jednotlivé hodnoty, ale chceme v grafu vyznačit i průměrnou hodnotu. Tak aby bylo hned pouhým okem patrné, které měsíce překročily průměrnou hodnotu a které naopak ne. Začneme tím, že do excelového sešitu vložíme jednoduchý sloupcový graf. Označíme hodnoty v tabulce a na kartě Vložení vybereme sloupcový graf. Graf se vloží do excelového sešitu, trochu ho posuneme a zvětšíme ho. Než se pustíme do zobrazování průměrných hodnot, tak graf ještě trochu zformátujeme. Označíme sloupce jedním kliknutím myši, pravé tlačítko myši a vybereme Formát datové řady a trochu sloupce rozšíříme. V šířce mezery vybereme požadovanou šířku a sloupce tím rozšíříme. Ještě z grafu smažeme pomocné vodicí čáry.
Naším cílem je v tomto grafu zobrazit průměrnou hodnotu. Abychom byli schopni průměrnou hodnotu v grafu zobrazit, musíme nejprve průměrnou hodnotu zobrazit ve zdrojové tabulce. Ve zdrojové tabulce vytvoříme další sloupec, který nazveme Průměr. V tomto sloupci použijeme excelovou funkci PRŮMĚR, kde vytvoříme průměr z hodnot v tabulce. Jelikož tuto hodnotu potřebujeme na každém řádku, tak musíme rozpětí buněk plně zafixovat klávesou F4. Funkce je hotová, takže ji potvrdíme a stáhneme ji dolů. Teď máme průměr hodnot zobrazený na každém řádku tabulky.
Teď můžeme tuto průměrnou hodnotu vložit do grafu. Klikneme do grafu a vybereme Vybrat data. Zde vybereme Přidat a otevře se okno přidání datové sady. Nejprve klikneme do prvního pole, kde označíme záhlaví sloupce, tedy název sloupce. Následuje datová sada, klikneme do pole, napíšeme rovná se a označíme data ve sloupci Průměr. Tím se vloží hodnoty průměru do grafu jako další sloupec. Všechno potvrdíme. Průměr hodnot ve formátu sloupců není v této podobě moc vypovídající, takže klikneme do grafu a vybereme Změnit typ grafu.
Překlikneme se na Kombinovaný graf a u průměru vybereme spojnicový graf. Tím se sloupce změní na rovnou linii v grafu, která vyjadřuje průměr hodnot v tabulce. To je jeden ze způsobů, jak zobrazit průměrnou hodnotu v excelovém grafu. Měsíce, jejichž sloupce čáru průměru protínají, jsou vyšší než průměrná hodnota, naopak ostatní sloupce mají hodnoty nižší než průměr. Vše je plně dynamické, pokud změníme hodnoty v tabulce, tak se přizpůsobí jak sloupce, tak čára průměru.
Druhou možností, jak zobrazit odchylku hodnot od průměru je v podobě barevných sloupců. Řekněme, že chceme sloupce, jejichž hodnoty převyšují průměr označit zeleně a sloupce, jejichž hodnoty jsou podprůměrné, tak chceme zobrazit červeně. Kromě průměrné hodnoty na každém řádku budeme k tomuto příkladu potřebovat ještě další dva pomocné sloupce. V jednom sloupci označíme, zda jsou hodnoty podprůměrné a ve druhém sloupci označíme hodnoty, které jsou nadprůměrné.
Opět nejprve vložíme sloupcový graf ze základních hodnot. Označíme tabulku a na kartě Vložení vybereme jednoduchý sloupcový graf. Vloží se graf, který posuneme a trochu ho zvětšíme. Opět můžeme graf trochu formátovat, můžeme smazat pomocné vodicí čáry nebo i název grafu, pokud ho nepotřebujeme.
Vrátíme se ke zdrojové tabulce a ve sloupci Podprůměr využijeme podmínkovou funkci KDYŽ, kde ověříme, zda je hodnota nižší než průměr. Takže podmínka KDYŽ, kde podmínka je, pokud je hodnota nižší než průměrná hodnota na řádku, tak chceme vrátit hodnotu, a pokud ne, tak chceme vrátit funkci NEDEF. Funkci NEDEF použijeme proto, že vrací chybovou hlášku, která se nezobrazí v grafu, což je přesně to, co potřebujeme. Funkci ukončíme a pošleme ji dolů.
Stejnou podmínkovou funkci využijeme i ve druhém sloupci Nadprůměr. Zde použijeme funkci KDYŽ, kde podmínka bude, pokud je hodnoty vyšší než průměr, tak chceme vrátit hodnotu a pokud ne, tak chceme vrátit funkci NEDEF. Funkci ukončíme a pošleme ji dolů.
Teď klikneme pravým tlačítkem do grafu a vybereme Vybrat data. Opět vybereme Přidat, kde nejprve označíme záhlaví sloupce Podprůměr a následně datovou sadu, tedy hodnoty ve sloupci podprůměr. Potvrdíme hodnoty a ještě jednou klikneme na Přidat. V názvu označíme záhlaví Nadprůměr a jako datovou sadu označíme hodnoty ve sloupci Nadprůměr. Potvrdíme a tím se hodnoty vloží do grafu ve formátu sloupců. Označíme sloupce podprůměr a změníme u nich barevnou výplň na červenou. Potvrdíme a vybereme druhé sloupce Nadprůměr a změníme u nich barevnou výplň na zelenou. Opět potvrdíme. Teď musíme sloupce překrýt. Pravé tlačítko myši, Formát datové sady a v překrytí řady vybereme 100 % a rovnou můžeme opět rozšířit sloupce, aby graf vypadal lépe. Zmenšíme šířku mezery a graf je zformátovaný. Teď jsou sloupce, které překračují průměr zelené a podprůměrné měsíce červené. Opět je vše plně dynamické, pokud změníme některé hodnoty v tabulce, tak se jak sloupce, tak barvy přizpůsobí.
Třetí možností, jak můžete zvýraznit ve sloupcovém grafu odchylku od průměru je, že bychom obarvily zeleně jen tu část sloupce, která převyšuje přímku průměru.
V tomto případě budeme potřebovat další dva pomocné sloupce. A rovněž budeme potřebovat sloupec s průměrnou hodnotou, kterou jsme již spočítali v přechozích příkladech. První sloupec můžeme rovněž nazvat Nadprůměr. V tomto sloupci potřebujeme ověřit, zda jsou hodnoty vyšší než průměrná hodnota, pokud ano, tak od sebe tentokrát hodnoty odečteme. Takže podmínka KDYŽ, kde ověříme, že pokud je hodnota vyšší než průměrná hodnota na řádku, tak že chceme od hodnoty odečíst hodnotu průměru, v opačném případě chceme vrátit funkci NEDEF. Funkci ukončíme, potvrdíme a pošleme dolů pro ostatní řádky. Tam kde je hodnota vyšší tak se vrátil rozdíl skutečné hodnoty od průměrné, tedy o kolik reálná hodnota převyšuje hodnotu průměrnou. Tam kde je hodnota podprůměrná se vrátila chyba díky funkci NEDEF.
Ve druhém pomocném sloupci, který nazveme Hodnoty rozdíl od sebe odečteme skutečnou hodnotu a hodnotu Nadprůměr. Vzorec pošleme dolů, akorát teď vidíme, že tam, kde máme chybovou hlášku se rovněž vrátila chyba. Celý vzorec proto zabalíme do funkce IFERROR, kde prvním argumentem je vzorec a využijeme i druhý argument, co se má vrátit v případě chyby. V takovém případě chceme vrátit reálnou hodnotu. Ukončíme závorku a funkci pošleme dolů. Teď máme zdrojové sloupce připravené.
Teď můžeme do sešitu vložit sloupcový graf. Označíme sloupec s měsíci, držíme klávesu CTRL a označíme tři pomocné sloupce, sloupec s reálnými hodnotami neoznačujeme. Na kartě Vložení vybereme sloupcové grafy a vybereme skládaný graf. Graf se vloží do sešitu, zvětšíme ho a trochu ho posuneme. Graf teď nevypadá tak, jak potřebujeme, jelikož ho musíme trochu zformátovat. Klikneme na graf pravým tlačítkem myši a vybereme Změnit typ grafu. V kombinovaném grafu u datové sady průměr vybereme spojnicový graf. Tím se průměrná hodnota opět změní v přímku. Další dvě datové sady necháme jako skládaný sloupcový graf.
To, proč graf nevypadá tak, jak potřebujeme je proto, že máme datové sady přehozené. Potvrdíme změnu grafu a klikneme do grafu ještě jednou pravým tlačítkem myši a vybereme Vybrat data. Musíme přehodit pořadí sloupců Nad průměrem a Hodnoty rozdíl. Klikneme na Hodnoty rozdíl a pomocí šipky nahoru posuneme datovou sadu nad nadprůměr. Tím se datové sady přehodí a graf je již správně.
Ještě zbývá změnit barvy v grafu. Můžeme nechat sloupce do průměru šedé a označit jen ty kousky, které převyšují průměr. Klikneme na tu část a změníme u ní barevnou výplň na zeleno. Teď máme v grafu označené ty části sloupců, které převyšují průměr. A opět je vše plně dynamické, pokud změníme jakékoliv hodnoty v tabulce, tak se graf přizpůsobí.
Jedna odpověď
😊👍