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

Podmíněné formátování umožňuje měnit vzhled buňky podle jejího obsahu, podle určitého pravidla, nebo podle hodnot obsažených v jiných buňkách. Může sloužit k signalizaci chybných dat nebo ke zvýraznění buněk s odlišným obsahem. Pomocí podmíněného formátování můžete např. zvýraznit buňky:

• se zápornými hodnotami, hodnotami které jsou vyšší než zadaný limit nebo které nespadají do zadaného rozmezí
• s nadprůměrnými nebo podprůměrnými hodnotami
• s textovými údaji, obsahujícími určitý úsek textu
• s jedinečnými nebo duplicitními údaji
• u buněk s datumovými hodnotami je možné porovnávat obsah s aktuálním datumem, např. zvýraznit buňky s datumem pro tento týden nebo měsíc

Podstata podmíněného formátování spočívá v tom, že u označené buňky nebo oblasti vytvoříte pravidlo a jednotlivé buňky toto pravidlo svým obsahem splňují nebo nesplňují. Při splněném pravidlu se uplatní odlišné formátování buňky (písmo, barevné pozadí apod.), které je součástí vytvořeného pravidla. Pravidlo podmíněného formátování funguje dynamicky a vyhodnocuje se při každém zápisu do buňky.

Do jedné buňky nebo oblasti je možné zadat i více formátovacích pravidel.

Pro vytvoření podmíněného formátování označíte buňku nebo oblast a na kartě Domů klepnete na tlačítko Podmíněné formátování. Po použití tohoto tlačítka se zobrazí rozsáhlejší nabídka, ve které však některé příkazy neumožňují využít všech možností, které podmíněné formátování poskytuje. Nejpraktičtější způsob je proto použít poslední příkaz Správa pravidel. Tím si zobrazíte dialogové okno, kde máte k dispozici všechny možnosti:

• tlačítkem Nové pravidlo vytvoříte nové pravidlo pro formátování,
• tlačítkem Upravit pravidlo označené pravidlo změníte,
• tlačítkem Odstranit pravidlo označené pravidlo vymažete.

Změny v podmíněném formátování potvrdíte tlačítkem OK.

Formátování buněk podle číselných hodnot

Toto nejčastěji používané podmíněné formátování nastavíte tímto postupem:

• Označte potřebné buňky.
• Klepněte na tlačítko Podmíněné formátování a zvolte příkaz Správa pravidel.
• V zobrazeném okně pro správu pravidel klepněte na tlačítko Nové pravidlo.
• V dalším zobrazeném okně označte volbu Formátovat pouze buňky obsahující.

• V levém seznamu ponechte volbu Hodnota buňky.
• V dalším seznamu zvolte způsob porovnání. Napravo zapište hodnoty pro porovnání.
• Klepněte na tlačítko Formát.

• V okně pro formátování buněk nastavte odlišné písmo, popř. odlišné barevné pozadí. Nastavení formátu potvrďte tlačítkem OK.
• Klepněte na tlačítko OK. Tím se podmíněné formátování nastaví.

Nadprůměrné, podprůměrné, nejvyšší a nejnižší hodnoty

Tvorba podmíněného formátování pro zvýraznění nadprůměrných nebo podprůměrných hodnot je jednoduchá. Zobrazte si okno pro správu pravidel, vytvořte nové pravidlo a v dalším okně označte volbu Formátovat pouze hodnoty nad nebo pod průměrem.

V seznamu vyberete volbu pro hodnoty nad nebo pod průměrem, popř. pro hodnoty vyšší nebo rovné průměru nebo menší nebo rovné průměru. Pomocí tlačítka Formát nastavíte vzhled buněk a potvrdíte tlačítkem OK.

Pro zvýraznění nejvyšších nebo nejnižších hodnot použijte tento postup:

• Označte buňky a vytvořte pro ně nové pravidlo podmíněného formátování.
• V dalším okně označte volbu Formátovat pouze hodnoty zařazené jako první nebo poslední.

• V seznamu vyberte volbu Prvních (nejvyšší hodnoty) nebo Posledních (nejnižší hodnoty).
• Napravo od seznamu zapište počet nejvyšších nebo nejnižších hodnot, které se mají formátováním odlišit.
• Volba % z vybrané oblasti znamená, že se naformátuje zapsaný podíl hodnot, udaný v procentech.
• Pomocí tlačítka Formát nastavte vzhled buněk a klepněte na tlačítko OK.

Tento postup můžete použít nejen k formátování číselných, ale také datumových hodnot. Volba Prvních pak znamená nejnovější a volba Posledních nejstarší datumy. U textových hodnot je tento způsob formátování neúčinný – není tedy možné odlišit např. několik textů ze začátku abecedy.

Formátování textových hodnot

Jestliže formátovaná oblast buněk obsahuje texty, často potřebujete zvýraznit buňky obsahující určité slovo nebo text. Postup:

• Označte buňky a vytvořte pro ně nové pravidlo podmíněného formátování.
• V dalším okně označte volbu Formátovat pouze buňky obsahující.

• V levém seznamu nastavte volbu Určitý text.
• V dalším seznamu vyberte způsob hledání. Zadaný textový řetězec se může hledat na začátku nebo kdekoliv v obsahu buňky.
• Napravo od tohoto seznamu zapište potřebný vzorek textu.
• Pomocí tlačítka Formát nastavte vzhled buněk a klepněte na tlačítko OK.

Formátování datumových hodnot

U buněk s datumovými hodnotami můžete vytvořit podmíněné formátování trojího typu:

• Datum, zapsané v buňce, se porovnává s pevnou datumovou hodnotou (např. datum musí ležet v prvém čtvrtletí roku 2014). Pro tento způsob formátování použijete stejný postup, jako při formátování číselných hodnot.
• V oblasti se mají zvýraznit nejnovější nebo nejstarší datumy – toto formátování je popsané výše.
• Datum, zapsané v buňce, se porovnává s aktuálním datumem v počítači. Při tomto způsobu formátování se vzhled oblasti mění s časem: např. buňky, zvýrazněné dnes, nemusí být již zvýrazněny příští týden.

Tento poslední způsob formátování datumových hodnot nastavíte takto:

• Označte buňky a vytvořte pro ně nové pravidlo podmíněného formátování.
• V dalším okně označte volbu Formátovat pouze buňky obsahující.
• V levém seznamu nastavte volbu Data připadající na následující dny.

• V dalším seznamu vyberte způsob porovnání s aktuálním datem (dnes, zítra, tento týden atd.).
• Pomocí tlačítka Formát nastavte vzhled buněk a klepněte na tlačítko OK.

Formátování buněk grafickým vyjádřením velikosti

Tento varianta podmíněného formátování vloží do všech buněk ve formátované oblasti grafické znázornění hodnoty, zapsané v buňce. Formátování je použitelné pro buňky s číselnými nebo s datumovými hodnotami; u datumových hodnot se nejnovější datum chápe jako nejvyšší hodnota a nejstarší datum jako nejnižší hodnota. Pro grafické vyjádření velikosti použijete volbu Formátovat všechny buňky na základě hodnot. V dalším zobrazeném okně si vyberete typ grafiky, popř. další nastavení.

Do buněk můžete vložit čtyři typy grafiky:

• Dvoubarevnou škálu – hodnoty jsou znázorněny barevným pozadím buňky, přecházejícím mezi dvěma krajními barvami.
• Tříbarevnou škálu – hodnoty jsou znázorněny barevným pozadím buňky, přecházejícím mezi třemi barvami.
• Datovou čáru – do pozadí buněk se vloží barevný pruh, jehož délka se mění podle hodnoty v buňce (nejvyšší hodnota nebo nejnovější datum má nejdelší pruh).
• Sadu ikon – do buněk se vloží jedna ze tří, čtyř nebo pěti ikon, které signalizují rozdělení hodnot podle velikosti.

U dvoubarevné škály můžete nastavit obě krajní barvy. V seznamech Maximum a Minimum si můžete nastavit rozsah, pro který se uplatní přechody v barevné škále. Výchozí hodnoty jsou Nejnižší hodnota a Nejvyšší hodnota. U těchto voleb krajní barvy uplatní pouze pro nejvyšší a nejmenší hodnotu ve formátované oblasti. Druhou možností je v seznamech nastavit volbu Číslo nebo Procenta a do textového políčka pod seznamem zapsat potřebnou hodnotu. V takovémto případě se krajní barvy uplatní u všech buněk, jejichž hodnoty jsou menší než nastavené minimum, popř. větší než nastavené maximum.

U tříbarevné škály nastavíte:

• Polohu středního bodu, který bude označen prostřední barvou. Výchozí nastavení je 50 percentilů (percentily dělí sérii hodnot podle velikosti na 100 rovnoměrných úseků). Výchozí nastavení znamená, že hodnoty v jednotlivých buňkách budou rozděleny přesně 1:1 kolem střední barvy. Polohu středního bodu můžete zvolit také v procentech (volba Procenta) nebo pevnou hodnotu (volba Číslo).
• Polohu krajních bodů: použijete seznamy Maximum a Minimum stejným způsobem, jako u dvoubarevné škály.
• Barvy, odpovídající nejmenší, největší a střední hodnotě.

Při formátování pomocí datové čáry máte tyto možnosti:

• V seznamu Barva pruhu vyberete její barvu.
• V seznamech Nejkratší čára a Nejdelší čára můžete nastavit rozsah délky barevné čáry obdobně, jako u dvoubarevné škály. Výchozí hodnoty Nejnižší hodnota a Nejvyšší hodnota nastaví délky barevných pruhů plynule od nejvyšší do nejmenší hodnoty. Jestliže v seznamech nastavíte volbu Číslo nebo Procenta a do textového políčka pod seznamem zapíšete potřebnou hodnotu, nejkratší nebo nejdelší pruh se přiřadí všem buňkám, jejichž hodnoty jsou menší než nastavené minimum, popř. větší než nastavené maximum.
• Volba Zobrazit pouze čáru skryje obsah buněk a zobrazí se pouze barevné pruhy.

Při formátování pomocí ikon se uplatní tyto možnosti:

• V seznamu Styl ikony vyberete typ ikon. Excel nabízí trojice, čtveřice nebo pětice ikon.
• V textových políčcích Hodnota a v seznamech Typ můžete změnit rozmezí hodnot pro jednotlivé ikony. Je možné využít rozdělení v procentech nebo (volba Procenta) nebo podle pevných hodnot (volba Číslo). Výchozí nastavení je rovnoměrné rozdělení v procentech.
• Volbou Obrátit pořadí ikon nastavíte obráceně rozmezí pro jednotlivé ikony.
• Volbou Zobrazit pouze ikonu skryjete obsah buněk.

Další možnosti podmíněného formátování

V některých případech můžete využít ještě další tři možnosti, které podmíněné formátování nabízí. Prvou je odlišení duplicitních nebo naopak jedinečných hodnot. Po zobrazení dialogového okna pro podmíněné formátování použijte volbu Formátovat pouze jedinečné nebo duplicitní hodnoty, dále v seznamu vyberte volbu duplikovat nebo jedinečné, nastavte vhodné formátování a potvrďte tlačítkem OK. Tento způsob podmíněného formátování můžete uplatnit u kteréhokoliv typu hodnot: číselných, textových i datumových.

Jestliže v dialogovém pro podmíněné formátování použijete volbu Formátovat pouze buňky obsahující, v levém seznamu jsou k dispozici ještě čtyři další volby:

• Prázdné buňky – nevyplněné buňky je možné odlišit barevným pozadím.
• Jiné než prázdné - vyplněné buňky je možné odlišit barevným pozadím.
• Chyby – nastavené formátování se uplatní u buněk s chybovou hodnotou.
• Bez chyb – nastavené formátování se uplatní u buněk, které neobsahují chybovou hodnotu.

Poslední dva způsoby podmíněného formátování využijete u vzorců.

Nastavení podmíněného formátování zjednodušeným způsobem

Podmíněné formátování můžete také rychle nastavit pomocí příkazů v tlačítko Podmíněné formátování, a to bez využití dialogového okna. Ve všech případech se zobrazí pomocné okénko, ve kterém upřesníte zvolenou podmínku a vyberete některý z předvolených způsobů formátování. Poslední volba Vlastní formát zobrazí další okno pro kompletní nastavení formátu.

Příkazem Zvýraznit pravidla buněk můžete nastavit formátování číselných, datumových a textových hodnot a zvýraznění duplicitních nebo jedinečných položek.

Příkazem Pravidla pro nejnižší či nejvyšší hodnoty můžete nastavit formátování nejvyšších nebo nejmenších hodnot a dále hodnot nadprůměrných nebo podprůměrných.

Další tři příkazy Datové čáry, Barevné škály a Sady ikon slouží k rychlému výběru zvoleného typu barevného pruhu, tříbarevné škály nebo ikon. Tyto příkazy však neumožňují nastavit polohu krajních bodů, popř. středního bodu a rozmezí hodnot pro jednotlivé ikony.

Využití více pravidel

V jedné datové oblasti je možné vytvořit více pravidel podmíněného formátování. Při tvorbě dvou a více pravidel je nutno rozeznávat dvě základní situace:

• Mezi pravidly není konflikt – pro každou buňku v oblasti může být vyhodnoceno jako pravdivé pouze jedno pravidlo.
• Mezi pravidly může nastat konflikt – u některých buněk může být splněno více pravidel.

Příkladem prvé situace může být podmíněné formátování, ve kterém se mají záporné hodnoty zvýraznit červeným a hodnoty nad 10 000 modrým písmem. V tomto případě je jedno, v jakém pořadí obě pravidla vytvoříte.

Jestliže mezi pravidly může nastat konflikt, platí tyto zásady:

• Pravidlo, umístěné výše, má vyšší prioritu.
• Nově přidávané pravidlo se zařadí jako první, tj. má nejvyšší prioritu.
• Jestliže se u buňky mohou uplatnit dvě pravidla, formátující totéž různým způsobem (např. dvě různé barvy písma nebo pozadí buňky), uplatní se pravidlo s vyšší prioritou.
• Jestliže se u buňky mohou uplatnit dvě pravidla, formátující dvě různá nastavení (např. jedno pravidlo barvu písma a druhé pravidlo barvu pozadí buňky), použijí se obě nastavení.

Prioritu vytvořených pravidel můžete měnit přesunem označeného pravidla nahoru a dolů. K tomu použijete tlačítka se šipkou nahoru nebo šipkou dolů.

Jako příklad si můžeme uvést použití dvou pravidel: pravidlo č.1 formátuje buňky s hodnotou nad 1000 červeným písmem, pravidlo č.2 buňky s hodnotou nad 5000 modrým písmem. Jestliže bude pravidlo č.1 uvedeno jako první, budou všechny hodnoty nad 1000 zvýrazněny červeně. Modrá barva se neuplatní, protože pravidlo č.1 má vyšší prioritu. Jestliže bude jako první uvedeno pravidlo č.2, modrá barva má vyšší prioritu. Proto budou hodnoty nad 5000 zvýrazněny modře a hodnoty nad 1000, ale menší než 5000 červeně.

Prioritu pravidel můžete také ovlivnit volbou Zastavit, pokud platí. Jestliže je tato volba označena u pravidla s vyšší prioritou, buňky, u kterých se toto pravidlo uplatnilo, již dalším pravidlům nepodléhají a jejich formát se dále nemění.

Příkladem může být formátování sloupce s datumovými hodnotami. Buňky s dnešním datumem jsou zvýrazněny tučným písmem (pravidlo s vyšší prioritou), datumy připadající na aktuální měsíc žlutým pozadím (pravidlo s nižší prioritou). Při takto nastaveném formátování budou všechny datumy připadající na aktuální měsíc se žlutým pozadím a dnešní datumy budou mít navíc tučné písmo. Jestliže u prvého pravidla označíte volbu Zastavit, pokud platí, dnešní datumy budou zvýrazněny tučným písmem, ale bez žlutého pozadí.

Podmínění formátování pomocí vzorce

Při tomto způsobu podmíněného formátování je formátovací pravidlo tvořeno vzorcem, který má charakter podmínky. Výsledná hodnota vzorce je ovlivněna obsahem formátované buňky. Pokud je výsledkem vzorce hodnota PRAVDA, formátování se uplatní, při hodnotě NEPRAVDA se neuplatní. Příklady:

• Ve sloupci B jsou zapsány peněžní částky, ve sloupci C sazba DPH a ve sloupci D vypočtená DPH v korunách. Částky ve sloupci B, kterým odpovídá DPH vyšší, než zadaný limit, jsou zvýrazněny.
• Ve dvou sloupcích jsou zapsány číselné nebo datumové hodnoty. Hodnota v prvém sloupci, která je menší než odpovídající hodnota ve druhém sloupci, je zvýrazněna.
• V oblasti buněk jsou zapsány číselné údaje. Buňky budou zvýrazněny, jestliže jejich součet přesáhne stanovenou hodnotu.

Při využití vzorce k podmíněnému formátování použijete tento postup:

• Označte formátované buňky. Zobrazte si dialogové okno pro nastavení podmíněného formátování a klepněte na tlačítko Nové pravidlo.
• V dalším zobrazeném okně označte volbu Určit buňky k formátování pomocí vzorce.
• Klepněte do textového políčka a vytvořte potřebný vzorec.
• Pomocí tlačítka Formát nastavte potřebné formátování.
• Potvrďte tlačítkem OK.

Odkazy na buňky se ve vytvářeném vzorci zapíší jako absolutní, tedy se znakem dolaru. Vzorec proto je proto použitelný, pokud se formátování celé oblasti buněk bude řídit společným vzorcem. Jestliže se však má vytvořený vzorec pro jednotlivé buňky měnit, je třeba upravit odkazy na relativní. To provedete vymazáním znaku $, nebo opakovaným stiskem klávesy F4.

Prvý příklad: v oblasti buněk jsou zapsány číselné hodnoty, které se mají barevně zvýraznit, pokud jejich součet přesáhne hodnotu 1000. Na listu vytvoříte pomocnou buňku se součtem a v podmíněném formátování vytvoříte potřebný vzorec. Absolutní odkazy na buňky ponecháte nezměněné. Vzorec také můžete vytvořit zápisem z klávesnice, bez nutnosti použít pomocnou buňku. Jako prvý znak je třeba zapsat rovnítko: „=SUMA($B$10:$B$12)>1000“.

Druhý příklad: ve sloupci B a C jsou datumové hodnoty. Datumy ve sloupci B, které jsou starší než odpovídající datumy ve sloupci C, je třeba zvýraznit. V tomto případě vytvoříte vzorec, odpovídající prvému řádku formátované oblasti a odkazy upravíte na relativní: „=B2<C2“.

Kopírování a odstranění podmíněného formátování

Podmíněné formátování je součástí celkového formátování buňky. U buněk s podmíněným formátováním proto platí tyto zásady:

• Při vymazání buňky klávesou Delete se podmíněné formátování nemění.
• Při kopírování buňky s podmíněným formátováním se do cílové buňky přenese i podmíněné formátování. Totéž platí pro plnění oblastí.
• Při kopírování formátu (tlačítko se štětečkem) se přenese i podmíněné formátování.
• Při kopírování buňky bez podmíněného formátování se v cílové buňce podmíněné formátování vymaže. Pokud jej chcete zachovat, musíte použít příkaz Vložit jinak a do cílové buňky přenést pouze hodnotu.