Šurina Michal - logo

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.

Podmienené formátovanie v Exceli

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ť?

  1. Označíme oblasť / bunky, kde sa nachádzajú hodnoty, ktoré chceme naformátovať
  2. Klikneme na karte​​ Domov​​ na nástroj​​ Podmienené formátovanie
  3. 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
Podmienené formátovanie - 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ý.

Podmienené formátovanie - Nastavenie

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
Údajové pruhy a podmienené formátovanie

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ú
Farebné škály v podmienenom formátovaní

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 upraviť a odstrániť podmienené formátovanie

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:

  1. Označíme si celú tabuľku, môžeme aj s hlavičkou
  2. V podmienenom formátovaní treba zvoliť možnosť Nové pravidlo
  3. 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
  4. 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”
  5. 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
  6. Čiže dolár pred 1 treba zmazať. Musíme nechať dolár iba pred stĺpcom B
Podmienené formátovanie - Nové pravidlá

Ak bude Bratislavská pobočka (BA) v stĺpci B. Riadok bude zelený.

Podmienené formátovanie cez vzorec
Viac informácií...
Šurina Michal : Excel guru a lektor - Svojim klientom ponúkam 14 ročné vedomosti a know-how s prácou v Exceli a 9 ročné skúsenosti z programovania vo VBA. Naše kurzy a školenia z Excelu a VBA sú primárne zamerané na vzdelávanie firiem, organizácií, podnikov a štátnych alebo neštátnych inštitúcií. Mám dlhoročné skúsenosti s organizovaním školení a kurzov pre jednotlivcov alebo skupiny. Uvedomujem si, že vzdelávanie firemných zamestnancov je od vzdelávania širokej verejnosti odlišné. Preto ku každému klientovi/firme pristupujem individuálne. Zhodnotím Vaše potreby, firemné zameranie a pripravím osnovy presne podľa Vašich potrieb a predstáv.