Obnovovací kontingenční tabulka aplikace Excel VBA
Když vložíme kontingenční tabulku do listu, jakmile se data změní, data kontingenční tabulky se sama nezmění, musíme to udělat ručně, ale ve VBA existuje příkaz k aktualizaci kontingenční tabulky, který je výrazem. Obnovitelná , pomocí toho můžeme aktualizovat kontingenční tabulka odkazem na list, který ji tvoří, nebo můžeme odkazovat na celé kontingenční tabulky v listech a aktualizovat je všechny najednou.
Kontingenční tabulka je zásadní při analýze obrovského množství dat. Pomáhá hned při analýze, sumarizaci a provádění užitečné interpretace dat. Jedním z problémů této kontingenční tabulky je však to, že se automaticky neobnoví, pokud dojde ke změně zdrojových dat. Uživatel musí kontingenční tabulku aktualizovat přechodem na konkrétní kontingenční tabulku pokaždé, když dojde ke změně. Ale rozloučte se s ručním procesem, protože zde máme metodu k obnovení kontingenční tabulky, jakmile provedete jakoukoli změnu v kontingenční tabulce.
Jak automaticky aktualizovat kód VBA dat kontingenční tabulky?
Pivotní tabulku je třeba aktualizovat pouze v případě, že dojde ke změně zdrojových dat kontingenční tabulky, na kterou odkazujeme.
Podívejte se například na níže uvedená data a kontingenční tabulku.
Nyní změním čísla ve zdrojových datech, tj. Z A1 na B17.
V buňce B9 musím změnit hodnotu z 499 na 1499, tj. 1000 zvýšení dat, ale pokud se podíváte na pivot, stále zobrazuje výsledek jako 4295 místo 5295. Musím ručně aktualizovat svou kontingenční tabulku, abych aktualizoval kontingenční tabulka.
Abychom tento problém překonali, musíme napsat jednoduchý kód makra aplikace Excel, abychom obnovili kontingenční tabulku, kdykoli dojde ke změně zdrojových dat.
# 1 - Jednoduché makro pro obnovení celé tabulky
Krok 1: Změňte událost v datovém listu
Musíme spustit událost změny datasheetu. V editoru jazyka Visual Basic dvakrát klikněte na datový list.
Jakmile dvakrát kliknete na list, vyberte „List“ a vyberte událost jako „Změnit“.
Uvidíte automatický dílčí postup otevřený jako Worksheet_Change (ByVal Target As Range)
Krok 2: Použijte objekt listu
Podívejte se na datový list pomocí objektu Worksheets.
Krok 3: Viz kontingenční tabulka podle názvu
Název kontingenční tabulky najdete podle názvu kontingenční tabulky.
Krok 4: Použijte metodu Obnovit tabulku
Vyberte metodu jako „Obnovit tabulku“.
Tento kód nyní obnoví kontingenční tabulku „PivotTable1“, kdykoli dojde ke změně ve zdrojovém datovém listu. Můžete použít níže uvedený kód. Musíte pouze změnit název kontingenční tabulky.
Kód:
Soukromý dílčí list_Change (cíl ByVal jako rozsah) Pracovní list ("Datový list"). Kontingenční tabulky ("Kontingenční tabulka1"). RefreshTable End Sub
# 2 - Obnovte všechny kontingenční tabulky ve stejném listu
Pokud máte ve stejném listu mnoho kontingenčních tabulek, můžete všechny kontingenční tabulky obnovit jediným kliknutím. Pomocí níže uvedeného kódu obnovíte všechny kontingenční tabulky v listu.
Kód:
Sub Refresh_Pivot_Tables_Example1 () Worksheets ("Data Sheet"). Select With ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4"). RefreshTable .PivotTables ("Table5"). RefreshTable End With End Sub
Je třeba změnit název listu a názvy kontingenční tabulky podle podrobností listu.
# 3 - Obnovit všechny tabulky v sešitu
Je vysoce nepravděpodobné, že máme všechny kontingenční tabulky na stejném listu. Obvykle se pro každou zprávu snažíme přidat samostatné kontingenční tabulky do samostatných listů. V těchto případech nemůžeme pokračovat v psaní kódu pro každou kontingenční tabulku, která se má aktualizovat.
Takže, co můžeme udělat, je jediný kód pomocí smyček, můžeme projít všechny kontingenční tabulky v sešitu a obnovit je jediným kliknutím na tlačítko.
Níže uvedený kód projde každou kontingenční tabulkou a obnoví je.
Kód 1:
Sub Refresh_Pivot_Tables_Example2 () Dim PT jako kontingenční tabulka pro každý PT v ActiveWorkbook.PivotTables PT.RefreshTable Next PT End Sub
Kód 2:
Sub Refresh_Pivot_Tables_Example3 () Dim PC jako PivotCache pro každý PC v ActiveWorkbook.PivotCaches PC.Refresh Další PT End Sub
Oba kódy obnoví kontingenční tabulky.
Pokud chcete, aby se kontingenční tabulka obnovila, jakmile dojde k jakékoli změně v datovém listu otočného listu, musíte zkopírovat a vložit výše uvedené kódy do události Změna listu v daném sešitu.
# 4 - Vyvarujte se načítání času pomocí události Deaktivovat list
Když použijeme událost „Změna listu“, bude se obnovovat, i když nedojde ke změně zdroje dat, ale pokud dojde k nějaké změně v listu.
I když v listu zadáte jednu tečku, pokusí se obnovit kontingenční tabulku. Abychom tomu zabránili, můžeme místo metody „Změnit list“ použít metodu „Deaktivovat list“.
Při přechodu z jednoho listu na jiný deaktivujte aktualizace událostí v kontingenční tabulce.
