1 EEN DRAAITABEL SAMENSTELLEN

1 EEN DRAAITABEL SAMENSTELLEN Wat is een draaitabel? De functie ‘Draaitabel’ is het krachtigste instrument van Excel om grote verzamelingen data te analyseren. De data worden razendsnel samengevat in een tabel, een draaitabelrapport. Waar vindt u de functie draaitabellen? De functie ‘Draaitabellen’ is te vinden in de groep ‘Tabellen’ in het lint ‘Invoegen’.

Waarom zijn draaitabellen zo handig? Een draaitabel geeft de mogelijkheid om snel een rapport samen te stellen, waarbij het eenvoudig is om de samenstelling van het rapport aan te passen. Waarom de naam ‘Draaitabellen’? De instrumenten die bij de functie draaitabellen horen, maken het mogelijk om snel verschillende rapporten (samenvattingen) van de brongegevens weer te geven. Hierbij kunnen onder andere rijen en kolommen snel van plaats worden verwisseld. (draaien) Wat is er nodig om een draaitabel samen te stellen? Om een draaitabel te maken is een lijst nodig die geschikt is om een draaitabel mee samen te stellen, de brontabel. De brontabel kan afkomstig zijn van een werkblad of een database. In dit boek wordt uitgegaan van een tabel uit een werkblad. De tabel bestaat uit een aaneengesloten lijst met kolomkoppen. Zonder kolomkoppen kan er geen draaitabel worden aangemaakt. Wat doet een draaitabel met de oorspronkelijke tabel? Niets. De oorspronkelijke tabel wordt gebruikt als bronmateriaal. De draaitabel is een samenvatting van de oorspronkelijke tabel. Wat is er niet mogelijk met een draaitabel? Hoewel een draaitabel veel op een normale tabel lijkt, zijn er toch duidelijke verschillen. Zo is het niet mogelijk om cellen, kolommen of rijen te wissen. Ook is het niet mogelijk om nieuwe rijen of kolommen in de draaitabel te voegen. Wat is er nieuw in draaitabellen binnen Excel 2013?  De functie ‘Aanbevolen draaitabellen’. Met behulp van deze knop is het mogelijk vanuit een geselecteerde tabel heel snel een standaard draaitabel te creëren.  Het nieuwe filter ‘Tijdlijn’. Een filter om op te filteren op datums.  De mogelijkheid om een draaitabel te creëren uit meerdere tabellen.  Het nieuwe uiterlijk van Excel.

Hoofdstuk 2 gedeeltelijk – Draaitabellen Excel 2013

Pagina 1

In de opdrachten wordt als bron een lijst gebruikt van de verkoopcijfers van een groothandel in koffie. De lijst geeft de verkoopcijfers van 3 vertegenwoordigers in een bepaalde periode in een bepaald gebied.

1.1.

Een tabel

De basis van een draaitabel is een lijst of tabel. Deze hoeft niet perse met Excel te zijn gemaakt, maar voorlopig gaan we daar van wel van uit. Waar moet een tabel aan voldoen?     

Elke kolom bevat soortgelijke informatie, bijvoorbeeld de naam van een persoon of een factuurbedrag. Elke rij bevat informatie over een bepaald geval, bijvoorbeeld de gegevens van een bepaalde factuur. De kolomkop heeft een naam, een duidelijke omschrijving van de inhoud van de kolom. De tabel bevat géén lege rijen of kolommen. De tabel bevat liefst géén lege cellen.



In de afbeelding hieronder ziet u al drie fouten in een tabel; een lege kolom, een lege regel en een lege cel.



De verbeterde uitvoering:


Pagina 2

1.2.

Een draaitabel invoegen

Doel van de draaitabel Een draaitabelrapport maken van de verkoopcijfers van elke vertegenwoordiger per provincie.    

Open de werkmap ‘Draaitabellen’. Maak het werkblad ‘Omzet koffie’ actief. Plaats de celaanwijzer in de lijst. Klik op de knop ‘Draaitabel’ in het lint ‘Invoegen’. Het venster ‘Draaitabel maken’ verschijnt.

In dit venster moeten enkele keuzes worden gemaakt:  Wordt de hele tabel of een deel van de tabel gebruikt voor de draaitabel?  Wordt een externe gegevensbron gebruikt?  Wordt de draaitabel op een aparte pagina geplaatst of op een specifiek werkblad? 

  

Zoals u ziet staat het totale bereik van de tabel al vermeld in het venster. Het bereik wordt in de absolute vorm weergegeven; ‘$A$1:$G$298’. Verder wordt weergegeven op welk werkblad de gebruikte tabel staat; ‘Omzet koffie’!$A$1:$G$298’. Accepteer de standaardinstellingen door op ‘OK’ te klikken. Er wordt een nieuw werkblad aangemaakt en er verschijnt een nieuwe tab ‘Hulpmiddelen voor draaitabellen’ met twee linten, ‘Analyseren’ en ‘Ontwerpen’. Links ziet u een venster waar de draaitabel zal verschijnen en rechts een venster met de draaitabelvelden. Klikt u buiten de draaitabel dan verdwijnt dit venster.


Pagina 3

Velden en veldnamen In het bovenste deel van het rechtervenster met de draaitabelvelden, is een lijst met veldnamen te zien. Met de term ‘Velden’ wordt verwezen naar de kolomkoppen van de brontabel. Onder de lijst met veldnamen ziet u vier vakken; Filters Met dit vak kan het draaitabelrapport worden toegespitst op een deel van de tabel. Dit vak is een extra optie. De andere drie vakken worden altijd gebruikt. Kolommen Dit vak is bestemd voor de velden die in de kolomkoppen van het rapport staan. Rijen Dit vak is bestemd voor de velden die aan de linkerkant van het rapport zullen verschijnen. Σ Waarden Dit vak is bedoeld voor de cijfers waarmee het rapport wordt gemaakt. In het rapport zullen deze getallen standaard verschijnen als ‘Som’ van de rijen of kolommen die worden gebruikt in het rapport.


Pagina 4

1.3.

Een draaitabel samenstellen

In deze eerste oefening wordt een rapport gemaakt over de verkoopcijfers van de vertegenwoordigers per provincie.   

Sleep het veld ‘Vertegenwoordiger’ naar het vak ‘Kolommen’. Sleep het veld ‘Provincie’ naar het vak ‘Rijen’. Sleep het veld ‘Verkoopcijfers’ naar het vak ‘Σ Waarden’.



In het vak ‘Waarden’ verschijnt een veldnaam plus de naam van de bewerking, ‘Som van Verkoopcijfers’. Excel kan namelijk ook andere waarden, bijvoorbeeld ‘Gemiddelde’ laten zien. De draaitabel toont nu een rapport met de gevraagde gegevens.



1.4.

De opmaak van de getallen aanpassen

De getallen in de draaitabel zijn nog niet echt geschikt voor een goed overzicht. Een bedrag als 141498,9 is niet echt handig te lezen. De decimalen zijn niet nodig en een scheidingsteken voor de duizendtallen zullen de getallen duidelijker maken. Net als in de standaard werkbladen van Excel kunnen we ook hier het venster ‘Celeigenschappen’ gebruiken.  



Selecteer de getallen en gebruik het venster ‘Celeigenschappen’, tabblad ‘Getal’ om de getallen in de draaitabel aan te passen. Gebruik de optie ‘Geen decimalen’ en een scheidingsteken voor de duizendtallen.

Het is ook mogelijk om de opmaak van de getallen aan te passen met de knoppen ‘Duizendtalnotatie’ en ‘Minder decimalen’. Deze knoppen zijn te vinden op het lint ‘Start’ in de groep ‘Getal’. Eén van de minder goede eigenschappen van een draaitabel is dat deze getalopmaak niet kan worden vastgelegd. U zult dit dus bij elke nieuwe draaitabel moeten herhalen.


Pagina 5

1.5.

Een veld toevoegen aan de draaitabel

Mogelijk moet het rapport meer details tonen. De omzetcijfers moeten bijvoorbeeld worden weergegeven per kwartaal.   

Plaats de muisaanwijzer in de draaitabel. Het venster ‘Draaitabelvelden’ verschijnt weer. Selecteer en sleep het veld ‘Kwartaal’ naar het vak ‘Rijen’. De draaitabel wordt aangepast. De jaarcijfers van elke vertegenwoordiger worden aangevuld met de kwartaalcijfers.

1.6.

De volgorde van de velden aanpassen

In het vorige voorbeeld is het veld ‘Kwartaal’ toegevoegd aan het vak ‘Rijen’. Het staat onder het veld ‘Provincie’. Door het veld ‘Kwartaal’ boven het veld ‘Provincie’ te slepen ontstaat er een andere situatie.   

Plaats de muisaanwijzer in de draaitabel. Sleep in het venster ‘Draaitabelvelden’ het veld ‘Kwartaal’ boven het veld ‘Provincie’. De draaitabel wordt opnieuw aangepast.


Pagina 6

1.7.

Een veld verwijderen

Het verwijderen van een veld is net zo gemakkelijk als het toevoegen van een veld. 

Maak het venster ‘Draaitabelvelden’ weer actief en sleep het veld ‘Kwartaal’ uit de lijst.

1.8.

Het bijwerken van de indeling uitstellen

In de voorbeelden in dit boek worden tabellen gebruikt die niet erg groot zijn. In de praktijk kan het voorkomen dat u met een enorm grote hoeveelheid data in een tabel werkt. Elke verandering van de velden wordt standaard meteen uitgevoerd. Afhankelijk van de kracht van de processor kan het dan even duren voor u een volgend veld kunt aanpassen. U kunt dan de optie ‘Bijwerken van de indeling’ gebruiken om de uitvoering uit te stellen tot u alles naar tevredenheid hebt ingesteld. Deze optie vindt u in het venster ‘Draaitabelvelden’.      

Vink de optie ‘Bijwerken van de indeling’ aan. Pas twee van de velden aan. De indeling wordt niet bijgewerkt. Klik de knop ‘Bijwerken’ aan. De indeling wordt nu bijgewerkt. Maak de verandering ongedaan. Werk de indeling bij met de knop ‘Bijwerken’. Schakel de optie weer uit.

1.9.

Velden toevoegen of verwijderen

Er is een tweede manier om de velden in één van de vakken te plaatsen. Vink een veld aan, en Excel zal het veld in één van de veldvakken plaatsen. Excel plaatst de velden in één van de vakken op basis van de informatie die in de velden (kolom) staat. Kolommen gevuld met cijfers worden geplaatst in het vak ‘Σ Waarden’. Getallen met tekst komen in het kolom- of rijveld terecht. Mogelijk moet u dus corrigeren. Welke methode u kiest moet u zelf weten. Persoonlijk vind ik de sleepmethode plezieriger, dan hoef je nooit te corrigeren. U verwijdert de velden door het veld weer af te vinken.


Pagina 7

1.10. Opnieuw beginnen Mogelijk hebt u zich toch vergist in de keus van de velden, of is er een andere reden om helemaal overnieuw te willen beginnen (met een draaitabel).     

Plaats de muisaanwijzer in de draaitabel. Maak het lint ‘Analyseren’ actief. Klik in de groep ‘Acties’ op de knop ‘Wissen’. Kies daarna de optie ‘Alles wissen’. Maak de actie ongedaan met ‘Ctrl + Z’ of de knop ‘Ongedaan maken’.

1.11. Subtotalen In de vorige voorbeelden worden de subtotalen weergegeven boven de kwartalen. De subtotalen kunnen ook onder de kwartalen worden geplaatst.    

Plaats de muisaanwijzer in de draaitabel. Maak het lint ‘Ontwerpen’ actief. Klik in de groep ‘Indeling’ op de knop ‘Subtotalen’. Er verschijnt een keuzemenu. Klik op de optie ‘Alle subtotalen onder de groep weergeven’.


Pagina 8

1.12. De kolom ‘Totalen’ verbergen De kolom ‘Eindtotaal’ is in dit rapport niet van belang en kan worden uitgeschakeld.   

Plaats de muisaanwijzer in de draaitabel. Maak het lint ‘Ontwerpen’ actief. Klik op de knop ‘Eindtotalen’ in de groep ‘Indeling’.

Er verschijnt een keuzemenu.  Kies de optie ‘Aan alleen voor kolommen’. De kolom ‘Eindtotaal’ is verwijderd uit de draaitabel.

1.13. De opmaak van de draaitabel aanpassen Alle opmaakmogelijkheden die Excel heeft, bijvoorbeeld ‘Vet’ of ‘Randen’ zijn ook te gebruiken in een draaitabel. In het voorbeeld hieronder hebben de provincies het opmaakkenmerk ‘Vet’ gekregen. Er zijn verschillende dikke randen aangebracht en de rijen met cijfers hebben een dunne rand. 

Pas de opmaak van de draaitabel aan volgens onderstaand voorbeeld of gebruik uw eigen fantasie.


Pagina 9

1.14. Draaitabelstijlen Naast de mogelijkheid om de opmaak van de draaitabel handmatig aan te passen is er de optie ‘Draaitabelstijlen’. Met deze optie is het mogelijk om snel een voorgeprogrammeerde opmaak toe te passen op een draaitabel. De optie ‘Draaitabelstijlen’ is te vinden in het lint ‘Ontwerpen’. Via deze optie hebt u de beschikking over 85 stijlen.

   

    

Plaats de muisaanwijzer in de draaitabel. Maak het lint ‘Ontwerpen’ actief. Klik op de onderste keuzepijl van de groep ‘Draaitabelstijlen’. Het venster klapt open. Er zijn 3 groepen stijlen; ‘Licht’, ‘Normaal’ en ‘Donker’. Beweeg de muisaanwijzer over de stijlvoorbeelden. Het effect van de stijl wordt meteen zichtbaar in de draaitabel. De naam van de stijl verschijnt als u de muisaanwijzer boven een stijlvoorbeeld laat ‘hangen’. Klik op de stijl die u wilt toepassen. Probeer enkele stijlen uit. De standaardstijl is ‘Licht 16’. Dit is de stijl in de derde rij, derde kolom. Pas de standaardstijl toe. Sla de werkmap op.

1.15. Opties voor draaitabelstijlen Het is mogelijk de draaitabelstijlen aan te passen. De draaitabelstijlen kunnen al behoorlijk veranderen door de opties in het vak ‘Opties voor draaitabelstijlen’ aan te vinken. 

Pas een stijl toe en probeer de opties uit. De meeste zijn beter dan de standaardstijl.


Pagina 10

1.16. Een aangepaste draaitabelstijl maken Naast de mogelijkheden van ‘Opties voor draaitabelstijlen’ zijn er meer mogelijkheden om de draaitabelstijlen aan te passen. U kunt namelijk een stijl maken op basis van een standaardstijl.   

Kies een stijl uit die al een beetje op weg is naar de opmaak die u wilt creëren. Klik met rechts op de stijl om het snelmenu te openen. Kies de optie ‘Dupliceren’.

Het venster ‘Stijl voor draaitabellen wijzigen’ verschijnt.  

De nieuwe stijl heeft nu nog de naam van de oude stijl, met een 2 toegevoegd, bijvoorbeeld ‘PivotStyleLight16 2’ Geef de stijl een nieuwe naam, bijvoorbeeld uw eigen naam.

Laten we als voorbeeld de kleur van de gearceerde rijen aanpassen.      

Klik in het vensterdeel ‘Tabelelement’ op de optie ‘Eerste rijstreep’. Klik op de knop ‘Opmaak’. Klik op de tab ‘Opvulling’. Kies een gewenste kleur of stijl uit. Bevestig met ‘OK’. Verander eventueel nog enkele andere onderdelen van de draaitabel. De aanpassingen worden nog niet toegepast. 

Open de lijst met draaitabelstijlen. Er is een nieuw vak bijgekomen ‘Aangepast’ met daarin uw eigen stijl.  Klik de stijl aan en hij wordt toegepast. Dit is typisch zo’n optie waar u een beetje mee moet spelen om de mogelijkheden te zien.

1.17. Nieuwe stijl voor draaitabellen U kunt ook besluiten om een geheel nieuwe stijl te maken. Gebruik dan de optie ‘Nieuwe stijl voor draaitabellen’. Draaitabelstijl wissen Wilt u de opmaak van de draaitabelstijl verwijderen, gebruik dan de optie ‘Wissen’. Ook deze vindt u onder in het venster ‘Draaitabelstijlen’.


Pagina 11

1.18. De labelnamen aanpassen In het gemaakte rapport zijn koppen als ‘Som van Verkoopcijfers’ of ‘Kolommen’ niet erg mooi. Dit is gemakkelijk aan te passen. Dubbelklik in de cel en pas de naam aan. Hierbij mag de naam van het veld niet worden gebruikt. Eén van de veldnamen is: ‘Provincie’. Dit kunt u omzeilen door bijvoorbeeld het woord ‘Provincies’ te gebruiken. Gebruik een spatie voor een cel die geen inhoud hoeft te hebben.

1.19. Lege cellen opvullen met nullen Eén van de elementen van een goed tabelontwerp, is dat geen enkele cel in een kolom met cijfers wordt leeggelaten. Als u dat bij een tabel doet die wordt gebruikt voor een draaitabel, kan dit verschillende foutmeldingen of verkeerde berekeningen opleveren. Het is dan ook vervelend dat een draaitabel wél lege cellen vertoont. Zie het voorbeeld in de vorige paragraaf. Het is eenvoudig om de lege cellen in een draaitabel op te vullen met nullen.         

Maak de draaitabel actief. Maak het lint ‘Analyseren’ actief. Klik in het vak ‘Draaitabel’ op de knop ‘Opties’. Klik in het menu op de knop ‘Opties’. Maak de tab ‘Indeling en opmaak’ actief. Vink de optie ‘Voor lege cellen weergeven’ aan. Vul in het invoervak een nul in. Bevestig met ‘OK’. Alle lege cellen in de draaitabel worden nu gevuld met nullen.


Pagina 12

1.20. De opmaak van de ‘nul’ aanpassen U kunt eventueel de getalopmaak een aanpassen, zodat de nullen een zachtere kleur krijgen.     

Klik met de rechtermuisknop op een getal in het waardegebied van de draaitabel en kies in het snelmenu de optie ‘Waardeveldinstellingen’. Klik op de knop ‘Getalnotatie’. Klik in het venster ‘Celeigenschappen’ op ‘Aangepast’. Typ in het invoerveld, #.##0;#.##0;[kleur15]#.##0 Klik tweemaal op de knop ‘OK’ om de wijziging te bevestigen. 

1.21. De indeling van het rapport aanpassen Als u meerdere velden in één vak gebruikt kan het handig zijn de diverse indelingsmogelijkheden te bekijken. 

Maak de volgende draaitabel.

 

Maak de tab ‘Ontwerpen’ actief. Klik op de knop ‘Rapportindeling’.

 

Er zijn drie mogelijkheden; De compacte weergave zoals hierboven en de overzichts- en de tabelweergave


Pagina 13

1.22. Lege regels invoegen Het is via de optie ‘Lege regels’ mogelijk om lege regels tussen elk item in te voegen.   

Maak de tab ‘Ontwerpen’ actief. Klik op de knop ‘Lege rijen’. Klik op de optie ‘Lege regel invoegen na elk item’. Het effect zal wel duidelijk zijn.


Pagina 14

1.23. De werkbalk Snelle Toegang (WST) 

 

Als u vaak een draaitabel moet maken, kan het handig zijn om een knop op de WST te plaatsen. Dit is het kleine balkje met werkbalkknoppen boven of onder de tabbladen. Klik op de keuzepijl aan de rechterkant van de WST. Kies in het menu voor de optie ‘Meer opdrachten’. U weet dat de knop ‘Draaitabellen in het lint ‘Invoegen’ staat.

      

Klik in het vervolgscherm op de keuzepijl van het schermdeel ‘Kies opdrachten uit’. Selecteer de optie ‘Tabblad Invoegen’. Nu verschijnen alle knoppen die beschikbaar zijn op het tabblad ‘Invoegen’. Selecteer de optie ‘Draaitabel’. Klik op de knop ‘Toevoegen’. Bevestig met ‘OK’. De knop ‘Draaitabel’ is nu toegevoegd aan de WST.


Pagina 15

1.24. Opdrachten 1 Er volgen nu een aantal opdrachten om de behandelde onderwerpen beter onder de knie te krijgen. Dit hoofdstuk liet zien hoe krachtig een draaitabel kan zijn met een lijst van zo’n 300 rijen. In deze opdracht wordt gebruik gemaakt van een lijst met 18.000 rijen. Opdracht 1 a) Maak het werkblad ‘Facilitair bedrijf’ actief. b) Maak een draaitabel met de volgende velden; ‘Dienst’, ‘Gebied’, ‘Bedrag’. Dit zal het volgende rapport opleveren.

Opdracht 2 a) Pas de opmaak van de getallen aan, zodat de decimalen verdwijnen en een scheidingsteken voor de duizendtallen verschijnt.

Opdracht 3 a) Vervang het veld ‘Gebied’ door het veld ‘Vertegenwoordiger’. b) Laat de kolom ‘Eindtotaal’ verdwijnen.


Pagina 16

Opdracht 4 a) Voeg het veld ‘Maand’ toe aan het vak ‘Rijen’.

Opdracht 5 a) Plaats het veld ‘Dienst’ onder het veld ‘Maand’.

Opdracht 6 a) Verander de namen van de labels in ‘Omzet’, ‘Naam’ en ‘Dienst’. b) Pas de lettergrootte van de labelnamen aan in 14 punten.

Opdracht 7 a) Gebruik één van de stijlen om de opmaak aan te passen. Opdracht 8 a) Verwijder het werkblad met de draaitabel.


Pagina 17

1 EEN DRAAITABEL SAMENSTELLEN

Recommend Documents