OpenOffice.org Calc pod drobnohľadom (10) - Dátumové a časové funkcie

Pri množstve tabuliek sa nezaobídeme bez sledovania dátumových alebo časových údajov. Ako príklad nemusí byť iba kalendár, ale aj napríklad tabuľky, kde môžeme sledovať povedzme elektrickú spotrebu, spotrebu vody a pod. s výpočtom odhadovanej spotreby na konci zúčtovacieho obdobia.

Pri všetkých takýchto tabuľkách sa nezaobídeme bez výpočtu napríklad počtu dní medzi dvomi dátumami, poradového čísla dňa, v prípade sledovania časov napríklad počtu odpracovaných hodín a pod. Pretože nemá zmysel, aby sme si vysvetľovali všetky funkcie podrobne, ukážeme si použitie dátumových funkcií na príklade tvorby kalendára.

Kalendár

Predpokladajme, že si chceme vytvoriť kalendár s tým, že zadáme iba príslušný rok a všetky ostatné údaje sa nám vypíšu automaticky sami. Preto, aby sme toto dokázali urobiť potrebujeme zistiť minimálne dve veci – číslo dňa, v ktorom začína príslušný mesiac zadaného roku a počet dní príslušných mesiacov. Druhý údaj je jednoznačný pre všetky mesiace okrem februára, kedy musíme zistiť, či rok je alebo nie je priestupný.

Odkaz na zadaný rok v pomocnom liste

Tieto údaje si zadáme do pomocného listu (pomenujme ho „Pomocne“). Predpokladajme, že rok máme zadaný v bunke A1 (v skutočnosti si tam uložíme iba odkaz na bunku do listu „Kalendar“, kde budeme zadávať rok, pre ktorý tvoríme kalendár). Najprv si musíme z čísla roku vytvoriť dátumy prvých dní v mesiaci. Na toto použijeme funkciu DATE:
DATE(rok;mesiac;deň)

Takže v našom prípade získame príslušné dátumy funkciami:
DATE($A$1;1;1), DATE($A$1;2;1), DATE($A$1;3;1) až DATE($A$1;12;1).

Poradové číslo dňa

Poradové číslo príslušného dňa sa dá zistiť pomocou funkcie WEEKDAY:
WEEKDAY(dátum;typ)

pričom typ určuje, ako sa počíta prvý deň v týždni. Pri hodnote 1 sa počítajú od 1 od nedele, pri hodnote 2 sa počítajú od1 od pondelka a pri hodnote 3 sa počítajú od 0 od nedele. Pre nás bude teda vyhovovať typ 2. Pre prehľadnosť si napíšeme do stĺpca A názvy mesiacov a do stĺpca B zadáme nasledovné vzorce:

=WEEKDAY(DATE($A$1;1;1);2)
=WEEKDAY(DATE($A$1;2;1);2)
…
=WEEKDAY(DATE($A$1;12;1);2)

Poradové číslo (deň) dátumu

Do stĺpca C zadáme počet dní príslušného mesiaca. Pre zistenie posledného dňa v mesiaci február môžeme použiť v podstate dva vzorce. Funkcia EDATE umožňuje zistiť dátum, ktorý nasleduje o zadaný počet mesiacov (pred) po počiatočnom dátume:
EDATE(počiatočný dátum, počet mesiacov).

V našom prípade by sme teda mohli zadať takýto vzorec:
EDATE(DATE($A$1;1;31);1)

a následne z neho zistiť číslo dňa pomocou funkcie DAY:
DAY(EDATE(DATE($A$1;1;31);1)).

Počet dní vo februári (prestupné roky)

Táto funkcia má tú nevýhodu, že nepočíta s dňami. Pretože január má viac dní ako február, vráti nám však správny výsledok. Pre prípad, že potrebujeme zistiť počet dní iných mesiacov je však nevyhovujúca a lepšia je funkcia EOMONTH, ktorá vracia ako výsledok posledný deň mesiaca, ktorý nasleduje o zadaný počet mesiacov (pred) po počiatočnom dátume:

EOMONTH(počiatočný dátum, počet mesiacov).

Preto do bunky C3, kde chceme mať uložený počet dní mesiaca február zadáme vzorec:
=DAY(EOMONTH(DATE($A$1;1;1);1))

Počet dní v mesiaci február

Teraz máme pripravené všetky potrebné podklady na tvorbu vlastného kalendára. Na liste „Kalendar“ si pripravíme zoznam mesiacov, pričom musíme počítať s tým, že mesiac môže byť až v šiestich týždňoch (napríklad marec 2008).

Vlastný kalendár

Podmienka pre pondelok prvého týždňa v mesiaci

Pretože musíme zaistiť, aby sa nám začali vypisovať dátumy až od toho dňa, kedy mesiac skutočne začína, musíme testovať, či vypočítaný prvý deň v mesiaci už nastal, alebo nie. Preto v prvom riadku príslušného mesiaca zadáme nasledovné podmienky (príklad je pre január, kde máme počet dní v bunke B2 na liste „Pomocne“):

Podmienka pre prostredné dni prvého týždňa v mesiaci

Pondelok (bunka A5) =IF(Pomocne.$B$2=1;1;"") – ak začína mesiac v pondelok, tak vypíšeme číslo 1, inak ponecháme bunku prázdnu.
Utorok (bunka B5) =IF(Pomocne.$B$2<=2;A5+1;"") – ak začína mesiac v utorok alebo skôr, tak zväčšíme číslo dátumu, inak bunku ponecháme prázdnu. V tomto prípade využívame to, že prázdna bunka sa správa ako číslo 0. Toto opakujeme až po sobotu:
Streda (bunka C5) =IF(Pomocne.$B$2<=3;B5+1;"")
Štvrtok (bunka D5) =IF(Pomocne.$B$2<=4;B5+1;"")
Piatok (bunka E5) =IF(Pomocne.$B$2<=5;B5+1;"")
Sobota (bunka F5) =IF(Pomocne.$B$2<=6;B5+1;"")
Nedeľa (bunka G5) =F5+1 – pretože mesiac musí začínať v niektorý deň, pokiaľ nezačal po nedeľu, musí začať v tento deň a preto tu testovanie už nemá zmysel.

Nedeľa prvého týždňa v mesiaci

Následne musíme zadať ostatné riadky. V nich však musíme testovať zároveň aj maximálny počet dní (pre január máme tento údaj v bunke C2 na liste „Pomocne“), aby sme správne ukončili príslušný mesiac. Počet dní vlastne zväčšujeme o 1 oproti predchádzajúcej bunke s tým, že pre pondelok (stĺpec A) musíme pripočítať jednotku k nedeli (stĺpec G):

=IF(G5<Pomocne.$C$2;G5+1;"")

Výpočet pondelku ostatných týždňov v mesiaci

a pre ostatné dni (stĺpec B až G) k predchádzajúcemu stĺpcu:

=IF(A6<Pomocne.$C$2;A6+1;"")
=IF(A6<Pomocne.$C$2;B6+1;"")
…
=IF(F6<Pomocne.$C$2;F6+1;"")

Výpočet ďalších dní v mesiaci

Tieto vzorce môžeme zadať v jednom riadku a potom nám ich stačí nakopírovať do zvyšných možných týždňov príslušného mesiaca.

Podobne postupujeme aj pre ostatné mesiace. Musíme si však uvedomiť, že používame absolútne adresovanie počiatočného dňa v mesiaci a absolútne adresovanie počtu dní, preto musíme byť pozorní (pre február používame bunky Pomocne.$B$3 a Pomocne.$C$3 atď.).

Úprava vzorcov pre ďalší mesiac

Nakoniec si kalendár podľa svojich požiadavok naformátujeme – orámujeme, nastavíme farby, vyberieme vhodné písmo a jeho veľkosť, upravíme šírky buniek, zlúčime bunky s nadpismi mesiacov do jednej… a kalendár si môžeme vytlačiť. Pravdaže, kalendár je iba jednoduchý, neobsahuje sviatky, to si však môže každý doplniť podľa svojej ľubovôle.

Náhľad na hotový kalendár

Nabudúce: Jednoduché makro pre formátovanie buniek.

Stránky OpenOffice.org: www.openoffice.sk