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
1 ping