Momenteel kunnen we draaitabellen dynamisch wijzigen of bijwerken met behulp van Excel-tabellen of dynamische benoemde bereiken. Maar deze technieken zijn niet onfeilbaar. Omdat je de draaitabel nog steeds handmatig moet vernieuwen. Als u grote gegevens hebt die duizenden rijen en kolommen bevatten, zullen Excel-tabellen u niet veel helpen. In plaats daarvan wordt uw bestand zwaar. Dus de enige manier blijft VBA.
In dit artikel zullen we leren hoe we onze draaitabel automatisch de gegevensbron kunnen laten wijzigen. Met andere woorden, we automatiseren het handmatige proces van het wijzigen van de gegevensbron om dynamisch nieuwe rijen en kolommen op te nemen die aan brontabellen zijn toegevoegd en de wijziging in de draaitabel onmiddellijk weer te geven.
Schrijf code in brongegevensblad
Omdat we willen dat dit volledig automatisch gaat, zullen we bladmodules gebruiken om code te schrijven in plaats van een kernmodule. Hierdoor kunnen we werkbladgebeurtenissen gebruiken.
Als de brongegevens en draaitabellen zich in verschillende bladen bevinden, zullen we de VBA-code schrijven om de draaitabelgegevensbron te wijzigen in het bladobject dat de brongegevens bevat (niet dat de draaitabel bevat).
Druk op CTRL+F11 om de VB-editor te openen. Ga nu naar de projectverkenner en zoek het blad met de brongegevens. Dubbelklik erop.
Er wordt een nieuw coderingsgebied geopend. Mogelijk ziet u geen verandering, maar nu hebt u toegang tot de werkbladgebeurtenissen.
Klik op het linker vervolgkeuzemenu en selecteer het werkblad. Selecteer in het linker vervolgkeuzemenu deactiveren. U ziet een lege sub geschreven op de naam van het codegebied worksheet_deativate. Onze code voor het dynamisch wijzigen van brongegevens en het vernieuwen van de draaitabel komt in dit codeblok. Deze code wordt uitgevoerd wanneer u van het gegevensblad naar een ander blad overschakelt. U kunt hier alle werkbladgebeurtenissen lezen.
Nu zijn we klaar om de code te implementeren.
Broncode om draaitabel dynamisch bij te werken met nieuw bereik
Om uit te leggen hoe het werkt, heb ik een werkboek. Dit werkboek bevat twee bladen. Blad1 bevat de brongegevens die kunnen veranderen. Sheet2 bevat de draaitabel die afhankelijk is van de brongegevens van sheet2.
Nu heb ik deze code in het coderingsgebied van blad1 geschreven. Ik gebruik de Worksheet_Deactivate-gebeurtenis, zodat deze code wordt uitgevoerd om de draaitabel bij te werken wanneer we overschakelen van het brongegevensblad.
Private Sub Worksheet_Deactivate() Dim pt As PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Cells(Rows.Count, 1).End(xlUp).Rij lstcol = Cells(1, Columns.Count).End(xlToLeft). Kolom Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data) Set pt = Sheet2.PivotTables("PivotTable1") pt.ChangePivotCache pc Einde Sub
Als je een vergelijkbare werkmap hebt, kun je deze gegevens direct kopiëren. Ik heb uitgelegd dat deze code hieronder werkt.
Je kunt het effect van deze code in gif hieronder zien.
Hoe verandert deze code automatisch brongegevens en werkt draaitabellen bij?
Allereerst hebben we een worksheet_deactivate-gebeurtenis gebruikt. Deze gebeurtenis wordt alleen geactiveerd wanneer het blad met de code wordt omgeschakeld of gedeactiveerd. Dus zo wordt de code automatisch uitgevoerd.
Om nu de brongegevens van de draaitabel te wijzigen, wijzigen we de gegevens in de draaicache.
Een draaitabel wordt gemaakt met behulp van de pivot-cache. De pivot-cache bevat de oude brongegevens totdat de draaitabel niet handmatig wordt vernieuwd of het brongegevensbereik handmatig wordt gewijzigd.
We hebben referenties gemaakt van draaitabellen met de naam pt, pivot-cache met de naam pc en een bereik met de naam source_data. De brongegevens zullen de volledige gegevens bevatten.
Om de hele tabel dynamisch als gegevensbereik te krijgen, bepalen we de laatste rij en laatste kolom.
lstrow = Cellen(Rijen.Aantal, 1).Einde(xlOmhoog).Rij
lstcol = Cellen(1, Kolommen.Aantal).End(xlNaarLinks).Kolom
Met behulp van deze twee getallen definiëren we de source_data. We zijn er zeker van dat het brongegevensbereik altijd vanaf A1 zal beginnen.
Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))
Nu hebben we de brongegevens die dynamisch zijn. We hoeven het alleen in de draaitabel te gebruiken.
We slaan deze gegevens op in de pivot-cache, omdat we weten dat de pivot-cache alle gegevens opslaat.
Stel pc in = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Vervolgens definiëren we de draaitabel die we willen bijwerken. Omdat we de draaitabel1 (naam van de draaitabel. U kunt de naam van de draaitabel in het tabblad Analyseren terwijl u de draaitabel selecteert, controleren) op blad 1, stellen we pt in zoals hieronder weergegeven.
Stel pt = Blad2.PivotTables("PivotTable1") in
Nu gebruiken we eenvoudig deze pivot-cache om de draaitabel bij te werken. We gebruiken de changePivotCache-methode van het pt-object.
pt.ChangePivotCache pc
En we hebben onze draaitabel geautomatiseerd. Hiermee wordt uw draaitabel automatisch bijgewerkt. Als u meerdere tabellen met dezelfde gegevensbron heeft, gebruikt u gewoon dezelfde cache in elk draaitabelobject.
Dus ja jongens, dit is hoe je het gegevensbronbereik in Excel dynamisch kunt wijzigen. Ik hoop dat ik voldoende uitleg heb gegeven. Als je vragen hebt over dit artikel, laat het me dan weten in de comments hieronder.
Draaitabellen automatisch vernieuwen met VBA: Om uw draaitabellen automatisch te vernieuwen, kunt u VBA-gebeurtenissen gebruiken. Gebruik deze eenvoudige regel code om uw draaitabel automatisch bij te werken. U kunt een van de drie methoden gebruiken om draaitabellen automatisch te vernieuwen.
Voer macro uit als er een wijziging is aangebracht op het blad in het opgegeven bereik: In uw VBA-praktijken zou u de noodzaak krijgen om macro's uit te voeren wanneer een bepaald bereik of cel verandert. In dat geval gebruiken we de change-gebeurtenis om macro's uit te voeren wanneer een wijziging in een doelbereik wordt aangebracht.
Macro uitvoeren wanneer er een wijziging op het blad wordt aangebracht | Dus om uw macro uit te voeren wanneer het blad wordt bijgewerkt, gebruiken we de werkbladgebeurtenissen van VBA.
Eenvoudigste VBA-code om huidige rij en kolom te markeren met behulp van | Gebruik dit kleine VBA-fragment om de huidige rij en kolom van het blad te markeren.
De werkbladgebeurtenissen in Excel VBA | De werkbladgebeurtenis is erg handig wanneer u wilt dat uw macro's worden uitgevoerd wanneer een opgegeven gebeurtenis op het blad plaatsvindt.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.De VERT.ZOEKEN-functie in Excel | Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
AANTAL.ALS in Excel 2016 | Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om een specifieke waarde te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
