In dit artikel leren we hoe u draaitabelgegevens automatisch ververst in Excel.
Scenario:
Zoals we allemaal weten, wordt dit niet onmiddellijk weergegeven in de draaitabel wanneer we wijzigingen aanbrengen in de brongegevens van een draaitabel. We moeten de draaitabellen vernieuwen om de wijzigingen te zien wanneer we een Excel-werkmap openen. En als u een bijgewerkt bestand verzendt zonder de draaitabellen te vernieuwen, kunt u zich in verlegenheid brengen. Dus leer hier hoe u de vernieuwingsoptie kunt vinden bij het gebruik van de draaitabel
Gegevens vernieuwen bij het openen van een bestand in Excel
Maak eerst een draaitabel en klik vervolgens met de rechtermuisknop op een draaitabelcel.
Ga naar Draaitabelopties > tabblad Gegevens > Vink het vakje aan met de tekst Gegevens vernieuwen bij het openen van een bestand
Hierdoor worden gegevens automatisch bijgewerkt wanneer het bestand wordt geopend.
Voorbeeld :
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we eens kijken hoe we de functie kunnen gebruiken aan de hand van een voorbeeld. Hier hebben we wat gegevens en we moeten eerst een draaitabel maken en vervolgens de opties vinden om draaitabellen automatisch bijwerken in te schakelen.
Maak een draaitabel en klik vervolgens met de rechtermuisknop op een draaitabelcel, zoals hieronder wordt weergegeven.
Selecteer de Draaitabel-opties en dit zal een dialoogvenster Draaitabel-opties openen.
Selecteer het gegevenstabblad en vink vervolgens het vakje aan met de tekst Gegevens vernieuwen bij het openen van het bestand. U kunt dit doen zonder het bestand te openen en te sluiten met VBA.
VBA gebruiken
Dus hier leren we hoe we een draaitabel automatisch kunnen vernieuwen met VBA. Deze manier is makkelijker dan je had gedacht.
Dit is de eenvoudige syntaxis om draaitabellen in de werkmap automatisch te vernieuwen.
| 'Code in brongegevensbladobject'
Privé subwerkblad_Deactivate() bladnaam_van_pivot_table.Draaitabellen("pivot_table_name").PivotCache.Refresh Einde sub |
Wat zijn Pivot-caches?
Elke draaitabel slaat de gegevens op in de pivot-cache. Dit is de reden waarom pivot eerdere gegevens kan weergeven. Wanneer we draaitabellen vernieuwen, wordt de cache bijgewerkt met nieuwe brongegevens om de wijzigingen in de draaitabel weer te geven.
We hebben dus alleen een macro nodig om de cache van draaitabellen te vernieuwen. We zullen dit doen met behulp van een werkbladgebeurtenis, zodat we de macro niet handmatig hoeven uit te voeren.
Waar te coderen om draaitabellen automatisch te vernieuwen?
Als uw brongegevens en draaitabellen zich in verschillende bladen bevinden, moet de VBA-code in het brongegevensblad staan.
Hier zullen we Worksheet_SelectionChange Event gebruiken. Hierdoor wordt de code uitgevoerd wanneer we overschakelen van het brongegevensblad naar een ander blad. Ik zal later uitleggen waarom ik deze gebeurtenis heb gebruikt.
Hier heb ik brongegevens in blad2 en draaitabellen in blad1.
Open VBE met de CTRL+F11-toets. In de projectverkenner ziet u drie objecten, Blad1, Blad2 en de Werkmap.
Aangezien Sheet2 de brongegevens bevat, dubbelklikt u op het sheet2-object.
Nu ziet u twee vervolgkeuzelijsten bovenaan het codegebied. Selecteer het werkblad in de eerste vervolgkeuzelijst. En selecteer in de tweede vervolgkeuzelijst Deactiveren. Hiermee wordt een lege subnaam Worksheet_Deactivate ingevoegd. Onze code zal in deze sub worden geschreven. Alle regels die in deze sub zijn geschreven, worden uitgevoerd zodra de gebruiker van dit blad naar een ander blad overschakelt.
Op blad1 heb ik twee draaitabellen. Ik wil slechts één draaitabel vernieuwen. Daarvoor moet ik de naam van de draaitabel weten. Om de naam van een draaitabel te weten, selecteert u een cel in die draaitabel en gaat u naar het tabblad Analyse van draaitabel. Aan de linkerkant ziet u de naam van de draaitabel. U kunt hier ook de naam van de draaitabel wijzigen.
Nu we de naam van de draaitabel kennen, kunnen we een eenvoudige regel schrijven om de draaitabel te vernieuwen.
| Privé subwerkblad_Deactivate()
Blad1.PivotTables("PivotTable1").PivotCache.Refresh Einde sub |
En het is gedaan.
Wanneer u nu van de brongegevens overschakelt, wordt deze vba-code uitgevoerd om de draaitabel1 te vernieuwen. Zoals je kunt zien in de gif hieronder.
Hoe alle draaitabellen in de werkmap te vernieuwen?
In het bovenstaande voorbeeld wilden we slechts één specifieke draaitabel vernieuwen. Maar als u alle draaitabellen in een werkmap wilt vernieuwen, hoeft u slechts kleine wijzigingen in uw code aan te brengen.
| Privé subwerkblad_Deactivate()
'Blad1.PivotTables("PivotTable1").PivotCache.Refresh Voor elke pc in ThisWorkbook.PivotCaches pc.Vernieuwen Volgende pc Einde sub |
In deze code gebruiken we een For-lus om door elke pivot-cache in de werkmap te lopen. Het ThisWorkbook-object bevat alle pivot-caches. Om toegang te krijgen gebruiken we ThisWorkbook.PivotCaches.
Waarom Worksheet_Deactivate Event gebruiken?
Als u de draaitabel wilt vernieuwen zodra er een wijziging in de brongegevens is aangebracht, moet u de gebeurtenis Worksheet_Change gebruiken. Maar ik raad het niet aan. Het zorgt ervoor dat uw werkmap de code uitvoert telkens wanneer u een wijziging in het blad aanbrengt. Mogelijk moet u honderden wijzigingen aanbrengen voordat u het resultaat wilt zien. Maar Excel ververst de draaitabel bij elke wijziging. Dit zal leiden tot verspilling van verwerkingstijd en middelen. Dus als je draaitabellen en gegevens in verschillende bladen hebt, is het beter om Worksheet Deactivate Event te gebruiken. Hiermee kunt u uw werk afmaken. Zodra u overschakelt naar draaitabelbladen om de wijzigingen te zien, worden de wijzigingen gewijzigd.
Als u draaitabellen en brongegevens op hetzelfde blad heeft en u wilt dat draaitabellen zichzelf automatisch vernieuwen, kunt u Worksheet_Change Event gebruiken.
| Private Sub Worksheet_Change (ByVal-doel als bereik)
Blad1.PivotTables("PivotTable1").PivotCache.Refresh Einde sub |
Hoe alles in werkmappen vernieuwen wanneer er een wijziging wordt aangebracht in brongegevens?
Als u alles in een werkmap (grafieken, draaitabellen, formules, enz.) wilt vernieuwen, kunt u de opdracht ThisWorkbook.RefreshAll gebruiken.
| Private Sub Worksheet_Change (ByVal-doel als bereik)
ThisWorkbook.RefreshAll Einde sub |
Opmerking : De code verandert niets aan de gegevensbron. Dus als u gegevens toevoegt onder de brongegevens, bevat deze code die gegevens niet automatisch. U kunt Excel-tabellen gebruiken om brongegevens op te slaan. Als u geen tabellen wilt gebruiken, kunnen we VBA ook gebruiken om nieuwe gegevens op te nemen. We zullen het leren in de volgende tutorial.
Ik hoop dat dit artikel over het automatisch vernieuwen van draaitabelgegevens in Excel verklarend is. Vind hier meer artikelen over het berekenen van waarden en gerelateerde Excel-formules. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite.
Dynamisch bijwerken van het gegevensbronbereik van de draaitabel in Excel : Om het brongegevensbereik van draaitabellen dynamisch te wijzigen, gebruiken we draaicaches. Deze paar regels kunnen elke draaitabel dynamisch bijwerken door het brongegevensbereik te wijzigen. Gebruik in VBA draaitabellenobjecten zoals hieronder weergegeven …
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 als u wilt dat uw macro's worden uitgevoerd wanneer een opgegeven gebeurtenis op het werkblad plaatsvindt.
Populaire artikels :
50 Excel-snelkoppelingen om uw productiviteit te verhogen : Werk sneller aan uw taken in Excel. Met deze sneltoetsen kunt u uw werkefficiëntie in Excel vergroten.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : 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.
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
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.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.