Jak dynamicky aktualizovat všechny zdroje dat kontingenčních tabulek v aplikaci Excel

V předchozím článku jsme se dozvěděli, jak můžete dynamicky měnit a aktualizovat jednotlivé kontingenční tabulky se zmenšováním nebo rozšiřováním zdrojů dat.

V tomto článku budeme Zjistěte, jak můžeme v kontingenční tabulce v sešitu automaticky změnit zdroj dat. Jinými slovy, místo změny jedné kontingenční tabulky najednou se pokusíme změnit zdroj dat všech kontingenčních tabulek v sešitu tak, aby dynamicky zahrnoval nové řádky a sloupce přidané do zdrojových tabulek a okamžitě promítl změnu kontingenčních tabulek.

Zápis kódu do zdrojového datového listu

Protože chceme, aby to bylo zcela automatické, použijeme k zápisu kódu místo základního modulu listové moduly. To nám umožní používat události listu.

Pokud jsou zdrojová data a kontingenční tabulky v různých listech, zapíšeme kód VBA, abychom změnili zdroj dat kontingenční tabulky v objektu listu, který obsahuje zdrojová data ( který neobsahuje kontingenční tabulky).

Stisknutím kláves CTRL + F11 otevřete editor VB. Nyní přejděte do průzkumníka projektu a najděte list, který obsahuje zdrojová data. Poklepejte na něj.

Otevře se nová oblast kódování. Možná nevidíte žádnou změnu, ale nyní máte přístup k událostem v listu.

Klikněte na levou rozbalovací nabídku a vyberte list. V rozbalovací nabídce vlevo vyberte možnost deaktivovat. Na názvu oblasti kódu worksheet_deativate uvidíte prázdný díl. Náš kód pro dynamickou změnu zdrojových dat a osvěžující kontingenční tabulku bude použit v tomto bloku kódu. Tento kód se spustí, kdykoli přepnete z datového listu na jakýkoli jiný list. O všech událostech v listu si můžete přečíst zde.

Nyní jsme připraveni kód implementovat.

Zdrojový kód k dynamické aktualizaci všech kontingenčních tabulek v sešitu s novým rozsahem

Abych vysvětlil, jak to funguje, mám sešit. Tento sešit obsahuje tři listy. List1 obsahuje zdrojová data, která se mohou změnit. List2 a Sheet3 obsahují kontingenční tabulky, které závisí na zdrojových datech listu2.

Nyní jsem tento kód napsal v oblasti kódování listu1. Používám událost Worksheet_Deactivate, takže tento kód běží k aktualizaci kontingenční tabulka, kdykoli přejdeme ze zdrojového datového listu.

Pokud máte podobný sešit, můžete tato data přímo zkopírovat. Vysvětlil jsem, že tento kód funguje níže, abyste jej mohli upravit podle svých potřeb.

Účinek tohoto kódu můžete vidět níže v gifu.

Jak se tento kód automaticky mění zdrojová data a aktualizace kontingenčních tabulek?

Nejprve jsme použili událost worksheet_deactivate. Tato událost se spustí, pouze když je list obsahující kód přepnut nebo deaktivován. Takže takto se kód automaticky spustí.

Abychom dynamicky získali celou tabulku jako rozsah dat, určíme poslední řádek a poslední sloupec.

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Buňky (1, Columns.Count) .End (xlToL eft) .Column

Pomocí těchto dvou čísel definujeme source_data. Jsme si jisti, že rozsah zdrojových dat bude vždy začínat od A1. Můžete definovat vlastní počáteční odkaz na buňku.

Nastavit source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

Nyní máme zdrojová data, která jsou dynamický. Musíme jej použít pouze v kontingenční tabulce.

Protože nevíme, kolik kontingenčních tabulek bude sešit obsahovat najednou, projdeme každý list a kontingenční tabulky každého listu. že nezůstane žádná kontingenční tabulka. K tomu používáme vnořené smyčky.

Pro každý ws v ThisWorkbook.Worksheets

Pro každý pt v ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Další pt

Další ws

První smyčka prochází každým listem. Druhá smyčka iteruje přes každou kontingenční tabulku v listu.

Kontingenční tabulky jsou přiřazeny do proměnné pt. Používáme metodu ChangePivotCache objektu pt. Dynamicky vytváříme pivotní mezipaměť pomocí metody ThisWorkbook.PivotCaches.Create

. Tato metoda bere dvě proměnné SourceType a SourceData. Jako typ zdroje deklarujeme xlDatabase a jako SourceData předáme rozsah source_data, který jsme vypočítali dříve.

A to i je to. Naše kontingenční tabulky máme automatizované. Tím se automaticky aktualizují všechny kontingenční tabulky v sešitu.

Takže jo, takto můžete dynamicky měnit rozsahy zdrojů dat všech kontingenčních tabulek v sešitu v aplikaci Excel. Doufám, že jsem byl dostatečně vysvětlující. Máte-li jakékoli dotazy týkající se tohoto článku, dejte mi vědět v sekci komentáře níže.

Jak dynamicky aktualizovat rozsah zdroje dat kontingenční tabulky v aplikaci Excel: K dynamickému 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 libovolnou kontingenční tabulku změnou rozsahu zdrojových dat.

Jak automaticky obnovovat kontingenční tabulky pomocí VBA: K automatickému obnovení kontingenčních tabulek můžete použít události VBA. Pomocí tohoto jednoduchého řádku kódu můžete automaticky aktualizovat kontingenční tabulku. Můžete použít některou ze 3 metod automatického obnovování kontingenčních tabulek.

Spustit makro, pokud dojde k jakékoli změně na listu ve specifikovaném rozsahu: Ve vašich postupech VBA byste dostali potřebu spouštět makra, když určitý rozsah nebo buněčné změny. V takovém případě ke spuštění maker při změně cílového rozsahu použijeme událost změny.

Spustit makro při jakékoli změně na listu | Abychom spustili vaše makro, kdykoli se list aktualizuje, používáme události listu VBA.

Nejjednodušší kód VBA ke zvýraznění aktuálního řádku a sloupce pomocí | Pomocí tohoto malého fragmentu VBA zvýrazněte aktuální řádek a sloupec listu.

Události pracovního listu v aplikaci Excel VBA | Událost listu je opravdu užitečná, pokud chcete, aby se vaše makra spouštěla, když na listu dojde k určité události.

Populární články:

50 zkratek aplikace Excel pro zvýšení produktivity | Zrychlete svůj úkol. Těchto 50 zkratek vám umožní pracovat ještě rychleji v aplikaci Excel. Funkce VLOOKUP v aplikaci Excel | Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí aplikace Excel, která se používá k vyhledání hodnoty z různých rozsahů a listů.

COUNTIF v aplikaci Excel 2016 | Počítejte hodnoty s podmínkami pomocí této úžasné funkce. Chcete-li spočítat konkrétní hodnotu, 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 sečíst hodnoty za konkrétních podmínek.