Přidání podmíněného formátování v Excelu vám umožní použít různé možnosti formátování na buňku nebo rozsah buněk, které splňují specifické podmínky, které nastavíte. Nastavení takových podmínek může pomoci uspořádat tabulku a usnadnit skenování. Mezi možnosti formátování, které můžete použít, patří změny barvy písma a pozadí, styly písma, ohraničení buněk a přidání formátování čísel do dat.
Excel má vestavěné možnosti pro běžně používané podmínky, jako je hledání čísel, která jsou větší nebo menší než konkrétní hodnota nebo hledání čísel, která jsou nad nebo pod průměrnou hodnotou. Kromě těchto přednastavených možností můžete také vytvářet vlastní pravidla podmíněného formátování pomocí vzorců aplikace Excel.
Tyto pokyny platí pro Excel 2019, 2016, 2013, 2010 a Excel pro Microsoft 365.
Použití více podmínek v Excelu
Můžete použít více než jedno pravidlo na stejná data pro testování různých podmínek. Například údaje o rozpočtu mohou mít nastaveny podmínky, které aplikují změny formátování, když je dosaženo určitých úrovní výdajů, jako je 50 %, 75 % a 100 % celkového rozpočtu.
Za takových okolností Excel nejprve určí, zda jsou různá pravidla v konfliktu, a pokud ano, program se řídí stanoveným pořadím priority, aby určil, které pravidlo podmíněného formátování má na data použít.
Hledání údajů přesahujících 25 % a 50% nárůst
V následujícím příkladu budou dvě vlastní pravidla podmíněného formátování použita na rozsah buněk B2 až B5.
- První pravidlo kontroluje, zda jsou data v buňkách A2:A5 větší než odpovídající hodnota v B2:B5 podle více než 25 %.
- Druhé pravidlo kontroluje, zda stejná data v A2:A5 přesahují odpovídající hodnotu v B2:B5 o více než 50 %.
Jak je vidět na obrázku výše, pokud platí některá z výše uvedených podmínek, barva pozadí buňky nebo buněk v rozsahu B1:B4 se změní.
- U dat, kde je rozdíl větší než 25 %, se barva pozadí buňky změní na zelenou.
- Pokud je rozdíl větší než 50 %, barva pozadí buňky se změní na červenou.
Pravidla použitá k provedení tohoto úkolu budou zadána pomocí dialogového okna Nové pravidlo formátování. Začněte vložením ukázkových dat do buňky A1 až C5, jak je vidět na obrázku výše.
V poslední části tutoriálu přidáme do buněk C2:C4 vzorce, které ukazují přesný procentuální rozdíl mezi hodnotami v buňkách A2:A5 a B2:B5; to nám umožní zkontrolovat přesnost pravidel podmíněného formátování.
Nastavení pravidel podmíněného formátování
Nejprve použijeme podmíněné formátování, abychom našli 25% nebo více významné zvýšení.
Funkce bude vypadat takto:
=(A2-B2)/A2>25 %
- Zvýrazněte buňky B2 až B5 v listu.
- Klikněte na kartu Domovská stránka ribbon.
- Kliknutím na ikonu Podmíněné formátování v ribbon otevřete rozevírací nabídku.
- Vyberte Nové pravidlo a otevřete dialogové okno Nové pravidlo formátování.
-
V části Vyberte typ pravidla klikněte na poslední možnost: Použijte vzorec k určení, které buňky se mají formátovat.
- Zapište vzorec uvedený výše do prostoru níže Formátujte hodnoty, kde platí tento vzorec:
- Kliknutím na tlačítko Format otevřete dialogové okno. Klikněte na kartu Vyplnit a vyberte barvu.
- Kliknutím na OK zavřete dialogová okna a vraťte se do listu.
- Barva pozadí buněk B3 a B5 by se měla změnit na vámi vybranou barvu.
Nyní použijeme podmíněné formátování, abychom našli 50procentní nebo větší nárůst. Vzorec bude vypadat takto:
- Zopakujte prvních pět kroků výše.
- Zapište vzorec do níže uvedeného prostoru Formátujte hodnoty, kde platí tento vzorec:
- Kliknutím na tlačítko Format otevřete dialogové okno. Klikněte na kartu Vyplnit a vyberte jinou barvu než v předchozí sadě kroků.
- Kliknutím na OK zavřete dialogová okna a vraťte se do listu.
Barva pozadí buňky B3 by měla zůstat stejná, což znamená, že procentuální rozdíl mezi čísly v buňkách A3 aB3 je větší než 25 procent, ale menší nebo rovno 50 procentům. Barva pozadí buňky B5 by se měla změnit na novou barvu, kterou jste vybrali, což znamená, že procentuální rozdíl mezi čísly v buňkách A5 a B5 je větší než 50 procent.
Kontrola pravidel podmíněného formátování
Abychom si ověřili, že zadaná pravidla podmíněného formátování jsou správná, můžeme do buněk C2:C5 zadat vzorce, které vypočítají přesný procentuální rozdíl mezi čísly v rozsazíchA2:A5 a B2:B5.
Vzorec v buňce C2 vypadá takto:
=(A2-B2)/A2
- Kliknutím na buňku C2 z ní uděláte aktivní buňku.
- Zadejte výše uvedený vzorec a stiskněte klávesu Enter na klávesnici.
- Odpověď 10 % by se měla objevit v buňce C2, což znamená, že číslo v buňce A2 je o 10 % větší než číslo v buňka B2.
- Pro zobrazení odpovědi v procentech může být nutné změnit formátování buňky C2.
- Pomocí fill handle zkopírujte vzorec z cell C2 do cells C3 do C5.
- Odpovědi pro buňky C3 až C5 by měly být 30 %, 25 % a 60 %.
Odpovědi v těchto buňkách ukazují, že pravidla podmíněného formátování jsou přesná, protože rozdíl mezi buňkami A3 a B3 je větší než 25 procent a rozdíl mezi buňkami A5 a B5 je větší než 50 procent.
Buňka B4 nezměnila barvu, protože rozdíl mezi buňkami A4 a B4 se rovná 25 procent a naše pravidlo podmíněného formátování uvádělo, že pro změnu barvy pozadí bylo vyžadováno procento větší než 25 procent.
Přednostní pořadí pro podmíněné formátování
Když použijete více pravidel na stejný rozsah dat, Excel nejprve určí, zda jsou pravidla v konfliktu. Konfliktní pravidla jsou ta, kde nelze obě možnosti formátování použít na stejná data.
V našem příkladu jsou pravidla v rozporu, protože obě používají stejnou možnost formátování – změnu barvy pozadí buňky.
V situaci, kdy platí druhé pravidlo (rozdíl v hodnotě je mezi dvěma buňkami větší než 50 procent), platí i první pravidlo (rozdíl v hodnotě větší než 25 procent).
Vzhledem k tomu, že buňka nemůže mít obě dvě různé barvy pozadí současně, Excel potřebuje vědět, které pravidlo podmíněného formátování by měl použít.
Pořadí priority v Excelu uvádí, že pravidlo, které je výše v seznamu v dialogovém okně Správce pravidel podmíněného formátování, se použije jako první.
Jak je znázorněno na obrázku výše, druhé pravidlo použité v tomto tutoriálu je v seznamu výše, a proto má přednost před prvním pravidlem. V důsledku toho je barva pozadí buňky B5 zelená.
Ve výchozím nastavení jsou nová pravidla na začátku seznamu; pro změnu pořadí použijte tlačítka se šipkami Nahoru a Dolů v dialogovém okně.
Uplatnění nekonfliktních pravidel
Pokud nejsou dvě nebo více pravidel podmíněného formátování v rozporu, použijí se obě, když se podmínka, kterou každé pravidlo testuje, stane pravdivou.
Pokud by první pravidlo podmíněného formátování v našem příkladu formátovalo rozsah buněk B2:B5 s oranžovým okrajem místo oranžové barvy pozadí, dvě pravidla podmíněného formátování by nefungovala konflikt, protože oba formáty lze použít, aniž by narušovaly ten druhý.
Podmíněné formátování vs. běžné formátování
V případě konfliktů mezi pravidly podmíněného formátování a ručně použitými možnostmi formátování má pravidlo podmíněného formátování vždy přednost a bude použito namísto jakýchkoli ručně přidaných možností formátování.
