dátum és időkezelés, profi eszközök

2021-01-24    Excel 2019
fogalmak
Dátumegységekkel végzett számítások előtt tisztáznunk kell, hogy milyen dátumegységekről van szó, naptári dátumegységekről vagy az adott dátumegység nap-számával azonos időszakokról. Vegyük például a heteket! Hány hetet tartalmaz a 2021.01.14. és 2021.01.28. közötti időszak.
a naptári hét és hét-periódus
A válasz: egy naptári hetet és két darab… Micsodát? Mondjuk, hét-periódust. A hét esetében nem okoz nehézséget a dátumegység nap-számának megadása, de az évek és a hónapok esetében már ez is kérdés lehet.
az év és a hónap-periódusok nap-száma
dátumegységek darabszáma
A napok darabszámának megállapítása nem probléma, de ne felejtsük az utolsó és az első nap különbségéhez hozzáadni egyet! Tehát napok száma: <utolsó nap dátuma>-<első nap dátuma>+1. A további dátumegységek darabszámának megállapítása sem okozhat nehézséget!
a naptári és a periódikus dátumegységek darabszámát meghatározó képletek
• A naptári hónapok összegét három tényező alkotja: az első nap évének naptári hónapjai, a naptári évek hónapjai és az utolsó nap évének naptári hónapjai.
•• A naptári hetek darabszámát a vizsgált időszak első hétfője és utolsó vasárnapja által meghatározott részéből képezzük.
A dátumegység-periódusok számát század pontossággal adtam meg.
törtév, dátumtólig
A program két függvényt kínál a fenti számítások alternatívájaként. A TÖRTÉV-et és a DÁTUMTÓLIG-ot. A TÖRTÉV az év-periódusok számát tudja meghatározni. Első és második argumentuma az első nap és az utolsó nap dátuma. A harmadik argumentuma az év-periódus nap-számát meghatározó szám. Ez az argumentum nem kötelező.
a TÖRTÉV függvény működése
• A háromszázhatvannapos éveket harmincnapos hónapok alkotják. Az USA és az EU előírásai a hónap-képzést eltérő módon szabályozzák, erre utal a 0-ás és a 4-es módszer jelzője.
•• Az 1-es módszer nap-számát az első nap éve, az utolsó nap éve és az időszak naptári évei nap-számainak átlaga adja.
A harmadik argumentumot a program „alap”-nak hívja. Szerintem a „kalap” jobb lett volna! Egyszerűen jobban hangzik, mert köze neki is körülbelül annyi van a nap-számhoz, mint az alapnak.
A DÁTUMTÓLIG függvény egy konspirációs számítási eszköz: a névkiegészítő nem tartalmazza, argumentum-listája titkos. Ezért, én is csak a témába-vágó funkcióit ismertetem. Bizalmasan!
a titkos DÁTUMTÓLIG függvény
Ezzel a függvénnyel egy időszak év és hónap-periódusainak számát határozhatjuk meg, de nem a nap-számok, hanem a határnapok dátumainak alapján. Első és második argumentuma az első és az utolsó nap dátuma. A harmadik argumentuma idézőjelek között áll. Ha éveket akarunk számoltatni, akkor egy ipszilon (year), ha hónapokat, akkor egy em (month). Először egy év-periódusos példát vegyünk!
év-periódusok darabszámának megállapítása a DÁTUMTÓLIG függvénnyel
A függvény az utolsó nap éveiből kivonja az első nap éveit és ha az első nap dátuma, az éveket figyelmen kívül hagyva, nagyobb, mint az utolsó nap dátuma, akkor levon belőle egyet. Ezt figyelhetjük meg a képen az első dátum-pár esetén: 2021-2019 » 2 és ebből le von egyet, mert január 17. nagyobb, mint január 16. A „-(<összehasonlítás>)” képlet-rész IGAZ logikai érték esetén mínusz 1-et, HAMIS esetén 0-t ad eredményül.
hónap-periódusok darabszámának megállapítása a DÁTUMTÓLIG függvénnyel
A hónapszám képlete két tényező összegéből áll. Az első tényezőt a teljes évek száma, amelynek kiszámítását az előző példában láttuk, és a 12 szorzata adja. A második tényező értékét egy feltétel határozza meg: kisebb-e az utolsó nap dátuma, az éveket figyelmen kívül hagyva, az első nap dátumánál. Az elágazás mindkét képlete tartalmazza a dátum napjainak összehasonlítását. A kiértékelés logikai értékét itt is számmá konvertálja a program, mint ahogy azt előző példában is láttuk.
A függvénynek van „napos” változata is, de arra nincs szükségünk, mert egy időszak nap-számát egyszerűen megállapíthatjuk a „<utolsó nap dátuma>-<első nap dátuma>+1” kifejezéssel.
dátum egyéni megjelenítése
Saját dátum-formátumot a Cellaformázás, Szám, Kategória Egyéni utasítással deklarálhatunk.
egyéni dátum-formátum megadása
A Formátumkád mezőbe kell kattintanunk, majd miután letöröltük a Normál bejegyzést, meg kell adnunk a kívánt megjelenítés kódját. Az elválasztó karakterek (.:-/<szóköz>) közvetlenül beírhatók a kódba.
egyéni dátum-formátum megadására szolgáló karakterek
• Megjelenítés a szükséges számú karakterrel: a tíz alattiak egy, a többiek két karakterrel.
A táblázatban felsorolt kódok, az elválasztó karaktereket is beleértve, tetszőleges sorrendben és tetszőleges összeállításban használhatók.
egyéni dátum-formátumok
A legfontosabb szempont a jó ólvashatóság! Minden formai elemnek ezt a célt kell szolgálnia: oszlopszélesség, sormagasság, vízszintes és függőleges igazítás… A program standard dátum-formátuma (2021.01.21) nem elég „szellős”, ezen mindenképpen javítanunk kell! Mindenkinek ízlése szerint.
A jó olvashatóság érdekében, ne zsúfoljuk össze egyetlen cellába az összes szükséges dátum-elemet! Hozzunk létre segéd-oszlopot. Hivatkozzunk a dátum cellájára és adjuk meg a szükséges megjelenítési kódot vagy használjuk a SZÖVEG függvényt.
dátum-elemek szétosztása több cellába
A megadott dátumot (első argumentum) a függvény a megadott formátumban (második argumentum) szövegként jeleníteni meg. A megjelenítési kód idézőjelek között áll.
idő egyéni megjelenítése
Az idő egyéni formátumkódját is a Cellaformázás, Szám, Kategória, Egyéni utasítással deklaráljuk. Az elválasztó karaktereket (.:-/<szóköz>) közvetlenül beírhatjuk a kódba.
egyéni idő-formátum megadására szolgáló karakterek
• Megjelenítés a szükséges számú karakterrel: a tíz alattiak egy, a többiek két karakterrel.
A program huszonnégyórás idő-rendszert használ de előírhatunk tizenkétórás, napszak-jelölős formátumot is.
12 órás idő-formátum
• A napszak-jelölőt az időpont elé helyező formátum. Ne hagyjuk el a szögletes zárójeles kód-részt, mert annak hiányában, a kívánt megjelenítés (du. 5:03) csak időleges! A munkafüzet következő megnyitásakor a napszak-jelölő angolul fog megjelenni (PM 5:03).
A mindennapi életben gyakran felmerülő igény, hogy a napok órákban vagy az órák percekben jelenjenek meg. Ezt a feladatot is egyéni formátum deklarálásával tudjuk megoldani. Bemutatom egy példán: négy ember öt nap alatt…
napok megjelenítése órákban
Tehát a napokat órákban akarom megjeleníteni. Ez nem probléma, hiszen a napi munkavégzés formátumát (ó:pp) a program automatikusan érvényesíti az összeg-sorra. De az eredmény láthatólag nem jó. Az összegeket konstanssá alakítottam és ellenőriztem a szerkesztősorban. Ez látszik a képen.
Ha összeadjuk Léna óráit 31-et kapunk. Van még kétszer 30 perce, akkor ez újabb egy óra. Összesen 32 óra. Ha ezt elosztjuk 24-el 1 napot kapunk. Ez az érték 1900.01.01-nek felel meg. Az osztási maradék a 8 óra. A szükséges kód a „[ó]:pp” lesz. A szögletes zárójel arra utasítja a programot, hogy az óránál nagyobb dátumegységeket váltsa át órára és adja hozzá az eredeti óraszámhoz.
Hasonló történik a 3:12:24 standard megjelenítésű idővel is, miután deklaráltuk a „[p]:mm” kódot. Van 3 óránk. Ez 180 perc. Ehhez kell még hozzáadnunk az eredeti percszámot, a 12-t. Így 192:24-t kapunk.
dátumok képleten alapuló feltételes formázása
A művelet a formázandó cellák kijelölésével kezdődik. A legtöbb esetben nem csak a dátumot, hanem az őt tartalmazó sort is ki akarjuk emelni, ezért az oszlop-címkék kivételével jelöljük ki a teljes táblázatot. Ezután megnyitjuk a képlet deklarálására szolgáló parancstáblát: Kezdőlap, Stílusok, Feltételes formázás, Új szabály…, A formázandó cellák kijelölése képlettel.
A formázó-képletnek logikai értéket kell eredményeznie! Ha az eredmény IGAZ, akkor a program formázza a cellát, ha HAMIS, akkor nem. Az Excel a formázó-képletet a munkalap-képlettel megegyező módon kezeli: elhelyezi (képletesen) az aktuális cellába, majd átmásolja (képletesen) a többi kijelölt cellába is.
dátumok feltételes formázása
A képen az aktuális dátum sorát kiemelő feltételes formázás látható. A formai jegyeket a Formátum… vezérlőre kattintva adhatjuk meg.
Feltételes formázás tulajdonságainak módosítása illetve a formázás törlése, az érintett cellák kijelölése után, a Feltételes formázás szabálykezelője feliratú parancstáblán történik, amelyet a Kezdőlap, Stílusok, Feltételes formázás, Szabályok kezelése… utasítással jeleníthetünk meg.
a feltételes formázás tulajdonságainak módosítása a szabály törlése
Akkor most nézzünk néhány formázó-képletet. A válogatás teljesen esetleges, hiszen olyan sok a variáció…
dátumok feltételes formázása képlettel
Több formázó-képletben szerepel a PLAFON.MAT függvény, amely az első argumentumával meghatározott számot a második argumentumának többszörösére, felfelé kerekíti.
margitfalvi.arpad@gmail.com