Bottom Line: Lær hvordan du bruker en enkel makro til å oppdatere pivottabeller automatisk når endringer gjøres i kildedataene. Jeg deler også en ikke-makro-løsning for å oppdatere pivottabellene når filen åpnes. Inkluderer videoopplæring og nedlasting av Excel-filer.
Ferdighetsnivå: Mellomliggende
Last ned Excel-filen
Hvis du lærer best ved å gjøre det alene, kan du last ned filen jeg bruker i videoen for å følge med. Her er Excel-filen som inneholder VBA-koden.
Oppdater pivottabell automatisk.xlsm (41,5 KB)
Oppdater pivottabeller automatisk
Kan pivottabellene dine oppdateres umiddelbart og automatisk når kildedataene endres?
Absolutt. Det krever bruk av en veldig enkel makro som jeg vil vise deg hvordan du lager nedenfor.
Hvis du ikke er så kjent med makroer og VBA, foreslår jeg at du sjekker ut den gratis 3-delte videoserien min om å komme i gang med makroer & VBA.
Også, hvis du ikke har brukt pivottabeller, Jeg har en serie som viser deg hva de er og hvordan du bruker dem. Se den første videoen i den serien på Pivot Tables & Dashboards
For automatisk å oppdatere pivottabellene våre, skal vi skrive en makro med en enkel instruksjon. Denne instruksjonen sier i utgangspunktet: Når jeg endrer regnearket, oppdaterer du alle pivottabellene og datatilkoblingene. Her er trinnene for å lage makroen.
Åpne Visual Basic Editor.
Du kan gjøre dette ved å klikke på Visual Basic-knappen i kategorien Developer på båndet.
Hurtigtasten for å åpne Visual Basic-redigereren er Alt + F11.
Hvis du ikke ser i kategorien Utvikler, kan du gjøre den synlig ved hjelp av instruksjonene her. Du trenger bare å gjøre dette en gang, og deretter vil utviklerfanen alltid være synlig hver gang du åpner Excel i fremtiden.
Åpne arkmodulen som inneholder kildedataene dine.
I prosjektutforskervinduet til Visual Basic-redigereren, finn arbeidsboken du vil endre. Under den arbeidsboken er oppført arkene i arbeidsboken. Velg arket som inneholder kildedataene. Dobbeltklikk deretter på den.
Hvis du ikke ser Project Explorer-vinduet, kan du aktivere det fra View meny (hurtigtast: Ctrl + R).
Legg til en ny hendelse for regnearkendringer.
Dobbeltklikk på arket åpner kodemodulen for det objektet. Innen kodemodulen ønsker vi å lage en hendelsesmakro. For å gjøre det, velg Arbeidsark i rullegardinboksen Objekt til venstre.
Det vil legge til en arbeidsark_Seleksjonsendringshendelse til modulen, som vi egentlig ikke vil ha, så vi vil slette den på bare et øyeblikk. Før vi gjør det, la oss gå til rullegardinmenyen Prosedyre til høyre og velge Endre.
Dette legger til en ny hendelse øverst kalt Worksheet_Change. Nå vil vi markere og slette den unødvendige koden under den.
Hendelsesmakroen for Worksheet_Change vil kjøre når som helst endring blir gjort til celler i regnearket. Vi kan legge til VBA-kode i Worksheet_Change-hendelsen for å utføre handlinger når brukeren redigerer celler.
Merk: SelectionChange-hendelsen som er lagt til som standard vil kjøre når som helst brukeren velger en celle i arket. Siden vi bare vil at koden skal kjøre når brukeren redigerer / endrer celler, bruker vi Endringshendelsen. Sjekk artikkelen min om VBA-kodemoduler & Slik kjører du makroer basert på brukerhendelser for å lære mer om arkmodulene og hendelsene.
Legg til VBA-koden for å oppdatere alle pivottabeller.
Deretter skriver du inn denne instruksjonen, rett under Worksheet_Change-linjen:
ThisWorkbook.RefreshAll
RefreshAll-metoden vil oppdatere alle pivottabellene, spørringene og datatilkoblingene i arbeidsboken. Denne handlingen er den samme som om du manuelt klikker på Oppdater-knappen i kategorien Data.
Legg til denne kodelinjen i Worksheet_Change-hendelsen vil oppdatere arbeidsboken hver gang det gjøres en endring i regnearket som koden er i.
Pivottabell & Kildedata på samme ark
Aleksandrs stilte et stort spørsmål på YouTube-videokommentarene. Hvis pivottabellen og kildedataene er på samme ark, må du legge til kode for å deaktivere hendelser.
Oppdateringen setter hendelsen i en rekursiv sløyfe, og kan ende opp med å krasje Excel. Her er koden for å forhindre det.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Kontrollerer at makroen din kjører
En måte å sjekke om makroen fungerer er å teste det. Gjør en endring i kildedataene og se om de gjenspeiles i pivottabellen.Hvis endringen ikke er lett å få øye på fordi du har for mye data, eller av en annen grunn, er det en annen måte å se om makroen din skyter ut.
I VB-redigereren kan du klikke på grå kolonne rett til venstre for Worksheet_Change-makroen. Dette vil få en rød sirkel til å vises. Den fremhever også linjen med kode i rødt.
Dette kalles et stopp- eller brytepunkt.
Tastaturgenveien for å slå et avbrekkpunkt på / av er: F9
Nå når en handling oppstår som utløser makroen, hopper Excel til VB Editor og stopper makroen slik at du kan sjekke koden. I vårt tilfelle er denne handlingen en hvilken som helst endring i regnearket.
Du kan deretter trykke F8 for å gå gjennom hver linje, eller trykk på F5 for å løpe til slutten (eller neste brytpunkt).
Hvis du gjør en endring i regnearket og Excel ikke trekker deg inn i VB Editor, vet du at det er et problem med makroen kjører ikke. Hvis dette er tilfelle, er det sannsynlig at du ikke har lagret filen som en makroaktivert arbeidsbok (.xlsm) og / eller aktiverte makroer. Du må kanskje lagre & lukke filen, deretter åpne den på nytt og aktivere makroer.
Hvis du vil fjerne bruddpunktet du har plassert på makroen, bare klikk på den røde sirkelen for å få den til å forsvinne (hurtigtast: F9).
Hurtigtasten for å fjerne alle brytepunkter er: Ctrl + Shift + F9
Oppdatering av pivottabeller uten Makro
En ulempe med å bruke denne makroen til å oppdatere pivottabellene, er at eventuell Angre-historie går tapt hver gang makroen kjører. Med andre ord, når du klikker på Angre-knappen (eller trykker på Ctrl + Z), husker Excel ikke det siste du gjorde, så det kan ikke angre det. Følgelig vil ingenting skje, og den siste endringen din vil ikke bli angret.
Det er et alternativ som lar deg beholde historien om angre. Dette alternativet oppdaterer imidlertid bare pivottabellen når arbeidsboken åpnes, ikke hver gang det gjøres en endring. Slik kan du bruke dette alternativet.
Starter fra hvilken som helst celle i pivottabellen:
- Gå til fanen Analyser i båndet.
- Velg alternativknappen.
- Gå til Data-fanen i det nye vinduet som åpnes.
- Merk av i boksen som sier «Oppdater data når du åpner filen.»
Etter å ha klikket OK, kan du få følgende advarsel hvis du har flere pivottabeller opprettet fra samme kildedataområde. Bare klikk OK for å komme gjennom den.
Igjen, bare for sammenligning, Hvis du bruker dette alternativet, beholder du Angre historikk, men det oppdaterer bare pivottabellen når arbeidsboken lukkes og åpnes på nytt. Hvis du bruker makroalternativet, mister du Angre historie, men pivottabellen automatisk upda når som helst endring i arbeidsboken.
Variasjoner for oppdatering av pivottabeller
Makroen vi så på, vil ikke bare oppdatere pivottabellene dine, men vil også oppdatere alle spørsmål også . Hvis du bare vil oppdatere pivottabeller, kan du erstatte kommandoen «ThisWorkbook.RefreshAll» med denne koden 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 pivottabell er koblet til en underliggende pivotbuffer, som er koblet til kildedataene. Koden går gjennom alle pivotbuffer i arbeidsboken og oppdaterer hver enkelt. Pivottabeller fra samme kildeområde kan dele pivotbuffer, så denne metoden er raskere enn å gå gjennom alle pivottabeller .
På samme måte, la oss si at du bare vil oppdatere en bestemt pivottabell. I så fall kan du bytte ut «ThisWorkbook.RefreshAll» -koden med koden nedenfor.
Og til slutt, hvis du bruker Power Query og vil deaktivere bakgrunnsoppdateringen slik at spørsmål blir oppdatert FØR pivottabeller, har jeg skrevet en artikkel for å forklare hvordan du gjør det ved å deaktivere bakgrunnsoppdateringen på spørsmålene.
Bruk Deaktiver hendelse i stedet
Et annet alternativ er å bruke Worksheet_Deactivate event i stedet for Worksheet_Change. Worksheet_Deactivate-hendelsen vil kjøre hver gang brukeren forlater arket og velger et annet ark. Dette gjør at brukeren kan gjøre alle endringene i kildedataene, da blir pivottabellen automatisk oppdatert når de går til et annet ark, inkludert arkene som inneholder pivottabellen.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Denne koden vil fortsatt være plassert i arkmodulen som inneholder kildedataene. Dette er et godt alternativ hvis pivottabellene eller datatilkoblingene tar en noen sekunder eller lenger for å oppdatere, og du vil ikke vente hver gang det gjøres en endring i kildedataene.
Den eneste gangen du kanskje ikke vil bruke dette, er hvis pivottabellen og kilden din er data er på samme ark.Det vil vanligvis være et sjeldent tilfelle, og noe jeg generelt ikke anbefaler.
Takk til forslaget fra Ted om denne.
Spar tid & Forlegenhet
Jeg håper denne artikkelen hjelper deg med å spare tid og gjør det lettere for brukere av filene dine. Det kan også bidra til å forhindre forlegenhet når du glemmer å oppdatere pivottabeller før du sender ut rapporter. Tro meg, jeg har gjort denne feilen flere ganger enn jeg vil innrømme… 🙂
