Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » formatovani » tvorba-dynamickeho-kalendare-Excel
Microsoft Excel logo

Tvorba dynamického kalendáře Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Potřebuji vytvořit dynamický kalendář, kdy pouhou změnou roku a měsíce se vytvoří automaticky kalendář na daný měsíc a rok a podbarví víkendové dny.

Doplněno.

Ú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:

Ukázka

Excel dynamický kalendář podklady - Excel

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

Excel dynamický kalendář zdroj pro tvorbu - Excel

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

Excel dynamický kalendář funkce - Excel

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

Excel dynamický kalendář podmíněné formátování - Excel

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

Excel dynamický kalendář funkce - Excel

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.

Excel dynamický kalendář funkce - Excel

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):

Excel dynamický kalendář funkce - Excel

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) Excel dynamický kalendář funkce - Excel

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:

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

Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor Kalendář - dynamický Excel - zdrojová data soubor ve formátu *.xlsx 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: 15.02.2019 08:30

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

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







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 1125

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.