dátum és időkezelő függvények
2020-12-30 Excel 2019
fogalmak
A dátum alkotóelemei sorrendben az évek, a hónapok és a napok. A három dátumegység sorszáma adja a dátumot. Például: időszámításunk kétezer-tizedik évének, negyedik hónapjában, a hónap tizedik napján Szmolenszk repülőtere közelében…
Az idő is három alkotóelemből épül fel. Az időegységek sorrendben: órák, percek és másodpercek. Itt is sorszámokról van szó! A példát folytatva: a fenti nap tízedik órájában, a tízedik óra negyvenegyedik percében, a negyvenegyedik perc tizenhetedik másodpercében adta utolsó rádióüzenetét…
A dátum- és az időegységekkel végzett műveletek során nem a „sorszám” kifejezést használjuk, hanem egyszerűen a dátum és időegységek számáról beszélünk, darabszám értelemben. A dátum és időegységek kezelésére öt függvény-pár áll rendelkezésünkre.
dátum és időegységek számának megállapítása
A leggyakoribb és legegyszerűbb művelet ebben a témakörben. Függvényei az ÉV, HÓNAP és a NAP. A függvények egyetlen argumentuma egy dátum vagy dátumnak megfeleltethető szám, amely lehet konstans, hivatkozás vagy kifejezés. Időegységek számának megállapításához az ÓRA, a PERCEK és a MPERC függvényeket használhatjuk. Egyetlen argumentumuk a konstansként, vagy hivatkozásként, vagy kifejezésként megadott idő, illetve időnek megfeleltethető szám. Figyelem, figyelem! A hat függvény neve közül öt egyes számban áll, de a második időegység számát adó függvény neve többes számú: PERCEK!
Nézzünk néhány tipikus példát a függvények alkalmazására. Szeretnék egy táblázatot, amelyben folyamatosan követni tudom a kollégáim életkorát a születési dátumuk ismeretében.
Ha megkérdik, hány éves vagyok, akkor veszem az aktuális évszámot és kivonom belőle a születési évemet… Így számoltam a C oszlopban. De az életkoromat úgy is meghatározhatom, hogy a megélt napjaimat elosztom 365,25-tel és veszem a hányados egész részét. Ezt a számolási módot alkalmaztam a D oszlopban. A KVÓCIENS függvény két argumentuma az osztandó és az osztó. Eredménye pedig a hányados egész része. A négy évenként előforduló szökőnapot is figyelembe kell vennünk: ezért számoltam 365,25-dal és nem 365-tel.
A „napos” számítás két kollégát fiatalabbnak mutat, mint az „éves” számítás. Mi ennek az oka? Egyszerű a magyarázat. Ezt az ismertetőt december 23-án írom, tehát Szakács Leonóra és Csordás Bíborka születésnapja (december 30. és december 27.) még odébb van… Tehát a „napos” számítás a pontosabb.
A következő példában a HÓNAP függvény segítségével megállapítjuk egy dátumról, hogy melyik félévbe és melyik negyedévbe esik.
A félév számításakor a dátum hónapszámát hattal osztottam, majd a hányadost felfelé egészre kerekítettem. Az eredményt a RÓMAI függvénnyel alakítottam át, majd szöveg-összefűző operátorral egy ponttal egészítettem ki.
A negyedévek száma a dátum hónap-száma és a három egészre, felfelé kerekített hányadosával egyenlő. Az egészre kerekítést a KEREK.FEL függvény második argumentumának nulla értéke biztosítja.
A következő példában ki kell számolnunk, hány teljes hónap telik el két dátum között. Az ÉV és a HÓNAP függvényeket fogjuk használni.
Az eredmény három részből tevődik össze. [1] A kezdő-dátum évének még hátralévő teljes hónapjainak száma. Vegyük a 001 azonosítójú sort: a kezdő dátum februári, akkor még tíz teljes hónap van hátra az évből. Tehát: 12-HÓNAP(B2).
[2] A két dátum közötti teljes évek hónapjai. Először kiszámoljuk a két dátum közötti teljes éveket. Most is a 001-es adatokat nézzük! A két dátum évei között három a különbség, de ebből csak kettő a teljes. Tehát a hónapok száma: (ÉV(C2)-ÉV(B2)-1)*12.
[3] A záró-dátum évének teljes hónapjai a záró-dátumig. A 001-es sor záró dátuma februári, tehát egyetlen hónap telik el a záró-dátumig. A kifejezés, akkor így alakul: HÓNAP(C2)-1.
Vegyünk egy „idős” példát is. Dolgozók napi fizetését kell meghatározni a kezdés és a befejezés idejének ismeretében. Minden megkezdett negyvenöt percért jár hétezer Forint!
Az első gondolatunk a dátumoknál bevált műveletek megismétlése: kiszámítjuk a két időpont közötti perc-számot az ÓRA és a PERCEK függvényekkel, azután elosztjuk negyvenöttel… De, ahogy a képen látjuk, erre nincs szükség. A képletben időre hivatkozunk, konstansként időt határozunk meg és nem foglalkozunk időegységekkel! A KVÓCIENS függvény argumentumai az osztandó és az osztó, eredménye a hányados egész-része.
dátum és idő képzése alkotóelemeikből
Ha megnézzük az idetartozó két függvény argumentum-listáját, nehezen tudjuk elképzelni, milyen probléma megoldásában tudjuk majd őket használni. DÁTUM( évek száma ; hónapok száma ; napok száma ). IDŐ( órák száma ; percek száma ; másodpercek száma ).
A függvények argumentumai lehetnek konstansok, hivatkozások és kifejezések. A két függvény mindig kronológiailag helyes eredményt ad! De hogyan! Vizsgáljuk meg a két függvény argumentum-kezelését!
Foglaljuk össze a tapasztalatainkat. Az évek száma tehát nem lehet negatív. Ha az évek száma kisebb mint ezerkilencszáz, akkor hozzáadódik ezerkilencszázhoz.
Ha a hónapok száma kisebb mint egy, akkor levonódik az előző év decemberéből. Ha nagyobb tizenkettőnél, akkor a program elosztja tizenkettővel és a hányados egész része az évek számát növeli, míg a maradék a képzett dátum hónapszáma lesz. Utóbbi természetesen csak akkor, hogy ha a napok száma argumentum nem módosítja.
Ha a napok száma kisebb mint egy, akkor levonódik az előző hónap utolsó napjából, ha nagyobb mint az adott hónap utolsó napja, akkor a többletből újabb hónapok képződnek.
Ahogy a képen megfigyelhetjük az IDŐ függvény argumentum-kezelése azonos logikai rendszert követ, mint a DÁTUM függvény argumentum-kezelése.
Most nézzünk néhány példát a két függvény alkalmazására! Egy börtönigazgató Excel táblázatban számolja ki az elitéltek szabadon bocsájtásának napját.
Vagy, ha ez a példa túl „távoli” lenne, akkor vegyünk egy bankos feladatot! Számítsuk ki lekötött betétek lejáratának dátumát!
Készítsünk képletet, amely kiszámolja, hogy még hány nap van hátra az aktuális negyedévből! A képlet eredménye napról-napra változzon!
A negyedévek utolsó napjának sorszáma változó. Vagy harminc, vagy harmincegy. Ezért a DÁTUM függvénnyel a negyedévet követő hónap első napját képeztem, mert az mindig egy. Tehát hozzáadtam egyet a negyedév utolsó hónapjának sorszámához. Ezután a DÁTUM függvény által képzett dátumból kivontam egy napot.
Nézzünk egy-két példát az IDŐ függvényre is. A B oszlopban az „eredeti” időt kell a másodperceitől megfosztani, a C-ben pedig, a csonkolt időt hat perc pontoságra kerekíteni.
A TÖBBSZ.KEREKÍT függvény az első argumentumával meghatározott számot a második argumentumával megadott szám többszörösére kerekíti.
dátum és idő képzése karakterláncból
A művelethez a kétfunkciós DÁTUMÉRTÉK és IDŐÉRTÉK függvények állnak rendelkezésünkre, de csak egyik működésükben képzik, a másikban csak deklarálják a dátumot illetve az időt. Lássuk!
Egy vállalat dolgozói öt évente hűség-jutalmat kapnak. A jutalom minden év július 14-én esedékes. Ki kell számolnunk a következő jutalmazott évfordulót és esedékességének napját a munkatársak azonosítója alapján! Az azonosító két részből áll. A belépési évszámból, évszázad és vezető nulla nélkül, valamint egy háromkarakteres sorszámból.
A C oszlop képletében először az aktuális év sorszámát képeztem évszázad nélkül: ÉV( MA() ) - 2000. A belépés dátumát úgy kaptam, hogy az azonosítót osztottam ezerrel és a hányados egész részét vettem: KVÓCIENS( A2 ; 1000 ). Ennek a két számnak a különbségét felfelé ötre kerekítettem: PLAFON.MAT( <különbség> ; 5 ).
Az esedékesség napját a D oszlopban számoltam ki. Először a belépés, évszázadok nélküli, évszámához hozzáadtam a következő jutalmazott évfordulót: KVÓCIENS( A2 ; 1000 ) + C2. Az így kapott számot fűztem össze az esedékesség napjának karakterláncával: <a következő jutalmazott évforduló évszáma, évszázadok nélkül> & ”-7-14”. Ezt a kifejezést adtam meg a DÁTUMÉRTÉK függvény argumentumaként.
A képlet eredménye a dátumnak megfeleltetett egész szám, amelyet cellaformázással dátumként kell megjelenítenünk.
Az IDŐÉRTÉK függvény idő-képzős működése azonos logikát követ. Például: IDŐÉRTÉK( C2+10 & ”:00” ). Logikai kifejezésben a dátum és az idő csak ennek a két függvénynek az argumentumában szerepelhet. Ez a függvények deklarációs funkciója: „ez a karakterlánc egy dátum” vagy „ez a karakterlánc egy idő”.
A függvény nélküli, de szabályos formátumú dátum vagy időkonstanst tartalmazó összehasonlítást a program simán kiértékeli! Csak nem úgy, ahogy mi gondoljuk!
margitfalvi.arpad@gmail.com
