V súčasnosti môžeme kontingenčné tabuľky dynamicky meniť alebo aktualizovať pomocou tabuliek programu Excel alebo dynamicky pomenovaných rozsahov. Tieto techniky však nie sú spoľahlivé. Pretože stále budete musieť kontingenčnú tabuľku aktualizovať ručne. Ak máte veľké údaje, ktoré obsahujú tisíce riadkov a stĺpcov, excelové tabuľky vám veľmi nepomôžu. Namiesto toho bude váš súbor ťažký. Jedinou cestou teda zostáva VBA.
V tomto článku sa naučíme, ako môžeme v našej kontingenčnej tabuľke automaticky zmeniť zdroj údajov. Inými slovami, zautomatizujeme manuálny proces zmeny zdroja údajov tak, aby dynamicky zahŕňal nové riadky a stĺpce pridané do zdrojových tabuliek a okamžite odrážal zmenu v kontingenčnej tabuľke.
Napíšte kód do zdrojového listu
Pretože chceme, aby to bolo úplne automatické, namiesto jadrového modulu použijeme na zápis kódu hárkové moduly. To nám umožní používať udalosti pracovného hárka.
Ak sú zdrojové údaje a kontingenčné tabuľky na rôznych listoch, napíšeme kód VBA na zmenu zdroja údajov kontingenčnej tabuľky v objekte hárka, ktorý obsahuje zdrojové údaje (nie ten, ktorý obsahuje kontingenčnú tabuľku).
Stlačením CTRL+F11 otvorte editor VB. Teraz choďte do prieskumníka projektov a nájdite hárok, ktorý obsahuje zdrojové údaje. Dvakrát naň kliknite.
Otvorí sa nová oblasť kódovania. Možno neuvidíte žiadnu zmenu, ale teraz máte prístup k udalostiam pracovného hárka.
Kliknite na rozbaľovaciu ponuku vľavo a vyberte pracovný hárok. V ľavej rozbaľovacej ponuke vyberte deaktivovať. Uvidíte prázdny podradený text v názve oblasti kódu worksheet_deativate. Náš kód pre dynamicky sa meniace zdrojové údaje a osviežujúcu kontingenčnú tabuľku pôjde do tohto bloku kódu. Tento kód sa spustí vždy, keď prejdete z dátového listu na iný list. Tu si môžete prečítať o všetkých udalostiach pracovného hárka.
Teraz sme pripravení implementovať kód.
Zdrojový kód na dynamickú aktualizáciu kontingenčnej tabuľky s novým rozsahom
Aby som vysvetlil, ako to funguje, mám pracovný zošit. Tento zošit obsahuje dva listy. List 1 obsahuje zdrojové údaje, ktoré sa môžu zmeniť. List2 obsahuje kontingenčnú tabuľku, ktorá závisí od zdrojových údajov listu2.
Teraz som napísal tento kód do kódovacej oblasti sheet1. Používam udalosť Worksheet_Deactivate, aby sa tento kód spustil na aktualizáciu kontingenčnej tabuľky vždy, keď prejdeme zo zdrojového listu.
Súkromný čiastkový pracovný hárok_Deaktivovať () Dim pt ako kontingenčnú tabuľku Dim pc ako PivotCache Dim source_data Ako rozsah lstrow = bunky (riadky.počet, 1). Koniec (xlUp). Riadok lstcol = bunky (1, stĺpce. Počet). Koniec (xlToLeft). Sada stĺpcov source_data = rozsah (bunky (1, 1), bunky (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivotCache koniec PC
Ak máte podobný zošit, môžete tieto údaje priamo skopírovať. Vysvetlil som, že tento kód funguje nižšie.
Účinok tohto kódu môžete vidieť v gify nižšie.
Ako tento kód automaticky zmení zdrojové údaje a aktualizuje kontingenčné tabuľky?
Najprv sme použili udalosť worksheet_deactivate. Táto udalosť sa spustí iba vtedy, ak je list obsahujúci kód prepnutý alebo deaktivovaný. Takto sa kód automaticky spustí.
Teraz, aby sme zmenili zdrojové údaje kontingenčnej tabuľky, zmeníme údaje v kontingenčnej pamäti cache.
Kontingenčná tabuľka sa vytvára pomocou kontingenčnej pamäte cache. Kontingenčná pamäť cache obsahuje staré zdrojové údaje, kým sa kontingenčná tabuľka neaktualizuje ručne alebo sa manuálne nezmení rozsah zdrojových údajov.
Vytvorili sme referencie na názvy kontingenčných tabuliek pt, kontingenčnú vyrovnávaciu pamäť s názvom pc a rozsah s názvom source_data. Zdrojové údaje budú obsahovať všetky údaje.
Aby sme dynamicky získali celú tabuľku ako rozsah údajov, určíme posledný riadok a posledný stĺpec.
lstrow = Bunky (riadky. počet, 1). Koniec (xlUp). Riadok
lstcol = bunky (1, stĺpce. počet). Koniec (xlToLeft). stĺpec
Pomocou týchto dvoch čísel definujeme zdrojové_údaje. Sme si istí, že rozsah zdrojových údajov bude vždy začínať od A1.
Nastaviť source_data = rozsah (bunky (1, 1), bunky (lstrow, lstcol))
Teraz máme zdrojové údaje, ktoré sú dynamické. Potrebujeme ho použiť iba v kontingenčnej tabuľke.
Tieto údaje ukladáme do pivot cache, pretože vieme, že pivot cache ukladá všetky dáta.
Nastaviť pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Ďalej definujeme kontingenčnú tabuľku, ktorú chceme aktualizovať. Pretože chceme aktualizovať kontingenčnú tabuľku1 (názov kontingenčnej tabuľky. Názov kontingenčnej tabuľky môžete skontrolovať na karte analýzy pri výbere kontingenčnej tabuľky.) Na hárku1, nastavíme pt podľa nasledujúceho obrázku.
Nastaviť pt = List2.Kontingenčné tabuľky ("Kontingenčná tabuľka1")
Teraz túto kontingenčnú vyrovnávaciu pamäť jednoducho používame na aktualizáciu kontingenčnej tabuľky. Používame metódu changePivotCache objektu pt.
pt.ChangePivotCache pc
A máme kontingenčnú tabuľku automatizovanú. Kontingenčná tabuľka sa tým automaticky aktualizuje. Ak máte viacero tabuliek s rovnakým zdrojom údajov, použite v každom objekte kontingenčnej tabuľky rovnakú vyrovnávaciu pamäť.
Takže áno, chlapci, takto môžete dynamicky meniť rozsah zdrojov údajov v programe Excel. Dúfam, že som bol dostatočne vysvetľujúci. Ak máte akékoľvek otázky týkajúce sa tohto článku, dajte mi vedieť v sekcii komentárov nižšie.
Ako automaticky aktualizovať kontingenčné tabuľky pomocou VBA: Na automatickú obnovu vašich kontingenčných tabuliek môžete použiť udalosti VBA. Tento jednoduchý riadok kódu použite na automatickú aktualizáciu kontingenčnej tabuľky. Môžete použiť jednu z troch metód automatického obnovovania kontingenčných tabuliek.
V prípade zmeny vykonanej na hárku v určenom rozsahu spustite makro: Vo vašich postupoch VBA by ste potrebovali spustiť makrá, keď sa zmení určitý rozsah alebo bunka. V takom prípade na spustenie makier pri zmene cieľového rozsahu použijeme udalosť zmeny.
Spustite makro, keď sa na hárku vykonajú akékoľvek zmeny | Na spustenie makra vždy, keď sa aktualizuje hárok, preto používame udalosti pracovného hárka VBA.
Najjednoduchší kód VBA na zvýraznenie aktuálneho použitia riadkov a stĺpcov | Pomocou tohto malého útržku VBA zvýraznite aktuálny riadok a stĺpec listu.
Udalosti pracovného hárka v programe Excel VBA | Udalosť pracovného hárka je skutočne užitočná, keď chcete, aby sa vaše makrá spustili, keď sa v hárku vyskytne zadaná udalosť.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.Funkcia VLOOKUP v programe Excel | Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
COUNTIF v Exceli 2016 | Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnej hodnoty nepotrebujete filtrovať svoje údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.