Cursus Excel draaitabellen

Excel is een fantastisch mooi programma. Een programma dat je zonder al te veel aanwijzingen al kunt gebruiken. Dit voordeel is gelijk ook het nadeel. Wil je namelijk meer voordelen van Excel kunnen benutten, dan is bijvoorbeeld een cursus Excel draaitabellen wel erg handig.

Excel draaitabellen. Je hebt ze vast wel eens gezien, je hebt er wellicht ook wel eens iemand mee zien werken en heel misschien heb je zelf ook wel eens een poging gewaagd. Draaitabellen in Excel zijn voor veel mensen namelijk echt een struikelblok. Maar na de cursus Excel draaitabellen wil je niet meer zonder.

Een van de grote voordelen van draaitabellen is het vermogen om snel inzicht te verschaffen in de inhoud van grote datasets. Bovendien kun je resultaten eenvoudig bijwerken als onderliggende gegevens veranderen. Het toevoegen, verwijderen of wijzigen van gegevens kun je namelijk met een enkele muisklik doorvoeren.

Cursus inhoud

• Tabellen
• Voorbereiding op draaitabellen
• Draaitabellen maken
• Werken met slicers

Online cursus Excel draaitabellen

De online cursus bestaat uit 4 lessen. Iedere les bestaat uit een video, waarin je les krijgt. Tijdens de les heb je zicht op het onderwerp van de les. Voorafgaand aan de les kun je de bijlage bij de les downloaden, zodat je tijdens het bekijken van de les meteen mee kunt doen.

Na iedere les is er oefenmateriaal beschikbaar, die je kunt gebruiken om te oefenen. Afhankelijk van het thema is, kun je tevens de antwoorden / oplossingen van het oefenmateriaal downloaden.

Kom je er niet uit dan, kun je bellen, mailen of appen met de docent die je in de betreffende lessen ziet.

Het voordeel van de online cursus is dat je direct toegang hebt tot de online leeromgeving en je zelf bepaalt waar, wanneer en in welk tempo je de cursus volgt. Door het in delen te volgen zorgt dit bovendien voor een betere integratie van de kennis en vaardigheden.

Klassikale cursus Excel draaitabellen

Kleine groepen van maximaal 10 deelnemers op 7 locaties in Nederland. Dat wil zeggen persoonlijke aandacht voor iedereen. Met de klassikale cursus maak je in één dag een grote sprong in je kennis en vaardigheden.

De training is geschikt voor alle versies van Excel. In de training werken we met de nieuwste versie van Excel. (voor Mac gebruikers zal de docent eventuele verschillen benoemen en/of laten zien in de cursus)

Hieronder hebben we een typische tabel met gegevens.

We moeten de volgende vragen over onze producten beantwoorden:

1. De totale verkoop voor elk product.
2. De totale verkoop voor alle producten.
3. De rangorde van de meeste verkopen tot de minste verkopen per product.

We moeten de volgende vragen over onze klanten beantwoorden:

1. De totale omzet voor elke klant.
2. Het percentage van de omzet voor elke klant.

Het beantwoorden van deze vragen zal net zo eenvoudig zijn als het spelen van een spelletje computer Patience, maar in een klein deel van de tijd.

Maak deze beginnersfouten niet

Voordat je begint met het maken van de draaitabel, moet je ervoor zorgen dat je brongegevens voldoen aan bepaalde regels.

• De gegevens moeten in een tabelstructuur staan; kolommen zijn categorieën en rijen zijn transacties (records).
• De tabel bevat geen lege rijen of kolommen.
• De tabel bevat geen rijen voor subtotalen of eindtotalen.
• Elke kolom heeft een unieke kop en de koppen moeten in één cel staan.

De draaitabel maken

Voer de volgende stappen uit om een ​​draaitabel te maken:

1. Klik op een cel die deel uitmaakt van je dataset.
2. Selecteer Invoegen (tabblad) -> Tabellen (groep) -> Draaitabel.

3. In het dialoogvenster Draaitabel maken zie je dat het geselecteerde bereik hard is gecodeerd in een bepaald aantal rijen en kolommen.

Het potentiële probleem is dat de draaitabel niet verder kijkt dan rij 108. Dit zou namelijk de situatie zijn als we een nieuwe rij met gegevens aan de tabel toevoegen.

De draaitabel dynamisch maken

Om de draaitabel de mogelijkheid te geven om nieuwe rijen gegevens te lezen, moeten we de originele tabel converteren naar een goede Excel tabel. Door dit te doen, kijkt de draaitabel namelijk naar de kolom voor gegevens en niet naar een bepaald aantal rijen.

Annuleer het dialoogvenster Draaitabel maken en laten we een paar stappen teruggaan.

1. Converteer de tabel naar een Excel-tabel door op CTRL-L te drukken en klik op OK in het dialoogvenster Tabel maken.
2. Geef de tabel een eigen naam door Tabelontwerp (tabblad) -> Eigenschappen (groep) -> te selecteren en bewerk de standaardnaam (hoogstwaarschijnlijk “Tabel1” in iets zinvoller, zoals “Verkoop”.

OPMERKING: Deze laatste stap is optioneel, maar het zal je helpen om tabellen gemakkelijker bij te houden.

Laten we nu teruggaan naar het maken van de draaitabel.

4. Selecteer Tabelontwerp -> Extra (groep) -> Samenvatten met draaitabel.

Merk op hoe onze bereikselectie niet langer vastzit aan een vast aantal rijen en kolommen. De selectie kijkt naar de tabel met de naam “Verkoop”.

5. Klik in het dialoogvenster Draaitabel maken op OK.

We krijgen een lege schil van een draaitabel (links) te zien, samen met een sleep interface genaamd de veldlijst voor het invullen van het rapport (rechts).

De interface Veldlijst kan worden aangepast door de versnellingsknop te selecteren.

Je kunt de zones met slepen en neerzetten rangschikken en je veldnamen sorteren en doorzoeken.

De veldenlijst geeft een lijst weer van de koppen uit de brongegevens.

Opmerking

De cursor MOET zich binnen de grenzen van de draaitabel bevinden om de bijbehorende lintfuncties weer te geven. Je kunt de lijst met velden ook verbergen/weergeven door PivotTable Analyze -> Show -> Field List te selecteren.

Om te beginnen met het beantwoorden van onze vragen over klanten:

6. Houd het item Bedrijfsnaam in de lijst met velden ingedrukt en sleep het binnen de rijen
7. Houd het item Verkoop EUR in de lijst met velden ingedrukt en sleep het binnen de waarden

We krijgen het volgende rapport gepresenteerd.

De standaard weergave is om de verkopen op te tellen. Als je liever aantal of gemiddelde wilt, klik je met de rechtermuisknop op een verkoopwaarde in het rapport. En selecteert je “Waarden samenvatten op …” en klik je op de gewenste formaat.

De resultaten opmaken

Als je je berekeningsresultaten in een ander formaat wilt weergeven? Zoals Valuta of Percentage. Dan klik je met de rechtermuisknop op een verkoopwaarde in het rapport en selecteer je “Getalnotatie…”. Hiermee ga je rechtstreeks naar het gedeelte voor getalopmaak van het dialoogvenster Cellen opmaken.

BELANGRIJK: Selecteer NIET “Cellen opmaken” in het rechtsklikmenu. Hierdoor worden namelijk alleen de geselecteerde cellen opgemaakt, niet de rapportresultaten. Het voordeel van Getalnotatie is dat de opmaak wordt uitgebreid naar nieuwe vermeldingen naarmate de gegevens groeien. Dit in tegenstelling tot het gebruik van Cellen opmaken.

Globale filtering van het rapport

De lijst met velden bevat een zone met de naam Filters. Elk veld dat hier wordt geplaatst, wordt toegepast in de linkerbovenhoek van het rapport en zorgt voor een filter voor het hele rapport.

Elk filter dat in de zone Filters wordt gebruikt, is van toepassing op de hele tabel.

Rapportvelden herschikken

Als je liever elke regio in het rapport als afzonderlijke aggregaties wilt weergeven, maar je niet op elke regio wilt filteren, kun je het veld Regio uit de zone Filters slepen en in de zone Kolommen neerzetten.

Een rapport op meerdere niveaus maken

Als je een hiërarchisch rapport moet maken waarin een “bovenliggend” item wordt opgesplitst in “onderliggende” items, plaats je het benodigde veld in dezelfde zone als een bestaand veld.

OPMERKING: De stapelvolgorde in de zone bepaalt de ouder-kindrelatie.

De hiërarchie maakt automatisch subtotalen aan voor elk item op bovenliggend niveau.

De opmaak van de draaitabel aanpassen

Er zijn tientallen aanpasbare opties beschikbaar voor draaitabellen. Enkele van de meer algemeen gebruikte functies zijn (vanaf het tabblad Ontwerpen):

• Subtotalen in- of uitschakelen
• Eindtotalen in- of uitschakelen
• De indeling van de tafel wijzigen
• Lege rijen tussen bovenliggende rijen toevoegen of verwijderen
• Labels op bovenliggend niveau herhalen naar rijen op onderliggend niveau
• Een vooraf gedefinieerd kleurenschema toepassen
• Kleur afwisselende rijen, kolommen en koppen

We raden je aan te experimenteren met verschillende combinaties van cosmetische functies om visueel interessante rapporten te maken.

Hiërarchieën samenvouwen en uitbreiden

Wanneer je een ouder-kindrelatie tussen velden aanmaakt, krijg je toegang tot plus en min-knoppen links van elk item op ouderniveau.

Hiermee kun je gedetailleerde informatie op kindniveau over geselecteerde items weergeven, terwijl samenvattingsinformatie over andere items op bovenliggend niveau wordt weergegeven.

Deze knoppen kunnen worden verborgen of opnieuw worden weergegeven door Draaitabel analyseren -> Tonen (groeperen) -> +/- knoppen te selecteren.

Een veld uit de lijst met velden verwijderen

Als je een bepaald veld niet langer in een zone met veldenlijst wilt hebben, kun je ofwel de veldnaam van de zone terug naar de hoofdveldlijst slepen, of je kunt de vereiste veldnaam in de hoofdveldlijst deselecteren.

De standaardkoppen bijwerken

De automatische koppen zijn meestal minder dan wenselijk. Je kunt in de kopcel klikken en een meer beschrijvende kop typen.

LET OP: draaitabellen houden er niet van wanneer je een kop een naam geeft die overeenkomt met een bestaande kop. Een truc is om een ​​spatie toe te voegen voor of na de aangepaste kop om het conflict te voorkomen.

De rapportresultaten sorteren

De standaard sorteervolgorde is op de rijkop, maar rapportlezers zijn doorgaans geïnteresseerd in zaken als ’toppresteerders’ of ‘minste incidenten’. We kunnen het rapport eenvoudig sorteren door met de rechtermuisknop op een aggregatiewaarde te klikken en Sorteren -> Sorteren XX tot YY te selecteren.

Automatisch formaat wijzigen op basis van inhoud

De draaitabel zal automatisch de grootte van de kolommen wijzigen om ervoor te zorgen dat er geen gegevens visueel worden afgekapt. In de meeste gevallen is dit fijn. Als je echter het formaat van je kolommen hebt aangepast naar een formaat naar keuze, verlies je die afmetingen waarschijnlijk bij de volgende update. Als je deze automatische handeling voor het wijzigen van de kolomgrootte wilt deactiveren, selecteer je Draaitabelanalyse (tabblad) -> Draaitabel (groep) -> Optie.

Schakel in het dialoogvenster Draaitabelopties de functie “Kolombreedten automatisch aanpassen bij bijwerken” uit.

Aanvullende aggregaties maken

Als je je dat herinnert, was een van onze vragen die beantwoord moesten worden, welk percentage van de totale verkoop elke klant beslaat?

We kunnen dit percentage in een paar klikken bepalen zonder een enkele formule te schrijven.

1. Voeg het veld Verkoop EUR een tweede keer toe aan de zone Waarden. Plaats deze onder de oorspronkelijke invoer.

2. Klik met de rechtermuisknop op een van de waarden in de nieuw toegevoegde kolom en selecteer “Waarden weergeven als” -> “% van het eindtotaal”.

Neem even de tijd om de lijst ‘Waarden weergeven als’ te bekijken. Er is hier namelijk een GOUDMIJN van wiskundige rijkdom te vinden.

We hebben nu een volledig rapport dat al onze vragen van klanten beantwoordt.

Het rapport bijwerken

Wanneer de onderliggende gegevens veranderen (toevoegingen, verwijderingen of wijzigingen), moet je het rapport “vernieuwen” om de gewijzigde gegevens weer te geven.

Er zijn veel manieren om het draaitabelrapport te vernieuwen.

• Klik met de rechtermuisknop op het draaitabelrapport en selecteer Vernieuwen.
• Selecteer Draaitabel analyseren (tabblad) -> Gegevens (groep) -> Vernieuwen.
• Selecteer vervolgens Gegevens (tabblad) -> Query’s en verbindingen (groep) -> Alles vernieuwen.

Ons productenrapport maken

Een truc bij het maken van een tweede rapport is om het eerste rapport te dupliceren en vervolgens de toegewezen velden aan te passen.

1. Markeer het originele draaitabelrapport.
2. Druk op CTRL-C op het toetsenbord.
3. Klik in een lege cel op een nieuw blad of aan de zijkant van een bestaande draaitabel (zorg ervoor dat u voldoende lege kolommen overlaat om het oorspronkelijke rapport indien nodig te laten ‘groeien’.)
4. Druk op CTRL-V om een ​​kopie van de draaitabel te plakken.
5. Verwijder alle onnodige veldnamen uit de neerzetzones en voeg tevens de benodigde veldnamen toe.
6. Sorteer indien nodig de nieuwe draaitabel op waarden.

Een voordeel van het kopiëren van een bestaand rapport is dat alle cosmetische instellingen zoals: kleur, getalnotatie, totalen, enz. behouden blijven in het dubbele rapport.

Slicers toevoegen om filteren te versnellen

Als je Slicers hebt gebruikt, weet je hoe eenvoudig filteren wordt uitgevoerd in tegenstelling tot ouderwetse vervolgkeuzefilters. We kunnen ook slicers toevoegen aan draaitabellen om het filterproces te versnellen. Bovendien is het leuk en ziet het er cool uit.

Het belangrijkste voordeel voor snijmachines:

In tegenstelling tot filters die zijn ingebouwd in de draaitabel, kunnen slicers filteren op ELKE categorie in de dataset.

Waar traditionele filters alleen kunnen filteren op wat er in het rapport staat, manipuleren slicers de ‘back-end’-gegevens, die vervolgens worden overgedragen naar de draaitabel.

Slicers toevoegen aan draaitabelrapporten:

1. Klik op een cel in een draaitabel.
2. Selecteer Draaitabel analyseren (tabblad) -> Filter (groep) -> Slicer invoegen.

3. Selecteer in het dialoogvenster Slicer invoegen de categorieën die u wilt filteren.

We krijgen een slicer(s) voor de item(s) te zien.

De slicer heeft alleen invloed op de draaitabel die was geselecteerd toen de slicer werd gemaakt. Als je de slicer nodig hebt om meerdere draaitabellen te filteren:

4. Selecteer de slicer.
5. Kies Slicer (tabblad) -> Slicer (groep) -> Rapportverbindingen.

6. Selecteer in het dialoogvenster Rapportverbindingen zoveel (of zo weinig) draaitabellen die je nodig hebt om het gewenste gedrag te bereiken.

Alternatieve manier om draaitabellen te maken

Als je in de gegevensbron klikt, kun je vervolgens Invoegen (tabblad) -> Tabellen (groep) -> Aanbevolen draaitabellen selecteren.

Excel heeft een geweldige functie waarmee je met één klik voltooide draaitabellen kunt maken. Excel analyseert namelijk je gegevens en presenteert verschillende voorgestelde tabelontwerpen om de meest voorkomende vragen over je gegevens te beantwoorden. Deze kunnen de exacte tafel leveren die je zocht. Als dat niet het geval is, kunnen ze op zijn minst een snellere start bieden om je doel te bereiken. Als je een van de voorgestelde tabellen niet leuk vindt, kun je op Lege draaitabel in de linkerbenedenhoek klikken en met een schone lei beginnen.