Dinamikusan módosuló Pivot-tábla mezőlista

2017. október 30. - Office Guru

Az elmúlt hetek során velem is előfordult az, ami egyik olvasómmal esett meg a napokban - adott volt egy masszív Excel-tábla és egy hozzá tartozó Pivot, majd az Excel-tábla módosítása után hiába nyomogatott a felhasználó a Refresh gombra, nem sikerült az újonnan hozzáadott adatokat egyszerűen belevarázsolni a Pivotba. Hogy miről is van szó? Nézzük a lenti kis táblázatot:

01.JPGSzúrjunk be egy Pivot-táblát az Insert ribbonfül Tables szekciója alatt lévő PivotTable funkcióval:

02.JPGLátjuk, szépen meg is jelent a Field Listben az összes oszlopunk fejléce:

03.JPGHa most például a Típus és a Város közé szúrnánk be egy új oszlopot, a Pivot szépen frissülne egy Refresh után, de tegyünk mondjuk egy plusz oszlopot a tábla végéhez:

04.JPGBőszen nyomogathatjuk a megjelent Analyze ribbonfül Data szekciójában a Refresh gombot, a Field Listben egyszerűen nem fogjuk megtalálni az újonnan hozzáadott fejlécünket:

05.JPGVan jópár megoldás ennek orvoslására, pont a Refresh mellett van a Change Data Source opció is, készíthetünk táblát is, most azonban a nevesített tartományok megoldását hívjuk segítségül.

Ugyebár a Formulas ribbonfül Defined Names szekciójában van a Name Manager, nyissuk meg:

06.JPGA New gombra való kattintással hozzunk létre egy új nevesített tartományt, ami legyen az eredeti táblánk kijelölve, de nem szimpla hivatkozással, hanem megturbózva egy OFFSET függvénnyel:

07.JPG=OFFSET($B$1,0,0,COUNTA($B:$B),COUNTA($1:$1))

Mint ugyebár tudjuk már, az OFFSET egy olyan cella vagy tartomány hivatkozását/elérési útját adja vissza, ami a függvény paramétereként megadott sorszámra vagy oszlopszámra van a megadott cellától. Jelen példánknál maradva, a $B$1 cellától indulunk ki, majd COUNTA segítségével megnézzük, hogy hány darab nem üres cella van a B oszlopban és hány nem üres van az első sorban és ez adja meg a tartományunkat.

Ezután amikor létrehozzuk a Pivot-táblánkat vagy módosítjuk a forrást, használjuk az elnevezett tartományt, mert innentől kezdve automatikusan érzékelni fogja a sorok vagy oszlopok hozzáadását és automatikusan hozza is a Pivot-tábla Field Listjén:

08.JPG

09.JPG

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr4413118312

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

Nincsenek hozzászólások.