I den här artikeln kommer vi att lära oss hur man automatiskt uppdaterar pivottabelldata i Excel.
Scenario:
Som vi alla vet, när vi gör ändringar i en källdata i en pivottabell, återspeglas det inte direkt i pivottabellen. Vi måste uppdatera pivottabellerna för att se ändringarna när vi öppnar en Excel -arbetsbok. Och om du skickar en uppdaterad fil utan att uppdatera pivottabellerna kan du känna dig generad. Så lär dig här hur du hittar uppdateringsalternativet när du använder pivottabell
Uppdatera data när du öppnar en fil i Excel
Skapa först en pivottabell och högerklicka sedan på en pivottabellcell.
Gå till pivottabellalternativ> fliken Data> Markera rutan som säger Uppdatera data när du öppnar en fil
Detta möjliggör automatisk uppdatering av data när filen öppnas.
Exempel:
Allt detta kan vara förvirrande att förstå. Låt oss förstå hur man använder funktionen med hjälp av ett exempel. Här har vi lite data och vi måste först skapa en pivottabell och sedan hitta alternativen för att aktivera pivottabeller för automatisk uppdatering.
Skapa en pivottabell och högerklicka sedan på vilken pivottabellcell som helst nedan.
Välj Pivottabellalternativ så öppnas en PIvot -tabellalternativ.
Välj datafliken och markera sedan rutan som säger Uppdatera data när du öppnar filen. Du kan utföra detta utan att öppna och stänga filen med VBA.
Använda VBA
Så här kommer vi att lära oss hur man automatiskt uppdaterar ett pivottabell med VBA. Detta sätt är lättare än du föreställt dig.
Detta är den enkla syntaxen för att automatiskt uppdatera pivottabeller i arbetsboken.
| 'Kod i källdatabladobjekt
Private Sub Worksheet_Deactivate () sheetname_of_pivot_table.PivotTables ("pivot_table_name") .PivotCache.Refresh Avsluta Sub |
Vad är Pivot Caches?
Varje pivottabell lagrar data i pivotcache. Det är därför pivot kan visa tidigare data. När vi uppdaterar pivottabellerna uppdateras cacheminnet med ny källdata för att återspegla ändringarna i pivottabellen.
Så vi behöver bara ett makro för att uppdatera cachen för pivottabeller. Vi kommer att göra detta med hjälp av en kalkylhändelse så att vi inte behöver köra makro manuellt.
Var ska man koda till automatisk uppdatering av pivottabeller?
Om dina källdata och pivottabeller finns i olika blad, ska VBA -koden finnas i källdatabladet.
Här kommer vi att använda Worksheet_SelectionChange Event. Detta gör att koden körs när vi byter från källdatabladet till ett annat blad. Jag förklarar senare varför jag använde denna händelse.
Här har jag källdata i sheet2 och pivottabeller i sheet1.
Öppna VBE med CTRL+F11 -tangenten. I projektutforskaren kan du se tre objekt, Sheet1, Sheet2 och Workbook.
Eftersom Sheet2 innehåller källdata dubbelklickar du på sheet2 -objektet.
Nu kan du se två nedrullningar längst upp i kodområdet. Från den första rullgardinsmenyn, välj kalkylbladet. Och från den andra rullgardinsmenyn väljer du Inaktivera. Detta kommer att infoga ett tomt undernamn Worksheet_Deactivate. Vår kod kommer att skrivas i denna del. Alla rader som skrivs i denna del, körs så snart användaren byter från detta blad till något annat blad.
På blad1 har jag två vridbord. Jag vill bara uppdatera ett pivottabell. För det måste jag veta namnet på pivottabellen. Om du vill veta namnet på någon pivottabell markerar du vilken cell som helst i den pivottabellen och går till fliken pivottabellanalys. På vänster sida ser du namnet på pivottabellen. Du kan också ändra namnet på pivottabellen här.
Nu vet vi namnet på pivottabellen, vi kan skriva en enkel rad för att uppdatera pivottabellen.
| Private Sub Worksheet_Deactivate ()
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Avsluta Sub |
Och det är gjort.
Nu när du byter från källdata körs denna vba -kod för att uppdatera pivottabellen1. Som du kan se i gif nedan.
Hur uppdaterar jag alla pivottabeller i arbetsboken?
I exemplet ovan ville vi bara uppdatera en specifik pivottabell. Men om du vill uppdatera alla pivottabellerna i en arbetsbok behöver du bara göra små ändringar i din kod.
| Private Sub Worksheet_Deactivate ()
'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh För varje dator i ThisWorkbook.PivotCaches PC Uppdatera Nästa dator Avsluta Sub |
I den här koden använder vi en For -loop för att gå igenom varje pivotcacher i arbetsboken. ThisWorkbook -objektet innehåller alla pivotcacher. För att komma åt dem använder vi ThisWorkbook.PivotCaches.
Varför använda Worksheet_Deactivate Event?
Om du vill uppdatera pivottabellen så snart någon ändring görs i källdata bör du använda Worksheet_Change -händelsen. Men jag rekommenderar det inte. Det kommer att få din arbetsbok att köra koden varje gång du gör några ändringar i arket. Du kan behöva göra hundratals ändringar innan du vill se resultatet. Men excel kommer att uppdatera pivottabellen vid varje förändring. Detta kommer att leda till slöseri med behandlingstid och resurser. Så om du har pivottabeller och data i olika blad är det bättre att använda Worksheet Deactivate Event. Det låter dig avsluta ditt arbete. När du väl byter till pivottabellark för att se ändringarna, ändrar det ändringarna.
Om du har pivottabeller och källdata på samma blad och du vill att pivottabellerna ska uppdatera det själv kan du använda Worksheet_Change Event.
| Private Sub Worksheet_Change (ByVal Target As Range)
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Avsluta Sub |
Hur uppdaterar jag allt i arbetsböcker när en ändring görs i källdata?
Om du vill uppdatera allt i en arbetsbok (diagram, pivottabeller, formler etc.) kan du använda kommandot ThisWorkbook.RefreshAll.
| Private Sub Worksheet_Change (ByVal Target As Range)
ThisWorkbook.RefreshAll Avsluta Sub |
Notera : Koden ändrar inte datakällan. Så om du lägger till data under källdatan kommer den här koden inte att inkludera dessa data automatiskt. Du kan använda Excel -tabeller för att lagra källdata. Om du inte vill använda tabeller kan vi också använda VBA för att inkludera ny data. Vi lär oss det i nästa handledning.
Hoppas att den här artikeln om hur man automatiskt uppdaterar pivottabelldata i Excel är förklarande. Hitta fler artiklar om beräkning av värden och relaterade Excel -formler här. Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen.
Hur du dynamiskt uppdaterar pivottabellens datakällor i Excel : För att dynamiskt ändra källdataintervallet för pivottabeller använder vi pivotcacher. Dessa få rader kan dynamiskt uppdatera alla pivottabeller genom att ändra källdataintervallet. I VBA använder du pivottabeller som visas nedan …
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å dina uppgifter i Excel. Dessa genvägar hjälper dig att öka din arbetseffektivitet i Excel.
Så här använder du 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.
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
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.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.