Hoe draaitabellen in Excel te maken

De 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 locatievak.
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 leer je meer dan je 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 en maar ze verwerpen de oude functies niet (zoals MS deed met win 8. Het was zielig). Dit stelt de oudere gebruiker 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 wordt Excel geopend om een draaitabelwizard te maken.

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: Besteldatum (Blijkbaar)

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).

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 je wilt in je 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.
Nu, om de bovenstaande vraag te beantwoorden, zal ik selecteren: Item (selecteer een willekeurige kolom, zorg er alleen voor dat er geen lege cel tussen staat) in waardevelden.

Selecteer Item uit de lijst met velden en sleep deze naar het veld Waarde.

We hebben ons antwoord. Draaitabellen vertellen me dat er in totaal 43 bestellingen zijn. Dit is correct.
Pro-tip: U moet uw gegevens controleren of deze correct zijn of niet. Als dit nummer niet overeenkomt met de gegevens, betekent dit dat u een onjuist bereik hebt geselecteerd of dat dat veld lege cellen bevat.
Informatie: Het waardeveld telt standaard het aantal vermeldingen in een kolom als deze tekst bevat en sommen als het veld alleen waarden bevat. U kunt dit wijzigen in de instellingen van het waardeveld. Hoe? We zien je later in deze zelfstudie voor draaitabels.
Nu ik heb geselecteerd Besteldatum in het Waardegebied, 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.
INFO: Welke wijzigingen u ook aanbrengt in uw brongegevens, de draaitabel blijft werken aan oude gegevens totdat je het ververst. Excel maakt een pivot-cache en een draaitabel draait op die cache. Bij het vernieuwen wordt de oude cache gewijzigd met nieuwe gegevens.

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 het menu Subtotalen.

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 tabelvorm optie. 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 artikelen 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…

Klik met de rechtermuisknop ergens op de som van de kolom Kostprijs in de draaitabel
Klik Waarde Veld Instellingen
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 Tabblad Analyseren

2. Klik op Velden, items en sets in rekengroep.

3. Klik op Berekende velden.
U ziet dit invoervak voor uw rekenveld:

4. Schrijf in het invoerveld voor de naam het gemiddelde Kosten of wat je maar wilt, Excel zal het niet erg vinden. In de formule invoervak, schrijf en druk op Oke.