Ako používať vlastné podmienené pravidlá formátovania pre dátumy v programe Excel

Pridaním podmieneného formátovania do bunky v programe Excel môžete použiť rôzne možnosti formátovania, napríklad farbu, keď údaje v tejto bunke spĺňajú nastavené podmienky.

Ak chcete jednoduchšie používať podmienené formátovanie, sú k dispozícii prednastavené možnosti, ktoré pokrývajú bežne používané situácie, napríklad:

• Termíny
• Duplicitné údaje
• Hodnoty nad alebo pod priemernou hodnotou v rozsahu buniek

V prípade dátumov vám vopred nastavené možnosti umožňujú jednoduché skontrolovanie vašich údajov na dátumy blízke aktuálnemu dátumu, napríklad včera, zajtra, minulý týždeň alebo nasledujúci mesiac.

Ak chcete skontrolovať dátumy, ktoré nespadajú do uvedených možností, môžete prispôsobiť podmienené formátovanie pridaním vlastného vzorca pomocou jednej alebo viacerých funkcií dátumu programu Excel.

01 z 06

Kontrola dátumov 30, 60 a 90 dní

Prispôsobenie podmieneného formátovania pomocou vzorcov sa vykonáva nastavením nového pravidla, ktoré program Excel použije pri hodnotení údajov v bunke.

Podrobný príklad tu nastavuje tri nové pravidlá podmieneného formátovania, ktoré skontrolujú, či dátumy zadané do vybraného rozsahu buniek sú za posledných 30 dní, za posledných 60 dní alebo za posledných 90 dní.

Vzorce používané v týchto pravidlách odpočítavajú určitý počet dní od aktuálneho dátumu v bunkách C1 až C4.

Aktuálny dátum sa vypočítava pomocou funkcie TODAY .

Ak chcete tento návod pracovať, musíte zadať dátumy, ktoré spadajú do vyššie uvedených parametrov.

Poznámka : Aplikácia Excel používa podmienené formátovanie v poradí, zhora nadol, že pravidlá sú uvedené v dialógovom okne Podmienené riadenie formátovania správ, ako je vidieť na obrázku vyššie.

Napriek tomu, že na niektoré bunky sa môžu vzťahovať viaceré pravidlá, prvé pravidlo, ktoré spĺňa podmienky, sa uplatňuje na bunky.

02 z 06

Kontrola dátumov 30 dní po splatnosti

1. Označte bunky C1 až C4 a vyberte ich. Toto je rozsah, na ktorý použijeme podmienené pravidlá formátovania
2. Kliknite na kartu Domov v ponuke pásky .
3. Kliknutím na ikonu podmieneného formátovania otvoríte rozbaľovaciu ponuku.
4. Vyberte možnosť Nový predpis . Otvorí sa dialógové okno Nový formátovací kód.
5. Kliknutím na možnosť Použiť vzorec môžete určiť, ktorá možnosť buniek sa má formátovať .
6. Zadajte nasledujúci vzorec do poľa pod hodnotami Format (Formát), kde je táto hodnota pravdivá v dolnej polovici dialógového okna:
   = TODAY () - C1> 30
   Tento vzorec kontroluje, či sú dátumy v bunkách C1 až C4 viac ako 30 dní
7. Kliknutím na tlačidlo Formát otvoríte dialógové okno Formát buniek.
8. Kliknutím na kartu Vyplniť zobrazíte možnosti farby naplnenia pozadia.
9. Vyberte farbu naplnenia pozadia, aby ste v súlade s príkladom v tomto návode vybrali zelenú farbu.
10. Kliknutím na kartu Font zobrazíte možnosti formátovania písma
11. V sekcii farieb nastavte farbu písma na bielu, aby zodpovedala tomuto návodu.
12. Dvakrát kliknite na tlačidlo OK, čím zatvoríte dialógové okno a vrátite sa do pracovného hárka.
13. Farba pozadia buniek C1 až C4 sa zmení na zvolenú farbu výplne, aj keď v bunkách nie sú žiadne údaje.

03 z 06

Pridanie pravidla pre dátumy viac ako 60 dní

Použitie možnosti Spravovať pravidlá

Namiesto opakovania všetkých vyššie uvedených krokov pridajte ďalšie dve pravidlá, použijeme možnosť Správa pravidiel, ktorá nám umožní pridať ďalšie pravidlá naraz.

1. Ak je to potrebné, zvýraznite bunky C1 až C4.
2. Kliknite na kartu Domov v ponuke pásky.
3. Kliknutím na ikonu Conditional Formatting otvoríte rozbaľovaciu ponuku.
4. Vyberte možnosť Spravovať pravidlá a otvorte dialógové okno Podmienené pravidlá správcu formátovania.
5. Kliknite na možnosť Nový predpis v ľavom hornom rohu dialógového okna
6. Kliknutím na možnosť Použiť vzorec môžete určiť, ktoré bunky chcete formátovať zo zoznamu v hornej časti dialógového okna.
7. Zadajte nasledujúci vzorec do poľa pod hodnotami Format (Formát), kde je táto hodnota pravdivá v dolnej polovici dialógového okna :
   = TODAY () - C1> 60

   Tento vzorec kontroluje, či sú dátumy v bunkách C1 až C4 dlhšie ako 60 dní.

8. Kliknutím na tlačidlo Formát otvoríte dialógové okno Formát buniek.
9. Kliknutím na kartu Vyplniť zobrazíte možnosti farby naplnenia pozadia.
10. Vyberte farbu výplne pozadia; aby ste v súlade s príkladom v tomto výučbe zvolili žltú.
11. Dvakrát kliknite na tlačidlo OK, čím zatvoríte dialógové okno a vrátite sa do dialógového okna Podmienené riadenie formátovacích pravidiel.

04 z 06

Pridanie pravidla pre dátumy dlhšie ako 90 dní

1. Opakujte kroky 5 až 7 vyššie a pridajte nové pravidlo.
2. Pri použití vzorca:
   = TODAY () - C1> 90
3. Vyberte farbu výplne pozadia; aby ste zodpovedali príkladu tohto tutoriálu, vyberte oranžovú.
4. Nastavte farbu písma na bielu, aby zodpovedala tomuto návodu.
5. Dvakrát kliknite na tlačidlo OK, čím zatvoríte dialógové okno a vrátite sa do dialógového okna Podmienené riadenie formátovacích pravidiel
6. Opätovným kliknutím na tlačidlo OK zatvorte toto dialógové okno a vrátite sa na pracovný hárok .
7. Farba pozadia buniek C1 až C4 sa zmení na poslednú zvolenú farbu výplne.

05 z 06

Testovanie podmienených pravidiel formátovania

Ako je vidieť v tutoriálnom obrázku, môžeme testovať podmienené pravidlá formátovania v bunkách C1 až C4 zadaním nasledujúcich dátumov:

• Aktuálny dátum - bunka by sa mala zmeniť na predvolené biele pozadie s čiernym textom, keďže sa nevzťahuje žiadne z pravidiel podmieneného formátovania.
• 40 dní pred aktuálnym dátumom - bunka by sa mala zmeniť na svetlo zelené pozadie s bielym textom.
  
  (Poznámka: Ak máte problémy s vypočítaním takého dátumu, ako je vidieť na obrázku vyššie, vzorec = TODAY () - 40 zadá dátum 40 dní pred aktuálnym dátumom.)
• 70 dní pred aktuálnym dátumom - bunka by sa mala zmeniť na žlté pozadie s bielym textom.
• 100 dní pred aktuálnym dátumom - bunka by sa mala zmeniť na tmavočervené pozadie s bielym textom.

06 z 06

Alternatívne podmienené pravidlá formátovania

Ak váš pracovný hárok už zobrazuje aktuálny dátum - a väčšina pracovných hárkov to robí - alternatívny vzorec k vyššie uvedeným môže použiť odkaz na bunku, kde je zobrazený aktuálny dátum, namiesto použitia funkcie TODAY.

Napríklad, ak je dátum zobrazený v bunke B4, vzorec zadaný ako pravidlo pre podmienene formátované dátumy, ktoré sú dlhšie ako 30 dní, môže byť:

= $ B $ 4> 30

Znaky dolára ($), ktoré obklopujú bunkovú referenciu B4, bránia zmene odkazu na bunku, ak sa pravidlo podmieneného formátovania skopíruje do iných buniek v pracovnom hárku.

Znaky v dolári vytvárajú to, čo je známe ako absolútna referencia buniek .

Ak sú znaky dolára vynechané a pravidlo podmieneného formátovania je skopírované, cieľová bunka alebo bunky s najväčšou pravdepodobnosťou zobrazia #REF! chybná správa.