Excellerend
Kwartaaltip 2015-1
Excellerend Heemraadweg 21 2741 NC Waddinxveen 06 – 5115 97 46
[email protected] BTW: NL0021459225 BANK: NL72ABNA0536825491 KVK: 24389967
Grote hoeveelheid gegevens analyseren: draaitabellen Het kan zijn dat je een grote tabel met gegevens hebt en je wilt deze gegevens eenvoudig analyseren. Via formules als AANTAL.ALS, SOM.ALS en SOMMEN.ALS kom je een heel eind, en ook de functionaliteit SUBTOTALEN kan je goed helpen. Maar pas wanneer je draaitabellen gaat gebruiken, dring je door tot de kracht van Excel! Uitleggen wat de mogelijkheden zijn van draaitabellen lukt niet in een paar tips, maar ik ga wel een aanzet geven. Dit is de eerste kwartaaltip, als er behoefte aan is volgen er meer. Gegevenstabel De tabel waarvan je de gegevens wilt analyseren, kan statisch zijn, maar het is ook mogelijk dat je een koppeling hebt gelegd met een database waarin nog meer gegevens worden ingevoerd. In deze tip werk ik met een statische tabel in Excel, dus niet vanuit een andere bron. Het bestand staat op mijn website bij de kwartaaltips. In de print screens staat slechts een deel van de tabel. De tabel bevat 7 kolommen: Kolom A met de datum waarop een product verkocht is; Kolom B met de regio waar de verkoop plaatsvond; Kolom C met de verkoper; Kolom D met het product (Dit zijn alleen desktops en laptops); Kolom E met het verkochte aantal; Kolom F met de kosten van de verkochte aantallen in euro’s; Kolom G met de opbrengst van de verkochte aantallen in euro’s.
Pagina 1 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-1
Het maken van een draaitabel De eerste stap bestaat uit het activeren van het tabblad waarin de tabel staat en het selecteren van een cel in die tabel. Klik dan op de tab Invoegen en kies uit de eerste groepering de optie Draaitabel > Draaitabel:
Excel herkent gelijk de hele tabel. En let op, een tabel eindigt bij de eerste lege kolom en lege rij! Lege cellen worden wel meegenomen. Er verschijnt een dialoogvenster waarin het bereik van de draaitabel wordt weergegeven:
Het bereik bevat in dit geval een tabel van 54 regels, en onderaan wordt aangegeven dat de draaitabel op een nieuw werkblad moet worden gezet. Het is mogelijk via dit venster te kiezen voor een externe gegevensbron. Dat kan een csv-bestand zijn, een accesstabel en nog veel meer. Wanneer er in een later stadium regels toegevoegd worden aan de tabel, is het verstandig het bereik uit te breiden tot misschien wel de volledige kolom: $A:$G. In de draaitabel komt dan ook (leeg) te staan zonder gegevens. Ook kan de draaitabel op een bestaand tabblad gezet worden en is het mogelijk dan een bepaalde cel te kiezen om bijvoorbeeld tien regels vrij te houden boven de tabel. Dat doe ik hier niet, de draaitabel komt op een nieuw tabblad terecht, en vanaf cel A1. Pagina 2 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-1
Klik op OK. Het nieuwe tabblad ziet er dan zo uit (verkleinde weergave):
Rechtsboven staat de lijst met draaitabelvelden. Iedere kolom in de tabel met gegevens heet in de draaitabel een veld. In ons voorbeeld zijn er 7 velden. Rechtsonder staan vier vakken die de vier gebieden van de draaitabel voorstellen: Rapportfilter; Kolomlabels; Rijlabels en Waarden. In het veld waarden zet je doorgaans het veld met aantallen, kosten, opbrengsten en andere zaken die je kunt tellen. Links komt de draaitabel te staan. En midden bovenin op het lint verschijnt de hulptab Hulpmiddelen voor draaitabellen in het paars. Deze hulptab heeft twee tabbladen welke misschien in een volgende tip uitgebreider aan bod komen:
Vink rechts het veld aan bij Kosten en Opbrengst. Excel zet de velden dan in het gebied Waarden. Als dat niet zo is, sleep het dan alsnog naar dit gebied. Vink ook het veld Datum aan. Excel zet dat in het gebied Rijlabels. Sleep het veld Regio naar het gebied Kolomlabels boven ”–waarden” en de volgende draaitabel wordt getoond:
Pagina 3 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-1
Links staan in kolom A de datums en in de kolommen staat de combinatie van het veld Regio en de gekozen Waardevelden ‘Kosten’ en ‘Opbrengst’. Een onduidelijkheid –vind ik- is dat we standaard zien staan “Rijlabels” en “Kolomlabels”. Welk veld het betreft is in deze weergave niet goed te zien (maar wel af te leiden uit de waarden zoals Noord, Oost, West en Zuid), maar dat gaan we straks aanpassen. Gebruik van de filter Het aanpassen van de draaitabel kan op verschillende manieren. Zodra een cel in de draaitabel geselecteerd wordt, verschijnt de lijst met draaitabelvelden. Daarin kun je willekeurig velden naar en van gebieden slepen en de draaitabel wordt direct bijgewerkt. Sleep het veld Product naar het gebied Rapportfilter. Klik op de driehoek en selecteer “laptop”. De draaitabel ziet er dan -deels- zo uit:
In cel B1 valt te zien dat we een filter hebben toegepast doordat het icoontje is veranderd.
Pagina 4 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-1
Wanneer een optelling per rij of per kolom nietszeggend is, kan deze ook worden weggelaten. Klik met de rechter muisknop in de tabel en kies ‘Opties voor draaitabel…’:
Er verschijnt dan een dialoogvenster met zes tabbladen. Ga naar het tabblad ‘Totalen en filters’ en vink de vierkantjes uit bij ‘Totalen voor rijen weergeven’ en ‘Totalen voor kolommen weergeven’.
Klik op OK.
Pagina 5 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-1
Opmaak en berekening van de waarden aanpassen Bij het maken van de draaitabel, heeft Excel zelf gekozen om de waarden op te tellen met de functie SOM. Dat klopt, maar de opmaak wordt vanuit de tabel niet overgenomen. We zien dus bij de bedragen geen euroteken. Klik met de rechter muisknop in de draaitabel in een cel die onder ‘Som van Kosten’ staat, en kies Waardeveldinstellingen:
Excel komt dan met een dialoogvenster waarin je een aantal zaken kunt wijzigen:
Pagina 6 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-1
De waarden uit de tabel worden nu gesommeerd weergegeven. Wanneer je wilt weten hoeveel rijen er in de tabel zijn van de combinatie datum, verkoper, product, regio etc. kies je hier voor Aantal. Linksonder staat een knop ‘Getalnotatie’. Klik daarop om daarna in het dialoogvenster Celeigenschappen te kiezen voor:
Nu is de opmaak van de kosten aangepast, en ook de totaaltelling per rij en kolom:
Maar nog niet die van de Opbrengsten. Per veld in het gebied waarden kun je dus een eigen opmaak meegeven zodat je euro’s en aantallen in één draaitabel kunt plaatsen. Het maakt niet uit of het veld Regio onder of boven de waarden staat:
en Veranderen wel de opbouw van de tabel, maar de aangepaste opmaak blijft gelden voor de velden in het gebied Waarden. Pagina 7 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-1
Aanpassen uiterlijk draaitabel Het is mogelijk om het uiterlijk van de draaitabel aan te passen naar de oude indeling uit de tijd van Excel 2003 en eerder. Klik weer ergens met de rechter muisknop in de tabel en kies Opties, klik op de hulptab op Opties, en dan helemaal links op het lint in de groepering Draaitabel op Opties > Opties:
Excel komt dan met een venster met een dialoogvenster met zes tabs. Klik op de tab “Weergave” en vink het vierkantje aan bij “Klassieke draaitabelindeling…”:
Klik op OK. In plaats van “Rijlabels” staat er nu “Datum”, en in plaats van “Kolomlabels” staat er nu “Waarden” en “Regio”. Zo vind ik dat het duidelijker is waar je naar kijkt.
Pagina 8 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-1
Groeperen op maanden De cijfers worden nu nog getoond per datum, maar omdat Excel de datums als zodanig herkent, kun je er ook mee groeperen. Klik met de rechter muisknop op het label “Datum” en kies ‘Groeperen…’:
In het venster dat dan verschijnt geeft Excel aan wat de laagste datum en de hoogste datum in de tabel is, en waar je op kunt groeperen. Ik groepeer hier op Jaar en Maand en klik dan op OK:
Pagina 9 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-1
N.B. Wanneer de onderliggende tabel uitgebreid wordt met datums die buiten het aangegeven bereik vallen, worden ze bij het verversen van de draaitabel automatisch meegenomen in de groepering. De draaitabel ziet er nu zo uit:
De gegevens worden nu samengevat weergegeven per maand en per jaar. De datum zelf is ‘verdwenen’ omdat ik dat bij het groeperen zo had bepaald. Als je meer van draaitabellen wilt lezen in een kwartaaltip, dan lees ik dat wel in een e-mail. Einde kwartaaltip 1-2015
De kwartaaltip(s) zijn als PDF te downloaden via: www.excellerend.nl\kwartaaltips.html Wilt u een op maat gemaakte cursus Excel voor uw organisatie regelen? Zoekt u ondersteuning in het bouwen van rekenmodellen, controles, of denkt u dat iets mogelijk is in Excel maar u weet niet hoe? Neem dan contact op met Richard Meijles: 06 – 5115 97 46 of via e-mail:
[email protected]. Wilt u geen kwartaaltips meer ontvangen? Klik dan op:
[email protected] Hebt u een verzoek voor een kwartaaltip? Klik dan op:
[email protected] en vermeld tevens uw Excelversie. Richard Meijles
Pagina 10 van 10
www.excellerend.nl
