Excel VBA Oppdater pivottabell
Når vi setter inn en pivottabell i arket, når dataene endrer pivottabelldataene ikke endrer seg, trenger vi å gjøre det manuelt, men i VBA er det en uttalelse for å oppdatere pivottabellen som er uttrykk. Forfriskbar . Ved å bruke dette kan vi oppdatere pivottabell ved å referere til regnearket som består av det, eller vi kan referere til hele pivottabellene i regnearkene og oppdatere dem alle samtidig.
Pivottabellen er viktig for å analysere den enorme mengden data. Det hjelper rett fra å analysere, oppsummere, samt gjøre nyttig datatolkning. Et av problemene med denne pivottabellen er imidlertid at den ikke automatisk blir oppdatert hvis det er noen endring i kildedataene. Brukeren må oppdatere pivottabellen ved å gå til den aktuelle pivottabellen hver gang det er en endring. Men si farvel til den manuelle prosessen, for her har vi metoden for å oppdatere pivottabellen så snart du gjør noen endringer i pivottabellen.
Hvordan oppdaterer du VBA-koden for pivottabelldata automatisk?
Den eneste gangen pivottabellen må oppdateres, er når det er noen endring i kildedataene til pivottabellen vi refererer til.
Se for eksempel på dataene nedenfor og pivottabellen.
Nå vil jeg endre tallene i kildedataene, dvs. fra A1 til B17.
I celle B9 må jeg endre verdien fra 499 til 1499, dvs. 1000 økning i data, men hvis du ser på pivoten fremdeles viser resultatet som 4295 i stedet for 5295. Jeg må oppdatere pivottabellen min manuelt for å oppdatere dreiebord.
For å løse dette problemet, må vi skrive en enkel Excel-makrokode for å oppdatere pivottabellen når det er noen endring i kildedataene.
# 1 - Enkel makro for å oppdatere hele tabellen
Trinn 1: Endre hendelsen i databladet
Vi må utløse endringshendelsen til databladet. Dobbeltklikk på databladet i Visual Basic Editor.
Når du dobbeltklikker på arket, velger du "Regneark" og velger hendelsen som "Endre."
Du vil se en automatisk underprosedyre åpnet som Worksheet_Change (ByVal Target As Range)
Trinn 2: Bruk regnearkobjekt
Se databladet ved å bruke regnearkobjektet.
Trinn 3: Se pivottabell etter navn
Henvis til pivottabellnavnet med navnet på pivottabellen.
Trinn 4: Bruk Oppdater tabellmetoden
Velg metoden som "Oppdater tabell."
Nå vil denne koden oppdatere pivottabellen "PivotTable1" når det er noen endring i kildedataarket. Du kan bruke koden nedenfor. Du må bare endre pivottabellnavnet.
Kode:
Privat underarbeidsark_endring (ByVal-mål som rekkevidde) Regneark ("Dataark"). Pivottabeller ("Pivottabell1"). Oppdater tabell
# 2 - Oppdater alle pivottabeller i samme regneark
Hvis du har mange pivottabeller i samme regneark, kan du oppdatere alle pivottabellene med et enkelt klikk. Bruk koden nedenfor for å oppdatere alle pivottabellene i arket.
Kode:
Sub Refresh_Pivot_Tables_Example1 () regneark ("Data Sheet"). Velg med ActiveSheet. PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable. RefreshTable. PivotTables ("Table5"). RefreshTable End With End Sub
Du må endre navnet på regneark- og pivottabellnavnene i henhold til regnearkdetaljene dine.
# 3 - Oppdater alle tabeller i arbeidsboken
Det er svært lite sannsynlig at vi har alle pivottabellene på samme regneark. Vanligvis prøver vi for hver rapport å legge til separate pivottabeller i separate ark. I disse tilfellene kan vi ikke fortsette å skrive koden for hver pivottabell som skal oppdateres.
Så det vi kan gjøre er med en enkelt kode ved hjelp av sløyfer, vi kan gå gjennom alle pivottabellene på arbeidsboken og oppdatere dem med et enkelt klikk på knappen.
Koden nedenfor vil gå gjennom hver pivottabell og oppdatere dem.
Kode 1:
Sub Refresh_Pivot_Tables_Example2 () Dim PT som pivottabell for hver PT i ActiveWorkbook.PivotTables PT.RefreshTable Neste PT End Sub
Kode 2:
Sub Refresh_Pivot_Tables_Example3 () Dim PC som PivotCache for hver PC i ActiveWorkbook.PivotCaches PC.Refresh Neste PT End Sub
Begge kodene vil forfriskes av pivottabeller.
Hvis du vil at pivottabellen skal oppdateres så snart det er noen endring i databladet til pivotearket, må du kopiere og lime inn ovennevnte koder til arbeidsarkendringshendelsen i den arbeidsboken.
# 4 - Unngå lastetid ved å bruke arbeidsark Deaktiver hendelse
Når vi bruker "Arbeidsarkendring" -hendelsen, oppdateres den kontinuerlig selv om det ikke er noen endring i datakilden, men hvis det skjer noen endring i regnearket.
Selv om du skriver inn en enkelt prikk i regnearket, prøver den å oppdatere pivottabellen. Så for å unngå dette, kan vi bruke metoden "Regneark deaktiver" i stedet for "Endring av regneark".
Deaktiver hendelsesoppdateringene på pivottabellen når du flytter fra ett ark til et annet ark.
