For øyeblikket kan vi dynamisk endre eller oppdatere pivottabeller ved hjelp av Excel -tabeller eller dynamiske navngitte områder. Men disse teknikkene er ikke idiotsikre. Siden du fortsatt må oppdatere pivottabellen manuelt. Hvis du har store data som inneholder tusenvis av rader og kolonner, vil Excel -tabeller ikke hjelpe deg mye. I stedet vil det gjøre filen tung. Så den eneste måten gjenstår er VBA.
I denne artikkelen vil vi lære hvordan vi kan få pivottabellen til å endre datakilden automatisk. Med andre ord, vi vil automatisere den manuelle prosessen med å endre datakilde for dynamisk å inkludere nye rader og kolonner som er lagt til i kildetabeller og gjenspeile endringen i pivottabellen umiddelbart.
Skriv kode i kildedatablad
Siden vi vil at dette skal være helt automatisk, vil vi bruke arkmoduler til å skrive kode i stedet for en kjernemodul. Dette vil tillate oss å bruke regnearkhendelser.
Hvis kildedataene og pivottabellene er i forskjellige ark, skriver vi VBA -koden for å endre pivottabellens datakilde i arkobjektet som inneholder kildedata (ikke som inneholder pivottabell).
Trykk CTRL+F11 for å åpne VB -editoren. Gå nå til project explorer og finn arket som inneholder kildedata. Dobbeltklikk på den.
Et nytt kodeområde åpnes. Du kan ikke se noen endring, men nå har du tilgang til regnearkhendelser.
Klikk på rullegardinmenyen til venstre og velg regnearket. Velg deaktiver fra venstre nedtrekksmeny. Du vil se en tom sub skrevet på koden område navnet worksheet_deativate. Koden vår for dynamisk endring av kildedata og forfriskende pivottabell kommer i denne kodeblokken. Denne koden kjøres hver gang du bytter fra databladet til et annet ark. Du kan lese om alle regnearkhendelser her.
Nå er vi klare til å implementere koden.
Kildekode for oppdatering av pivottabell dynamisk med nytt område
For å forklare hvordan det fungerer, har jeg en arbeidsbok. Denne arbeidsboken inneholder to ark. Ark1 inneholder kildedataene som kan endres. Sheet2 inneholder pivottabellen som avhenger av kildedataene til sheet2.
Nå har jeg skrevet denne koden i arkets kodingsområde. Jeg bruker hendelsen Worksheet_Deactivate, slik at denne koden kjører for å oppdatere pivottabellen hver gang vi bytter fra kildedatablad.
Private Sub Worksheet_Deactivate () Dim pt As PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft). Column Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivotCache pc End Sub
Hvis du har en lignende arbeidsbok, kan du kopiere disse dataene direkte. Jeg har forklart at denne koden fungerer nedenfor.
Du kan se effekten av denne koden i gif nedenfor.
Hvordan endrer denne koden automatisk kildedata og oppdaterer pivottabeller?
Først av alt brukte vi en regneark_deaktiver hendelse. Denne hendelsen utløses bare når arket som inneholder koden er byttet eller deaktivert. Så dette er hvordan koden automatisk kjøres.
For å endre kildedataene i pivottabellen endrer vi data i pivotbufferen.
En pivottabell opprettes ved hjelp av pivotbuffer. Pivotbufferen inneholder de gamle kildedataene til pivottabellen ikke oppdateres manuelt eller kildedataområdet endres manuelt.
Vi har opprettet referanser til pivottabeller navn pt, pivotbuffer kalt pc og et område kalt source_data. Kildedataene vil inneholde hele dataene.
For å dynamisk få hele tabellen som dataområde, bestemmer vi den siste raden og den siste kolonnen.
lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row
lstcol = Celler (1, Columns.Count) .End (xlToLeft) .Column
Ved å bruke disse to tallene definerer vi source_data. Vi er sikre på at kildedataområdet alltid vil starte fra A1.
Angi source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))
Nå har vi kildedataene som er dynamiske. Vi trenger bare å bruke den i pivottabellen.
Vi lagrer disse dataene i pivot -cache, slik vi vet at pivot -cache lagrer alle dataene.
Sett pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Deretter definerer vi pivottabellen som vi vil oppdatere. Siden vi ønsker å oppdatere pivottabellen1 (navnet på pivottabellen. Du kan sjekke navnet på pivottabellen i analysefanen mens du velger pivottabellen.) På ark1, setter vi pt som vist nedenfor.
Sett pt = Sheet2.PivotTables ("PivotTable1")
Nå bruker vi bare denne pivotbufferen til å oppdatere pivottabellen. Vi bruker changePivotCache -metoden for pt -objekt.
pt.ChangePivotCache pc
Og vi har vårt pivottabell automatisert. Dette oppdaterer pivottabellen din automatisk. Hvis du har flere tabeller med samme datakilde, bruker du bare den samme hurtigbufferen i hvert pivottabellobjekt.
Så ja gutta, slik kan du dynamisk endre datakildeområdet i Excel. Jeg håper jeg var forklarende nok. Hvis du har spørsmål angående denne artikkelen, gi meg beskjed i kommentarfeltet nedenfor.
Slik oppdaterer du pivottabeller automatisk ved hjelp av VBA: For å automatisk oppdatere pivottabellene kan du bruke VBA -hendelser. Bruk denne enkle kodelinjen til å oppdatere pivottabellen automatisk. Du kan bruke en av tre metoder for automatisk oppdatering av pivottabeller.
Kjør makro hvis det er gjort endringer på ark i spesifisert område: I din VBA -praksis vil du få behov for å kjøre makroer når et bestemt område eller celle endres. I så fall bruker vi endringshendelsen for å kjøre makroer når det gjøres en endring i et målområde.
Kjør makro når det gjøres noen endringer på arket | Så for å kjøre makroen din når arket oppdateres, bruker vi regnearkhendelser i VBA.
Enkleste VBA -kode for å markere gjeldende rad og kolonne ved hjelp | Bruk denne lille VBA -biten til å markere gjeldende rad og kolonne i arket.
Regnearket Hendelser i Excel VBA | Regnearkhendelsen er veldig nyttig når du vil at makroene dine skal kjøres når en spesifisert hendelse oppstår på arket.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.VLOOKUP -funksjonen i Excel | Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
COUNTIF i Excel 2016 | Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke filtrere dataene dine for å telle spesifikk verdi. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.