excel in de financiële praktijk

excel in de financiële praktijk

Excel in de financiële praktijk Ir. C.A. Overgaag

Brinkman Uitgeverij · Amsterdam · 2009

isbn 978 90 5752 165 2 / nur 123 Omslagontwerp: Proforma, Barcelona Redactie en boekverzorging: Henk Pel, Zeist Opmaak en zetwerk: Teus de Jong, Nij Beets © 2009 Brinkman Uitgeverij, Amsterdam Gehele of gedeeltelijke overneming of reproductie van de inhoud van deze uitgave, op welke wijze dan ook, zonder voorafgaande schriftelijke toestemming van de auteursrechthebbende is verboden, behoudens de beperkingen bij de wet gesteld. Het verbod betreft ook gehele of gedeeltelijke bewerking. De uitgever is met uitsluiting van ieder ander gerechtigd de door derden verschuldigde vergoedingen voor kopiëren, als bedoeld in artikel 17 Auteurswet 1912 en in het kb van 20 juni 1974 (Stb. 351, 1974) ex artikel 16b Auteurswet 1912, te innen en/of daartoe in en buiten rechte op te treden.

Correspondentie inzake overneming of reproductie richten aan: Brinkman Uitgeverij, Postbus 59686, 1040 ld Amsterdam www.brinkman-uitgeverij.nl tel. 020-4120970, fax 020-4120972 e-mail: [email protected]

5

1 Draaitabellen

Draaitabellen is een erg handige functie in Excel waarmee je eenvoudig gegevens kunt samenvatten. De weergave ziet er dan een stuk overzichtelijker uit. Het brengt het groeperen en totaliseren van gegevens terug tot een paar simpele handelingen. Vervolgens kun je met de draaitabel allerlei analyses maken van de originele gegevens. Je hoeft als gebruiker alleen maar velden te verslepen om de tabel tot stand te brengen of later te wijzigen. Om te leren werken met draaitabellen wordt in dit hoofdstuk het volgende behandeld: – wat is een draaitabel – een draaitabel maken – groeperen – velden opmaken – de draaitabel opmaken – in een draaitabel filteren – meer dan alleen optellen – onderliggende gegevens in een draaitabel raadplegen – grafieken maken van draaitabellen – draaitabellen printen

1.1 Wat is een draaitabel? Een draaitabel is een samenvatting van een lijst met gegevens. Het doel is om de gegevens te onderzoeken of te analyseren. De originele gegevens laat Excel onaagetast en de draaitabel zelf wordt op een nieuwe plek geplaatst. Een draaitabel is niet star, maar maakt het je mogelijk om heel snel van invalshoek te veranderen. Om het begrip draaitabellen uit te leggen, is het handig om eerst stil te staan bij de gegevens waarop de draaitabel gebaseerd is, de zogenaamde lijsten. Met deze achtergrond weet je beter wat de toegevoegde waarde van een draaitabel is. Lijsten

Neem als voorbeeld het Excel-bestand Vertegenwoordigers.xlsx (afb. 1). Hierin staat een lijst vertegenwoordigers. Bij iedere vertegenwoordiger zijn gegevens zoals woonplaats, jaarsalaris enzovoort ingevoerd. Op iedere regel wordt een andere vertegenwoordiger beschreven. De vertegenwoordigers staan in een rij onder elkaar. Een regel in een lijst wordt ook wel een record genoemd. En wat is het bijzondere aan een record in een lijst? Een record heeft altijd dezelfde structuur. Zo vind je hier (afb. 1) in de eerste kolom van de records altijd een waarde met dezelfde betekenis (waarde 231, 498 enzovoort). Voor de overige kolommen geldt hetzelfde. Dit

6

Excel in de financiële praktijk

is natuurlijk handig als er gevraagd wordt: ‘Geef de waarde van de derde kolom die bij de eerste kolomwaarde 176 past?’ Iedereen weet – en dus ook het programma – dat het antwoord Eindhoven is (afb. 1). Als je in plaats van de derde kolom zou vragen naar woonplaats en de eerste kolom Wknr noemt, dan zou de vraag veel sprekender zijn, namelijk: ‘Geef de waarde van Woonplaats die bij Wknr = 176 past?’ Dat de eerste kolom Wknr en de derde kolom Woonplaats genoemd wordt, vind je bovenaan de lijst. Daar staat hoe de kolom heet, de zogenaamde Veldnamen. De afzonderlijke onderdelen van een record, iedere cel, worden velden genoemd.

Kolommen Veld (Ieder vakje is een veld)

Veldnaam

Regel of Record

Rijen

Afb. 1 Vertegenwoordiger.xlsx: verschillende onderdelen van een lijst

Vertegenwoordiger.xlsx is een mooie en gestructureerde lijst die gegevens van vertegenwoordigers weergeeft. Zoals je kunt zien zijn er maar een paar districten en zijn er meer vertegenwoordigers die in één district zitten. Een draaitabel maakt het mogelijk om bijvoorbeeld alle records (of regels) van één district bijeen te pakken, te groeperen, en op één regel te zetten. Voor de draaitabel is het vervolgens een peulenschil om getallen te totaliseren per district (afb. 2). In afbeelding 2 zie je de vertegenwoordigerslijst aan de linkerkant en rechts ernaast een draaitabel (voor de duidelijkheid omcirkeld). Deze draaitabel groepeert alle districten en totaliseert de Jaarsalarissen per District. De pijlen geven als voorbeeld aan welke velden uit de lijst waar naartoe gaan in de draaitabel. Je ziet dat de veldnamen in de draaitabel gebruikt worden bij het kopje Som van Jaarsalaris. Dat wordt automatisch gedaan.

1

Draaitabellen

Zonder de functie draaitabel zou dit niet zo makkelijk te maken zijn.

Afb. 2 Vertegenwoordiger.xlsx: een draaitabel naast de lijst

Waarom heeft Microsoft voor deze functie het woord ‘draaitabel’ gekozen? Tabel is een ander woord voor lijst. De draaitabellenfunctie wordt altijd toegepast op lijsten in Excel. Door de gewenste kolommen in de tabel te kantelen oftewel ‘draaien’ maakt Excel het mogelijk om gegevens te groeperen en dus ook te totaliseren (afb. 3).

Afb. 3 Vertegenwoordiger.xlsx: de kolommen worden ‘gedraaid’ naar de draaitabel

7

8


1.2 Een draaitabel maken Het wordt tijd om zelf aan de slag te gaan met draaitabellen. Al spelenderwijs zul je ze leren aanmaken en aanpassen. Hiervoor gebruik je de verkoopgegevens van postorderbedrijf Buitendeur. Deze firma levert outdoor artikelen in binnen- en buitenland. Ieder kwartaal worden de omzetgegevens geanalyseerd door de verkoopleider om na te gaan in welk land het meest wordt verkocht, welk artikel het best verkoopt en wat de vertegenwoordigers doen. De omzetgegevens komen uit een administratief pakket en zijn in Excel ingelezen. Elke kolom heeft een naam, de zogenaamde veldnaam (afb. 4). Een kwartaal bestaat uit drie perioden. Je hebt het Excel-bestand ontvangen om er je kunsten mee te vertonen. Je maakt de volgende draaitabellen voor de verkoopleider: – Netto omzet per klant over de perioden – Netto omzet per land en artikel over de perioden – Kortingbedrag per klant over de vertegenwoordigers – Omzet per vertegenwoordiger per periode

Afb. 4 Buitendeur.Xlsx: lijst met omzetten in een kwartaal

Je maakt een draaitabel waarbij totale netto omzetten van de klant uitgesplitst naar periode te zien zijn. 1 Start Excel 2007. 2 Open de werkmap Buitendeur.xlsx uit de map Rekenbladen van je schijf. Je krijgt een lijst van omzetgegevens die alleen is gesorteerd op periode. 3 Klik op het tabblad Invoegen van het Lint in de groep Tabellen op het uitschuifpijltje bij de knop Draaitabel. De keuze wordt geboden voor een draaitabel of een draaigrafiek (afb. 4).

1

Draaitabellen

Klik op de optie Draaitabel. Het dialoogvenster Draaitabel maken verschijnt (afb. 5).

Afb. 5 Buitendeur.xlsx: dialoogvenster Draaitabel maken

Opmerking In dit dialoogvenster wordt een aantal mogelijkheden geboden voor de te maken draaitabel. • Selecteer de gegevens die u wilt analyseren Hiermee bepaal je de invoergegevens voor de draaitabel. Standaard – zoals in ons geval – wordt de lijst van het actieve werkblad voorgesteld. Voor de duidelijkheid zie je dat de lijst ook wordt omstippeld. Bevat het actieve werkblad geen lijst, dan wordt het vak Tabel/bereik leeg gelaten en moet je zelf een bereik aangeven. Je kunt ook een koppeling maken met een externe gegevensbron, zoals een Access-database of zelfs met het web. Voor degenen die geïnteresseerd zijn in externe koppelingen: nieuwe externe verbindingen kun je definiëren via het tabblad Gegevens van het Lint in de groep Externe gegevens ophalen. • Selecteer de locatie voor het draaitabelrapport Nieuw werkblad: De standaardkeus is om de draaitabel in een nieuw werkblad te plaatsen. Dit is de meest nette optie en deze kies je in alle oefeningen. Bestaand werkblad: De draaitabel wordt geplaatst in hetzelfde werkblad, zoals je hebt gezien in afbeelding 2. In het veld achter Locatie geef je de cel aan waar de draaitabel moet beginnen. 4 Je accepteert de standaardopties in het dialoogvenster Draaitabel maken en klikt op de knop OK. Een nieuw werkblad wordt geopend, gericht op het maken van een draaitabel (afb. 6).

9

10


Afb. 6 Buitendeur.xlsx: werkblad met draaitabelopmaak

Aan de linkerkant van het werkblad zie je de plek voor de draaitabel. Hier komen de gegevens vanzelf terecht. Aan de rechterkant zie je de werkset, waarmee je kunt bepalen wat er in de draaitabel moet komen. In de werkset kun je velden uit de Lijst met draaitabelvelden selecteren, die je nodig hebt voor het rapport. Als je een veld aanklikt, verschijnt het veld hierdoor in een van de gebieden eronder. In de het gebied Rijlabels komen vanzelf de velden die geen getallen zijn. In het gebied Waarden komen vanzelf de getallen. Een veld in het gebied Rijlabels komt aan de zijkant van de draaitabel (afb. 7). Een veld in het gebied Kolomlabels komt aan de bovenkant van de draaitabel. Een veld in het gebied Waarden komt in het midden van de draaitabel. Een veld in het gebied Rapportfilter wordt bovenaan de werkmap, in cel A1, geplaatst. Daarmee kan makkelijk een filter gelegd worden op de Waarden in de draaitabel. Je kunt zelf bepalen waar uiteindelijk de velden terecht moeten komen, door een veld aangeklikt te houden en te verslepen. Dan kun je ze van het ene gebied naar het andere slepen. Op het moment dat een veld in een van de gebieden is geplaatst, wordt direct de draaitabel bijgewerkt. Al deze zaken komen verderop aan bod. Je gaat stap voor stap aan de slag. 5 Selecteer het veld Klant uit het deelvenster Lijst met draaitabelvelden (het venster aan de rechterkant) en sleep dit naar het gebied Rijlabels. Je ziet de draaitabel aan de linkerkant automatisch gevuld worden met alle klanten die er

1

Draaitabellen

te vinden zijn in de lijst uit Blad 1. In het deelvenster Lijst met draaitabelvelden is het veld Klant door deze actie nu aangevinkt. 6 Selecteer het veld Netto bedrag uit het deelvenster Lijst met draaitabelvelden en sleep dit naar het gebied Waarden. Nu wordt de draaitabel direct gevuld met de netto bedragen getotaliseerd per klant. De draaitabel groepeert in dit geval per klant. 7 Het uitsplitsen van de netto bedragen naar perioden is eveneens eenvoudig. Sleep het veld Periode uit het deelvenster Lijst met draaitabelvelden naar het gebied Kolomlabels. Direct wordt de draaitabel weer bijgewerkt (afb. 7). De netto bedragen worden nu gegroepeerd én per klant én per periode. En dat gaat heel vlug!

Afb. 7 Buitendeur.xlsx: de eerste draaitabel!

Opmerking Wanneer je in het blad van de draaitabel een keer op een cel buiten de draaitabel klikt, merk je dat de inrichtingshulp aan de rechterkant (Lijst met draaitabelvelden) en de tabbladen Opties en Ontwerpen verdwijnen. Je krijgt dit weer op het scherm door op de draaitabel te klikken. Je ziet dat je met een aantal simpele handelingen (vier muisklikken en driemaal slepen) al tweedimensionaal kunt totaliseren (per klant, per periode). De flexibiliteit en snelheid waarmee je dit kunt is ongeëvenaard!

11

12


Verder groeperen

Eigenlijk heb je al gegroepeerd in de vorige paragraaf. Je gaat nu verder groeperen en onderverdelen op rijlabel-niveau. Je maakt een draaitabel met een overzicht van de netto omzet over de perioden uitgesplitst naar land, en vervolgens gegroepeerd naar artikel. Ga verder vanaf het eind van de vorige paragraaf. 1 Zorg dat het werkblad van afbeelding 7 voor je staat. 2 Verwijder eerst de rijlabel Klant door in het deelvenster Lijst met draaitabelvelden het veld Klant uit te vinken. De klanten verdwijnen uit de draaitabel. 3 Breng het veld Land in de draaitabel rij door het veld uit het deelvenster Lijst met draaitabelvelden naar het gebied Rijlabels te slepen. Nu wordt de omzet per land, verdeeld over de perioden getoond. 4 Breng het veld Artikel vervolgens in de draaitabel rij door het veld uit het deelvenster Lijst met draaitabelvelden naar het gebied Rijlabels te slepen, onder de rijlabel Klant. Laat je het veld Artikel voor het veld Land vallen, dan krijg je in de draaitabel het overzicht eerst gegroepeerd op Artikel en dan op Land. Opmerking Het wisselen van de groepeervolgorde is eenvoudig: in het gebied Rijlabels, Kolomlabels of Waarden kun je met de muis ingedrukt een veld verslepen naar de gewenste plaats. Eveneens kun je velden verslepen van het ene vak naar het andere – zie afbeelding 8.

Afb. 8 Buitendeur.xlsx: verplaatsen van de velden in de gebieden

5 Bewaar de werkmap op schijf.

1

Draaitabellen

Opdracht: Veranderen van waarden in de draaitabel In deze opdracht werk je met het verschuiven van de velden in de gebieden. Ga uit van het werkblad dat je zojuist op schijf hebt opgeslagen (afb. 8). De directie wil ook weten welke kortingsbedragen de vertegenwoordigers per klant totaal hebben verleend. Het resultaat dient er als volgt uit te zien (afb. 9). Je moet dus alle velden in de draaitabel vervangen door nieuwe.

Afb. 9 Buitendeur.xlsx: korting per klant en vertegenwoordiger

a Sluit het werkblad zonder op te slaan. Opmaak velden

De getallen in de draaitabel die je zojuist hebt opgeslagen (afb. 8) zien er slordig uit. Je verandert de opmaak van die getallen in 2 cijfers achter de komma en brengt een duizendtalscheidingsteken aan. 1 Open de werkmap Buitendeur.xlsx die je als laatste in de vorige paragraaf hebt opgeslagen. 2 Je zorgt ervoor dat je in de draaitabel een willekeurig getal van Netto bedrag hebt aangeklikt, zodat dit actief is. Wanneer het lint Opties actief is, dan is in de groep Actief veld de naam van het veld zichtbaar. In dit geval Netto bedrag (afb. 10).

13

14


Afb. 10 Buitendeur.xlsx: tabblad Opties

3 Klik op het tabblad Opties van het Lint in de groep Actief veld op de knop Veldinstellingen. Het dialoogvenster Waardeveldinstellingen verschijnt (afb. 11).

Afb. 11 Het dialoogvenster Waardeveldinstellingen

In dit dialoogvenster kun je bepalen hoe het veld in de draaitabel genoemd moet worden (Aangepaste naam), hoe het veld berekend moet worden (de tabs Samenvatten als en Waarden weergeven als) en hoe het eruit moet zien (knop Getalnotatie). 4 Klik op de knop Getalnotatie. Het bekende dialoogvenster van de Celeigenschappen verschijnt.

1

Draaitabellen

5 Kies voor categorie Getal en klik op het vakje Scheidingstekens voor duizendtallen (.) gebruiken en klik op de knop OK onderaan in het dialoogvenster van Celeigenschappen. 6 Je bent nu weer terug in het dialoogvenster Waardeveldinstellingen. Klik op de knop OK. 7 De draaitabel wordt nu met overzichtelijke getallen getoond, die allemaal zijn opgemaakt op dezelfde manier. Opmerking De getalnotatie is ook te wijzigen door: – één keer te klikken op het veld Som van Netto bedrag in het gebied Waarden;

Afb. 12 Buitendeur.xlsx: Waardeveldinstellingen

– de muisaanwijzer op een van de getallen in de draaitabel te plaatsen, vervolgens op de rechtermuisknop te klikken. In beide gevallen krijg je ook een keuzelijst te zien, waarin je kunt kiezen voor onder andere Waardeveldinstellingen.

15

16


Afb. 13 Buitendeur.xlsx: Waardeveldinstellingen

Opmaak tabel

Je kunt op veel manieren de opmaak van de gehele tabel wijzigen. Onder andere zijn kleuren te wijzigen, is veld Kopteksten te verwijderen en zijn knoppen in de draaitabel te verwijderen, (sub)totalen naar wens weer te geven, sortering van de diverse velden te definiëren. De meeste spreken voor zich. We beperken ons tot het aanpassen van de rapportindeling, het wijzigen van de sorteervolgorde en het wijzigen van de totalen. Aanpassen van de rapportindeling

Ga door waar je in de vorige paragraaf gebleven was. 1 Klik op het tabblad Ontwerpen van het Lint in de groep Indeling op de knop Rapportindeling (afb. 14). Een keuzelijst verschijnt. 2 Klik in de keuzelijst op Tabelweergave. Je ziet nu in de tabel vakken, de veldnamen (periode, land en artikel), de subtotalen per land onder de landen in plaats van erboven.

1

Draaitabellen

Afb. 14 Buitendeur.xlsx: rapportindeling wijzigen

Wijzigen van de sorteervolgorde

Je hebt waarschijnlijk gemerkt dat namen van de velden in de draaitabel automatisch op alfabet worden gesorteerd. Je verandert allereerst de sorteervolgorde van de landen van A..Z in Z..A. En hierna wijzig je de volgorde van de landen naar hoogste omzet. Sorteervolgorde van landen wijzigen in aflopend (Z..A): 1 Klik op een land in de draaitabel om het veld Land actief te maken. Je ziet in de lint Opties in de groep Actief veld de veldnaam Land. Als dit niet zichtbaar is, klik alsnog een land aan in de draaitabel.

Afb. 15 Tab Opties

2 Klik op het tabblad Opties van het Lint in de groep Sorteren op de knop Sorteren van Z naar A 3 Je ziet Nederland als eerste land in de lijst verschijnen. Sorteervolgorde van landen wijzigen in hoogste naar laagste omzet: 1 Klik op het tabblad Opties van het Lint in de groep Sorteren op de knop Sorteren. Het dialoogvenster Sorteren verschijnt (afb. 16).

17

18


Opmerking Het veld dat gesorteerd wordt, staat tussen haakjes vermeld in de titel van het dialoogvenster. In dit geval zie je dus Sorteren (Land).

Afb. 16 Het dialoogvenster Sorteren

2 Klik op de keuze Aflopend (Z tot A) op: de mogelijkheid wordt geboden om de onderliggende keuzelijst te wijzigen. 3 Klik op de keuzelijst Land die nu wit is. De keuzelijst verschijnt met in dit geval de keuze voor Land of Som van Netto bedrag. 4 Kies voor Som van Netto bedrag en klik op de knop OK van het dialoogvenster Sorteren (Land). De draaitabel wordt nu gesorteerd naar hoogte van de omzet per land. Frankrijk staat nu bovenaan. Opdracht: sorteren van artikelen naar omzet binnen de landen a Sorteer de artikelen naar omzet binnen de groepering van de landen. De artikelen met de hoogste omzet komen bovenaan.

b Om de artikelen te kunnen sorteren maak je eerst de artikelen actief door op een artikel in de draaitabel te klikken. Wijzigen van totalen

In dit onderdeel ontdoe je de draaitabel van zijn subtotalen en totalen. 1 Klik op het tabblad Ontwerpen van het Lint in de groep Indeling op de knop Subtotalen. Een keuzelijst verschijnt. 2 Kies voor Subtotalen niet weergeven. In de draaitabel verdwijnen alle subtotalen en alleen de eindtotalen blijven over. 3 Kies nu voor Eindtotalen vlak naast de Subtotalen. Een keuzelijst verschijnt. 4 Kies voor Uit voor rijen en kolommen. Nu verdwijnen ook de totalen uit de draaitabel.

1

Draaitabellen

Opdracht: totaliseren van rijen en kolommen a Geef nu de rijen weer subtotalen en totalen en totaliseer de kolommen weer. Jouw draaitabel moet er als volgt uitzien (afb. 17):

Afb. 17 Buitendeur.xlsx

b Bewaar het werkblad op schijf.

1.3 Filteren in een draaitabel In een draaitabel zijn bepaalde gegevens van een kolom of rij uit het overzicht te filteren, zodat ze niet meer zichtbaar zijn. Zo kun je heel simpel een kolom of een rij uit de tabel filteren. Een ingreep die iets verder gaat is het handmatig filteren van bepaalde waarden uit de kolommen. Nog verder gaat het filteren van gegevens op basis van de cijfers in het midden van de draaitabel, de waarden. Filteren van een gehele kolom of rij

Je legt een rapportfilter met het veld Periode. Hierdoor heb je geen perioden meer in de kolommen, maar kun je wel bepalen over welke perioden je de cijfers te zien wilt hebben. Ga verder met het werkblad uit de vorige paragraaf. 1 Sleep met de muis het veld Periode van het gebied Kolomlabels naar het gebied Rapportfilter. De draaitabel geeft nu de netto omzet per land, per artikel, zonder de verdeling naar periode. In de linkerbovenhoek van het werkblad zie je nu Periode.

19

20


2 Wanneer je op het pull-down-knopje van het veld Periode, linksboven, klikt, verschijnt een keuzelijst met de mogelijke waarden van het veld – in dit geval het aantal perioden. 3 Selecteer alleen Periode 1. Klik op de knop OK. Je ziet dat de bedragen aangepast zijn. De sortering van de landen en artikelen is ook veranderd. Nog steeds staat de hoogste netto omzet bovenaan (afb. 18).

Afb. 18 Buitendeur.xlsx: filteren op een kolom

4 Laat weer alle perioden zien. Dit doe je door in het filtervenster te kiezen voor alle categorieën. Alle bedragen en originele plaatsen zijn weer terug. Handmatig filteren van een aantal waarden in een kolom of rij

Je haalt de schoenen uit het overzicht door te filteren. Dit betekent dat de artikelen Runnershoes en Wandellaars niet meer in de draaitabel te zien zullen zijn. Je neemt hiervoor de draaitabel uit de vorige paragraaf. 1 Klik op de pull-down-knop in de kolom Artikel in de draaitabel. Een keuzelijst verschijnt (afb. 19).

1

Draaitabellen

21

Afb. 19 Buitendeur.xlsx: dialoogvenster Filteren open

2 Klik op Runnershoes en op Wandellaars. De vinkjes zijn hier nu weg. 3 Klik op de knop OK. Overal in de draaitabel verdwijnen de twee schoensoorten. Opmerking Doordat de twee artikelen uit de lijst ‘verdwenen’ zijn, is de rangschikking van hoge netto omzet naar kleine netto omzet veranderd. Dit wordt direct uitgevoerd in de draaitabel bij de gewijzigde situatie. Bij de kolom waarop een filter is toegepast zie je een trechtertje in de pull-down knop . Daaraan zie je dat je gegevens uitgefilterd hebt. Filteren op basis van de waarden

In afbeelding 19 zie je ook de mogelijkheid om te kiezen voor Labelfilters of Waardefilters. De keuze Labelfilters geeft een keuzelijst weer, waarbij je zelf het filter op de labelgegevens kunt opgeven op basis van vergelijkingen. Je maakt eerst een Labelfilter en daarna maak je een Waardefilter. 1 Klik op de pull-down-knop met het filterteken lijst verschijnt.

bij rijlabel Artikel. De keuze-

2 Ga met de muisaanwijzer op Labelfilters staan. Een andere keuzelijst verschijnt met mogelijke vergelijkingen die uit te voeren zijn om te filteren. 3 Kies uit deze keuzelijst voor Begint met. Het dialoogvenster Labelfilter (Artikel) verschijnt (afb. 20).

22


Afb. 20 Labelfilter

4 Vul in R. 5 Klik op de knop OK. Alleen de artikelen Runnershoes en Rugzak verschijnen in de draaitabel. Op basis van de naam van de rijlabels heb je nu bepaald wat er te zien valt. De keuze Waardefilters maakt het mogelijk om regels weg te laten op basis van de waarden in het gebied Waarden. In dit voorbeeld is het dus mogelijk om de regels van artikelen te laten verdwijnen op basis van de netto omzet. Eerst verwijder je het filter uit Artikel (begint met R) en dan zet je een waardefilter. Daarmee filter je alleen de twee best verkochte artikelen qua netto omzet en per land eruit. Voor het verwijderen van de artikelfilter klik je op de pull-down-knop met het filterteken bij rijlabel Artikel. De keuzelijst verschijnt. 1 Klik op de keuze Filter uit artikel wissen. Alle artikelen zijn weer zichtbaar in de draaitabel en het filterteken is weg uit de pull-down-knop. 2 Klik wederom op de pull-down-knop van het rijlabel Artikel voor het keuzemenu. 3 Breng de muisaanwijzer boven de keuze Waardefilters. Een keuzelijst verschijnt met de diverse filtermogelijkheden (afb. 21).

Afb. 21 Waardefilters

4 Klik op Top 10…. Het dialoogvenster Top 10-filter (Artikel) verschijnt (afb. 22).

1

Draaitabellen

Afb. 22 Top 10 waardefilter

6 Je verandert in het tweede vak de 10 in 2. 7 Klik op de knop OK. Per land zijn nu twee artikelen te zien die voor de hoogste netto omzet zorgen. Opmerking In het eerste invulvak kun je de hoogste (Top) of de laagste (Onder) selecteren. In het derde invulvak kun je de selectie op het aantal hoogste of laagste laten plaatsvinden (Items) op het percentage (Procent) of op de optelling (Som). Let er in dit geval op dat per land deze berekening telkens wordt uitgevoerd. 8 Je verwijdert de filter door op de pull-down-knop bij Artikel te klikken. De keuzelijst verschijnt. 9 Klik op de keuze Filter uit artikel wissen. Alle artikelen zijn weer zichtbaar in de draaitabel en het filterteken is weer weg uit de pull-down-knop. 10 Bewaar het werkblad op schijf. Opdracht: Top-5 beste klanten qua bruto omzet, gesorteerd van hoog naar laag Je maakt voor de directie een overzicht van de vijf klanten die het meest bijdragen aan de bruto omzet, gesorteerd van hoogste naar laagste. a Ga verder met de werkmap die je zojuist op schijf hebt bewaard. b Bouw het overzicht volgens afbeelding 23. – Verwijder velden uit de draaitabel en plaats gewenste velden in de draaitabel. – Vergeet niet te sorteren. Om de functie Sorteren in het tabblad Opties in het Lint te kunnen selecteren dien je het veld Klant in de draaitabel geactiveerd te hebben door erop te klikken. – Let ook op de opmaak van de waardevelden – hier hebben de bruto bedragen geen cijfers achter de komma.

Afb. 23 Buitendeur.xlsx: Top-5 klanten, hoogste omzet boven

23

24


Veranderen van de berekening van het veld in Waarden

Tot nu toe heb je de waarden gesommeerd. Soms kan het ook interessant zijn om te zien hoe vaak iets voorkomt of om de procentuele verhoudingen te kunnen zien. Draaitabellen voorzien in de mogelijkheid om meer dan alleen maar bedragen op te tellen (SOM). Je maakt nu met het werkblad uit de opdracht van de vorige paragraaf een overzicht van de top-5 klanten met hun procentuele bruto omzet en de bruto omzetbedragen in een kolom ernaast. 1 Klik in het gebied Waarden op Som van Bruto bedrag. Het dialoogvenster Waardeveldinstellingen verschijnt. 2 Klik op tabblad Waarden weergeven als.

Afb. 24 Dialoogvenster Waarden weergeven als

3 Klik op het uitschuifpijltje achter Standaard. Een keuzelijst verschijnt met mogelijkheden hoe het getal weer te geven. 4 Ga met de muisaanwijzer helemaal naar beneden in de keuzelijst totdat % van kolom verschijnt. Klik deze aan. Opmerking Zoals je kunt zien zijn ook andere manieren van representatie van het waardeveld te kiezen. % van kolom, % van rij en % van totaal zijn het meest gangbaar. % van rij verdeelt de procenten horizontaal en heeft nut om te gebruiken als er minstens één kolomlabel is gedefinieerd (zoals eerder met periode). % van totaal verdeelt de procenten over alle waarden van het veld in de draaitabel en heeft alleen zin als er zowel rij- als kolomlabels zijn gedefinieerd. 5 Klik vervolgens op de knop OK. De lijst van klanten met hun bruto omzet wordt nu procentueel weergegeven. Opmerking Alhoewel de draaitabel maar een gedeelte van de omzet bevat, verdeelt de draaitabel de omzet voor de volle 100 procent over alleen de 5 klanten. 6 Om ook de bruto bedragen naast de percentages te vermelden, sleep je het veld Bruto bedrag uit de Lijst met draaitabelvelden naar het gebied Waarden. Het

1

Draaitabellen

veld krijgt in het gebied Waarden en als label in de draaitabel de naam Som van Bruto bedrag2 (afb. 25). Opmerking De naam van de labels is te wijzigen door op het bewuste veld in het gebied te klikken, te kiezen voor Waardeveldinstellingen en de Aangepaste naam in te voeren.

Afb. 25 Buitendeur.xlsx: Top-5 klanten, omzet in procenten

Iedere kwartaal krijgt de verkoper die in een van de periodes procentueel netto het meest heeft omgezet een kleine attentie. Je maakt voor de directie een overzicht van de procentuele netto omzet (gebruik bij Waarden weergeven als % van totaal) van de verkopers over de drie perioden. 1 Ga verder met de huidige werkmap. 2 Bouw het overzicht volgens afbeelding 26. – Probeer te sorteren op de waarden, zodat het hoogste percentage boven staat. Dit is makkelijker leesbaar.

Afb. 26 Buitendeur.xlsx: procentuele omzet van de verkopers over de perioden

1.4 Onderliggende gegevens van de draaitabel raadplegen Je weet dat de totalen in een draaitabel zijn opgemaakt uit een onderliggende lijst van gegevens. De functie draaitabel groepeert de gegevens uit de lijst op basis van de criteria die aan de velden in de draaitabel worden meegegeven. Een getal in de cel waar de waarden staan is dus ontstaan aan de hand van een geselecteerd aantal regels in de onderliggende lijst. Wanneer je de gegevens van draaitabellen dieper analyseert, kan snel de behoefte ontstaan om te zien welke regels daaraan ten grondslag liggen. Bijvoorbeeld als er in een periode een uitschieter omhoog of omlaag wordt geconstateerd, is het handig om te weten wat er dan precies heeft plaatsgevonden.

25

26


Met Excel-draaitabellen heb je de mogelijkheid om eenvoudig de onderliggende regels te zien. Dit kun je doen door te dubbelklikken op de betreffende cel in het waardengedeelte in de draaitabel. Er wordt een nieuw werkblad gemaakt met daarin de regels die het getal in de bewuste cel hebben opgebouwd. Je gaat verder met het werkblad dat je in de voorgaande opdracht hebt gebouwd. Je bent nieuwsgierig waaruit de 24,99% van verkoper FW is opgebouwd. 1 Klik tweemaal (dubbelklik) op de cel met 24,99% in het waardegedeelte van de draaitabel. Een nieuw werkblad met alle onderliggende rijen wordt geopend. Excel heeft er automatisch een tabelstructuur aan gegeven (afb. 27).

Afb. 27 Buitendeur.xlsx: records waaruit de 24,99% van verkoper FW is opgebouwd

Opmerking Deze gegevens zijn nu afzonderlijk te analyseren. Je ziet dat er alleen regels zijn meegenomen uit periode 3 en van verkoper FW. De regels in het originele werkblad blijven netjes bestaan. Een grafiek maken van een draaitabel; een draaigrafiek

Een draaigrafiek is een grafiek gebaseerd op een draaitabel. De tabel en grafiek zijn gekoppeld. Wanneer de inhoud van een draaitabel wijzigt, wijzigt de bijbehorende draaigrafiek eveneens. De draaigrafiek is gezamenlijk met een nieuwe draaitabel te maken of achteraf, gebaseerd op een bestaande draaitabel. Draaigrafieken hebben dezelfde techniek als de standaardgrafieken in Excel. Je maakt allereerst een draaigrafiek op basis van een bestaande draaitabel. Later maak je een draaitabel samen met een nieuwe draaitabel.

1

Draaitabellen

Wanneer een draaitabel actief is, verschijnen de tabs Opties en Ontwerpen op het lint. Bij een draaigrafiek verschijnen vier tabs: Ontwerpen, Indeling, Opmaak en Analyseren. De eerste drie tabs komen bij de standaardgrafieken ook voor. De tab Analyseren is speciaal voor draaigrafieken (afb. 28). In deze tab vind je mogelijkheden om de gegevens van de draaigrafiek te manipuleren. Later wordt hierop ingegaan.

Afb. 28 Tab Analyseren

We willen grafisch zien hoeveel stuks van de artikelgroepen per klantengroep verkocht worden. Het is in dit geval eenvoudiger om vanuit de basisgegevens te starten. Daarom maak je vanuit de basislijst met gegevens (Blad 1) de draaigrafiek aan. 1 Je klikt in het werkboek Buitendeur.xlsx op Blad 1 om in de originele lijst met gegevens te komen. 2 In het lint klik je op de tab Invoegen en in de groep Tabellen klik je op het uitschuifpijltje bij de knop Draaitabel. De keuze wordt geboden voor een draaitabel of een draaigrafiek. 3 Kies voor draaigrafiek. De gegevens die meegenomen moeten worden zijn weer met bewegende streepjes omcirkeld en het dialoogvenster Draaitabel maken met draaigrafiek verschijnt. 4 Alles is standaard goed ingevuld, dus je hoeft niets aan te passen. Je klikt op de knop OK. Een nieuw werkblad met de werkset voor draaitabellen en draaigrafieken verschijnt (afb. 29).

27

28


Afb. 29 Buitendeur.xlsx: aanmaken van een draaigrafiek

Opmerking Het kan zijn dat je in eerste instantie niet het middelste venster Deelvenster voor filter van draaigrafiek te zien krijgt. Om dit venster op het scherm te krijgen, moet je in het lint op de tab Analyseren klikken. Je kunt dan in de groep Weergeven/verbergen met de knop Filter van draaigrafiek het deelvenster op het scherm krijgen. Klik je nog een keer, dan is het deelvenster weer weg. Hetzelfde kun je doen met de knop Lijst met velden voor het deelvenster uiterst rechts. 5 In het deelvenster Lijst met draaitabelvelden klik je op het veld Klantgroep. Het veld Klantgroep is nu aangevinkt en verschijnt ook in het gebied Asvelden (categorieën). Opmerking Je ziet dat in het geval van draaitabellen het gebied Asvelden anders wordt genoemd, namelijk Rijlabels. Het gebied Legendavelden (reeks) heet Kolomlabels bij draaitabellen. Dus een rijlabel van een draaitabel wordt in een draaigrafiek een categorie, een Kolomlabel wordt een reeks. 6 In het deelvenster Lijst met draaitabelvelden klik je op het veld Artikelgroep. Dit veld komt in het gebied Asvelden (categorieën) onder Klantgroep te staan. De grafiek laat nog niets zien. De draaitabel laat wel de artikelgroepen per klantgroep zien. 7 Klik op het veld Aantal. Dit veld wordt automatisch in het gebied Waarden geplaatst. Niet alleen wordt de draaitabel bijgewerkt, maar nu zie je ook de grafiek (klik anders het deelvenster in het midden weg en ‘schuif ’ het andere deelvenster opzij). Als alles goed is gegaan krijg je ongeveer afbeelding 30 te zien.

1

Draaitabellen

Afb. 30 Buitendeur.xlsx: draaigrafiek

Bovenaan in het lint kun je de tabs Ontwerpen en Indeling gebruiken om de grafiek op allerlei manieren aan te passen. Speciaal voor draaigrafieken ten opzichte van gewone grafieken is dat je de gegevens in de grafiek kunt filteren en sorteren, op dezelfde manier zoals je dat al kent van de draaitabel. 8 Als je in het lint op de tab Analyseren klikt en vervolgens in de groep Weergeven/verbergen op de knop Filter van draaigrafiek, dan verschijnt weer het Deelvenster voor filter van draaigrafiek in het midden van het werkblad. Als je onder Asvelden (categorieën) op de pull-down knop achter Klantgroep klikt, krijg je de bekende keuze om te sorteren of filteren. 9 Je wilt alleen de resultaten van het binnenland laten zien. 10 Klik Alles selecteren aan. Alle vinkjes zijn nu weg. 11 Klik op Binnenland en op de knop OK. 12 Klik het deelvenster weg, zodat de grafiek weer zichtbaar is. Zowel de grafiek als de draaitabel laten alleen gegevens van klantgroep Binnenland zien. Je ziet dat het rechtstreeks maken van een draaigrafiek net zo eenvoudig is als het maken van een draaitabel.

29

30


Een draaigrafiek van een draaitabel

Je bouwt nu een grafiek op basis van een al bestaande draaitabel. In Blad 4 staat de draaitabel met de procentuele verdeling van de netto omzet van de verkopers over de periodes. Bij Buitendeur heeft men de gewoonte om zoveel mogelijk grafisch weer te geven: een plaatje zegt meer dan veel cijfers. De procentuele omzet per vertegenwoordiger verdeeld over de perioden wordt in grafiekvorm in de kantine opgehangen. Jij maakt daarvoor de grafiek. Je hebt de draaitabel al. Het enige dat je hoeft te doen is deze in grafiekvorm weergeven. 1 Klik in werkboek Buitendeur op Blad4. In dit werkblad staat de draaitabel van de verkopers met hun procentuele omzet over de perioden. 2 Klik in het lint op de tab Opties. Als de tab niet te zien is, moet je eerst op de draaitabel in het werkblad klikken om de hulpmiddelen voor de draaitabel actief te maken. 3 Klik in de groep Extra op de knop Draaigrafiek. Het dialoogvenster Grafiek invoegen verschijnt. 4 Kies voor de eerste grafiek in de categorie Kolom en klik op de knop OK. 5 De grafiek verschijnt in het werkblad op de draaitabel. 6 Klik het Deelvenster voor filter van draaigrafiek weg om de grafiek beter te zien. 6 Sleep de grafiek van de draaitabel, zodat het overzichtelijk wordt. Dit kun je pas doen als je de muisaanwijzer over de grafiek beweegt en de aanwijzer er als . volgt uitziet: 7 Houd de muis ingedrukt en verplaats de grafiek naar een lege plaats (afb. 31).

Afb. 31 Buitendeur.xlsx: grafiek verplaatsen

1

Draaitabellen

Print de grafiek. Als je eerst de grafiek actief maakt door met de muis erop te klikken, krijg je bij het afdrukken alleen de grafiek.

1.5 Extra opdrachten Opdracht: VerleaseMeNiet – Aantallen Je bent net aangenomen bij het leasebedrijf VerleaseMeNiet. Hoofd communicatie, Lies Breuk, wil als extra service aan de klanten diverse autogegevens op het web plaatsen. Dat komt goed uit, want je weet hoe je op een eenvoudige manier analyses kunt maken met Excel en zo leer je ook wat van de auto’s die de firma least. Er is al een lijst met diverse merken en modellen. Dus je hoeft eigenlijk alleen maar een aantal draaitabellen te maken. Dat is iets waar men vroeger dagen op zat te zwoegen. Maar jij weet nu wel beter. a Open de werkmap VerleaseMeNiet uit de map Rekenbladen op je schijf. b Ten eerste wil Lies graag een overzicht van je van het aantal mogelijkheden per merk dat VerleaseMeNiet momenteel aanbiedt. Het resultaat ziet eruit als afbeelding 32. – Wanneer je in het werkblad van de gegevens op werkblad klikt wordt automatisch het waardebereik bepaald. Hierover hoef je je niet te bekommeren. – Je krijgt aantallen van de merken door merk niet alleen als rijlabel in te voeren, maar ook als waarde.

Afb. 32 VerleaseMeNiet.xlsx: aanbod lease-auto’s

c Het is maar een kleine stap om hiervan nu een mooie cirkelgrafiek van te maken. Ga je gang!

31

32


– Mocht je in de grafiek niet alle merken in de legenda (het lijstje met kleurtjes en merken aan de linkerkant) te zien krijgen, dan kun je de grafiek aan zijn hoekpunt uittrekken. Opdracht: VerleaseMeNiet – Filteren a De volgende stap is om te weten welke modellen per merk er zijn, hoeveel er van ieder model worden aangeboden. Je filtert er één m erk uit, bijvoorbeeld VW. – Je gaat uit van de draaitabel die je in de vorige opdracht gemaakt hebt. – Je vindt filters zetten door op de pull-down-knop te klikken. De knop vind je naast de rijlabels in de draaitabel.

Afb. 33 VerleaseMeNiet – filteren

Opdracht: VerleaseMeNiet – Gemiddelden a De volgende stap is dat Lies naast de aantallen ook de gemiddelde verkoopprijs wil zien. De bedragen dienen te zijn afgerond op hele euro’s en voorzien te zijn van een duizendtal-scheidingsteken (afb. 34). – Je gaat weer uit van de draaitabel die je al gemaakt hebt. – Verwijder de eerder gemaakte grafiek.

1

Draaitabellen

Afb. 34 VerleaseMeNiet – gemiddelden

Opdracht: VerleaseMeNiet – Sorteren a Om de draaitabel nog leesbaarder te maken vraagt Lies om de modellen te sorteren naar gemiddelde prijs (afb. 35). – Je gaat met de bestaande draaitabel aan de slag. – Dit lijkt makkelijk, maar dat is het niet. Je komt op deze manier namelijk niet bij het sorteren van het model, het tweede veld in de rijlabel. Je kunt bij het sorteren van model komen, door de Rapportindeling te wijzigen naar Tabelweergave. Dan is de pull-down-knop van het model in de draaitabel te benaderen. Ga je gang!

33

34


Afb. 35 VerleaseMeNiet – sorteren

Opdracht: VerleaseMeNiet – Allerlei a Nu wil Lies graag laten zien wat per merk/brandstofsoort het gemiddelde verbruik is, zodat men per merk een globaal overzicht krijgt hoe zuinig een auto is. Je maakt een draaitabel zoals je ziet in afbeelding 36. – Let er op om voor de waarden gemiddelden te nemen. – Vergeet niet de getallen op te maken met 2 cijfers achter de komma. – Sorteer de automerken op volgorde van verbruik gecombineerd (laatste kolom van verbruik), de zuinigste bovenaan.

Afb. 36 VerleaseMeNiet – allerlei

1

Draaitabellen

35

b Maak een grafiek van de draaitabel. Je kiest in dit geval voor lijngrafiek. Je ziet dat de grafiek moeilijk te lezen is, omdat er te veel gegevens op staan. Filter alleen op Brandstof = ongelood. De grafiek is nu veel sprekender en verschillen tussen de merken zijn nu makkelijker te zien. Nadeel is wel dat je slechts een overzicht van één brandstofsoort in de grafiek hebt. – In het Deelvenster voor filter van draaigrafiek kun je door op de pull-downknop naast Brandstof te klikken filteren op Ongelood. – Geef in deze gefilterde grafiek met tekst aan dat je alleen een overzicht geeft van het verbruik van ongelode benzine. Je doet dit door op de tab Indeling van het lint op het uitschuifmenu Invoegen te klikken. Je kiest dan voor tekstvak en je plaats dit rechtsboven in de grafiek. Je plaatst hier de tekst.

→

Opdracht: VerleaseMeNiet – groepselectie a Om klanten inzicht te geven wat een milieubewuste keus kan zijn, afhankelijk van het aantal pk’s, maak jij een overzicht van de CO2-uitstoot van alle merken uitgezet tegen het aantal pk’s. Uiteraard hangt de CO2-uitstoot ook af van de brandstofsoort. Maak van brandstof een rapportfilter. In de kolommen zet je de merken en in de rij de pk’s. De waarden zijn de gemiddelde CO2-uitstoot. – Schrik niet; je krijgt een gigantische, onleesbare draaitabel. Dit komt door: » De getallen van de CO2-gemiddelden in het waardengebied. Via de getalnotatie kun je die makkelijk als getal op 0 decimalen zetten. » Alle mogelijke pk’s worden in de rijlabel getoond. Prettiger zou het zijn als ze per bundel gegroepeerd in een interval worden weergegeven, bijvoorbeeld op 1 regel 55-65, op de volgende 65-75, enzovoort. Dan wordt de lijst aanmerkelijk kleiner. Om dit voor elkaar te krijgen ga je als volgt te werk: 1 Klik op een waarde van de rijlabels in de draaitabel, met andere woorden, kies een pk-waarde in de draaitabel. 2 De draaitabel is actief. Klik op de tab Opties van het lint en in de groep Groeperen op de knop Groepselectie. Het dialoogvenster Groeperen verschijnt (afb. 37).

36


Afb. 37 Dialoogvenster Groeperen

3 Je kunt aangeven hoe groot het interval moet worden (Op) en waar het interval moet beginnen en waar eindigen. 4 Een interval van 10 lijkt in orde. Klik op de knop OK. Je ziet nu een veel duidelijker draaitabel. – Verander om de leesbaarheid te verder vergroten de Rapportindeling in Overzichtsweergave en verander eventueel de Draaitabelstijl. – Vergeet het rapportfilter niet om maar één brandstofsoort te zien. Als je dit af hebt, mag je tevreden naar het resultaat kijken (afb. 38).

Afb. 38 VerleaseMeNiet – groeperen, filteren en opmaak

excel in de financiële praktijk

Recommend Documents