Podmíněné formátování v Excelu má přednastaveno velké množství možností, které můžete využít. Od datových sloupců, barevných škál, ikon až po přednastavené podmínky. Velmi často se ale stane, že potřebujete použít podmíněné formátování na základě vzorce. A právě dnes si ukážeme, jak použít podmíněné formátování v Excelu spolu se vzorci. Budete tak schopni obarvit hledanou hodnotu, vyznačit celý řádek nebo sloupec a použít komplexnější formátování na základě logických podmínek.
Excelový soubor ke stažení
Podmíněné formátování v Excelu
Ve cvičném Excelu ke stažení máme zdrojovou tabulku s několika společnostmi a jejich tržbami v několika měsících. Navíc máme vedle tabulky rozbalovací seznam se seznamem společností. Budeme se snažit pomocí podmíněného formátování obarvit hledané hodnoty dle zadání.
První pravidlo, které si u podmíněného formátování musíte pamatovat je, že nejprve musíte označit buňky, na které chcete podmíněný formát uplatnit.
Druhé pravidlo u podmíněného formátování je, že musíte vědět, kdy použít relativní a kdy absolutní fixace buněk. Toto je naprosto klíčové. Pokud totiž někdy použijete podmíněné formátování se vzorcem a nevrátí se vám výsledek, jaký jste chtěli, tak v 99 % případů máte špatně zafixované buňky.
Podmíněné formátování ve vzorci píšete vždy z pohledu první buňky. Na rozdíl od klasických excelových vzorců, které po napsání stahujeme dolů nebo přetahujeme do stran, tak v podmíněném formátování toto manuální stahování neděláme. To ovšem neznamená, že se buňky nepohybují. Buňky se pohybují, akorát na pozadí Excelu. Na pozadí totiž Excel porovnává každou buňku ve vyznačené oblasti, zda vyhovuje zadané logické podmínce.
Ukážeme si to na jednotlivých příkladech.
Jak obarvit hledanou hodnotu
V prvním příkladu chceme ve zdrojové tabulce označit společnost, kterou vybereme v rozbalovacím seznamu. Jelikož chceme označit pouze název společnosti v tabulce, tak si označíme pouze sloupec se společnostmi (A4:A12) a na kartě Domů vybereme Podmíněné formátování. Zde vybereme Nové pravidlo a v seznamu vybereme možnost Určit buňky k formátování pomocí vzorce.
V tomto příkladu je naše logická podmínka, že chceme obarvit společnost ve sloupci A4:A12, která se bude rovnat společnosti v buňce rozbalovacího seznamu (F3). V podmíněném formátování musíme vždy začít se znaménkem rovná se. Takže napíšeme rovná se a označíme buňku A4, napíšeme druhé rovná se a označíme buňku F3.
Musíte si představit, co se děje na pozadí Excelu se vzorcem, co jste zadali do podmíněného formátu. Porovnáváme, zda se nějaká z buněk ve sloupci se společnostmi (A4:A12) rovná hodnotě z rozbalovacího seznamu (F3). Potřebujeme tedy, aby se buňka A4 posouvala dolu a nahoru po jednotlivých společnostech. Z toho plyne, že tato buňka musí být zafixována pro sloupec, ale nikoliv pro řádek.
Když v podmíněném formátu pomocí vzorce přímo označíme buňku v sešitu, tak se u podmíněného formátování vždy buňka automaticky zafixuje jak pro sloupec, tak pro řádek. V tomto příkladu ovšem chceme, aby se tato první buňka posouvala směrem dolů, takže to změníme a klávesou F4 ji zafixujeme pouze pro sloupec ($A4). Druhá buňka, která se odkazuje na rozbalovací seznam ale musí být plně zafixována, aby se nám nikam neposunula ($F$3). Tím je podmínka hotová. Teď již stačí jen vybrat požadovaný formát.
Jak obarvit celý řádek v tabulce
V dalším příkladu u zdrojové tabulky zůstaneme a budeme chtít, aby se obarvila nejen společnost, kterou vybereme v seznamu, ale rovnou celý řádek. Tentokrát si musíme označit celou tabulku i s hodnotami (A16:D24), jelikož chceme, aby se obarvil celý řádek. Na kartě Domů vybereme Podmíněné formátování a Nové pravidlo. Vybereme určit formátování podle vzorce. Naše podmínka bude v tomto případě stejná jako v příkladu jak obarvit hledanou hodnotu, dokonce bude i stejně zafixovaná. Tedy $A16=$F$15. Rozdíl mezi podmíněnými formáty je pouze v tom, pro jaké buňky jsme podmíněné formátování vybrali. Pokud si vedle sebe porovnáte tyto dva podmíněné formáty, tak zjistíte, že jediný rozdíl je v tom, pro které buňky podmíněné formátování platí. V prvním příkladu, kde jsme chtěli obarvit hledanou hodnotu, platí podmíněný formát pouze pro buňky A4:A12, kdežto ve druhém případě, kdy chceme obarvit celý řádek, platí podmíněný formát na celou tabulku (A16:D24) a proto se obarví celý řádek.
Jak obarvit buňky do kříže
V dalším příkladu máme kromě rozbalovacího seznamu pro výběr společnosti ještě rozbalovací seznam pro výběr měsíce (F28). Chceme, aby se obarvil nejen řádek s vybranou společností, ale i sloupec s vybraným měsícem. Označíme tedy opět celou tabulku hodnot (A28:D36) a vybereme Podmíněné formátování a Nové pravidlo a formátovat pomocí vzorce. Potřebujeme v tomto případě zapojit dvě podmínky. První podmínkou je, že se společnost ve sloupci (A28:A36) bude rovnat společnosti v rozbalovacím seznamu (F27) nebo že se měsíc v řádku (A27:D27) bude rovnat měsíci, který vybereme v rozbalovacím seznamu (F28).
Stejně jako ve funkci KDYŽ i v podmíněném formátu můžeme použít funkci NEBO. První logickou podmínkou ve funkci NEBO je, že se společnost ve zdrojové tabulce rovná společnosti v prvním rozbalovacím seznamu (A28=F27). Buňka se společností se musí pohybovat v rámci sloupce, takže ji zafixujeme pro sloupec ($A28), buňku rozbalovacího seznamu necháme plně zafixovanou ($F$27). Napíšeme středník a druhá logická podmínka ve funkci NEBO je, že se měsíc v řádku v tabulce rovná měsíci v rozbalovacím seznamu (A27=F28). Měsíc v záhlaví musíme zafixovat pro řádek (A$27), aby se mohl pohybovat v rámci svého záhlaví a buňku rozbalovacího seznamu necháme opět plně zafixovanou ($F$28). Ukončíme závorku a vybereme formát. Potvrdíme.
Jednu věc, kterou si musíte pamatovat je, že pokud chcete obarvovat celý řádek nebo sloupec, tak že musíte začít vzorec vždy v první buňce tabulky. V první logické podmínce ve funkci NEBO chceme, aby se obarvil řádek, musíme se tedy na podmíněné formátování dívat z první buňky v této oblasti, což je buňka A28. Ve druhé podmínce ve funkci NEBO chceme, aby se obarvil celý sloupec. Pokud bychom ale ve druhé logické podmínce začali posuzovat podmínku od ledna, tedy od buňky B27, tak by se nám obarvil špatný sloupec.
Pokud bychom označili ve druhém parametru funkce NEBO buňku B27, tak se místo února označí sloupec s lednem. A je to proto, že s podmíněným formátováním nezačínáte v první buňce, ale uprostřed tabulky. V podmíněném formátování tedy nemůžete začít v polovině tabulky, to by se vám mohly označit špatné řádky nebo sloupce.
Co kdybychom ale nechtěli obarvit celý sloupec a řádek, ale pouze hodnotu, kde řádek protíná sloupec? V tomto případě bychom tedy chtěli obarvit hodnotu 933 pro společnost Coca Cola v měsíci únoru. V tomto případě je podmínka trochu jiná. Zatímco v předcházejícím příkladu byla podmínka, že se buď bude řádek rovnat podmínce nebo sloupec, tak v tomto případě musíme použít funkci A, jelikož hledáme průsečík hodnot a obě podmínky musí platit zároveň. Vybereme tedy opět podmíněné formátování, nové pravidlo a formátovat pomocí vzorce.
První logickou podmínkou ve funkci A je, že se buňka A40 bude rovnat buňce rozbalovacího seznamu (F39), opět zafixujeme buňku A40 pouze pro sloupec ($A40), aby se buňka mohla pohybovat v rámci sloupce se společnostmi. Buňka F39, která se odkazuje na rozbalovací seznam musí být zafixována pro řádek i sloupec ($F$39). Druhá logická podmínka ve funkci A je, že se buňka A39 bude rovnat druhému rozbalovacímu seznamu (F40), přičemž buňka A39 musí být zafixována pro řádek (A$39), jelikož se musí pohybovat v rámci záhlaví, buňka odkazující se na druhý rozbalovací seznam musí být opět zafixována pro řádek i sloupec ($F$40). Vybereme formát a potvrdíme.
Jak obarvit buňky podle jiné hodnoty
V dalším příkladu bychom chtěli označit všechna čísla v tabulce, která jsou vyšší než je jejich průměrná hodnota. Začneme tím, že určíme průměrnou hodnotu. Vedle tabulky napíšeme do nějaké buňky průměr a použijeme funkci PRŮMĚR, kde označíme všechny hodnoty. Průměrná hodnota hodnot v tabulce je tedy 550. Jelikož chceme opět označit všechny hodnoty v tabulce, tak musíme označit celou tabulku hodnot (B52:D60). Na liště vybereme Podmíněné formátování, Nové pravidlo a Formát pomocí vzorce. Napíšeme rovná se a logická podmínka je, pokud je hodnota B52 vyšší než průměrná hodnota v buňce G52.
Jak zafixujeme buňku B52? Představte si, kudy se tato buňka musí v rámci označené oblasti posouvat. Tato buňka musí projít všechny hodnoty v buňkách B52:D60, aby zjistila, jestli je číslo v tabulce větší než průměrná hodnota v buňce G52. Tato buňka se tedy musí být schopná pohybovat v označené oblasti jak dolů, tak do stran. Z toho plyne, že tuto buňku fixovat nemůžeme. Hodnota průměru musí být naopak zafixována jak pro řádek, tak sloupec ($G$52). Vybereme formát a potvrdíme. Stejného efektu byste docílili i kdybyste vybrali v Podmíněném formátování Pravidla zvýraznění buněk a Větší než a zde vybrali buňku průměru. Nebo v Dalších pravidlech > Formátovat pouze hodnoty nad nebo pod průměrem.
Jak obarvit jedinečné hodnoty v seznamu
Podmíněné formátování má v základním nastavení možnost obarvit duplicitní položky. Tuto možnost najdeme na záložce Podmíněné formátování, Pravidla zvýraznění buněk a Duplicitní hodnoty. Velmi jednoduše ale můžete označit i jedinečné hodnoty. V tomto příkladu chceme označit jedinečné hodnoty v tabulce A65:A73. Seznam porovnáváme se seznamem v buňkách C65:C69. Označíme si oba seznamy a na liště vybereme Podmíněné formátování a Pravidla zvýraznění buněk. Zde rozklikneme možnost Další pravidla a najedeme na možnost Formátovat pouze jedinečné nebo duplicitní hodnoty. V rozbalovacím seznamu najdeme možnost vybrat formátovat dle jedinečných hodnot.
Vybereme formát a potvrdíme.
