Jak zobrazit dynamicky MIN a MAX hodnotu v grafu

V dnešním videu si ukážeme, jak použít podmíněné formátování v excelovém grafu tak, abyste byli v grafu schopní dynamicky zobrazit minimální a maximální hodnoty. Jedná se zobrazení minimální (MIN) a maximální (MAX) hodnoty v grafu tím, že hodnoty barevně odlišíme. Vše je navíc plně dynamické, takže vám podmíněné formátování v grafu bude reagovat na jakoukoliv změnu ve zdrojových datech.

Excelový soubor ke stažení

Ve zdrojové tabulce máme údaje pro několik společností a jejich tržby. Z tabulky si vytvoříme jednoduchý sloupcový graf. Označíme tabulku, můžeme využít klávesové zkratky CTRL+A pro označení tabulky, a na kartě Vložení vybereme sloupcový graf. Pro vložení grafu můžete rovněž využít klávesovou zkratku ALT+F1. Pokud nemáte jako základní graf nastavený sloupcový graf, tak se ve videu 20 triků s Excelovými grafy podívejte na to, jak můžete změnit nastavení základních grafů v Excelu.

MIN a MAX v grafu
Obrázek č.1 Vložení sloupcového grafu

Pokud chceme v grafu zobrazit minimální a maximální hodnoty odlišnou barvou, tak si musíme pomoci pomocnými sloupci ve zdrojové tabulce.

Nalezení minimální hodnoty

Začneme s minimální hodnotou. Vedle tabulky vytvoříme pomocný sloupec Min a do buňky D4 napíšeme funkci MIN. Ve funkci MIN označíme celý sloupec hodnot (C4:C14), který zafixujeme pro řádky i sloupce ($C$4:$C$14) a zeptáme se, zda se tyto hodnoty rovnají první buňce v seznamu hodnot (C4). Funkci potvrdíme a na řádcích, kde máme nejnižší hodnotu se vrátilo slovo PRAVDA, na ostatních řádcích je slovo NEPRAVDA. Funkci MIN teď stačí zabalit do podmínkové funkce KDYŽ. Podmínkou ve funkci KDYŽ bude funkce MIN. Parametr ano ve funkci KDYŽ bude, hodnota příslušných tržeb. To znamená, že pokud funkce MIN vrátí pravdu, tak chceme, aby se na řádku objevila hodnota tržeb, v ostatních případech chceme vrátit prázdnou buňku, což se v Excelu vyjádří dvěma uvozovkami.

Podmínka je hotová a můžeme ji stáhnout pro všechny buňky dolů.

MIN a MAX v grafu
Obrázek č.2 Nalezení minimální hodnoty

Nalezení maximální hodnoty

Naprosto stejně budeme postupovat v druhém pomocném sloupci, kde budeme hledat maximální hodnoty. Akorát funkci MIN nahradíme funkcí MAX. Opět začneme tím, že ve funkci MAX porovnáme, zda se hodnoty ze sloupce $C$4:$C$14 rovnají první hodnotě v seznamu hodnot (C4). Tuto funkci MAX zabalíme do funkce KDYŽ, kde podmínkou bude celá funkce MAX. Pokud podmínka splněná bude, tak chceme vrátit hodnotu z daného řádku, pokud podmínka splněná nebude tak chceme vrátit prázdný řádek. 

MIN a MAX v grafu
Obrázek č.3 Nalezení maximální hodnoty

Když máme pomocné sloupce připravené, můžeme tyto pomocné sloupce vložit do grafu.

Zobrazení MIN a MAX hodnoty v grafu

Proto, aby se nejnižší a nejvyšší hodnoty v grafu obarvily, musíme vložit pomocné sloupce do grafu jako další série hodnot. Klikneme do sloupcového grafu pravým tlačítkem myši a vybereme možnost Vybrat data. Pod položkou legendy vybereme Přidat. Nejprve přidáme sérii dat pro nejnižší hodnoty. Jako název řady vybereme název sloupce. A jako Hodnoty řady označíme celý sloupec MIN. Potvrdíme.

To samé zopakujeme pro maximální hodnoty. Opět vybereme možnost Přidat a tentokrát přidáme sérii hodnot pro nejvyšší hodnoty. Opět označíme nejprve nadpis sloupce a následně celý sloupec hodnot MAX. Potvrdíme.

MIN a MAX v grafu
Obrázek č.4 Vložení série MIN a MAX do grafu

Do grafu se vložily nové sloupce. Nejprve u nich změníme formát. Označíme sloupce pro nejnižší hodnotu a vybereme u nich červenou barevnou výplň. Stejný úkon zopakujeme pro nejvyšší hodnoty, vybereme u nich třeba zelenou barevnou výplň.

Následně klikněte pravým tlačítkem myši na sloupce v grafu a vyberte Formát datové řady. Pod záložkou Možnosti řady najděte Překrytí řady a nastavte zde 100 %. Sloupce se překryly.

MIN a MAX v grafu
Obrázek č.5 Překrytí řad

Datová série MAX a MIN překryly původní sloupce a proto máte teď tyto sloupce označené jinou barvou. Vše je samozřejmě plně dynamické, takže pokud změníte hodnoty ve zdrojové tabulce, tak bude graf včetně barevné výplně dynamicky reagovat na změny.

MIN a MAX v grafu
Obrázek č.6 Podmíněné formátování v grafu - MIN a MAX v grafu

MOHLO BY VÁS ZAJÍMAT

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *