V dnešním videu se podíváme na základy podmíněného formátování v Excelu. Podmíněný formát v praxi znamená, že se na vybranou skupinu buněk uplatní formát, který bude splňovat předem definované kritérium. Pokud budou buňky vyhovovat zadanému kritériu, tak se na tyto buňky uplatní formát, který jste si nastavili.
Za pomoci podmíněného formátování můžeme v Excelu formátovat jednotlivé buňky, ale i celé oblasti buněk. Na výběr máme z nepřeberného množství přednastavených možností a barevných kombinací, stejně tak si můžete nastavit svůj vlastní formát. V minulých videích jsme se už věnovali podmíněnému formátu na základě vzorců a podmíněnému formátu v excelových grafech. Dnes se vrátíme k základům podmíněného formátu a ukážeme si, jak jednoduše můžete v Excelu podmíněný formát použít.
Excelový soubor ke stažení
Podmíněné formátování v Excelu
Možnost podmíněného formátování najdeme v Excelu na liště Domů v záložce Styly. Podmíněné formátování se řídí dle hodnoty v buňce, a to buď podle hodnoty číselné a nebo textové. Na výběr máme z nepřeberného množství předvolených stylů. Některé z nich si právě teď ukážeme.
První, co vždy musíte udělat, než podmíněný formát začnete nastavovat, je označit buňky, na které chcete podmíněný formát uplatnit.
Začneme s podmíněným formátováním podle Pravidla zvýraznění buněk.
Větší než
V prvním příkladu máme ve zdrojové tabulce prodejní data několika produktů a máme barevně odlišit produkty, kterých se prodalo více než 20. Označíme si tedy hodnoty (B4:B12) a na kartě Domů vybereme Podmíněné formátování. Zde vybereme možnost Pravidla zvýraznění buněk. Jelikož chceme barevně odlišit hodnoty, kterých se prodalo více než 20, tak vybereme možnost Větší než. Do pole stačí zapsat číselnou hodnotu, v našem případě se jedná o hodnotu 20 a vedle v rozbalovacím seznamu vybrat požadovaný formát.
Na výběr máme z několika přednastavených formátů. Můžeme obarvit buňky na zeleno, pokud splňují kritérium, nebo naopak na červeno nebo na žluto. Pokud si nějakou variantu vyberete, tak ještě před potvrzením se vám formát v buňkách ukáže, takže máte náhled toho, jak data budou vypadat. Pokud vám ani jedna možnost nevyhovuje, tak máte možnost zvolit si vlastní formát. V tomto příkladu jsme chtěli obarvit buňky na žluto, tak můžeme vybrat tento přednastavený formát. Potvrdíme a hodnoty nad 20 jsou obarvené na žluto.
Výhodou podmíněného formátu je to, že je dynamický. Pokud teď v hodnotách změníme některá čísla nad 20, tak se na tyto buňky automaticky podmíněný formát uplatní. Stejné by to bylo, pokud bychom některá čísla změnili na číslo menší než 20. Automaticky se z těchto buněk podmíněný formát smaže.
Větší než, menší než a rovno
V dalším příkladu použijeme ta samá prodejní data, ale tentokrát je naším úkolem obarvit na zeleno hodnoty nad 20, červeně chceme označit hodnoty pod 20 a hodnoty, které se přímo rovnají 20 chceme obarvit na žluto. Znovu bychom označili hodnoty (B16:B24) a vybrali Podmíněné formátování a zde Pravidla zvýraznění buněk. Začneme od hodnot větších než, vybereme větší než a nastavíme jako limit 20 a zde přednastavený formát zelený. Potvrdíme pravidlo a necháme buňky označené a vybereme Podmíněné formátování znovu. Tentokrát vybereme variantu menší než, a nastavíme menší než 20 a barvu červenou. Ještě nám zbývá nastavit formát pro hodnoty, které se rovnají přesně číslu 20. Opět označíme hodnoty a vybereme Podmíněné formátování. Do třetice vybereme Rovná se a nastavíme, že pokud se hodnoty rovnají 20, tak chceme formát žlutý.
Odstranění podmíněného formátu
Pokud chcete z nějakých buněk podmíněný formát odstranit, tak opět vybereme buňky, na které jsme předtím podmíněný formát uplatnili, a na kartě Domů vybereme Podmíněný formát a zde vybereme Vymazat pravidla. Máme na výběr dvě možnosti, buď vymazat pravidlo z vybraných buněk a nebo z celého listu. Vybereme z vybraných buněk a podmíněný formát z buněk zmizí.
Hodnoty mezi
Barevně můžeme rovněž odlišit hodnoty, které jsou mezi stanovenými čísly. Chceme obarvit čísla mezi 20 a 30. Označíme hodnoty ve sloupci a vybereme Podmíněné formátování a Pravidla zvýraznění buněk a možnost Mezi. Jako spodní limit nastavíme hodnotu 20 a jako horní limit nastavíme hodnotu 30. Vybereme třeba zelený formát a potvrdíme. Hodnoty mezi čísly se obarvily nazeleno.
Podmíněné formátování pomocí odkazů na buňky
V podmíněném formátu můžeme využít i odkazy na buňky místo natvrdo stanovených hodnot. Zkusíme to na stejném příkladu. Tentokrát ovšem k obarvení buněk použijeme odkazy na buňky. Hodnoty si napíšeme do buněk vedle. Teď označíme hodnoty ve sloupci a vybereme Podmíněný formát a Pravidla zvýraznění buněk a Mezi. Místo natvrdo nastavených hodnot vybereme odkazy na buňku. To znamená, že klikneme do buňky a vybereme spodní limit. Před buňkou musí být vždy rovná se. Následně klikneme do druhé buňky a označíme horní limit. Teď můžeme vybrat správný formát buněk. Potvrdíme a máme barevně odlišené buňky, které splňují naše kritérium, a to pomocí odkazu na buňky. Pokud teď změníme hodnoty v těchto buňkách, tak se přizpůsobí i podmíněné formátování.
Úprava podmíněného formátování
Každé pravidlo podmíněného formátu můžeme i zpětně upravit. Stačí obarvit buňky a v Podmíněném formátování vybereme Spravovat pravidla. Zde se rozbalí všechna pravidla, která máme na daných buňkách uplatněná. Pokud chceme pravidlo upravit vybereme Upravit pravidlo a pravidlo upravíme. Z tohoto zobrazení máme rovněž možnost pravidlo podmíněného formátu odstranit.
Text, který obsahuje
Podmíněné formátování lze použít i na textové hodnoty. V dalším příkladu máme zvýraznit zeleně produkty, které obsahují slovo tričko. Označíme buňky a vybereme Podmíněné formátování, v Pravidlech zvýraznění buněk vybereme Text, který obsahuje a zde napíšeme text, který hledáme. Pak už stačí jen vybrat formát, kterým si přejete textové hodnoty označit. Tato možnost Text, který obsahuje reaguje i na část textu. Můžeme tedy do příkazového řádku napsat jen kus slova a excel obarví všechny textové hodnoty, které budou kus tohoto slova obsahovat.
Datum připadající na následující dny
Stejně tak můžete podmíněné formátování využít i pro datumové hodnoty. V dalším příkladě máme za úkol barevně odlišit datumy za minulý měsíc. Označíme hodnoty a na kartě Podmíněného formátování vybereme Pravidla zvýraznění buněk a zde Datum připadající na následující dny. Zde máte na výběr datumové hodnoty, na které můžete použít podmíněné formátování. Můžete tak barevně nebo jinak zvýraznit hodnoty připadající na včera, dnes, zítra, za poslední týden nebo příští týden, minulý týden a příští nebo minulý měsíc. My jme chtěli barevně odlišit data za minulý měsíc, vybereme tedy tuto variantu a jako formát vybereme Vlastní formát a buňky, které splňují kritérium obarvíme třeba na modro.
Duplicitní hodnoty
Velmi hojně využívanou variantou podmíněného formátování je i poslední možnost v Pravidlech zvýraznění buněk a tou je možnost barevně obarvit Duplicitní hodnoty. Řekněme, že v listu hodnot potřebujeme rychle zjistit, zda v něm nemáme duplicitní položky. Stačí si hodnoty označit a vybrat variantu Pravidla zvýraznění buněk a zde Duplicitní hodnoty. Tímto se vám velmi jednoduše v seznamu hodnot označí všechny duplicitní položky.
V další sekci si ukážeme podmíněné formátování ze sekce Pravidla pro nejvyšší a nejnižší hodnoty.
Prvních a posledních 10 položek
Pro číselné hodnoty máte v podmíněném formátování nastavené ještě další možnosti. V dalším příkladu chceme barevně zvýraznit pět nejlepších hodnot, tedy těch nejvyšších. Označíme hodnoty, a v Podmíněném formátování vybereme Pravidla pro nejvyšší či nejnižší hodnoty. Zde vybereme prvních 10 položek. Jak vidíte tak název je trochu matoucí, jelikož si zde můžete vybrat kolik prvních položek ve skutečnosti chcete označit. Nejste tak limitování pouze hodnotou deset. My jsme chtěli označit prvních 5, takže místo 10 napíšeme 5 a vybereme příslušný formát, třeba obarvíme buňky na zeleno. Potvrdíme a obarvilo se prvních pět hodnot. Zde je ovšem třeba mít na paměti, že pokud máte v seznamu duplicity, tak se duplicity započítají. Pokud tedy změníme některé hodnoty, tak aby se rovnaly nejvyšší hodnotě, tak se jich ve skutečnosti obarví více. Pokud by naopak v seznamu bylo 5x číslo 60, tedy nejvyšší hodnota, tak se obarví pouze tyto buňky.
Prvních 10 % a Posledních 10 %
Stejně tak můžete odlišovat i položky podle toho, zda spadají do %, které udáte. Řekněme, že byste v dalším příkladě chtěli označit hodnoty, které spadají do prvních 20 % hodnot. Označíme tedy hodnoty a vybereme možnost Pravidla pro nejvyšší nebo nejnižší hodnoty a zde možnost Prvních 10 %. Hledali jsme prvních 20 %, nastavíme tedy prvních 20 %. Potvrdíme. Excel obarvil první čtyři hodnoty, které jsou nejvyšší, a to i přesto, že seznam hodnot není seřazený. Pokud bychom chtěli obarvit nejvyšších 50 % hodnot, tak označíme buňky, vrátíme se k pravidlu pomocí Spravovat pravidlo a změníme hodnotu z 20 % na 50 %, mělo by se tedy obarvit prvních 10 hodnot. Potvrdíme a obarvilo se správně 10 nejvyšších hodnot.
Nad průměrem a Pod průměrem
V dalším příkladu máme skupinu studentů a rádi bychom na zeleno obarvily hodnoty, které jsou nad průměrem dané skupiny a na červeno, které jsou pod průměrem. Označíme hodnoty a v Podmíněném formátování vybereme možnost Pravidla pro nejvyšší nebo nejnižší hodnoty a zde možnost Nad průměrem. Tyto buňky bychom rádi obarvili na zeleno. U této varianty nemusíte průměrnou hodnotu nastavovat, Excel si ji spočítá sám z označených buněk. Můžeme provést kontrolu. Vedle zkusíme spočítat průměrnou hodnotu označených buněk. Použijeme funkci PRŮMĚR, jako hodnota průměru vyšla hodnota 34 a vidíme, že podmíněné formátování obarvilo buňky správně. Stejně tak můžeme obarvit na červeno buňky, které jsou pod průměrem. Podmíněné formátování bude dynamicky reagovat na jakékoliv změny v seznamu hodnot.
Větší nebo rovno
Kromě těchto všech variant vám ještě podmíněné formátování nabízí jiné, trochu skryté možnosti. V dalším příkladě chceme označit modrou barvou hodnoty, které jsou větší nebo rovny hodnotě 50. Pokud označíme hodnoty a najedeme na Podmíněné formátování, tak zde žádnou takovou možnost jako větší nebo rovno neuvidíme. Pro tuto možnost se musíme přepnout do dalších pravidel. Zde vybereme formátovat buňky obsahující a zde dole rozklikneme tento rozbalovací seznam. Jak vidíte, tak další pravidla formátování vám nabízejí o něco širší škálu možností. Najdeme zde tedy variantu větší nebo rovno a jako hodnotu napíšeme 50 a vybereme styl formátu, modrá výplň a potvrdíme.
Prázdné buňky
V dalším příkladu máme seznam hodnot v tabulce a potřebujeme rychle zjistit, zda se v nich někde nenachází prázdná buňka. Pokud ano, tak bychom takovou buňku potřebovali obarvit na červeno, abychom do ní mohli doplnit chybějící hodnotu. Označíme tedy celou oblast hodnot (A148:A161) a v Podmíněném formátování vybereme Další pravidla a zde Formátovat pouze buňky obsahující a zde vybereme Prázdné buňky a jako formát tmavě červenou výplň. Hotovo.
Chybové hodnoty
Ve druhé tabulce chceme ověřit, než ji pošleme kolegovi, že tam nemáme nějaké chybové hodnoty. Označíme proto opět celou sérii hodnot (C165:C174) a v podmíněném formátování vybereme opět Další pravidla a zde Formátovat pouze buňky, které obsahují a v nabídce vybereme chybové hodnoty. A jak vidíme, dvě chybové hodnoty nám proklouzli. Máme teď šanci je opravit, než Excel pošleme dál.
Datové pruhy a barevné škály
Na buňky v Excelu můžeme uplatnit i více vizuální formát. Označíme následující hodnoty (A178:A187) a na kartě Domů vybereme Podmíněné formátování a zde rozklikneme Datové pruhy. Vybereme například zelený pruh a potvrdíme formát. Do buněk se přidaly datové pruhy, které reprezentují číselnou hodnotu v buňce. Pokud nějakou hodnotu změníme, tak se délka pruhu automaticky přizpůsobí.
Řekněme, že bychom v dalším příkladu chtěli hodnoty, které jsou záporné označit žlutými pruhy a hodnoty, které jsou kladné modrými pruhy. Označíme hodnoty (A191:A200), najdeme Podmíněné formátování a vybereme Datové pruhy. Pokud bychom vybrali pouze datový pruh zelený, tak je v Excelu přednastaveno, že se kladné pruhy zobrazí zeleně a záporné pruhy červeně. To jsme ale nechtěli, takže se k podmíněnému formátu datových pruhů vrátíme a vybereme zde Další možnosti. Zde můžeme vybrat Záporná hodnota a osa a nastavit si požadovaný formát. Barvu záporného pruhu jsme chtěli obarvit na žluto, takže vybereme žlutou barvu. Potvrdíme a ještě nastavíme barvu kladného pruhu. Vybereme nějakou světle modrou. Potvrdíme. A máme hodnoty obarvené správně.
Stejně tak můžeme na buňky uplatnit i různé barevné škály. Označíme hodnoty (A203:A213) a vybereme Podmíněný formát a rozklikneme barevné škály. Stejně jako u ostatních pravidel si i zde můžeme vybrat z přednastavených barevných možností a nebo si vybrat vlastní barvy. Pokud chceme barvy změnit, tak pod Barevnými škálami vybereme Další možnosti a v polích minimální a maximální hodnoty si nastavíme vlastní barevnou kombinaci. Potvrdíme a hodnoty máme barevně odlišené.
Podmíněný formát v oficiální excelové tabulce
Na závěr je ještě dobré si říct, jak použít podmíněný formát v oficiální excelové tabulce. Výhodou oficiálních excelových tabulek je, že se v nich jakýkoliv formát, včetně podmíněného formátu, přenese i na nově přidaná data. Nejprve zdrojovou tabulku změníme na excelovou tabulku. Klikneme do zdrojové tabulky a zmáčkneme kombinaci kláves CTRL+T. Tabulka se tím změní na oficiální excelovou tabulku. Teď najedeme myší na záhlaví tabulky u sloupce, na který chceme uplatnit podmíněný formát, až se z kurzoru myši stane černá šipka. Tím označíme záhlaví tabulky a následně vybereme Podmíněný formát a vybereme třeba Barevné škály a jakýkoliv formát. Teď, pokud přidáme do tabulky nové hodnoty, tak se podmíněný formát automaticky uplatní i na nově přidané hodnoty.
Jedna odpověď
😊