Přidání podmíněného formátování v aplikaci Excel vám umožňuje použít různé možnosti formátování na buňku nebo rozsah buněk, které splňují specifické podmínky, které jste nastavili.
Možnosti formátování se používají pouze tehdy, když vybrané buňky splňují tyto nastavené podmínky.
Možnosti formátování, které lze použít, zahrnují změny písem a barvy pozadí, styly písma, hranice buněk a přidání formátování čísel k datům.
Od aplikace Excel 2007 má aplikace Excel řadu vestavěných možností běžně používaných podmínek, jako je nalezení čísel, které jsou větší nebo menší než určitá hodnota, nebo najít čísla, která jsou nad nebo pod průměrnou hodnotou .
Kromě těchto přednastavených možností je také možné vytvořit vlastní pravidla podmíněného formátování pomocí vzorců aplikace Excel pro testování podmínek určených uživatelem.
Použití více pravidel
Na stejná data lze použít více pravidel pro testování různých podmínek. Například data o rozpočtu mohou mít nastavené podmínky, které používají změny formátování, když jsou vyčerpány určité úrovně - například 50%, 75% a 100% - z celkového rozpočtu.
Za takových okolností aplikace Excel nejdříve určí, zda jsou různá pravidla v rozporu, a pokud ano, program se řídí nastaveným pořadím přednosti, aby zjistil, které pravidlo podmíněného formátování se na data použije.
Příklad: Hledání dat, která překračuje 25% a 50%, se zvýší s podmíněným formátováním
V následujícím příkladu se na rozsah buněk B2 až B5 použijí dvě pravidla vlastního podmíněného formátování.
- první pravidlo kontroluje, zda jsou data v buňkách A2 až A5 větší než odpovídající hodnota v B2 až B5 o více než 25%.
- druhé pravidlo kontroluje, zda stejné údaje v A2: A5 překračují odpovídající hodnotu v B2: B5 o více než 50%.
Jak je vidět na obrázku výše, je-li některá z výše uvedených podmínek pravdivá, barva pozadí buňky nebo buňky v rozsahu B1: B4 se změní.
- u dat, kde je rozdíl větší než 25%, barva pozadí buňky se změní na modrou.
- pokud je rozdíl větší než 50%, barva pozadí buňky se změní na červenou.
Pravidla používaná k tomu,
= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%bude zadáno pomocí dialogového okna Formátování podmíněného formátování.
Zadání výukového data
- Zadejte data do buněk A1 až C5, jak je vidět na obrázku výše
Poznámka: Krok 3 tutoriálu přidá vzorce do buněk C2: C4, které ukazují přesný procentní rozdíl mezi hodnotami v buňkách A2: A5 a B2: B5 za účelem kontroly přesnosti podmíněných formátovacích pravidel.
Nastavení pravidel konformního formátování
Jak bylo uvedeno, podmínky podmíněné formátování, které kontrolují dvě podmínky, budou zadány pomocí dialogového okna Formátování podmíněného formátování.
Nastavení podmíněného formátování pro zvýšení nárůstu o více než 25%
- Zvýrazněte buňky B2 až B5 v listu.
- Klikněte na kartu Domov na pásu karet.
- Kliknutím na ikonu Podmíněné formátování v pásu karet otevřete rozbalovací nabídku.
- Zvolte nové pravidlo a otevřete dialogové okno Nové formátovací pravidlo, jak je vidět na obrázku výše.
- V horní polovině dialogového okna klikněte na poslední možnost: Pomocí vzorce určete, které buňky se mají formátovat.
- Ve spodní polovině dialogového okna klepněte na hodnoty formátu, kde je tento vzorec pravdivý: řádek.
- Zadejte požadovaný vzorec : = (A2-B2) / A2> 25%
- Klepnutím na tlačítko Formát otevřete dialogové okno Formát buněk.
- V tomto dialogovém okně klikněte na kartu Vyplnit a vyberte modrou barvu výplně.
- Klepnutím dvakrát na tlačítko OK zavřete dialogová okna a vrátíte se do pracovního listu.
- V tomto okamžiku by měla být barva pozadí buněk B3 a B5 modrá.
Nastavení podmíněného formátování pro zvýšení nárůstu o více než 50%
- Při výběru buněk B2 až B5 opakujte kroky 1 až 6 výše.
- Zadejte požadovaný vzorec: = (A2-B2) / A2> 50%.
- Klepnutím na tlačítko Formát otevřete dialogové okno Formát buněk.
- Klikněte na kartu Výplň a vyberte červenou barvu výplně.
- Klepnutím dvakrát na tlačítko OK zavřete dialogová okna a vrátíte se do pracovního listu .
- Barva pozadí buňky B3 by měla být stále modrá, což znamená, že procentní rozdíl mezi čísly v buňkách A3 a B3 je větší než 25%, ale menší nebo roven 50%.
- Barva pozadí buňky B5 by se měla změnit na červenou, což znamená, že procentní rozdíl mezi čísly v buňkách A5 a B5 je větší než 50%.
Kontrola podmíněných formátovacích pravidel
Výpočet% rozdílu
Chcete-li zkontrolovat, zda jsou zadaná pravidla podmíněného formátování správná, můžete do buněk C2: C5 zadat vzorce, které vypočítají přesný procentní rozdíl mezi čísly v rozmezích A2: A5 a B2: B5.
- Klepnutím na buňku C2 ji vytvoříte jako aktivní buňku.
- Zadejte vzorec = (A2-B2) / A2 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ňce B2.
- Může být nutné změnit formátování na buňce C2, aby se odpověď zobrazila jako procento.
- Použijte popisovač výplně pro zkopírování vzorce z buňky C2 do buněk C3 až C5.
- Odpovědi na buňky C3 až C5 by měly být: 30%, 25% a 60%.
- Odpovědi v těchto buňkách ukazují, že vytvořená podmíněná pravidla pro formátování jsou správná, protože rozdíl mezi buňkami A3 a B3 je větší než 25% a rozdíl mezi buňkami A5 a B5 je větší než 50%.
- Buňka B4 nezměnila barvu, protože rozdíl mezi buňkami A4 a B4 se rovnal 25% a naše pravidlo podmíněného formátování specifikovalo, že pro změnu barvy pozadí na modrou je požadováno procento větší než 25%.
Pořadí přednostních podmínek podmíněných formátovacích pravidel
Použití podmíněných podmíněných formátovacích pravidel
Při použití více pravidel stejného rozsahu dat aplikace Excel nejdříve určí, zda jsou pravidla v rozporu.
Konfliktní pravidla jsou pravidla, která umožňují, aby zvolené možnosti formátování pro každé pravidlo nebyly použity na stejné údaje .
V příkladu použitém v tomto výukovém programu je konflikt pravidel, protože obě pravidla používají stejnou možnost formátování - změnu barvy buňky pozadí.
V situaci, kdy je pravidlo druhé pravdivé (rozdíl v hodnotě je větší než 50% mezi dvěma buňkami), je pravdivé i první pravidlo (rozdíl v hodnotě větší než 25%).
Excel řádu přednosti
Vzhledem k tomu, že buňka nemůže mít současně červené a modré pozadí, aplikace Excel potřebuje vědět, které podmíněné formátovací pravidlo by se mělo použít.
Které pravidlo se použije, určuje pořadí priority aplikace Excel, které uvádí, že pravidlo, které je vyšší v seznamu v dialogovém okně Správce podmíněných formátovacích pravidel, má přednost.
Jak je vidět na obrázku výše, druhé pravidlo použité v tomto tutoriálu (= (A2-B2) / A2> 50%) je v seznamu vyšší a proto má přednost před prvním pravidlem.
Výsledkem je změna barvy pozadí buňky B5 na červenou barvu.
Ve výchozím nastavení jsou nová pravidla přidána do horní části seznamu, a proto mají vyšší prioritu.
Chcete-li změnit pořadí priorit, použijte šipky Nahoru a Dolů v dialogovém okně, jak je uvedeno na obrázku výše.
Použití nekonfliktních pravidel
Pokud se dvě nebo více podmíněných formátovacích pravidel netýkají obou, použije se, když se splní podmínka, kterou každé pravidlo testuje.
Pokud první podmínka podmíněného formátování v našem příkladu (= (A2-B2) / A2> 25%) formátuje rozsah buněk B2: B5 s modrým rámečkem místo modré barvy pozadí, dvě podmíněné formátovací pravidla by nebyly v rozporu oba formáty lze aplikovat bez zasahování do druhého.
V důsledku toho by buňka B5 měla jak modrý okraj, tak červenou barvu pozadí, protože rozdíl mezi čísly v buňkách A5 a B5 je větší než 25 a 50 procent.
Podmíněné formátování vs. řádné formátování
V případě konfliktů mezi podmíněnými pravidly formátování a ručně nastavenými možnostmi formátování má pravidlo podmíněného formátování vždy přednost a použije se místo ručně přidaných formátovacích možností.
Pokud byla v příkladu na buňkách B2 až B5 nejprve použita barva žlutého pozadí, jakmile budou přidána pravidla pro podmíněné formátování, zůstávají pouze buňky B2 a B4 žluté.
Vzhledem k tomu, že zadaná pravidla podmíněného formátování platí pro buňky B3 a B5, barvy jejich pozadí se změní ze žluté na modrou a červenou.