Tvorba dynamického kalendáře Excel

Úvodem do tvorby kalendáře

Často se ve firmách tvoří měsíční kalendář, kdy se podbarvují ručně řádky, které jsou víkendové (sobota, neděle). Bohužel se tento kalendář tvoří ručně a jeho tvorba zabírá několik hodin každý měsíc.

Jak na to se dozvíte v tomto článku, kdy je přehledně rozdělen na jednotlivé kapitoly:

• Zdroj dat
• Výpočty dnů a víkendů
• Podmíněné formátování
• Co dále... rozkopírovat
• Tipy na další vylepšení
  • Dny nad rámec měsíce
  • Svátky
  • Ověření dat
• Tip na další kalendář - na šířku
• Další související články
• Ke stažení

Ukázka

Zdroj dat

Pro vytvoření kalendáře je potřeba příprava podkladů. Do dvou buněk se bude zadávat rok a měsíc (potažmo jméno a příjmení, například pokud se bude jednat o pracovní výkaz). Dále sloupec, kde budou čísla 1 - 31 (pomůcka pro tvorbu datumu), nebo-li max počet dnů, které může měsíc obsahovat. Plus pomocné sloupce Den a Den týdne (které budou potřeba pro podmíněné formátování), případně si připravte další sloupce, které v kalendáři budete potřebovat, například čas příchodu a odchodu, přestávka, poznámky, název činnosti, atd.

Ukázka

Výpočty dnů a víkendů

Podklady máte připraveny a teď zbývá jen doplnit vzorce pro vytvoření dne, neboli příslušného datumu a další funkce, která zjistí o který den v týdnu jde.

Nejprve vygenerujeme příslušné datumy, ze zadaného měsíce a roku (což jsou statické údaje, na které budeme absolutně odkazovat). Dále dnem je ona řada 1 - 31, která vám v relativním odkazu pomůže datum vytvořit:

=DATUM($E$4;$E$5;B8)

Následně zjistíme o který den v týdnu se jedná, ať můžeme zúročit v podmíněném formátování.

=DENTÝDNE(C8;2)

Můžete doplnit i další výpočty pro odpracovanou dobu atd. Což nechám již na vás.

Ukázka

Podmíněné formátování

Zbývá nastavit podmíněné formátování (o který den v týdnu se jedná, už máte z předchozí kapitoly zjištěno). Můžete pro víkendové dny mít jednu barvu, nebo pro každý den víkendu jinou.

Pokud máte označenou oblast (B8:H38) ve které chcete řádky podbarvit. Na pásu karet Domů v sekci Styl klik na ikonu Podmíněné formátování > Nové pravidlo. V dialogovém okně stačí vybrat: Určit buňky k formátování pomocí vzorce. Pravidlo stačí zadat:

=$D8>5

A vybrat jaký formát potřebujete. Pro samostatné formáty (například sobota světlejší barvou a neděle tmavší), můžete využít pravidla:

=$D8=6 =$D8=7

Samozřejmě každé pravidlo musíte zadat samostatně.

Ukázka

Co dále?

Můžete skrýt sloupec s dnem týdne, který máte jako pomocný.

Kalendář jako zdroj máte hotov. Teď jen stačí rozkopírovat. Například tento dynamický kalendář mít jako vzorový a pro další měsíce použít rozkopírováni listu. Držet Ctrl a zkopírovat list. Tento přejmenovat např na 01 atd.

Pokud provedete 12 můžete mít kalendář na celý rok, během pár minut od otevření prázdného listu v Excel.

Ukázka

Tipy na další vylepšení

Kalendář si můžete vylepšit:

• Dny nad rámec měsíce - se nebudou zobrazovat (stačí využít funkce KDYŽ a MĚSÍC)
• Svátky - například přidat sloupec X a jen ručně zaškrtnout, nebo pomocnou tabulku se svátky a SVYHLEDAT / KDYŽ a v pomocném sloupci a opět podbarvovat celý řádek
• Ověření dat - pro zadávání roku a měsíců ať člověk nezadá měsíc 13 ;)
• atd. - což už nechám na vás jak si kalendář upravíte

Skrýt dny nad rámec měsíce

Řešení ke skrytí měsíců na příní v komentářích. Například využít funkce KDYŽ a MĚSÍC. Funkce z řádku 36.

=KDYŽ(MĚSÍC(DATUM($D$4;$D$5;A36))=$D$5;DATUM($D$4;$D$5;A36);"")

Nebo s funkcí EOMONTH

=KDYŽ( DATUM($D$4;$D$5;A36)<=EOMONTH(DATUM($D$4;$D$5;1);0);DATUM($D$4;$D$5;A36);"")

Nebo další x možných řešení, ale to již nechávám na vás čtenářích.

Svátky

Zde předpokládám tabulku svátku pro daný rok. Například (ukázkové svátky, jde o příklad, některé se v ČR nemusí vyskytovat):

Posléze se stačí zkontrolovat, zda daný den je sváteční, využitím SVYHLEDAT (proto ve druhém sloupci to x, které předtavuje svátek):

=IFERROR(SVYHLEDAT(B8;$K$9:$M$12;2;0);"")

Nebo COUNTIFS

=COUNTIF($K$9:$K$14;B8)

Podmíněné formátování již dotvoříte sami, zopakováním postupu z výše uvedeného příkladu.

Tip na další kalendář

Podobně můžete vytvořit kalendář na šířku, neboli datum rozkopírujete v řádku a postu zopakujete jen hodnoty nebudou ve sloupcích jako ve víše představené ukázce, ale v řádcích.

Řešení již nechám na čtenáři.

Další související články

Pokud si nejste jistí tvorbou mohou vám pomoci články:

• Podmíněné formátování prakticky
• Přehled funkcí datum a čas
• Funkce datum a čas

Případně docházkový list, který je pzrapcán v samostatném článku do větších podrobností:.

• Docházkový list - základ
• Docházkový list 2 - pokračování a vylepšení funkčnosti

Ke stažení

Soubor Kalendář - dynamický Excel - zdrojová data ke stažení zdarma.

Závěrem

Máte nějaký tip nebo trik na tvorbu kalendáře? Můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 10:56

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.