Dátumfejléc automatizálása Excelben

Riportok készítése során mindig arra törekszem, hogy a lehető legkisebb energiaráfordítással frissíthetők legyenek az új adatokkal.

Ebben a cikkben egy újabb apró, bár nagyon hasznos „trükköt” fogok bemutatni, mely az automatizálást hivatott elősegíteni, azaz hogy miként kell úgy felparaméterezni a dátumfejlécet, hogy az dinamikusan változzon hónapról hónapra.

Tegyük fel, hogy a vezetőség tagjai egy olyan havi riportot várnak el tőlünk, mely mindig az előző 12 hónap értékesítési eredményeit tartalmazza.

Ehhez azt kell megoldanunk, hogy a riport dátum mezői mindig csússzanak egyet az előző havi állapothoz képest.

Első lépésben nézzük meg azt, hogy mi kell egyáltalán ahhoz, hogy az ÉVHÓ érték előállhasson.

=ÉV(MA())&HÓNAP(MA())

A képlet első fele az aktuális dátum évét, míg a második része a hónap sorszámát adja vissza eredményül, melyek összefűzésre kerülnek. 2018 szeptemberében például az adott képlet 20189-et ad vissza.

Következő lépésben szépítsünk kicsit az ÉVHÓ értéken, miszerint legyen egységesen 6 karakter hosszú minden hónapban.

=ÉV(MA())&HA(HÓNAP(MA())<10;"0"&HÓNAP(MA());HÓNAP(MA()))

Látható, hogy a hónapra vonatkozó rész kiegészült egy logikai művelettel, azaz ha a hónap sorszáma kisebb, mint 10, akkor kerüljön egy nulla a hónap sorszáma elé. Ennek hatására 201809 értéket kapunk eredményül.

A riportok rendszerint az aktuális hónapban az előző hónap adataival kerülnek kiegészítésre, ezért szükség van még némi módosításra a képleten.

=ÉV(MA())&HA(HÓNAP(MA())-1<10;"0"&HÓNAP(MA())-1;HÓNAP(MA())-1)

Az aktuális hónapból a képlet összes részében kivonásra került egy, biztosítva ezzel, hogy a riport készítésekor az előző hónap értékét kapjuk meg.

Az így összerakott képletet fogjuk használni az utolsó ÉVHÓ meghatározására.

A megelőző hónapok számítására pedig össze kell állítanunk egy olyan képletet, mely láncolatban egymásra hivatkozva adja vissza a korábbi hónapok ÉVHÓ értékeit, kezdve a láncot az előbb kalkulált utolsó ÉVHÓ mezőre hivatkozkozással, s amelyben az évváltás is lekezelésre kerül.

=HA(JOBB(L2-1;2)="00";(BAL(L2;4)-1)&"12";L2-1)

A HA függvény segítségével tulajdonképpen az került megfogalmazásra, hogy ha a láncolatban a hónap sorszáma eléri a „00”-t, akkor visszaugrik 12-re, egyébként pedig eggyel kerül csökkentésre az előző hónapéhoz képest.

Ez utóbbi függvényt visszafele végighúzva megkapjuk a fennmaradó 11 hónap ÉVHÓ értékét.

Azok kedvéért, akik vizuálisan is szeretik látni a megvalósítást:

Megjegyzés: Ha homályos a videó, érdemes állítani a képminőségen.

Címkék:
© 2017 AdatTérKép