Nedre raden: Lär dig hur du använder ett enkelt makro för att uppdatera pivottabeller automatiskt när ändringar görs i källdata. Jag delar också en icke-makrolösning för att uppdatera pivottabellerna när filen öppnas. Inkluderar videohandledning och nedladdning av Excel-filer.
Skill Level: Intermediate
Ladda ner Excel-filen
Om du lär dig bäst genom att göra det själv kan du ladda ner filen som jag använder i videon för att följa med. Här är Excel-filen som innehåller VBA-koden.
Uppdatera pivottabell automatiskt. xlsm (41,5 kB)
Uppdatera pivottabeller automatiskt
Kan dina pivottabeller uppdateras omedelbart och automatiskt när deras källdata ändras?
Absolut. Det kräver användning av ett riktigt enkelt makro som jag kommer att visa dig hur du skapar nedan.
Om du inte är så bekant med makron och VBA, föreslår jag att jag tittar på min gratis 3-delade videoserie för att komma igång med makron & VBA.
Om du är nybörjare i pivottabeller, Jag har en serie för att gå igenom vad de är och hur man använder dem. Se den första videon i den serien på pivottabeller & Instrumentpaneler
För att automatiskt uppdatera våra pivottabeller ska vi skriva ett makro med en enkel instruktion. Den instruktionen säger i grund och botten: när jag gör en ändring i mitt kalkylblad, uppdatera alla pivottabeller och dataanslutningar. Här är stegen för att skapa makrot.
Öppna Visual Basic Editor.
Du kan göra detta genom att klicka på Visual Basic-knappen på fliken Developer i menyfliksområdet.
Kortkommandot för att öppna Visual Basic-redigeraren är Alt + F11.
Om du inte ser på fliken Utvecklare kan du göra den synlig med instruktionerna här. Du behöver bara göra detta en gång, och då kommer fliken Developer alltid att vara synlig varje gång du öppnar Excel i framtiden.
Öppna Sheet Module som innehåller dina källdata.
Leta reda på arbetsboken som du vill ändra i Project Explorer-fönstret i Visual Basic-redigeraren. Under den arbetsboken listas arken i arbetsboken. Välj arket som innehåller källdata. Dubbelklicka sedan på det.
Om du inte ser Project Explorer-fönstret kan du aktivera det från Visa meny (kortkommando: Ctrl + R).
Lägg till en ny händelse för ändringar av kalkylblad.
Dubbelklicka på arket öppnar kodmodulen för det objektet. Inom kodmodulen vill vi skapa ett händelsemakro. För att göra det, välj Kalkylblad i listrutan Objekt till vänster.
Det lägger till en kalkylark_SelectionChange-händelse till modulen, som vi egentligen inte vill ha, så vi tar bort den på ett ögonblick. Innan vi gör det, låt oss gå till rullgardinsmenyn Procedur till höger och välj Ändra.
Detta lägger till en ny händelse högst upp som heter Worksheet_Change. Nu kommer vi att markera och ta bort den onödiga koden under den.
Händelsemakrot Worksheet_Change körs när som helst en ändring görs till celler i det kalkylbladet. Vi kan lägga till VBA-kod till Worksheet_Change-händelsen för att utföra åtgärder när användaren redigerar celler.
Obs: SelectionChange-händelsen som läggs till som standard körs närhelst användaren väljer en cell i arket. Eftersom vi bara vill att koden ska köras när användaren redigerar / ändrar celler använder vi ändringshändelsen. Kolla in min artikel om VBA-kodmoduler & Så här kör du makron baserat på användarhändelser för att lära dig mer om arkmodulerna och händelserna.
Lägg till VBA-koden för att uppdatera alla pivottabeller.
Därefter, nedanför raden Worksheet_Change, skriv in den här instruktionen:
ThisWorkbook.RefreshAll
Metoden RefreshAll uppdaterar alla pivottabeller, frågor och dataanslutningar i arbetsboken. Den här åtgärden är densamma som om du manuellt klickar på knappen Uppdatera på fliken Data.
Lägg till den här raden kod i händelsen Worksheet_Change uppdaterar arbetsboken när en ändring görs i kalkylbladet som koden är in.
Pivottabell & Källdata på samma blad
Aleksandrs ställde en stor fråga på YouTube-videokommentarerna. Om din pivottabell och källdata finns på samma ark måste du lägga till kod för att inaktivera händelser.
Uppdateringen sätter händelsen i en rekursiv slinga och kan sluta krascha Excel. Här är koden för att förhindra det.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Kontrollerar att din makro körs
Ett sätt att kontrollera om makrot fungerar är att testa det. Gör en ändring av källdata och se om den återspeglas i din pivottabell.Om din förändring inte är lätt att upptäcka eftersom du har för mycket data eller av någon annan anledning finns det ett annat sätt att se om ditt makro avfyras.
I VB-redigeraren kan du klicka på grå kolumn till vänster om ditt Worksheet_Change-makro. Detta gör att en röd cirkel visas. Den markerar också den här kodraden i rött.
Detta kallas stopp eller brytpunkt.
Kortkommandot för att slå en brytpunkt på / av är: F9
Nu när en åtgärd inträffar som utlöser makrot, hoppar Excel till VB Editor och pausar makrot så att du kan kontrollera koden. I vårt fall är den åtgärden någon förändring som görs i kalkylbladet.
Du kan sedan trycka på F8 för att gå igenom varje eller tryck på F5 för att springa till slutet (eller nästa brytpunkt).
Om du gör en ändring i kalkylbladet och Excel inte drar dig in i VB Editor, vet du att det finns ett problem med makrot körs inte. Om så är fallet är det troligt att du inte har sparat filen som en makroaktiverad arbetsbok (.xlsm) och / eller aktiverade makron. Du kan behöva spara & stäng filen, öppna den igen och aktivera makron.
För att ta bort brytpunkten som du har placerat i makrot, klicka bara på den röda cirkeln för att försvinna (tangentbordsgenväg: F9).
Kortkommandot för att rensa alla brytpunkter är: Ctrl + Skift + F9
Uppdatera pivottabeller utan en Makro
En nackdel med att använda detta makro för att uppdatera dina pivottabeller är att eventuell Ångra-historik går förlorad varje gång makrot körs. Med andra ord, när du klickar på Ångra-knappen (eller trycker på Ctrl + Z) kommer Excel inte ihåg det senaste du gjorde, så det kan inte ångra det. Följaktligen kommer inget att hända och din senaste ändring kommer inte att ångras.
Det finns ett alternativ som gör att du kan behålla din Ångra-historik. Det här alternativet uppdaterar dock bara din pivottabell när arbetsboken öppnas, inte varje gång en ändring görs. Så här kan du använda det alternativet.
Från vilken cell som helst i din pivottabell:
- Gå till fliken Analysera i menyfliksområdet.
- Välj alternativknappen.
- Gå till fliken Data i det nya fönstret som öppnas.
- Markera rutan som säger ”Uppdatera data när filen öppnas.”
När du har klickat på OK kan du få följande varningsmeddelande om du har flera pivottabeller skapade från samma källdataområde. Klicka bara på OK för att komma igenom det.
Återigen, bara som en jämförelse, om du använder det här alternativet behåller du Ångra historik, men det uppdaterar bara pivottabellen när arbetsboken stängs och öppnas igen. Om du använder makroalternativet förlorar du Ångra historik, men pivottabellen upda automatiskt närhelst någon ändring görs i arbetsboken.
Variationer för att uppdatera pivottabeller
Makrot som vi tittade på uppdaterar inte bara dina pivottabeller utan uppdaterar också alla frågor . Om du bara vill uppdatera pivottabellerna kan du ersätta kommandot ”ThisWorkbook.RefreshAll” med den här koden istället:
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
Varje pivottabell är ansluten till en underliggande pivotcache, som är ansluten till källdata. Koden slingrar genom alla pivotcacher i arbetsboken och uppdaterar var och en. Pivottabeller från samma källområde kan dela pivotcacher, så den här metoden går snabbare än att gå igenom alla pivottabeller .
På samma sätt kan vi säga att du bara vill uppdatera en viss pivottabell. I så fall kan du byta ut ”ThisWorkbook.RefreshAll” -koden med koden nedan.
Och slutligen, om du använder Power Query och vill inaktivera bakgrundsuppdateringen så att frågor uppdateras INNAN pivottabellerna har jag skrivit en artikel för att förklara hur man gör det genom att inaktivera bakgrundsuppdateringen i frågorna.
Använd Inaktivera händelse istället
Ett annat alternativ är att använda Worksheet_Deactivate-händelsen istället för Worksheet_Change. Worksheet_Deactivate-händelsen körs varje gång användaren lämnar arket och väljer ett annat ark. Detta gör att användaren kan göra alla ändringar i källdata, då uppdateras pivottabellen automatiskt när de går till något annat ark, inklusive ark som innehåller pivottabellen.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Denna kod skulle fortfarande placeras i arkmodulen som innehåller källdata. Detta är ett bra alternativ om dina pivottabeller eller dataanslutningar tar en några sekunder eller längre för att uppdatera, och du vill inte vänta varje gång en ändring görs av källdata.
Den enda gången du kanske inte vill använda detta är om din pivottabell och källa uppgifterna finns på samma ark.Det är vanligtvis ett sällsynt fall, och något som jag i allmänhet inte rekommenderar.
Tack vare förslaget från Ted om detta.
Spara tid & Förlägenhet
Jag hoppas att den här artikeln sparar tid och gör det lättare för användare av dina filer. Det kan också hjälpa till att förhindra pinsamhet när du glömmer att uppdatera pivottabeller innan du skickar ut rapporter. Tro jag, jag har gjort detta misstag flera gånger än jag skulle vilja erkänna … 🙂
