PowerPivot szeletelő (Slicer) szűrőfeltételeinek kiolvasása tömbképlettel

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:

PPslicer_1

(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.

PPslicer_2n

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!

 

Vélemény, hozzászólás?

Your email address will not be published.

css.php