Hoe kan ik het bereik van een draaitabel automatisch bijwerken?

Het bijwerken van een draaitabel is lastig, nietwaar? Als u regelmatig draaitabellen in uw werk gebruikt, weet ik zeker dat u zich hierin kunt vinden.

Het punt is dat elke keer dat u nieuwe gegevens aan het bronblad toevoegt, u het bronbereik van de draaitabel moet bijwerken voordat u uw draaitabel vernieuwt.

Stel je nu voor dat als je elke dag gegevens aan je bronblad toevoegt, je het bronbereik elke dag moet bijwerken.

En elke keer is het veranderen van het bereik van de draaitabel een puinhoop. Ja, dat klopt. Hoe vaker u gegevens toevoegt, hoe vaker u het bronbereik moet bijwerken.

Het punt is dus dat je een methode nodig hebt om het bronbereik automatisch bij te werken wanneer je nieuwe gegevens toevoegt.

OPMERKING : Draaitabellen maken deel uit van INTERMEDIATE EXCEL SKILLS .

Pas tabel toe om het draaitabelbereik automatisch bij te werken

Een paar dagen geleden vroeg ik John Michaloudis naar zijn draaitabeltip van een miljoen dollar. Er staat: Zet uw brongegevens in een tabel. Geloof me, dit is een fooi van een miljoen dollar.

Door een tabel in de brongegevens toe te passen, hoeft u het bronbereik van uw draaitabel niet steeds opnieuw te wijzigen.

Elke keer dat u nieuwe gegevens toevoegt, wordt het draaitabelbereik automatisch bijgewerkt.

Converteer gegevens naar een tabel voordat u een draaitabel maakt

Elke keer voordat u een draaitabel maakt, moet u de tabel toepassen op de brongegevens met behulp van de volgende stappen.

1. Selecteer een van de cellen in uw gegevens.
2. Gebruik de sneltoets Ctrl + T of navigeer naar → Tabblad invoegen → Tabellen → Tabel.
3. U krijgt een pop-up met uw huidige gegevensbereik.
   
4. Klik OK.
5. Om nu een draaitabel te maken, selecteert u een willekeurige cel in uw gegevens. Ga naar → tabblad Ontwerp → Extra → Samenvatten met draaitabel.
   
6. Klik OK.

Wanneer u nu nieuwe gegevens aan uw gegevensblad toevoegt, wordt het draaitabelbereik automatisch bijgewerkt en hoeft u alleen maar uw draaitabel te vernieuwen.

Converteer gegevens naar een tabel nadat u een draaitabel heeft gemaakt

Als u al een draaitabel in uw werkblad heeft, kunt u de volgende stappen gebruiken om uw gegevensbron naar een tabel te converteren.

1. Selecteer een van de cellen in uw gegevensbron.
2. Gebruik de sneltoets Ctrl + T of navigeer naar → Tabblad invoegen → Tabellen → Tabel.
3. U krijgt een pop-up met uw huidige gegevensbereik.
4. Klik OK.
5. Selecteer nu een van de cellen in uw draaitabel en ga naar → Analyseren → Gegevens → Gegevensbron bewerken → Gegevensbron bewerken (vervolgkeuzemenu).
6. U krijgt een pop-up waarin u uw gegevensbron opnieuw kunt selecteren, of u kunt ook de tabelnaam invoeren in de bereikinvoer.
   
7. Klik OK.

Vanaf nu wordt, elke keer dat u nieuwe gegevens aan uw bronblad toevoegt, het bereik van de draaitabel vergroot, zodat dit automatisch wordt bijgewerkt.

Creëer een dynamisch draaitabelbereik met de OFFSET-functie

De andere beste manier om het draaitabelbereik automatisch bij te werken, is door een dynamisch bereik te gebruiken.

Het dynamische bereik kan automatisch groter worden telkens wanneer u nieuwe gegevens aan uw bronblad toevoegt. Hier zijn de stappen om een dynamisch bereik te creëren.

1. Ga naar → Tabblad Formules → Gedefinieerde namen → Naambeheer.
2. Zodra u op de naambeheerder klikt, krijgt u een pop-upvenster.
   
3. Klik in uw Name Manager-venster op Nieuw om een benoemd bereik te maken.
4. Voer in uw nieuwe naamvenster in
   1. Een naam voor uw nieuwe assortiment. Ik gebruik de naam “SourceData”.
   2. Geef de omvang van het bereik op. U kunt opgeven tussen het huidige werkblad of de werkmap.
   3. Voeg een opmerking toe om uw benoemde bereik te beschrijven. Voer de onderstaande formule in de invoerbalk ‘Verwijzen naar’ in.
      

       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. Klik aan het einde op OK.

Nu hebt u een dynamisch bereik om een draaitabel te maken.

Het enige dat u hoeft te doen, is eenvoudigweg een draaitabel maken met uw brongegevens en vervolgens de bron met het benoemde bereik wijzigen met behulp van dezelfde methode die ik gebruikte in de eerste tabellenmethode.

Nadat u nieuwe gegevens aan uw bronblad heeft toegevoegd, vernieuwt u eenvoudig uw draaitabel.

Hoe werkt deze formule?

In de bovenstaande formule heb ik de offsetfunctie gebruikt om een dynamisch bereik te creëren.

Ik noemde cel A1 als uitgangspunt en zonder rijen en kolommen te vermelden, specificeerde ik de hoogte en breedte van het bereik met behulp van COUNTA.

COUNTA telt cellen met waarden uit kolom A en rij 1 en vertelt offset om de hoogte en breedte dienovereenkomstig uit te breiden.

Het enige waar u op moet letten is dat er geen lege cel tussen kolom A en rij 1 staat.

Update de draaitabel met behulp van VBA-code

De meeste mensen gebruiken graag VBA-codes. Dus hier is de code die u moet gebruiken om het draaitabelbereik bij te werken met VBA.

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

Dingen die u moet wijzigen voordat u deze in uw werkmap gebruikt.

1. Regel 13: Wijzig de naam van het bronwerkblad.
2. Regel 14: Wijzig de naam van het draaitabelblad.
3. Regel 17: Wijzig de naam van de draaitabel.

Als u nog steeds problemen ondervindt bij het gebruik van deze code, schrijf mij dan in het opmerkingenveld. Laat me u nu laten zien hoe deze code werkt, zodat u deze eenvoudig kunt aanpassen aan uw behoeften.

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

In het bovenstaande deel van de code hebben we de draaitabel- en werkbladvariabelen van de brongegevens gespecificeerd. U kunt de spreadsheetnaam hier wijzigen.

 PivotName = "PivotTable2"

Voer in het bovenstaande deel van de code de naam in van de draaitabel waarop u deze code wilt gebruiken.

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

Het bovenstaande deel van de code creëert een dynamisch bereik met behulp van cel A1 van het gegevensbronwerkblad.

Het controleert de laatste kolom en de laatste rij met gegevens om een dynamisch bereik te creëren. Elke keer dat u deze macro uitvoert, wordt er een nieuw dynamisch bereik gecreëerd.

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

Het bovenstaande deel van de code vernieuwt de draaitabel en geeft een bericht aan de gebruiker weer dat de draaitabel nu is bijgewerkt.