Bundlinie: Lær, hvordan du bruger en simpel makro til automatisk at opdatere pivottabeller, hver gang der foretages ændringer i kildedataene. Jeg deler også en ikke-makro-løsning for at opdatere pivottabellerne, når filen åbnes. Inkluderer videovejledning og download af Excel-filer.
Færdighedsniveau: Mellemliggende
Download Excel-filen
Hvis du lærer bedst ved at gøre det alene, kan du download den fil, jeg bruger i videoen for at følge med. Her er Excel-filen, der indeholder VBA-koden.
Opdater pivottabel automatisk.xlsm (41,5 KB)
Opdater pivottabeller automatisk
Kan dine pivottabeller opdateres straks og automatisk, når deres kildedata ændres?
Absolut. Det kræver brug af en rigtig enkel makro, som jeg viser dig, hvordan du opretter nedenfor.
Hvis du ikke er alt for fortrolig med makroer og VBA, foreslår jeg at tjekke min gratis 3-delede videoserie om at komme i gang med makroer & VBA.
Også hvis du er ny med at dreje tabeller, Jeg har en serie, der fører dig igennem, hvad de er, og hvordan de bruges. Se den første video i den serie på pivottabeller & Dashboards
For automatisk at opdatere vores pivottabeller skal vi skrive en makro med en enkel instruktion. Denne instruktion siger dybest set: Når jeg foretager en ændring af mit regneark, skal du opdatere alle drejetabellerne og dataforbindelserne. Her er trinene til oprettelse af makroen.
Åbn Visual Basic Editor.
Du kan gøre dette ved at klikke på knappen Visual Basic på fanen Developer på båndet.
Tastaturgenvejen til åbning af Visual Basic-editoren er Alt + F11.
Hvis du ikke kan se under fanen Udvikler, kan du gøre det synligt ved hjælp af instruktionerne her. Du skal kun gøre dette en gang, og derefter vil fanen Udvikler altid være synlig hver gang du åbner Excel i fremtiden.
Åbn arkmodulet, der indeholder dine kildedata.
I vinduet Project Explorer i Visual Basic-editoren skal du finde den projektmappe, du vil ændre. Under denne projektmappe er arkene i projektmappen angivet. Vælg det ark, der indeholder kildedataene. Dobbeltklik derefter på det.
Hvis du ikke kan se Project Explorer-vinduet, kan du aktivere det fra visningen menu (tastaturgenvej: Ctrl + R).
Tilføj en ny begivenhed til regnearksændringer.
Dobbeltklik på arket åbner kodemodulet for det pågældende objekt. Inden for kodemodulet ønsker vi at oprette en begivenhedsmakro. For at gøre dette skal du vælge Arbejdsark i rullemenuen Objekt til venstre.
Det vil tilføje en regneark_SelectionChange-begivenhed til modulet, som vi faktisk ikke vil have, så vi sletter det om et øjeblik. Før vi gør det, lad os gå til rullemenuen Procedure til højre og vælge Skift.
Dette tilføjer en ny begivenhed øverst kaldet Worksheet_Change. Nu vil vi fremhæve og slette den unødvendige kode under den.
Arbejdsark_ændringshændelsesmakroen kører hver gang en ændring foretages til celler i dette regneark. Vi kan tilføje VBA-kode til Worksheet_Change-begivenheden for at udføre handlinger, når brugeren redigerer celler.
Bemærk: SelectionChange-begivenheden, der tilføjes som standard, kører, når som helst brugeren vælger en celle i arket. Da vi kun ønsker, at koden skal køre, når brugeren redigerer / ændrer celler, bruger vi ændringshændelsen. Tjek min artikel om VBA-kodemoduler & Sådan køres makroer baseret på brugerhændelser for at lære mere om arkmodulerne og begivenhederne.
Tilføj VBA-koden for at opdatere alle pivottabeller.
Derefter, lige under linjen Arbejdsark_ændring, skal du skrive denne instruktion:
ThisWorkbook.RefreshAll
RefreshAll-metoden opdaterer alle pivottabeller, forespørgsler og dataforbindelser i projektmappen. Denne handling er den samme som hvis du manuelt klikker på knappen Opdater på fanen Data.
Føj denne linje kode til begivenheden Worksheet_Change opdaterer projektmappen, hver gang der foretages en ændring i regnearket, som koden er in.
Pivottabel & Kildedata på samme ark
Aleksandrs stillede et stort spørgsmål til YouTube-videokommentarer. Hvis din pivottabel og kildedata er på det samme ark, skal du tilføje kode for at deaktivere begivenheder.
Opdateringen sætter begivenheden i en rekursiv sløjfe og kan ende med at gå ned i Excel. Her er koden for at forhindre det.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Kontrol for at sikre, at din makro kører
En måde at kontrollere, om makroen fungerer er at teste det. Foretag en ændring af kildedataene, og se om de afspejles i din pivottabel.Hvis din ændring ikke er let at få øje på, fordi du har for mange data eller af en anden grund, er der en anden måde at se, om din makro affyrer.
I VB-editoren kan du klikke på grå søjle lige til venstre for din Worksheet_Change-makro. Dette får en rød cirkel til at vises. Det fremhæver også denne kodelinje i rødt.
Dette kaldes et stop- eller breakpoint.
Tastaturgenvejen til at slå et brydepunkt til / fra er: F9
Nu når der opstår en handling, der udløser makroen, springer Excel til VB Editor og sætter makroen på pause, så du kan kontrollere koden. I vores tilfælde er denne handling enhver ændring, der foretages i regnearket.
Du kan derefter trykke på F8 for at gå gennem hver linje, eller tryk på F5 for at køre til slutningen (eller næste pausepunkt).
Hvis du foretager en ændring i regnearket, og Excel ikke trækker dig ind i VB Editor, ved du, at der er et problem med makroen kører ikke. Hvis dette er tilfældet, er det sandsynligt, at du ikke har gemt filen som en makroaktiveret projektmappe (.xlsm) og / eller aktiverede makroer. Du skal muligvis gemme & lukke filen, derefter åbne den igen og aktivere makroer.
For at fjerne det brudpunkt, du har placeret i makroen, Klik bare på den røde cirkel for at få den til at forsvinde (tastaturgenvej: F9).
Tastaturgenvejen for at rydde alle breakpoints er: Ctrl + Skift + F9
Opdatering af drejetabeller uden en Makro
En ulempe ved at bruge denne makro til at opdatere dine pivottabeller er, at fortrydelseshistorikken går tabt, hver gang makroen kører. Med andre ord, når du klikker på knappen Fortryd (eller trykker på Ctrl + Z), kan Excel ikke huske det sidste, du gjorde, så det kan ikke fortryde det. Derfor vil der ikke ske noget, og din sidste ændring fortrydes ikke.
Der er et alternativ, der giver dig mulighed for at beholde din fortrydelseshistorik. Dette alternativ opdaterer dog kun din pivottabel, når projektmappen åbnes, ikke hver gang der foretages en ændring. Sådan kan du bruge denne mulighed.
Startende fra en hvilken som helst celle i din pivottabel:
- Gå til fanen Analyser i båndet.
- Vælg knappen Indstillinger.
- Gå til fanen Data i det nye vindue, der åbnes.
- Marker afkrydsningsfeltet, der siger “Opdater data, når filen åbnes.”
Når du har klikket på OK, får du muligvis følgende advarselsmeddelelse, hvis du har oprettet flere drejetabeller fra det samme kildedataområde. Klik bare på OK for at komme igennem det.
Igen, bare som sammenligning, Hvis du bruger denne indstilling, bevarer du Fortryd historik, men den opdaterer kun pivottabellen, når projektmappen lukkes og genåbnes. Hvis du bruger makroindstillingen, mister du Fortryd historik, men pivottabellen automatisk upda når der foretages ændringer i projektmappen.
Variationer til opdatering af pivottabeller
Makroen, vi kiggede på, opdaterer ikke kun dine pivottabeller, men opdaterer også alle forespørgsler . Hvis du kun vil opdatere pivottabeller, kan du erstatte kommandoen “ThisWorkbook.RefreshAll” med denne kode i stedet:
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
Hver pivottabel er forbundet til en underliggende pivot-cache, som er forbundet til kildedataene. Koden løber gennem alle pivot-cacher i projektmappen og opdateres hver enkelt. Pivottabeller fra samme kildeområde kan dele pivot-caches, så denne metode er hurtigere end at løbe gennem alle pivottabeller .
Lad os ligeledes sige, at du kun vil opdatere en bestemt pivottabel. I så fald kan du bytte koden “ThisWorkbook.RefreshAll” med nedenstående kode.
Og endelig, hvis du bruger Power Query og vil deaktivere baggrundsopdateringen, så forespørgsler opdateres FØR pivottabeller, har jeg skrevet en artikel for at forklare, hvordan du gør det ved at deaktivere baggrundsopdateringen på forespørgslerne.
Brug Deaktiver begivenhed i stedet
En anden mulighed er at bruge begivenheden Arbejdsark_Deaktiver i stedet for Arbejdsark_ændring. Begivenheden Arbejdsark_Deaktiver kører hver gang brugeren forlader arket og vælger et andet ark. Dette gør det muligt for brugeren at foretage alle ændringer i kildedataene, så opdateres pivottabellen automatisk, når de går til et andet ark, inklusive de ark, der indeholder pivottabellen.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Denne kode ville stadig være placeret i arkmodulet, der indeholder kildedataene. Dette er en god mulighed, hvis dine drejetabeller eller dataforbindelser tager en få sekunder eller længere for at opdatere, og du vil ikke vente, hver gang der foretages en ændring af kildedataene.
Den eneste gang du måske ikke ønsker at bruge dette, er hvis din pivottabel og kilde data er på samme ark.Det vil normalt være et sjældent tilfælde, og noget jeg generelt ikke kan anbefale.
Takket være forslaget fra Ted om denne.
Spar tid & Forlegenhed
Jeg håber, at denne artikel hjælper dig med at spare tid og gør det lettere for brugere af dine filer. Det kan også hjælpe med at forhindre forlegenhed, når du glemmer at opdatere pivottabeller, inden du sender rapporter. mig, jeg har lavet denne fejl flere gange, end jeg gerne vil indrømme … 🙂
