Obnoviť kontingenčnú tabuľku vo VBA

Spravidla vytvárame kontingenčnú tabuľku, keď potrebujeme vytvoriť nejaký graf alebo graf alebo ak potrebujeme vykonať nejakú analýzu nad ňou. Pripravením kontingenčnej tabuľky môžeme získať celkový pohľad a predstavu o tom, čo je v skutočnosti v údajoch. Toto je najlepší spôsob, ako nájsť spôsob, ako sa dostať k údajom. A vždy, keď urobíme akékoľvek zmeny v údajoch, musíme tiež aktualizovať kontingenčnú tabuľku. Aby sa vyplnil aj aktualizovaný počet údajov. Obnovenie akejkoľvek kontingenčnej tabuľky je veľmi jednoduché, ale čo keď máme 10s kontingenčných tabuliek v jednom excelovom súbore, ktorý musíme aktualizovať. Takže namiesto obnovovania všetkých kontingenčných tabuliek jeden po druhom, môžeme priamo obnoviť všetky kontingenčné tabuľky naraz pomocou VBA obnovovacej kontingenčnej tabuľky.

Ako používať obnovovaciu kontingenčnú tabuľku v programe Excel VBA?

Nižšie sú uvedené rôzne príklady použitia obnovovacej kontingenčnej tabuľky v programe Excel pomocou kódu VBA.

Túto šablónu VBA obnovovacej kontingenčnej tabuľky si môžete stiahnuť tu - šablónu VBA obnovovacej kontingenčnej tabuľky

Kontingenčná tabuľka VBA - príklad č. 1

Na obnovenie kontingenčnej tabuľky je potrebné najprv vytvoriť kontingenčnú tabuľku. Nižšie sú uvedené údaje o riadkovej položke 1 000 s, pomocou ktorej vytvoríme kontingenčnú tabuľku.

Na obrázku vyššie vidíme posledný riadok na čísle 1001 a tieto údaje obsahujú informácie o zákazníkoch oproti množstvu, ktoré sa im predalo.

Teraz prejdite na kartu Vložiť ponuku a kliknite na možnosť Kontingenčná tabuľka .

Dostaneme pole Vytvoriť kontingenčnú tabuľku. Odtiaľ najskôr vyberte rozsah tabuľky, ktorú chceme zahrnúť do kontingenčnej tabuľky. Potom vyberte akékoľvek miesto v aktuálnom hárku pre kontingenčnú tabuľku. Môžeme si tiež zvoliť Nový pracovný hárok.

Po dokončení kliknite na tlačidlo OK.

Vytvoríme kontingenčnú tabuľku. Teraz potiahnite požadované polia do rôznych oblastí, aby ste získali skutočnú kontingenčnú tabuľku. Tu presúvame meno zákazníka do ROWS a množstvo do stĺpcov, ako je to znázornené nižšie.

Týmto sa dokončí vytvorenie kontingenčnej tabuľky. Musíme však aktualizovať kontingenčnú tabuľku po zmene čohokoľvek v údajoch, bolo by to možné urobiť jednoducho obnovením možnosti z rozbaľovacej ponuky pravým tlačidlom myši, ako je to zobrazené nižšie.

Rovnaký proces sa však dá automatizovať aj prostredníctvom VBA. Na to by sme potrebovali modul. takže,

Krok 1: Prejdite na kartu Vložiť a z rozbaľovacieho zoznamu vyberte možnosť Modul, ako je to znázornené nižšie.

Krok 2: Do novo otvoreného modulu napíšte podkategóriu VBA Pivot Refresh alebo si môžeme zvoliť ľubovoľný názov podľa nášho výberu.

kód:

 Sub Pivot_Refresh2 () End Sub

Krok 3: Najskôr definujte premennú ako PivotCache, ako je to znázornené nižšie. PivotCache používa vyrovnávaciu pamäť Pivot, nie údaje použité na vytvorenie kontingenčnej tabuľky.

kód:

 Sub Pivot_Refresh2 () Dim Table ako PivotCache End Sub

Krok 4: Teraz použijeme pre každú slučku. Takže otvorte slučku For-Every, ako je uvedené nižšie.

kód:

 Sub Pivot_Refresh2 () Dim Table ako PivotCache pre každú nasledujúcu End Table Sub

Krok 5: Teraz v rámci slučky For-End napíšeme podmienku, v ktorej vyberieme aktuálne otvorený pracovný hárok, ktorý má kontingenčnú tabuľku s kontingenčnou hodnotou PivotCache .

kód:

 Sub Pivot_Refresh2 () Dim tabuľka ako PivotCache pre každú tabuľku v ThisWorkbook.PivotCaches Ďalší koniec tabuľky Sub

Krok 6: Teraz použijeme príkaz Obnoviť, ktorý mu priradí definovanú premennú Tabuľka .

kód:

 Sub Pivot_Refresh2 () Dim tabuľka ako PivotCache pre každú tabuľku v ThisWorkbook.PivotCaches Table.Refresh Next End End Sub Sub

Krok 7: Týmto sa dokončí kód, teraz kompilujte kód do súboru stlačením funkčného klávesu F8. Teraz, aby sme otestovali, či napísaný kód nefunguje, zmenili sme množstvo zákazníka14 na 69 .

Krok 8: Teraz spustite kód. Uvidíme, že celkový počet zákazníkov14 bol aktualizovaný na 2183, čo je zvýraznené žltou farbou.

Kontingenčná tabuľka VBA - príklad č. 2

Existuje aj iný spôsob, ako obnoviť kontingenčnú tabuľku pomocou VBA. Predtým, ako sa pohneme vpred, môžeme zvážiť zmenu názvu kontingenčnej tabuľky alebo môžeme použiť aj predvolený názov. Skúsme dať kontingenčnej tabuľke nový názov. Vyberte kontingenčnú tabuľku a prejdite na kartu ponuky Analyzovať, ako je to znázornené nižšie.

V prvej časti názvu kontingenčnej tabuľky vidíme predvolený názov ako kontingenčná tabuľka1 .

Teraz zmeníme toto meno. Zvážte napísať nový názov ako Údaje o zákazníkoch, ako je uvedené nižšie.

Krok 1: Potom otvorte nový modul a napíšte podkategóriu VBA Refresh, ako je to znázornené nižšie.

kód:

 Sub Pivot_Refresh3 () End Sub

Krok 2: Teraz definujte premennú ako kontingenčnú tabuľku . V tomto prípade s kontingenčnou tabuľkou zvážime úplné zdrojové údaje.

kód:

 Sub Pivot_Refresh3 () Dim Table ako PivotTable End Sub

Krok 3: Teraz použite Set s definovanou premennou a vyberte hárok, ktorý je práve otvorený.

kód:

 Sub Pivot_Refresh3 () Dim tabuľka ako tabuľka kontingenčnej tabuľky = ActiveSheet. End Sub

Krok 4: Vyberte názov kontingenčnej tabuľky, ktorú chceme obnoviť, spolu s typom premennej údajov, ktorý sme použili ako kontingenčnú tabuľku .

kód:

 Sub Pivot_Refresh3 () Dim tabuľka ako tabuľka kontingenčných tabuliek = ActiveSheet.PivotTables („zákaznícke údaje“) End Sub

Krok 5: Nakoniec použite premennú s funkciou RefreshTable na jej vykonanie.

kód:

 Sub Pivot_Refresh3 () Dim Tabuľka ako PivotTable Set Table = ActiveSheet.PivotTables ("Údaje o zákazníkoch") Table.RefreshTable End Sub Sub

Krok 6: Urobíme nejaké ďalšie zmeny v údajoch, aby sme poskytli reálne vizualizácie aplikovaného kódu.

Počet zákazníkov2 sme zmenili na 56 . Ak teraz spustíme kód, v kontingenčnej tabuľke mena zákazníka by sa mali vyskytnúť zmeny v súčte množstva pre zákazníka2 .

Krok 7: Teraz prejdite do okna VBA a zostavte kód. Ak sa nenájde žiadna chyba, spustite ju kliknutím na tlačidlo Prehrať, ktoré je pod panelom s ponukami, ako je uvedené nižšie. Zaznamenáme, že suma predaná pre zákazníka2 sa teraz zmení na 1724 vykonaním zmien v zdrojovej tabuľke.

Týmto môžeme zahrnúť viac ako jeden zdroj údajov a vytvoriť inú kontingenčnú tabuľku. A automatizácia týchto kontingenčných tabuliek je tiež jednoduchá, pretože stačí uviesť názov kontingenčnej tabuľky a hárok, kde sa tabuľka nachádza.

Výhody obnovovacej kontingenčnej tabuľky VBA

  • Obnovenie kontingenčnej tabuľky pomocou kódu VBA zaberie veľmi menej času.
  • Implementácia je veľmi jednoduchá.

Čo je potrebné pamätať

  • Môžeme pridať viac tabuliek zdrojov údajov a automatizovať ich pomocou kódu VBA.
  • Ak chcete vidieť zmeny, ktoré sa stali, nechajte okno VBA a hárok programu Excel navzájom rovnobežné.
  • Ak obchodujete s viacerými zdrojmi údajov a kontingenčnými tabuľkami a manipulujete s nimi, je lepšie pomenovať každú kontingenčnú tabuľku.
  • Kód s názvom kontingenčnej tabuľky je ľahko zrozumiteľný, sledovateľný a lokalizovateľný.

Odporúčané články

Toto je návod na aktualizáciu kontingenčnej tabuľky VBA. Tu diskutujeme o tom, ako používať Excel VBA Obnoviť kontingenčnú tabuľku spolu s praktickými príkladmi a stiahnuteľnou vynikajúcou šablónou. Môžete si tiež prečítať naše ďalšie navrhované články -

  1. VBA FileSystemObject (FSO)
  2. Funkcia podčiarknutia Excel
  3. Dĺžka reťazca VBA
  4. Excel ISNUMBER vzorec

Kategórie:

VBA