V předchozím článku jsme se dozvěděli, jak můžete dynamicky měnit a aktualizovat jednotlivé kontingenční tabulky pomocí zmenšování nebo rozšiřování zdrojů dat.
V tomto článku se naučíme, jak můžeme zajistit, aby všechny kontingenční tabulky v sešitu automaticky měnily zdroj dat. Jinými slovy, namísto změny jedné kontingenční tabulky najednou se pokusíme změnit zdroj dat všech kontingenčních tabulek v sešitu, aby dynamicky zahrnovaly nové řádky a sloupce přidané do zdrojových tabulek a okamžitě odrážely změnu v kontingenčních tabulkách.
Napište kód do zdrojového listu
Protože chceme, aby to bylo zcela automatické, použijeme k zápisu kódu místo jádrového modulu archové moduly. To nám umožní používat události pracovního listu.
Pokud jsou zdrojová data a kontingenční tabulky na různých listech, napíšeme kód VBA, abychom změnili zdroj dat kontingenční tabulky v objektu listu, který obsahuje zdrojová data (nikoli ta, která obsahuje kontingenční tabulky).
Stisknutím CTRL+F11 otevřete editor VB. Nyní přejděte do Průzkumníka projektů a najděte list, který obsahuje zdrojová data. Poklepejte na něj.
Otevře se nová oblast kódování. Možná neuvidíte žádnou změnu, ale nyní máte přístup k událostem v listu.
Klikněte na rozevírací nabídku vlevo a vyberte list. V levé rozevírací nabídce vyberte deaktivovat. Uvidíte prázdný díl napsaný na názvu oblasti kódu worksheet_deativate. V tomto bloku kódu půjde náš kód pro dynamicky se měnící zdrojová data a aktualizaci kontingenční tabulky. Tento kód se spustí vždy, když přepnete z datového listu na jakýkoli jiný list. Zde si můžete přečíst o všech událostech pracovního listu.
Nyní jsme připraveni implementovat kód.
Zdrojový kód pro dynamickou 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 List3 obsahují kontingenční tabulky, které závisí na zdrojových datech listu2.
Nyní jsem napsal tento kód do kódovací oblasti sheet1. Používám událost Worksheet_Deactivate, takže se tento kód spustí k aktualizaci kontingenční tabulky, kdykoli přepneme ze zdrojového listu.
Private Sub Worksheet_Deactivate () Dim source_data As Range 'Určení posledního řádku a čísla sloupce lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column 'Nastavení nového rozsahu Nastavit zdrojové_data = Rozsah (buňky (1, 1), buňky (lstrow, lstcol))' 'Kód pro procházení každým listem a kontingenční tabulkou pro každý ws v ThisWorkbook.Worksheets For Every pt In ws.PivotTables pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Další pt Další ws Konec Sub
Pokud máte podobný sešit, můžete tato data přímo zkopírovat. Vysvětlil jsem, že tento kód funguje níže, takže jej můžete upravit podle svých potřeb.
Účinek tohoto kódu můžete vidět v gifu níže.
Jak tento kód automaticky mění zdrojová data a aktualizuje kontingenční tabulky?
Nejprve jsme použili událost worksheet_deactivate. Tato událost se spustí pouze v případě, že je list obsahující kód přepnut nebo deaktivován. 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 = Cells (1, Columns.Count) .End (xlToLeft) .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 = rozsah (buňky (1, 1), buňky (lstrow, lstcol))
Nyní máme zdrojová data, která jsou dynamická. Jen to musíme použít 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. Aby nezůstala žádná kontingenční tabulka. K tomu používáme vnořené smyčky.
Za každý ws v ThisWorkbook.Worksheets
Pro každý pt v ws.Kontingenčních tabulkách
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
Typ zdroje: = xlDatabase, _
SourceData: = source_data)
Další pt
Další ws
První smyčka prochází každým listem. Druhá smyčka iteruje nad každou kontingenční tabulkou v listu.
Kontingenční tabulky jsou přiřazeny k proměnné pt. Používáme metodu ChangePivotCache objektu pt. Dynamicky vytváříme mezipaměť pivotů pomocí ThisWorkbook.PivotCaches.Create
Metoda. Tato metoda přebírá 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 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 ano, lidi, takto můžete dynamicky měnit rozsahy zdrojů dat všech kontingenčních tabulek v sešitu v Excelu. Doufám, že jsem to vysvětlil dostatečně. 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 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.
Jak automaticky aktualizovat kontingenční tabulky pomocí VBA: K automatické aktualizaci kontingenčních tabulek můžete použít události VBA. Tento jednoduchý řádek kódu použijte k automatické aktualizaci kontingenční tabulky. Můžete použít jednu ze tří metod automatického obnovení kontingenčních tabulek.
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.