Podmienené formáty formátovania programu Excel

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.

Od programu Excel 2007 má aplikácia Excel 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.

Použitie viacerých pravidiel

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% - z 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.

Príklad: Hľadanie údajov, ktoré prekračujú 25% a 50%, sa zvyšuje pri podmienenom formátovaní

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 údaje v bunkách A2 až A5 sú väčšie ako zodpovedajúce hodnoty v B2 až B5 o viac ako 25%.
• druhé pravidlo skontroluje, či rovnaké údaje v A2: A5 presahujú 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 sa zmení.

• pre údaje, kde je rozdiel väčší ako 25%, sa farba pozadia bunky zmení na modrý.
• ak je rozdiel väčší ako 50%, farba pozadia bunky sa zmení na červenú.

Pravidlá používané na splnenie tejto úlohy,

= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%

bude zadané pomocou dialógového okna Formátovanie podmieneného formátovania.

Zadanie údajov výučby

1. Zadajte údaje do buniek A1 až C5, ako vidíte na obrázku vyššie

Poznámka: Krok 3 tutoriálu pridá vzorky do buniek C2: C4, ktoré zobrazujú presný percentuálny rozdiel medzi hodnotami v bunkách A2: A5 a B2: B5, aby sa skontrolovala presnosť pravidiel podmieneného formátovania.

Nastavenie konformných pravidiel formátovania

Ako bolo spomenuté, podmienené pravidlá formátovania, ktoré kontrolujú dve podmienky, sa zadajú pomocou dialógového okna podmieneného formátovania nového formátu.

Nastavenie podmieneného formátovania na nájdenie zvýšenia o viac ako 25%

1. Zvýraznite bunky B2 až B5 v pracovnom liste.
2. Kliknite na kartu Domov na karte.
3. Kliknutím na ikonu Conditional Formatting na karte otvoríte rozbaľovaciu ponuku.
4. Zvoľte nové pravidlo, čím otvoríte dialógové okno Nový formátovací formát, ako je vidieť na obrázku vyššie.
5. V hornej polovici dialógového okna kliknite na poslednú možnosť: Použite vzorec na určenie, ktoré bunky sa majú formátovať.
6. V dolnej polovici dialógového okna kliknite na hodnoty formátu, kde je tento vzorec pravdivý: riadok.
7. Zadajte vzorec : = (A2-B2) / A2> 25% v danom priestore
8. Kliknutím na tlačidlo Formát otvoríte dialógové okno Formát buniek.
9. V tomto dialógovom okne kliknite na kartu Vyplniť a vyberte modrou farbu výplne.
10. Dvakrát kliknite na tlačidlo OK, čím zatvoríte dialógové okná a vrátite sa do pracovného hárka.
11. V tomto bode by farba pozadia buniek B3 a B5 mala byť modrá.

Nastavenie podmieneného formátovania na nájdenie zvýšenia o viac ako 50%

1. Pri bunkách B2 až B5, ktoré sú stále vybrané, opakujte kroky 1 až 6 uvedené vyššie.
2. Do zobrazeného miesta zadajte vzorec: = (A2-B2) / A2> 50%.
3. Kliknutím na tlačidlo Formát otvoríte dialógové okno Formát buniek.
4. Kliknite na kartu Vyplniť a vyberte červenú farbu výplne.
5. Dvakrát kliknite na tlačidlo OK, čím zatvoríte dialógové okná a vrátite sa do pracovného hárka .
6. Farba pozadia bunky B3 by mala byť stále modrá, čo znamená, že percentuálny rozdiel medzi číslami v bunkách A3 a B3 je väčší ako 25%, ale menší alebo rovný 50%.
7. Farba pozadia bunky B5 by sa mala zmeniť na červenú, čo znamená, že percentuálny rozdiel medzi číslami v bunkách A5 a B5 je väčší ako 50%.

Kontrola podmienených pravidiel formátovania

Výpočet% rozdielu

Ak chcete skontrolovať, či sú zadané pravidlá podmieneného formátovania správne, môžeme do buniek C2: C5 zadať vzorce, ktoré vypočítajú presný percentuálny rozdiel medzi číslami v rozsahu A2: A5 a B2: B5.

1. Kliknutím na bunku C2 ju vytvoríte aktívnou bunkou.
2. Zadajte vzorec = (A2-B2) / A2 a stlačte kláves Enter na klávesnici.
3. Odpoveď 10% by sa mala objaviť v bunke C2, čo znamená, že číslo v bunke A2 je o 10% väčšie ako číslo v bunke B2.
4. Na zobrazenie odpovede v percentách môže byť potrebné zmeniť formátovanie na bunke C2.
5. Pomocou rukoväte na naplnenie skopírujte vzorec z bunky C2 do buniek C3 až C5.
6. Odpovede na bunky C3 až C5 by mali byť: 30%, 25% a 60%.
7. Odpovede v týchto bunkách ukazujú, že vytvorené pravidlá podmieneného formátovania sú správne, pretože rozdiel medzi bunkami A3 a B3 je väčší ako 25% a rozdiel medzi bunkami A5 a B5 je väčší ako 50%.
8. Bunka B4 nezmenila farbu, pretože rozdiel medzi bunkami A4 a B4 sa rovnal 25% a naše pravidlo podmieneného formátovania špecifikovalo, že na zmenu farby pozadia na modrou sa vyžaduje percento väčšie ako 25%.

Poradie prednosti pre podmienené pravidlá formátovania

Uplatňovanie pravidiel konfliktných podmienených formátov

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, kde je druhé pravidlo pravdivé (rozdiel v hodnote je väčší ako 50% medzi dvomi bunkami), je pravda aj prvé pravidlo (rozdiel v hodnote je väčší ako 25%).

Excel Order of Precedence

Keďže bunka nemôže mať súčasne červené a modré 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 (= (A2-B2) / A2> 50%) 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 priorít, použite tlačidlá so šípkami nahor a nadol 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 (= (A2-B2) / A2> 25%) formátovalo rozsah buniek B2: B5 s modrým okrajom namiesto modrej farby pozadia, dve podmienené pravidlá formátovania by neboli v konflikte oba formáty sa môžu použiť bez toho, aby zasahovali do druhej.

V dôsledku toho by bunka B5 mala modrú hranicu a červenú farbu pozadia, pretože rozdiel medzi číslami v bunkách A5 a B5 je väčší 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 v príklade na bunky B2 až B5 na začiatku aplikovaná žltá farba pozadia, akonáhle budú pridané pravidlá podmieneného formátovania, zostanú žlté len bunky B2 a B4.

Pretože zadané pravidlá podmieneného formátovania platia pre bunky B3 a B5, ich farby pozadia sa zmenia zo žltej na modrej a červenej.