I en tidligere artikkel lærte vi hvordan du dynamisk kan endre og oppdatere individuelle pivottabeller med krympende eller utvidende datakilder.
I denne artikkelen lærer vi hvordan vi kan gjøre at alle pivottabeller i en arbeidsbok automatisk endrer datakilden. Med andre ord, i stedet for å endre en pivottabell om gangen, vil vi prøve å endre datakilden til alle pivottabellene i arbeidsboken for dynamisk å inkludere nye rader og kolonner som er lagt til i kildetabeller og gjenspeile endringen i pivottabeller 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 kildedata og pivottabeller er i forskjellige ark, skriver vi VBA -koden for å endre pivottabellens datakilde i arkobjektet som inneholder kildedataene (ikke som inneholder pivottabeller).
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 dynamisk oppdatering av alle pivottabeller i arbeidsbok med nytt område
For å forklare hvordan det fungerer, har jeg en arbeidsbok. Denne arbeidsboken inneholder tre ark. Ark1 inneholder kildedataene som kan endres. Sheet2 og Sheet3 inneholder pivottabeller som er avhengig 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 source_data As Range 'Bestemme siste rad og kolonnenummer lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column 'Angi det nye området Angi source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))' Kode for å gå gjennom hvert ark og pivottabell For hver ws i ThisWorkbook.Worksheets For Every pt In ws.PivotTables pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Neste pt Neste ws End Sub
Hvis du har en lignende arbeidsbok, kan du kopiere disse dataene direkte. Jeg har forklart at denne koden fungerer nedenfor, slik at du kan endre den etter dine behov.
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 å 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. Du kan definere din egen begynnelsescellehenvisning.
Angi source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))
Nå har vi kildedataene som er dynamiske. Vi trenger bare å bruke den i pivottabellen.
Siden vi ikke vet hvor mange pivottabeller en arbeidsbok vil inneholde om gangen, går vi gjennom hvert ark og pivottabeller i hvert ark. Slik at ingen pivottabell er igjen. Til dette bruker vi nestet for løkker.
For hver gang i ThisWorkbook.Worksheets
For hver pt In ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
SourceType: = xlDatabase, _
SourceData: = source_data)
Neste pt
Neste ws
Den første sløyfen går gjennom hvert ark. Den andre løkken gjentar seg over hvert pivottabell i et ark.
Pivottabellene er tilordnet variabel pt. Vi bruker ChangePivotCache -metoden for pt -objekt. Vi oppretter dynamisk en pivotbuffer ved hjelp av ThisWorkbook.PivotCaches.Create
Metode. Denne metoden tar to variabler SourceType og SourceData. Som kildetype deklarerer vi xlDatabase og som SourceData passerer vi source_data -området som vi har beregnet tidligere.
Og det er det. Vi har våre pivottabeller automatisert. Dette oppdaterer automatisk alle pivottabellene i arbeidsboken.
Så ja gutta, slik kan du dynamisk endre datakildeområder for alle pivottabellene i en arbeidsbok 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.
Hvordan du dynamisk oppdaterer pivottabellens datakildeområde i Excel: For å dynamisk endre kildedataområdet for pivottabeller bruker vi pivot -cacher. Disse få linjene kan dynamisk oppdatere hvilken som helst pivottabell ved å endre kildedataområdet.
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.
