För närvarande kan vi dynamiskt ändra eller uppdatera pivottabeller med Excel -tabeller eller dynamiska namngivna intervall. Men dessa tekniker är inte idiotsäkra. Eftersom du fortfarande måste uppdatera pivottabellen manuellt. Om du har stora data som innehåller tusentals rader och kolumner kommer Excel -tabeller inte att hjälpa dig mycket. Istället kommer det att göra din fil tung. Så det enda sättet som återstår är VBA.
I den här artikeln kommer vi att lära oss hur vi kan få vår pivottabell att automatiskt ändra datakällan. Med andra ord kommer vi att automatisera den manuella processen med att ändra datakälla för att dynamiskt inkludera nya rader och kolumner som läggs till i källtabellerna och återspegla förändringen i pivottabellen direkt.
Skriv kod i källdatablad
Eftersom vi vill att detta ska vara helt automatiskt kommer vi att använda arkmoduler för att skriva kod istället för en kärnmodul. Detta gör att vi kan använda kalkylbladshändelser.
Om källdata och pivottabeller finns i olika blad skriver vi VBA -koden för att ändra pivottabellens datakälla i bladobjektet som innehåller källdata (inte som innehåller pivottabell).
Tryck på CTRL+F11 för att öppna VB -redigeraren. Gå nu till projektutforskaren och hitta arket som innehåller källdata. Dubbelklicka på den.
Ett nytt kodningsområde öppnas. Du kanske inte ser någon ändring men nu har du tillgång till kalkylbladshändelserna.
Klicka på den vänstra rullgardinsmenyn och välj kalkylbladet. Välj inaktivera från den vänstra rullgardinsmenyn. Du kommer att se en tom sub skriven på kodområdets namn worksheet_deativate. Vår kod för dynamiskt förändring av källdata och uppfriskande pivottabell kommer i detta kodblock. Denna kod körs när du byter från databladet till något annat blad. Du kan läsa om alla arbetsbladshändelser här.
Nu är vi redo att implementera koden.
Källkod för att uppdatera pivottabell dynamiskt med nytt intervall
För att förklara hur det fungerar har jag en arbetsbok. Denna arbetsbok innehåller två blad. Sheet1 innehåller källdata som kan ändras. Sheet2 innehåller pivottabellen som beror på källdata för sheet2.
Nu har jag skrivit den här koden i bladets kodningsområde. Jag använder händelsen Worksheet_Deactivate så att denna kod körs för att uppdatera pivottabellen när vi byter från källdatablad.
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
Om du har en liknande arbetsbok kan du kopiera dessa data direkt. Jag har förklarat att den här koden fungerar nedan.
Du kan se effekten av denna kod i gif nedan.
Hur ändrar denna kod automatiskt källdata och uppdaterar pivottabeller?
Först och främst använde vi en arbetsblad_deaktivera händelse. Denna händelse utlöses endast när arket som innehåller koden byts eller inaktiveras. Så det är så koden körs automatiskt.
Nu för att ändra källdata i pivottabellen ändrar vi data i pivotcachen.
En pivottabell skapas med hjälp av pivotcache. Pivotcacheminnet innehåller den gamla källdatan tills pivottabellen inte uppdateras manuellt eller källdataintervallet ändras manuellt.
Vi har skapat referenser till pivottabellerna pt, pivotcache med namnet pc och ett område som heter source_data. Källdata kommer att innehålla hela data.
För att dynamiskt få hela tabellen som dataintervall bestämmer vi den sista raden och sista kolumnen.
lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row
lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column
Med hjälp av dessa två nummer definierar vi source_data. Vi är bestämda att källdataintervallet alltid kommer att börja från A1.
Ange source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))
Nu har vi källdata som är dynamiska. Vi behöver bara använda den i pivottabellen.
Vi lagrar dessa data i pivotcache som vi vet att pivotcache lagrar all data.
Ange pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Därefter definierar vi pivottabellen som vi vill uppdatera. Eftersom vi vill uppdatera pivottabellen1 (pivottabellens namn. Du kan kontrollera namnet på pivottabellen på fliken analys medan du väljer pivottabellen.) På blad1, ställer vi in pt enligt nedan.
Ange pt = Sheet2.PivotTables ("PivotTable1")
Nu använder vi helt enkelt denna pivotcache för att uppdatera pivottabellen. Vi använder metoden changePivotCache för pt -objekt.
pt.ChangePivotCache -dator
Och vi har vårt svängbord automatiserat. Detta uppdaterar automatiskt din pivottabell. Om du har flera tabeller med samma datakälla använder du bara samma cache i varje pivottabellobjekt.
Så ja grabbar, så här kan du dynamiskt ändra datakällans intervall i Excel. Jag hoppas att jag var tillräckligt förklarande. Om du har några frågor angående denna artikel, låt mig veta i kommentarfältet nedan.
Så här uppdaterar du automatiskt pivottabeller med VBA: För att automatiskt uppdatera dina pivottabeller kan du använda VBA -händelser. Använd denna enkla kodrad för att uppdatera ditt pivottabell automatiskt. Du kan använda någon av tre metoder för automatisk uppdatering av pivottabeller.
Kör makro om någon ändring görs på blad i specificerat intervall: I dina VBA -metoder skulle du behöva köra makron när ett visst område eller en cell ändras. I så fall använder vi ändringshändelsen för att köra makron när en ändring görs i ett målintervall.
Kör makro när någon ändring görs på arket | Så för att köra ditt makro när arket uppdateras använder vi Arbetsbladshändelser i VBA.
Enklaste VBA -kod för att markera aktuell rad och kolumn med | Använd det här lilla VBA -utdraget för att markera den aktuella raden och kolumnen i arket.
Arbetsbladshändelser i Excel VBA | Arbetsbladshändelsen är verkligen användbar när du vill att dina makron ska köras när en viss händelse inträffar på arket.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.VLOOKUP -funktionen i Excel | Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
COUNTIF i Excel 2016 | Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifikt värde. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel | Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.