Aktualizovat kontingenční tabulku ve VBA

Obvykle vytváříme kontingenční tabulku, když potřebujeme vytvořit nějaké grafy nebo grafy nebo musíme provést nějakou analýzu. Připravením kontingenční tabulky můžeme získat celkový pohled a představu o tom, co je vlastně uvnitř dat. To je nejlepší způsob, jak najít způsob, jak se dostat do dat. A pokaždé, když provedeme jakékoli změny v datech, musíme také aktualizovat kontingenční tabulku. Aby naplnil také aktualizovaný počet dat. Obnovení jakékoli kontingenční tabulky je velmi snadné, ale co když máme 10s kontingenčních tabulek v jednom excelovém souboru, který musíme aktualizovat. Takže namísto obnovení všech kontingenčních tabulek jeden po druhém, můžeme přímo obnovit všechny kontingenční tabulky najednou pomocí VBA Refresh Pivot Table.

Jak používat aktualizaci kontingenční tabulky v aplikaci Excel VBA?

Níže jsou uvedeny různé příklady použití aktualizační kontingenční tabulky v aplikaci Excel pomocí kódu VBA.

Tuto šablonu kontingenční tabulky VBA pro aktualizaci tabulky si můžete stáhnout zde - šablonu kontingenční tabulky VBA pro aktualizaci tabulky

Kontingenční tabulka VBA - příklad # 1

Pro aktualizaci kontingenční tabulky musíme nejprve vytvořit kontingenční tabulku. Níže máme údaje o řádkové položce 1000 s, pomocí které budeme vytvářet kontingenční tabulku.

Na výše uvedeném snímku vidíme poslední řádek na 1001 a tato data obsahují informace o zákaznících oproti množství, které jim bylo prodáno.

Nyní přejděte na kartu Vložit nabídku a klikněte na možnost Kontingenční tabulka .

Dostaneme pole Vytvořit kontingenční tabulku. Odtud nejprve vyberte rozsah tabulky, kterou chceme zahrnout do kontingenční tabulky. Poté vyberte libovolné umístění v aktuálním listu pro kontingenční tabulku. Můžeme si také vybrat Nový list.

Po dokončení klikněte na OK.

Vytvoříme kontingenční tabulku. Nyní přetáhněte povinná pole do různých oblastí a získáte skutečnou kontingenční tabulku. Zde přetahujeme jméno zákazníka do ROWS a množství do sloupců, jak je ukázáno níže.

Tím je dokončeno vytváření kontingenční tabulky. Musíme však aktualizovat kontingenční tabulku po změně čehokoli v Data, mohlo by to být provedeno jednoduše aktualizací možnosti z rozbalovací nabídky Pravým tlačítkem myši, jak je ukázáno níže.

Stejný proces však lze automatizovat i pomocí VBA. K tomu bychom potřebovali modul. Tak,

Krok 1: Přejděte na kartu Vložit a v rozevíracím seznamu vyberte možnost Modul, jak je uvedeno níže.

Krok 2: Do nově otevřeného modulu napište podkategorii VBA Pivot Refresh, nebo si můžeme zvolit libovolný název podle našeho výběru.

Kód:

 Sub Pivot_Refresh2 () End Sub

Krok 3: Nejprve definujte proměnnou jako PivotCache, jak je ukázáno níže. PivotCache používá mezipaměť kontingenční tabulky, nikoli data použitá pro vytvoření kontingenční tabulky.

Kód:

 Sub Pivot_Refresh2 () Dim Table jako PivotCache End Sub

Krok 4: Nyní použijeme smyčku pro každou smyčku. Takže otevřete smyčku pro každý, jak je ukázáno níže.

Kód:

 Sub Pivot_Refresh2 () Dim Table jako PivotCache pro každý další konec tabulky Sub

Krok 5: Nyní uvnitř smyčky For-End zapíšeme podmínku, ve které vybereme aktuálně otevřený list, který má kontingenční tabulku s PivotCache .

Kód:

 Sub Pivot_Refresh2 () Dim tabulka jako PivotCache pro každou tabulku v ThisWorkbook.PivotCaches Další Konec tabulky Sub

Krok 6: Nyní použijeme příkaz Refresh, který mu přiřadí definovanou proměnnou Table .

Kód:

 Sub Pivot_Refresh2 () Dim tabulka jako PivotCache pro každou tabulku v ThisWorkbook.PivotCaches Table.Refresh Next End End Sub Sub

Krok 7: Tím je dokončen kód, nyní zkompilujte kód do souboru chybu stisknutím funkční klávesy F8. Nyní abychom otestovali, zda psaný kód nefunguje, změnili jsme množství Zákazníka14 na 69 .

Krok 8: Nyní spusťte kód. Uvidíme, že celkový počet proti zákazníkovi14 je aktualizován na 2183, což je zvýrazněno žlutou barvou.

Kontingenční tabulka VBA - příklad # 2

Existuje jiný způsob, jak aktualizovat kontingenční tabulku pomocí VBA. Než se přesuneme dopředu, můžeme zvážit změnu názvu kontingenční tabulky nebo můžeme použít také výchozí název. Zkusme dát kontingenční tabulce nový název. Za tímto účelem vyberte kontingenční tabulku a přejděte na kartu Analyzovat nabídku, jak je uvedeno níže.

V první části názvu kontingenční tabulky můžeme vidět výchozí název jako kontingenční tabulka1 .

Nyní změníme toto jméno. Zvažte napsat nový název jako zákaznická data, jak je uvedeno níže.

Krok 1: Poté otevřete nový modul a zapište podkategorii aktualizace VBA, jak je ukázáno níže.

Kód:

 Sub Pivot_Refresh3 () End Sub

Krok 2: Nyní definujte proměnnou jako kontingenční tabulku . Zde s kontingenční tabulkou budeme brát v úvahu kompletní zdrojová data.

Kód:

 Sub Pivot_Refresh3 () Dim Tabulka jako kontingenční tabulka End Sub

Krok 3: Nyní použijte Set s definovanou proměnnou a vyberte list, který je právě otevřen.

Kód:

 Sub Pivot_Refresh3 () Dim tabulka jako PivotTable Set Table = ActiveSheet. End Sub

Krok 4: Vyberte název kontingenční tabulky, kterou chceme aktualizovat, spolu s proměnným datovým typem, který jsme použili jako kontingenční tabulku .

Kód:

 Sub Pivot_Refresh3 () Dim tabulka jako PivotTable Set Table = ActiveSheet.PivotTables ("Customer Data") End Sub

Krok 5: Nakonec použijte proměnnou s funkcí RefreshTable pro její provedení.

Kód:

 Sub Pivot_Refresh3 () Dim Table jako PivotTable Set Table = ActiveSheet.PivotTables ("Customer Data") Table.RefreshTable End Sub Sub

Krok 6: Udělejme nějaké další změny v datech, abychom získali skutečnou vizualitu aplikovaného kódu.

Počet zákazníků2 jsme změnili na 56 . Nyní, když spustíme kód, v kontingenční tabulce jména zákazníka by měly být změny v součtu množství pro zákazníka2 .

Krok 7: Nyní přejděte do okna VBA a sestavte kód. Pokud nebyla nalezena žádná chyba, spusťte ji kliknutím na tlačítko Přehrát, které je pod panelem nabídek, jak je znázorněno níže. Všimneme si, že součet množství prodaného pro Customer2 se nyní změní na 1724 provedením změn ve zdrojové tabulce.

Tím můžeme zahrnout více než jeden zdroj dat a vytvořit jinou kontingenční tabulku. A automatizace těchto kontingenčních tabulek je také snadná, protože stačí uvést název kontingenční tabulky a list, kde se tabulka nachází.

Výhody revoluční kontingenční tabulky VBA

  • Obnovení kontingenční tabulky pomocí kódu VBA trvá velmi méně času.
  • Implementace je velmi snadná.

Co si pamatovat

  • Můžeme přidat více tabulek zdrojů dat a automatizovat je pomocí kódu VBA.
  • Chcete-li vidět, že ke změnám došlo, udržujte okno VBA a list aplikace Excel navzájem rovnoběžné.
  • Je lepší pojmenovat každou kontingenční tabulku, pokud jednáte a pracujete s více zdroji dat a kontingenčními tabulkami.
  • Kód s názvem kontingenční tabulky lze snadno pochopit, sledovat a vyhledat.

Doporučené články

Toto je průvodce aktualizací kontingenční tabulky VBA. Zde diskutujeme o tom, jak používat Excel VBA Refresh Pivot Table spolu s praktickými příklady a stahovatelnou Excel šablonou. Můžete si také prohlédnout naše další doporučené články -

  1. VBA FileSystemObject (FSO)
  2. Funkce podstrčení Excel
  3. Délka řetězce VBA
  4. Excel ISNUMBER vzorec

Kategorie:

VBA