Podmíněné formátování je užitečná možnost, jak v Excelu můžeme vizualizovat hodnoty. Pokud se chcete podívat, jak vypadá solidní report, který podmíněné formátování využívá, tak se můžete před tím, než budete pokračovat ve čtení, podívat na článek – Reprezentativní reporting v Excelu i jinde – Rozmazlujte svého manažera nebo se podívejte na obrázek níže do tabulky.

V tomto článku se na funkci formátování podívám blíže, podíváme se na základy a pokusím se vysvětlit, proč je dobré to umět.

Představte si jakoukoliv časovou řadu (např vývoj úrokových sazeb hypoték po jednotlivých měsících jako v příkladu viz dále). Když se do takové tabulky podíváte, tak vidíte spoustu čísel, ale na první pohled nevidíte souvislosti – jak úrokové sazby rostou nebo klesají v čase.

Pokud tyto souvislosti chcete zjistit, tak máte 2 možnosti. Buď můžete do takové tabulky dlouze koukat a snažit se informace získat tzv. okem – to není moc spolehlivé 🙂 nebo můžete použít nějakou vizualizaci – např graf nebo právě podmíněné formátování – to vám data v tabulce např. barevně označí podle toho jestli sazby rostou nebo klesají.

Graf je často lepší volba jak graficky reprezentovat data, ale někdy ho z nějakého důvodu použít nemůžeme, nechceme nebo bychom chtěli podmíněné formátování a grafy použít současně.

Ve firmách se podmíněné formátování spolu s dalšími vizualizacemi (grafy, formátování tabulek) používá velmi často při reportingu jako nástroj, jak zjednodušit manažerovi orientaci v reportu a číslech. Je opravdu velký rozdíl jestli se koukáme do tabulky, která není nijak graficky upravena oproti tabulce, která takto upravena je.

S pomocí podmíněného formátování si můžete vymyslet jakékoliv kritérium a podle tohoto kritérie data označit třeba

• Hodnoty větší/menší než než 0
• Hodnoty v nějakém rozmezí
• Můžeme použít ikony nebo datové pruhy
• Podmíněné formátování samozřejmě funguje nejen na čísla ale i na text
• Mnoho dalšího

Ukázka Podmíněného Formátování na Příkladu

Na začátku článku jsem zmínil, že ukázku podmíněného formátování provedeme na nějakých zajímavých číslech. Vybral jsem vývoj úrokových sazeb na hypotéky od 1.1.2017 do 31.3.2019. Zdrojem časových řad je databáze České národní banky ARAD.

Data po exportu do Excelu (viz obrázek níže) obsahují:

• Období
• Úrokové sazby stávajících úvěrů (úvěry poskytnuté dříve a nové úvěry)
• Úrokové sazby nových úvěrů (pouze nově poskytnuté úvěry za daný měsíc)
• Rozdíl mezi úrokovými sazbami (zelený sloupec) jsem dopočítal já jako rozdíl mezi sazbami nových úvěrů a sazbami všech úvěrů.

Toto je celkem zajímavá statistika třeba pro ty, kteří zrovna uvažují nad pořízením bydlení nebo je čeká refixace úvěru. Cílem je, data pomocí podmíněného formátování vizualizovat. Dejme tomu, že:

• První dva sloupce chceme vizualizovat pomocí datových pruhů
• Třetí sloupec s rozdílem chceme vizualizovat barvou pozadí – růst úrokových sazeb nových úvěrů oproti stávajících chceme mít červeně (to pro nás není dobrá zpráva) a pokles zase zeleně (pokud jsou nové úvěry levnější, tak je to naopak dobrá zpráva)

Krok 1 – Kde najdeme podmíněné formátování v Excelu

Kartu podmíněného formátování najdete v záložce Domů (Home) – Podmíněné formátování (Conditional formating).

Krok 2 – Popis možností podmíněného formátování

Podíváme se co se pod touto kartou skrývá za možnosti (viz obrázek níže). Mám excel v angličtině ale to nevadí, vysvětlím. Tučně označím formátování, které použijeme v našem příkladu. Bylo by dobré aby jste si všechny možnosti proklikali a nejlépe i vyzkoušeli na nějakých číslech.

• Highlight cells Rules (Zvýraznit pravidla buněk) – pomocí této možnosti můžeme označit buňky, které splňují určité kritérium. Například jsou větší než 0. Můžeme například udělat to, že pro všechny buňky, které jsou větší než 0 nastavíme červené a tučné písmo.
• Top/Bottom Rules (Pravidla pro nejvyšší/nejnižší) – Tato volba funguje podobně jako předchozí varianta s tím rozdílem, že nám Excel nabízí možnost označit určitý předem stanovený počet. Například Nejvyšších/Nejnižších 10 hodnot nebo třeba hodnoty, které jsou nadprůměrné/podprůměrné
• Data Bars (Datové pruhy) – Tady už se dostáváme k pěkným vizualizacím, konkrétně tuto vizualizaci použijeme my. Pokud si označíte nějakou oblast buněk a nastavíte tento způsob podmíněného formátování, tak excel do každé buňky doplní datový pruh jehož délka bude odpovídat hodnotě v buňce. Například hodnota 1 bude mít kratší pruh než hodnota 2.
• Color Scales (Barevná stupnice) – tato vizualizace funguje na podobném principu jako datové pruhy s tím, že do buňky nedoplňuje datový pruh, ale mění pozadí buňky podle toho jakou obsahuje hodnotu. Například hodnota -1 bude červená, 0 bude oranžová, 1 světle žlutá, 2 sveětle zelená a 3 tmavě zelená. Pořadí barev se dá i přehodit pokud chceme aby záporné hodnoty byly zeleně a kladné červeně.
• Icon Sets (Sady ikon) – Možnost, kdy můžeme hodnotám přiřadit nějakou ikonu – křížek, vykřičník, fajfku, semafory, šipky.

Krok 3 – Přidání datových pruhů

Označíme oblast v tabulce oblast (buňky B6:B32), do které chceme přidat datové pruhy a následně přes Kartu Domů – podmíněné formátování – datové pruhy naformátujeme oblast (viz obrázek). Následně stejným způsobem postupujeme u druhého sloupce.

Krok 4 – Přidání barevné stupnice pro sloupec, kde máme rozdíly mezi sazbami

Postupujeme opět stejným způsobem pro oblast D6:D32 s tím rozdílem, že místo datových pruhů zvolíme stupnici. Chceme, aby záporné hodnoty byly zeleně a kladné hodnoty červeně. Střední hodnoty budou mít nějaký odstín mezi zelenou a červenou.

A je hotovo. Takto jednoduché to je. Na první pohled nyní vidíme, že úrokové sazby nových úvěrů rostou čím dál rychleji oproti starým úvěrům (vč refixovaných).