seychellesartprojects.org

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 expression.refreshtable , pomocí tohoto můžeme aktualizovat kontingenční tabulka odkazem na list, který ji tvoří, nebo můžeme odkázat 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ě ve zdrojových datech, uživatel musí kontingenční tabulku aktualizovat přechodem ke konkrétní kontingenční tabulce 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í tabulku.

K překonání tohoto problému musíme napsat jednoduchý kód makra aplikace Excel, abychom obnovili kontingenční tabulku, kdykoli dojde ke změně zdrojových dat.

Tuto šablonu Excel kontingenční tabulky VBA Refresh Table si můžete stáhnout zde - šablonu Excel kontingenční tabulky VBA Refresh

# 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 (rozsah 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“.

Nyní tento kód 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, stačí 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.

Co tedy můžeme udělat, je, že pomocí jediného kódu pomocí smyček můžeme procházet všechny kontingenční tabulky v sešitu a aktualizovat 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 „Deaktivace pracovního listu“.

Při přechodu z jednoho listu na jiný deaktivujte aktualizace událostí v kontingenční tabulce.