Jak všichni víme, kdykoli provedeme změny ve zdrojových datech kontingenční tabulky, neodráží se to okamžitě v kontingenční tabulce. Abychom viděli změny, musíme aktualizovat kontingenční tabulky. A pokud odešlete aktualizovaný soubor bez obnovení kontingenčních tabulek, můžete se cítit trapně.
V tomto článku se tedy naučíme, jak automaticky aktualizovat kontingenční tabulku pomocí VBA. Tento způsob je jednodušší, než jste si představovali.
Toto je jednoduchá syntaxe k automatické aktualizaci kontingenčních tabulek v sešitu.
'Kód ve zdrojovém datovém listu Objekt Soukromá dílčí pracovní list_Deaktivovat () název_listu_kontingenční_tabulky.Konkrétní tabulky („název_kontingenční_tabulky“). PivotCache.Refresh Konec Sub
Co jsou to kontingenční mezipaměti?
Každá kontingenční tabulka ukládá data do kontingenční mezipaměti. To je důvod, proč je pivot schopen zobrazit předchozí data. Když aktualizujeme kontingenční tabulky, aktualizuje mezipaměť o nová zdrojová data, aby odrážela změny v kontingenční tabulce.
Potřebujeme tedy pouze makro na aktualizaci mezipaměti kontingenčních tabulek. Uděláme to pomocí události listu, abychom nemuseli spouštět makro ručně.
Kde kódovat automatické kontingenční tabulky?
Pokud jsou vaše zdrojová data a kontingenční tabulky na různých listech, pak by měl kód VBA jít do zdrojového listu.
Zde použijeme událost Worksheet_SelectionChange. Díky tomu se kód spustí vždy, když přepneme ze zdrojového listu na jiný list. Později vysvětlím, proč jsem tuto událost použil.
Zde mám zdrojová data v listu2 a kontingenční tabulky v listu1.
Otevřete VBE pomocí kláves CTRL+F11. V Průzkumníku projektů můžete vidět tři objekty, List1, List2 a Sešit.
Protože Sheet2 obsahuje zdrojová data, poklepejte na objekt sheet2.
Nyní můžete vidět dvě rozbalovací nabídky v horní části oblasti kódu. Z prvního rozevíracího seznamu vyberte list. A z druhého rozevíracího seznamu vyberte Deaktivovat. Tím se vloží prázdný podnázev Worksheet_Deactivate. Náš kód bude zapsán v tomto sub. Všechny řádky zapsané v tomto dílčím odstavci budou spuštěny, jakmile uživatel přepne z tohoto listu na jakýkoli jiný list.
Na sheet1 mám dvě kontingenční tabulky. Chci aktualizovat pouze jednu kontingenční tabulku. K tomu potřebuji znát název kontingenční tabulky. Chcete -li znát název jakékoli kontingenční tabulky, vyberte libovolnou buňku v této kontingenční tabulce, přejděte na kartu analýzy kontingenční tabulky. Na levé straně uvidíte název kontingenční tabulky. Zde můžete také změnit název kontingenční tabulky.
Nyní známe název kontingenční tabulky, můžeme napsat jednoduchý řádek pro aktualizaci kontingenční tabulky.
Private Sub Worksheet_Deactivate () Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
A je hotovo.
Nyní, kdykoli přepnete ze zdrojových dat, se spustí tento kód vba k aktualizaci kontingenční tabulky1. Jak můžete vidět na obrázku níže.
Jak aktualizovat všechny kontingenční tabulky v sešitu?
Ve výše uvedeném příkladu jsme chtěli aktualizovat pouze jednu konkrétní kontingenční tabulku. Pokud ale chcete aktualizovat všechny kontingenční tabulky v sešitu, stačí v kódu provést drobné změny.
Soukromý dílčí pracovní list_Deaktivovat () 'List1.PivotTables ("PivotTable1"). PivotCache.Refresh pro každý počítač v ThisWorkbook.PivotCaches pc.Refresh Další pc End Sub
V tomto kódu používáme smyčku For k procházení jednotlivými mezipaměti v sešitu. Objekt ThisWorkbook obsahuje všechny mezipaměti mezipaměti. K přístupu k nim používáme ThisWorkbook.PivotCaches.
Proč používat událost Worksheet_Deactivate?
Pokud chcete kontingenční tabulku aktualizovat, jakmile dojde ke změně zdrojových dat, měli byste použít událost Worksheet_Change. Ale nedoporučuji to. Váš sešit spustí kód pokaždé, když v listu provedete jakoukoli změnu. Než budete chtít vidět výsledek, možná budete muset provést stovky změn. Ale excel obnoví kontingenční tabulku při každé změně. To povede ke ztrátě času a zdrojů na zpracování. Pokud tedy máte kontingenční tabulky a data v různých listech, je lepší použít událost Deaktivovat list. To vám umožní dokončit vaši práci. Jakmile přepnete na listy kontingenční tabulky, aby se změny zobrazily, změny se provedou.
Pokud máte kontingenční tabulky a zdrojová data na stejném listu a chcete, aby si kontingenční tabulky automaticky obnovovaly samy, můžete použít událost Worksheet_Change.
Private Sub Worksheet_Change (ByVal Target As Range) Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Jak obnovit vše v sešitech, když dojde ke změně zdrojových dat?
Pokud chcete v sešitu obnovit vše (grafy, kontingenční tabulky, vzorce atd.), Můžete použít příkaz ThisWorkbook.RefreshAll.
Soukromý dílčí pracovní list_Změnit (ByVal cíl jako rozsah) ThisWorkbook.RefreshAll End Sub
Tento kód nemění zdroj dat. Pokud tedy přidáte data pod zdrojová data, tento kód tato data nezahrne automaticky. K ukládání zdrojových dat můžete použít tabulky aplikace Excel. Pokud nechcete používat tabulky, můžeme použít VBA také pro zahrnutí nových dat. Naučíme se to v dalším tutoriálu.
Ano, kámo, takto můžete automaticky aktualizovat kontingenční tabulky v aplikaci Excel. Doufám, že jsem byl dostatečně vysvětlující a tento článek vám dobře posloužil. Máte -li jakékoli dotazy týkající se tohoto tématu, můžete se mě zeptat v sekci komentáře níže.
Jak dynamicky aktualizovat rozsah zdrojů kontingenční tabulky v aplikaci Excel: K dynamické změně rozsahu zdrojových dat kontingenčních tabulek používáme kontingenční mezipaměti. Těchto několik řádků může dynamicky aktualizovat jakoukoli kontingenční tabulku změnou rozsahu zdrojových dat. Ve VBA používejte objekty kontingenčních tabulek, jak je uvedeno níže…
V případě změny provedené na listu ve specifikovaném rozsahu spusťte makro: Ve vašich postupech VBA byste dostali potřebu spouštět makra, když se změní určitý rozsah nebo buňka. V takovém případě ke spuštění maker při změně cílového rozsahu použijeme událost change.
Spustit makro po provedení jakékoli změny na listu | Chcete -li tedy spustit makro vždy, když se list aktualizuje, používáme události pracovního listu VBA.
Nejjednodušší kód VBA pro zvýraznění aktuálního řádku a sloupce pomocí | Pomocí tohoto malého úryvku VBA zvýrazněte aktuální řádek a sloupec listu.
Události listu v aplikaci Excel VBA | Událost listu je opravdu užitečná, když chcete, aby se vaše makra spustila, když na listu dojde k zadané události.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Funkce VLOOKUP v Excelu | Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
COUNTIF v aplikaci Excel 2016 | Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétní hodnoty nepotřebujete filtrovat data. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.