Obnovovacia kontingenčná tabuľka programu Excel VBA
Keď vložíme kontingenčnú tabuľku do hárka, akonáhle sa údaje zmenia, údaje kontingenčnej tabuľky sa nezmenia, musíme to urobiť manuálne, ale vo VBA existuje príkaz na obnovenie kontingenčnej tabuľky, ktorý je expression.refreshtable , pomocou tohto môžeme aktualizovať kontingenčná tabuľka odkazom na pracovný hárok, ktorý ju obsahuje, alebo môžeme odkazovať na celé kontingenčné tabuľky v pracovných hárkoch a obnovovať ich naraz.
Kontingenčná tabuľka je nevyhnutná pri analýze veľkého množstva údajov. Pomáha hneď pri analýze, sumarizácii a tiež pri interpretácii užitočných údajov. Jedným z problémov tejto kontingenčnej tabuľky je však to, že sa automaticky neobnoví, ak dôjde k zmene zdrojových údajov. Používateľ musí kontingenčnú tabuľku obnoviť prechodom na konkrétnu kontingenčnú tabuľku vždy, keď dôjde k zmene. Ale rozlúčte sa s manuálnym procesom, pretože tu máme spôsob obnovenia kontingenčnej tabuľky, akonáhle v nej urobíte akékoľvek zmeny.
Ako automaticky aktualizovať kód VBA údajov kontingenčnej tabuľky?
Kontingenčnú tabuľku je potrebné aktualizovať iba v prípade, že dôjde k akejkoľvek zmene v zdrojových údajoch kontingenčnej tabuľky, na ktorú odkazujeme.
Napríklad si pozrite nasledujúce údaje a kontingenčnú tabuľku.
Teraz zmením čísla v zdrojových dátach, tj z A1 na B17.
V bunke B9 musím zmeniť hodnotu zo 499 na 1499, tj. O 1 000 zväčšenie údajov, ale ak sa pozriete na otočný ukazovateľ, stále zobrazuje výsledok ako 4295 namiesto 5295. Musím ručne aktualizovať svoju kontingenčnú tabuľku, aby som aktualizoval kontingenčná tabuľka.
Na prekonanie tohto problému musíme napísať jednoduchý kód makra programu Excel, ktorý obnoví kontingenčnú tabuľku vždy, keď dôjde k zmene zdrojových údajov.
# 1 - Jednoduché makro na obnovenie celej tabuľky
Krok 1: Zmena udalosti v údajovom liste
Musíme spustiť udalosť zmeny v údajovom liste. V editore jazyka Visual Basic dvakrát kliknite na údajový hárok.
Po dvojitom kliknutí na hárok vyberte možnosť „Pracovný hárok“ a udalosť vyberte ako „Zmeniť“.
Uvidíte automatický vedľajší postup otvorený ako Worksheet_Change (rozsah ByVal Target As Range)
Krok 2: Použite objekt pracovného hárka
Pozrite si údajový list pomocou objektu Pracovné listy.
Krok 3: Odkazujte na kontingenčnú tabuľku podľa názvu
Názov kontingenčnej tabuľky nájdete pod názvom kontingenčnej tabuľky.
Krok 4: Použite metódu Obnoviť tabuľku
Vyberte metódu ako „Obnoviť tabuľku“.
Tento kód teraz obnoví kontingenčnú tabuľku „PivotTable1“ vždy, keď dôjde k akejkoľvek zmene v zdrojovom údajovom liste. Môžete použiť nižšie uvedený kód. Musíte len zmeniť názov kontingenčnej tabuľky.
Kód:
Súkromný vedľajší pracovný hárok_Change (cieľový rozsah ByVal) pracovný hárok ("údajový list"). Kontingenčné tabuľky ("kontingenčná tabuľka1"). Obnovovacia koncová tabuľka
# 2 - Obnovte všetky kontingenčné tabuľky v rovnakom pracovnom hárku
Ak máte v rovnakom pracovnom hárku veľa kontingenčných tabuliek, môžete všetky kontingenčné tabuľky obnoviť jediným kliknutím. Pomocou nižšie uvedeného kódu obnovíte všetky kontingenčné tabuľky v hárku.
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
Musíte zmeniť názov hárka a názvy kontingenčnej tabuľky podľa podrobností vášho hárka.
# 3 - Obnoviť všetky tabuľky v zošite
Je vysoko nepravdepodobné, že by sme mali všetky kontingenčné tabuľky na rovnakom pracovnom hárku. Zvyčajne sa pre každú správu snažíme pridať samostatné kontingenčné tabuľky do samostatných hárkov. V týchto prípadoch nemôžeme pokračovať v písaní kódu pre každú kontingenčnú tabuľku, ktorá sa má aktualizovať.
To, čo môžeme urobiť, je teda jediný kód pomocou slučiek, môžeme prechádzať všetky kontingenčné tabuľky v zošite a obnovovať ich jediným kliknutím na tlačidlo.
Nasledujúci kód prehľadá každú kontingenčnú tabuľku a obnoví ich.
Kód 1:
Sub Refresh_Pivot_Tables_Example2 () Dim PT ako kontingenčná tabuľka pre každý PT v ActiveWorkbook. Kontingenčné tabuľky PT.RefreshTable ďalší PT End Sub
Kód 2:
Sub Refresh_Pivot_Tables_Example3 () Dim PC ako PivotCache pre každý PC v ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub
Oba kódy obnovia kontingenčné tabuľky.
Ak chcete, aby sa kontingenčná tabuľka obnovila hneď, ako dôjde k akejkoľvek zmene v údajovom hárku otočného hárku, je potrebné vyššie uvedené kódy skopírovať a vložiť do udalosti Zmena pracovného hárka v danom zošite.
# 4 - Vyvarujte sa načítaniu času pomocou udalosti deaktivácie pracovného hárka
Keď použijeme udalosť „Zmena pracovného hárka“, aktualizuje sa, aj keď nedôjde k žiadnej zmene v zdroji údajov, ale ak dôjde k akejkoľvek zmene v pracovnom hárku.
Aj keď v pracovnom hárku zadáte jednu bodku, pokúsi sa obnoviť kontingenčnú tabuľku. Aby sme sa tomu vyhli, môžeme namiesto metódy „Zmena pracovného hárka“ použiť metódu „Deaktivovať pracovný hárok“.
Pri prechode z jedného hárka na iný hárok deaktivujte aktualizácie udalostí v kontingenčnej tabuľke.
