Podmíněné formátování v excelových grafech vám dovoluje dynamicky formátovat grafy. V dnešním díle si ukážeme, jak můžete využít podmíněné formátování v excelových grafech. Díky podmíněnému formátování ušetříte spoustu času zbytečným a opakujícím se formátováním grafů. Jako bonus zvýšíte i přehlednost grafu a upoutáte pozornost na důležitá data. Podmíněné formátování v excelových grafech si ukážeme na několika následujících praktických příkladech.
Excelový soubor ke stažení
Podmíněné formátování v excelových grafech
V prvním příkladu máme velmi jednoduchou tabulku s několika hodnotami. Z této tabulky bychom rádi udělali sloupcový graf a dále bychom chtěli, aby se v grafu záporné hodnoty zobrazily jako červený sloupec a kladné hodnoty jako zelený sloupec. Začneme tedy tím, že označíme tabulku a vložíme sloupcový graf. Excel vloží základní sloupcový graf, kdy jsou všechny sloupce obarvené stejnou barvou.
Pokud bychom nevyužili podmíněné formátování, tak bychom samozřejmě mohli na jednotlivé sloupce kliknout a manuálně u nich změnit barevnou výplň. Nevýhodu tohoto přístupu je, že pokud by se u produktů změnily hodnoty například z kladných na záporné, tak by sloupce zůstaly obarvené špatnou barvou. Pro vyřešení úkolu tedy použijeme podmíněné formátování.
Abychom dostali efekt podmíněného formátování do grafu, tak do tabulky musíme přidat pomocné sloupce. Jeden sloupec pro kladné hodnoty (C2:C6) a jeden sloupec pro záporné hodnoty (D2:D6). Do sloupce pro kladné hodnoty napíšeme podmínku KDYŽ/IF, kde podmínka bude, pokud je buňka B3 větší než nula, tak chceme vrátit tu samou hodnotu (B3), a pokud hodnota nebude větší než nula, tak chceme vrátit nulu. Potvrdíme a stáhneme pro ostatní buňky dolů. To samé jen obráceně uděláme i ve druhém sloupci pro záporné hodnoty. Do buňky D3 napíšeme funkci KDYŽ a podmínka je, pokud je buňka B3 menší než nula, tak chceme vrátit hodnotu z buňky B3 a pokud ne, tak chceme vrátit nulu. Potvrdíme a stáhneme pro ostatní buňky dolů. Základ pro graf máme hotový.
Vložíme základní sloupcový graf z původních hodnot a následně do grafu klikneme pravým tlačítkem myši a vybereme možnost Vybrat data. Zde vybereme možnost Přidat. V poli název řady označíme název záhlaví sloupce a v poli hodnoty řady označíme sloupec s daty. Potvrdíme a uděláme to samé i pro sloupec se zápornými hodnotami. Přidáme datovou sadu, nejprve označíme název sloupce a následně označíme sloupec s daty. Potvrdíme.
Do grafu se vložily dodatečné sloupce. Klikneme na sloupce, které představují kladné hodnoty a změníme u nich barevnou výplň na zeleno. To samé zopakujeme i pro sloupce, které reprezentují záporné hodnoty a změníme u nich barevnou výplň na červeno. Teď už zbývá jen označit sloupce a kliknout na ně pravým tlačítkem myši. Vybereme formát Datové řady a v Možnostech řady vybereme Překrytí řad a nastavíme 100 %. Sloupce se překryly a původní sloupce s modrou barvou jsou překryté zelenými a červenými sloupci. Zkusíme ve zdrojové tabulce změnit nějaké hodnoty. A sloupce automaticky reagují na změny.
První úkol je hotový.
Podmíněné formátování v excelových grafech – limitní hodnoty
V druhém příkladu máme trochu jiný úkol. Máme ze zdrojové tabulky (A30:B34) vytvořit sloupcový graf. Zároveň potřebujeme, aby se hodnoty v grafu, které převýší limit uvedený v buňce E29 obarvily na zeleno. Využijeme k tomu podobný postup jako u předchozího příkladu. Nejprve vložíme z tabulky dat (A30:B34) jednoduchý sloupcový graf. Následně vytvoříme v tabulce pomocný sloupec (C30:C34), ve kterém bude uvedená funkce KDYŽ/IF a podmínka bude, pokud je buňka B31 vyšší než limit uvedený v buňce $E$29, tak chceme vrátit hodnotu ze sloupce Splněno (B31), v opačném případě chceme vrátit nulu. Potvrdíme a stáhneme pro ostatní buňky dolů. Klikneme pravým tlačítkem myši do grafu a vybereme možnost Vybrat data. Zde přidáme novou datovou sadu, kde opět označíme nejprve název sloupce a následně označíme hodnoty. Potvrdíme a do grafu se přidala další sada dat. Teď stačí vybrané sloupce označit, změnit u nich barevnou výplň a rovnou kliknout do grafu a vybrat Formát datové řady a vybrat 100 % překrytí řad.
Podmíněné formátování v excelových grafech – tři hodnoty
V dalším příkladu máme rovnou tři limity. U hodnot, které jsou pod hodnotou 30 % chceme sloupec v grafu obarvit na červeno, hodnoty, které přesahují 75 % chceme obarvit na zeleno a ostatní sloupce chceme oranžové. Máme tři limity, takže vytvoříme tři pomocné sloupce. Začneme sloupcem pro hodnoty menší než 30 % (C55:C62). Opět využijeme funkci KDYŽ/IF, a podmínka je, pokud je hodnota B56 nižší nebo rovna hodnotě E51/C55, tak chceme vrátit hodnotu ze sloupce Splněno (B56). A pokud ne, tak chceme vrátit nulu. Potvrdíme a stáhneme pro ostatní buňky dolů.
Ve druhém sloupci musíme použít funkci KDYŽ/IF v kombinaci s funkcí A/AND. Začneme s funkcí KDYŽ a do podmínky vložíme funkci A, otevřeme závorku a parametry funkce A jsou, pokud je buňka B56 vyšší než první limit E51, středník, a pokud je buňka B56 zároveň menší než horní limit E53. Konec závorky pro funkci A, středník, pokud je podmínka splněná, tak chceme vrátit hodnotu B56 a pokud ne, tak nulu. Zbývá poslední interval. Funkce KDYŽ/IF, pokud je buňka B56 vyšší nebo rovna hornímu limitu E53, tak chceme vrátit hodnotu B56 a pokud ne, tak nulu.
Příprava pro graf je hotová.
Tentokrát vložíme všechny sloupce najednou. Označíme celou tabulku i s pomocnými sloupci A55:E62 a vložíme sloupcový graf. Označíme hodnoty, které reprezentují hodnoty pod 30 % a změníme jejich barevnou výplň na červeno. Teď označíme sloupce pro hodnoty vyšší než 75 % a obarvíme je na zeleno. Všechny ostatní sloupce musejí být oranžové. Klikneme do grafu pravým tlačítkem a vybereme formát datové řady. Překryjeme datové řady a potvrdíme.
Hotovo.
Jedna odpověď
👍😊