Trenutno lahko vrtilne tabele dinamično spreminjamo ali posodabljamo z uporabo Excelovih tabel ali dinamičnih imenovanih obsegov. Toda te tehnike niso varne. Ker boste morali vrtilno tabelo še vedno osvežiti ročno. Če imate velike podatke, ki vsebujejo na tisoče vrstic in stolpcev, vam tabele Excel ne bodo v veliko pomoč. Namesto tega bo vaša datoteka otežena. Tako ostane le še VBA.
V tem članku se bomo naučili, kako lahko izvedemo, da naša vrtilna tabela samodejno spremeni vir podatkov. Z drugimi besedami, avtomatizirali bomo ročni postopek spreminjanja vira podatkov, da bo dinamično vključeval nove vrstice in stolpce, dodane v izvorne tabele, in v trenutku odražal spremembo vrtilne tabele.
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 vrtilno tabelo).
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 vrtilne tabele z novim obsegom
Za razlago, kako deluje, imam delovni zvezek. Ta delovni zvezek vsebuje dva lista. List1 vsebuje izvorne podatke, ki se lahko spremenijo. List2 vsebuje vrtilno tabelo, ki je odvisna od izvornih podatkov lista 2.
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.
Zasebni pod -delovni list_Deactivate () Dim pt Kot vrtilna tabela Dim pc Kot vrtilni predpomnilnik Dim source_data As Range lstrow = Celice (vrstice.Count, 1) .End (xlUp) .Row lstcol = Celice (1, stolpci.Count) .End (xlToLeft). Stolpec set source_data = Range (Celice (1, 1), Cells (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivot pc End Sub
Če imate podoben delovni zvezek, lahko te podatke neposredno kopirate. Spodaj sem pojasnil, da ta koda deluje.
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.
Zdaj za spremembo izvornih podatkov vrtilne tabele spremenimo podatke v vrtilnem predpomnilniku.
Z vrtilnim predpomnilnikom se ustvari vrtilna tabela. Vrtilni predpomnilnik vsebuje stare izvorne podatke, dokler se vrtilna tabela ne ročno osveži ali dokler se ročno ne spremeni obseg izvornih podatkov.
Ustvarili smo sklice na ime vrtilnih tabel pt, vrtilni predpomnilnik z imenom pc in obseg z imenom source_data. Izvorni podatki bodo vsebovali celotne podatke.
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.
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.
Te podatke shranjujemo v vrtilnem predpomnilniku, saj vemo, da v vrtilnem predpomnilniku so shranjeni vsi podatki.
Nastavi pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Nato določimo vrtilno tabelo, ki jo želimo posodobiti. Ker želimo posodobiti vrtilno tabelo1 (ime vrtilne tabele. Ime vrtilne tabele lahko preverite na zavihku za analizo, medtem ko izberete vrtilno tabelo.) Na listu 1, nastavimo pt, kot je prikazano spodaj.
Set pt = Sheet2.Vrtilne tabele ("Vrtilna tabela1")
Zdaj preprosto uporabimo ta vrtilni predpomnilnik za posodobitev vrtilne tabele. Uporabljamo metodo changePivotCache predmeta pt.
pt.ChangePivotCache pc
Svojo vrtilno tabelo imamo avtomatizirano. To bo samodejno posodobilo vašo vrtilno tabelo. Če imate več tabel z istim virom podatkov, uporabite isti predpomnilnik v vsakem objektu vrtilne tabele.
Torej, fantje, tako lahko dinamično spremenite obseg vira podatkov 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 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.