15 ukážok, aby ste zistili, či sa PF zíde aj vám

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.

Formátovanie v Exceli

Každá bunka sa „skladá“ z dvoch častí:

1. hodnota = to je to, čo je v bunke napísané (napríklad text alebo číslo),
2. formát = to je to, ako bunka vyzerá, jej dizajn (má žlté pozadie, tučné písmo, je orámovaná, číslo je zobrazené vo formáte Mena so znakom € atď.).

Š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í.

Na druhej strane podmienené formátovanie berie ohľad na hodnotu, ktorá je v bunke napísaná. Ak sa táto hodnota zmení, tak sa môže zmeniť aj formát.

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.

Prečo je dobré používať (nielen) podmienené formátovanie?

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.

Praktické ukážky

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é.

Sami si nastavíte podmienku a sami si nastavíte formát, akým sa má bunka formátovať, ak túto podmienku spĺňa.

Zdá sa vám to zbytočné?

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.

Nevýhody podmieneného formátovania

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 🙂

• napríklad je náročné prerobiť podmienené formátovanie na klasické. Ale dá sa to a je to určite oveľa jednoduchšie a rýchlejšie, ako prerábať klasické formátovanie na podmienené, Ale úprimne, PF som na klasické prerábala maximálne 5-krát za posledných 10 rokov. Nie je k tomu ani vážny dôvod, pretože PF je lepšie ako klasické…
• ak neviete, ako na to, tak pri kopírované alebo pridávaní / odstraňovaní riadkov/stĺpcov sa niekedy rozbije rozsah. V kurze sa venujem aj tejto téme, ako si s týmto problémom jednoducho poradiť,
• PF nemá toľko možností ako klasické formátovanie. Nemôžete použiť zarovnanie, nemôžete ním nastaviť typ a veľkosť písma. Ale priznajme si, toto nie je až taká katastrofa, pretože aj tak väčšinou farbíme pozadie bunky alebo písmo. Ako by to vyzeralo, keby máme v stĺpci bunky s rôznym typom písma alebo niečo vľavo a iné na stred? Bola to síce skôr rečníka otázka, ale ak chcete aj odpoveď: Vyzeralo by to hrozne.

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.)

Zíde sa vám to?

Č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.