Podmienené formátovanie v Exceli je veľmi užitočný nástroj, pomocou ktorého sa dajú bunky formátovať práve vtedy, keď spĺňajú nami zadanú podmienku.
Od čoho vznikol aj samotný názov – Podmienené formátovanie, inak povedané formátovanie (t.j. nastavenie určitých farieb a vlastností bunkám a písmu) na základe podmienky.
Čo môžeme nastaviť?
Formátovať sa dá farba textu, výplne, orámovanie alebo samotný formát bunky – eurá, počet desatinných miest a pod.
V Exceli sa tento nástroj nachádza na karte DOMOV.
Kedy sa podmienené formátovanie používa?
- Napríklad v databáze, kde evidujeme faktúry nastavíme podmienené formátovanie tak, aby sa faktúry splatné „dnes“ rozsvietili červenou farbou.
- V tabuľke, kde evidujeme jednotlivé objednávky, vieme pomocou podmieneného formátovania zistiť, či sa v tabuľke nachádzajú duplicity. To znamená, že sa jedna a tá istá objednávka vyskytuje v tabuľke dva a viackrát, čo je nežiadúce.
- V tabuľke s objednávkami by sme chceli nastaviť na zeleno tie objednávky, kde bol nás zisk vyšší ako 30 €. V tabuľke objednávok evidujeme aj Marže v €, čo predstavuje náš zisk.
Potrebujete pomôcť s Excelom?
Ako podmienené formátovanie nastaviť?
- Označíme oblasť / bunky, kde sa nachádzajú hodnoty, ktoré chceme naformátovať
- Klikneme na karte Domov na nástroj Podmienené formátovanie
- Z ponuky vyberieme možnosť, ktorú potrebujeme: Napríklad, ak by sme chceli ceny vyššie ako 30 € zvolíme – Pravidlá zvýrazňovania buniek a následne Väčšie ako
V dialógovom okne nastavíme hodnotu 30 a môžeme použiť jeden z predvolených formátov alebo si vytvoriť vlastný.
Potvrdíme tlačidlom OK a následne sa nám všetky čísla, kde by bola hodnota vyššia ako 30 vyplnia nejakou farbou (naformátujú).
Aké možnosti ponúka vlastný formát?
V ponuke nastavenia vlastného formátu nájdete nasledovné možnosti:
- V skupine Číslo sa nachádzajú možnosti formátovania buniek: eurá, percentá, formáty pre dátumy, čas… vlastné formáty buniek
- V skupine Písmo sa nachádzajú možnosti na zmenu: tučné, kurzíva a farby písma
- V skupine Orámovanie môžete nastaviť: štýl orámovania, farbu orámovania a typ orámovania
- V skupine Výplň sa nachádzajú možnosti na zmenu: farby výplne a štýl výplne (vzorku)
Aké nástroje a možnosti podmienené formátovanie ponúka
Podrobnejšie si všetky nástroje a možnosti, ktoré nám Excel ponúka rozoberieme nižšie. Každému nástroju bude venovaná vlastná časť tohto článku. V tejto časti Vám vymenujeme, na ktoré nástroje sa môžete tešiť.
Pravidlá zvýrazňovania buniek:
- Väčšie ako
- Menšie ako
- Medzi
- Rovná sa
- Text obsahujúce
- Vyskytujúci sa dátum
- Duplicitné hodnoty (Zdvojenie hodnôt)
Pravidlá pre horné a dolné:
- Prvých 10
- Prvých 10%
- Posledných 10
- Posledných 10%
- Nadpriemerom
- Podpriemerom
Ďalšie možnosti, ktoré podmienené formátovanie ponúka sú: Údajové pruhy, Farebné škály, Množiny ikon a Formátovanie na základe vzorcov a funkcií
Rovná sa
Možnosť Rovná sa sa pri podmienenom formátovaní v Exceli používa vtedy, ako potrebujeme naformátovať presné hodnoty (čísla, texty, dátumy, čas…) čiže napríklad úlohy:
- naformátujte všetky objednávky z pobočky Bratislava
- naformátujte všetky čísla, ktoré sa rovnajú 50
- naformátujte záznamy, ktoré boli vykonané 10.1.2021
- naformátujte objednávky vykonané v čase 17:40
- a pod., čiže presné hodnoty
Väčšie ako / Menšie ako
Možnosti Väčšie ako a Menšie ako sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (čísla, dátumy a čas), ktoré sú väčšie alebo menšie ako Vami zadaná hodnota čiže napríklad úlohy:
- naformátujte všetky objednávky, kde sa zarobilo viac ako 100 €
- naformátujte všetky záznamy, ktoré boli spravené po 15.1.2021
- naformátujte všetky objednávky, ktoré boli objednané pre 07:30
Medzi
Možnosť Medzi sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (čísla, dátumy a čas), ktoré sú medzi ako sú Vami zadané hodnoty, čiže napríklad úlohy:
- naformátujte všetky objednávky, kde sa zarobilo 150 až 250 €
- naformátujte všetky záznamy, ktoré boli spravené od 15.1.2021 do 25.1.2021
- naformátujte všetky objednávky, ktoré boli objednané od 07:30 do 16:30
Text obsahujúci
Možnosť Text obsahujúci sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (čísla, texty), ktoré obsahujú v sebe Vami zadané hodnoty, čiže napríklad úlohy:
- naformátujte všetky objednávky, kde mal klient email @gmail.com
- naformátujte všetky záznamy, ktoré boli zaplatené z banky Vymyslená a.s., ktorá má kód 7589
Vyskytujúci sa dátum
Možnosť Vyskytujúci sa dátum sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (dátumy), ktoré patria do Vami zadaných hodnôt, čiže napríklad úlohy:
- naformátujte všetky dátumy, ktoré sú dnes, včera, zajtra, tento týždeň, minulý mesiac a pod.
Duplicitné hodnoty a jedinečné záznamy
Možnosť Duplicitné hodnoty sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (čísla, texty, dátumy a čas), ktoré sa vo Vami zvolenej oblasti vyskytujú 2 a viac krát, čiže vieme s týmto nástrojom vyriešiť úlohy ako:
- zistite, či sa v tabuľke nachádzajú duplicity, t.j. či sú v tabuľke nejaké objednávky 2x a viac
Nad priemerom / Pod priemerom
Možnosť Nad priemerom / Pod priemerom sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (čísla, dátumy a čas), ktoré sa v nami zvolenej oblasti nachádzajú nad priemerom, alebo pod priemerom, čiže vieme s týmto nástrojom vyriešiť úlohy ako:
- zistite, na ktorých objednávkach sme zarobili nadprimerne / podpriemerne
Pozor na to, že pri riešení takýchto úloh neberte priemer nikdy ako konštantu. To znamená, že ak viete, že priemer je napríklad 10.
Tak nepoužívajte nástroj Väčší ako / Menší ako… pretože priemer sa Vám môže a pravdepodobne aj bude meniť. Stačí, ak prepíšete nejaké hodnoty, odstránite nejaké hodnoty alebo pridáte iné hodnoty (nové) a priemer už nebude 10.
Najvyššie / Najnižšie hodnoty
Možnosť Prvých 10 položiek / Posledných 10 položiek sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (čísla, dátumy a čas), ktoré sa v nami zvolenej oblasti nachádzajú medzi maximálnymi / minimálnymi hodnotami, čiže vieme s týmto nástrojom vyriešiť úlohy ako:
- zistite, ktorých 7 objednávok nám zarobilo najviac
- naformátujte 4 najnižšie cenové ponuky
Údajové pruhy
Možnosť Údajové pruhy sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (čísla) tak, že Excel pridá do buniek „minigraf / škálu“, na ktorej bude vykresľovať danú hodnotu čísla, čiže vieme s týmto nástrojom vyriešiť úlohy ako:
- graficky ukážte, na ktorých objednávkach sme zarobili, a na ktorých sme boli v strate
Farebné škály
Možnosť Farebné škály sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (čísla, dátumy a čas) tak, že Excel nastaví pre najvyššiu hodnotu určitú farbu a nastaví farbu najnižšej hodnote (prednastavená voľba – môžete ju zmeniť) a následne prechádza z jednej farby do druhej. Podľa toho, či čísla klesajú alebo stúpajú. Čiže vieme s týmto nástrojom vyriešiť úlohy ako:
- naformátujte čísla podľa toho, ako stúpajú / klesanú
Množiny ikon
Možnosť Množiny ikon sa pri podmienenom formátovaní v Exceli používajú vtedy, ako potrebujeme naformátovať hodnoty (čísla, dátumy a čas) tak, že Excel nastaví k danej hodnote určitú ikonu, ktorú si vyberie na základe pravidla / rozsahu, ktorý stanovíme. Čiže vieme s týmto nástrojom vyriešiť úlohy ako:
- naformátujte čísla medzi 0 – 50 tak, aby mali zelenú šípku
- čísla od 50 do 150, aby mali žltú šípku
- a čísla nad 150, aby mali pri sebe zelenú šípku
Ako pravidlá vymazať, opraviť alebo upraviť
Možnosť Spravovať pravidlá sa pri podmienenom formátovaní v Exceli používa vtedy, ako potrebujeme pravidlá vymazať, opraviť a upraviť.
Ako si naformátovať celý riadok / stĺpec
Pri náročnejšej práci s tabuľkami sa Vám určite bude hodiť formátovanie celého riadka alebo celého stĺpca. Táto možnosť je super vtedy, ak napríklad nami naformátovaná hodnota sa nachádza v poslednom stĺpci, máme širokú tabuľku… tak pekne vidíme, že je naformátovaný celý riadok.
Postup pre celý riadok:
- Označíme si celú tabuľku, môžeme aj s hlavičkou
- V podmienenom formátovaní treba zvoliť možnosť Nové pravidlo…
- Následne možnosť Použiť vzorec na určenie buniek, ktoré sa majú formátovať, kde treba zadať vzorec alebo funkciu, ktorá vyhovuje zadaným kritériám
- Povedzme, že v oblasti A1 až D500 máme tabuľku. V stĺpci B budeme mať pobočky a chceme všetky BA (Bratislavské) pobočky mať zelenou farbou, tak vzorec by vyzeral nasledovne: =$B1=“BA“
- Pozor na ukotvenia (doláre) chceme, aby sa chcel hýbal smerom dole… čiže išiel do ďalších riadkov, ale potrebujeme, aby zostal v stĺpci B, pretože tam sú pobočky
- Čiže dolár pred 1 treba zmazať. Musíme nechať dolár iba pred stĺpcom B
Ak bude Bratislavská pobočka (BA) v stĺpci B. Riadok bude zelený.
