Ako všetci vieme, kedykoľvek vykonáme zmeny v zdrojových údajoch kontingenčnej tabuľky, neodráža sa to okamžite v kontingenčnej tabuľke. Aby sme videli zmeny, musíme obnoviť kontingenčné tabuľky. A ak odošlete aktualizovaný súbor bez obnovenia kontingenčných tabuliek, môžete pocítiť rozpaky.
V tomto článku sa teda naučíme, ako automaticky aktualizovať kontingenčnú tabuľku pomocou jazyka VBA. Tento spôsob je jednoduchší, ako ste si predstavovali.
Toto je jednoduchá syntax na automatické obnovenie kontingenčných tabuliek v zošite.
'Kód v zdrojovom dátovom hárku Objekt súkromný čiastkový pracovný hárok_Deaktivovať () hárok_na_kontingenčnej_tabulky.Konsolidačné tabuľky ("názov_kontingenčného_tabuľky"). PivotCache.Refresh End Sub
Čo sú to kontingenčné cache?
Každá kontingenčná tabuľka ukladá údaje do kontingenčnej pamäte cache. Preto môže pivot zobrazovať predchádzajúce údaje. Keď obnovíme kontingenčné tabuľky, aktualizuje vyrovnávaciu pamäť novými zdrojovými údajmi, aby odrážali zmeny v kontingenčnej tabuľke.
Na obnovu pamäte cache kontingenčných tabuliek teda potrebujeme iba makro. Urobíme to pomocou udalosti pracovného hárka, aby sme makro nemuseli spúšťať ručne.
Kde kódovať automatickú obnovu kontingenčných tabuliek?
Ak sú vaše zdrojové údaje a kontingenčné tabuľky na rôznych listoch, kód VBA by mal byť uvedený v zdrojovom hárku.
Tu použijeme udalosť Worksheet_SelectionChange. Vďaka tomu sa kód spustí vždy, keď prejdeme zo zdrojového listu na iný list. Neskôr vysvetlím, prečo som využil túto udalosť.
Tu mám zdrojové údaje v hárku2 a kontingenčné tabuľky v hárku1.
Otvorte VBE pomocou klávesov CTRL+F11. V Prieskumníkovi projektov môžete vidieť tri objekty, List1, List2 a zošit.
Pretože Sheet2 obsahuje zdrojové údaje, dvakrát kliknite na objekt sheet2.
Teraz môžete vidieť dve rozbaľovacie ponuky v hornej časti oblasti kódu. V prvom rozbaľovacom zozname vyberte pracovný hárok. A z druhej rozbaľovacej ponuky vyberte Deaktivovať. Tým sa vloží prázdny pod názov Worksheet_Deactivate. Náš kód bude zapísaný v tomto pododdiele. Všetky riadky napísané v tomto pododdiele sa spustia hneď, ako sa používateľ prepne z tohto hárku na iný hárok.
Na hárku 1 mám dve kontingenčné tabuľky. Chcem aktualizovať iba jednu kontingenčnú tabuľku. Na to potrebujem vedieť názov kontingenčnej tabuľky. Ak chcete poznať názov akejkoľvek kontingenčnej tabuľky, vyberte ľubovoľnú bunku v tejto kontingenčnej tabuľke a prejdite na kartu analýzy kontingenčnej tabuľky. Na ľavej strane uvidíte názov kontingenčnej tabuľky. Tu môžete tiež zmeniť názov kontingenčnej tabuľky.
Teraz poznáme názov kontingenčnej tabuľky, môžeme napísať jednoduchý riadok na aktualizáciu kontingenčnej tabuľky.
Súkromný čiastkový pracovný hárok_Deaktivovať () List1.Kontingenčné tabuľky („Kontingenčná tabuľka1“). PivotCache.Refresh Koniec Sub
A je hotovo.
Teraz, kedykoľvek prepnete zo zdrojových údajov, sa spustí tento kód vba na obnovenie kontingenčnej tabuľky1. Ako vidíte na obrázku nižšie.
Ako obnoviť všetky kontingenčné tabuľky v zošite?
V uvedenom príklade sme chceli obnoviť iba jednu konkrétnu kontingenčnú tabuľku. Ak však chcete v zošite obnoviť všetky kontingenčné tabuľky, stačí, ak v kóde urobíte malé zmeny.
Súbor Sub Subheet_Deactivate () 'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh for each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
V tomto kóde používame slučku For na to, aby sme mohli prechádzať každou medzipamäťou v zošite. Objekt ThisWorkbook obsahuje všetky kontingenčné pamäte cache. Na prístup k nim používame ThisWorkbook.PivotCaches.
Prečo používať udalosť Worksheet_Deactivate?
Ak chcete kontingenčnú tabuľku obnoviť hneď po vykonaní akejkoľvek zmeny zdrojových údajov, mali by ste použiť udalosť Worksheet_Change. Ale neodporúčam to. Z vášho zošita sa spustí kód pri každej zmene hárka. Skôr ako budete chcieť vidieť výsledok, možno budete musieť vykonať stovky zmien. Excel však obnoví kontingenčnú tabuľku pri každej zmene. To povedie k plytvaniu časom a zdrojmi spracovania. Ak teda máte kontingenčné tabuľky a údaje v rôznych listoch, je lepšie použiť udalosť deaktivácie pracovného hárka. Umožňuje vám dokončiť prácu. Keď prepnete na hárky kontingenčnej tabuľky, aby ste videli zmeny, zmeny zmení.
Ak máte na jednom hárku kontingenčné tabuľky a zdrojové údaje a chcete, aby sa kontingenčné tabuľky automaticky obnovovali, môžete použiť udalosť Worksheet_Change.
Súkromný čiastkový pracovný hárok_Zmeniť (ByVal cieľ ako rozsah) List 1. Kontingenčné tabuľky („PivotTable1“). PivotCache.Refresh End Sub
Ako obnoviť všetko v zošitoch, keď dôjde k zmene zdrojových údajov?
Ak chcete obnoviť všetko v zošite (grafy, kontingenčné tabuľky, vzorce, atď.), Môžete použiť príkaz ThisWorkbook.RefreshAll.
Súkromný čiastkový pracovný hárok_Zmeniť (ByVal cieľ ako rozsah) ThisWorkbook.RefreshAll End Sub
Upozorňujeme, že tento kód nemení zdroj údajov. Ak teda pridáte údaje pod zdrojové údaje, tento kód tieto údaje automaticky nezahrnie. Na uloženie zdrojových údajov môžete použiť tabuľky programu Excel. Ak nechcete používať tabuľky, môžeme použiť VBA aj na zahrnutie nových údajov. Naučíme sa to v nasledujúcom návode.
Takže áno, kamarát, takto môžete automaticky obnoviť kontingenčné tabuľky v programe Excel. Dúfam, že som bol dostatočne vysvetľujúci a tento článok vám dobre slúžil. Ak máte akékoľvek otázky týkajúce sa tejto témy, môžete sa ma opýtať v sekcii komentárov nižšie.
Ako dynamicky aktualizovať rozsah zdrojov údajov v kontingenčnej tabuľke v programe Excel: Na dynamickú zmenu rozsahu zdrojových údajov kontingenčných tabuliek používame kontingenčné pamäte cache. Týchto niekoľko riadkov môže dynamicky aktualizovať akúkoľvek kontingenčnú tabuľku zmenou rozsahu zdrojových údajov. Vo VBA používajte objekty kontingenčných tabuliek, ako je uvedené nižšie …
V prípade zmeny vykonanej na hárku v určenom rozsahu spustite makro: Vo vašich postupoch VBA by ste potrebovali spustiť makrá, keď sa zmení určitý rozsah alebo bunka. V takom prípade na spustenie makier pri zmene cieľového rozsahu použijeme udalosť zmeny.
Spustite makro, keď sa na hárku vykonajú akékoľvek zmeny | Na spustenie makra vždy, keď sa aktualizuje hárok, preto používame udalosti pracovného hárka VBA.
Najjednoduchší kód VBA na zvýraznenie aktuálneho použitia riadkov a stĺpcov | Pomocou tohto malého útržku VBA zvýraznite aktuálny riadok a stĺpec listu.
Udalosti pracovného hárka v programe Excel VBA | Udalosť pracovného hárka je skutočne užitočná, keď chcete, aby sa vaše makrá spustili, keď sa v hárku vyskytne zadaná udalosť.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Funkcia VLOOKUP v programe Excel | Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
COUNTIF v Exceli 2016 | Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnej hodnoty nepotrebujete filtrovať svoje údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.