V predchádzajúcom článku sme sa dozvedeli, ako môžete dynamicky meniť a aktualizovať jednotlivé kontingenčné tabuľky pomocou zmenšovania alebo rozširovania zdrojov údajov.
V tomto článku sa dozvieme, ako môžeme dosiahnuť, aby všetky kontingenčné tabuľky v zošite automaticky menili zdroj údajov. Inými slovami, namiesto zmeny jednej kontingenčnej tabuľky naraz sa pokúsime zmeniť zdroj údajov všetkých kontingenčných tabuliek v zošite tak, aby dynamicky zahŕňali nové riadky a stĺpce pridané do zdrojových tabuliek a okamžite odrážali zmenu v kontingenčných tabuľkách.
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 hárkoch, 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ľky).
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 všetkých kontingenčných tabuliek v zošite s novým rozsahom
Aby som vysvetlil, ako to funguje, mám pracovný zošit. Tento zošit obsahuje tri listy. List 1 obsahuje zdrojové údaje, ktoré sa môžu zmeniť. Hárok 2 a list 3 obsahujú kontingenčné tabuľky, ktoré závisia 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 source_data ako rozsah „Určenie posledného riadka a čísla stĺpca lstrow = bunky (riadky. Počet, 1). Koniec (xlUp). Riadok lstcol = bunky (1, stĺpce. Počet). Koniec (xlToLeft). Stĺpec „Nastavenie nového rozsahu Nastaviť zdrojové_data = Rozsah (Bunky (1, 1), Bunky (lstrow, lstcol))“ Kód, ktorý sa bude opakovať v každom hárku a kontingenčnej tabuľke pre každé ws v ThisWorkbook. Pracovné listy pre každý pt V ws.PivotTables pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Ďalší pt Ďalší ws Koniec Sub
Ak máte podobný zošit, môžete tieto údaje priamo skopírovať. Vysvetlil som, že tento kód funguje nižšie, aby ste ho mohli upraviť podľa svojich potrieb.
Úč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í.
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. Môžete definovať vlastnú referenciu na počiatočnú bunku.
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.
Pretože nevieme, koľko kontingenčných tabuliek bude zošit obsahovať naraz, previnieme sa cez každý hárok a kontingenčné tabuľky každého hárka. Aby nezostala žiadna kontingenčná tabuľka. Na to používame vnorené pre slučky.
Za každé ws v ThisWorkbook.Worksheets
Pre každý bod v. Kontingenčné tabuľky
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
Typ zdroja: = xlDatabase, _
SourceData: = source_data)
Ďalší bod
Ďalej ws
Prvá slučka prechádza cez každý list. Druhá slučka sa opakuje nad každou kontingenčnou tabuľkou v hárku.
Kontingenčné tabuľky sú priradené k premennej pt. Používame metódu ChangePivotCache pt objektu. Kontingenčnú vyrovnávaciu pamäť dynamicky vytvárame pomocou ThisWorkbook.PivotCaches.Create
Metóda. Táto metóda používa dve premenné SourceType a SourceData. Ako typ zdroja deklarujeme xlDatabase a ako SourceData odovzdávame rozsah source_data, ktorý sme vypočítali skôr.
A to je všetko. Naše kontingenčné tabuľky máme zautomatizované. Tým sa automaticky aktualizujú všetky kontingenčné tabuľky v zošite.
Takže áno, chlapci, takto môžete dynamicky meniť rozsahy zdrojov údajov všetkých kontingenčných tabuliek v zošite 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 dynamicky aktualizovať rozsah zdrojov údajov v kontingenčnej tabuľke v programe Excel: Na dynamickú zmenu rozsahu zdrojových údajov kontingenčných tabuliek používame kontingenčné pamäte cache. Týchto niekoľko riadkov môže dynamicky aktualizovať akúkoľvek kontingenčnú tabuľku zmenou rozsahu zdrojových údajov.
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.