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Ý.

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ď sa podelíte v komentároch pod príspevkom, alebo mi napíšte na zuzana@zuzanapogranova.sk


Radi by ste sa na niečo spýtali? Je v článku niečo nejasné, alebo vás zaujímajú doplňujúce informácie? Napíšte mi a ja vám veľmi rada odpoviem na všetky otázky.

Máte aj vy nejaké svoje trápenie s Excelom, s Wordom alebo PowerPointom? Napíšte mi o tom. Ak to trápi vás, tak to s veľkou pravdepodobnosťou bude trápiť aj niekoho iného. Riešenie môžem spracovať vo forme článku a zašlem odkaz aj na váš e-mail, aby ste sa o ňom dozvedeli medzi prvými. Pomôžete tak sebe aj ostatným.

Alebo sa chcete učiť sami v pohodlí a v čase, ktorý vám vyhovuje? Vyberte si niektorý z hotových online kurzov:

Prípadne môžeme spolupracovať aj na väčších projektoch: spolupráca, firemné i individuálne kurzy, súbory na kľúč podľa vašich potrieb.

Zuzana Pogranová
Som dlhoročnou lektorkou programu MS Excel a svojich klientov učím, ako Excel „premýšľa“, čo ponúka a ako nájdu a použijú ten správny postup pre svoje výpočty. Viac o mne >>
Komentáre