Sečteno a podtrženo: Naučte se, jak používat jednoduché makro k automatickému obnovení kontingenčních tabulek, kdykoli dojde ke změnám ve zdrojových datech. Sdílím také řešení bez makra k aktualizaci kontingenčních tabulek při otevření souboru. Zahrnuje videonávod a stahování souborů Excel.
Úroveň dovedností: středně pokročilá
Stáhněte si soubor Excel
Pokud se nejlépe naučíte tím, že si to uděláte sami, můžete stáhněte si soubor, který používám ve videu, abych mohl pokračovat. Tady je soubor Excel, který obsahuje kód VBA.
Obnovit kontingenční tabulku automaticky.xlsm (41,5 KB)
Automatická aktualizace kontingenčních tabulek
Lze své kontingenční tabulky aktualizovat okamžitě a automaticky, když se změní jejich zdrojová data?
Absolutně. Vyžaduje použití opravdu jednoduchého makra, které vám ukážu, jak vytvořit níže.
Pokud nejste příliš obeznámeni s makry a VBA, doporučuji vyzkoušet si moji bezplatnou třídílnou sérii videí o zahájení používání maker & VBA.
Také, pokud jste v kontingenčních tabulkách noví, Mám řadu, která vás provede tím, čím jsou a jak je používat. Podívejte se na první video v této sérii na kontingenčních tabulkách & Řídicí panely
Abychom automaticky aktualizovali naše kontingenční tabulky, napíšeme makro pomocí jedné jednoduché instrukce. Tato instrukce v podstatě říká: když provedu změnu ve svém listu, obnovte všechny kontingenční tabulky a datová připojení. Tady jsou kroky k vytvoření makra.
Otevřete editor jazyka.
To provedete kliknutím na tlačítko Visual Basic na kartě Vývojář na pásu karet.
Klávesová zkratka pro otevření editoru jazyka je Alt + F11.
Pokud nevidíte na kartě Vývojář ji můžete zviditelnit podle pokynů zde. Musíte to udělat pouze jednou a karta Vývojář bude vždy viditelná pokaždé, když v budoucnu otevřete Excel.
Otevřete modul Sheet, který obsahuje vaše zdrojová data.
V okně Průzkumník projektu editoru jazyka vyhledejte sešit, který chcete změnit. Pod tímto sešitem jsou uvedeny listy v sešitu. Vyberte list, který obsahuje zdrojová data. Poté na něj dvakrát klikněte.
Pokud nevidíte okno Průzkumníka projektu, můžete jej povolit z pohledu nabídka (klávesová zkratka: Ctrl + R).
Přidat novou událost pro změny v listu.
Poklepáním na list otevřete modul kódu pro daný objekt. V rámci modulu kódu chceme vytvořit makro události. Chcete-li tak učinit, vyberte v rozevíracím seznamu Objekt vlevo list.
Tím se do události Worksheet_SelectionChange přidá událost modul, který vlastně nechceme, takže ho za chvíli smažeme. Než to uděláme, pojďme do rozbalovací nabídky Procedura vpravo a zvolte Změnit.
Tím se přidá nový událost nahoře s názvem Worksheet_Change. Nyní pod ním zvýrazníme a odstraníme nepotřebný kód.
Makro události Worksheet_Change se spustí při každé změně do buněk v tomto listu. K události Worksheet_Change můžeme přidat kód VBA, abychom mohli provádět akce, když uživatel upraví buňky.
Poznámka: Událost SelectionChange, která je přidána ve výchozím nastavení, se spustí pokaždé, když uživatel vybere buňku v listu. Protože chceme, aby se kód spustil, jen když uživatel upraví / změní buňky, použijeme událost Change. Podívejte se na můj článek o modulech kódu VBA & Jak spustit makra na základě událostí uživatele a dozvědět se více o modulech a událostech listu.
Obnovte kód VBA všechny kontingenční tabulky.
Dále těsně pod řádek Worksheet_Change zadejte tento pokyn:
ThisWorkbook.RefreshAll
Metoda RefreshAll obnoví všechny kontingenční tabulky, dotazy a datová připojení v sešitu. Tato akce je stejná, jako když ručně kliknete na tlačítko Obnovit na kartě Data.
Přidejte tento řádek kódu do události Worksheet_Change, obnoví sešit vždy, když dojde ke změně listu, kterým je kód. in.
Kontingenční tabulka & Zdrojová data na stejném listu
Aleksandrs položil skvělou otázku ohledně komentářů k videu na YouTube. Pokud jsou vaše kontingenční tabulka a zdrojová data na stejném listu, budete muset přidat kód pro deaktivaci událostí.
Obnovení vloží událost do rekurzivní smyčky a může skončit havárií aplikace Excel. Zde je kód, který tomu zabrání.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Kontrola, zda je vaše makro funkční
Jeden způsob, jak zkontrolovat, zda makro funguje je otestovat to. Proveďte změnu zdrojových dat a zjistěte, zda se projeví v kontingenční tabulce.Pokud vaši změnu není snadné zjistit, protože máte příliš mnoho dat, nebo z nějakého jiného důvodu, existuje další způsob, jak zjistit, zda se vaše makro spouští.
V editoru VB můžete kliknout na šedý sloupec nalevo od makra Worksheet_Change. Tím se zobrazí červený kruh. Rovněž zvýrazňuje tento řádek kódu červeně.
Tomu se říká stop nebo zarážka.
Klávesová zkratka pro zapnutí / vypnutí zarážky je: F9
Nyní, kdykoli dojde k akci, která spustí makro, Excel přeskočí na editor VB a pozastaví makro, abyste mohli zkontrolovat kód. V našem případě je touto akcí jakákoli změna provedená v listu.
Poté můžete stisknutím klávesy F8 projít každou řádku nebo stisknutím klávesy F5 přejdete na konec (nebo další zarážku).
Pokud provedete změnu v listu a Excel vás nestáhne do editoru VB, víte, že je problém s makro nefunguje. Pokud je to váš případ, je pravděpodobné, že jste soubor neuložili jako sešit s povolenými makry (.xlsm) nebo povolená makra. Možná budete muset uložit & zavřít soubor, znovu jej otevřít a povolit makra.
Chcete-li odstranit zarážku, kterou jste do makra vložili, stačí kliknout na červený kruh, aby zmizel (klávesová zkratka: F9).
Klávesová zkratka pro vymazání všech zarážek je: Ctrl + Shift + F9
Obnovení kontingenčních tabulek bez a Makro
Jednou z nevýhod použití tohoto makra k obnovení kontingenčních tabulek je, že při každém spuštění makra dojde ke ztrátě jakékoli historie Zpět. Jinými slovy, když kliknete na tlačítko Zpět (nebo stisknete Ctrl + Z), Excel si nebude pamatovat poslední věc, kterou jste udělal, takže to nebude možné vrátit zpět. V důsledku toho se nic nestane a vaše poslední změna nebude vrácena.
Existuje alternativa, která vám umožní uchovat si vaši historii zpět. Tato alternativa však obnovuje kontingenční tabulku pouze při otevření sešitu, ne při každé změně. Tuto možnost můžete použít takto.
Počínaje libovolnou buňkou v kontingenční tabulce:
- Přejděte na pásu karet na kartu Analýza.
- Zvolte tlačítko Možnosti.
- V novém okně, které se otevře, přejděte na kartu Data.
- Zaškrtněte políčko „Obnovit data při otevření souboru.“
Po kliknutí na OK se může zobrazit následující varovná zpráva, pokud máte více kontingenčních tabulek vytvořených ze stejného rozsahu zdrojových dat. Kliknutím na OK se k tomu dostanete.
Opět jen pro srovnání, pokud použijete tuto možnost, uchováte historii zpět, ale obnoví kontingenční tabulku pouze při zavření a opětovném otevření sešitu. Pokud použijete možnost makra, ztratíte historii zpět, ale kontingenční tabulka se automaticky aktualizuje kdykoli dojde k jakékoli změně v sešitu.
Varianty pro aktualizaci kontingenčních tabulek
Makro, které jsme prohlíželi, nejen obnoví vaše kontingenční tabulky, ale také obnoví všechny dotazy. . Pokud chcete obnovit pouze kontingenční tabulky, můžete místo toho nahradit příkaz „ThisWorkbook.RefreshAll“ tímto kódem:
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
Každá kontingenční tabulka je připojena k základní kontingenční mezipaměť, která je připojena ke zdrojovým datům. Kód prochází všemi kontingenčními mezipaměti v sešitu a každou z nich obnovuje. Kontingenční tabulky ze stejného rozsahu zdrojů mohou sdílet kontingenční mezipaměti, takže tato metoda je rychlejší než opakování všech kontingenčních tabulek .
Podobně řekněme, že chcete obnovit pouze jednu konkrétní kontingenční tabulku. V takovém případě můžete vyměnit kód „ThisWorkbook.RefreshAll“ za kód uvedený níže.
A nakonec, pokud používáte Power Query a chcete deaktivovat aktualizaci pozadí, aby se dotazy obnovovaly PŘED kontingenčními tabulkami, napsal jsem článek, který vysvětluje, jak to udělat, deaktivací aktualizace pozadí v dotazech.
Použijte Místo toho deaktivovat událost
Další možností je použít místo Worksheet_Change událost Worksheet_Deactivate. Událost Worksheet_Deactivate se spustí pokaždé, když uživatel list opustí a vybere jiný list. To uživateli umožní provést všechny změny v zdrojová data, pak se kontingenční tabulka automaticky obnoví, když přejdou na jakýkoli jiný list, včetně listů, které obsahují kontingenční tabulku.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Tento kód bude stále umístěn v modulu listu, který obsahuje zdrojová data. To je dobrá volba, pokud vaše kontingenční tabulky nebo datová připojení několik sekund nebo déle k aktualizaci a nechcete čekat pokaždé, když dojde ke změně zdrojových dat.
Jediné, co možná nebudete chtít použít, je, pokud je vaše kontingenční tabulka a zdroj údaje jsou na stejném listu.To bude obvykle ojedinělý případ a něco, co obecně nedoporučuji.
Díky doporučení Teda k tomuto.
Ušetřit čas & Rozpaky
Doufám, že vám tento článek pomůže ušetřit čas a usnadní uživatelům vašich souborů. Může také pomoci předcházet rozpakům, když zapomenete obnovit kontingenční tabulky před odesláním zpráv. Věřte já jsem udělal tuto chybu vícekrát, než bych si chtěl přiznat … 🙂
