Draaitabellen zijn een van de krachtigste functies in Excel. Zelfs als u een nieuweling bent, kunt u grote hoeveelheden gegevens omzetten in bruikbare informatie. Met draaitabel kunt u binnen enkele minuten rapporten maken. Analyseer uw schone gegevens eenvoudig en als de gegevens niet schoon zijn, kan het u helpen uw gegevens op te schonen. Ik wil je niet vervelen, dus laten we erin springen en het verkennen.
Een draaitabel maken
Het is makkelijk. Selecteer gewoon uw gegevens. Ga naar Invoegen. Klik op de draaitabel en het is klaar.
Maar wacht. Voordat u een draaitabel maakt, moet u ervoor zorgen dat alle kolommen een kop hebben.
Als een kolomkop leeg wordt gelaten, wordt de draaitabel niet gemaakt en wordt er een foutmelding weergegeven.
Vereiste 1: Alle kolommen moeten een kop hebben om aan de slag te gaan met draaitabellen in Excel
U moet uw gegevens ordenen met de juiste kop. Zodra u deze hebt, kunt u de draaitabel invoegen.
De draaitabel van lint invoegen
Volg deze stappen om een draaitabel vanuit het menu in te voegen:
1. Selecteer uw gegevensbereik
2. Ga naar het tabblad Invoegen
3. Klik op het draaitabelpictogram.
4. Maak een keuzelijst voor een draaitabel verschijnt.
5. Hier ziet u het gegevensbereik dat u hebt geselecteerd. Als u denkt dat dit niet het bereik is dat u wilt selecteren, wijzigt u het bereik direct vanaf hier, in plaats van terug te gaan en gegevens opnieuw te selecteren.
6. Vervolgens kunt u selecteren waar u uw draaitabel wilt hebben. Ik raad aan om het nieuwe werkblad te gebruiken, maar je kunt ook het huidige werkblad gebruiken. Definieer gewoon de locatie in het vak Locatie.
7. Als u nu klaar bent met de instellingen, drukt u op de knop OK. U krijgt uw draaitabel in een nieuw blad. Selecteer gewoon uw velden voor samenvattingen. We zullen zien hoe we een samenvatting van gegevens maken met behulp van de draaitabel, maar laten we eerst de basis duidelijk maken. In deze Excel draaitabel-tutorial leert u meer dan u verwacht.
Sneltoets draaitabel invoegen (Alt > N > V)
Dit is een sequentiële sneltoets om de Create . te openen Draaitabel optie doos.
Druk op de Alt-knop en laat deze los. Raken N en laat het los. Raken V en laat het los. Het optievenster Een draaitabel maken wordt geopend.
Volg nu gewoon de bovenstaande procedure om een draaitabel in Excel te maken.
Draaitabelsnelkoppeling invoegen met oude Excel-snelkoppeling (Alt> D> P)
Een ding dat ik het leukst vind aan Microsoft Excel, is dat ze in elke nieuwe versie van Excel nieuwe functies introduceren, maar ze gooi de oude functies niet weg (zoals MS deed met win 8. Het was zielig). Dit stelt de oude gebruikers in staat om normaal te werken aan nieuwe versies zoals ze vroeger aan oudere versies werkten.
Als u achtereenvolgens op drukt ALT, D en P op het toetsenbord opent Excel een draaitabelwizard.
Selecteer de juiste optie. De geselecteerde optie in de bovenstaande schermafbeelding zal ons ertoe brengen een draaitabel te maken zoals we eerder hebben gemaakt.
Druk op Enter of klik op Volgende als u uw geselecteerde bereik wilt controleren.
Druk nogmaals op Enter.
Selecteer een nieuw werkblad of waar u maar wilt dat uw draaitabel op Enter drukt. En het is klaar.
Rapporten maken met draaitabellen
Nu weet je hoe je een draaitabel invoegt. Laten we ons voorbereiden om binnen enkele minuten rapporten te maken met draaitabellen.
We hebben de gegevens voor de stationaire volgorde.
De kolomvelden zijn:
Besteldatum: Datum van bestelling (uiteraard)
Regio: De regio van orde in het land
Klant: Naam van de klant (wat kan het anders zijn)
Item: Besteld artikel
Eenheid: Aantal eenheden van een besteld artikel
Kosten per eenheid: Kosten per eenheid
Totaal: Totale kosten van de bestelling (eenheid*eenheidskosten).
Om rapporten te maken met behulp van draaitabellen zullen we gebruiken
Draaitabelvelden: Bevat de lijst met namen van kolommen in uw gegevens.
Draaigebieden: Deze 4 gebieden worden gebruikt om uw gegevens op een gemanierde manier te tonen.
FILTERS: Plaats hier velden waarvan u filters in uw rapport wilt toepassen.
KOLOMMEN: Zet hier de velden die u wilt in uw kolommen in het rapport: (Het is beter om te tonen dan uit te leggen)
RIJEN: Sleep velden die u onbewerkt wilt weergeven, zoals in de bovenstaande afbeelding, ik heb Regio in RIJEN getoond.
WAARDEN: Selecteer een veld om het aantal, de som, het gemiddelde, het percentage (en nog veel meer) enz. te krijgen die u wilt zien.
Met behulp van de bovenstaande informatie hebben we dit snelle draairapport opgesteld dat laat zien uit welke regio hoeveel bestellingen voor elk artikel zijn geplaatst.
Nu u uw gegevens en draaitabel begrijpt (u bent tenslotte slim), laten we enkele vragen met betrekking tot deze gegevens snel beantwoorden met behulp van de draaitabel.
Q1. Hoeveel bestellingen zijn er?
De draaitabel is aanvankelijk leeg, zoals weergegeven in de onderstaande afbeelding.
U moet velden (kolomnamen) in de juiste gebieden selecteren om de samenvatting of details van dat veld te zien.
Om de bovenstaande vraag te beantwoorden, selecteer ik Item (selecteer een willekeurige kolom, zorg er alleen voor dat er geen lege cel tussen staat) in waardevelden.
Selecteer Item in de lijst met velden en sleep het naar het veld Waarde.
We hebben ons antwoord. Draaitabellen vertellen me dat er in totaal 43 bestellingen zijn. Dit is correct.
Nu ik heb geselecteerd Besteldatum in het gebied Waarden wordt weergegeven: 42. Dit middelen Besteldatum heeft een lege cel sinds we weten dat het totale aantal bestellingen is: 43.
Identificeer onregelmatige gegevens met behulp van draaitabellen en ruim deze op.
Draaitabel kan u helpen om onjuiste informatie in de gegevens te vinden.
Meestal worden onze gegevens opgesteld door operators voor gegevensinvoer of door gebruikers die meestal onregelmatig zijn en enige opschoning nodig hebben om nauwkeurige rapporten en analyses op te stellen.
U moet uw gegevens altijd op een gemanierde manier opschonen en voorbereiden voordat u rapporten opstelt. Maar soms komen we pas na het opstellen van het rapport te weten dat onze gegevens enige onregelmatigheid bevatten. Kom, ik zal je laten zien hoe…
Q2: Vertel het aantal bestellingen van elke regio
Om nu deze vraag te beantwoorden:
Selecteer Regio en sleep het naar rijen Gebied en Item tot Waarden Gebied.
We krijgen een Region-Wise verdeelde gegevens. Uit welke regio kunnen wij antwoorden hoeveel bestellingen er zijn gekomen.
Er zijn in totaal 4 regio's in onze gegevens volgens de draaitabel. Maar wacht, merk op dat Centraal en Midden regio. We weten dat Centre zou moeten zijn Centraal. Er is een onregelmatigheid. We moeten naar onze gegevens gaan en gegevens opschonen.
Om gegevens in het regioveld op te schonen, filteren we de onjuiste regionaam (Centrle) en corrigeren deze (Central).
Ga nu terug naar uw spilgegevens.
Klik met de rechtermuisknop ergens in de draaitabel en klik op Vernieuwen.
Uw rapport is nu bijgewerkt.
Nu kun je zien dat er in feite maar 3 regio's zijn.
Draairapportopmaak met gecategoriseerde rijen.
Soms heeft u rapporten zoals de bovenstaande afbeelding nodig. Dit maakt het gemakkelijk om uw gegevens op een gestructureerde manier in te zien. U kunt eenvoudig zien uit welke regio hoeveel artikelen er worden besteld. Laten we eens kijken hoe u dit kunt doen.
Beweging Regio en Item tot RIJEN Oppervlakte. Zorg ervoor dat de regio bovenaan staat en items onderaan, zoals weergegeven in de afbeelding.
Sleuren Item voor Waarde Gebied.
Als resultaat krijgt u dit rapport.
Het zal doen. Maar soms wil je baas rapporteren in tabelvorm zonder subtotalen. Om dit te doen, moeten we onze draaitabel opmaken.
Subtotalen verwijderen uit de draaitabel
Volg deze stappen:
1. Klik ergens in uw draaitabel.
2. Ga naar de Ontwerp Tabblad.
3. Klik op de subtotalen menu.
4. Klik op Subtotalen niet weergeven.
U kunt zien dat er nu geen subtotalen zijn.
Prima. Maar het is nog steeds niet in tabelvorm. Regio's en items worden in één enkele kolom weergegeven. Laat ze apart zien.
Een draaitabeltabel maken
Om nu regio's en items in verschillende kolommen weer te geven, volgt u deze stappen:
1. Klik ergens in de draaitabel
2. Ga naar het tabblad Ontwerpen
3. Klik op Rapportindeling.
4. Klik op Tonen voor de optie Tabelvorm. Eindelijk heeft u deze verfijnde weergave van uw rapport.
Nu weten we het totale aantal bestellingen dat voor elk artikel uit elke regio is geplaatst. Het wordt getoond in de Count van Items kolommen.
Wijzig de kolomnaam in Bestellingen.
Het ziet er nu beter uit.
Q3: Hoeveel eenheden van elk artikel worden besteld?
Om deze vraag te beantwoorden hebben we een som van Eenheden nodig. Verplaats hiervoor het veld Eenheden naar Waarden. Het zal automatisch het aantal eenheden voor elk item optellen. Als een kolom in de draaitabel alleen waarden bevat, toont de draaitabel standaard de som van die waarden. Maar het kan worden gewijzigd vanuit een waardeveldinstelling. Hoe? Dat laatste laat ik je zien.
Instellingen voor veldwaarden van draaitabel
Vraag 4: De gemiddelde prijs van elk item?
In onze voorbeeldgegevens is de prijs van één artikel verschillend voor verschillende bestellingen. Zie bijvoorbeeld Bindmiddelen.
Ik wil de gemiddelde kosten van elk item in de draaitabel weten. Om dit te weten te komen, sleept u Kostprijs per eenheid naar Waardeveld. Het toont de som van de eenheidskosten.
We willen niet Som van eenheidskosten, wij willen Gemiddelde van eenheidskosten. Om dit te doen…
1. Klik met de rechtermuisknop ergens op de som van de kolom Kostprijs in de draaitabel
2. Klik op Waarde Veld Instellingen
3. Op basis van de beschikbare opties, selecteer Gemiddeld en druk op OK.
Ten slotte heb je dit draairapport:
Draaitabel berekende velden
Een van de handigste functies van de draaitabel zijn de berekende velden. Berekende velden zijn velden die worden verkregen door sommige bewerkingen op beschikbare kolommen.
Laten we eens kijken hoe u berekende velden in de draaitabel kunt invoegen met één voorbeeld:
Op basis van onze gegevens hebben we dit rapport opgesteld.
Hier hebben we Som van eenheden en Totale prijs. Ik heb zojuist de totale kolom naar het veld Waarden verplaatst en deze hernoemd naar Totale prijs. Nu wil ik de gemiddelde prijs van een eenheid van elk artikel voor elke regio weten. En dat zou zijn:
Gemiddelde prijs = totale kosten / totale eenheden
Laten we een veld invoegen in de draaitabel dat de gemiddelde prijs van elk item per regio weergeeft:
Volg deze stappen om een berekend veld in de draaitabel in te voegen
1. Klik ergens op de draaitabel en ga naar het tabblad Analyseren
2. Klik op Velden, Artikelen & Sets in de berekeningsgroep.
3. Klik op Berekende velden.
U ziet dit invoervak voor uw rekenveld:
4. Schrijf in het invoervak voor de naam de gemiddelde kosten of wat u maar wilt, Excel zal het niet erg vinden. Schrijf in het formule-invoervak en druk op OK.
=Totaal / Eenheden
U kunt dit handmatig vanaf het toetsenbord schrijven of u kunt dubbelklikken op de veldnamen in het gebied Velden om bewerkingen uit te voeren.
5. Je hebt je berekende veld nu toegevoegd aan je draaitabel. Het wordt genoemd als de som van de gemiddelde kosten, maar het is geen som. Excel voert alleen de standaardfunctie uit om de kolom een naam te geven (zoals een ritueel). Hernoem deze kolom en beperk de getoonde decimale cijfers.
En daar heb je een berekend veld. Je kunt het zo ingewikkeld maken als je zelf wilt. Omwille van een voorbeeld nam ik deze eenvoudige gemiddelde operatie.
Groeperen in de draaitabel
U heeft dit spilrapport opgesteld.
Nu wil ik dat dit rapport jaarlijks wordt verdeeld. Zie de momentopname hieronder.
We hebben een kolom op Besteldatum. Verplaats het veld OrderDate naar Rijen bovenaan.
Het lijkt in niets op het vereiste rapport. We moeten het hele jaar door groepsdata hebben.
Om nu een veld in Excel-draaitabel te groeperen, volgt u deze stappen:
1. Klik met de rechtermuisknop op het veld dat u wilt groeperen.
2. Klik op Groep. U heeft deze optiebox voor maatwerk. Aangezien dit een gegevenskolom is, toont Excel ons de groepering dienovereenkomstig. U kunt uw start- en einddatum kiezen.
3. Kies voor jaren en druk op OK. U hebt jaarlijks gegroepeerd in de draaitabel van Excel.
Snijmachines van draaitabel
Slicers zijn in Excel 2010 geïntroduceerd als een invoegtoepassing. In Excel 2013 en 2016 is het standaard beschikbaar, net als filters.
Slicers zijn niets anders dan filters. In tegenstelling tot filters toont Slicers alle beschikbare opties recht voor je. Het maakt uw dashboard interactiever.
Slicers toevoegen in draaitabel, Excel 2016 en 2013
Pivot Table Slicers is eenvoudig toe te voegen. Volg deze stappen:
1. Klik ergens in de draaitabel en ga naar het tabblad Analyseren.
2. Klik op Slicer invoegen. U krijgt een lijst met velden voor uw gegevens. Selecteer er zoveel als je wilt.
3. Selecteer in dit voorbeeld Regio en druk op OK.
U heeft Slicer aan uw rapport toegevoegd. Pas nu het filter toe met slechts één klik.
Tijdlijn invoegen in Excel 2016 en 2013
Dit is een van mijn favoriete Excel-draaitabellen met functionaliteit. Deze nieuwe functionaliteit werkt alleen met datums. Hiermee kunt u visueel een tijdspanne selecteren om uw gegevens te filteren.
Volg deze stappen om een tijdlijncurser in te voegen:
Tijdlijn toevoegen in draaitabel, Excel 2016 en 2013
1. Klik ergens in de draaitabel en ga naar het tabblad Analyseren.
2. Klik op Tijdlijn invoegen vanuit filtergroep. Alle kolommen met tijdwaarden in brongegevens worden weergegeven in een keuzevak waaruit u kunt kiezen. Hier hebben we er maar één. Dus ja…
2. Kies uw opties en druk op Enter of klik op OK. Het is klaar en je hebt de tijdlijn van je draaitabel recht voor je.
U kunt ervoor kiezen om het dagelijks, maandelijks, driemaandelijks of jaarlijks weer te geven. Ik heb hier Maandelijks gekozen.
In dit artikel heb ik de belangrijkste en handigste functionaliteiten van draaitabel behandeld. We hebben nieuwe functies van draaitabel in Excel 2016 en 2013 verkend. We leerden over het klassieke gebruik van een draaitabel die werd uitgevoerd in Excel 2007, 2010 en ouder. Ze zijn nog steeds nuttig. Als u uw antwoord met betrekking tot uw draaitabel hier niet kunt vinden, vraag het dan in het opmerkingengedeelte.
Er zijn nog veel meer functies die nog moeten worden uitgelegd. We leren de geavanceerde draaitabelfunctie in het volgende artikel. Tot dan Excel op alles.