Jak používat vlastní podmíněné formátovací pravidla pro data v aplikaci Excel

Přidání podmíněného formátování do buňky v aplikaci Excel umožňuje použít různé možnosti formátování, například barvu, pokud data v buňce splňují nastavené podmínky.

Chcete-li usnadnit používání podmíněného formátování, jsou k dispozici přednastavené možnosti, které pokrývají běžně používané situace, například:

• Termíny
• Duplicitní data
• Hodnoty nad nebo pod průměrnou hodnotou v rozsahu buněk

V případě dat, přednastavené možnosti usnadňují kontrolu vašich dat pro data blízká aktuálnímu datu, například včera, zítra, minulý týden nebo příští měsíc.

Pokud chcete zkontrolovat data, která spadají mimo uvedené možnosti, můžete však přizpůsobit podmíněné formátování přidáním vlastního vzorce pomocí jedné nebo více funkcí datumu aplikace Excel.

01 z 06

Kontrola termínů 30, 60 a 90 dnů

Přizpůsobení podmíněného formátování pomocí vzorců se provádí nastavením nového pravidla, které aplikace Excel použije při vyhodnocování dat v buňce.

Podrobný příklad zde nastavuje tři nová pravidla podmíněného formátování, která zkontrolují, zda data zadaná do vybraného rozsahu buněk jsou za posledních 30 dnů, za posledních 60 dní nebo za posledních 90 dní.

Vzorce použité v těchto pravidlech odečíslují určitý počet dnů od aktuálního data v buňkách C1 až C4.

Aktuální datum se vypočítá pomocí funkce TODAY .

Chcete-li tento návod pracovat, musíte zadat data, která spadají do výše uvedených parametrů.

Poznámka : Aplikace Excel používá podmíněné formátování v pořadí shora dolů, že pravidla jsou uvedena v dialogovém okně Podmíněné formátování správců pravidel, jak je vidět na obrázku výše.

Přestože na některé buňky se mohou vztahovat více pravidel, platí první pravidlo, které splňuje podmínku, na buňky.

02 ze dne 06

Kontrola dat po uplynutí 30 dnů

1. Označte buňky C1 až C4 a vyberte je. Toto je rozsah, na který budeme používat podmíněné formátovací pravidla
2. Klepněte na kartu Domů v nabídce pásu karet .
3. Klepnutím na ikonu podmíněného formátování otevřete rozbalovací nabídku.
4. Vyberte možnost Nová pravidla . Otevře se dialogové okno Nový formátovací pravidlo.
5. Klepnutím na příkaz Použít vzorec můžete určit, které buňky chcete formátovat .
6. Zadejte následující vzorec do pole pod hodnotami Formát, kde je tato hodnota pravdivá ve spodní polovině dialogového okna:
   = TODAY () - C1> 30
   Tento vzorec kontroluje, zda jsou data v buňkách C1 až C4 delší než 30 dnů
7. Klepnutím na tlačítko Formát otevřete dialogové okno Formát buněk.
8. Klepnutím na kartu Vyplnit zobrazíte možnosti doplnění barvy na pozadí.
9. Vyberte barvu výplně pozadí - chcete-li odpovídat příkladu v tomto výukovém programu, zvolte světle zelený.
10. Klepnutím na kartu Font zobrazíte možnosti formátování písma
11. Pod částí barev nastavte barvu písma na bílou, aby odpovídala tomuto výukovému programu.
12. Klepnutím dvakrát na tlačítko OK zavřete dialogové okno a vrátíte se do pracovního listu.
13. Barva pozadí buněk C1 až C4 se změní na vybranou barvu výplně, přestože v buňkách nejsou žádná data.

03 ze dne 06

Přidání pravidla pro data více než 60 dní

Použití možnosti Spravovat pravidla

Spíše než opakovat všechny výše uvedené kroky, abychom přidali další dvě pravidla, použijeme volbu Spravovat pravidla, která nám umožní přidávat další pravidla najednou.

1. V případě potřeby zvýrazněte buňky C1 až C4.
2. Klikněte na kartu Domů v nabídce pásu.
3. Klepnutím na ikonu podmíněného formátování otevřete rozbalovací nabídku.
4. Zvolte možnost Spravovat pravidla pro otevření dialogového okna Správce podmíněných formátovacích pravidel.
5. Klikněte na možnost Nová pravidla v levém horním rohu dialogového okna
6. Klepnutím na příkaz Použít vzorec můžete určit, které buňky chcete formátovat ze seznamu v horní části dialogového okna.
7. Zadejte následující vzorec do pole pod hodnotami Formát, kde je tato hodnota pravdivá ve spodní polovině dialogového okna :
   = TODAY () - C1> 60

   Tento vzorec zkontroluje, zda jsou data v buňkách C1 až C4 delší než 60 dnů.

8. Klepnutím na tlačítko Formát otevřete dialogové okno Formát buněk.
9. Klepnutím na kartu Vyplnit zobrazíte možnosti doplnění barvy na pozadí.
10. Vyberte barvu výplně pozadí; chcete-li odpovídat příkladu v tomto výukovém programu, zvolte žlutou.
11. Klepnutím dvakrát na tlačítko OK zavřete dialogové okno a vrátíte se do dialogového okna Podmíněný správce formátovacích pravidel.

04 z 06

Přidání pravidla pro data více než 90 dnů

1. Opakujte kroky 5 až 7 výše a přidejte nové pravidlo.
2. Pro použití vzorce:
   = TODAY () - C1> 90
3. Vyberte barvu výplně pozadí; chcete-li odpovídat příkladu v tomto výukovém programu, vyberte oranžovou.
4. Nastavte barvu písma na bílou, aby odpovídala tomuto návodu.
5. Klepnutím dvakrát na tlačítko OK zavřete dialogové okno a vrátíte se do dialogového okna Podmíněný správce formátovacích pravidel
6. Klepnutím na tlačítko OK zavřete toto dialogové okno a vrátíte se do pracovního listu .
7. Barva pozadí buněk C1 až C4 se změní na poslední vybranou barvu výplně.

05 ze dne 06

Testování podmíněných formátovacích pravidel

Jak je vidět v tutoriálním obrázku, můžeme otestovat podmíněné formátovací pravidla v buňkách C1 až C4 zadáním následujících dat:

• Aktuální datum - buňka by se měla změnit na výchozí bílé pozadí s černým textem, protože se nepoužije žádné z podmínek podmíněného formátování.
• 40 dnů před aktuálním datem - buňka by se měla změnit na světle zelené pozadí s bílým textem.
  
  (Poznámka: Pokud máte potíže s výpočtem takového data, jak je vidět na obrázku výše, vzorec = TODAY () - 40 zadá datum 40 dnů před aktuálním datem.)
• 70 dní před aktuálním datem - buňka by se měla změnit na žluté pozadí s bílým textem.
• 100 dnů před aktuálním datem - buňka by se měla změnit na tmavě červené pozadí s bílým textem.

06 z 06

Alternativní podmíněné formátovací pravidla

Pokud se v pracovním listu již zobrazuje aktuální datum - a většina pracovních listů se děje - alternativní vzorec k výše uvedenému může použít odkaz buňky na buňku, kde je aktuální datum zobrazen, nikoli pomocí funkce TODAY.

Například pokud je datum v buňce B4 zobrazeno, vzorec zadaný jako pravidlo pro podmíněně formátované data, které jsou více než 30 dní po splatnosti, může být následující:

= $ B $ 4> 30

Označení dolaru ($) obklopující odkaz na buňku B4 brání změně odkazu na buňku, pokud je pravidlo podmíněného formátování zkopírováno do jiných buněk v listu.

Znaky dolaru vytvářejí to, co je známé jako absolutní odkaz na buňky .

Pokud jsou znaky dolaru vynechány a pravidlo podmíněného formátování je zkopírováno, cílová buňka nebo buňky s největší pravděpodobností zobrazí #REF! chybové hlášení.