Podmíněné formátování

Podmíněným formátováním lze zvýraznit buňky, které splňují zadané podmínky. Ke zvýraznění můžeme použít všechny formáty buněk (styl a barva písma, barva pozadí a orámování).

Podmíněné formátování je vhodným doplňkem statistik a vyhodnocovacích tabulek. Podmíněný formát může být na buňku aplikován v závislosti na hodnotě téže buňky nebo v závislosti na hodnot jiné buňky.

Podmíněné formátování definované hodnotou v buňce

Implicitní možnost je navázat formát buňky na její hodnotu, tzn., že se testuje obsah buňky a je-li splněna zadaná podmínka, projeví se příslušné formátování. Pokud obsah buňky nevyhovuje zadané podmínce, formátování se neprojeví. Pro jednu buňku lze nastavit až tři podmínky, pokud by došlo k situaci, že by bylo splněno více podmínek, uplatní se formát té podmínky, která je první v pořadí.

Podmíněné formátování definované vzorcem

V případě, že potřebujeme ovlivnit vizuální formát jiných buněk hodnotou určité buňky, je nutné podmínku napsat jako vzorec.

Postupujeme takto: vybereme všechny buňky, jejichž formát má být ovlivňován hodnotou v určité buňce – Formát – Podmíněné formátování – ze seznamu vybrat volbu Vzorec – do pole vedle seznamu napsat vzorec.
Až na to, že vzorec musí začínat rovnítkem, je jeho konstrukce shodná s konstrukcí argumentu Podmínka ve funkci KDYŽ(Podmínka;Ano;Ne). Pokud podmínka vyjádřená vzorcem bude splněna, uplatní se na předem vybrané buňky nastavené formátování. Pokud podmínka splněna nebude, formátování buněk se nezmění.
Aby podmíněné formátování definované vzorcem plnilo svůj účel – ovlivnit hodnotou v konkrétní buňce formát jiných buněk – musí se v tomto vzorci vyskytovat odkaz na onu konkrétní buňku.

Vzhledem k tomu, že i zde je možné použít odkaz absolutní, relativní nebo smíšený, představuje podmíněné formátování definované vzorcem vysoce efektivní, i když ne zrovna triviální aparát.

Podmíněné formátování a funkce RANK

Můžeme zadat až tři podmínky. Podmínky mohou být na kontrolu konkrétní hodnoty v buňce nebo podmínky zadané vzorcem. V uvedeném příkladu použiji obě varianty. Podmíněné formátování najdete v menu Formát – Podmíněné formátování.
Pro ukázku jsem vytvořila jednoduchou statistiku týdenního plnění, kde jsou zadána fiktivní jména a jejich % plnění úkolů. V tomto příkladu nám Podmíněné formátování barevně odliší sledované hodnoty. V této tabulce chci sledovat a barevně rozlišit:

  • % plnění (< 75)-černá, (75 – 85)-červená, (85 – 100)-zlatá, (100 >)-zelená
  • Nejvyšší hodnotu plnění
  • Nejnižší hodnotu plnění
  • Nejčastější hodnotu plnění
  • Vyhodnocení – první a poslední

Nejdříve zadáme podmínku pro barevné odlišení % plnění (viz. legenda). Označíme oblast s daty „D10:G14“ a otevřeme Podmíněné formátování (Formát – Podmíněné formátování). Budeme kontrolovat hodnotu buněk a zadáme podmínky podle obrázku. Po stisku tlačítka Formát se zobrazí okno pro formátování. Zde vyberte barvu pozadí a tučné písmo. Další podmínku vložíme stiskem tlačítka Přidat. Po dokončení potvrďte tlačítkem OK.

Potřebujeme 4 úrovně odlišení, ale máme možnost zadat jen tři podmínky. To vyřešíme tak, že oblast „D10:G14“ naformátujeme zeleně (plnění na 100%) standardním formátováním. Podmíněné formátování je nadřazené standardnímu. Po zadávání hodnot se buňky formátují podle legendy (= naše podmínky).

Přejdeme k dalšímu kroku. Vzhledem k tomu, že pro další sledování použijeme jiné podmínky, musela jsem vytvořit novou tabulku, kde hodnoty jsou propojené s původní tabulkou. Tím je docílena aktualizace druhé tabulky. Zde sledujeme a potřebujeme zvýraznit tři hodnoty.

  • Nejčastější hodnota (nejčastěji dosažený výkon) – tyrkysová, vzorec MODE($D$20:$G$24)
  • Nejvyšší hodnota (nejvyšší dosažené plnění a kdo = nejlepší výkon) – žlutá, vzorec MAX($D$20:$G$24)
  • Nejnižší hodnota (nejnižší dosažené plnění a kdo = nejnižší výkon) – jasně zelená, vzorec MIN($D$20:$G$24)

Nastavte se na buňku D20 a otevřete Podmíněné formátování (Formát – Podmíněné formátování). Budeme kontrolovat hodnotu buněk vzorcem a zadáme podmínky podle obrázku.
Po stisku tlačítka Formát se zobrazí okno pro formátování. Zde vyberte barvu pozadí buňky. Další podmínku vložíme stiskem tlačítka Přidat. Po dokončení potvrďte tlačítkem OK. Naformátovanou buňku zkopírujeme na zbylou oblast tabulky.

Posledním krokem bude vyhodnocení plnění. Hodnocení bude na základě nejvyššího průměru jedince. Proto si do buňky H20 zadáme výpočet průměrného plnění za čtyři týdny. Vzorec bude vypadat takto „=PRŮMĚR(D20:G20)“. Vzorec zkopírujeme až k buňce H24. Abychom zjistili, kdo je první, tak do buňky B20 (nalevo od jména) vložím vzorec „=RANK(H20;$H$20:$H$24)“. Vzorec zkopírujeme až k buňce B24.

Funkce RANK

Vrátí pořadí argumentu (podle velikosti) v seznamu čísel. Hodnota pořadí je svou velikostí úměrná jiným hodnotám v seznamu. (Pokud by bylo potřeba seřadit seznam, bude pořadí čísla podle velikosti zároveň jeho pozicí.)

Syntaxe – RANK(číslo;odkaz)

  • číslo – je číslo, jehož pořadí hledáte. V našem případě je to pořadí průměrného plnění.
  • odkaz – je matice nebo odkaz na seznam čísel. Nečíselné hodnoty jsou ignorovány. V našem případě je to oblast s průměrnými hodnotami, která se nachází v „H20:H24“.

Vzorce nám vrátí pořadové číslo ze seznamu průměrů plnění. Tím získáme přehled o tom, kdo a na jakém místě skončil. To je ideální pro finální vyhodnocení, kde chci odprezentovat první a poslední místo v týdenním plnění. Do buňky D3 zadám vzorec, kterým chci vyhledat první místo.
Použiji funkci SVYHLEDAT a vzorec bude vypadat takto „=SVYHLEDAT(1;$B$20:$C$24;2;0)“. Do buňky D4 zadám vzorec, kterým chci vyhledat poslední místo. Použiji funkci SVYHLEDAT a vzorec bude vypadat takto „=SVYHLEDAT(5;$B$20:$C$24;2;0)“.

Pomocí Podmíněného formátování a funkce RANK lze vytvořit přehledné statistky s vyhodnocením. Celkový pohled na uvedený příklad.

Vyhledání buněk s podmíněným formátem

Pro získání orientace o tom, které buňky mají nastaveno podmíněné formátování a které nikoliv, slouží příkaz na kartě Domů. V pravé části karty vyberete tlačítko Najít a vybrat -> Přejít na – jinak … – v dialogovém okně Přejít na zapnout volbu Podmíněné formáty. Pokud předem byla/nebyla vybrána oblast, označí se všechny buňky z této oblasti/v celém aktivním listu, které mají definováno podmíněné formátování.

Odstranění podmíněných formátů

V případě, že potřebujeme odstranit podmíněné formátování, postupujeme takto: vybrat příslušné buňky Formát – Podmíněné formátování – tlačítko Odstranit v dialogovém okně Odstranit podmíněný formát zaškrtnout podmínku, kterou chceme odstranit.

C1 – Podmíněné formátování

Přepište si níže uvedenou tabulku s daty.

Podmíněné formátování v Excelu

Obarvení hledané hodnoty.
V prvním příkladu chceme ve zdrojové tabulce označit společnost, která je uvedena v buňce F3. V našem konkrétním případě se jedná o společnost Coca Cola.

Obarvení celého řádku.
V dalším příkladu u zdrojové tabulky zůstaneme a budeme chtít, aby se obarvila nejen společnost, kterou vybereme v seznamu, ale rovnou celý řádek.

Pokud si vedle sebe porovnáte tyto dva podmíněné formáty, tak zjistíte, že jediný rozdíl je v tom, pro které buňky podmíněné formátování platí. V prvním příkladu, kde jsme chtěli obarvit hledanou hodnotu, platí podmíněný formát pouze pro buňky A4:A12, kdežto ve druhém případě, kdy chceme obarvit celý řádek, platí podmíněný formát na celou tabulku (A16:D24) a proto se obarví celý řádek.

Cvičení

C1 – Prospěch žáků

  • Vytvořte tabulku podle vzoru.
  • Nastav podmíněné formátování buněk jako barvu pro rozmezí známek 1-2, 3, 4-5.

C2 – Analýza prodeje

Vytvořte tabulku zobrazující měsíční prodeje různých produktů a použijte podmíněné formátování k vizuálnímu vyhodnocení výkonu jednotlivých produktů.

Vytvořte tabulku s následujícími sloupci:

  • Název produktu
  • Měsíc (leden – prosinec)
  • Počet prodaných kusů
  • Celkový zisk (v Kč)

Vyplňte tabulku alespoň 10 různými produkty a jejich prodeji za 12 měsíců s náhodnými hodnotami.

Použijte podmíněné formátování na následující pravidla:

🔹 Označení úspěšných a neúspěšných měsíců:

  • Pokud je počet prodaných kusů nad 100, buňka se označí zelenou barvou.
  • Pokud je počet prodaných kusů mezi 50 a 100, buňka se označí žlutou barvou.
  • Pokud je počet prodaných kusů pod 50, buňka se označí červenou barvou.

🔹 Další pravidlo:

  • Pokud byl prodej v daném měsíci 0 kusů, buňka se označí černým pozadím a bílým textem.

C3 – Monitoring docházky studentů

Vytvořte tabulku s přehledem docházky studentů za jeden měsíc a použijte podmíněné formátování pro snadnou identifikaci problémových a vzorných docházkových trendů.

Vytvořte tabulku s následujícími sloupci:

  • Jméno studenta
  • Dny v měsíci (1–30, každý den bude mít svou vlastní buňku)

Vyplňte tabulku alespoň 10 studenty a vyznačte docházku pomocí těchto symbolů:

  • P (přítomen)
  • A (absent)
  • O (omluvená absence)

Použijte podmíněné formátování na následující pravidla:

Barevné označení jednotlivých hodnot v tabulce:

  • Zelená – pokud je student přítomen (P)
  • Červená – pokud je student nepřítomen (A)
  • Oranžová – pokud má student omluvenou absenci (O)

C4 – Vánoční Excel, podmíněné formátování

  1. Otevřete si prázdný list a označte sloupce C až Y (pro označení celých sloupců klikněte na název sloupce C a přetáhněte myší až na Y).
  2. Na záložce Domů klikněte na Formát a zvolte Šířka sloupce. Zadejte hodnotu 2.70 a potvrďte tlačítkem OK.
  3. Na záložce Domů klikněte na Formát a zvolte Výška řádku. Zadejte hodnotu 33.80 a potvrďte tlačítkem OK.
  4. Označte buňku C1 a vložte tam tento vzorec:
    =KDYŽ(ABS(SLOUPEC($N$1)-SLOUPEC())<=ŘÁDEK()-1;RANDBETWEEN(1;10);"")
  5. Po zadání vzorce do buňky C1 by se nemělo zobrazit nic, protože výsledek vzorce v této buňce je nepravda. Nyní však označte tuto buňku, klepněte na malé plusko v jejím pravém dolním rohu a přetáhněte myší až na buňku Y1, čímž se vzorec zkopíruje do všech buněk v rozsahu.
  6. V buňce N1 se zobrazí náhodné číslo. Nyní celý tento rozsah buněk C1:Y1 opět stejným způsobem přetáhněte až na řádek 12.
  7. Vytvoří se vám stromek z náhodných čísel. Pojďme jej nyní ozdobit. Označte celý rozsah C1:Y12 a v záložce Domů klikněte na Podmíněné formátování, kde zvolte Nové pravidlo.
  8. Vyberte možnost Určit buňky k formátování pomocí vzorce. Napište tam vzorec: =JE.ČÍSLO(C1)
  9. Otevřete záložku Výplň. Vyberte hezkou zelenou barvu. Nyní přepněte na záložku Písmo a nastavte jako barvu písma stejnou zelenou. Potvrďte tlačítkem OK.
  10. Zbývá poslední krok, kterým je ozdobení stromku světýlky. Označte celý výběr C1:Y12 a klikněte na Podmíněné formátování. Tam zvolte možnost Sady ikon a vyberte barevné koule.
  11. Zkuste stisknout a podržet klávesu F9.
Více: