I denne artikel lærer vi, hvordan du automatisk opdaterer pivottabeldata i Excel.
Scenarie:
Som vi alle ved, hver gang vi foretager ændringer i en kildedata i en pivottabel, afspejles det ikke umiddelbart i pivottabellen. Vi skal opdatere pivottabellerne for at se ændringerne, når vi åbner en Excel -projektmappe. Og hvis du sender en opdateret fil uden at opdatere pivottabellerne, kan du føle forlegenhed. Så lær her, hvordan du finder opdateringsindstillingen, når du bruger pivottabel
Opdater data, når du åbner en fil i Excel
Opret først en pivottabel, og højreklik derefter på en hvilken som helst pivottabellcelle.
Gå til pivottabelindstillinger> fanen Data> Marker feltet, der siger Opdater data, når du åbner en fil
Dette aktiverer automatisk opdatering af data, når filen åbnes.
Eksempel:
Alt dette kan være forvirrende at forstå. Lad os forstå, hvordan du bruger funktionen ved hjælp af et eksempel. Her har vi nogle data, og vi skal først oprette en pivottabel og derefter finde mulighederne for at aktivere pivottabeller til automatisk opdatering.
Opret en pivottabel, og højreklik derefter på en hvilken som helst pivottabellcelle som vist herunder.
Vælg indstillingerne for pivottabellen, og dette åbner en dialogboks med indstillinger for PIvot -tabel.
Vælg fanen data, og marker derefter feltet, der siger Opdater data, når filen åbnes. Du kan udføre dette uden at åbne og lukke filen ved hjælp af VBA.
Brug af VBA
Så her lærer vi, hvordan man automatisk opdaterer et pivottabel ved hjælp af VBA. Denne måde er lettere, end du havde forestillet dig.
Dette er den enkle syntaks til automatisk opdatering af pivottabeller i projektmappen.
| 'Kode i kildedatabladobjekt
Privat under regneark_Deaktiver () sheetname_of_pivot_table.PivotTables ("pivot_table_name") .PivotCache.Refresh Afslut Sub |
Hvad er Pivot Caches?
Hver pivottabel gemmer dataene i pivot -cachen. Det er derfor, pivot er i stand til at vise tidligere data. Når vi opdaterer pivottabeller, opdaterer det cachen med nye kildedata for at afspejle ændringerne i pivottabellen.
Så vi mangler bare en makro for at opdatere cachen i pivottabeller. Vi gør dette ved hjælp af en regnearkhændelse, så vi ikke behøver at køre makro manuelt.
Hvor skal man kode til automatisk opdatering af pivottabeller?
Hvis dine kildedata og pivottabeller findes i forskellige ark, skal VBA -koden stå i kildedatabladet.
Her vil vi bruge Worksheet_SelectionChange Event. Dette får koden til at køre, når vi skifter fra kildedatabladet til et andet ark. Jeg forklarer senere, hvorfor jeg brugte denne begivenhed.
Her har jeg kildedata i sheet2 og pivottabeller i sheet1.
Åbn VBE ved hjælp af CTRL+F11 -tasten. I project explorer kan du se tre objekter, Sheet1, Sheet2 og Workbook.
Da Sheet2 indeholder kildedataene, skal du dobbeltklikke på sheet2 -objektet.
Nu kan du se to dropdown'er øverst i kodeområdet. Vælg regnearket fra den første rulleliste. Og fra den anden drop down vælg Deaktiver. Dette vil indsætte et tomt undernavn Arbejdsark_Deaktiver. Vores kode vil blive skrevet i denne under. Alle linjer skrevet i denne sub, bliver eksekveret, så snart brugeren skifter fra dette ark til et andet ark.
På ark1 har jeg to drejeborde. Jeg vil kun opdatere et drejebord. Til det skal jeg kende navnet på pivottabellen. For at kende navnet på en hvilken som helst pivottabel, skal du markere en celle i denne pivottabel, gå til fanen pivottabelanalyse. På venstre side vil du se navnet på pivottabellen. Du kan også ændre navnet på pivottabellen her.
Nu kender vi navnet på pivottabellen, vi kan skrive en simpel linje for at opdatere pivottabellen.
| Privat under regneark_Deaktiver ()
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Afslut Sub |
Og det er gjort.
Når du nu skifter fra kildedataene, kører denne vba -kode for at opdatere pivottabellen1. Som du kan se i gif'en herunder.
Sådan opdateres alle pivottabellerne i projektmappen?
I ovenstående eksempel ønskede vi kun at opdatere en bestemt pivottabel. Men hvis du vil opdatere alle pivottabellerne i en projektmappe, skal du bare foretage små ændringer i din kode.
| Privat under regneark_Deaktiver ()
'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh For hver pc I ThisWorkbook.PivotCaches pc Opdater Næste pc Afslut Sub |
I denne kode bruger vi en For -loop til at gå gennem hver pivot -cache i projektmappen. ThisWorkbook -objektet indeholder alle pivot -cacherne. For at få adgang til dem bruger vi ThisWorkbook.PivotCaches.
Hvorfor bruge Worksheet_Deactivate Event?
Hvis du vil opdatere pivottabellen, så snart der foretages ændringer i kildedata, skal du bruge Worksheet_Change -hændelse. Men jeg anbefaler det ikke. Det får din projektmappe til at køre koden hver gang du foretager ændringer i arket. Du skal muligvis foretage hundredvis af ændringer, før du vil se resultatet. Men excel vil opdatere pivottabellen ved hver ændring. Dette vil føre til spild af behandlingstid og ressourcer. Så hvis du har pivottabeller og data i forskellige ark, er det bedre at bruge regneark Deaktiver hændelse. Det lader dig afslutte dit arbejde. Når du først skifter til pivottabelark for at se ændringerne, ændrer det ændringerne.
Hvis du har pivottabeller og kildedata på det samme ark, og du vil have pivottabeller til automatisk at opdatere det selv, kan du bruge Worksheet_Change Event.
| Private Sub Worksheet_Change (ByVal Target As Range)
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Afslut Sub |
Hvordan opdateres alt i projektmapper, når der foretages en ændring i kildedata?
Hvis du vil opdatere alt på en projektmappe (diagrammer, pivottabeller, formler osv.), Kan du bruge ThisWorkbook.RefreshAll -kommandoen.
| Private Sub Worksheet_Change (ByVal Target As Range)
ThisWorkbook.RefreshAll Afslut Sub |
Bemærk : Koden ændrer ikke datakilden. Så hvis du tilføjer data under kildedataene, inkluderer denne kode ikke disse data automatisk. Du kan bruge Excel -tabeller til at gemme kildedata. Hvis du ikke vil bruge tabeller, kan vi også bruge VBA til at inkludere nye data. Vi lærer det i den næste vejledning.
Håber denne artikel om Sådan opdateres pivottabeldata automatisk i Excel er forklarende. Find flere artikler om beregning af værdier og relaterede Excel -formler her. Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet.
Sådan dynamisk opdateres pivottabellens datakildeområde i Excel : For dynamisk at ændre kildedataområdet for pivottabeller bruger vi pivotcaches. Disse få linjer kan dynamisk opdatere enhver pivottabel ved at ændre kildedataområdet. I VBA skal du bruge pivottabeller som vist herunder …
Kør makro, hvis der foretages ændringer på ark i specificeret område : I din VBA -praksis får du behovet for at køre makroer, når et bestemt område eller en celle ændres. I så fald bruger vi ændringshændelsen til at køre makroer, når der foretages en ændring i et målområde.
Kør makro, når der foretages ændringer på ark : Så for at køre din makro, når arket opdateres, bruger vi regnearkhændelser i VBA.
Den enkleste VBA -kode til at fremhæve den aktuelle række og kolonne ved hjælp af : Brug dette lille VBA -udsnit til at markere den aktuelle række og kolonne i arket.
Regnearket Begivenheder i Excel VBA : Regnearkshændelsen er virkelig nyttig, når du vil have dine makroer til at køre, når en bestemt hændelse forekommer på arket.
Populære artikler:
50 Excel -genveje til at øge din produktivitet : Bliv hurtigere til dine opgaver i Excel. Disse genveje hjælper dig med at øge din arbejdseffektivitet i Excel.
Sådan bruges VLOOKUP -funktionen i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges IF -funktionen i Excel : IF -sætningen i Excel kontrollerer betingelsen og returnerer en bestemt værdi, hvis betingelsen er SAND, eller returnerer en anden specifik værdi, hvis FALSK.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.
Sådan bruges COUNTIF -funktionen i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.