I denne artikkelen lærer vi hvordan du automatisk oppdaterer pivottabelldata i Excel.
Scenario:
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 når vi åpner en Excel -arbeidsbok. Og hvis du sender en oppdatert fil uten å oppdatere pivottabellene, kan du føle deg flau. Så lær her hvordan du finner oppdateringsalternativet når du bruker pivottabell
Oppdater data når du åpner en fil i Excel
Opprett først en pivottabell, og høyreklikk deretter en hvilken som helst pivottabellcelle.
Gå til pivottabellalternativer> Datafane> Merk av i boksen som sier Oppdater data når du åpner en fil
Dette vil aktivere data for automatisk oppdatering når filen åpnes.
Eksempel:
Alt dette kan være forvirrende å forstå. La oss forstå hvordan du bruker funksjonen ved hjelp av et eksempel. Her har vi noen data, og vi må først opprette et pivottabell og deretter finne alternativene for å aktivere pivottabeller for automatisk oppdatering.
Lag en pivottabell og høyreklikk deretter hvilken som helst pivottabellcelle som vist nedenfor.
Velg Pivottabellalternativer, og dette vil åpne en PIvot -tabellalternativer.
Velg datafanen og merk av i boksen som sier Oppdater data når du åpner filen. Du kan utføre dette uten å åpne og lukke filen ved hjelp av VBA.
Bruker VBA
Så her 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 kildedatabladobjekt
Private Sub Worksheet_Deactivate () arkenavn_av_pivot_tabell.PivotTables ("pivot_table_name") .PivotCache.Refresh Slutt 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 Slutt 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 hver pc I ThisWorkbook.PivotCaches PC Oppdater Neste pc Slutt 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 Slutt 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 Slutt Sub |
Merk : Koden endrer ikke 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.
Håper denne artikkelen om hvordan du automatisk oppdaterer pivottabelldata i Excel er forklarende. Finn flere artikler om beregning av verdier og relaterte Excel -formler her. Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post.
Hvordan dynamisk oppdatere pivottabelldatakildeområ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 med oppgavene i Excel. Disse snarveiene hjelper deg med å øke arbeidseffektiviteten din i Excel.
Slik bruker du 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.
Slik bruker du IF -funksjonen i Excel : IF -setningen i Excel sjekker tilstanden og returnerer en bestemt verdi hvis betingelsen er SANN eller returnerer en annen spesifikk verdi hvis FALSE.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.