Slik oppdaterer du dynamisk alle pivottabeller datakilde i Excel
I en tidligere artikkel lærte vi hvordan du dynamisk kan endre og oppdatere individuelle pivottabeller med krympende eller utvidende datakilder.
I denne artikkelen vil vi lære 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 for alle pivottabellene i arbeidsboken for dynamisk å inkludere nye rader og kolonner lagt til kildetabellene og gjenspeile endringen i pivottabeller umiddelbart. / p>
Skriv kode i kildedataarket
Siden vi ønsker 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, vil vi skrive 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 prosjektutforsker og finn arket som inneholder kildedataene. Dobbeltklikk på den.
Et nytt kodeområde åpnes. Du ser kanskje ingen endringer, men nå har du tilgang til regnearkhendelsene.
Klikk på rullegardinmenyen til venstre og velg regnearket. Velg deaktiver fra rullegardinmenyen til venstre. Du vil se en tom sub skrevet på koden områdens navn worksheet_deativate. Koden vår for dynamisk endring av kildedata og forfriskende pivottabell vil gå i denne kodeblokken. Denne koden vil kjøre når du bytter fra databladet til et annet ark. Du kan lese om alle regnearkhendelsene 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 kodingsområdet for sheet1. Jeg bruker hendelsen Worksheet_Deactivate, slik at denne koden kjører for å oppdatere pivottabell når vi bytter fra kildedataark.
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 i henhold til dine behov.
Du kan se effekten av denne koden i gif nedenfor.
Hvordan endres denne koden automatisk kildedata og oppdater pivottabeller?
Først og fremst brukte vi et regneark_deaktiver hendelse. Denne hendelsen utløses bare når arket som inneholder koden er slått eller deaktivert. Så dette kjører koden automatisk.
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 (xlToL eft) .Kolonne
Ved å bruke disse to tallene definerer vi kildedataene. Vi er sikre på at kildedataområdet alltid vil starte fra A1. Du kan definere din egen startcellereferanse.
Angi kildedata = Område (celler (1, 1), celler (lstrow, lstcol))
Nå har vi kildedataene som er dynamisk. Vi trenger bare å bruke den i pivottabellen.
Siden vi ikke vet hvor mange pivottabeller en arbeidsbok vil inneholde om gangen, vil vi gå gjennom hvert ark og pivottabeller på hvert ark. Så at det ikke er igjen noen pivottabell. Til dette bruker vi nestet for løkker.
For hver ws I ThisWorkbook.Worksheets
For hver pt I ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
SourceType: = xlDatabase, _
SourceData: = source_data)
Neste pt
Neste ws
Den første sløyfen sløyfer gjennom hvert ark. Den andre sløyfen går over hver pivottabell i et ark.
Pivottabellene er tildelt til variabel pt. Vi bruker ChangePivotCache-metoden for pt-objekt. Vi lager dynamisk en pivot-cache ved hjelp av ThisWorkbook.PivotCaches.Create
Metoden. Denne metoden tar to variabler SourceType og SourceData. Som kildetype erklærer vi xlDatabase og som SourceData passerer vi kildedataområdet som vi har beregnet tidligere.
Og at jeg s det. Vi har automatiserte pivottabeller. Dette vil automatisk oppdatere alle pivottabeller i arbeidsboken.
Så ja folkens, dette er hvordan du dynamisk kan endre datakildene til 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.
Slik oppdaterer du dynamisk pivottabelldatakildeområde i Excel: For å endre kildedataområdet til pivottabeller dynamisk, bruker vi pivotcacher. Disse få linjene kan oppdatere hvilken som helst pivottabell dynamisk ved å endre kildedataområdet.
Slik oppdaterer du automatisk pivottabeller ved bruk av VBA: For å oppdatere pivottabellene automatisk kan du bruke VBA-hendelser. Bruk denne enkle kodelinjen for å oppdatere pivottabellen automatisk. Du kan bruke en av de tre metodene for automatisk oppdatering av pivottabeller.
Kjør makro hvis noen endringer er gjort på ark i spesifisert område: I VBA-praksis vil du få behov for å kjøre makroer når et bestemt område eller celleendringer. I så fall, for å kjøre makroer når en endring gjøres i et målområde, bruker vi endringshendelsen.
Kjør makro når noen endringer gjøres på ark | Så for å kjøre makroen når arket oppdateres, bruker vi arbeidsarkhendelsene til VBA.
Enkleste VBA-kode for å markere gjeldende rad og kolonne ved hjelp av | Bruk dette lille VBA-utdraget for å markere den gjeldende raden og kolonnen på arket.
Regnearkhendelsene i Excel VBA | Regnearkhendelsen er veldig nyttig når du vil at makroene dine skal kjøre når en spesifisert hendelse oppstår på arket.
Populære artikler:
50 Excel-snarveier for å øke produktiviteten din | Bli raskere på oppgaven din. Disse 50 snarveiene vil få deg til å jobbe enda raskere på Excel. VLOOKUP-funksjonen i Excel | Dette er en av de mest brukte og populære funksjonene i Excel som brukes til å slå opp verdi fra forskjellige områder og ark.
COUNTIF i Excel 2016 | Tell verdier med forhold som bruker denne fantastiske funksjonen. Du trenger ikke filtrere dataene dine for å telle spesifikk verdi. Countif-funksjonen er viktig for å forberede dashbordet.
Slik bruker du SUMIF-funksjonen i Excel | Dette er en annen viktig dashboardfunksjon. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.