Ha kevés az időd és csak a videó érdekel, itt megtalálod.
A riportolás rendkívüli fontossága nehezen vitatható. Ha az adatokat megfelelően alakítjuk, a megfelelő számításokat végezzük el és még a prezentálás is strukturált, érdekes, és interaktív… nos akkor nagyon nem nyúlhatunk mellé.
Eddigi munkáim során számtalan riportot, adatbázist kellett létrehoznom, átalakítanom vagy éppen folyamatosan fejlesztenem, frissítenem. Óriási könnyebbséget jelentett, mikor már saját makró formulákat (angolul UDF vagyis User Defined Function) tudtam írni. A lenti minta továbbfejlesztett változatait jó párszor alkalmaztam. Segítségével rugalmasan ki tudjuk számolni a havi, negyedéves, éves értékeket egy adott időszakra, a megadott KPI-ra.
Az alábbi adattáblából indulunk ki:
Példa 1:
Tegyük fel, hogy a „KPI01”-nél akarjuk a „Július” havi összesítést kiszámolni (sárga cellák a fenti képen). Ezt az „ErtekSzamitas” elnevezésű makró formulával így tehetjük meg:
Az eredmény 251, ami korrekt.
Vagyis egy üres cellában elkezdjük begépelni a formula nevét („ErtekSzamitas”) majd kitöltjük a paramétereket hasonlóképpen, mint egy beépített Excel formulánál. Így néz ki a képlet:
|
1 |
=ErtekSzamitas(A1:C25,F1,F2,F3) |
Nagyban megkönnyíthetjük a dolgunkat, ha a formula paramétereit ún. segédtáblából vesszük, hiszen innentől elegendő ennek a táblának az adatait módosítani. A módosítás pedig még könnyebb és hibamentesebb, ha a legördülő listát adunk hozzá:
A legördülő lista az F1, F2 és F3-as cellákban, míg az eredményt kiíró makró formula az F4-ben található.
Ezt követően kizárólag a zöld cellákat kell módosítanunk az F1:F3 tartományban és az eredmény automatikusan frissül az F4-ben.
Példa 2:
A „KPI03”-nál szeretnénk összegezni a negyedik negyedévet (mely októbertől decemberig tart): ezek a szürke háttérszínű cellák a poszt első képén. Megoldás:
Az eredmény 537, ami szintén helyes.
Pár kattintás az egész, a makró formulát itt nem kell manuálisan frissíteni.
Példa 3:
A „KPI02”-nél kell kiszámolnunk az éves összesítést, vagyis minden hónapot figyelembe kell vennünk: ezek a narancssárga háttérszínű cellák a poszt első képén. Megoldás:
Az eredmény 643, korrekt.
Ha nincs érték a megadott KPI/Hónap párosítással: a formula “Nincs eredmény”-t ír ki. A hibaüzenet tetszés szerint módosítható. A nem numerikus értékeket a “C” oszlopban nem veszi figyelembe
A makró formula abban a munkafüzetben érhető el, amelyhez hozzá lett adva.
Az adott munkafüzetben nyissuk meg a Visual Basic szerkesztőjét (ALT + F11-gyel). Majd szúrjunk be egy modult és az alábbi kódokat adjuk hozzá:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
Function ErtekSzamitas(Tart As Range, KPI As String, ElsoHonap As String, UtolsoHonap As String) Dim i As Long, Ertek 'Készítette: XLMotyo (https://xlmotyo.hu) 'ez a makró formula (UDF, azaz User Defined Function) az általunk megadott KPI/Hónap alapján kiszámolja 'a havi/negyedéves/éves értékeket '- ha az érték 0: kiírja. Ha nincs érték a megadott KPI/Hónap párosítással: "Nincs eredmény"-t ír ki '- a nem numerikus értékeket a "C" oszlopban nem veszi figyelembe 'KPI mezõ nem lehet üres: If KPI = "" Then ErtekSzamitas = "A KPI mezõ nem maradhat üresen!" Exit Function End If 'ElsoHonap és UtolsoHonap nem lehet üres: If ElsoHonap = "" Or UtolsoHonap = "" Then ErtekSzamitas = "Az elsõ és utolsó hónap nem maradhat üresen!" Exit Function End If 'Ertek kiszámítása a megadott KPI illetve ElsoHonap/UtolsoHonap alapján: For i = 2 To Tart.Rows.Count If Tart.Cells(i, 1) = KPI And Application.IsNumber(Tart.Cells(i, 3)) And _ Hanyadik(Tart.Cells(i, 2)) >= Hanyadik(ElsoHonap) And Hanyadik(Tart.Cells(i, 2)) <= Hanyadik(UtolsoHonap) Then Ertek = Ertek + Tart.Cells(i, 3) End If Next i If Not IsEmpty(Ertek) Then ErtekSzamitas = Ertek Else ErtekSzamitas = "Nincs eredmény" End Function Function Hanyadik(AthozottHonap As String) As Integer Dim i As Long, Honapok() 'ez a kód kiszámolja, hogy a fõ kódból ("Function ErtekSzamitas") áthozott hónapok hányadik elemei a "Honapok" tömbnek Honapok = Array("Január", "Február", "Március", "Április", "Május", "Június", _ "Július", "Augusztus", "Szeptember", "Október", "November", "December") For i = LBound(Honapok) To UBound(Honapok) If AthozottHonap = Honapok(i) Then Hanyadik = i Exit Function End If Next i End Function |
Fejlesztési tippek:
– tartományt dinamikussá alakítani (ha sort adunk hozzá vagy törlünk: ne kelljen a makró formulát módosítani)
– szumma helyett pl. átlag kiszámítása
– kettő vagy több KPI alapján történő összesítés
– a hónap mellett a nap/év hozzáadása
– hónapok hozzáadása több évre visszamenőleg és az adott év/hónap értékeinek kiszámítása adott időszakra
– hónapok helyett pl. 1. negyedévet hozzáadni a formulához (1. negyedév tartalmazza a január, február és március hónapokat)
– más kezdő hónapot megadni egy éven belül: egyes cégeknél a pénzügyi év nem januárban, hanem pl. áprilisban kezdődik. Vagyis az 1. negyedév ez esetben NEM januártól márciusig, hanem áprilistól júniusig tartana.
