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)
- Először nevezd el a kijelölt adat-tartományt
- A pivot tábla létrehozásakor ezt a nevet írd be a forrásadat tartományhoz
- 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!
2 comments
Hali!
nem a fenti témához kapcsolódik de lenne egy problémám és a segítségeteket kérném benne.
olyan dolgot kellene megoldani hogy lenne egy oszlop amibe szöveg van és ezeknek kellene egy értéket adni külön külön pl ha a szöveg “asztal ” akkor ahhoz társítson egy értéket amit az oszlop alján össze lehetne adni
így képzeltem el
A
1 KUTYA ezeknek mindnek lenne egy értéke
2 MACSKA
4 MACSKA
5 KUTYA
6 MACSKA
és itt a sor végén össze lehetne adni őket
válaszotokat előre is köszönöm 😀
Author
Szia Levi!
Nem igazán értem e kérdést. Honnan jönnek a számok? Ezek külön oszlopban vannak? Ha igen, akkor miért lenne probléma összeadni őket?
Ha nem, akkor miért nem tesszük külön oszlopba?
Jó lenne, ha tudnál küldeni egy fájlt a Blog értesítőnél található címre egy kis példával, amiből látszik, hogy milyen adatból milyen végeredményt szeretnél.
Üdv:
Kris