Értékek számítása adott időszakra (havi, negyedéves, éves)

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:

=ErtekSzamitas(A1:C25,F1,F2,F3)

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á:

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

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.

Ha kevés az időd és csak a videó érdekel, itt megtalálod.

Az alábbi adattáblából indulunk ki:

Példa 1:

Példa 2:

Példa 3:

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á:

Fejlesztési tippek:

A kapcsolódó videót itt találod:

Remélem hasznos volt a poszt.

Kérdésed, észrevételed van? Szólj hozzá lent vagy dobj egy emailt: xlmotyo@gmail.com

Értékek számítása adott időszakra (havi, negyedéves, éves)

Ha kevés az időd és csak a videó érdekel, itt megtalálod.

Az alábbi adattáblából indulunk ki:

Példa 1:

Példa 2:

Példa 3:

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á:

Fejlesztési tippek:

A kapcsolódó videót itt találod:

Remélem hasznos volt a poszt.

Kérdésed, észrevételed van? Szólj hozzá lent vagy dobj egy emailt: xlmotyo@gmail.com

Ajánló