✅ Kontingenčná vyrovnávacia pamäť v programe Excel - čo to je a ako ju najlepšie použiť

Obsah

Čo je to Pivot Cache?
Vedľajšie účinky vyrovnávacej pamäte Pivot
Zdieľanie medzipamäte Pivot
Vytvorenie duplicitnej vyrovnávacej pamäte (s rovnakým zdrojom údajov)
Spočítajte počet pivotných vyrovnávacích pamätí
Zlepšenie výkonu pri práci s kontingenčnými tabuľkami

Ak pracujete s kontingenčnými tabuľkami programu Excel, medzipamäť kontingenčnej pamäte je niečo, o čom by ste mali určite vedieť.

Čo je to Pivot Cache?

Kontingenčná vyrovnávacia pamäť je niečo, čo sa automaticky generuje pri vytváraní kontingenčnej tabuľky.

Jedná sa o objekt, ktorý vlastní repliku zdroja údajov. Aj keď ho nevidíte, je súčasťou zošita a je pripojený k kontingenčnej tabuľke. Keď vykonáte akékoľvek zmeny v kontingenčnej tabuľke, nepoužije zdroj údajov, ale použije kontingenčnú vyrovnávaciu pamäť.

Dôvodom generovania medzipamäte je optimalizácia fungovania kontingenčnej tabuľky. Aj keď máte tisíce riadkov údajov, kontingenčná tabuľka ich sumarizuje veľmi rýchlo. Položky môžete presúvať a presúvať do riadkov/stĺpcov/hodnôt/filtrov a výsledky sa okamžite aktualizujú.

Pivot Cache umožňuje toto rýchle fungovanie kontingenčnej tabuľky.

Aj keď si myslíte, že ste priamo prepojení so zdrojovými údajmi, v skutočnosti po vykonaní zmien v kontingenčnej tabuľke získate prístup k kontingenčnej vyrovnávacej pamäti (a nie k zdrojovým údajom).

To je tiež dôvod, prečo musíte aktualizovať kontingenčnú tabuľku, aby odrážala všetky zmeny vykonané v súbore údajov.

Vedľajšie účinky vyrovnávacej pamäte Pivot

Jednou z nevýhod pivot cache je, že zväčšuje veľkosť vášho zošita.

Keďže ide o repliku zdrojových údajov, pri vytváraní kontingenčnej tabuľky sa kópia týchto údajov uloží do kontingenčnej pamäte cache.

Keď na vytvorenie kontingenčnej tabuľky použijete veľké množiny údajov, veľkosť súboru zošita sa výrazne zvýši.

Zdieľanie medzipamäte Pivot

Ak od roku 2007 v programe Excel 2007 už máte kontingenčnú tabuľku a vytvoríte dodatočnú kontingenčnú tabuľku pomocou rovnakých zdrojových údajov, Excel automaticky zdieľa kontingenčnú vyrovnávaciu pamäť (čo znamená, že obe kontingenčné tabuľky používajú rovnakú kontingenčnú pamäť). Je to užitočné, pretože sa vyhýba duplikácii medzipamäte medzipamäte a následne má za následok menšie využitie pamäte a menšiu veľkosť súboru.

Obmedzenia zdieľanej vyrovnávacej pamäte

Napriek tomu, že zdieľaná medzipamäť vylepšuje fungovanie kontingenčnej tabuľky a využitie pamäte, má nasledujúce obmedzenia:

Keď obnovíte jednu kontingenčnú tabuľku, obnovia sa všetky kontingenčné tabuľky prepojené s rovnakou vyrovnávacou pamäťou.
Keď zoskupíte polia v jednej z kontingenčných tabuliek, použije sa na všetky kontingenčné tabuľky pomocou rovnakej kontingenčnej pamäte cache. Ak napríklad zoskupíte dátumy podľa mesiacov, táto zmena sa prejaví vo všetkých kontingenčných tabuľkách.
Keď vložíte vypočítané pole/položku do jednej z kontingenčných tabuliek, zobrazí sa vo všetkých kontingenčných tabuľkách, ktoré zdieľajú kontingenčnú vyrovnávaciu pamäť.

Cesta okolo týchto obmedzení je prinútiť Excel vytvoriť samostatnú kontingenčnú vyrovnávaciu pamäť pre rôzne kontingenčné tabuľky (pri použití rovnakého zdroja údajov).

Poznámka: Ak pre rôzne kontingenčné tabuľky používate rôzne zdroje údajov, Excel pre ne automaticky vygeneruje samostatné kontingenčné pamäte.

Vytvorenie duplicitnej vyrovnávacej pamäte (s rovnakým zdrojom údajov)

Tu sú tri spôsoby, ako vytvoriť duplicitnú medzipamäť pri vytváraní kontingenčných tabuliek z rovnakého zdroja údajov:

#1 Používanie rôznych názvov tabuliek

Kliknite kdekoľvek v zdroji údajov a prejdite na položku Vložiť -> Tabuľka (alebo môžete použiť klávesovú skratku - Ctrl + T).
V dialógovom okne Vytvoriť tabuľku kliknite na tlačidlo OK. Vytvorí sa tabuľka s názvom Table1.
Ak je v tabuľke vybratá ľubovoľná bunka, prejdite na položku Vložiť -> Kontingenčná tabuľka.
V dialógovom okne Vytvoriť kontingenčnú tabuľku si všimnete, že v poli Tabuľka/Rozsah má názov tabuľky. Kliknite na tlačidlo OK.
- Tým sa vytvorí prvá kontingenčná tabuľka.
Prejdite na zdroj údajov (tabuľka), vyberte ľubovoľnú bunku a prejdite na položku Návrh nástrojov tabuľky -> Nástroje -> Previesť na rozsah. Zobrazí sa výzva s otázkou, či chcete tabuľku previesť na normálny rozsah. Kliknite na Áno. Tým sa tabuľka prevedie na bežné tabuľkové údaje.

Teraz zopakujte vyššie uvedené kroky a jednoducho zmeňte názov tabuľky (z Tabuľky1 na Tabuľku2 alebo čokoľvek chcete). Môžete to zmeniť zadaním názvu do poľa pod názvom tabuľky na karte Návrh nástrojov tabuľky.

Aj keď obe tabuľky (Tabuľka1 a Tabuľka2) odkazujú na rovnaký zdroj údajov, táto metóda zaisťuje, že pre každú tabuľku sa vygenerujú dve oddelené medzipamäte.

#2 Použitie starého sprievodcu kontingenčnou tabuľkou

Tieto kroky použite, ak chcete pri použití rovnakého zdroja údajov vytvoriť ďalšiu kontingenčnú tabuľku so samostatnou medzipamäťou.

Vyberte ľubovoľnú bunku v údajoch a stlačte kombináciu klávesov ALT + D + P.
- Otvorí sa kontingenčná tabuľka a sprievodca kontingenčnými grafmi.
V kroku 1 z 3 kliknite na Ďalej.
V kroku 2 z 3 skontrolujte, či je rozsah údajov správny, a kliknite na Ďalej.
Excel zobrazuje výzvu, ktorá v zásade hovorí, že kliknutím na Áno vytvoríte zdieľanú medzipamäť medzipamäte a kliknutím na položku Nie vytvoríte samostatnú medzipamäť.
Kliknite na Nie.
V kroku 3 sprievodcu vyberte, či chcete kontingenčnú tabuľku vložiť do nového alebo rovnakého pracovného hárka, a potom kliknite na položku Dokončiť.

Poznámka: Uistite sa, že údaje nie sú tabuľkou programu Excel.

Spočítajte počet pivotných vyrovnávacích pamätí

Možno budete chcieť spočítať počet kontingenčných vyrovnávacích pamätí, aby ste sa vyhli viacerým pivotným vyrovnávacím pamätiam z rovnakého zdroja údajov.

Tu je rýchly spôsob, ako to spočítať:

Stlačením klávesov ALT + F11 otvorte editor VB (alebo prejdite na kartu Vývojár -> Visual Basic).
V ponuke editora jazyka Visual Basic kliknite na Zobraziť a vyberte položku Okamžité okno (alebo stlačte kombináciu klávesov Control + G). Vďaka tomu bude okamžité okno viditeľné.
Do bezprostredného okna vložte nasledujúci kód a stlačte kláves Enter:
```
? ActiveWorkbook.PivotCaches.Count
```

V zošite okamžite zobrazí počet kontingenčných vyrovnávacích pamätí.

Zlepšenie výkonu pri práci s kontingenčnými tabuľkami

Pri práci s kontingenčnými tabuľkami môžete urobiť niekoľko vecí, ktoré môžu zlepšiť výkon zošitov (veľkosť súboru a využitie pamäte):

#1 Odstráňte zdrojové údaje

Zdrojové údaje môžete odstrániť a používať iba kontingenčnú vyrovnávaciu pamäť. Stále budete môcť robiť všetko pomocou kontingenčnej pamäte cache, pretože obsahuje snímku pôvodných údajov. Keďže ste však odstránili zdrojové údaje, veľkosť súboru zošita by sa zmenšila.

V prípade, že chcete získať späť zdrojové údaje, jednoducho dvakrát kliknite na priesečník súčtov pre danú kontingenčnú tabuľku. Vytvorí nový pracovný hárok a zobrazí všetky údaje použité na vytvorenie tejto kontingenčnej tabuľky.

#2 Neukladajte údaje do medzipamäte Pivot

Keď uložíte súbor s kontingenčnou tabuľkou a zdrojovými údajmi, uloží sa aj kontingenčná vyrovnávacia pamäť, ktorá má kópiu zdrojových údajov. To znamená, že zdrojové údaje ukladáte na dve miesta: do pracovného hárka, v ktorom sú údaje, a do kontingenčnej pamäte cache.

Existuje možnosť neuložiť údaje do vyrovnávacej pamäte a zavrieť ich. To povedie k nižšej veľkosti súboru.

Urobiť toto:

Vyberte ľubovoľnú bunku v kontingenčnej tabuľke.
Prejdite na položku Analyzovať -> Kontingenčná tabuľka -> Možnosti.
V dialógovom okne Možnosti kontingenčnej tabuľky prejdite na kartu Údaje.
Zrušte začiarknutie políčka Uložiť zdrojové údaje pomocou súboru.
Začiarknite možnosť - Obnoviť údaje pri otváraní súboru.
- Ak nezaškrtnete túto možnosť, po otvorení zošita programu Excel sa údaje neobnovia a nebudete môcť používať funkcie kontingenčnej tabuľky. Aby to fungovalo, budete musieť kontingenčnú tabuľku ručne aktualizovať.

Keď to urobíte, Excel neuloží údaje do kontingenčnej pamäte cache, ale obnoví ich pri ďalšom otvorení zošita programu Excel. Vaše údaje môžu byť v rovnakom zošite, inom zošite alebo v externej databáze. Keď otvoríte súbor, obnoví údaje a znova sa vytvorí medzipamäť Pivot.

Aj keď to môže viesť k nižšej veľkosti súboru, otvorenie súboru môže trvať dlhšie (pretože program Excel obnoví vyrovnávaciu pamäť).

Pozri tiež: Ukladanie zdrojových údajov pomocou kontingenčnej tabuľky.

Poznámka: Ak použijete túto možnosť, uistite sa, že máte zdroj údajov neporušený. Ak odstránite zdrojové údaje (zo zošita alebo akéhokoľvek externého zdroja údajov), nebudete môcť znova vytvoriť medzipamäť.

#3 Zdieľanie medzipamäte Pivot pre lepší výkon

Ak sa náhodou (alebo úmyselne) dostanete do situácie, keď máte duplicitnú pivot cache a chcete duplikát odstrániť a zdieľať pivot cache, postupujte takto:

Odstráňte jednu z kontingenčných tabuliek, pre ktorú chcete vymazať vyrovnávaciu pamäť. Ak to chcete urobiť, vyberte kontingenčnú tabuľku a prejdite na položku Domov -> Vymazať -> Vymazať všetko.
Teraz jednoducho skopírujte kontingenčnú tabuľku, ktorú chcete duplikovať, a prilepte ju (buď do rovnakého pracovného hárka, alebo do samostatného pracovného hárka).
- Odporúčame ho prilepiť do samostatných pracovných hárkov, aby sa pri rozbalení neprekrýval s inou kontingenčnou tabuľkou. Aj keď to niekedy kopírujem bok po boku, aby som porovnal rôzne pohľady. Toto prilepenie kópie kontingenčnej tabuľky zaisťuje zdieľanie kontingenčnej pamäte cache.