Pridaním podmieneného formátovania v programe Excel môžete použiť rôzne možnosti formátovania na bunku alebo rozsah buniek, ktoré spĺňajú špecifické podmienky, ktoré ste nastavili. Možnosti formátovania sa uplatňujú iba vtedy, ak vybrané bunky spĺňajú tieto nastavené podmienky.
Možnosti formátovania, ktoré je možné použiť, zahŕňajú zmeny farieb písma a farieb, štýly písma, hranice buniek a pridanie formátovania čísel k údajom.
Program Excel mal niekoľko vstavaných možností pre bežne používané podmienky, ako je nájdenie čísel, ktoré sú väčšie alebo menšie ako určitá hodnota alebo nájdenie čísel, ktoré sú nad alebo pod priemernou hodnotou.
Okrem týchto prednastavených možností je tiež možné vytvoriť vlastné pravidlá podmieneného formátovania pomocou vzorcov programu Excel na testovanie podmienok určených používateľmi.
01 z 04Použitie viacerých pravidiel v programe Excel
Na rovnaké údaje je možné použiť viac pravidiel na testovanie rôznych podmienok. Napríklad údaje o rozpočte môžu mať nastavené podmienky, ktoré uplatňujú zmeny formátovania, keď sa vyčerpajú určité úrovne, napríklad 50%, 75% a 100% celkového rozpočtu.
Za takýchto okolností program Excel najprv určuje, či sú rôzne pravidlá v konflikte, a ak áno, program nasleduje po stanovenom poradí priority, aby určil, ktoré pravidlo podmieneného formátovania sa použije na údaje.
Hľadanie údajov, ktoré prekračujú 25% a 50%, sa zvyšuje
V nasledujúcom príklade sa na rozsah použijú dve vlastné pravidlá podmieneného formátovania bunky B2 na B5.
- Prvé pravidlo skontroluje, či sú údaje v buniek A2 na A5 je väčšia ako zodpovedajúca hodnota v B2 na B5 o viac ako 25%.
- Druhé pravidlo kontroluje, či sú rovnaké údaje v A2: A5 presahuje zodpovedajúcu hodnotu v B2: B5 o viac ako 50%.
Ako je možné vidieť na obrázku vyššie, ak je jedna z vyššie uvedených podmienok pravdivá, farba pozadia bunky alebo buniek v rozsahu B1: B4 zmení sa.
- Pri údajoch, 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žívané na vykonanie tejto úlohy sa zadajú pomocou podmieneného formátovania Nové pravidlo pre formátovanie dialógové okno. Začnite zadaním vzorových údajov do buniek A1 až C5, ako vidíte na obrázku vyššie
V tretí krok tutoriálu pridá bunkové vzorce C2: C4 ktoré ukazujú presný percentuálny rozdiel medzi hodnotami v bunkách A2: A5 a B2: B5 aby sa skontrolovala správnosť pravidiel podmieneného formátovania.
02 z 04Nastavenie podmienených pravidiel formátovania
Ako bolo uvedené, podmienky podmieneného formátovania, ktoré kontrolujú dve podmienky, sa zadajú pomocou podmieneného formátovania Nové pravidlo pre formátovanie dialógové okno. Začnime tým, že nastavíme vlastné podmienené formátovanie:
Podmienené formátovanie pre nájdenie väčšieho ako 25% zvýšenie
= (A2-B2) / A2> 25%
- Zlatý klinec bunky B2 na B5 v pracovnom liste.
- Klikni na Domovská karta z stuha.
- Klikni na Podmienené formátovanie ikona v priečinku stuha otvorte rozbaľovaciu ponuku.
- zvoliť Nové pravidlo otvoriť Nové pravidlo pre formátovanie dialógové okno.
- Z Štýl: začiarknite políčko klasický voľba.
- V druhej rozbaľovacej ponuke kliknite na poslednú možnosťPomocou vzorca môžete určiť, ktoré bunky sa majú formátovať.
- Zadajte reťazec vzorec uvedené vyššie na poskytnutom mieste.
- Klikni na Formátovanie pomocou: rozbaľovací zoznam a potom zvoľte položku Svetlo červená výplň s tmavočerveným textom voľba.
- kliknite OK zatvorte dialógové okná a vráťte sa do pracovného hárka.
- V tomto bode je farba pozadia buniek B3 a B5 by mala byť zelená.
Podmienené formátovanie, aby ste našli viac ako 50% nárast
= (A2-B2) / A2> 50%
Zlatý klinec bunky B2 na B5 v pracovnom liste.
- Klikni na Domovská karta z stuha.
- Klikni na Podmienené formátovanie ikona v priečinku stuha otvorte rozbaľovaciu ponuku.
- zvoliť Nové pravidlo otvoriť Nové pravidlo pre formátovanie dialógové okno.
- Z Štýl: začiarknite políčko klasický voľba.
- V druhej rozbaľovacej ponuke kliknite na poslednú možnosťPomocou vzorca môžete určiť, ktoré bunky sa majú formátovať.
- Zadajte reťazec vzorec uvedené vyššie na poskytnutom mieste.
- Klikni na Formátovanie pomocou: rozbaľovací zoznam a potom zvoľte položku Svetlozelená výplň s tmavomodrým textom voľba.
- kliknite OK zatvorte dialógové okná a vráťte sa do pracovného hárka.
Farba pozadia bunka B3 by mala byť stále zelená, čo znamená, že percentuálny rozdiel medzi číslami v buniek A3 a B3 je väčší ako 25%, ale menší alebo rovný 50%. Farba pozadia bunka B5 by sa mala zmeniť na červenú, čo znamená, že percentuálny rozdiel medzi číslami v buniek A5 a B5 je viac ako 50 percent.
03 z 04Kontrola podmienených pravidiel formátovania
Ak chcete skontrolovať, či sú zadané pravidlá podmieneného formátovania správne, môžeme do buniek vkladať vzorky C2: C5 ktorý vypočíta presný percentuálny rozdiel medzi číslami v rozsahu A2: A5 a B2: B5.
= (A2-B2) / A2
- Kliknite na bunka C2 aby bola aktívna bunka.
- Zadajte vyššie uvedený vzorec a stlačte tlačidlo vstúpiť na klávesnici.
- Odpoveď 10% by sa mala objaviť v bunka C2, čo znamená, že číslo v bunka A2 je o 10% väčší ako počet v bunka B2.
- Možno bude potrebné zmeniť formátovanie bunka C2 aby sa odpoveď zobrazila ako percento.
- Použi vyplniť rukoväť skopírujte vzorec z bunka C2 na buniek C3 na C5.
- Odpovede na buniek C3 na C5 by mali byť 30%, 25% a 60%.
Odpovede v týchto bunkách ukazujú, že vytvorené pravidlá podmieneného formátovania sú správne, pretože rozdiel medzi nimi buniek A3 a B3 je väčší ako 25 percent a rozdiel medzi buniek A5 a B5 je viac ako 50 percent.
Bunka B4 nezmenila farbu, pretože rozdiel medzi bunky A4 a B4 sa rovná 25 percentám a naše pravidlo podmieneného formátovania špecifikovalo, že na zmenu farby pozadia na zelenú je potrebné viac ako 25 percent.
04 z 04Poradie prednosti pre podmienené formátovanie
Ak sa na rovnaký rozsah údajov uplatňuje viacero pravidiel, program Excel najprv určuje, či sú pravidlá v konflikte. Konfliktné pravidlá sú tie, v ktorých zvolené možnosti formátovania pre každé pravidlo nemôžu byť použité na rovnaké dáta.
V príklade použitom v tomto výučbe sa konflikt pravidiel používa, pretože obe pravidlá používajú rovnakú možnosť formátovania - zmenu farby buniek pozadia.
V situácii, keď je pravidlo druhé pravdivé (rozdiel v hodnote je väčší ako 50 percent medzi dvoma bunkami), potom je pravda aj pravidlo prvé (rozdiel v hodnote je väčší ako 25 percent).
Excel Order of Precedence
Pretože bunka nemôže mať súčasne červené a zelené pozadie, program Excel potrebuje vedieť, ktoré pravidlo podmieneného formátovania by malo platiť.
Ktoré pravidlo sa použije určuje poradie priority programu Excel, v ktorom sa uvádza, že pravidlo, ktoré je vyššie v zozname v dialógovom okne Podmienené riadenie pravidiel formátovania, má prednosť.
Ako je uvedené na obrázku vyššie, druhé pravidlo použité v tomto výučbe je vyššie v zozname, a preto má prednosť pred prvým pravidlom. Výsledkom je zmena farby pozadia bunky B5 na červenú.
V predvolenom nastavení sa do hornej časti zoznamu pridávajú nové pravidlá, a preto majú vyššiu prioritu. Ak chcete zmeniť poradie priority, použite Hore a dole šípky v dialógovom okne, ako je uvedené na obrázku vyššie.
Uplatňovanie pravidiel bez konfliktu
Ak sa dve alebo viac pravidiel podmieneného formátovania netýkajú obidvoch, použije sa, keď sa splní podmienka, ktorú každé pravidlo testuje.
Ak prvé pravidlo podmieneného formátovania v našom príklade formátovalo rozsah buniek B2: B5 so zelenou hranicou namiesto zelenej farby pozadia, dve podmienené pravidlá formátovania by neboli v konflikte, pretože oba formáty môžu byť použité bez toho, aby sa rušili s druhým.
Ako výsledok, bunka B5 by mali zelenú hranicu a červenú farbu pozadia, pretože rozdiel medzi číslami v buniek A5 a B5 je väčšia ako 25 a 50 percent.
Podmienené formátovanie vs. pravidelné formátovanie
V prípade konfliktov medzi pravidlami podmieneného formátovania a ručne nastavenými možnosťami formátovania má pravidlo podmieneného formátovania vždy prednosť a bude použité namiesto ručne pridaných možností formátovania.
Ak bola na začiatku aplikovaná žltá farba pozadia bunky B2 na B5 v príklade, len keď boli pridané pravidlá podmieneného formátovania bunky B2 a B4 zostalo žlté. Keďže zadané pravidlá podmieneného formátovania platia pre bunky B3 a B5, farby pozadia sa zmenia zo žltej na zelenú a červenú.
