Som vi alle vet, hver gang vi gjør endringer i kildedataene i en pivottabell, gjenspeiles det ikke umiddelbart i pivottabellen. Vi må oppdatere pivottabellene for å se endringene. Og hvis du sender en oppdatert fil uten å oppdatere pivottabellene, kan du føle deg flau.
Så i denne artikkelen lærer vi hvordan du automatisk oppdaterer et pivottabell ved hjelp av VBA. Denne måten er enklere enn du hadde forestilt deg.
Dette er den enkle syntaksen for å automatisk oppdatere pivottabeller i arbeidsboken.
'Kode i kildedatablad Objekt Privat under Worksheet_Deactivate () sheetname_of_pivot_table.PivotTables ("pivot_table_name"). PivotCache.Refresh End Sub
Hva er Pivot Caches?
Hver pivottabell lagrer dataene i pivotbufferen. Dette er grunnen til at pivot kan vise tidligere data. Når vi oppdaterer pivottabeller, oppdaterer den hurtigbufferen med nye kildedata for å gjenspeile endringene i pivottabellen.
Så vi trenger bare en makro for å oppdatere hurtigbufferen til pivottabeller. Vi gjør dette ved hjelp av en regnearkhendelse, slik at vi ikke trenger å kjøre makro manuelt.
Hvor koder jeg for å automatisk oppdatere pivottabeller?
Hvis kildedataene og pivottabellene er i forskjellige ark, bør VBA -koden gå i kildedatabladet.
Her vil vi bruke Worksheet_SelectionChange Event. Dette vil få koden til å kjøre hver gang vi bytter fra kildedatabladet til et annet ark. Jeg skal forklare senere hvorfor jeg brukte denne hendelsen.
Her har jeg kildedata i ark2 og pivottabeller i ark1.
Åpne VBE ved hjelp av CTRL+F11 -tasten. I prosjektutforsker kan du se tre objekter, Sheet1, Sheet2 og Workbook.
Siden Sheet2 inneholder kildedataene, dobbeltklikker du på sheet2 -objektet.
Nå kan du se to nedtrekkslinjer øverst i kodeområdet. Velg regnearket fra den første rullegardinlisten. Og fra den andre rullegardinmenyen, velg Deaktiver. Dette vil sette inn et tomt undernavn Worksheet_Deactivate. Koden vår vil bli skrevet i denne delen. Alle linjer skrevet i denne suben, blir utført så snart brukeren bytter fra dette arket til et annet ark.
På ark1 har jeg to pivottabeller. Jeg vil bare oppdatere ett pivottabell. For det må jeg vite navnet på pivottabellen. For å vite navnet på en hvilken som helst pivottabell, velg hvilken som helst celle i den pivottabellen, gå til fanen for pivottabellanalyse. På venstre side vil du se navnet på pivottabellen. Du kan også endre navnet på pivottabellen her.
Nå som vi vet navnet på pivottabellen, kan vi skrive en enkel linje for å oppdatere pivottabellen.
Private Sub Worksheet_Deactivate () Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Og det er gjort.
Når du nå bytter fra kildedata, kjører denne vba -koden for å oppdatere pivottabellen1. Som du kan se i gifen nedenfor.
Hvordan oppdatere alle pivottabellene i arbeidsboken?
I eksemplet ovenfor ville vi bare oppdatere ett bestemt pivottabell. Men hvis du vil oppdatere alle pivottabellene i en arbeidsbok, trenger du bare å gjøre små endringer i koden din.
Private Sub Worksheet_Deactivate () 'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh For Every pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
I denne koden bruker vi en For -løkke for å gå gjennom hver pivotbuffer i arbeidsboken. ThisWorkbook -objektet inneholder alle pivotbufferne. For å få tilgang til dem bruker vi ThisWorkbook.PivotCaches.
Hvorfor bruke Worksheet_Deactivate Event?
Hvis du vil oppdatere pivottabellen så snart endringer er gjort i kildedata, bør du bruke Worksheet_Change -hendelsen. Men jeg anbefaler det ikke. Det vil få arbeidsboken til å kjøre koden hver gang du gjør endringer i arket. Du må kanskje gjøre hundrevis av endringer før du vil se resultatet. Men excel vil oppdatere pivottabellen ved hver endring. Dette vil føre til sløsing med behandlingstid og ressurser. Så hvis du har pivottabeller og data i forskjellige ark, er det bedre å bruke Worksheet Deactivate Event. Det lar deg finne arbeidet ditt. Når du bytter til pivottabellark for å se endringene, endrer det endringene.
Hvis du har pivottabeller og kildedata på samme ark, og du vil at pivottabeller skal oppdatere det selv, kan det være lurt å bruke Worksheet_Change Event.
Private Sub Worksheet_Change (ByVal Target As Range) Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Hvordan oppdatere alt i arbeidsbøker når det endres i kildedata?
Hvis du vil oppdatere alt på en arbeidsbok (diagrammer, pivottabeller, formler osv.), Kan du bruke ThisWorkbook.RefreshAll -kommandoen.
Private Sub Worksheet_Change (ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Vær oppmerksom på at denne koden ikke endrer datakilden. Så hvis du legger til data under kildedataene, vil denne koden ikke inkludere disse dataene automatisk. Du kan bruke Excel -tabeller til å lagre kildedata. Hvis du ikke vil bruke tabeller, kan vi bruke VBA for å inkludere nye data også. Vi lærer det i neste opplæring.
Så ja kompis, slik kan du automatisk oppdatere pivottabellene i Excel. Jeg håper jeg var forklarende nok, og denne artikkelen tjente deg godt. Hvis du har spørsmål angående dette emnet, kan du stille meg 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. I VBA bruker du pivottabeller som vist nedenfor …
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.