Kontingenční tabulka a její aktualizace pomocí VBA

Publikoval(a) admin dne

Kontingenční tabulka je jedním z nejčastěji používaných nástrojů v excelu pro středně pokročilého uživatele. Aktualizace samotných dat v kontingenční tabulce je relativně jednoduché (pomocí Excel GUI – Obnovit), ale je velice snadné na tuto akci zapomenout. V tomto článku (zaměřeném právě na tyto pokročilejší uživatele) se podíváme jak vytvořit jednoduché makro, které zajistí automatickou aktualizace všech dat kontingenčních tabulek uložených v PivotCache, pokud dojde ke změně zdrojových dat pro kontingenční tabulku.

https://drive.google.com/file/d/18Ns5R0_gUUXGF8Sj-4lhxhe4Xu2VPvJS/view?usp=sharing

Kontingenční tabulka a Pivot Cache

Každá kontingenční tabulka se vždy skládá ze tří základních komponent. Zdrojová data, Pivot Cache (PivotCache je název objektu používaného v jazyce VBA a také název pro samostatnou „pamět“ do které si kontingenční tabulka ukládá data – Pivot v anglickém jazyce znamená kontingenční, tedy Pivot Table = Kontingenční tabulka) a samotná kontingenční tabulka, která je objektem na úrovni listu (o objektovém modelu MS Excel) a musí být umístěna alespoň na jednom listu.

Pivot Cache má za úkol jednu jedinou věc. Uchovávat data pro kontingenční tabulku . Pokud vaše kontingenční tabulky pracují se stejným zdrojem dat, je více než pravděpodobné že zároveň sdílejí stejnou Pivot Cache. Pivot cache má za úkol zrychlit načítání dat do kontingenční tabulky, pokud dojde ke změně filtru, nebo při přidání nového sloupce. Důležitou informací pro nás je fakt, že kontingenční tabulky mohou sdílet pivot cache. Tedy pokud obnovíme jednu kontingenční tabulku, ve skutečnosti dochází k aktualizaci pivot cachce. Pokud tabulky sdílejí stejnou pivot cache, dojde k aktualizaci všech tabulek. Pokud se chcete podívat, která kontingenční tabulka používá jakou cache, můžete použít .CacheIndex.

Aktualizujeme kontingenční tabulku

Nejjednodušší způsob jak aktualizovat kontingenční tabulku je použít metodu .RefreshTable. Pokud známe jméno dané tabulky a její umístění v sešitu, tak se jedná o krátký příkaz na jeden řádek. Pro jednoduchost si vytvoříme list „List2“ a na něm kontingenční tabulku „Kontingenční tabulka1„.

Náš příkaz pro obnovení této kontingenční tabulky by vypadal následovně:

Sheets("List2").PivotTables("Kontingenční tabulka1").RefreshTable
VB

Kontingenční tabulka a hromadná aktualizace

Nyní když dokážeme aktualizovat jednu tabulku, je nejspíš namístě si ukázat jak aktualizovat všechny tabulky pro případ, že v sešitu máte více než jednu kontingenční tabulku. Pro aktualizaci ostatních tabulek existují dvě možnosti. Postupně projít všechny tabulky na všech listech a aktualizovat je pomocí .RefreshTable, nebo použít .RefreshAll. Po použití této metody bude obnovena každá kontingenční tabulka ve vašem sešitu.

Sub RefreshPTables()    
    ThisWorkbook.RefreshAll 
End Sub
VB

Jak se jmenuje moje tabulka?

Pokud znáte jména všech kontingenčních tabulek, nebo chcete pouze aktualizovat všechny pomocí .RefreshAll, máte vyhráno. Pokud ale potřebujete zjistit jak se které tabulky jmenují, a na jakém listu se nacházejí, máte dvě možnosti. Postupně projít všechny listy a kontrolovat, jak se která tabulka jmenuje. Nebo si napsat krátký skript, který vám vrátí názvy všech tabulek v sešitu. Pro zobrazení jmen všech tabulek na jednom listu:

Sub GetTNamesOnSheet()
    For Each Ptable In Sheets("List1").PivotTables    
        Debug.Print Ptable.Name
    Next Ptable 
End Sub
VB

Pokud chcete zjistit jména všech tabulek na všech listech vašeho sešitu, musíte použít dvě smyčky pro zjištění jména listu a jména kontingenční tabulky(tabulky na jiných listech se mohou jmenovat stejně :-/):

Sub GetAllPtablesInWbook()
    For Each SheetName In ThisWorkbook.Sheets    
        For Each Ptable In SheetName.PivotTables        
            Debug.Print Ptable.Name, SheetName.Name    
        Next Ptable
    Next SheetName
End Sub
VB

Nyní když známe jména všech tabulek a jejich umístění v jednotlivých listech, můžeme jednoduše aktualizovat jednotlivé kontingenční tabulky dle potřeby. Pokud vaše tabulky používají rozdílnou PivotCachce, můžete tímto způsobem aktualizovat pouze vybrané tabulky. Jediné co musíte udělat, je změnit Debug.Print na Ptable.RefreshTable.

Sub RefreshAllPitovTables()
    For Each SheetName In ThisWorkbook.Sheets    
        For Each Ptable In SheetName.PivotTables        
            Ptable.RefreshTable    
        Next Ptable
    Next SheetNameEnd 
Sub
VB

0 komentářů

Napsat komentář

Avatar placeholder

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *