V současné době můžeme kontingenční tabulky dynamicky měnit nebo aktualizovat pomocí tabulek aplikace Excel nebo dynamicky pojmenovaných rozsahů. Tyto techniky však nejsou spolehlivé. Protože stále budete muset kontingenční tabulku aktualizovat ručně. Pokud máte velká data, která obsahují tisíce řádků a sloupců, excelové tabulky vám moc nepomohou. Místo toho bude váš soubor těžký. Jedinou cestou tedy zůstává VBA.
V tomto článku se naučíme, jak můžeme z naší kontingenční tabulky automaticky změnit zdroj dat. Jinými slovy, zautomatizujeme ruční proces změny zdroje dat tak, aby dynamicky zahrnoval nové řádky a sloupce přidané do zdrojových tabulek a okamžitě zohlednil změnu v kontingenční tabulce.
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í tabulku).
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 kontingenční tabulky s novým rozsahem
Abych vysvětlil, jak to funguje, mám sešit. Tento sešit obsahuje dva listy. List1 obsahuje zdrojová data, která se mohou změnit. List2 obsahuje kontingenční tabulku, 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 pt As PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft). Sloupec Sada source_data = Rozsah (buňky (1, 1), buňky (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivotCache pc 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.
Úč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í.
Nyní, abychom změnili zdrojová data kontingenční tabulky, měníme data v kontingenční mezipaměti.
Kontingenční tabulka je vytvořena pomocí kontingenční mezipaměti. Kontingenční mezipaměť obsahuje stará zdrojová data, dokud se kontingenční tabulka neaktualizuje ručně nebo se ručně nezmění rozsah zdrojových dat.
Vytvořili jsme reference kontingenčních tabulek s názvem pt, mezipaměti s názvem pc a rozsahu s názvem source_data. Zdrojová data budou obsahovat celá data.
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.
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.
Tato data ukládáme do mezipaměti, protože víme, že mezipaměť ukládá všechna data.
Nastavit pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Dále definujeme kontingenční tabulku, kterou chceme aktualizovat. Protože chceme aktualizovat kontingenční tabulku1 (název kontingenční tabulky. Název kontingenční tabulky můžete zkontrolovat na kartě analýzy při výběru kontingenční tabulky.) Na sheet1, nastavíme pt podle obrázku níže.
Nastavit pt = Sheet2.PivotTables ("PivotTable1")
Nyní tuto kontingenční mezipaměť jednoduše použijeme k aktualizaci kontingenční tabulky. Používáme metodu changePivotCache objektu pt.
pt.ChangePivotCache pc
A naši kontingenční tabulku máme automatizovanou. Tím se automaticky aktualizuje vaše kontingenční tabulka. Pokud máte více tabulek se stejným zdrojem dat, použijte v každém objektu kontingenční tabulky stejnou mezipaměť.
Takže jo, lidi, takto můžete dynamicky měnit rozsah zdrojů dat 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 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.