V prejšnjem članku smo izvedeli, kako lahko dinamično spreminjate in posodabljate posamezne vrtilne tabele s krčenjem ali širjenjem podatkovnih virov.
V tem članku se bomo naučili, kako lahko vse vrtilne tabele v delovnem zvezku samodejno spremenijo vir podatkov. Z drugimi besedami, namesto da bi spreminjali eno vrtilno tabelo naenkrat, bomo poskušali spremeniti vir podatkov vseh vrtilnih tabel v delovnem zvezku tako, da bodo dinamično vključevale nove vrstice in stolpce, dodane v izvorne tabele, in takoj odražale spremembe v vrtilnih tabelah.
Kodo vnesite v izvorni podatkovni list
Ker želimo, da je to popolnoma samodejno, bomo namesto jedrnega modula za pisanje kode uporabili module listov. To nam bo omogočilo uporabo dogodkov na delovnem listu.
Če so izvorni podatki in vrtilne tabele na različnih listih, bomo zapisali kodo VBA, da spremenimo vir podatkov vrtilne tabele v objektu lista, ki vsebuje izvorne podatke (ne tisti, ki vsebuje vrtilne tabele).
Pritisnite CTRL+F11, da odprete urejevalnik VB. Zdaj pojdite na raziskovalca projektov in poiščite list, ki vsebuje izvorne podatke. Dvokliknite nanjo.
Odpre se novo območje kodiranja. Morda ne boste videli nobenih sprememb, zdaj pa imate dostop do dogodkov na delovnem listu.
Kliknite spustni meni na levi in izberite delovni list. V spustnem meniju na levi izberite deaktiviraj. Na delovnem listu imena kode bo prikazan prazen podpis. Naša koda za dinamično spreminjanje izvornih podatkov in osveževanje vrtilne tabele bo vključena v ta blok kode. Ta koda se bo izvajala vsakič, ko preklopite s podatkovnega lista na kateri koli drug list. Vse dogodke na delovnem listu lahko preberete tukaj.
Zdaj smo pripravljeni na izvajanje kode.
Izvorna koda za dinamično posodabljanje vseh vrtilnih tabel v delovnem zvezku z novim obsegom
Za razlago, kako deluje, imam delovni zvezek. Ta delovni zvezek vsebuje tri liste. List1 vsebuje izvorne podatke, ki se lahko spremenijo. List2 in Sheet3 vsebujeta vrtilne tabele, ki so odvisne od izvornih podatkov lista2.
Zdaj sem to kodo zapisal na kodirnem področju sheet1. Uporabljam dogodek Worksheet_Deactivate, tako da se ta koda zažene za posodobitev vrtilne tabele, kadar koli preidemo iz izvornega podatkovnega lista.
Private Sub Worksheet_Deactivate () Dim source_data As Range 'Določanje zadnje vrstice in števila stolpcev lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column "Nastavitev novega obsega Nastavi source_data = Range (Celice (1, 1), Cells (lstrow, lstcol))" Kodirajte po vsakem listu in vrtilni tabeli Za vsak ws V tem delovnem zvezku. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Naprej pt Naprej ws Konec pod
Če imate podoben delovni zvezek, lahko te podatke neposredno kopirate. Spodaj sem pojasnil, da ta koda deluje, tako da jo lahko spremenite glede na svoje potrebe.
Učinek te kode si lahko ogledate v gifu spodaj.
Kako ta koda samodejno spremeni izvorne podatke in posodobi vrtilne tabele?
Najprej smo uporabili dogodek worksheet_deactivate. Ta dogodek se sproži le, če je list s kodo preklopljen ali deaktiviran. Tako se koda samodejno zažene.
Za dinamično pridobivanje celotne tabele kot obsega podatkov določimo zadnjo vrstico in zadnji stolpec.
lstrow = Celice (vrstice. število, 1). konec (xlUp). vrstica
lstcol = celice (1, stolpci. število). konec (xlToLeft). stolpec
S pomočjo teh dveh številk določimo vir_data. Zagotovo smo, da se bo obseg izvornih podatkov vedno začel od A1. Določite lahko svojo referenco začetne celice.
Set source_data = Range (Celice (1, 1), Cells (lstrow, lstcol))
Zdaj imamo izvorne podatke, ki so dinamični. Uporabiti ga moramo le v vrtilni tabeli.
Ker ne vemo, koliko vrtilnih tabel bo hkrati vseboval delovni zvezek, bomo prelistali vsak list in vrtilne tabele vsakega lista. Tako, da ne ostane nobena vrtilna tabela. Za to uporabljamo ugnezdene zanke.
Za vsako ws v ThisWorkbook.Worksheets
Za vsako točko V ws.Vrtilne tabele
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
Vrsta vira: = xlDatabase, _
VirData: = vir_data)
Naslednja točka
Naslednji ws
Prva zanka se poda skozi vsak list. Druga zanka se ponavlja po vsaki vrtilni tabeli na listu.
Vrtilne tabele so dodeljene spremenljivki pt. Uporabljamo metodo ChangePivotCache predmeta pt. S pomočjo ThisWorkbook.PivotCaches.Create ustvarimo dinamično vrtilni predpomnilnik
Metoda. Ta metoda uporablja dve spremenljivki SourceType in SourceData. Kot vir vira razglasimo xlDatabase, kot SourceData pa posredujemo obseg source_data, ki smo ga izračunali prej.
In to je to. Naše vrtilne tabele imamo avtomatizirane. S tem se samodejno posodobijo vse vrtilne tabele v delovnem zvezku.
Torej, fantje, tako lahko dinamično spremenite obsege virov podatkov za vse vrtilne tabele v delovnem zvezku v Excelu. Upam, da sem bil dovolj razložljiv. Če imate kakršna koli vprašanja v zvezi s tem člankom, mi to sporočite v spodnjem razdelku za komentarje.
Kako dinamično posodobiti obseg vira podatkov vrtilne tabele v Excelu: Za dinamično spreminjanje obsega izvornih podatkov vrtilnih tabel uporabljamo vrtilne predpomnilnike. Teh nekaj vrstic lahko dinamično posodobi katero koli vrtilno tabelo s spreminjanjem območja izvornih podatkov.
Kako samodejno osvežiti vrtilne tabele z uporabo VBA: Za samodejno osvežitev vrtilnih tabel lahko uporabite dogodke VBA. S to preprosto vrstico kode samodejno posodobite vrtilno tabelo. Uporabite lahko enega od treh načinov samodejnega osveževanja vrtilnih tabel.
Zaženite makro, če je na listu v določenem obsegu prišlo do kakršne koli spremembe: V praksah VBA boste morali zagnati makre, ko se določeno območje ali celica spremeni. V tem primeru za izvajanje makrov pri spremembi ciljnega območja uporabimo dogodek spremembe.
Zaženi makro, če na listu pride do kakršne koli spremembe | Zato za zagon makra vsakič, ko se list posodobi, uporabljamo dogodke delovnega lista VBA.
Najpreprostejša koda VBA za označevanje trenutne vrstice in stolpca | S tem majhnim delčkom VBA označite trenutno vrstico in stolpec lista.
Dogodki na delovnem listu v Excelu VBA | Dogodek delovnega lista je resnično uporaben, če želite, da se makri zaženejo, ko se na listu pojavi določen dogodek.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.Funkcija VLOOKUP v Excelu | To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
COUNTIF v Excelu 2016 | Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določene vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.