Přidání podmíněného formátování v aplikaci Excel umožňuje použít různé možnosti formátování na buňku nebo oblast buněk, které splňují konkrétní podmínky, které nastavíte. Nastavení takových podmínek může pomoci uspořádat tabulku a usnadnit skenování. Možnosti formátování, které můžete použít, zahrnují změny barev písma a pozadí, styly písma, ohraničení buněk a přidání formátování čísel k datům. Excel má integrované 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é vytvořit 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.
Uplatnění více podmínek v aplikaci Excel
Na stejná data můžete použít více než jedno pravidlo k testování různých podmínek. Například v rozpočtových datech mohou být nastaveny podmínky, které použijí změny formátování, když je dosaženo určité úrovně výdajů, například 50%, 75% a 100% celkového rozpočtu.
Za takových okolností aplikace Excel nejprve určí, zda jsou různá pravidla v rozporu, a pokud ano, program se řídí stanoveným pořadím priority, aby určil, které pravidlo podmíněného formátování se použije na data.
Nalezení údajů, které přesahují 25% a 50%, se zvyšuje
V následujícím příkladu budou na rozsah použita dvě vlastní pravidla podmíněného formátování buňky B2 na B5.
- První pravidlo kontroluje, zda jsou data v buňky A2: A5 je větší než odpovídající hodnota v B2: B5 o více než 25%.
- Druhé pravidlo kontroluje, zda jsou v něm stejná data A2: A5 přesahuje 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 změní se.
- 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í Nové pravidlo formátování dialogové okno. Začněte zadáním ukázkových dat do buňky A1 na C5 jak je vidět na obrázku výše. Ve finále část tutoriálu přidáme vzorce do buněk C2: C4 které ukazují přesný procentní 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 procentní nárůst.
Funkce bude vypadat takto: = (A2-B2) / A2> 25%
zadejte = „kód“>
-
Zvýraznit buňky B2 na B5 v listu.
-
Klikněte na Karta Domů z stuha.
-
Klikněte na Podmíněné formátování ikona v stuha otevřete rozevírací nabídku.
-
Vybrat Nové pravidlo otevřít Nové pravidlo formátování dialogové okno.
-
Pod Vyberte typ pravidla, klikněte na poslední možnost: Pomocí vzorce určete, které buňky se mají formátovat.
-
Zadejte vzorec uvedeno výše do prostoru níže Hodnoty formátu, kde platí tento vzorec:
-
Klikněte na ikonu Formát tlačítko pro otevření dialogového okna. Klikněte na ikonu Vyplňte kartu a vyberte barvu.
-
Klikněte OK zavřete dialogová okna a vraťte se do listu.
-
Barva pozadí buňky B3 a B5 by se měla změnit na barvu, kterou jste vybrali.
Nyní použijeme podmíněné formátování, abychom našli 50 nebo více procentní nárůst. Vzorec bude vypadat takto:
-
Opakujte prvních pět kroků výše.
-
Zadejte vzorec poskytnuty výše v prostoru níže Hodnoty formátu, kde platí tento vzorec:
-
Klikněte na ikonu Formát tlačítko pro otevření dialogového okna. Klikněte na ikonu Vyplňte kartu a vyberte jinou barvu, než jakou jste použili v předchozí sadě kroků.
-
Klikněte OK zavřete dialogová okna a vraťte se do listu.
Barva pozadí buňka B3 by měl zůstat stejný, což znamená, že procentní rozdíl mezi čísly v buňky A3 a B3 je větší než 25 procent, ale menší nebo rovno 50 procent. Barva pozadí buňka B5 by se měla změnit na novou barvu, kterou jste vybrali, což znamená, že procentuální rozdíl mezi čísly v buňky A5 a B5 je větší než 50 procent.
Kontrola pravidel podmíněného formátování
Abychom ověřili správnost zadaných pravidel podmíněného formátování, můžeme do buněk zadávat vzorce C2: C5 který vypočítá přesný procentní rozdíl mezi čísly v rozsazích A2: A5 a B2: B5.
Vzorec v buňce C2 vypadá takto: = (A2-B2) / A2
zadejte = „kód“>
-
Klikněte na buňka C2 aby se stala aktivní buňkou.
-
Zadejte výše uvedený vzorec a stiskněte Enter na klávesnici.
-
Odpověď 10% by se měla objevit v buňka C2, což znamená, že číslo v buňka A2 je o 10% větší než počet v buňka B2.
-
Může být nutné změnit formátování na buňka C2 zobrazit odpověď v procentech.
-
Použijte vyplnit rukojeť zkopírovat vzorec z buňka C2 na buňky C3 na C5.
-
Odpovědi pro buňky C3 na C5 by měla 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 existuje rozdíl mezi nimi buňky A3 a B3 je větší než 25 procent a rozdíl mezi buňky A5 a B5 je větší než 50 procent.
Buňka B4 nezměnil barvu, protože rozdíl mezi buňky A4 a B4 se rovná 25 procent a naše pravidlo podmíněného formátování určilo, že ke změně barvy pozadí bylo zapotřebí procento větší než 25 procent.
Pořadí priority pro podmíněné formátování
Když použijete více pravidel na stejný rozsah dat, Excel nejprve určí, zda jsou pravidla v rozporu. Konfliktní pravidla jsou ta, u kterých nelze na obě data použít možnosti formátování na stejná data.
V našem příkladu jsou pravidla v konfliktu, protože obě používají stejnou možnost formátování – změnu barvy buňky pozadí. V situaci, kdy platí druhé pravidlo (rozdíl v hodnotě je více než 50 procent mezi dvěma buňkami), platí také první pravidlo (rozdíl v hodnotě je větší než 25 procent). Protože buňka nemůže mít současně dvě různá barevná pozadí, potřebuje Excel vědět, které pravidlo podmíněného formátování má použít. Pořadí priority aplikace Excel uvádí, že se nejprve použije pravidlo, které je v seznamu v dialogovém okně Správce pravidel podmíněného formátování vyšší. Jak je znázorněno na obrázku výše, druhé pravidlo použité v tomto kurzu je v seznamu vyšší, a proto má přednost před prvním pravidlem. Výsledkem je barva pozadí buňka B5 je zelený. Ve výchozím nastavení přejdou nová pravidla na začátek seznamu; Chcete-li změnit objednávku, použijte Nahoru a dolů tlačítka se šipkami v dialogovém okně.
Uplatňování pravidel, která nejsou v rozporu
Pokud dvě nebo více pravidel podmíněného formátování nejsou v konfliktu, použijí se obě, když se splní podmínka, kterou každé pravidlo testuje. Pokud první pravidlo podmíněného formátování v našem příkladu zformátovalo rozsah buněk B2: B5 s oranžovým okrajem namísto oranžové barvy pozadí by dvě pravidla podmíněného formátování nebyla v rozporu, protože oba formáty lze použít bez zásahu do druhého.
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 místo ručně přidaných možností formátování.