A tömbképletekről szóló cikksorozatot (első és második rész) kicsit megakasztotta az adat vizualizációs tréninghez írt sorozat, de most újra felvesszük a fonalat.
Rögtön egy különleges használati esetet mutatok be: hogyan lehet tömbképlettel kiolvasni a PowerPivot adatbázist szűrő szeletelők szűrési feltételeit?
Vagyis: ki szeretném tenni a munkalapra, hogy mi van leszűrve a szeletelőben – ahogy az AE oszlopban látjátok:
(A példában szerepő adatok az Önkiszolgáló BI Workshop tanfolyam mintaadatai.)
Az Önkiszolgáló BI workshopon megismert formulát fogjuk használni:
= CUBERANKEDMEMBER( Connection, Set_Expression, Rank, [Caption] )
- Connection: PowerPivot esetén ez „ThisWorkbookDataModel”
- Set_Expression: itt kell megadni a szeletelő nevét. A szeletelő beállítások menüjében (jobb klikk, Slicer Settings) megtalálod, milyen névvel kell a formulában a szeletelőre hivatkozni.
- Rank: itt adod meg, hogy a szűrt elemek közül hányadikat mutassa a formula.
Például ez a formula:
=CUBERANKEDMEMBER( „ThisWorkbookDataModel”; Slicer_Hatóanyag; 1 )
A szűrés első elemét adja vissza: acetilszalicilsav.
Ha a többi elemre is kíváncsi vagy, meg kell hívni a formulát Rank 2, 3, 4… paraméterekkel.
Most következik, hogyan szoktam a sorszámok megadását megoldani a tömbképlettel. Ehhez segítségül hívjuk a ROW() formulát, amit könnyű lesz „tömbösíteni”:
=CUBERANKEDMEMBER(„ThisWorkbookDataModel”; Slicer_Hatóanyag; ROW(A1) ) – Enter, majd másolás lefelé.
A ROW(…) formula a hivatkozás sorának számát adja meg, vagyis A1 esetén 1-et. Ha lefelé másolod ezt a formulát, a Rank paraméter helyére írt ROW(…) növekedni fog (A2, A3, …), ami 2, 3, 4..-et ad majd, tehát egyszerűen kiolvashatod a szűrés minden elemét. Probléma lehet, ha sort szúrsz be a munkalapra, mert a hivatkozások elcsúszhatnak, ezért én inkább tömb képletet alkalmazok: (Ha nem vagy biztos benne, hogy mi az a tömbképlet, olvasd el az alapokat itt!)
=CUBERANKEDMEMBER( „ThisWorkbookDataModel”; Slicer_Hatóanyag; ROW(1:10) )
Jelölj ki egy 10 sorból és egy oszlopból álló tömböt, ebbe írd be a formulát Ctrl+Shift+Enter-rel (CSE).
A Rank helyére írt ROW(1:10) egy függőleges szám tömböt ad vissza 1-10-ig, a CUBERANKEDMEMBER formula pedig mindegyik számhoz kiolvassa a megfelelő szűrt elemet, és egy függőleges (oszlop) tömbként adja vissza.
A hiba értékek elkerülése érdekében én egy IFERROR-ba is becsomagoltam a formulát. Igy néz ki a végleges verzió:
=IFERROR( CUBERANKEDMEMBER( „ThisWorkbookDataModel”; Slicer_Hatóanyag; ROW(1:10) ); „” ) – CSE 10 soros tömbbe.
Ha pedig egészen biztosan el akarod kerülni a sor beszúrás problémáját, konstans-tömböt is használhatsz, amit kapcsos zárójelben kell megadni:
=IFERROR( CUBERANKEDMEMBER( „ThisWorkbookDataModel”; Slicer_Hatóanyag; {1;2;3;4;5;6;7;8;9;10} ); „” ) – CSE 10 soros tömbbe.
Remélem, a PowerPivot használók hasznosnak találják ezt a kis apróságot!
Legutóbbi hozzászólások