Excel – „K čomu je dobré podmienené formátovanie?“ „Na čo sa dá využiť?“ „Zíde sa aj mne?“ … Koncom januára začne online video-kurz „Excel – podmienené formátovanie“ a možno aj vám napadli podobné otázky.
Na tú poslednú zrovna odpoveď nepoznám, ale ponúknem vám 15 ukážok na inšpiráciu. Pomôžu vám získať prehľad o tom, k čomu sa dá podmienené formátovanie využiť. A potom uvidíte, či sa hodí aj do vašich súborov.
Na úvod trošku teórie, čo to vlastne to formátovanie v Exceli je a v čom sa líši podmienené formátovanie (PF) od klasického.
Každá bunka sa „skladá“ z dvoch častí:
Štandardne k formátovaniu buniek používame ikony na karte Domov, kde si môžete vybrať typ a veľkosť písma, farbu písma alebo bunky, zarovnanie, rôzne formáty čísla a podobne.
Alebo možno máte radšej klik pravým tlačidlom myši a voľbu Formátovať bunky, kde je možností oveľa viac ako na karte Domov.
Je jedno, ktorou cestou pôjdete, výsledok bude rovnaký = upravíte formát bunky bez ohľadu na to, aká je jej hodnota. Ak prepíšete hodnotu v bunke, formát sa nezmení.
Ako už z názvu vyplýva, ide o podmienené formátovanie, to znamená, že zadávame nejakú konkrétnu podmienku, na základe ktorej sa nastavený formát aplikuje.
Napríklad máte bunky s číslami a potrebujete, aby všetky čísla väčšie ako 100 mali žlté pozadie (farbu si sami vyberiete, akú chcete).
Cez kartu Domov a ikonu Podmienené formátovanie (v anglickom: Home > Conditional Formatting) nastavte túto podmienku vo vybraných bunkách a Excel ju bude kontrolovať. Bunky, ktoré podmienku spĺňajú, zafarbí.
Najlepšie na tom je, že keď sa hodnota v bunke zmení (je jedno, či ju prepíšete, alebo je výsledkom vzorca) na číslo menšie alebo rovné ako 100, tak sa odfarbí. Excel teda na pozadí stále kontroluje, či je, alebo nie je podmienka splnená. A podľa toho bunku (ne)naformátuje.
Slovo „nielen“ je uvedené schválne, pretože je dobré používať akékoľvek formátovanie buniek. Dôvodov je veľa: dobre to vyzerá, je v tom lepší prehľad…
Ale najdôležitejšie je to, že ak potrebujete získať komplexný pohľad na údaje alebo niečo rýchlo nájsť, tak skôr si všimnete nejakú farbu alebo symbol, než by ste mali čítať číslo za číslom, text za textom, či je to práve to, čo hľadáte.
Predstavte si, že by na semafore neboli farby, ale bolo by tam napísané: choď, priprav sa, stoj… 🙂 Ani sa nemusím rozpisovať o tom, čo by to spôsobilo. Farby nám ľuďom pomáhajú v lepšej a rýchlejšej orientácii. Naviac podmienené formátovanie tie farby mení automaticky podľa pravidiel, ktoré sme si sami nastavili.
Dosť bolo teórie, poďme na ukážky. Je úplne jedno, o čom sú čísla/texty/dátumy v ukážke, pointa je rovnaká pri akýchkoľvek údajoch.
Namiesto dlhého pomenovania „podmienené formátovanie“ budem používať skratku PF.
1. Na obrázku sú pomocou PF zafarbené zelenou čísla väčšie ako 100. Ak sa ktorékoľvek číslo zmení a bude menšie ako 100, formát sa tiež zmení. Podobne môžete automaticky formátovať aj čísla menšie ako nejaká hranica, alebo medzi.
2. Na obrázku je prehľad predaja v jednotlivých mesiacoch a rokoch (ale v podstate je jedno, čo tie čísla znamenajú). Zelenou farbou je zvýraznená najvyššia hodnota v danom roku a červenou najnižšia.
3. Rovnaký prípad ako predchádzajúci s tým rozdielom, že sú nastavené odtiene modrej. Čím väčšie číslo, tým tmavšia modrá. V tomto prípade sa neporovnávajú hodnoty v každom roku samostatne, ale celé obdobie = všetky čísla. Vy si to samozrejme môžete nastaviť tak, ako potrebujete.
4. Ešte raz tie isté čísla, ale PF teraz farbí tie, ktoré sú menšie ako priemer.
5. PF môžete aplikovať aj na texty. V stĺpci sú zelenou naformátované bunky, ktoré obsahujú Slovensko.
6. Dokonca ho môžete použiť aj na dátumy alebo čas. V ukážke je automaticky zelenou farbený aktuálny dátum a žltou tri nasledujúce dni.
Podľa farieb vidíte, že článok bol napísaný 25.1.2022. Ak by to nebol obrázok, ale reálny excelovský súbor, tak by bol zafarbený aktuálny dátum a tri nasledujúce dni. Každým dňom by sa to vďaka PF menilo a stále by ste mali prehľad o tom, čo spadá do dnešného dňa a čo sa blíži.
7. Táto ukážka tiež zohľadňuje dátumy. Aby ste si nemysleli, že pomocou PF farbíme iba pozadie bunky, tak tu je tučné šikmé červené písmo pri dátumoch, ktoré už boli.
8. Vďaka PF môžete jednoducho nájsť aj opakujúce sa (duplicitné) hodnoty.
9. Okrem formátov, ktoré sú podobné klasickému formátu, PF ponúka aj údajové pruhy a množiny ikon. Na obrázku sú údajové pruhy = čím väčšie číslo, tým väčší obdĺžnik. Taký malý graf v bunke.
10. Ikony sú pomerne obľúbené, pretože sú zaujímavejšie a vyzerajú lepšie, ako „obyčajné“ pozadie bunky. Sú nastavené intervaly a podľa toho, do ktorého intervalu dané číslo patrí, taká ikona sa zobrazí.
11. Ikony sa dajú použiť aj na vizuálne znázornenie nárastu alebo poklesu.
Tiež ide o intervaly, kedy nárast je, ak je rozdiel väčší ako nula, a pokles, ak je rozdiel menší ako nula.
Ak je bez zmeny, tak je rozdiel nula. Toto je taký mini interval s jedným číslom. Ale pre potreby použitia ikon v PF to postačí.
V stĺpci C je vypočítaný aj rozdiel medzi stĺpcami D a B, ale je skrytý. Pri ikonách je možné hodnoty nezobrazovať a nechať viditeľný iba formát = ikony.
12. Pri nastavení podmienky môžete využiť aj vzorce alebo funkcie, ktorých výsledkom je TRUE (pravda) alebo FALSE (nepravda).
Tejto téme je v online video-kurze venovaná celá jedna bonusová časť. Je to v bonusoch, pretože to je nie postup, ktorý je užitočný pre každého. Takže ak to zrovna vás zaujíma, v kurze to nájdete.
Ako príklad sú pomocou PF zafarbené víkendy. Dajú sa takto nastaviť aj sviatky, alebo akékoľvek dátumy, ktoré potrebujete. Ak v bunkách prepíšete dátum alebo rok nahradíte iným rokom, automaticky sa farby zmenia.
13. PF nemusíte aplikovať iba na rozsahy buniek. Môže byť nastavené aj len v jednej bunke.
Povedzme, že v nejakej bunke sa vám niečo počíta = máte tam funkciu napríklad pre výpočet sumy. Do buniek pridávate/meníte hodnoty a táto suma sa prepočítava.
A teraz prichádza na rad PF, ktoré tú sumu kontroluje: ak je do 1000 vrátane, tak je bunka zelená (alebo nezafarbená, alebo akákoľvek chcete). Akonáhle suma prekročí hranicu 1000, tak sa zafarbí červenou farbou. Blikať nebude, to PF nevie, ale aj červená udrie do oka a je jasné, že sme prekročili rozpočet.
14. V tejto ukážke je formulár, v ktorom PF kontroluje prázdne bunky. Jednotlivé bunky, ktoré je potrebné vyplniť, sú červené, aby sa na niečo nezabudlo. Ak do bunky vložíte hodnotu, červená sa zmení na sivú.
15. PF môžete využiť aj trošku sofistikovanejšie 🙂 V ukážke je v bunke J1 rozbaľovací zoznam. Ktorú krajinu vyberiete, ten riadok sa zafarbí. Áno, celý riadok sa naformátuje. Aj toto PF dokáže.
Dokonca môžete formátovať bunky na základe hodnoty v úplne inej bunke.
K obom týmto nastaveniam nájdete vo video-kurze podrobné postupy.
Príkladov by som vám tu mohla dať ešte stovky… Možností, kedy PF využiť, je naozaj nesmierne veľa. Pointa je v tom, že vďaka nemu dokážete nastaviť automatické formátovanie buniek podľa toho, čo je v bunkách vložené.
Pri niektorých príkladoch môžete argumentovať, veď to viem jednoducho nastaviť aj pomocou klasického formátovania. Áno, to je pravda, ale…
a) podmienené formátovanie je rýchlejšie. Ak by ste mali nastaviť v 1000 bunkách zelenou farbou hodnoty väčšie ako 100, tak síce môžete použiť nejakú pomôcku, napr. filter, ale bude to zbytočne dlhšie trvať,
b) PF je dynamické = ak sa hodnota zmení, Excel skontroluje podmienku a ak treba, tak zmení formát.
Aby som PF iba nechválila, tak uvediem aj nejaké nevýhody. Priznám sa, že veľa mi ich na myseľ neprichádza. Asi preto, že ich ani veľa nie je 🙂
Keďže mi toho veľa nenapadlo, tak som googlila. Na viacerých stránkach som našla väčšinou len jednu nevýhodu, a to, že s formátom sa nedá ďalej pracovať vo vzorcoch. Neviete napr. zrátať, koľko je modrých. Toto ale neviete ani pri klasickom formátovaní. Skôr by som povedala, že podmienené formátovanie má v tomto v porovnaní s klasickým výhodu, pretože je založené na nejakej podmienke. A túto podmienku môžete použiť aj vo funkciách, ktoré dokážu s podmienkami pracovať, napr. IF, SUMIF, SUMIFS, COUNTIF, AVERAGEIF, MAXIFS atď.
(Poznámka: štandardné funkcie v Exceli nedokážu pracovať s formátom, iba s hodnotu. Ak by ste potrebovali v Exceli zrátať napr. počet ružových buniek, tak to môžete urobiť pomocou makier.)
Čo vravíte po prezretí ukážok? Hodí sa vám to?
Ak áno a chcete sa o podmienenom formátovaní dozvedieť nielen to, ako funguje v základných nastaveniach, ale chcete vedieť aj to, na čo si dávať pozor, spoznať rôzne fintičky i pokročilé techniky, tak vás pozývam do druhého ročníka online video-kurzu „Excel – podmienené formátovanie“.
Ak ste sa už prihlásili, teším sa na vás. Verím, že sa vám kurz bude páčiť, pretože PF je nielen praktické, ale aj veľmi hravé a zábavné 🙂 Ďakujem za prečítanie tohto článku.
Práve prebieha registrácia do druhého ročníka kurzu.
Cieľom online video-kurzu je, aby ste sa naučili pracovať s jedným z najpoužívanejších a najefektívnejších nástrojov v Exceli, vďaka čomu ho potom môžete jednoducho využiť aj vo vlastných súboroch.
Prístup k videám vám ostane aj po skončení kurzu.