✅ Kontingenční mezipaměť v aplikaci Excel - co to je a jak ji nejlépe použít

Obsah

Co je to Pivot Cache?
Vedlejší efekty mezipaměti Pivot
Sdílení mezipaměti Pivot
Vytvoření duplicitní mezipaměti Pivot (se stejným zdrojem dat)
Spočítejte počet kontingenčních mezipamětí
Zlepšení výkonu při práci s kontingenčními tabulkami

Pokud pracujete s kontingenčními tabulkami aplikace Excel, je mezipaměť Pivot něco, o čem byste určitě měli vědět.

Co je to Pivot Cache?

Kontingenční mezipaměť je něco, co se automaticky generuje při vytváření kontingenční tabulky.

Jedná se o objekt, který obsahuje repliku zdroje dat. I když to nevidíte, je součástí sešitu a je připojeno k kontingenční tabulce. Když provedete jakékoli změny v kontingenční tabulce, nepoužívá zdroj dat, ale používá kontingenční mezipaměť.

Důvodem generování mezipaměti mezipaměti je optimalizace fungování kontingenční tabulky. I když máte tisíce řádků dat, kontingenční tabulka je při jejich shrnutí super rychlá. Položky můžete přetáhnout do řádků/sloupců/hodnot/filtrů a výsledky se okamžitě aktualizují.

Pivot Cache umožňuje toto rychlé fungování kontingenční tabulky.

I když si myslíte, že jste přímo propojeni se zdrojovými daty, ve skutečnosti přistupujete ke kontingenční mezipaměti (a nikoli ke zdrojovým datům) při provádění změn v kontingenční tabulce.

To je také důvod, proč potřebujete aktualizovat kontingenční tabulku, aby odrážela veškeré změny provedené v datové sadě.

Vedlejší efekty mezipaměti Pivot

Jednou nevýhodou kontingenční mezipaměti je, že zvyšuje velikost sešitu.

Protože se jedná o repliku zdrojových dat, při vytváření kontingenční tabulky se kopie těchto dat uloží do kontingenční mezipaměti.

Když k vytvoření kontingenční tabulky použijete velké datové sady, velikost souboru sešitu se výrazně zvýší.

Sdílení mezipaměti Pivot

Pokud již od Excelu 2007 kontingenční tabulku máte a vytvoříte další kontingenční tabulku pomocí stejných zdrojových dat, Excel automaticky sdílí kontingenční mezipaměť (což znamená, že obě kontingenční tabulky používají stejnou kontingenční mezipaměť). To je užitečné, protože se vyhýbá duplicitě mezipaměti mezipaměti a má za následek menší využití paměti a menší velikost souboru.

Omezení sdílené kontingenční mezipaměti

Sdílená mezipaměť mezipaměti zlepšuje fungování kontingenční tabulky a využití paměti, ale má následující omezení:

Když aktualizujete jednu kontingenční tabulku, obnoví se všechny kontingenční tabulky propojené se stejnou mezipamětí.
Když seskupíte pole do jedné z kontingenčních tabulek, použije se na všechny kontingenční tabulky pomocí stejné kontingenční mezipaměti. Pokud například seskupíte data podle měsíců, tato změna se projeví ve všech kontingenčních tabulkách.
Když vložíte vypočítané pole/položku do jedné z kontingenčních tabulek, zobrazí se ve všech kontingenčních tabulkách, které sdílejí kontingenční mezipaměť.

Cesta kolem těchto omezení je přinutit Excel vytvořit samostatnou mezipaměť pro různé kontingenční tabulky (při použití stejného zdroje dat).

Poznámka: Pokud pro různé kontingenční tabulky používáte různé zdroje dat, Excel by pro ně automaticky generoval samostatné kontingenční mezipaměti.

Vytvoření duplicitní mezipaměti Pivot (se stejným zdrojem dat)

Zde jsou 3 způsoby, jak vytvořit duplicitní mezipaměť při vytváření kontingenčních tabulek ze stejného zdroje dat:

#1 Používání různých názvů tabulek

Klikněte kdekoli ve zdroji dat a přejděte na Vložit -> Tabulka (nebo můžete použít klávesovou zkratku - Control + T).
V dialogovém okně Vytvořit tabulku klikněte na OK. Vytvoří tabulku s názvem Table1.
S jakoukoli buňkou vybranou v tabulce přejděte na Vložit -> Kontingenční tabulka.
V dialogovém okně Vytvořit kontingenční tabulku byste si všimli, že v poli Tabulka/Rozsah má název tabulky. Klikněte na OK.
- Tím se vytvoří první kontingenční tabulka.
Přejděte na zdroj dat (tabulka), vyberte libovolnou buňku a přejděte na Návrh nástrojů tabulky -> Nástroje -> Převést na rozsah. Zobrazí se výzva s dotazem, zda chcete převést tabulku na normální rozsah. Klikněte na Ano. Tím se tabulka převede na běžná tabulková data.

Nyní zopakujte výše uvedené kroky a změňte pouze název tabulky (z Table1 na Table2 nebo co chcete). Můžete to změnit zadáním názvu do pole pod názvem tabulky na kartě Návrh nástrojů tabulky.

Ačkoli obě tabulky (Tabulka1 a Tabulka2) odkazují na stejný zdroj dat, tato metoda zajišťuje, že jsou pro každou tabulku generovány dvě samostatné mezipaměti.

#2 Použití starého průvodce kontingenční tabulkou

Tyto kroky použijte, pokud chcete při použití stejného zdroje dat vytvořit další kontingenční tabulku se samostatnou mezipamětí.

Vyberte libovolnou buňku v datech a stiskněte ALT + D + P.
- Tím se otevře kontingenční tabulka a průvodce kontingenčním grafem.
V kroku 1 ze 3 klikněte na Další.
V kroku 2 ze 3 zkontrolujte, zda je rozsah dat správný, a klikněte na Další.
Excel zobrazuje výzvu, která v podstatě říká, že kliknutím na Ano vytvoříte sdílenou mezipaměť pivotů a Ne vytvoříte samostatnou mezipaměť pivotů.
Klikněte na Ne.
V kroku 3 Průvodce vyberte, zda chcete kontingenční tabulku v novém listu nebo stejném listu, a poté klikněte na Dokončit.

Poznámka: Ujistěte se, že data nejsou tabulkou aplikace Excel.

Spočítejte počet kontingenčních mezipamětí

Možná budete chtít spočítat počet kontingenčních mezipamětí, abyste se vyhnuli více mezipaměti ze stejného zdroje dat.

Zde je rychlý způsob, jak to spočítat:

Stisknutím ALT + F11 otevřete editor VB (nebo přejděte na kartu Vývojář -> Visual Basic).
V nabídce editoru jazyka Visual Basic klikněte na Zobrazit a vyberte Okamžité okno (nebo stiskněte Ctrl + G). Tím bude viditelné okamžité okno.
V okamžitém okně vložte následující kód a stiskněte Enter:
```
„ActiveWorkbook.PivotCaches.Count
```

Okamžitě zobrazí počet kontingenčních mezipamětí v sešitu.

Zlepšení výkonu při práci s kontingenčními tabulkami

Při práci s kontingenčními tabulkami můžete zlepšit výkon sešitů (velikost souboru a využití paměti):

#1 Odstraňte zdrojová data

Můžete odstranit zdrojová data a použít pouze kontingenční mezipaměť. Stále budete moci dělat vše pomocí kontingenční mezipaměti, protože obsahuje snímek původních dat. Ale protože jste odstranili zdrojová data, velikost souboru sešitu by se zmenšila.

V případě, že chcete získat zpět zdrojová data, jednoduše dvakrát klikněte na průsečík velkých součtů pro danou kontingenční tabulku. Vytvoří nový list a zobrazí všechna data použitá k vytvoření této kontingenční tabulky.

#2 Neukládejte data do mezipaměti Pivot

Když uložíte soubor s kontingenční tabulkou a zdrojovými daty, uloží se také kontingenční mezipaměť, která má kopii zdrojových dat. To znamená, že zdrojová data ukládáte na dvě místa: do listu, který data obsahuje, a do kontingenční mezipaměti.

Existuje možnost neukládat data do mezipaměti a zavřít je. To povede k menší velikosti souboru.

Udělat toto:

Vyberte libovolnou buňku v kontingenční tabulce.
Přejděte na položku Analyzovat -> Kontingenční tabulka -> Možnosti.
V dialogovém okně Možnosti kontingenční tabulky přejděte na kartu Data.
Zrušte zaškrtnutí možnosti - Uložit zdrojová data pomocí souboru.
Zaškrtněte možnost - Aktualizovat data při otevírání souboru.
- Pokud tuto možnost nezaškrtnete, při otevření sešitu aplikace Excel data neobnoví a nebudete moci používat funkce kontingenční tabulky. Aby to fungovalo, budete muset kontingenční tabulku ručně aktualizovat.

Když to uděláte, aplikace Excel neuloží data do mezipaměti mezipaměti, ale obnoví ji při příštím otevření sešitu aplikace Excel. Vaše data mohou být ve stejném sešitu, jiném sešitu nebo externí databázi. Když soubor otevřete, obnoví data a znovu se vytvoří mezipaměť Pivot.

I když to může vést ke snížení velikosti souboru, otevření souboru může trvat déle (protože aplikace Excel znovu vytvoří mezipaměť).

Viz také: Uložení zdrojových dat pomocí kontingenční tabulky.

Poznámka: Pokud použijete tuto možnost, ujistěte se, že máte zdroj dat neporušený. Pokud odstraníte zdrojová data (ze sešitu nebo jakéhokoli externího zdroje dat), pak nebudete moci znovu vytvořit mezipaměť kontingenčních souborů.

#3 Sdílení kontingenční mezipaměti pro lepší výkon

Pokud se náhodou (nebo úmyslně) dostanete do situace, kdy máte duplicitní mezipaměť mezipaměti a chcete duplikát odstranit a sdílet mezipaměť kontingentu, postupujte takto:

Odstraňte jednu z kontingenčních tabulek, pro kterou chcete vymazat mezipaměť. Chcete -li to provést, vyberte kontingenční tabulku a přejděte na Domů -> Vymazat -> Vymazat vše.
Nyní jednoduše zkopírujte kontingenční tabulku, kterou chcete duplikovat, a vložte ji (buď do stejného listu, nebo do samostatného listu).
- Doporučuje se vložit do samostatných listů, aby se při rozbalení nepřekrývalo s druhou kontingenční tabulkou. I když to někdy kopíruji vedle sebe, abych porovnával různé pohledy. Toto vložení kopie kontingenční tabulky zajišťuje sdílení sdílené mezipaměti.