Ako automaticky farbiť dátumy = sviatky

EXCEL – K najobľúbenejším nástrojom v Exceli patrí bez debaty podmienené formátovanie. Každý má rád farbičky a tento nástroj farbičkuje za nás. Ušetrí množstvo času.

V príspevku sa budem venovať postupu, ako naformátujete dátumy pripadajúce na sviatok.

Pred pár dňami sa na mňa obrátil s prosbou Branislav, ktorý objavil článok s formátovaním celých riadkov (odkaz na článok), ale problém bol v tom, že potreboval formátovať stĺpce, nie riadky…

Má zoznam dátumov a potrebuje farebne odlíšiť víkendy. Tomuto bol v spojení s podmieneným formátovaním zasa venovaný iný príspevok (odkaz na článok).

Dva postupy by boli, už len to dať dohromady. Poradila som mu, ako to nastaviť, aby to fungovalo na stĺpce. No a potom prišla ďalšia otázka s formuláciou, ktorá ma aj pobavila… 🙂

„….všetko mi funguje tak, ako má. Ďakujem veľmi pekne. Ale keďže zvedaví ľudia nie sú nikdy úplne spokojní, tak aj mňa ešte máta jedno vylepšenie… Bolo by to úplne dokonalé, keby to vyznačovalo aj stĺpce so sviatkami. Niečo som pozeral na nete (len slovenské/české stránky) ale nič funkčné som nenašiel. Zoznam sviatkov mám. Ešte vymyslieť nejaké formátovanie, ktoré prebehne ten zoznam a ak nájde daný dátum v zozname, vyfarbí celý stĺpec…“

Takže má zoznam dátumov a potrebuje farebne odlíšiť víkendy a sviatky.

S víkendovými dátumami sme si už poradili, teraz sú na rade sviatky. Ponúka sa viac riešení. Mne sa ako najjednoduchšie a najrýchlejšie javí použitie funkcie VLOOKUP. Veď keď niečo hľadáme v zozname, táto funkcia je ideálna.

Podrobný postup:

1. najskôr si niekde vypíšte dátumy so sviatkami – môžu byť aj na samostatnom hárku, ktorý môžete skryť, nech zbytočne nezavadzia, keďže ide len o pomocné bunky.

Nemusia to byť len sviatky. Môžete si tam dať ľubovoľné dátumy, kedy sa u vás nepracuje (športový deň, celofiremná dovolenka, deň pre rodinu a pod.). Dôležité je, aby sedeli s obdobím, v ktorom sú ostatné dátumy. U mňa je to rok 2020.

Ak by som analyzovala údaje s minulým rokom, tak tam budú sviatky z roku 2019. Ak to budú dátumy z rôznych rokov, treba uviesť sviatky zo všetkých sledovaných rokov. Keby sme nemali pohyblivé sviatky, tak by sa to dalo nastaviť aj univerzálne.

2. vyznačte bunku alebo bunky s dátumami, v ktorých potrebujete nastaviť automatický formát,

3. na karte Domov (Home) kliknite na ikonu Podmienené formátovanie >> Nové pravidlo (Home >> Conditional Formatting >> New Rule),

4. v otvorenom okne zvoľte Použiť vzorec na určenie buniek, ktoré sa majú formátovať (Use a formula to determine which cells to format),

5. do okienka Formátovať hodnoty, v ktorých je tento vzorec pravdivý (Format values in where this formula is true) vložte funkciu: =VLOOKUP(A1;$E$2:$E$16;1;0)<>“#NEDOSTUPNÝ“

Funkcia VLOOKUP bude hľadať dátum z bunky A1 v bunkách E2:E16. Ak ho nenájde, tak je výsledkom chyba #NEDOSTUPNÝ. To je práve to, čo nás NEzaujíma. Formátovať potrebujeme iba tie, v ktorých táto chyba nenastane, t.j. tie, ktoré sú rôzne od #NEDOSTUPNÝ.

——–

Ak používate inú jazykovú mutáciu (nie slovenskú), tak je potrebné zadať chybu #NEDOSTUPNÝ v jazyku, ktorému „rozumie“ váš Excel. Alebo použite iný zápis, ktorý bude fungovať bez ohľadu na jazyk: =NOT(ISERROR(VLOOKUP(A1;$E$2:$E$16;1;0)))

Ak VLOOKUP dátum nenájde, tak bude výsledkom chyba, t.j. funkcia ISERROR sa bude rovnať TRUE. Nás však zaujímajú sviatky, čiže tie, ktoré VLOOKUP našiel. Preto treba výsledok funkcie ISERROR znegovať pomocou NOT.

——–

Vo vašich údajoch bude zrejme potrebné urobiť zopár úprav:

• prvú adresu (v mojom prípade A1) prepíšte na adresu bunky, ktorú máte ako prvú vyznačenú, t.j. kde sú dátumy,
• druhý argument (v mojom prípade $E$2:$E$16) sú bunky, kde sú sviatky. Prepíšte podľa vášho zoznamu sviatkov,

6. nezabudnite nastaviť Formát vpravo dolu (inak sa nič nenaformátuje) a potvrďte Ok.

Hotovo 🙂

Iné riešenia:

• Použiť by sa samozrejme dala aj iná funkcia, napr. OR. Mnohým by mohla pripadať jednoduchšia ako VLOOKUP, ale z môjho pohľadu by to bolo zbytočne veľa klikania. Len pre ukážku časť zápisu:
  =OR(A1=$E$2;A1=$E$3;A1=$E$4; A1=$E$5; A1=$E$6….ďalšie sviatky….)
  Zápis je dosť dlhý a to je zohľadnených len päť dátumov so sviatkami. Každý argument funkcie OR by bol venovaný jednému sviatku. Tento postup by bol výhodný vtedy, ak nechcete mať sviatky v pomocných bunkách, ale priamo vo funkcii v podmienenom formátovaní.
• V novších verziách Excelu by mohla byť použitá aj funkcia IFS. No zase by sa musel „naklikať“ každý sviatok samostatne.
• Obdobným riešením ako je funkcia VLOOKUP, je použitie funkcií INDEX a MATCH. Ich cieľom je to isté, čo pri funkcii VLOOKUP.
  Ak vás zaujímajú tieto vyhľadávacie funkcie, v ponuke mojich online videokurzov nájdete aj kurz venovaný problémom a záludnostiam funkcie VLOOKUP + vysvetľujem aj iné pokročilé vyhľadávacie funkcie (napr. INDEX, MATCH, OFFSET, HLOOKUP). Viac o kurze sa dozviete tu >> klik.

Napadlo vám elegantnejšie riešenie k tomu, ako zafarbiť dátum so sviatkami? Ak áno, tak ma poteší, keď  mi napíšte na zuzana@zuzanapogranova.sk