✅ Pivot Cache in Excel - Wat is het en hoe kan je het het beste gebruiken?

Inhoudsopgave

Wat is draaicache?
Bijwerkingen in cache draaien
De draaicache delen
Dubbele draaicache maken (met dezelfde gegevensbron)
Tel het aantal draaicaches
Prestaties verbeteren tijdens het werken met draaitabellen

Als u met Excel-draaitabellen werkt, is Pivot Cache iets dat u zeker moet weten.

Wat is draaicache?

Draaicache is iets dat automatisch wordt gegenereerd wanneer u een draaitabel maakt.

Het is een object dat een replica van de gegevensbron bevat. Hoewel u het niet kunt zien, maakt het deel uit van de werkmap en is het verbonden met de draaitabel. Wanneer u wijzigingen aanbrengt in de draaitabel, gebruikt deze niet de gegevensbron, maar de draaitabel.

De reden dat een pivot-cache wordt gegenereerd, is om de werking van de draaitabel te optimaliseren. Zelfs als je duizenden rijen gegevens hebt, is een draaitabel supersnel in het samenvatten ervan. U kunt items in de rijen/kolommen/waarden/filtervakken slepen en neerzetten en de resultaten worden onmiddellijk bijgewerkt.

Pivot Cache maakt deze snelle werking van een draaitabel mogelijk.

Hoewel u denkt dat u rechtstreeks aan de brongegevens bent gekoppeld, krijgt u in werkelijkheid toegang tot de draaitabel (en niet de brongegevens) wanneer u wijzigingen aanbrengt in de draaitabel.

Dit is ook de reden dat u de draaitabel moet vernieuwen om eventuele wijzigingen in de gegevensset weer te geven.

Bijwerkingen in cache draaien

Een nadeel van pivot-cache is dat het de grootte van uw werkmap vergroot.

Omdat het een replica is van de brongegevens, wordt bij het maken van een draaitabel een kopie van die gegevens opgeslagen in de draaitabel.

Wanneer u grote gegevenssets gebruikt om een draaitabel te maken, neemt de bestandsgrootte van de werkmap aanzienlijk toe.

De draaicache delen

Als u vanaf Excel 2007 al een draaitabel heeft en u een extra draaitabel maakt met dezelfde brongegevens, deelt Excel automatisch de draaitabellen (wat betekent dat beide draaitabellen dezelfde draaitabellen gebruiken). Dit is handig omdat het duplicatie van de pivot-cache voorkomt en op zijn beurt resulteert in minder geheugengebruik en kleinere bestandsgrootte.

Beperkingen van gedeelde draaicache

Hoewel een gedeelde pivot-cache de werking van de draaitabel en het geheugengebruik verbetert, heeft het de volgende beperkingen:

Wanneer u één draaitabel vernieuwt, worden alle draaitabellen die aan dezelfde cache zijn gekoppeld, vernieuwd.
Wanneer u velden groepeert in een van de draaitabellen, wordt deze toegepast op alle draaitabellen met dezelfde draaitabellen. Als u bijvoorbeeld datums per maand groepeert, wordt deze wijziging doorgevoerd in alle draaitabellen.
Wanneer u een berekend veld/item in een van de draaitabellen invoegt, wordt het weergegeven in alle draaitabellen die de draaitabellen delen.

De manier om deze beperkingen te omzeilen is om Excel te dwingen een aparte draaitabellen te maken voor verschillende draaitabellen (terwijl dezelfde gegevensbron wordt gebruikt).

Opmerking: als u verschillende gegevensbronnen voor verschillende draaitabellen gebruikt, genereert Excel er automatisch afzonderlijke draaicaches voor.

Dubbele draaicache maken (met dezelfde gegevensbron)

Hier zijn 3 manieren om dubbele pivot-cache te maken terwijl u draaitabellen maakt vanuit dezelfde gegevensbron:

#1 Verschillende tabelnamen gebruiken

Klik ergens in de gegevensbron en ga naar Invoegen -> Tabel (of je kunt de sneltoets gebruiken - Control + T).
Klik in het dialoogvenster Tabel maken op OK. Het zal een tabel maken met de naam Table1.
Met een cel geselecteerd in de tabel, ga naar Invoegen -> Draaitabel.
In het dialoogvenster Draaitabel maken ziet u dat in het veld Tabel/Bereik de naam van de tabel staat. Klik OK.
- Hiermee wordt de eerste draaitabel gemaakt.
Ga naar de gegevensbron (tabel), selecteer een cel en ga naar Table Tools Design -> Tools -> Converteren naar bereik. Er wordt een prompt weergegeven met de vraag of u de tabel naar het normale bereik wilt converteren. Klik op Ja. Hierdoor wordt de tabel omgezet in gewone tabelgegevens.

Herhaal nu de bovenstaande stappen en verander gewoon de tabelnaam (van tabel1 in tabel2 of wat u maar wilt). U kunt dit wijzigen door de naam in te voeren in het veld onder Tabelnaam op het tabblad Ontwerpen van hulpmiddelen voor tabellen.

Hoewel beide tabellen (Tabel1 en Tabel2) naar dezelfde gegevensbron verwijzen, zorgt deze methode ervoor dat er voor elke tabel twee afzonderlijke draaicaches worden gegenereerd.

#2 De wizard Oude draaitabel gebruiken

Gebruik deze stappen wanneer u een extra draaitabel wilt maken met een aparte draaitabel terwijl u dezelfde gegevensbron gebruikt.

Selecteer een willekeurige cel in de gegevens en druk op ALT + D + P.
- Hiermee wordt de wizard Draaitabel en draaigrafiek geopend.
Klik in Stap 1 van 3 op Volgende.
Controleer in stap 2 van 3 of het gegevensbereik correct is en klik op Volgende.
Excel toont een prompt die in wezen zegt: klik op Ja om een gedeelde pivot-cache te maken en Nee om een aparte pivot-cache te maken.
Klik op Nee.
Selecteer in stap 3 van de wizard of u de draaitabel in een nieuw werkblad of hetzelfde werkblad wilt hebben en klik vervolgens op Voltooien.

Opmerking: zorg ervoor dat de gegevens geen Excel-tabel zijn.

Tel het aantal draaicaches

Misschien wilt u het aantal pivot-caches tellen om meerdere pivot-caches van dezelfde gegevensbron te vermijden.

Hier is een snelle manier om het te tellen:

Druk op ALT + F11 om de VB Editor te openen (of ga naar het tabblad Ontwikkelaar -> Visual Basic).
Klik in het Visual Basic Editor-menu op Beeld en selecteer Direct venster (of druk op Control + G). Hierdoor wordt het onmiddellijke venster zichtbaar.
Plak de volgende code in het onmiddellijke venster en druk op Enter:
```
?ActiveWorkbook.PivotCaches.Count
```

Het toont direct het aantal Pivot Caches in de werkmap.

Prestaties verbeteren tijdens het werken met draaitabellen

Er zijn een aantal dingen die u kunt doen om de prestaties van werkmappen (bestandsgrootte en geheugengebruik) te verbeteren terwijl u met draaitabellen werkt:

#1 Verwijder de brongegevens

U kunt de brongegevens verwijderen en alleen de draaicache gebruiken. U kunt nog steeds alles doen met behulp van de pivot-cache omdat deze een momentopname van de originele gegevens bevat. Maar aangezien u de brongegevens hebt verwijderd, wordt de bestandsgrootte van uw werkmap kleiner.

Als u de brongegevens wilt terugkrijgen, dubbelklikt u eenvoudig op het snijpunt van eindtotalen voor die draaitabel. Er wordt een nieuw werkblad gemaakt en alle gegevens weergegeven die zijn gebruikt om die draaitabel te maken.

#2 Sla de gegevens niet op in draaicache

Wanneer u een bestand opslaat met een draaitabel en brongegevens, slaat het ook de draaicache op die een kopie van de brongegevens bevat. Dit betekent dat u de brongegevens op twee plaatsen opslaat: in het werkblad met de gegevens en in de spilcache.

Er is een optie om de gegevens niet in de cache op te slaan en te sluiten. Dit leidt tot een kleinere bestandsgrootte.

Om dit te doen:

Selecteer een cel in de draaitabel.
Ga naar Analyseren -> Draaitabel -> Opties.
Ga in het dialoogvenster Opties draaitabel naar het tabblad Gegevens.
Schakel de optie - Brongegevens opslaan met bestand uit.
Vink de optie - Gegevens vernieuwen aan bij het openen van het bestand.
- Als u deze optie niet aanvinkt, worden de gegevens niet vernieuwd wanneer u de Excel-werkmap opent en kunt u de draaitabelfuncties niet gebruiken. Om het te laten werken, moet u de draaitabel handmatig vernieuwen.

Wanneer u dit doet, slaat Excel de gegevens niet op in de pivot-cache, maar wordt deze vernieuwd wanneer u de Excel-werkmap de volgende keer opent. Uw gegevens kunnen zich in dezelfde werkmap, een andere werkmap of een externe database bevinden. Wanneer u het bestand opent, worden de gegevens vernieuwd en wordt de draaicache opnieuw gemaakt.

Hoewel dit kan leiden tot een kleinere bestandsgrootte, kan het wat langer duren om het bestand te openen (omdat Excel de cache opnieuw aanmaakt).

Zie ook: Brongegevens opslaan met draaitabel.

Opmerking: als u deze optie gebruikt, zorg er dan voor dat de gegevensbron intact is. Als u de brongegevens verwijdert (uit de werkmap of een externe gegevensbron), kunt u de pivot-cache niet opnieuw maken.

#3 De Pivot Cache delen voor betere prestaties

Als je per ongeluk (of opzettelijk) in een situatie terechtkomt waarin je een dubbele pivot-cache hebt en je de duplicaat wilt verwijderen en de pivot-cache wilt delen, zijn hier de stappen om dit te doen:

Verwijder een van de draaitabellen waarvan u de cache wilt verwijderen. Selecteer hiervoor de draaitabel en ga naar Home -> Wissen -> Alles wissen.
Kopieer nu eenvoudig de draaitabel die u wilt dupliceren en plak deze (in hetzelfde werkblad of in een apart werkblad).
- Het wordt aanbevolen om het in afzonderlijke werkbladen te plakken, zodat het niet overlapt met de andere draaitabel wanneer u deze uitvouwt. Hoewel ik het soms naast elkaar kopieer om verschillende weergaven te vergelijken. Dit kopiëren en plakken van de draaitabel zorgt ervoor dat de pivot-cache wordt gedeeld.