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. Og hvis du sender en opdateret fil uden at opdatere pivottabellerne, kan du føle forlegenhed.
Så i denne artikel lærer vi, hvordan du 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 kildedatablad Objekt Privat under Worksheet_Deactivate () sheetname_of_pivot_table.PivotTables ("pivot_table_name"). PivotCache.Refresh End 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 End 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.
Private Sub Worksheet_Deactivate () 'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh For Hver pc I ThisWorkbook.PivotCaches pc.Refresh Næste pc Slut 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.
Privat underarbejdsark_ændring (ByVal -mål som område) Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End 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 End Sub
Bemærk, at denne kode ikke ændrer 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.
Så ja kompis, sådan kan du automatisk opdatere pivottabellerne i Excel. Jeg håber, jeg var forklarende nok, og denne artikel tjente dig godt. Hvis du har spørgsmål vedrørende dette emne, kan du stille mig i kommentarfeltet herunder.
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 fremhæve 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 | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
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.
COUNTIF i Excel 2016 | Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke filtrere dine data for at tælle specifik værdi. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.