Podmienené formátovanie v programe Excel: Konečný sprievodca s príkladmi

Podmienené formátovanie je jednou z najjednoduchších, ale najúčinnejších funkcií tabuliek programu Excel.

Ako naznačuje názov, v Exceli môžete použiť podmienené formátovanie, ak chcete zvýrazniť bunky, ktoré spĺňajú zadanú podmienku.

To vám dáva možnosť rýchlo pridať vrstvu vizuálnej analýzy do vašej množiny údajov. Pomocou podmieneného formátovania v programe Excel môžete vytvárať tepelné mapy, zobrazovať rastúce/klesajúce ikony, Harveyho bubliny a mnoho ďalších.

Použitie podmieneného formátovania v programe Excel (príklady)

V tomto návode vám ukážem sedem úžasných príkladov použitia podmieneného formátovania v programe Excel:

• Rýchlo identifikujte duplikáty pomocou podmieneného formátovania v programe Excel.
• V množine údajov zvýraznite bunky s hodnotou vyššou/menšou ako číslo.
• Zvýraznenie horných/dolných hodnôt 10 (alebo 10%) v množine údajov.
• Zvýraznenie chýb/medzier pomocou podmieneného formátovania v programe Excel.
• Vytváranie tepelných máp pomocou podmieneného formátovania v programe Excel.
• Zvýraznite každý N -tý riadok/stĺpček pomocou podmieneného formátovania.
• Vyhľadajte a zvýraznite pomocou podmieneného formátovania v programe Excel.

1. Rýchlo identifikujte duplikáty

Podmienené formátovanie v programe Excel je možné použiť na identifikáciu duplikátov v množine údajov.

Takto to môžete urobiť:

• Vyberte množinu údajov, v ktorej chcete zvýrazniť duplikáty.
• Prejdite na domovskú stránku -> Podmienené formátovanie -> Zvýraznenie pravidiel bunky -> Duplicitné hodnoty.
• V dialógovom okne Duplicitné hodnoty skontrolujte, či je v ľavom rozbaľovacom zozname vybratá možnosť Duplikovať. Formát, ktorý sa má použiť, môžete určiť pomocou pravej rozbaľovacej ponuky. Existujú niektoré existujúce formáty, ktoré môžete použiť, alebo môžete zadať svoj vlastný formát pomocou možnosti Vlastný formát.
• Kliknite na tlačidlo OK.

To by okamžite zvýraznilo všetky bunky, ktoré majú duplikát vo vybranej množine údajov. Vaša množina údajov môže byť v jednom stĺpci, viacerých stĺpcoch alebo v nesúvislom rozsahu buniek.

Pozri tiež: Ultimate Guide to Find and remove Duplicates in Excel.

2. Zvýraznite bunky s hodnotou vyššou/menšou ako číslo

Podmienené formátovanie v Exceli môžete použiť na rýchle zvýraznenie buniek, ktoré obsahujú hodnoty väčšie/menšie ako zadaná hodnota. Napríklad zvýraznenie všetkých buniek s hodnotou predaja nižšou ako 100 miliónov alebo zvýraznenie buniek so značkami menšími, ako je prahová hodnota.

Tu sú kroky, ako to urobiť:

• Vyberte celú množinu údajov.
• Prejdite na domovskú stránku -> Podmienené formátovanie -> Zvýraznenie pravidiel bunky -> Väčšie ako… / Menej ako…
• Na základe toho, ktorú možnosť vyberiete (väčšiu alebo menšiu ako), sa otvorí dialógové okno. Povedzme, že vyberiete možnosť „Väčší ako“. V dialógovom okne zadajte číslo do poľa vľavo. Cieľom je zvýrazniť bunky, ktoré majú číslo vyššie ako toto zadané číslo.

• V rozbaľovacej ponuke vpravo zadajte formát, ktorý sa má použiť na bunky, ktoré spĺňajú podmienku. Existujú niektoré existujúce formáty, ktoré môžete použiť, alebo môžete zadať svoj vlastný formát pomocou možnosti Vlastný formát.
• Kliknite na tlačidlo OK.

To by v súbore údajov okamžite zvýraznilo všetky bunky s hodnotami väčšími ako 5.Poznámka: Ak chcete zvýrazniť hodnoty väčšie ako rovné 5, mali by ste znova použiť podmienené formátovanie s kritériami „Rovná sa“.

Rovnakým postupom je možné zvýrazniť bunky s hodnotou menšou ako zadané hodnoty.

3. Zvýraznenie horných/dolných 10 (alebo 10%)

Podmienené formátovanie v programe Excel dokáže rýchlo identifikovať 10 najlepších položiek alebo 10% najlepších z množiny údajov. Môže to byť užitočné v situáciách, keď chcete v údajoch o predaji rýchlo vidieť najlepších kandidátov podľa skóre alebo hodnôt najlepších ponúk.

Podobne môžete v množine údajov tiež rýchlo identifikovať 10 najnižších položiek alebo 10%.

Tu sú kroky, ako to urobiť:

• Vyberte celú množinu údajov.
• Prejdite na položku Domov -> Podmienené formátovanie -> Pravidlá pre hornú / dolnú časť -> Prvých 10 položiek (alebo %) / Spodných 10 položiek (alebo %).
• Na základe toho, čo vyberiete, sa otvorí dialógové okno. Povedzme, že ste vybrali 10 najlepších položiek, potom by sa otvorilo dialógové okno, ako je uvedené nižšie:
• V rozbaľovacej ponuke vpravo zadajte formát, ktorý sa má použiť na bunky, ktoré spĺňajú podmienku. Existujú niektoré existujúce formáty, ktoré môžete použiť, alebo môžete zadať svoj vlastný formát pomocou možnosti Vlastný formát.
• Kliknite na tlačidlo OK.

To by okamžite zvýraznilo 10 najlepších položiek vo vybranej množine údajov. Všimnite si toho, že to funguje iba pre bunky, ktoré majú v sebe číselnú hodnotu.

Ak máte v súbore údajov menej ako 10 buniek a vyberiete možnosti na zvýraznenie 10 najlepších položiek/10 dolných položiek, všetky bunky sa zvýraznia.

Tu je niekoľko príkladov fungovania podmieneného formátovania:

4. Zvýraznenie chýb/medzier

Ak pracujete s veľkým počtom numerických údajov a výpočtov v programe Excel, budete vedieť, aké dôležité je identifikovať a ošetrovať bunky, ktoré majú chyby alebo sú prázdne. Ak sa tieto bunky použijú v ďalších výpočtoch, mohlo by to viesť k chybným výsledkom.

Podmienené formátovanie v programe Excel vám môže pomôcť rýchlo identifikovať a zvýrazniť bunky, ktoré obsahujú chyby alebo sú prázdne.

Predpokladajme, že máme množinu údajov, ako je uvedené nižšie:

Tento súbor údajov má prázdnu bunku (A4) a chyby (A5 a A6).

Tu sú kroky na zvýraznenie buniek, ktoré sú prázdne alebo obsahujú chyby:

• Vyberte množinu údajov, v ktorej chcete zvýrazniť prázdne bunky a bunky s chybami.
• Prejdite na domovskú stránku -> Podmienené formátovanie -> Nové pravidlo.
• V dialógovom okne Nové pravidlo formátovania vyberte Použiť vzorec na určenie, ktoré bunky chcete formátovať.
• Do poľa v časti „Upraviť popis pravidla“ zadajte nasledujúci vzorec:
  = ALEBO (ISBLANK (A1), ISERROR (A1))
  • Vyššie uvedený vzorec kontroluje všetky bunky pre dve podmienky - či je prázdne alebo nie a či obsahuje chybu alebo nie. Ak je ktorákoľvek z podmienok PRAVDA, vráti PRAVDU.
• Nastavte formát, ktorý chcete použiť na bunky, ktoré sú prázdne alebo obsahujú chyby. Ak to chcete urobiť, kliknite na tlačidlo Formátovať. Otvorí sa dialógové okno „Formát buniek“, kde môžete zadať formát.
• Kliknite na Ok.

To by okamžite zvýraznilo všetky bunky, ktoré sú buď prázdne, alebo obsahujú chyby.

Poznámka: Pri podmienenom formátovaní nemusíte vo vzorci používať celý rozsah A1: A7. Vyššie uvedený vzorec používa iba A1. Keď použijete tento vzorec na celý rozsah, Excel skontroluje jednu bunku naraz a upraví referenciu. Keď napríklad kontroluje A1, použije vzorec = ALEBO (ISBLANK (A1), ISERROR (A1)). Keď skontroluje bunku A2, použije vzorec = OR (ISBLANK (A2), ISERROR (A2)). Automaticky upraví referenciu (pretože ide o relatívne referencie) podľa toho, ktorá bunka sa analyzuje. Preto nemusíte pre každú bunku písať samostatný vzorec. Excel je dosť chytrý na to, aby sám zmenil odkaz na bunku 🙂

Pozri tiež: Použitie IFERROR a ISERROR na spracovanie chýb v programe Excel.

5. Vytváranie tepelných máp

Tepelná mapa je vizuálna reprezentácia údajov, kde farba predstavuje hodnotu v bunke. Môžete napríklad vytvoriť tepelnú mapu, kde je bunka s najvyššou hodnotou zafarbená na zeleno a pri znižovaní hodnoty dochádza k posunu k červenej farbe.

Niečo, ako je uvedené nižšie:

Vyššie uvedený súbor údajov má hodnoty od 1 do 100. Bunky sú zvýraznené na základe hodnoty v nich. 100 dostane zelenú farbu, 1 dostane červenú farbu.

Tu sú kroky na vytvorenie tepelných máp pomocou podmieneného formátovania v programe Excel.

• Vyberte množinu údajov.
• Prejdite na položku Domov -> Podmienené formátovanie -> Farebné škály a vyberte jednu z farebných schém.

Hneď ako kliknete na ikonu tepelnej mapy, použije sa na množinu údajov formátovanie. Môžete si vybrať z niekoľkých farebných prechodov. Ak nie ste spokojní s existujúcimi možnosťami farieb, môžete vybrať viac pravidiel a zadať požadovanú farbu.

Poznámka: Podobným spôsobom môžete použiť aj sady Data Bard a Icon.

6. Zvýraznite každý ďalší riadok/stĺpec

Ak chcete zvýšiť čitateľnosť údajov, možno budete chcieť zvýrazniť alternatívne riadky.

Hovorí sa im zebra a môžu byť obzvlášť nápomocné pri tlači údajov.

Teraz existujú dva spôsoby, ako vytvoriť tieto zebra. Najrýchlejším spôsobom je previesť vaše tabuľkové údaje na tabuľku programu Excel. Na alternatívne riadky automaticky použilo farbu. Viac si o tom môžete prečítať tu.

Ďalším spôsobom je použitie podmieneného formátovania.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:

Tu sú kroky na zvýraznenie alternatívnych riadkov pomocou podmieneného formátovania v programe Excel.

• Vyberte množinu údajov. Vo vyššie uvedenom príklade vyberte A2: C13 (bez hlavičky). Ak chcete zahrnúť aj hlavičku, vyberte celý súbor údajov.
• Otvorte dialógové okno Podmienené formátovanie (Domov-> Podmienené formátovanie-> Nové pravidlo) [Klávesová skratka - Alt + O + D].
• V dialógovom okne vyberte „Použiť vzorec na určenie, ktoré bunky sa majú formátovať“.
• Do poľa v časti „Upraviť popis pravidla“ zadajte nasledujúci vzorec:
  = ISODD (RIADOK ())

• Vyššie uvedený vzorec skontroluje všetky bunky a ak je riadkové číslo bunky nepárne, vráti hodnotu TRUE. Zadaný podmienený formát by sa použil na všetky bunky, ktoré vracajú hodnotu TRUE.
• Nastavte formát, ktorý chcete použiť na bunky, ktoré sú prázdne alebo obsahujú chyby. Ak to chcete urobiť, kliknite na tlačidlo Formátovať. Otvorí sa dialógové okno „Formát buniek“, kde môžete zadať formát.
• Kliknite na tlačidlo OK.

To je všetko! Alternatívne riadky v súbore údajov sa zvýraznia.

V mnohých prípadoch môžete použiť rovnakú techniku. Všetko, čo musíte urobiť, je použiť príslušný vzorec v podmienenom formátovaní. Tu je niekoľko príkladov:

• Zvýraznite alternatívne párne riadky: = ISEVEN (ROW ())
• Zvýraznite alternatívne riadky pridávania: = ISODD (ROW ())
• Zvýraznite každý 3. riadok: = MOD (ROW (), 3) = 0

7. Vyhľadajte a zvýraznite údaje pomocou podmieneného formátovania

Toto je trochu pokročilé používanie podmieneného formátovania. Vyzerali by ste ako excelentná rocková hviezda.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, s názvom produktu, obchodným zástupcom a geografiou. Cieľom je napísať reťazec do bunky C2 a ak sa zhoduje s údajmi v akejkoľvek bunke (bunkách), malo by sa to zvýrazniť. Niečo, ako je uvedené nižšie:

Nasledujú kroky na vytvorenie tejto funkcie vyhľadávania a zvýraznenia:

• Vyberte množinu údajov.
• Prejdite na domovskú stránku -> Podmienené formátovanie -> Nové pravidlo (Klávesová skratka - Alt + O + D).
• V dialógovom okne Nové pravidlo formátovania vyberte možnosť „Na základe vzorca určiť, ktoré bunky sa majú formátovať“.
• Do poľa v časti „Upraviť popis pravidla“ zadajte nasledujúci vzorec:
  = AND ($ C $ 2 ””, $ C $ 2 = B5)
• Nastavte formát, ktorý chcete použiť na bunky, ktoré sú prázdne alebo obsahujú chyby. Ak to chcete urobiť, kliknite na tlačidlo Formátovať. Otvorí sa dialógové okno „Formát buniek“, kde môžete zadať formát.
• Kliknite na tlačidlo OK.

To je všetko! Keď teraz zadáte čokoľvek do bunky C2 a stlačíte kláves Enter, zvýraznia sa všetky zodpovedajúce bunky.

Ako to funguje?

Vzorec použitý v podmienenom formátovaní vyhodnotí všetky bunky v množine údajov. Povedzme, že zadáte Japonsko do bunky C2. Teraz Excel vyhodnotí vzorec pre každú bunku.

Vzorec by vrátil bunke PRAVDU, ak sú splnené dve podmienky:

• Bunka C2 nie je prázdna.
• Obsah bunky C2 sa presne zhoduje s obsahom bunky v množine údajov.

Preto sa zvýraznia všetky bunky, ktoré obsahujú text Japonsko.

Stiahnite si ukážkový súbor

Rovnakú logiku môžete použiť aj na vytváranie variácií, ako napríklad:

• Zvýraznite celý riadok namiesto bunky.
• Zvýraznite, aj keď dôjde k čiastočnej zhode.
• Pri zadávaní textu zvýraznite bunky/riadky (dynamické) [Tento trik si zamilujete :)].

Ako odstrániť podmienené formátovanie v programe Excel

Po použití zostane podmienené formátovanie na mieste, pokiaľ ho neodstránite ručne. Osvedčený postup je, aby podmienené formátovanie platilo iba pre tie bunky, kde to potrebujete.

Pretože je prchavý, môže viesť k pomalému zošitu programu Excel.

Ak chcete odstrániť podmienené formátovanie:

• Vyberte bunky, z ktorých chcete odstrániť podmienené formátovanie.
• Prejdite na domovskú stránku -> Podmienené formátovanie -> Vymazať pravidlá -> Vymazať pravidlá z vybratých buniek.
  • Ak chcete odstrániť podmienené formátovanie z celého pracovného hárka, vyberte položku Vymazať pravidlá z celého hárka.

Dôležité informácie o podmienenom formátovaní v programe Excel

• Podmienené formátovanie v prchavých hodnotách. Môže to viesť k pomalému zošitu. Používajte ho iba v prípade potreby.
• Keď kopírujete bunky, ktoré obsahujú podmienené formátovanie, skopíruje sa aj podmienené formátovanie.
• Ak na rovnakú skupinu buniek použijete viacero pravidiel, všetky pravidlá zostanú aktívne. V prípade akéhokoľvek prekrývania sa dáva prednosť naposledy použitému pravidlu. Poradie však môžete zmeniť zmenou poradia v dialógovom okne Spravovať pravidlá.