Excel VBA opdater pivottabel
Når vi indsætter en pivottabel i arket, når dataene ændrer pivottabeldataene ikke ændrer sig selv, er vi nødt til at gøre det manuelt, men i VBA er der en erklæring om at opdatere pivottabellen, som er udtryk. Opdaterbar , ved hjælp af dette kan vi opdatere pivottabel ved at henvise til regnearket, der består af det, eller vi kan henvise til hele pivottabellerne i regnearkene og opdatere dem alle på én gang.
Pivottabellen er afgørende for at analysere den enorme mængde data. Det hjælper lige fra at analysere, opsummere samt foretage nyttig datatolkning. Et af problemerne med denne pivottabel er imidlertid, at den ikke automatisk opdateres, hvis der er ændringer i kildedataene. Brugeren skal opdatere pivottabellen ved at gå til den bestemte pivottabel hver gang der er en ændring. Men farvel til den manuelle proces, for her har vi metoden til at opdatere pivottabellen, så snart du foretager ændringer i pivottabellen.
Sådan opdateres automatisk VBA-kode for pivottabeldata?
Den eneste gang pivottabellen skal opdateres, er når der er ændringer i kildedataene for pivottabellen, som vi henviser til.
Se for eksempel nedenstående data og pivottabel.
Nu vil jeg ændre numrene i kildedata, dvs. fra A1 til B17.
I celle B9 er jeg nødt til at ændre værdien fra 499 til 1499, dvs. 1000 stigning i dataene, men hvis du ser på drejetasten, vises resultatet stadig som 4295 i stedet for 5295. Jeg skal manuelt opdatere min drejetabel for at opdatere drejebord.
For at løse dette problem er vi nødt til at skrive en simpel excel-makro-kode for at opdatere pivottabellen, når der er ændringer i kildedataene.
# 1 - Enkel makro til at opdatere hele tabellen
Trin 1: Skift begivenhed i databladet
Vi er nødt til at udløse ændringshændelsen i databladet. Dobbeltklik på databladet i den visuelle grundeditor.
Når du dobbeltklikker på arket, skal du vælge "Arbejdsark" og vælge begivenheden som "Skift".
Du vil se en automatisk underprocedure åbnet som Worksheet_Change (ByVal Target As Range)
Trin 2: Brug regnearkobjekt
Se databladet ved hjælp af regnearkobjektet.
Trin 3: Se pivottabel efter navn
Se navnet på pivottabellen med navnet på pivottabellen.
Trin 4: Brug metoden Opdater tabel
Vælg metoden som "Opdater tabel."
Nu opdaterer denne kode pivottabellen "PivotTable1", når der er nogen ændringer i kildedataarket. Du kan bruge nedenstående kode. Du skal bare ændre pivottabelnavnet.
Kode:
Privat under regneark_ændring (ByVal-mål som rækkevidde) Arbejdsark ("Dataark"). Pivottabeller ("Pivottabel1"). Opdateringstabel Slutunder
# 2 - Opdater alle pivottabeller i samme regneark
Hvis du har mange pivottabeller i det samme regneark, kan du opdatere alle pivottabellerne med et enkelt klik. Brug nedenstående kode til at opdatere alle pivottabellerne på arket.
Kode:
Under Refresh_Pivot_Tables_Example1 () Arbejdsark ("Dataark"). Vælg med ActiveSheet. Pivottabeller ("Tabel1"). Opdateringstabel. Pivottabeller ("Tabel2"). Opdateretabel. RefreshTable. PivotTables ("Table5"). RefreshTable End With End Sub
Du er nødt til at ændre navnet på regnearket og pivottabelnavne i henhold til dine regnearkoplysninger.
# 3 - Opdater alle tabeller i projektmappen
Det er meget usandsynligt, at vi har alle drejetabellerne på det samme regneark. Normalt forsøger vi for hver rapport at tilføje separate pivottabeller i separate ark. I disse tilfælde kan vi ikke fortsætte med at skrive koden for hver pivottabel, der skal opdateres.
Så hvad vi kan gøre er med en enkelt kode ved hjælp af sløjfer, vi kan løbe gennem alle pivottabellerne på projektmappen og opdatere dem med et enkelt klik på knappen.
Nedenstående kode løber gennem hver drejetabel og opdaterer dem.
Kode 1:
Sub Refresh_Pivot_Tables_Example2 () Dim PT som pivottabel for hver PT i ActiveWorkbook.PivotTables PT.RefreshTable Næste PT End Sub
Kode 2:
Sub Refresh_Pivot_Tables_Example3 () Dim PC som PivotCache for hver pc i ActiveWorkbook.PivotCaches PC.Refresh Næste PT End Sub
Begge koder opdaterer pivottabellerne.
Hvis du vil have pivottabellen opdateret, så snart der er ændringer i databladet for drejearket, skal du kopiere og indsætte ovenstående koder i projektarkændringsbegivenheden i den projektmappe.
# 4 - Undgå indlæsningstid ved hjælp af regneark Deaktiver begivenhed
Når vi bruger begivenheden "Skift af regneark", opdateres den, selv når der ikke er nogen ændring i datakilden, men hvis der sker ændringer i regnearket.
Selvom du indtaster en enkelt prik i regnearket, forsøger den at opdatere drejetabellen. Så for at undgå dette kan vi bruge metoden "Arbejdsark Deaktiver" i stedet for "Ændring af regneark".
Deaktiver hændelsesopdateringerne på pivottabellen, når du flytter fra et ark til et andet ark.
