Pivot cache-ről: együtt frissülő pivot táblák készítése

Nem túlzás, ha azt mondom, nálunk, egy nagy multinacionális cégnél a pénzügyi riportok 99%-a pivot táblán alapul – nem tudnánk létezni pivot tábla nélkül… Ahhoz, hogy jól tudjuk használni, felépíteni a pivot táblákat, tudni kell, mi történik a háttérben: mi az a pivot cache. Technikai dolognak tűnik, de aki nem csak „egyszer használatos” pivot táblát készít, az sok kellemetlenségtől megkímélheti magát, ha megfogadja az alábbi javaslatokat – enélkül a pivot tábláink könnyen hatalmas, belassult Excel fájlokat növesztenek.

Pici elmélet…

Amikor pivot táblát készítesz, az Excel a forrásadatokat (a kijelölt táblát) lemásolja, és a memóriában tárolja – ezt hívják pivot cache-nek. A pivot tábla a cache-re épül – ez lehetővé teszi, hogy a pivot nagyon-nagyon gyors legyen, hátránya azonban, hogy ha változik a forrásadat, az nem „megy át” automatikusan a pivot táblába: mindig frissíteni kell.

A pivot tábla frissítése azt jelenti, hogy a cache-t frissíted a pivot tábla mögött – csak így lesz látható a forrásadat változása a pivot-ban.

A cache az Excel fájlba is elmentődik, tehát a fájl mérete növekedni fog! Különböző forrásadat területekhez az Excel mindenképpen külön cache-t épít fel, de azonos forrásra épített pivot-ok esetén lehetőség van közös cahce használatára:

A közös cache előnye

• A pivot táblák együtt frissülnek (mert, ahogy fentebb írtam, a frissítés magát a cache-t frissíti)
• Mindegyikben elérhetőek a kalkulált mezők és a csoportosítások (mert ezek a cache-ben tárolódnak)
• A fájl mérete kisebb lesz

Az első pont NAGYON fontos a mi munkánkban. Sok esetben több, mint 10 pivot táblát használunk egy-egy munkafüzetben – biztosnak kell lennünk benne, hogy ezek együtt frissülnek, különben egyesével kellene mindegyiken a frissítés gombot nyomkodni!

Hogyan kerüld el a cache duplikálódást?

A legegyszerűbb módszer, ahogy én is csinálom: másolás-beillesztés. Ha már van egy pivot táblád, jelöld ki (az a biztos, ha egy-egy sorral, oszloppal többet jelölsz ki, mint maga a tábla) és illeszd be, akár másik munkalapra. Általában átrendezni is könnyebb a már meglevő pivotot, mint nulláról újat építeni.

A közös cache elvesztése 🙁

Az izgalmak általában itt szoktak kezdődni!

Bővül a forrásadat-tartomány, több sort tartalmaz. Ezeket az új sorokat is be kellene venni a pivot táblába. Mit ne tegyél?

TILOS egy pivot tábla forrásadat-tartományát megváltoztatni!
Ezáltal új cache készül neki, azonnal „leesik” a közös cache-ről: nem fog együtt frissülni a többi pivottal, és a fájl mérete is megnőhet!

A közös cache megőrzése a forrásadat-tartomány bővülésekor

Tehát mi a megoldás?

Építsd a pivot tábládat elnevezett tartományra! (az elnevezett tartományról továbbiak itt)

1. Először nevezd el a kijelölt adat-tartományt
2. A pivot tábla létrehozásakor ezt a nevet írd be a forrásadat tartományhoz
3. Ha új sorok jönnek, csak az elnevezett tartomány referenciáját módosítsd (Ctrl+F3)

Így a pivot cache pedig ezt az elnevezést látja, mint pivot tábla tartomány. Amikor a cache-t frissíted (frissítést nyomsz az egyik pivoton), az elnevezésen keresztül fogja beolvasni az új tartományt, és ezt minden, a cache-re épülő pivot tábla látni fogja.

Ennyi az egész! 🙂

További olvasnivalók:

Magyarul: http://excel-bazis.hu/tutorial/pivot-cache-story

Angolul: http://www.myonlinetraininghub.com/excel-pivot-cache

Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!