Pivot adatforrás automatikus frissítése

Vállalati környezetben gyakori, hogy a riporthoz szükséges adatok adattárházi lekérdezését követően maga a vizuális megjelenítés már Excel segítségével kerül elkészítésre. Egy kód (pl. SAS projekt) időzített futtatása, s így a szükséges adatok exportja Feladatütemező segítségével könnyen elvégezhető, azonban az Excel oldali automatizálás általában már kevésbé jut fontos szerephez.

A következőkben bemutatásra kerülő apró kis trükk az Excel oldali automatizálást hivatott elősegíteni, mégpedig úgy, hogy a dashboard mögött megbúvó Kimutatás (Pivot) adatforrását nem kell minden alkalommal az új adattömeg méretéhez utóigazítani, hanem mindez automatikusan megtörténik, amennyiben további adatokkal egészül ki az adattábla.

Ennek megvalósításához egyrészt szükségünk lesz az Excel Névkezelő funkciójára, másrészt pedig az Eltolás (offset) és Darab2 (counta)* függvények kombinált alkalmazására.

Első lépésben álljuk arra a munkalapra, ahol a Kimutatás adatai szerepelnek (mi esetünkben data elnevezésű munkalap)**, majd válasszuk a Képletek menü Névkezelő funkcióját, ahol megadhatjuk az automatizmust biztosító parancssort.

Névkezelő menüpont elhelyezkedése a Képletek szalagon belül

Nyomjunk az "Új..." lehetőségre, és adjunk egy tetszőleges nevet (pl. adatforrás), amire majd a későbbiekben hivatkozhatunk, s a Hivatkozás részhez adjuk meg a következő parancssort:

=ELTOLÁS(data!$A$1;0;0;DARAB2(data!$A:$A);DARAB2(data!$1:$1))

Az Eltolás függvény segítségével adott magasságú és szélességű hivatkozást kapunk eredményül egy megadott pontból kiindulva. Az eredmény lehet egyetlen cella, vagy akár cellatartomány is. A Darab2 függvény hivatott megadni, hogy esetünkben a meghatározandó cellatartomány milyen magas, illetve széles legyen.

A megadott kifejezés tehát biztosítja, hogy az A1-es cellából kiindulva a Pivot tartománya olyan szélességű és magasságú lesz, ami adatot tartalmaz, tehát a kezdőponttól annyi sorral és oszloppal lesz a tartomány eltolva, amiben adat szerepel.

A képlet megadását követően nyomjunk egy OK-t, majd látni fogjuk, hogy a Névkezelőben megjelent egy új sor, a korábban megadott névvel (esetünkben adatforrás).

Elnevezett tartományok listája a Névkezelő felületén

Utolsó lépésben pedig nem kell mást tennünk, mint vagy létrehozni egy új Kimutatást és ott a Táblázat vagy tartomány részhez, vagy ha már létre lett hozva a Pivot, akkor az Elemzés fülön a Más adatforrás megadása lehetőségre kattintva megadni a korábban létrehozott parancs nevét (adatforrás).

Kimutatás adatforrásának meghatározása

Megjegyzés:

* A Darab2 függvény helyett természetesen használható Darab függvény is, amennyiben teljesen biztosak vagyunk benne, hogy például az első oszlopban csak számok szerepelnek és ez a jövőben sem fog változni.

** Fontos, hogy az alkalmazott adatforrás egybefüggő legyen, tehát ne legyen teletűzdelve üres sorokkal vagy oszlopokkal.

Címkék:
© 2017 AdatTérKép