Podmienené formátovanie v Exceli je nástroj, ktorý Vám umožní vizuálne zvýrazniť bunky na základe určitých kritérií. Vďaka podmienenému formátovaniu môžete zmeniť farbu, štýl písma alebo orámovanie bunky podľa toho, či hodnota spĺňa vopred nastavené podmienky.
Toto je užitočné pre rýchle prehľadanie údajov a zvýraznenie dôležitých informácií, ako sú nízke hodnoty zásob, odchýlky alebo výsledky nad určitou hranicou.
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ý.
