Tipy pro podmíněné formátování

Pokud často pracujete s mnoha údaji, pak při jejich vyhodnocování zjistíte, že je užitečné si vhodně nastavovat podmíněné formátování, které určité hodnoty vyznačí barevně. Tím snadno najdete potřebné hodnoty mezi údaji ostatními.
Přitom podmíněné formátování můžete nastavit na jednu buňku nebo na celou oblast buněk. Zároveň se zde lze odkazovat na dané buňky nebo i buňky jiné. Navíc je zde možnost nastavit neomezený počet různých podmínek.

Kde se podmíněné formátování nastaví

Na kartě Domů ve skupině ikon Styly si klepněte na ikonu Podmíněné formátování. Zjistíte, že zde najdete již předvolené nabídky pro rychlé formátování nebo se podívejte přímo na konec, kde se nachází příkazy jako Nové pravidlo nebo Správa pravidel. Pokud klepnete na příkaz Nové pravidlo, zobrazí se dialogové pro nastavení pravidla. Přičemž v horní polovině okna jsou na výběr různé typy pravidel, v dolní pak pro vybrané pravidlo nastavení upřesňujete. Dolní část dialogového okna se bude měnit podle zvoleného typu.
To znamená, že si nejprve vždy vyberete vhodný typ a poté vše nastavíte v dolní části. Po potvrzení tlačítkem OK již bude podmíněné formátování v označených buňkách pracovat.

Jednoduché nastavení barev

Představte si, že sledujete částky vydané na drobné nákupy v rámci měsíce a chcete, aby nepřesahovaly například 3000 Kč. Proto by bylo přehledné, že když někdo částku překročí, tak aby se zobrazila červeně. A pokud třeba někdo zaplatí méně jak 1000 Kč, pak si můžete tuto hodnotu naopak nechat zobrazit zeleně.
Označte si tedy hodnoty v dané tabulce a poté v dialogovém okně Nové pravidlo formátování vyberte hned druhý typ Formátovat pouze buňky obsahující. Tím se změní dolní část dialogového okna a vy v ní můžete zadat potřebnou podmínku.

Jako podmínku můžete vybrat je větší než nebo rovná se a do sousedního políčka pak zapište číslo 3000. Poté klepněte na tlačítko Formát a v zobrazeném dialogovém okně zvolte pro barvu písma červenou. Po potvrzení uvidíte ukázku nastaveného formátování v části Náhled. A po klepnutí na tlačítko OK se již formátování projeví přímo v tabulce.
 Pokud nyní ale chcete ale přidat ještě jedno pravidlo, například aby hodnoty menší než 1000 byly zelené, je třeba znovu klepnout na ikonu Podmíněné formátování a Nové pravidlo. Ale můžete také klepnout na příkaz Správa pravidel. Tím se zobrazí dialogové okno s již existujícími pravidly v dané oblasti.

A klepnutím na tlačítko Nové pravidlo stejným způsobem nastavíte tuto druhou podmínku pro hodnoty menší než 1000. Výhoda tohoto dialogového okna je v tom, že na jednom místě krásně vidíte všechny nastavené pravidla v dané oblasti a snadno je tak můžete rušit, opravovat nebo přidávat pravidla nová.

Jak zvýraznit stejnou hodnotu

Představte si, že máte dva sloupce čísel a potřebujete ve druhém z nich vždy označit tu hodnotu, která je stejná jako ve sloupci prvním. Potřebujete tedy danou buňku porovnávat s buňkou sousední, například buňku B2 potřebujete porovnat s buňkou A2. Označte si tedy druhý sloupec a poté můžete vybrat v dialogovém okně Nové pravidlo formátování opět typ Formátovat pouze buňky obsahující.

Zde však v dolní části nyní změňte zápis tak, aby se buňky opravdu porovnávaly. To znamená, vyberte vztah Rovná se a pak do sousední buňky zapište odkaz právě na sousední buňku, tedy v daném příkladě A2. Pak ještě pomocí tlačítka Formát nastavte způsob zvýraznění stejných hodnot, například světle modré podbarvení. A po potvrzení zjistíte, že máte hned označené stejné hodnoty ve sloupci B. Podobným způsobem můžete udělat také nastavení pro sloupec A. V praxi pak není nutné ani to, aby dané dva sloupce spolu sousedily.

Jak označit maximální hodnotu ve sloupci

Vezměte si předchozí tabulku nákupů. Tentokrát byste ale chtěli sledovat to, které oddělení v daném měsíci udělalo nejvyšší nákup a ten vždy označit. To znamená, že hledáte v rámci sloupce maximální hodnotu. V MS Excelu existuje funkce Max, kterou nyní při nastavení podmíněného formátování můžete použít.

Označte si tedy celou tabulku a poté v dialogovém okně Nové pravidlo formátování vyberte typ Určit buňky k formátování pomocí vzorce. V dolní části okna se pak zobrazí pouze jedno políčko pro zápis vašeho vztahu, vzorce, který bude vyjadřovat vaši podmínku. Pro danou situaci to bude =B3=MAX(B$3:B$7), protože porovnáváte vždy danou buňku, zda není maximem vzhledem k ostatním v daném sloupci. Proto musí být u čísel řádku absolutní adresa. U písmen sloupce naopak označení absolutní adresy být nesmí, protože tento vztah chcete, aby platil pro všechny sloupce postupně.
Po nastavení formátu a potvrzení pak můžete dostat následující vzhled tabulky.

Jak označit v tabulce nadprůměrné hodnoty

Pokud potřebujete například v rámci řádku označit hodnoty, které jsou nad průměrem daného oddělení (podobně by šlo i pod průměrem, například při určování výkonu), pak pro nastavení podmíněného formátování zase použijte vzorec. Zde si místo hledání maxima budete ale počítat průměrnou hodnotu a označovat jen ty buňky, které jsou vyšší než hodnota průměrná.

To znamená, že v dialogovém okně Nové pravidlo formátování zase vyberete typ Určit buňky k formátování pomocí vzorce. Zde do zobrazeného políčka již zapíšete vzorec ve tvaru =B3>PRŮMĚR($B3:$E3). Všimněte si, že zde jsou adresy zpevněny ve směru sloupců, protože se hledají nadprůměrné hodnoty na řádcích.

Jak udělat ohraničení řádku

Představte si, že postupně doplňujete tabulku. Nechcete mít však například kvůli tisku ohraničenou celou tabulku, ale vždy jen vyplněnou neprázdnou část. Můžete tedy nastavit podmíněné formátování tak, že pokud doplníte údaj do první buňky na daném řádku tabulky (to znamená, že už bude neprázdná), pak se orámuje celý řádek tabulky. Jak toto nastavit? Při nastavení se dá využít právě funkce Je.prázdné, která se bude ptát právě na obsah první buňky daného řádku.

V dialogovém okně Nové pravidlo formátování pak použijte vzorec ve tvaru =JE.PRÁZDNÉ($A2)=NEPRAVDA. Zde je důležité, že odkaz na první buňku je zpevněn ve směru sloupců, aby se všechny buňky na řádku opravdu ptali na tu první a nedocházelo k posunu. Po potvrzení pak vaše tabulka bude vypadat stejně jako na obrázku, kde po zápisu čísla do první buňky je hned ohraničený celý řádek.

Podobně byste třeba mohli podbarvovat celý řádek, nebo v celém řádku upravovat formát písma či zvýraznit celý řádek na základě určité hodnoty.

Jak označit určité dny v týdnu

Při plánování činností, práce, pracovních směn či služebních cest potřebujete mít například rychle označení soboty či neděle v rámci měsíce. Při nastavení podmíněného formátování pak můžete využít funkci Dentýdne, která umí vypočítat pořadové číslo dne v týdnu.

Připravte si tedy seznam všech dnů v měsíci a potom v dialogovém okně pro nastavení podmíněného formátování zapište právě funkci Dentýdne ve tvaru =DENTÝDNE(D2;2)=6. Číslo 6 zde znamená, že se ptáte na sobotu. Pokud pak použijete nastavení ještě druhé podmínky s číslem 7, pak se budete ptát na neděli.

Můžete tak mít nastavené dvě podmínky pro stejnou oblast buněk. Vše je vidět hezky právě ve Správci pravidel podmíněného formátování. Jinak při práci s Excelem si můžete pro danou oblast nastavit libovolný počet pravidel.

Jak označit buňky s určitým textem

Představte si, že byste potřebovali označit řádky, ve kterých se v daném sloupci nachází určitý text, například typ výrobku, město, barva,… Pro takové označení zase můžete využít podmíněné formátování, tentokrát ale s funkcí Hledat, která vrátí chybové hlášení, když se daný text v buňce nenachází nebo pozici jeho výskytu jako celé číslo. To znamená, že je ještě třeba zjistit to, zda je výsledkem funkce číslo a k tomu nám pomůže jednoduchá informační funkce Je.číslo.

Celý vzorec při nastavení podmíněného formátování pak může být =JE.ČISLO(HLEDAT("Tipo";$E2;1))=PRAVDA. To znamená, že pokud funkce Hledat najde text, tak vrátí číselnou hodnotu a celý řádek může být podbarvený vybranou výplní. Pokud funkce Hledat vrátí chybovou hodnotu, pak výsledkem funkce Je.číslo je Nepravda a tedy daný řádek zůstane stejný.
Všimněte si zde ještě absolutní adresy u písmene na odkaz E2. Je tam proto, aby podmíněný formát se mohl nastavit pro celý řádek ale hledá se text pouze ve sloupci E.

Jak v tabulce nezobrazovat chybové hlášení

Občas se připravují tabulky průběžně, někdy zase dopředu na celé časové období. A pokud jsou v těchto tabulkách i předpřipravené vzorce, pak bývají v takových tabulkách i chybová hlášení. To znamená, že máte vzorce sice správně, ale protože zde ještě chybí některé údaje, pak se hlášení o chybě zobrazí. Chybové hlášení se také často zobrazuje při použití vyhledávacích funkcí, protože to znamená, že daná hodnota neexistuje. Ve výsledné tabulce k tisku však toto nevypadá hezky.
 Pokud tedy nechcete, aby chybová hlášení v takových situacích byla vidět, ale přitom, jak jsou do buněk správné hodnoty doplněny tak aby se výsledek zobrazil. Můžete použít opět podmíněné formátování za pomocí funkce Je.chybhodn, která umí ověřit, zda se v buňce nachází chybové hlášení, tedy #NENÍ_K_DISPOZICI, #HODNOTA!, #ODKAZ!,  #NÁZEV?, #DĚLENÍ_NULOU!,  #ČÍSLO!, #NULL! (#N/A, #HODNOTA!, #REF!, #NÁZEV?, #DIV/0!, #NUM!, #NULL). Pokud se jedná o chybové hlášení, funkce vrátí hodnotu Pravda, jestliže tomu tak není, výsledkem je Nepravda.

Jestliže tedy výsledek funkce bude Pravda, tak pro písmo v buňce nastavíte v podmíněném formátování bílé, jinak ponecháte barvu původní. A po potvrzení se vám vše začne v tabulce správně zobrazovat či skrývat.

Jestliže se časem do tabulky doplní nové údaje o typu kurzu, výsledek se v ostatních buňkách na řádku automaticky zobrazí, aniž byste museli měnit barvu písma nebo mazat či doplňovat vzorce.

Jak zvýraznit nejčastější hodnotu

Už se vám někdy stalo to, že jste před sebou měli tabulku z mnoha hodnotami a zajímalo vás, která hodnota se v tabulce či daném sloupci vyskytuje nejčastěji? Při běžných statistických výpočtech by se jednalo o vyhledání modusu. A MS Excel umí modus také počítat a to pomocí funkce Mode. To znamená, že pokud tuto funkci použijete při definování podmíněného formátování, umíte také snadno označit hodnoty, které se v tabulce vyskytují nejčastěji.

Jak? Prostě po označení tabulky v dialogovém okně Nové pravidlo formátování vyberete typ Určit buňky k formátování pomocí vzorce a potom do políčka pro zápis vzorce použijete srovnání, zda daná hodnota buňky (není zde absolutní adresa, aby srovnání prošlo celou tabulkou) se rovná modusu z celého výběru (sloupce hodnot). Zde naopak absolutní adresy musí být, aby porovnání bylo přesně se zadaným rozsahem buněk. Po potvrzení se vám zvolenou barvou dané buňky bez hledání přímo samy označí.