čtvrtek 12. února 2015

Roční kalendář – 2. díl: Jak to funguje?

Před týdnem jsem představil šablonu ročního kalendáře, kterou jsem sám vytvořil. Jak jsem psal minule, vlastní šablonu jsem vytvářel hlavně proto, abych ji měl více pod kontrolou. Ostatním kalendářům jsem většinou pořádně nerozumněl a podrobnější vysvětlení od autorů scházelo.

Abych zabránil podobnému osudu u mého kalendáře, podíváme se nyní, jak celý kalendář funguje.

Než ale začnete číst, důrazně doporučuji, abyste si stáhli zdrojový soubor, na kterém jsem fungování kalendáře demonstroval.

Čeho chceme dosáhnout?

Potřebujeme vytvořit dynamický roční kalendář, kde jednotlivé buňky budou obsahovat datum daného dne (čili např. 1. 1. 2015, 2. 1. 2012, 3. 1. 2015 atd.). Samozřejmě nabízí se i varianta, kde bychom buňky naplnili "pouze" čísly 1, 2, 3 atd., první varianta je však univerzálnější a má více možných využití do budoucna. To si ukážeme ve 3. díle seriálu.

Teoretický postup

Vytvoříme si nejprve jednoduchou tabulku pro jeden měsíc (např. leden). Vzhledem k tomu, že tabulka bude dynamická (bude obsahovat vzorce), můžeme ji pak zkopírovat pro zbylých jedenáct měsíců.

V dalším kroku pak na tabulku aplikujeme vhodné formátování (včetně podmíněného).

Vytvoření tabulky
  • Rok obsahuje dvanáct měsíců, 52 týdnů. 
  • Týdny mají 7 dní. 
  • Každý měsíc, kromě února, se může "rozpadnout" až do šesti týdnů (např. březen 2015).
Základní (nevyplněná) tabulka pro jeden měsíc pak bude vypadat takto. (K zeleně označným buňkám se ještě vrátíme.)
Teď už potřebujeme "jenom" tabulku naplnit daty. Teorie je snadná. Leden 2015 začínal ve čtvrtek, do prvního týdne tak potřebujeme vepsat hodnotu 1. 1. 2015 a následně buňku naformátovat tak, aby nám zůstala zobrazená pouze jednička.

Abychom toho dosáhli, vytvoříme si nejprve pomocnou tabulku, která bude vypadat následovně.

  • Sloupec A: obsahuje data zvoleného roku. To zajistíme tak, že do buňky A2 vepíšeme vzorec =DATUM(G1;1;1) (první den, prvního měsíce, roku z buňky G1 – 2015). Buňka A3 přičítá k buňce A2 jeden den (=A2+1), další buňky následují. Tak vyplníme všechny dny roku.
  • Sloupec B: určuje, co za den v týdnu (pondělí, úterý...) je hodnota ze sloupce A. =DENTÝDNE(A2;2)
  • Sloupec C: určuje, v kolikátém týdnu den je =WEEKNUM(A2;2)
  • Sloupec D: pro první měsíc (leden) vrací hodnotu ze sloupce C, pro zbylé měsíce (od února do prosince) určí týden posledního dne předcházejícího měsíce (v únoru zjistí, že 31. leden byl v pátém týdnu). =KDYŽ(E2=1;C2;WEEKNUM(DATUM(ROK(A2);E2;1)-1;2))
  • Sloupec E: určuje, v kolikátém měsíci den je =MĚSÍC(A2)
Teď ještě v původní tabulce (kterou jsme tvořili jako první) upravíme zelená čísla označující týdny. První buňka nám vrátí číslo týdne prvního dne z vybraného měsíce, další buňky vždy jeden týden přičtou.

Základ máme připravený a můžeme se pustit do naplnění buněk samotné tabulky (červěne ohraničená část). K tomu využijeme funkci SOUČIN.SKALÁRNÍ. Ta nám pro každou buňku umožní najít datum z pomocné tabulky na základě zvolených kritérií. Vzorec vypadá následovně:

=SOUČIN.SKALÁRNÍ(($B$1:$B$367=L$1)*($C$1:$C$367=$H2)*($E$1:$E$367=$H$1);$A$1:$A$367)

Vše asi bude nejlépe patrné z následujícího obrázku (případně souboru, který jste si stáhli).
Co vzorec vlastně dělá? V případě prvního ledna hledá, ve kterém řádku pomocné tabulky je kombinace: prvního měsíce (ledna), čtvrtého dne týdne (čtvrtka) a prvního týdne. Pro tento řádek pak vrátí hodnotu ze sloupce A (výsledné datum).

No a to je celá alchymie. :-) Pokud jste neudělali chybu, dostaneme výsledek pro jeden měsíc. Následně stačí zkopírovat tabulky měsíců pro zbylých jedenáct měsíců, skrýt nežádoucí sloupce a řádky a tabulku vhodně naformátovat.

Formátování tabulky

(Je zobrazené pouze v původním souboru.)

Výsledný kalendář obsahuje několik formátů. Základem je formát čísla buňky (aby se nezobrazoval celý datum, ale pouze číslo dne v daném měsíci). Dále je v hojné míře využité podmíněné formátování

Jelikož výše zmíněná funkce SOUČIN.SKALÁRNÍ vrací hodnotu 0 (nula) v případě, že danou kombinaci nenalezne, je třeba to ošetřit. Je tak použito podmíněné formátování, které formátuje buňky jinak, pokud je buňka rovna nule.

Další podmíněné formátování hlídá (pomocí funkce SVYHLEDAT), zda se datum nenachází mezi důležitými daty, které si uživatel definuje v pravé části.

Závěr

Na první pohled či přečtení z toho máte určitě hokej a jde vám ze všech vzorců a logických posloupností hlava kolem. :-) V dalším díle se však podíváme, jaké další možnosti takto vytvořený kalendář nabízí a zdůvodníme si, proč jsme některé věci dělali tak či jinak...

Seznam dílů:
  • 1. díl: Šablona (5. 2. 2015)
    • Šablona pro roční kalendář s podmíněným formátováním na důležitá data
  • 2. díl: Jak to to funguje? (12. 2. 2015)
    • Jaké funkce jsou použité?
    • Co vlastně buňky zobrazují?
    • Jaké jsem použil podmíněné formátování?
  • 3. díl: Analýza dat v kalendáři (19. 2. 2015)
    • Jak na kalendář napojit jiná data?
  • 4. díl: Kontingenční tabulky (26. 2. 2015)
    • Jak vytvořit kalendář pomocí kontingenčních tabulek?
    • Jak jej napojit na data?
    • Jaké jsou výhody a nevýhody tohoto řešení?

Žádné komentáře:

Okomentovat