V tomto článku se naučíme Jak automaticky aktualizovat data kontingenční tabulky v aplikaci Excel.
Scénář:
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. Potřebujeme aktualizovat kontingenční tabulky, abychom viděli změny při každém otevření excelového sešitu. A pokud odešlete aktualizovaný soubor bez obnovení kontingenčních tabulek, můžete se cítit trapně. Zde se tedy dozvíte, jak najít možnost obnovení při použití kontingenční tabulky
Při otevírání souboru v aplikaci Excel aktualizujte data
Nejprve vytvořte kontingenční tabulku a poté klikněte pravým tlačítkem na libovolnou buňku kontingenční tabulky.
Přejděte na Možnosti kontingenční tabulky> karta Data> Zaškrtněte políčko, které říká Při otevírání souboru aktualizujte data
To umožní automatické aktualizace dat při každém otevření souboru.
Příklad:
Pochopit to všechno může být matoucí. Pojďme pochopit, jak používat funkci pomocí příkladu. Zde máme nějaká data a musíme nejprve vytvořit kontingenční tabulku a poté najít možnosti, jak povolit automatické aktualizace kontingenčních tabulek.
Vytvořte kontingenční tabulku a poté klikněte pravým tlačítkem na libovolnou buňku kontingenční tabulky, jak je znázorněno níže.
Vyberte Možnosti kontingenční tabulky a otevře se dialogové okno Možnosti tabulky PIvot.
Vyberte kartu data a poté zaškrtněte políčko Obnovit data při otevírání souboru. Můžete to provést bez otevření a zavření souboru pomocí VBA.
Pomocí VBA
Zde 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 v objektu Zdrojový list
Soukromý dílčí list_Deaktivovat () sheetname_of_pivot_table.Kontingenční tabulky ("název_kontingenční_tabulky") .PivotCache.Refresh End 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.
| Soukromý dílčí list_Deaktivovat ()
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čí list_Deaktivovat ()
'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Pro každý počítač v ThisWorkbook.PivotCaches pc Obnovit 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.
| Soukromý dílčí pracovní list_Změna (cíl podle cíle jako rozsah)
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ěna (cíl podle cíle jako rozsah)
ThisWorkbook.RefreshAll End Sub |
Poznámka : 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.
Doufám, že tento článek o tom, jak automaticky aktualizovat data kontingenční tabulky v aplikaci Excel, je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
Jak dynamicky aktualizovat rozsah zdrojů dat 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 : Abychom spustili vaše 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é úkoly v aplikaci Excel. Tyto zkratky vám pomohou zvýšit efektivitu práce v Excelu.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
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.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.