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:
Szúrjunk be egy Pivot-táblát az Insert ribbonfül Tables szekciója alatt lévő PivotTable funkcióval:
Látjuk, szépen meg is jelent a Field Listben az összes oszlopunk fejléce:
Ha 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:
Bő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:
Van 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:
A 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:
=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:
