Pridanie podmieneného formátovania v Exceli vám umožňuje použiť rôzne možnosti formátovania na bunku alebo rozsah buniek, ktoré spĺňajú špecifické podmienky, ktoré ste nastavili. Nastavenie takýchto podmienok môže pomôcť usporiadať tabuľku a uľahčiť jej skenovanie. Možnosti formátovania, ktoré môžete použiť, zahŕňajú zmeny farby písma a pozadia, štýly písma, okraje buniek a pridanie formátovania čísel k údajom.
Excel má vstavané možnosti pre bežne používané podmienky, ako je hľadanie čísel, ktoré sú väčšie alebo menšie ako konkrétna hodnota alebo hľadanie čísel, ktoré sú nad alebo pod priemernou hodnotou. Okrem týchto prednastavených možností môžete vytvárať aj vlastné pravidlá podmieneného formátovania pomocou vzorcov programu Excel.
Tieto pokyny platia pre Excel 2019, 2016, 2013, 2010 a Excel pre Microsoft 365.
Použitie viacerých podmienok v Exceli
Na testovanie rôznych podmienok môžete na rovnaké údaje použiť viac ako jedno pravidlo. Napríklad údaje o rozpočte môžu mať nastavené podmienky, ktoré aplikujú zmeny formátovania, keď sa dosiahnu určité úrovne výdavkov, ako napríklad 50 %, 75 % a 100 % celkového rozpočtu.
Za takýchto okolností Excel najskôr určí, či sú rôzne pravidlá v konflikte, a ak áno, program sa riadi stanoveným poradím priorít, aby určil, ktoré pravidlo podmieneného formátovania sa má použiť na údaje.
Hľadanie údajov, ktoré presahujú 25 % a 50 % sa zvyšuje
V nasledujúcom príklade sa na rozsah buniek B2 až B5 použijú dve vlastné pravidlá podmieneného formátovania.
- Prvé pravidlo kontroluje, či sú údaje v bunkách A2:A5 väčšie ako zodpovedajúca hodnota v B2:B5 podľa viac ako 25 %.
- Druhé pravidlo kontroluje, či rovnaké údaje v A2:A5 prekračujú zodpovedajúcu hodnotu v B2:B5 o viac ako 50 %.
Ako je vidieť na obrázku vyššie, ak je splnená niektorá z vyššie uvedených podmienok, farba pozadia bunky alebo buniek v rozsahu B1:B4 sa zmení.
- Pre údaje, kde je rozdiel väčší ako 25 %, sa farba pozadia bunky zmení na zelenú.
- Ak je rozdiel väčší ako 50 %, farba pozadia bunky sa zmení na červenú.
Pravidlá použité na vykonanie tejto úlohy budú zadané pomocou dialógového okna Nové pravidlo formátovania. Začnite zadaním vzorových údajov do buniek A1 až C5, ako je vidieť na obrázku vyššie.
V poslednej časti tutoriálu pridáme do buniek C2:C4 vzorce, ktoré ukazujú presný percentuálny rozdiel medzi hodnotami v bunkách A2:A5 a B2:B5; to nám umožní skontrolovať presnosť pravidiel podmieneného formátovania.
Nastavenie pravidiel podmieneného formátovania
Najprv použijeme podmienené formátovanie, aby sme našli 25-percentný alebo viac významný nárast.
Funkcia bude vyzerať takto:
=(A2-B2)/A2>25 %
- Zvýraznite bunky B2 až B5 v pracovnom hárku.
- Kliknite na kartu Domov ribbon.
- Kliknutím na ikonu Podmienené formátovanie v páske otvoríte rozbaľovaciu ponuku.
-
Vyberte Nové pravidlo a otvorí sa dialógové okno Nové pravidlo formátovania.
- V časti Vyberte typ pravidla kliknite na poslednú možnosť: Použite vzorec na určenie, ktoré bunky sa majú formátovať.
- Napíšte vzorec uvedený vyššie do priestoru nižšie Formátujte hodnoty, pri ktorých platí tento vzorec:
- Kliknutím na tlačidlo Format otvoríte dialógové okno. Kliknite na kartu Vyplniť a vyberte farbu.
- Kliknutím na OK zatvoríte dialógové okná a vrátite sa do pracovného hárka.
- Farba pozadia buniek B3 a B5 by sa mala zmeniť na farbu, ktorú ste vybrali.
Teraz použijeme podmienené formátovanie, aby sme našli zvýšenie o 50 percent alebo viac. Vzorec bude vyzerať takto:
- Zopakujte prvých päť krokov vyššie.
- Napíšte vzorec do nižšie uvedeného priestoru Formátujte hodnoty, pri ktorých platí tento vzorec:
- Kliknutím na tlačidlo Format otvoríte dialógové okno. Kliknite na kartu Vyplniť a vyberte si inú farbu ako v predchádzajúcej skupine krokov.
- Kliknutím na OK zatvoríte dialógové okná a vrátite sa do pracovného hárka.
Farba pozadia bunky B3 by mala zostať rovnaká, čo znamená, že percentuálny rozdiel medzi číslami v bunkách A3 aB3 je väčšie ako 25 percent, ale menšie alebo rovné 50 percentám. Farba pozadia bunky B5 by sa mala zmeniť na novú farbu, ktorú ste vybrali, čo znamená, že percentuálny rozdiel medzi číslami v bunkách A5 a B5 je väčšie ako 50 percent.
Kontrola pravidiel podmieneného formátovania
Na overenie správnosti zadaných pravidiel podmieneného formátovania môžeme do buniek C2:C5 zadať vzorce, ktoré vypočítajú presný percentuálny rozdiel medzi číslami v rozsahochA2:A5 a B2:B5.
Vzorec v bunke C2 vyzerá takto:
=(A2-B2)/A2
- Kliknutím na bunka C2 sa stane aktívnou bunkou.
- Napíšte vyššie uvedený vzorec a stlačte kláves Enter na klávesnici.
- Odpoveď 10 % by sa mala objaviť v bunke C2, čo znamená, že číslo v bunke A2 je o 10 % väčšie ako číslo v bunka B2.
- Možno bude potrebné zmeniť formátovanie bunky C2, aby sa odpoveď zobrazila ako percento.
- Pomocou fill handle skopírujte vzorec z cell C2 do cells C3 do C5.
- Odpovede pre bunky C3 až C5 by mali byť 30 %, 25 % a 60 %.
Odpovede v týchto bunkách ukazujú, že pravidlá podmieneného formátovania sú presné, pretože rozdiel medzi bunkami A3 a B3 je väčší ako 25 percent a rozdiel medzi bunkami A5 a B5 je väčší ako 50 percent.
bunka B4 nezmenila farbu, pretože rozdiel medzi bunkami A4 a B4 sa rovná 25 percent a naše pravidlo podmieneného formátovania špecifikovalo, že na zmenu farby pozadia bolo potrebné percento vyššie ako 25 percent.
Prednostné poradie pre podmienené formátovanie
Keď použijete viacero pravidiel na rovnaký rozsah údajov, Excel najprv určí, či sú pravidlá v rozpore. Konfliktné pravidlá sú pravidlá, pri ktorých nemožno obe možnosti formátovania použiť na rovnaké údaje.
V našom príklade sú pravidlá v rozpore, pretože obe používajú rovnakú možnosť formátovania – zmenu farby pozadia bunky.
V situácii, keď platí druhé pravidlo (rozdiel v hodnote medzi dvoma bunkami je viac ako 50 percent), platí aj prvé pravidlo (rozdiel v hodnote je väčší ako 25 percent).
Keďže bunka nemôže mať obe dve rôzne farebné pozadia súčasne, Excel potrebuje vedieť, ktoré pravidlo podmieneného formátovania má použiť.
Poradie priority v Exceli uvádza, že pravidlo, ktoré je vyššie v zozname v dialógovom okne Správca pravidiel podmieneného formátovania, sa použije ako prvé.
Ako je znázornené na obrázku vyššie, druhé pravidlo použité v tomto návode je vyššie v zozname, a preto má prednosť pred prvým pravidlom. V dôsledku toho je farba pozadia bunky B5 zelená.
V predvolenom nastavení sú nové pravidlá na začiatku zoznamu; na zmenu poradia použite tlačidlá so šípkami Hore a Dolu v dialógovom okne.
Uplatnenie nekonfliktných pravidiel
Ak dve alebo viaceré pravidlá podmieneného formátovania nie sú v rozpore, obe sa použijú, keď sa podmienka, ktorú každé pravidlo testuje, stane pravdivou.
Ak by prvé pravidlo podmieneného formátovania v našom príklade naformátovalo rozsah buniek B2:B5 s oranžovým orámovaním namiesto oranžovej farby pozadia, dve pravidlá podmieneného formátovania by nefungovali konflikt, pretože oba formáty je možné použiť bez interferencie s druhým.
Podmienené formátovanie vs. bežné formátovanie
V prípade konfliktov medzi pravidlami podmieneného formátovania a manuálne použitými možnosťami formátovania má pravidlo podmieneného formátovania vždy prednosť a použije sa namiesto akýchkoľvek manuálne pridaných možností formátovania.
