Párbeszéd részlet egy kollégámmal:

– Lehet olyat csinálni, hogy a kimutatásba az értékmezőt egy szeletelőből rakom be?
– Nem.
– Kár… Pedig milyen jó lenne abba a kimutatásba, ahol a választott mutatószám időbeli trendjét mutatjuk. A felhasználók nem nagyon szeretik a jobb oldali Mezőlistát megjeleníteni és abban berakni a mutatószámot.
– Ja… erre tök jó lenne… hm…. tulajdonképpen meg is lehetne csinálni. Ez tök jó ötlet!

Pár perc múlva már kész is volt. 🙂 Itt a minta fájl és egy kép belőle:

Tehát a felső szeletelőben lehet meagdni az alsó kimutatás mutatószámát. Ha (Ctrl lenyomásával) többet választunk, mind meg is jelenik! 🙂

Fontos: Az itt bemutatott megoldás hagyományos kimutatásokhoz készült. A PowerPivot-tal használható verziót lásd a folytatásban.

Nem kezdőknek való megoldás, de elmagyarázom, hogyan kell csinálni.

Szükség lesz pár sornyi VBA kódra, egy segéd táblára és egy segéd kimutatásra. Aki gyakorlott kimutatások használatában, és nem idegenkedik a VBA szerkesztőtől, annak nem lesz nehéz felépíteni ezt a kényelmes kis eszközt.

Adattábla

Kékkel jelöltem az érték oszlopokat (ezek a mutatószámok) – ezekből lehet majd szeletelő segítségével választani. (Nem szükséges, hogy egymás mellett legyenek.) Ebből az adattáblából készül az az alap kimutatás, amihez majd a trükkös szeletelőt kapcsoljuk.

A VBA kódban felhasználjuk majd a kimutatás nevét. Ezt a menüben találod meg, a Kimutatáseszközök / Beállítások (Pivot table tools / Analyze) alatt a bal oldalon. Az én fájlomban PivotTable1 a név.

Mutatószám lista, kimutatás és szeletelő

Egy új, üres munkalapon a mutatószámok neveiből csinálj egy kis táblázatot, majd ebből készíts egy kimutatást. A mutatószám mező kerüljön a sorcímkékhez. Vedd le a végösszegeket (grand total) a kimutatásról! Adj hozzá egy szeletelőt a kimutatáshoz: Kimutatáseszközök / Beállítások / Szeletelő beszúrása (Pivot table tools / Analyze / Insert Slicer). A szeletelőt helyezd át az alap kimutatás fölé.

(Természetesen nem kötelező minden mutatószámot felvenni a listába – ez a megoldás arra is jó lehetőség, hogy szűkítsd a választható mutatók körét az adott kimutatás céljának megfelelően!)

Jegyezd meg a munkalap nevét, ahova a kis kimutatást tetted. Nálam Szeletelo a neve.

Elnevezett cella

A mutatószám kimutatás első celláját (amiben az első – kiválasztott – mutatószám neve olvasható) nevezd el choice-nak (vagy másnak, csak jegyezd meg a nevet! :-)) (Az elnevezés munkafüzet hatókörű legyen.)

A szeletelőben választott első érték mindig itt jelenik majd meg, ha többet választunk, akkor azok alatta lévő cellákban lesznek.

VBA kód

A VBA Editort Alt+F11 billentyűkombinációval éred el. A kódot a Szeletelo munkalap (vagyis ahol a kis mutatószámos kimutatásod van) kód moduljába kell írni – ezt a Project Explorer ablakból (jobb oldalon) tudod megnyitni:

És a kód:

(A kimásolható kódot a cikk alján találod.)

Az ábrán látod, hogy milyen konstansokkal dolgozik a kód – ezeket kell átírnod, ha nálad mások az elnevezések.

S hogy mi történik itt? A Szeletelo munkalapra egy eseményvezérlést raktunk fel: A kimutatás megváltozásakor (PivotTableUpdate) fut le ez a kis VBA „programka”. Először „elkapja” a kimutatást, amiben az mutatószám mezőt cserélgetni kell (ezért kell a munkalap neve és a kimutatás neve). Aztán az összes értékmezőt „kiszedi” – ezt az xlHidden beállítással lehet megtenni.

Majd végignézi, hogy mit választottunk a szeletelőben: ehhez a choice elnevezést és az alatta levő cellákat vizsgálja. Amit itt talál, azt beteszi a kimutatás értékmezőihez (AddDataField).

És ennyi.  🙂

Nem kell megijedni, és se kisujjból ráztam ki ezt. Picit belekukkantottam az Object Browserbe. 🙂 Ha tetszik a megoldás, és szeretnéd alkalmazni, próbáld ki a minta fájlunkat, abban látni fogod a VBA kód ablakot, és ki tudod másolni a kódot a saját fájlodba is.

Ha nem szeretnél lemaradni az új bejegyzésekről, iratkozz fel blog értesítőnkre!

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
    Dim ptMain As PivotTable
    Dim pfMeasure As PivotField
    Dim i As Long
   
    On Error GoTo Errorhandler
    
    Set ptMain = Worksheets("Kimutatas").PivotTables("PivotTable1")
    
    For Each pfMeasure In ptMain.DataFields
        pfMeasure.Orientation = xlHidden
    Next
    
    i = 0
    Do While [choice].Offset(i, 0).Value <> ""
        ptMain.AddDataField ptMain.PivotFields([choice].Offset(i, 0).Value)
        i = i + 1
    Loop
    
    Exit Sub
    
Errorhandler:
Debug.Print Now(), Err.Description
    
End Sub