I øjeblikket kan vi dynamisk ændre eller opdatere pivottabeller ved hjælp af Excel -tabeller eller dynamiske navngivne intervaller. Men disse teknikker er ikke idiotsikre. Da du stadig skal opdatere pivottabellen manuelt. Hvis du har store data, der indeholder tusindvis af rækker og kolonner, hjælper Excel -tabeller dig ikke meget. I stedet vil det gøre din fil tung. Så den eneste måde er tilbage er VBA.
I denne artikel lærer vi, hvordan vi kan få vores pivottabel til automatisk at ændre datakilden. Med andre ord vil vi automatisere den manuelle proces med at ændre datakilde til dynamisk at inkludere nye rækker og kolonner tilføjet til kildetabeller og afspejle ændringen i pivottabellen med det samme.
Skriv kode i kildedatablad
Da vi ønsker, at dette skal være helt automatisk, vil vi bruge arkmoduler til at skrive kode i stedet for et kernemodul. Dette giver os mulighed for at bruge regnearkhændelser.
Hvis kildedataene og pivottabellerne er i forskellige ark, skriver vi VBA -koden for at ændre pivottabellens datakilde i det arkobjekt, der indeholder kildedataene (ikke der indeholder pivottabellen).
Tryk på CTRL+F11 for at åbne VB -editoren. Gå nu til project explorer og find det ark, der indeholder kildedataene. Dobbeltklik på den.
Et nyt kodningsområde åbnes. Du kan muligvis ikke se nogen ændring, men nu har du adgang til regnearkshændelser.
Klik på rullemenuen til venstre, og vælg regnearket. Vælg deaktiver fra venstre rullemenu. Du vil se en tom sub skrevet på kodeområdet navn worksheet_deativate. Vores kode til dynamisk ændring af kildedata og opdatering af pivottabellen kommer i denne kodeblok. Denne kode kører, når du skifter fra databladet til et andet ark. Du kan læse om alle regnearkbegivenheder her.
Nu er vi klar til at implementere koden.
Kildekode til opdatering af pivottabel dynamisk med nyt område
For at forklare hvordan det fungerer, har jeg en projektmappe. Denne projektmappe indeholder to ark. Sheet1 indeholder de kildedata, der kan ændres. Sheet2 indeholder pivottabellen, som afhænger af kildedataene for sheet2.
Nu har jeg skrevet denne kode i sheet1's kodningsområde. Jeg bruger hændelsen Worksheet_Deactivate, så denne kode kører for at opdatere pivottabellen, når vi skifter fra kildedatablad.
Private Sub Worksheet_Deactivate () Dim pt As PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Cells (Rows.Count, 1). End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft). Column Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivotCache pc Slut Sub
Hvis du har en lignende projektmappe, kan du direkte kopiere disse data. Jeg har forklaret, at denne kode fungerer nedenfor.
Du kan se effekten af denne kode i gif nedenfor.
Hvordan ændrer denne kode automatisk kildedata og opdaterer pivottabeller?
Først og fremmest brugte vi en regneark_deaktiver hændelse. Denne hændelse udløses kun, når arket, der indeholder koden, skiftes eller deaktiveres. Så sådan kører koden automatisk.
Nu for at ændre kildedataene i pivottabellen ændrer vi data i pivotcachen.
Der oprettes en pivottabel ved hjælp af pivot -cache. Pivot -cachen indeholder de gamle kildedata, indtil pivottabellen ikke opdateres manuelt, eller kildedataområdet ændres manuelt.
Vi har oprettet referencer til pivottabeller navn pt, pivot -cache med navnet pc og et område med navnet source_data. Kildedataene vil indeholde alle dataene.
For dynamisk at få hele tabellen som dataområde bestemmer vi den sidste række og sidste kolonne.
lstrow = Celler (rækker.Tælling, 1). Afslut (xlUp) .Række
lstcol = Celler (1, Columns.Count). End (xlToLeft) .Column
Ved hjælp af disse to tal definerer vi source_data. Vi er sikre på, at kildedataområdet altid starter fra A1.
Angiv source_data = Range (celler (1, 1), celler (lstrow, lstcol))
Nu har vi kildedataene, som er dynamiske. Vi skal bare bruge det i pivottabellen.
Vi gemmer disse data i pivot -cache, som vi kender, at pivot -cache gemmer alle data.
Indstil pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Dernæst definerer vi pivottabellen, som vi vil opdatere. Da vi ønsker at opdatere pivottabellen1 (pivottabellens navn. Du kan kontrollere navnet på pivottabellen i fanen Analyse, mens du vælger pivottabellen.) På ark1, sætter vi pt som vist nedenfor.
Indstil pt = Sheet2.PivotTables ("PivotTable1")
Nu bruger vi simpelthen denne pivot -cache til at opdatere pivottabellen. Vi bruger metoden changePivotCache for pt -objekt.
pt.ChangePivotCache pc
Og vi har vores drejebord automatiseret. Dette opdaterer automatisk din pivottabel. Hvis du har flere tabeller med den samme datakilde, skal du bare bruge den samme cache i hvert pivottabelobjekt.
Så ja fyre, sådan kan du dynamisk ændre datakildeinterval i Excel. Jeg håber, jeg var forklarende nok. Hvis du har spørgsmål vedrørende denne artikel, så lad mig vide det i kommentarfeltet herunder.
Sådan opdateres pivottabeller automatisk ved hjælp af VBA: For automatisk at opdatere dine pivottabeller kan du bruge VBA -begivenheder. Brug denne enkle kodelinje til automatisk at opdatere din pivottabel. Du kan bruge en af tre metoder til automatisk opdatering af pivottabeller.
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.