Použití vzorců pro podmíněné formátování v aplikaci Excel

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.

Aplikace Excel má ř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.

01 ze dne 04

Použití více pravidel v aplikaci Excel

Na stejná data lze použít více pravidel pro testování různých podmínek. Například údaje o rozpočtu mohou mít nastavené podmínky, které se používají při změnách formátování, pokud jsou vyčerpány určité úrovně, například 50%, 75% a 100% 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.

Hledání dat, která překračuje 25% a 50%, se zvyšuje

V následujícím příkladu se na rozsah použijí dvě vlastní pravidla podmíněného formátování buňky B2 na B5.

• První pravidlo kontroluje, jestli jsou data v souboru buňky A2 na A5 je větší než odpovídající hodnota v B2 na B5 o více než 25%.
• Druhé pravidlo kontroluje, zda nejsou shodné údaje v A2: A5 překročí 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 změní se.

• U dat, u kterých je rozdíl větší než 25%, barva pozadí buňky se 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í podmíněného formátování Nové pravidlo pro formátování dialogové okno. Začněte zadáním vzorových dat do buněk A1 až C5, jak je vidět na obrázku výše

V třetí krok tutoriálu přidáte vzorce do buněk C2: C4 které ukazují přesný procentní rozdíl mezi hodnotami v buňkách A2: A5 a B2: B5 aby bylo možné zkontrolovat přesnost podmíněných formátovacích pravidel.

02 z 04

Nastavení podmíněných formátovacích pravidel

Jak bylo uvedeno, podmínky podmíněného formátování, které kontrolují dvě podmínky, budou zadány pomocí podmíněného formátování Nové pravidlo pro formátování dialogové okno. Začněme nastavením vlastního podmíněného formátování:

Podmíněné formátování pro nalezení vyššího než 25% zvýšení

= (A2-B2) / A2> 25%

1. Zvýraznit buňky B2 na B5 v listu.
2. Klikněte na tlačítko Karta Domovská stránka z stuha.
3. Klikněte na tlačítko Podmíněné formátování ikona v stuha otevřete rozbalovací nabídku.
4. Vybrat Nové pravidlo k otevření Nové pravidlo pro formátování dialogové okno.
5. Z Styl: zaškrtněte políčko Klasický volba.
6. Ve druhém rozevíracím seznamu klikněte na poslední možnostPomocí vzorce určete, které buňky chcete formátovat.
7. Zadejte příkaz vzorec poskytnutých výše do poskytnutého prostoru.
8. Klikněte na tlačítko Formát s: rozbalovací nabídka pak vyberte položku Světlá červená výplň s tmavě červeným textem volba.
9. Klikněte na tlačítko OK zavřete dialogová okna a vraťte se do pracovního listu.
10. V tomto okamžiku je barva pozadí buňky B3 a B5 by měla být zelená.

Podmíněné formátování k nalezení vyššího než 50% zvýšení

= (A2-B2) / A2> 50%

Zvýraznit buňky B2 na B5 v listu.

1. Klikněte na tlačítko Karta Domovská stránka z stuha.
2. Klikněte na tlačítko Podmíněné formátování ikona v stuha otevřete rozbalovací nabídku.
3. Vybrat Nové pravidlo k otevření Nové pravidlo pro formátování dialogové okno.
4. Z Styl: zaškrtněte políčko Klasický volba.
5. Ve druhém rozevíracím seznamu klikněte na poslední možnostPomocí vzorce určete, které buňky chcete formátovat.
6. Zadejte příkaz vzorec poskytnutých výše do poskytnutého prostoru.
7. Klikněte na tlačítko Formát s: rozbalovací nabídka pak vyberte položku Světle zeleně naplňte tmavě zeleným textem volba.
8. Klikněte na tlačítko OK zavřete dialogová okna a vraťte se do pracovního listu.

Barva pozadí buňka B3 by měla být stále zelená, což znamená, že procentní rozdíl mezi čísly v buňky A3 a B3 je větší než 25 procent, ale menší nebo rovné 50 procent. Barva pozadí buňka B5 by se měla změnit na červenou, což znamená, že procentní rozdíl mezi čísly v buňky A5 a B5 je větší než 50 procent.

03 ze dne 04

Kontrola podmíněných formátovacích pravidel

Chcete-li zkontrolovat, zda jsou zadaná pravidla podmíněného formátování správná, můžeme do buněk vložit vzorce C2: C5 , který vypočítá přesný procentní rozdíl mezi čísly v rozmezích A2: A5 a B2: B5.

= (A2-B2) / A2

1. Klikněte na buňka C2 aby byla aktivní buňka.
2. Zadejte výše uvedený vzorec a stiskněte tlačítko Zadejte na klávesnici.
3. 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ž číslo v souboru buňka B2.
4. Může být nutné změnit formátování buňka C2 aby se odpověď zobrazila jako procento.
5. Použijte vyplňte rukojeť zkopírovat vzorec z buňka C2 na buňky C3 na C5.
6. 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 vytvořená podmíněná pravidla pro formátování jsou správná, protože je 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ěnila 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í specifikovalo, že pro změnu barvy pozadí na zelenou je požadováno procento větší než 25 procent.

04 ze dne 04

Pořadí přednosti pro podmíněné formátování

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 procent mezi dvěma buňkami), je pravda také první pravidlo (rozdíl v hodnotě větší než 25 procent).

Excel řádu přednosti

Vzhledem k tomu, že buňka nemůže mít zároveň červené a zelené 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 výukovém programu 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í priority, použijte Nahoru a dolů šipky 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í pravidlo podmíněného formátování v našem příkladu naformátuje rozsah buněk B2: B5 se zeleným rámečkem namísto zelené barvy pozadí, dvě podmíněné formátovací pravidla by nebyly v rozporu, protože oba formáty lze použít bez toho, aby se rušily s ostatními.

Jako výsledek, buňka B5 by měla zelenou hranici a červenou barvu pozadí, protože rozdíl mezi čísly v buňky 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 původně použita barva žlutého pozadí buňky B2 na B5 v příkladu, jakmile jsou přidána pravidla podmíněného formátování, pouze buňky B2 a B4 zůstane žlutá. Protože se na buňkách vztahují zadaná pravidla podmíněného formátování B3 a B5, jejich barvy pozadí by se změnily ze žluté na zelenou a červenou.