I en tidligere artikel lærte vi, hvordan du dynamisk kan ændre og opdatere individuelle pivottabeller med krympende eller udvidende datakilder.
I denne artikel lærer vi, hvordan vi kan få alle pivottabeller i en projektmappe til automatisk at ændre datakilden. Med andre ord, i stedet for at ændre en pivottabel ad gangen, vil vi forsøge at ændre datakilden for alle pivottabellerne i projektmappen for dynamisk at inkludere nye rækker og kolonner, der er tilføjet til kildetabeller, og afspejle ændringen i pivottabeller med det samme.
Skriv kode i kildedatablad
Da vi ønsker, at dette skal være helt automatisk, vil vi bruge arkmoduler til at skrive kode i stedet for et kernemodul. Dette giver os mulighed for at bruge regnearkhændelser.
Hvis kildedataene og pivottabellerne er i forskellige ark, skriver vi VBA -koden for at ændre pivottabellens datakilde i det arkobjekt, der indeholder kildedataene (ikke der indeholder pivottabeller).
Tryk på CTRL+F11 for at åbne VB -editoren. Gå nu til project explorer og find det ark, der indeholder kildedataene. Dobbeltklik på den.
Et nyt kodningsområde åbnes. Du kan muligvis ikke se nogen ændring, men nu har du adgang til regnearkshændelser.
Klik på rullemenuen til venstre, og vælg regnearket. Vælg deaktiver fra venstre rullemenu. Du vil se en tom sub skrevet på kodeområdet navn worksheet_deativate. Vores kode til dynamisk ændring af kildedata og opdatering af pivottabellen kommer i denne kodeblok. Denne kode kører, når du skifter fra databladet til et andet ark. Du kan læse om alle regnearkbegivenheder her.
Nu er vi klar til at implementere koden.
Kildekode til dynamisk opdatering af alle pivottabeller i projektmappe med nyt område
For at forklare hvordan det fungerer, har jeg en projektmappe. Denne projektmappe indeholder tre ark. Sheet1 indeholder de kildedata, der kan ændres. Sheet2 og Sheet3 indeholder pivottabeller, som er afhængige af kildedataene for sheet2.
Nu har jeg skrevet denne kode i sheet1's kodningsområde. Jeg bruger hændelsen Worksheet_Deactivate, så denne kode kører for at opdatere pivottabellen, når vi skifter fra kildedatablad.
Private Sub Worksheet_Deactivate () Dim source_data As Range 'Bestemmelse af sidste række og kolonnenummer lstrow = Celler (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column 'Indstilling af det nye område Indstil source_data = Range (Celler (1, 1), Cells (lstrow, lstcol))' Kode til at gå gennem hvert ark og pivottabel For hver ws i ThisWorkbook.Worksheets For hver pt In ws.PivotTables pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Næste pt Næste ws End Sub
Hvis du har en lignende projektmappe, kan du direkte kopiere disse data. Jeg har forklaret, at denne kode fungerer nedenfor, så du kan ændre den efter dine behov.
Du kan se effekten af denne kode i gif nedenfor.
Hvordan ændrer denne kode automatisk kildedata og opdaterer pivottabeller?
Først og fremmest brugte vi en regneark_deaktiver hændelse. Denne hændelse udløses kun, når arket, der indeholder koden, skiftes eller deaktiveres. Så sådan kører koden automatisk.
For dynamisk at få hele tabellen som dataområde bestemmer vi den sidste række og sidste kolonne.
lstrow = Celler (rækker.Tælling, 1). Afslut (xlUp) .Række
lstcol = Celler (1, Columns.Count). End (xlToLeft) .Column
Ved hjælp af disse to tal definerer vi source_data. Vi er sikre på, at kildedataområdet altid starter fra A1. Du kan definere din egen begyndelsescellereference.
Angiv source_data = Range (celler (1, 1), celler (lstrow, lstcol))
Nu har vi kildedataene, som er dynamiske. Vi skal bare bruge det i pivottabellen.
Da vi ikke ved, hvor mange pivottabeller en projektmappe vil indeholde ad gangen, går vi igennem hvert ark og pivottabeller på hvert ark. Så der ikke er et drejebord tilbage. Til dette bruger vi indlejret til sløjfer.
For hver gang i ThisWorkbook.Worksheets
For hver pt In ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
SourceType: = xlDatabase, _
SourceData: = source_data)
Næste pt
Næste ws
Den første sløjfe går gennem hvert ark. Den anden sløjfe gentager sig over hver pivottabel i et ark.
Pivottabellerne er tildelt variabel pt. Vi bruger ChangePivotCache -metoden for pt -objekt. Vi opretter dynamisk en pivot -cache ved hjælp af ThisWorkbook.PivotCaches.Create
Metode. Denne metode tager to variabler SourceType og SourceData. Som kildetype erklærer vi xlDatabase, og som SourceData passerer vi source_data -område, som vi tidligere har beregnet.
Og det er det. Vi har vores drejeborde automatiseret. Dette opdaterer automatisk alle pivottabeller i projektmappen.
Så ja fyre, sådan kan du dynamisk ændre datakildeområder for alle pivottabellerne i en projektmappe i Excel. Jeg håber, jeg var forklarende nok. Hvis du har spørgsmål vedrørende denne artikel, så lad mig vide det i kommentarfeltet herunder.
Sådan dynamisk opdateres pivottabellens datakildeområde i Excel: For dynamisk at ændre kildedataområdet for pivottabeller bruger vi pivotcaches. Disse få linjer kan dynamisk opdatere enhver pivottabel ved at ændre kildedataområdet.
Sådan opdateres pivottabeller automatisk ved hjælp af VBA: For automatisk at opdatere dine pivottabeller kan du bruge VBA -begivenheder. Brug denne enkle kodelinje til automatisk at opdatere din pivottabel. Du kan bruge en af tre metoder til automatisk opdatering af pivottabeller.
Kør makro, hvis der foretages ændringer på ark i specificeret område: I din VBA -praksis får du behovet for at køre makroer, når et bestemt område eller en celle ændres. I så fald bruger vi ændringshændelsen til at køre makroer, når der foretages en ændring i et målområde.
Kør makro, når der foretages ændringer på ark | Så for at køre din makro, når arket opdateres, bruger vi regnearkhændelser i VBA.
Den enkleste VBA -kode til at fremhæve den aktuelle række og kolonne ved hjælp af | Brug dette lille VBA -udsnit til at fremhæve den aktuelle række og kolonne i arket.
Regnearket Begivenheder i Excel VBA | Regnearkshændelsen er virkelig nyttig, når du vil have dine makroer til at køre, når en bestemt hændelse forekommer på arket.
Populære artikler:
50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.VLOOKUP -funktionen i Excel | Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
COUNTIF i Excel 2016 | Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke filtrere dine data for at tælle specifik værdi. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.