Als je deze tutorial aan het lezen bent, is de kans groot dat je de Excel Pivot Table hebt gehoord (of zelfs hebt gebruikt). Het is een van de krachtigste functies in Excel (geen grapje).
Het beste van het gebruik van een draaitabel is dat zelfs als je niets weet in Excel, je er nog steeds behoorlijk geweldige dingen mee kunt doen met een heel basaal begrip ervan.
Laten we beginnen.
Klik hier om de voorbeeldgegevens te downloaden en te volgen.
Wat is een draaitabel en waarom zou het u iets kunnen schelen?
Een draaitabel is een tool in Microsoft Excel waarmee je snel grote datasets kunt samenvatten (met een paar klikken).
Zelfs als u helemaal nieuw bent in de wereld van Excel, kunt u eenvoudig een draaitabel gebruiken. Het is net zo eenvoudig als het slepen en neerzetten van rijen/kolommen om rapporten te maken.
Stel dat u een dataset heeft zoals hieronder weergegeven:
Dit zijn verkoopgegevens die uit ~1000 rijen bestaan.
Het heeft de verkoopgegevens per regio, type verkoper en klant.
Nu wil je baas misschien een paar dingen uit deze gegevens weten:
- Wat was de totale omzet in de regio Zuid in 2016?
- Wat zijn de top vijf retailers op basis van omzet?
- Hoe verhouden de prestaties van The Home Depot zich tot andere retailers in het Zuiden?
U kunt doorgaan en Excel-functies gebruiken om u de antwoorden op deze vragen te geven, maar wat als uw baas plotseling met een lijst met nog vijf vragen komt.
U moet elke keer dat er een wijziging is, teruggaan naar de gegevens en nieuwe formules maken.
Dit is waar Excel-draaitabellen echt van pas komen.
Binnen enkele seconden zal een draaitabel al deze vragen beantwoorden (zoals je hieronder zult leren).
Maar het echte voordeel is dat het uw kieskeurige datagestuurde baas tegemoet kan komen door zijn vragen onmiddellijk te beantwoorden.
Het is zo eenvoudig dat je net zo goed een paar minuten de tijd kunt nemen om je baas te laten zien hoe hij het zelf moet doen.
Hopelijk heb je nu een idee waarom draaitabellen zo geweldig zijn. Laten we doorgaan en een draaitabel maken met behulp van de gegevensset (hierboven weergegeven).
Een draaitabel invoegen in Excel
Hier zijn de stappen om een draaitabel te maken met behulp van de hierboven getoonde gegevens:
- Klik ergens in de dataset.
- Ga naar Invoegen -> Tabellen -> Draaitabel.
- In het dialoogvenster Draaitabel maken werken de standaardopties in de meeste gevallen prima. Hier zijn een paar dingen om in te checken:
- Tafel/Bereik: Het wordt standaard ingevuld op basis van uw dataset. Als uw gegevens geen lege rijen/kolommen hebben, identificeert Excel automatisch het juiste bereik. U kunt dit indien nodig handmatig wijzigen.
- Als u de draaitabel op een specifieke locatie wilt maken, geeft u onder de optie 'Kies waar u het draaitabelrapport wilt plaatsen' de locatie op. Anders wordt een nieuw werkblad gemaakt met de draaitabel.
- Klik OK.
Zodra u op OK klikt, wordt een nieuw werkblad gemaakt met daarin de draaitabel.
Terwijl de draaitabel is gemaakt, ziet u er geen gegevens in. Het enige dat u ziet, is de naam van de draaitabel en een instructie van één regel aan de linkerkant, en draaitabelvelden aan de rechterkant.
Voordat we beginnen met het analyseren van gegevens met behulp van deze draaitabel, laten we eerst eens kijken wat de moeren en bouten zijn die een Excel-draaitabel maken.
De moeren en bouten van een Excel-draaitabel
Om een draaitabel efficiënt te gebruiken, is het belangrijk om de componenten te kennen die een draaitabel maken.
In dit gedeelte leer je over:
- Draaicache
- Waardengebied
- Rijengebied
- Kolommengebied
- Filtergebied
Draaicache
Zodra je een draaitabel maakt met de gegevens, gebeurt er iets in de backend. Excel maakt een momentopname van de gegevens en slaat deze op in het geheugen. Deze snapshot wordt de Pivot Cache genoemd.
Wanneer u verschillende weergaven maakt met behulp van een draaitabel, gaat Excel niet terug naar de gegevensbron, maar gebruikt het de draaicache om de gegevens snel te analyseren en u de samenvatting/resultaten te geven.
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 van de gegevens. U kunt items in de rijen/kolommen/waarden/filtervakken slepen en neerzetten en de resultaten worden onmiddellijk bijgewerkt.
Opmerking: een nadeel van de 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.
Lees verder: Wat is Pivot Cache en hoe u het het beste kunt gebruiken.
Waardengebied
Het Waardengebied is wat de berekeningen/waarden bevat.
Op basis van de gegevensset die aan het begin van de zelfstudie wordt getoond, kunt u, als u snel de totale verkoop per regio in elke maand wilt berekenen, een draaitabel krijgen zoals hieronder weergegeven (we zullen later in de zelfstudie zien hoe u dit kunt maken) .
Het oranje gemarkeerde gebied is het waardengebied.
In dit voorbeeld heeft het de totale verkoop in elke maand voor de vier regio's.
Rijengebied
De koppen aan de linkerkant van het gebied Waarden maken het gebied Rijen.
In het onderstaande voorbeeld bevat het gebied Rijen de regio's (rood gemarkeerd):
Kolommengebied
De koppen bovenaan het gebied Waarden maken het gebied Kolommen.
In het onderstaande voorbeeld bevat het gebied Kolommen de maanden (rood gemarkeerd):
Filtergebied
Het gebied Filters is een optioneel filter dat u kunt gebruiken om verder in te zoomen op de gegevensset.
Als u bijvoorbeeld alleen de verkopen voor Multiline-retailers wilt zien, kunt u die optie selecteren in de vervolgkeuzelijst (gemarkeerd in de onderstaande afbeelding), en de draaitabel wordt dan alleen bijgewerkt met de gegevens voor Multiline-retailers.
Gegevens analyseren met behulp van de draaitabel
Laten we nu proberen de vragen te beantwoorden met behulp van de draaitabel die we hebben gemaakt.
Klik hier om de voorbeeldgegevens te downloaden en te volgen.
Als u gegevens wilt analyseren met behulp van een draaitabel, moet u beslissen hoe u wilt dat de gegevenssamenvatting eruitziet in het eindresultaat. U wilt bijvoorbeeld alle regio's aan de linkerkant en de totale verkoop ernaast. Zodra u deze duidelijkheid in gedachten heeft, kunt u de relevante velden eenvoudig slepen en neerzetten in de draaitabel.
In het gedeelte Draaitabbladvelden hebt u de velden en de gebieden (zoals hieronder gemarkeerd):
De velden worden gemaakt op basis van de backend-gegevens die worden gebruikt voor de draaitabel. In het gedeelte Gebieden plaatst u de velden en afhankelijk van waar een veld naartoe gaat, worden uw gegevens bijgewerkt in de draaitabel.
Het is een eenvoudig mechanisme voor slepen en neerzetten, waarbij u eenvoudig een veld kunt slepen en in een van de vier gebieden kunt plaatsen. Zodra je dit doet, verschijnt het in de draaitabel in het werkblad.
Laten we nu proberen de vragen van uw manager te beantwoorden met behulp van deze draaitabel.
Q1: Wat waren de totale verkopen in de regio Zuid?
Sleep het veld Regio in het gebied Rijen en het veld Opbrengst in het gebied Waarden. Het zou automatisch de draaitabel in het werkblad bijwerken.
Houd er rekening mee dat zodra u het veld Opbrengst in het gebied Waarden laat vallen, het Som van Opbrengst wordt. Standaard telt Excel alle waarden voor een bepaalde regio op en toont het totaal. Als u wilt, kunt u dit wijzigen in Aantal, Gemiddelde of andere statistieken. In dit geval is de som wat we nodig hadden.
Het antwoord op deze vraag zou 21225800 zijn.
Q2 Wat zijn de top vijf retailers op basis van omzet?
Sleep het veld Klant in het gebied Rij en het veld Opbrengst in het waardengebied. Als er nog andere velden in de gebiedssectie zijn en u deze wilt verwijderen, selecteert u deze eenvoudig en sleept u ze eruit.
U krijgt een draaitabel zoals hieronder weergegeven:
Merk op dat de items (in dit geval de klanten) standaard in alfabetische volgorde worden gesorteerd.
Om de top vijf retailers te krijgen, kunt u deze lijst eenvoudig sorteren en de vijf beste klantnamen gebruiken. Om dit te doen:
- Klik met de rechtermuisknop op een cel in het gebied Waarden.
- Ga naar Sorteren -> Sorteer van groot naar klein.
Dit geeft u een gesorteerde lijst op basis van de totale verkoop.
Vraag 3: Hoe verhouden de prestaties van The Home Depot zich tot andere retailers in het Zuiden?
U kunt veel analyses doen voor deze vraag, maar laten we hier gewoon proberen de verkopen te vergelijken.
Sleep het veld Regio in het gebied Rijen. Sleep nu het veld Klant in het gebied Rijen onder het veld Regio. Wanneer u dit doet, zou Excel begrijpen dat u uw gegevens eerst per regio en vervolgens per klanten binnen de regio's wilt categoriseren. Je hebt iets zoals hieronder wordt weergegeven:
Sleep nu het veld Opbrengst naar het gebied Waarden en je hebt de verkopen voor elke klant (evenals de algemene regio).
U kunt de retailers sorteren op basis van de verkoopcijfers door de onderstaande stappen te volgen:
- Klik met de rechtermuisknop op een cel met de verkoopwaarde voor een detailhandelaar.
- Ga naar Sorteren -> Sorteer van groot naar klein.
Dit zou alle retailers onmiddellijk sorteren op verkoopwaarde.
Nu kunt u snel door de regio Zuid bladeren en vaststellen dat de verkoop van The Home Depot 3004600 was en het beter deed dan vier retailers in de regio Zuid.
Nu zijn er meer dan één manieren om de kat te villen. U kunt de regio ook in het filtergebied plaatsen en dan alleen de zuidelijke regio selecteren.
Klik hier om de voorbeeldgegevens te downloaden.
Ik hoop dat deze tutorial je een basisoverzicht geeft van Excel-draaitabellen en je helpt om ermee aan de slag te gaan.
Hier zijn nog enkele zelfstudies voor draaitabellen die u misschien leuk vindt:
- Brongegevens voorbereiden voor draaitabel.
- Voorwaardelijke opmaak toepassen in een draaitabel in Excel
- Hoe datums te groeperen in draaitabellen in Excel
- Nummers groeperen in draaitabel in Excel
- Gegevens filteren in een draaitabel in Excel
- Slicers gebruiken in Excel-draaitabel.
- Lege cellen vervangen door nullen in Excel-draaitabellen
- Hoe een Excel-draaitabel berekende velden toe te voegen en te gebruiken
- Hoe de draaitabel in Excel te vernieuwen
