PIVOT Cache story

Talán sokan tudjátok, hogy ha egy PIVOT tábla (Kimutatás) forrásaként szolgáló tartományban változás történik, akkor az nem jelenik meg automatikusan a kimutatásban. A változások követéséhez frissíteni kell a PIVOT-ot. Ennek az  a magyarázata, hogy a kimutatás nem az eredeti adatforrás alapján készül, hanem annak másolata alapján. Ez a másolat a kimutatás készítéskor jön létre a memóriában. Jellege és funkciója miatt pivot cache-nek hívjuk.

Mire jó a Pivot Cache?

Mint általában minden cache, ez is a sebesség növelésére szolgál. Beolvassa az adatokat a cache-be, és utána onnan használja, mint forrást. Amikor állítjuk össze, vagy módosítjuk a kimutatást, egyszerűbb és gyorsabb innen dolgozni, mint az eredeti forrásból. Főleg, ha külső forráshoz csatlakozva (Access, másik Excel munkafüzet, SQL szerver, stb..) készítjük a kimutatást.

A Pivot cache és a fájlméret

Alapértelmezésként az történik, hogy az adatforrást másolatként tartalmazó Pivot cache és az eredeti adat is tárolódik a munkafüzetben. Tehát duplikálva van. Ez teljesen logikusnak tűnik, ha az adatforrás ugyanabban a munkafüzetben van, mint a kimutatás. De ha külső forrásból táplálkozik, akkor megadható, hogy tárolja-e az adatforrást is, vagy sem. Ezt a kimutatás beállításainál lehet megadni. (Jobb klikk->Kimutatás beállításai)

Ha ezt nem pipáljuk be, akkor a külső adatforrás esetén nem menti el az adatokat is, de a cache-t természetesen igen, azaz a PIVOT használható akkor is, ha fizikailag nincs adatforrás.

Adatforrás törlése

Ha az adatok eleve egy fájlban vannak a kimutatással, akkor is tudunk spórolni a fájlmérettel úgy, hogy mentés előtt egyszerűen töröljük az adatforrást. Ezzel megint csak a cache marad a fájlban és a kimutatás.

Hogy kapom vissza az adatforrást?

Nagyon egyszerűen. Csak duplán kell kattintani a kimutatás jobb alsó sarkában lévő végösszegre, és egy külön munkalapra visszagenerálja a teljes adattáblát.

Amikor kísérleteztem ezzel, akkor alapul vettem egy kb. 43.000 soros forrás táblázatot. Majd ebbe készítettem egy kimutatást, végül kitöröltem a forrás adatokat tartalmazó munkalapot. A fájlméretek érdekesen alakultak.

Adatok nélkül tehát jelentősen kisebb a munkafüzet mérete, mégis hordozza a teljes adatforrást.

Több kimutatás és a Pivot cache

Új adatforráshoz automatikusan új cache készül. Minden cache a forrásadat mennyiségétől függő mennyiségű memóriát használ. Tehát minél több cache, annál több memória és annál nagyobb fájlméret.

Több PIVOT azonos forrás alapján.

Ha azonos adatforrás alapján készítünk újabb kimutatást, teljesen logikus lenne, ha nem készülne újabb és újabb cache, hanem azonos cache-ből tudnánk több kimutatást készíteni.

A 2003-as Excel verzióig úgy működött a dolog, hogy a kimutatás létrehozásakor a felhasználó kérhette, hogy már létező kimutatás (cache) alapján készüljön az új. Ha ezt nem kértük, az Excel felismerte, ha már létezik ugyanazon forrás szerinti kimutatás, és figyelmeztetett. Mi dönthettük el, hogy mi történjen: Új cache jöjjön létre, vagy használjuk a már meglévőt.

A 2007-es Excel verziótól megváltozott a PIVOT létrehozásának módja. Ha azonos adatforrást adunk meg, automatikusan a már létező cache-t fogja használni.

A közös cache használatnak velejárói

• Bármelyik kimutatást frissítjük, mindegyik frissül
• Számított oszlopok és mezők mindegyikben megjelennek
• Csoportosító művelet mindegyikre érvényes lesz

Az első tulajdonság kifejezetten előnyös, de a másik kettő nem feltétlenül az, ezért mégis érdemes körüljárni, hogyan lehet ugyanazon adatforrást használó PIVOT táblákhoz külön-külön cache-t rendelni.

PIVOT tábla azonos forrás de külön cache

Egyik lehetőség, ha létrehozáskor előcsalogatjuk a 2003-as verzióban megismert PIVOT varázslót.

Angol verzió: Nyomjuk meg egymás után az ALT-D-P billentyűket.

Magyar verzió: ALT-D-I

A Tovább gomb után adhatjuk meg az adatforrást. Ha ez ugyanaz, mint egy már létező kimutatásé, akkor rákérdez, hogy a két kimutatás független legyen-e vagy sem.

A „Nem” gombra kattintva az új kimutatás saját cache-t fog használni.

Kimutatások szétválasztása

Ha tudni szeretnénk,hogy két kimutatás azonos cache-t használ vagy sem, akkor erre több lehetőségünk van.

• Ellenőrizzük, hogy teljesen azonos-e az adatforrás, amit használnak
• Próbáljuk ki a fent felsorolt velejárók egyikét (Frissítés, csoportosítás, számított mező)
• Kérjük le a munkafüzet PivotCache-einek számát (lásd a videóban)

A szétválasztás legegyszerűbb módja:

A leválasztandó PIVOT tábla adatforrását módosítsuk legalább egy sorral hosszabbra, mint a többiét. Ezzel plusz egy üres sor kerül a forrásba, ami nem baj, cserébe új Cache készül neki.

A szétválasztás érdekes módja a következő:

1. Jelöljük ki az egyik kimutatást-> Kivágás
2. Illesszük be egy teljesen új munkafüzetbe
3. Frissítés
4. Másolással vagy kivágással helyezzük vissza az eredeti munkafüzetbe

Pivot Cache elemző makró

Töltsd le ezt a makró munkafüzetet és elemezd segítségével bármelyik munkafüzetet.

PIVOT-Cache-report-makro

Nézzük meg videón mindezt

• Hogy működik a Pivot cache?