további dátumkezelő függvények
2021-01-10 Excel 2019
kalk.dátum - hónap.utolsó.nap
Mindkét függvény egy dátumnál, megadott hónappal korábbi vagy későbbi dátumot képez. A KALK.DÁTUM magát a képzett dátumot, a HÓNAP.UTOLSÓ.NAP a képzett dátum hónapjának utolsó napját adja eredményül.
A két függvény argumentumai azonosak: az első a dátumot, a második a hónap-számot meghatározó konstans, hivatkozás vagy kifejezés. A KALK.DÁTUM( <dátum> ; <hónapszám> ) kifejezés csak látszólag azonos a DÁTUM( ÉV( <dátum> ) ; HÓNAP( <dátum> )+<hónapszám> ; NAP( <dátum> )) kifejezéssel!
Ahogy a képen látjuk a különbség csak a hónap utolsó napjára eső dátumoknál jelentkezik (középső példa). Az azonos eredményt adó, DÁTUM-os kifejezést az alsó példa mutatja. Mondjuk nem egy őrületes különbség, de azért körültekintőnek kell lennünk.
A képen a HÓNAP.UTOLSÓ.NAP függvény működését és a vele azonos eredményt adó DÁTUM-os kifejezést figyelhetjük meg.
munkanapok kezelése
Mielőtt elmerülünk a részletekben tisztázzuk a témakör fogalmait! Munkanapok: a hétköznapok és a szombatra áthelyezett munkanapok. Pihenőnapok: a hétvégék és az ünnepnapok. Áthelyezett munkanap: az ünnepnap és a hétvége közé beékelődő egyetlen munkanap „ledolgozására” kijelölt, az ünnepet megelőző valamelyik szombat. Munka: egy terv, amelyet cselekvéssel valósítunk meg. Munkavégzés: a cselekvés, amellyel a tervet megvalósítjuk.
A program függvényeivel három „munkanapos” számítást tudunk elvégezni. [1] Meg tudjuk határozni egy munkavégzés utolsó napját (határidő), a munkavégzés első napjának és a munka elvégzéséhez szükséges napok számának ismeretében. [2] Ki tudjuk számolni, melyik napon kell kezdeni (első nap) a munkavégzést a határidő (utolsó nap) és a munkavégzéshez szükséges napok számának ismeretében. [3] Meg tudjuk állapítani egy munkavégzés napjainak számát a munkavégzés első és utolsó napjának ismeretében. Természetesen mindhárom számításhoz rendelkeznünk kell az ünnepnapok és az áthelyezett munkanapok dátumaival.
Ez a két munkanap-kezelő függvény hivatalos leírása, de ahogy látjuk egyik sem veszi figyelembe az áthelyezett munkanapokat. Persze erre mondhatja a Microsoft, hogy nemzeti sajátosságokkal nem foglalkozik. De van még egy bibi: hétfőn reggel jön a mesterember kifesteni a konyhát. Kedd délután ötre végez vele. Akkor ez egy kétnapos munkavégzés. Nézzük, hogyan látják ezt a függvények!
Tehát a KALK.MUNKANAP hibás eredményt ad. Elmesélem, hogy miért. Az egyik függvényt jakutföldi Fegya programozta, akinek nagyon keveset kell fizetni, a másikat kalkuttai Rahul, akinek még kevesebbet kell fizetni, és csak az egyikük gondolt erre az apróságra. A bajt tetézte, hogy redmondi Alex, amikor ellenőrizni kellett volna őket, még el volt varázsolva az előző esti Michelin-csillagos vacsorától és az azt követő kokós eseményektől.
Ez lenne a KALK.MUNKANAP két funkciójának javítása. Az áthelyezett munkanapok figyelembe vétele már nagyobb falat.
Először az ÖSSZ.MUNKANAP-ra nézzünk egy komolyabb példát! Az ünnepnapok listájában nem szerepeltettem a hétvégére eső ünnepeket, mert azokat a függvény ünnep nélkül is munkaszüneti napnak veszi. Szerepeltettem viszont egy kamu ünnepet is, de csak azért, hogy az áthelyezett munkanapok „listája” legalább kételemű legyen.
Azoknak a munkáknak a celláit, amelyeknek az első vagy utolsó napja áthelyezett munkanapra esik aranysárgával színeztem.
A munkavégzés idejére eső szombatra áthelyezett munkanapok számát a DARABHATÖBB függvénnyel képeztem, amely azonos „méretű” és tájolású tartományokat vizsgál. Argumentumai párban állnak: tartomány1-feltétel1, tartomány2-feltétel2…Megfelelnek-e a tartományok cellái a hozzájuk tartozó kritériumnak. A tartományok azonos pozícióban álló celláit a függvény egy munkamenetben ellenőrzi, és azoknak a munkameneteknek a darabszámát adja eredményül, amelyekben minden cella megfelelt a saját feltételének. Ez a függvény működésének általános leírása. A mi esetünkben egyszerűbb a magyarázat: hány olyan dátum található az áthelyezett munkanapok listájában, amely az adott munkavégzés idejére esett, az első és az utolsó napot is beleértve.
Fordítsuk meg a példát! Vegyük konstansként a munkanapok számát és a munkavégzés első napjának ismeretében számoljuk ki a munkavégzés utolsó napját! Ha jól számolunk, akkor az eredmény azonos lesz az előző példában konstansként megadott utolsó nappal.
Az áthelyezett munkanapok figyelembe vétele az előző algoritmusban nem okozott különösebb gondot, de ebben az esetben már azt is számba kellett vennünk, hogy az utolsó nap egybeeshet egy áthelyezett munkanappal. A feladat megoldásának lépései a következők.
[UN.A] A munkavégzés utolsó napjának képzése az áthelyezett munkanapok figyelembe vétele nélkül. A KALK.MUNKANAP „napok száma” argumentuma értékének eggyel csökkentése a függvény hibás működésének javítása.
[ÁM] A munkavégzés idejére eső áthelyezett munkanapok száma. A KALK.MUNKANAP függvény eredményét a szombatra eső első munkanap nem befolyásolja!
[UN.B] A munkavégzés utolsó napjának képzése az áthelyezett munkanapok figyelembe vételével. A KALK.MUNKANAP „napok száma” argumentumának csökkentése az áthelyezett munkanapok darabszámával.
[UN.E] Az előző számítás nem százszázalékos, mert nem veszi figyelembe az utolsó nap és egy áthelyezett munkanap esetleges egybeesését. Vegyük a 007-es munkát. Az UN.A számítás 2021. július 5. hétfőt eredményez. Ha a „napok száma” argumentumból kivonjuk az áthelyezett munkanapok számát, az egyet, akkor a függvény az előző hét péntekét adja eredményül (2021. július 2.) az áthelyezett munkanap helyett (2021. június 3.). Ezért a DARABTELI függvénnyel ellenőrizzük kell, hogy az áthelyezett munkanapok listájában szerepel-e az utolsó napot követő nap. Ha szerepel, akkor a DARABTELI egyet ad eredményül, ha nem akkor nullát.
[UN.C] Az utolsó nap dátumának esetleges módosítása az ellenőrzés eredményével. Az UN.E számítás eredményét hozzáadjuk az UN.B számítás eredményéhez.
A KALK.MUNKANAP függvény másik funkciójának bemutatásához, ismét alakítsuk át a példa-feladatot: most a határidőt (utolsó nap) és a napok száma lesz konstans. Számoljuk ki mikor kezdődjön a munkavégzés!
A bonyolult képlet, annak köszönhető, hogy figyelembe kellett vennünk azt a lehetőséget, hogy a munkavégzés kezdése áthelyezett munkanapra is eshet. Tanulmányozzuk az algoritmust!
[EN.A] A munkavégzés első napjának képzése az áthelyezett munkanapok figyelembe vétele nélkül.
[ÁM] A munkavégzés idejére eső áthelyezett munkanapok száma. A KALK.MUNKANAP függvény eredményét ebben a számításban az áthelyezett munkanapra eső utolsó munkanap nem befolyásolja!
[EN.B] A munkavégzés első napjának képzése az áthelyezett munkanapok figyelembe vételével. A KALK.MUNKANAP „napok száma” argumentumának csökkentése az áthelyezett munkanapok darabszámával.
[EN.E] Az első nap számításnál azok a munkavégzések okoznak zavart, amelyek első napja áthelyezett munkanapra esik. Az EN.A számítás ezeknél a munkavégzéseknél pénteki dátumot eredményez. Az áthelyezett munkanapokkal kiegészített számítás (EN.B) viszont egy hétfői napot ad. Ezért szükséges az EN.B hétfők ellenőrzése: szerepel-e a dátumot megelőző második nap az áthelyezett munkanapok között.
[EN.C] Az első nap dátumának módosítása, ha az ellenőrzés egyet (IGAZ) ad eredményül: az EN.B számítás eredményéből levonunk kettőt.
Mindkét függvénynek van INTL-es változata, amelyek „egyéni” pihenőnapokkal is tudnak számolni.
hetek és napok sorszáma
Pontosabban a hetek sorszáma az évben, illetve a napok sorszáma a héten. Vegyük először a hetek sorszámozását. A számozás egyessel kezdődik és az első hét a… De, melyik is?
Ez nem kérdés! Hát a január elsejét tartalmazó hét. Igen-igen, de, ha mondjuk a január elseje szombatra esik, akkor az első hét kétnapos!? Ez hétnek elég csenevész! A probléma kezelésére vezették be azt a rendszert, amelyben az év első hete az a hét, amely legalább négynapos. A január elsejei verziót egyes rendszernek [1], a legalább négynapost kettes rendszernek [2] nevezzük. Az EU-ban a kettes rendszer használata az előírás.
Most már tisztáztuk, hogy melyik az év első hete, de azt még nem, hogy valójában mi szükségünk van erre a sorszámra. Statisztikák készítéséhez! „Készítsen egy diagramot az éves árbevételünkről Piroska, heti bontásban!” Tehát vannak dátumokhoz köthető bevételeink és meg kell állapítanunk a dátumokat tartalmazó hetek sorszámát…
Ennek meghatározásához két függvényt használhatunk, a HÉT.SZÁMA és az ISO.HÉT.SZÁMA függvényeket. A HÉT.SZÁMA mindkét rendszert alkalmazni tudja. Első argumentuma a dátum, a második a használandó rendszert deklaráló szám: egyes rendszer a kettes (2), a kettes rendszer a huszonegyes (21). Az ISO.HÉT.SZÁMA függvény csak a kettes rendszert „ismeri”. Ezért nincs is második argumentuma. Csak a hetet meghatározó dátumot kell megadnunk neki.
A legalább négynapos szabályt alkalmazó kettes rendszerben az elsejét tartalmazó hét csak akkor lesz az év első hete, ha elseje hétfőre, keddre, szerdára vagy csütörtökre esik. Ezeket az eseteket kék színnel formáztam.
A napok sorszámozásánál is felmerül a kérdésre, melyik naptól kezdődjön a számozás: vasárnaptól vagy hétfőtől. És az első sorszám nulla legyen vagy egy? Az EU-s előírás a hétfő és az egyes, de függvényünk a HÉT.NAPJA minden variációban működik. Első argumentuma a dátum, amelynek sorszámát keressük, a második a pedig a sorszámozás módját előíró szám. Az EU-s szabvány a kettes (2).
Állítsunk össze egy képletet, amely mindig a következő hét hétfőjének dátumát mutatja. Az első gondolatunk: sorszámok egytől hétig…, mindegyikkel más művelet…, VÁLASZT függvény. De, ha egy kicsit filózunk: mindig hét nap…, hány nap telt el a hétből…, hány nap van még hátra a hétből… nem kell a VÁLASZT.
A VÁLASZT függvény első argumentuma egy hivatkozás vagy egy kifejezés, amely egytől kezdődő sorszámot szolgáltat. A további argumentumai az egyes sorszámokhoz rendelt műveleteket határozzák meg. A tovább gondolt képlet a filózós képlet általánosítása. Ha a napról-napra változó MA() függvénnyel működik a képletünk, akkor tetszőleges dátummal is működnie kell.
margitfalvi.arpad@gmail.com
