Podmíněné formátování patří mezi nejpoužívanější nástroje v Excelu. Většina uživatelů ale zná jen barevné škály, datové pruhy nebo zvýraznění nejvyšších hodnot. Ve skutečnosti toho umí mnohem víc. Stačí využít vlastní pravidla založená na funkcích a můžete automaticky obarvovat průniky hodnot, zvýrazňovat změny v datech, označovat VIP zákazníky podle jiného seznamu nebo třeba zvýraznit vždy nejvyšší tržbu pro každý produkt. V tomto videu si ukážeme pět praktických triků, které využijete v reálné práci a díky kterým posunete podmíněné formátování na úplně novou úroveň.
Excelový soubor ke stažení:
Obarvení průniku hodnot
Velmi často potřebujeme obarvit průnik hodnot například v tabulce. K tomu je podmíněné formátování jako dělané. Akorát nemůžeme použít jednu z přednastavených možností, ale musíme použít vlastní pravidlo pro obarvení buněk.
Nevýhoda podmíněného formátování je to, že když chcete použít formátování pomocí funkce, že vám podmíněné formátování nenabízí nápovědu k psaní funkce. Takže je zejména pro začátečníky lepší nejprve si funkci vytvořit v listu a následně, když víte, že funkce funguje, ji nakopírovat do podmíněného formátu.
Takže začneme tvořit funkci. Potřebujeme obarvit průnik hodnot na řádku a ve sloupcích, takže potřebujeme ověřit dvě podmínky, které musí platit zároveň. Takže začneme s funkcí A, ve které ověříme, že se na řádku, a tato buňka bude zafixovaná pro sloupec, rovná hodnota hodnotě, kterou hledáme na řádku. A tato hodnota bude zafixována plně. V podmíněném formátování hraje fixace buněk zásadní roli, takže je potřeba buňky opravdu správně zafixovat.
A jako druhá podmínka bude, že se hodnota v záhlaví, přičemž tato buňka bude zafixovaná pro řádek, rovná hodnotě, kterou v záhlaví hledáme. To je celá podmínka. Když tuto funkci stáhneme dolů a doprava, tak pokud jsme vše udělali správně, tak se vrátí nepravdy a pouze jedna pravda, což je přesně buňka průniku.
=A($A1=$H$2;A$1=$H$3)
Teď když víme, že máme funkci správně, tak ji zkopírujeme a vložíme ji do podmíněného formátu.
Začneme tak jako vždy u podmíněného formátu, a to je, že označíme celou zdrojovou tabulku. Na kartě Domů v sekci Podmíněné formátování vybereme Nové pravidlo. A nastavíme Formátovat pomocí funkce. Zkopírovanou funkci do pole vložíme a nezapomeneme vybrat formát. A řekněme, že buňku obarvíme na modro. Potvrdíme a teď máme obarvený průnik hodnot podle hodnot, které máme na řádku a v záhlaví. Když hodnoty změníme, tak se obarvení buňky samozřejmě přizpůsobí.
Obarvení každého druhého řádku
V dalším příkladu chceme obarvit každý druhý řádek. Samozřejmě bychom mohli tabulku změnit na excelovou tabulku a použít jeden z klasických formátů tabulky. Ale co když z nějakého důvodu nechcete mít zdrojovou tabulku ve formátu excelové tabulky, ale stále se vám líbí barevné zvýraznění každého druhého řádku. Použijte podmíněný formát. Opět nejprve vytvoříme funkci vedle tabulky. Pro podmíněné formátování musíme určit pravdivé a nepravdivé řádky abychom podle toho mohli příslušné řádky obarvit. K tomu použijeme funkci MOD. Ve funkci MOD použijeme funkci ŘÁDEK, kterou necháme prázdnou, tato funkce vrátí pořadové číslo aktuálního řádku v tabulce. A jelikož chceme obarvit každý druhý řádek, tak ve druhém parametru funkce MOD použijeme číslo dva. A nakonec porovnáme, zda se funkce MOD rovná 0. Jelikož funkce MOD vrací zbytky po vydělení, tak pokud se bude jednat o sudý řádek, tak se funkce MOD bude rovnat nula, jelikož sudé číslo po vydělení hodnotou dva vrátí zbytek nula. Ale pokud se bude jednat o lichý řádek, tak bude funkce MOD vracet zbytkové číslo a tím pádem bude výsledkem funkce NEPRAVDA.
=MOD(ŘÁDEK();2)=0
Funkci potvrdíme a stáhneme ji pro celou tabulku a funkce opravdu vrací na střídačku pravdivé a nepravdivé řádky.
Obarvení nejvyšších hodnot
A co kdybyste chtěli v tabulce obarvit nejvyšší hodnotu pro každou položku? V příkladu máme tabulku s produkty a tržbami a chceme zvýraznit pro každý produkt řádek, na kterém se pro tento produkt vyskytuje nejvyšší tržba. Opět si nejprve funkci připravíme vedle tabulky. Začneme s funkcí, která dokáže identifikovat nejvyšší hodnotu na základě podmínky, což je funkce MAXIFS. Ve funkci MAXIFS nejprve označujeme sloupec hodnot, což je sloupec hodnot, který zafixujeme pro sloupec. Následuje oblast kritérií, což je sloupec s produkty, opět zafixovaný pro sloupec a jako poslední kritérium, což je první produkt v tabulce zafixovaný pro sloupec. To je funkce MAXIFS, nicméně pro podmíněné formátování z toho musíme udělat podmínku, takže to porovnáme, zda se celá funkce MAXIFS rovná první hodnotě v tabulce, zafixované pro sloupec. Když funkci potvrdíme a stáhneme dolů, tak vidíme, že se pravda vrací pouze na řádku, kde je pro produkt nejvyšší tržba.
=$B2=MAXIFS($B:$B;$A:$A;$A2)
Obarvení začátku změny
Podmíněné formátování můžete použít i pro obarvení změny v hodnotách. Ve sloupci máme seznam oddělení, které se opakují a my chceme obarvit vždy první výskyt oddělení. Použijeme jednoduché pravidlo. Ověříme, zda se první hodnota na řádku, která je zafixovaná pro sloupec nerovná hodnotě nad touto první hodnotou, v tomto případě buňce v záhlaví, která bude opět zafixovaná pro sloupec. Když tento vzorec stáhneme dolů, tak se při každé změně oddělení vrátí pravda, jelikož se hodnota na současném řádku nerovná předchozí hodnotě, a tak se pozná změna v oddělení.
=$A2<>$A1
Obarvení na základě hodnot v jiné tabulce
Jako poslední si ukážeme, jak můžeme pomocí podmíněného formátu označit hodnoty v tabulce na základě jiné tabulky. V příkladu máme tabulku se zákazníky. A vedle máme seznam VIP zákazníků. V tabulce zákazník chceme obarvit VIP zákazníky podle tohoto seznamu. Opět si nejprve funkci vytvoříme vedle. Začneme s funkcí COUNTIFS, kde v prvním parametru funkce označíme seznam VIP zákazníků, který pevně zafixujeme. A jako druhý parametr kritérium označíme prvního zákazníka v tabulce a tuto buňku zafixujeme pro sloupec. To je celá funkce COUNTIFS, kterou uzavřeme a teď porovnáme, zda je funkce COUNTIFS větší než nula. Funkce COUNTIFS totiž při shodě jmen vrátí jedničku a pokud se jméno v tabulce neshoduje se jménem ve VIP seznamu, tak funkce COUNTIFS vrátí nulu. Proto porovnáváme, zda je funkce COUNTIFS větší než nula.
=COUNTIF($F$2:$F$20;$A3)>0
