In sommige situaties is het handig om niet alleen het CBS als bron te noemen maar om ook de onderliggende info in je eigen analyse over te nemen en/of te gebruiken.
Er zijn verschillende methoden om die cijfers in Excel te krijgen. Hierna zullen we er enkele bespreken: allereerst het simpelweg kopiëren, daarna het op verschillende manieren downloaden van cijfers.
In een volgend artikel zullen we ook het gebruik van de Open Data-optie bespreken, waarmee het ophalen van cijfers verder gestandaardiseerd en geautomatiseerd kan worden.
Aanleiding
Aanleiding voor dit artikel was een verhaal in de Volkskrant van 3 mei jl. Dat ging over de resultaten van een rapport van het CBS, De arbeidsmarkt in cijfers 2022.
Bij het lezen van het artikel en het rapport kreeg ik de behoefte om op bepaalde onderwerpen wat verder in te zoomen. Daarvoor had ik wel de onderliggende cijfers nodig.
Een samenvatting met de belangrijkste resultaten van het CBS-onderzoek was snel gekopieerd:
selecteer de gewenste gegevens op de website
kies Kopiëren, Ctrl-C
selecteer de cel in Excel waar de linkerbovenhoek moet komen
kies Plakken, Ctrl-V
op de juiste plaatsen een lege regel toevoegen, de kopjes vet maken en de onderliggende items laten inspringen
We gaan nu de gegevens niet kopiëren, maar maken een download. Kies de button
open de download door in de Verkenner in de Download-map op het bestand te dubbelklikken (het bestand heeft in dit geval de naam table__85264NED.csv). In de Chrome-browser komt de naam van de download ook onder in het scherm te staan; de download kun je dan openen door daar te klikken. NB wordt het bestand niet in Excel geopend dan moet je er voor zorgen dat de extensie CSV gekoppeld is aan Excel of de optie Openen met gebruiken
alle gegevens komen in één kolom terecht. Die moeten we dus nog ‘knippen’.
selecteer alle betreffende cellen in de kolom.
kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Tekst naar kolommen.
zorg dat in het vervolgscherm de optie Gescheiden is aangevinkt. Klik op de button Volgende.
zorg dat Komma is aangevinkt en kies Voltooien
Alle gegevens, zoals ze in de tabel op het scherm stonden, worden in een overzichts-vorm aangeleverd.
Wat als we meer en andere gegevens er bij willen hebben?
Download 2
Door op de web-pagina de optie te klikken kom je vanzelf in het Statline-gebied van het CBS, waar je veel meer opties hebt om gegevens te kiezen.
Maar we gaan eerst eens kijken wat er gebeurt als je nu Download kiest: Kies de optie CSV volgens tabindeling.
Open de download; het resultaat zal direct in Excel getoond worden (zie het tabblad Download2 in het Voorbeeldbestand):
Je kunt allerlei filteringen aan of uit zetten. Maar je kunt ook onderdelen verplaatsen.
Wanneer je bijvoorbeeld de button Geslacht met de muis ‘vastpakt’ kun je die verplaatsen naar het rijengebied:
Plaats Geslacht boven Onderwerp en zorg bij de filtering dat alle opties zijn aangevinkt
Kies weer de Download-button en open het bestand:
Na wat oefening zul je merken dat je op deze manier redelijk snel gewenste gegevens in Excel kunt overnemen. Een nadeel is dat er veel ‘handwerk’ bij komt kijken en dat de lay-out vaak niet direct geschikt is om verschillende analyses uit te voeren. En als de cijfers door het CBS worden geactualiseerd zullen alle handelingen opnieuw moeten worden gedaan!
In het volgende artikel zullen we dan ook het gebruik van de zogenaamde Open-Data-optie van het CBS en Excel bespreken.
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
Bij een analyse van de populariteit van diverse kindernamen gebruikte ik (uiteraard) draaitabellen. Bij het sorteren realiseerde ik me weer dat daar bij een draaitabel wel wat haken en ogen aan zitten, maar dat deze optie ook extra mogelijkheden biedt. Een mooie aanleiding voor een nieuw artikel.
Brongegevens
Gelukkig hoeven we voor een overzicht van namen, die in de loop van de jaren aan kinderen zijn gegeven, niet zelf op onderzoek uit; al jaren publiceert het SVB deze. Op de website svbkindernamen.nl kunnen we de namen van de laatste 5 jaar terugvinden.
NB om te vermijden dat namen naar personen kunnen worden herleid, publiceert het SVB namen, die minder dan 25 keer voorkomen, niet.
Met wat kopiëren en plakken kunnen we de overzichten van de site snel overhevelen naar Excel. Het tabblad Data van het Voorbeeldbestand bevat alle namen van de laatste 5 jaar met het aantal keren dat ze toegekend zijn en de rangorde in het betreffende jaar; de gegevens zijn aangevuld met 2 extra kolommen: het jaar en of het een jongens- of meisjesnaam is.
De gegevens staan in een Excel-tabel tblData. Zoals te zien is, zijn nog enkele hulpkolommen en -cellen toegevoegd:
in cel C3 (met de naam MaxJr) wordt het hoogste jaar opgehaald. Tik in cel C3 in: =max( en wijs dan met de muis de bovenrand van cel B6 aan (het muisteken wordt een pijltje naar beneden) en geef een muisklik. Excel vult zelf de formule aan: =max(tblData[Jaar] Druk op Enter.
op een vergelijkbare manier bevat cel C4 (met de naam MinJr) het laagste jaar
in de nieuwe kolom Lengte wordt het aantal letters van de naam bepaald: =LENGTE([@Voornaam]) De functie Lengte berekent hier de lengte van de naam in de kolom Voornaam in deze tabel (aangeduid door de rechte haken) en wel de cel in de overeenkomende rij (aangeduid door de @). NB je hoeft dit soort notaties niet te onthouden; klik bij het invoeren van de formule op de gewenste cel en Excel vult alles automatisch in.
de beginletter van de naam vinden we met de volgende formule: =LINKS([@Voornaam]) Om te zorgen dat Ömer ook onder de O komt is de gebruikte formule iets uitgebreid: =ALS(LINKS([@Voornaam])=”Ö”; “O”;LINKS([@Voornaam]))
in de kolom daarnaast bepalen we of de naam vaker voorkomt dan het jaar daarvoor: =ALS([@Jaar]=MinJr;”-“; ALS([@Aantal] > SOMMEN.ALS([Aantal]; [Jaar];[@Jaar]-1;[JM];[@JM];[Voornaam];[@Voornaam] ); “J”;”N”)) De eerste Als zorgt er voor, dat als het om een naam uit het eerste jaar gaat (MinJr), de aanduiding een streepje wordt (we weten niet of die in populariteit gestegen is of gedaald). Anders: als het Aantal uit de betreffende regel groter is dan ‘iets anders’ dan wordt het resultaat gelijk aan J, anders N. Maar wat is dat ‘iets anders’? Met de functie Sommen.als tellen we alle (geen @) gegevens op uit de kolom Aantal, die voldoen aan de voorwaardes daarna: het Jaar moet gelijk zijn aan het jaar uit de huidige regel minus 1, de code JM moet gelijk zijn aan die uit de huidige regel én de Voornaam moet overeenkomen. NB als de bron-data consistent zijn dan levert deze exercitie maar 1 resultaat op: het aantal keren dat de naam een vorig jaar voorkwam.
om straks te kunnen zien of een naam in de loop van de jaren alleen maar populairder wordt ziet u nog een kolom SteedsStijgend met de formule: =AANTALLEN.ALS( [JM];[@JM]; [Voornaam];[@Voornaam]; [StijgendJN];”J” ) =MaxJr – MinJr Op een vergelijkbare manier als hiervoor met Sommen.als bepalen we met behulp van Aantallen.als eerst het aantal keren dat bij een naam de codering StijgendJN gelijk is aan J. Daarna vergelijken we of dit aantal gelijk is aan MaxJr minus MinJr; als dat zo is, dan wordt het resultaat WAAR, anders ONWAAR. NB door het gebruik van de cellen MaxJr en MinJr hoeft er aan ons ‘analyse-systeem’ niets meer gewijzigd te worden wanneer er gegevens van andere jaren worden toegevoegd.
Jaar-overzicht
Allereerst maken we een overzicht van totalen per jaar (zie het tabblad JrOverz# van het Voorbeeldbestand). De eerste conclusie zou kunnen zijn, dat er per jaar ruim 110.000 kinderen worden geboren en dat er blijkbaar per jaar meer jongens bij komen dan meisjes.
Deze getallen toch maar eens checken. Via Statline van het CBS zien we dat het aantal geboren kinderen in deze jaren ongeveer 170.000 is geweest en dat er ongeveer 5% meer jongens dan meisjes worden geboren:
NB wereldwijd worden er zelfs 7% meer jongens dan meisjes geboren. Volgens diverse bronnen zou uit onderzoek blijken, dat vrouwelijke embryo’s een net iets grotere kans hebben om te overlijden in de buik. Bij de bevruchting zou de verhouding wel degelijk 50/50 zijn.
Dat de totalen in het SVB-overzicht lager zijn is te wijten aan het uitsluiten van namen die minder dan 25 keer per jaar voorkomen. De verhouding jongens-meisjes volgens het SVB ligt nog verder scheef; blijkbaar worden voor meisjes vaker dan voor jongens originele namen bedacht!
Wanneer we dit overzicht beperken door de beginletter van de naam als filter te gebruiken, kunnen we diverse opvallende ontdekkingen doen. Bijvoorbeeld: er zijn 3x zoveel meer jongens- dan meisjesnamen met een B in gebruik (tenminste namen die vaker dan 25 keer voor komen). Wanneer we daar de aantallen voor corrigeren, dan is de verhouding jongens t.o.v. meisjes binnen deze categorie iets groter dan over de hele populatie.
Bij deze draaitabel is de sortering nog rechttoe rechtaan: bij het aanmaken van de draaitabel heeft Excel de rijen en kolommen oplopend gesorteerd. Wil je de sortering anders hebben:
klik op een rij- of kolomnaam
klik op de menutab Gegevens
klik op of
of klik rechts op een rij- of kolomnaam en kies binnen de menu-optie Sorteren de gewenste volgorde:
Top-10
Laten we eens een Top-10 maken (zie ook het artikel Top-5):
klik ergens in het tabblad Data van het Voorbeeldbestand op een cel in de tabel tblData
kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK
plaats het Jaar in de Kolommen, Voornaam in de Rijen en Aantal en Populariteit in het Waarden-gebied
klik rechts op één van de voornamen in de draaitabel en kies de optie Filteren en daarna de optie Top-tien. Zorg dat de volgende instelling is geselecteerd en sluit af via OK:
de namen staan nu nog in alfabetische volgorde; we willen ze natuurlijk van hoog naar laag van het aantal keren dat ze gebruikt zijn: klik rechts op één van de voornamen in de draaitabel en kies de optie Sorteren en daarna Meer sorteeropties. Vul het vervolgscherm als volgt in en klik op OK:
Om een top-10 van jongens óf meisjes en/of andere deelselecties te maken zijn in het tabblad Top10 van het Voorbeeldbestand de velden JM, BegLetter en Lengte in het Filters-gebied geplaatst:
In het overzicht dat we op deze manier hebben gemaakt, staan de namen in de volgorde van het Eindtotaal. Wil je bijvoorbeeld de volgorde van 2019 zien, doe dan het volgende:
klik rechts op één van de voornamen en kies de opties Sorteren en dan Meer sorteeropties
kies op het vervolgscherm Meer opties
vul het volgende scherm als volgt in:
LET OP op deze manier is de sortering wel aan te passen aan het gewenste jaar. De namen van de top-10 veranderen daardoor niet. De filtering wordt door Excel altijd op basis van het Eindtotaal gemaakt.
Top-10 per jaar
Een echte top-10 per jaar kunnen we alleen maken door ook het Jaar in het Filters-gebied van de draaitabel te plaatsen.
Wat opvalt: korte, krachtige namen zijn erg in trek!
Tendens over de jaren
Bij een ‘analyse’ mag een verloop in de tijd niet ontbreken (zie het tabblad JrTendens in het Voorbeeldbestand):
maak een draaitabel op basis van de brongegevens
plaats het Jaar in de Kolommen, de Voornaam in de Rijen en 2x het Aantal in het Waarden-gebied
om een deelselectie te kunnen maken plaatsen we JM, BegLetter en Lengte in het Filters-gebied
klik rechts op een van de getallen in de eerste Aantal-kolom, pas de naam aan (StDal)
kies Waarden weergeven als en vul het vervolgscherm in:
Wanneer we nu het overzicht voor jongens maken, valt direct een vreemde sortering van de namen op.
Alle namen staan netjes alfabetisch op de eerste 2 na! De reden daarvoor zag ik zo gauw niet, tot ik me ineens realiseerde dat Excel Jan en Sep waarschijnlijk als maandaanduiding ziet. Wat blijkt: Excel gaat bij een sortering in een draaitabel niet zonder meer uit van een standaard ‘alfabetische’ sortering maar gaat ook op zoek naar Aangepaste lijsten (zie Doorvoeren en sorteren). Als er gegevens zijn die aan een Aangepaste lijst voldoen, dan worden deze gegevens eerst gesorteerd, daarna komen de anderen in alfabetische volgorde.
Als je het probleem onderkent, is de oplossing vaak dichtbij. Bij een sortering buiten een draaitabel kun je op de grote button Sorteren klikken en krijg je een vervolgscherm waarin je kunt aangeven op welke manier er gesorteerd moet worden: Standaard staat de sortering op A-Z, maar die kun je wijzigen. Waaronder de optie om een Aangepaste lijst te gebruiken. Bij een draaitabel werkt dit echter niet. Via de volgende weg kun je dit oplossen:
klik rechts op een van de namen en kies de optie Sorteren en dan Meer sorteeropties
klik op de button Meer opties
zet de optie Automatisch sorteren in het vervolgscherm uit en kies als Sorteervolgorde: Geen berekening Bij Sorteervolgorde zit nu de optie verstopt of je wel of niet een Aangepaste lijst wilt gebruiken!
NB aan het veld StDal is een Voorwaardelijke opmaak toegevoegd, zodat de tendens in de loop van de jaren sneller duidelijk is.
Steeds populairdere namen
Wanneer we een overzicht willen hebben van alle namen die in de loop der jaren steeds populairder worden, maken we een draaitabel waaraan we als filter ook het veld SteedsStijgend toevoegen (zie het tabblad JrStijgend van het Voorbeeldbestand).
De naam Fos zal in 2015 ook wel zijn voorgekomen, maar het aantal zal lager dan 25 zijn geweest; door het SVB worden die in het kader van de privacy weggelaten.
Selectie
Ben je op zoek naar een handig hulpmiddel om een naam te vinden voor een kind (populair of juist niet, beginnend met een bepaalde letter, een korte of lange naam)? Het tabblad Selectie van het Voorbeeldbestand bevat een draaitabel met slicers waarmee een keuze maken heel eenvoudig is (?):
NB de inhoud van de slicers past zich automatisch aan aan keuzes die in andere slicers zijn gemaakt. Door als beginletter G te kiezen bij de jongens zijn er alleen nog namen met 4 tot 8 letters over.
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
Totalen in een grafiek weergeven, dat is simpel: voeg de betreffende reeks toe aan de grafiek-gegevens. Misschien dat je deze reeks nog aan de secundaire as moet koppelen, maar daarmee ben je klaar.
Maar bij een draai-grafiek (een grafiek gebaseerd op een draaitabel) zul je merken dat je wel wat flexibiliteit inlevert; dan is een lijntje met totalen toevoegen niet mogelijk. Met VBA kun je een heel eind komen, maar zonder programmeren niet. In dit artikel wat alternatieve mogelijkheden.
Basis-gegevens
We gaan uit van een tabel (tblData2 op het tabblad Data van het Voorbeeldbestand) met daarin Datums en het Soort artikel met een bijbehorend Aantal.
Wil je zien hoe dit overzicht tot stand is gekomen: kijk op het tabblad Basis van het Voorbeeldbestand. Daar worden telkens random nieuwe data gecreëerd.
Draaitabel
Om deze gegevens snel te kunnen analyseren maken we een draaitabel:
selecteer een cel in de brongegevens
klik in de menutab Invoegen in het blok Tabellen op de optie Draaitabel en dan op de button OK
sleep de Datum naar het Rijen-gebied, Soort naar de Kolommen en Aantal naar het Waarden-gebied
standaard zal Excel de datums direct groeperen (in Jaren, Kwartalen en Maanden) LET OP één van de namen in de Rijen is nog steeds Datum, maar deze bevat nu de maanden. Wil je een andere groepering? Zie het artikel Groeperen in een draaitabel.
sleep Jaren naar het Filters-gebied en verwijder Kwartalen uit de Rijen.
de Soort is alfabetisch gesorteerd: versleep de kolom met de waarde Drie naar rechts (door de rand met de muis ‘vast te pakken’). Het resultaat staat in het tabblad Ovz1 van het Voorbeeldbestand.
Draaigrafiek
Een grafiek maakt de onderlinge verhoudingen tussen de cijfers vaak een stuk duidelijker:
selecteer een cel van de draaitabel
kies in de menutab Hulpmiddelen voor draaitabellen/Analyseren in het blok Extra de optie Draaigrafiek
kies de optie Gegroepeerde kolom en klik op OK
Verander je nu iets in de draaitabel (filter je bijvoorbeeld een bepaald jaar uit) dan past de grafiek zich automatisch aan.
Draaigrafiek aanpassen 1
De standaard-grafiek kent wel wat nadelen. Allereerst willen we van die ‘lelijke’ veldknoppen af: klik rechts op één van de knoppen en kies de optie Alle veldknoppen verbergen in grafiek.
Op het tabblad Ovz2 van het Voorbeeldbestand staat het resultaat:
LET OP Excel geeft alle kolommen automatisch een kleur; normaal wijzig ik deze handmatig in vaste kleuren zodat ook bij filtering iedere Soort zijn eigen kleur houdt. Helaas: bij draaigrafieken worden deze instellingen door Excel niet vastgehouden.
Draaigrafiek aanpassen 2
Ook al is een grafiek bedoeld om intuïtief inzicht in de onderliggende cijfers te krijgen, dan nog werkt het goed (of is het zelfs noodzakelijk) om in een grafiek de waarde(s) van de belangrijkste gegeven(s) te laten zien.
In het tabblad Ovz3 van het Voorbeeldbestand heeft de grafiek een veelzeggende titel meegekregen:
in een lege cel creëren we daartoe eerst de volgende formule: =ALS(C2=”(Alle)”; “Totaal alle jaren: “; “Totaal voor ” & C2 &”: “) & TEKST(DRAAITABEL.OPHALEN(“Aantal”;$B$4);”#.##0″) Als in cel C2 alle jaren zijn gekozen dan nemen we een overeenkomende tekst, anders wordt de tekst Totaal voor met daarachter de inhoud van cel C2 (gekoppeld door het &-teken). Achter de zo gemaakte tekst plaatsen we met behulp van de functie DRAAITABEL.OPHALEN (zie het betreffende artikel) het totaal van Aantal. Met de functie Tekst geven we een opmaak mee (een punt voor de duizendtallen en geen decimalen).
zorg dat de grafiek een Grafiektitel heeft; bijvoorbeeld op de volgende manier: selecteer de grafiek en klik op de + rechts daarvan en vink de betreffende optie aan.
klik op de Grafiektitel en daarna in de formulebalk. Tik in = en klik dan op de cel uit de eerste stap (in Ovz3 is dat cel I2). In de formulebalk komt dan automatisch de formule: =’Ovz3′!$I$2. Druk op Enter.
Nog een paar slicers toevoegen (zie Slicers in Excel) en we hebben (het begin van) een interactief dashboard.
Bij het filteren in de draaitabel (hier mer behulp van slicers) kan het gebeuren dat het scherm ‘verspringt’; door een paar aanpassingen aan de draaitabel blijft de opmaak stabiel:
om te zorgen dat in jaren waar (nog) niet alle maanden gevuld zijn (in het voorbeeld 2020), toch alle maanden zichtbaar zijn (en dus ook in de grafiek): klik rechts op een van de maanden in de draaitabel, kies Veldinstellingen, vink op het tabblad Indeling&afdrukken de optie Items zonder gegevens weergevenaan
klik rechts op een van de cellen in het Waarden-gebied, kies Opties voor draaitabel en vink de optie Kolombreedte automatisch aanpassenuit
Draaigrafiek aanpassen 3
Maar we zijn niet gauw tevreden: we hebben nu het totaal aantal in de titel staan, maar wat zijn de totalen per maand? In de draaitabel staan de betreffende getallen netjes op het einde van iedere rij, maar we zien die niet terug in de grafiek.
Nog erger: er is ook geen optie om dat klaar te krijgen! Uiteraard kunnen we met VBA aan de slag, maar daar is wel wat programmeer-arbeid voor nodig. Eens even kijken of het ook zonder kan: ja natuurlijk, we zorgen dat één reeks labels heeft en laten de inhoud van die labels wijzen naar de totalen per maand.
zorg dat ergens in het tabblad een reeks cellen gevuld is met de rij-totalen. In het tabblad Ovz4 staan in de cellen C24:C35 verwijzingen naar de draaitabel met behulp van de functie DRAAITABEL.OPHALEN.
klik op één van de kolommen in de grafiek
klik op de + rechts van de grafiek en zet de Gegevenslabels aan, inclusief de optie Basis, binnenkant zodat alle labels op dezelfde hoogte komen
klik rechts op een van de labels en kies de optie Gegevenslabels opmaken
bij Labelopties kun je de waarden opgeven die weergegeven moeten worden (Waarde uit cellen). Maak hier een verwijzing naar de cellen C24:C35; vink de optie bij Waardeuit en dan Waarde uit cellenaan.
Helaas, deze methode heeft 2 (?) tekortkomingen (zie het tabblad Ovz4 van het Voorbeeldbestand):
de labels worden gecentreerd op de overeenkomende kolom
als de betreffende soort uitgefilterd wordt zijn ook de labels weg!
Draaigrafiek aanpassen 4
In het tabblad Ovz5 van het Voorbeeldbestand staat een grafiek die de totalen per maand toont, inclusief de procentuele verdeling over het jaar.
De volgende aanpassingen zijn doorgevoerd:
allereerst is er ruimte gemaakt onder aan de grafiek: klik rechts op de linkeras, kies As opmaken en zorg dat de minimumgrens niet meer automatisch wordt bepaald maar (in dit geval) -200 is
de notatie van de as is zodanig aangepast, dat de negatieve getallen niet worden weergegeven: #.##0;
vanaf cel C24 worden de maandtotalen opgehaald: =DRAAITABEL.OPHALEN(“Aantal”;$B$4;”Datum”;B24)
vanaf cel D24 bepalen we de inhoud van de teksten die we gaan toevoegen: =ALS(C24=0;””; TEKST(C24;”#.##0″)& TEKEN(13)& TEKST(C24/DRAAITABEL.OPHALEN(“Aantal”;$B$4);”0%”)) Als C24 nul is dan hoeft er niets weergegeven te worden, anders de inhoud van cel C24 samen met het resultaat van het maandresultaat (C24) gedeeld door het totale resultaat (weergegeven als percentage zonder decimalen); tussen de twee elementen staat een code 13 (‘naar de volgende regel’). NB in cel D24 en verder is het resultaat van code 13 niet te zien, maar dadelijk in de grafiek wel.
klik ergens in de grafiek en kies dan in de menutab Invoegen in het blok Illustraties de optie Vormen en daarna bij Basisvormen de optie Tekstvak en ’teken’ met de muis waar je het tekstvak wilt hebben LET OP als je niet eerst ergens in de grafiek klikt wordt het tekstvak niet aan de grafiek gekoppeld maar aan het tabblad; bij het verplaatsen van de grafiek gaat het tekstvak dan niet mee!
klik in de formulebalk, tik het =-teken en klik met de muis op de cel met de tekst die weergegeven moet worden
pas de grootte van de tekst en/of het tekstvak aan en verplaats het tekstvak naar de juiste plaats (pak met de muis de ‘rand vast’)
herhaal de stappen 5 t/m 7 voor alle maanden NB je kunt ook het eerste tekstvak kopiëren (Ctrl-C) en dan via Ctrl-V zoveel tekstvakken maken als nodig zijn. Die moeten dan nog verplaatst worden en de inhoud aangepast.
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
Iedere dag krijgen of vinden we wel gegevens, waarvan je denkt: interessant, maar als ik die met een andere set combineer (verrijken van data) kan ik er nog veel meer informatie uit halen.
In dit artikel geef ik uitleg over verschillende methoden zoals bestaande tabellen uitbreiden met extra kolommen, gebruik maken van het Excel-gegevensmodel en het gebruik van Power Query.
Brongegevens
We gaan als voorbeeld een Excel-tabel gebruiken met daarin het aantal verkochte producten, uitgesplitst naar datum, klant- en productcode. In het tabblad Verkoop van het Voorbeeldbestand vindt u 1000 records in de Excel-tabel tblVerkoop.
Welke klanten komen hierin voor? Welke producten zijn verkocht? Over welke periode gaat dit bestand? Meer vragen dan antwoorden.
Verkoopoverzicht
Om snel inzicht te krijgen in bovenstaande vragen maken we op basis van het bestand een draaitabel:
Plaats de cursor ergens in de tabel tblVerkoop.
Kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK.
Sleep de Datum naar de Rijen, Aantal naar het Waarden-gebied en Prod naar Kolommen.
We zien dus direct (tabblad OvzVerkoop) dat het 6 producten betreft en dat het over de periode 2018-2020 gaat.
Klik je op de + voor een jaar, dan zie je welke kwartalen en maanden er in het overzicht voorkomen.
NB1 vanaf versie 2016 zal Excel datums, die in de kolommen of rijen worden geplaatst, direct groeperen naar Jaren, Kwartalen en Maanden. Gebeurt dit niet automatisch, klik dan met de rechtermuisknop op een datum en kies Groeperen; selecteer daar dan de gewenste opties.
NB2 wil je geen groepering naar kwartaal? Klik rechts op een van de tijdaanduidingen, kies Groeperen en deselecteer de optie Kwartalen.
Maar hoe zit het met de klanten?
Verwijder Prod uit de Kolommen.
Verplaats Jaren van Rijen naar Kolommen.
Sleep Klanten naar Rijen.
Zie het tabblad OvzVerkoop.
NB Excel plaatst de klanten bij het aanmaken van de draaitabel netjes in alfabetische volgorde, maar door de methode van codering komt K10 direct na K1. Gelukkig is dit snel verholpen: selecteer de cel met K10, ‘pak’ met de muis de rand en sleep de code naar beneden.
‘Toevallig’ weet ik dat de klanten over 2 regio’s zijn verdeeld: K2, K5, K7 en K8 horen bij de regio Zuid, de anderen bij Noord. Die moeten we nu dus nog groeperen (zie Groeperen in een draaitabel): selecteer de draaitabel-rij met K2 (het muis-symbool is dan een pijltje naar rechts), houd Ctrl ingedrukt en selecteer op dezelfde manier K5, K7 en K8. Klik rechts en kies Groeperen. Nog even de namen van de groepen aanpassen (gewoon overschrijven) en de groep Noord naar boven verslepen: klaar!
Op deze manier hebben we handmatig de eerste verrijkingen aangebracht.
Verrijking door toevoegen kolommen
Maar we weten nog meer over onze klanten en de producten:
Tabblad Klant
Tabblad Product
Hiermee kunnen we onze dataset uitbreiden. Dezelfde gegevens van het tabblad Verkoop zijn allereerst gekopieerd naar VerkBerek van het Voorbeeldbestand.
Aan de Excel-tabel (met de naam tblVerkBerek) zijn nieuwe kolommen toegevoegd:
Regio, in cel F3 staat de formule: =INDEX(tblKlant[Regio];VERGELIJKEN([@Klant];tblKlant[Klant];0)) NB1 hier is een alternatief voor verticaal zoeken gebruikt. De avz-truc komt goed van pas! Zie zoeken-index-en-vergelijken. NB2 alle berekeningen worden binnen een Excel-tabel uitgevoerd, waardoor we gestructureerde verwijzingen kunnen gebruiken. Zie voor de voordelen van tabellen: kunst-en-excel. NB3 na het invoeren van de formule in F3 wordt deze automatisch naar beneden doorgevoerd, zodat alle cellen in die kolom een consistente formule hebben.
In cel G3 halen we op een vergelijkbare manier de naam van het product op: =INDEX(tblProd[ProdNaam];VERGELIJKEN([@Prod];tblProd[Product];0))
De totale kosten, die met een verkoop zijn gemoeid worden in cel H3 bepaald: =[@Aantal]* INDEX(tblProd[KostPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
In cel I3 bepalen we de omzet: =[@Aantal]* INDEX(tblProd[VerkPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
Daarmee kunnen we in J3 de bruto-winst berekenen: =[@Omzet]-[@Kosten]
Maar (sommige) klanten krijgen korting; in cel K3: =INDEX(tblKlant[Korting];VERGELIJKEN([@Klant];tblKlant[Klant];0))
Waarmee we ook een netto-winst kunnen bepalen in cel L3: =[@Omzet]*(1-[@Korting])-[@Kosten]
Op basis van deze nieuwe dataset kunnen we diverse analyses uitvoeren, uiteraard met behulp van een draaitabel. Bijvoorbeeld (zie tabblad OvzVerkBerek van het Voorbeeldbestand):
Verrijken m.b.v. gegevensmodel
Een nadeel van de vorige methode is, dat het bestand meteen een stuk groter wordt wanneer er kolommen aan een tabel worden toegevoegd. Dat valt nog wel mee als het over 1.000 records gaat, maar als het er een miljoen zijn en als het meer kolommen betreft….
De koppelingen, die we hiervoor met Index/Vergelijken hebben gemaakt, kunnen sinds versie 2013 ook intern in Excel met behulp van een gegevensmodel worden vastgelegd zonder dat dit extra ruimte in beslag neemt. Wel moeten de basisgegevens in Excel-tabellen vastliggen. In het Voorbeeldbestand bevatten de tabbladen Verkoop, Klant en Product onze basisgegevens.
Nu gaan we het gegevensmodel vullen door de relaties tussen deze tabellen vast te leggen:
Kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Relaties (de button met 3 tabellen en lijntjes daar tussen).
U legt een relatie vast door op de knop Nieuw te klikken: De eerste tabel moet de gegevens bevatten, die uitgebreid gaan worden; in dit geval dus tblVerkoop. We gaan via de klant-code gegevens opzoeken, dus geven als kolom Klant op. Daaronder moeten we aangeven in welke tabel we gaan zoeken, in het huidige voorbeeld tblKlant. Ook in deze tabel bevat de kolom Klant de overeenkomende gegevens. Klik op OK. NB1 in de laatste stap staat het woord primair; hiermee wordt bedoeld, dat de kolom unieke gegevens moet bevatten. Anders kan er geen relatie aangemaakt worden. NB2 heb je meer dan 1 kolom nodig om een relatie te leggen (bijvoorbeeld Naam en Afd) dan zul je een kunstgreep moeten uithalen: in beide tabellen moet je de 2 kolommen aan elkaar koppelen in een nieuwe kolom, bijvoorbeeld Naam&Afd.
Maak op dezelfde manier ook een relatie tussen tblVerkoop en tblProd; de kolomnamen die daarbij gebruikt worden zijn respectievelijk Prod en Product. De namen hoeven dus niet hetzelfde te zijn.
Sluit het scherm Relaties beheren.
Nu het gegevensmodel is gevuld kunnen we op basis daarvan overzichten maken met behulp van draaitabellen (in het tabblad OvzVerkModel1 van het Voorbeeldbestand staat een voorbeeld):
Plaats de cursor in een lege cel. Hier zal het overzicht komen, dus meestal doe je dit op een nieuw tabblad.
Start via Toevoegen/Draaitabel een nieuwe draaitabel
Hiernaast is te zien, dat Excel nu automatisch weet, dat we ons gegevensmodel als bron willen gebruiken. Klik OK.
Het overzicht van draaitabel-velden ziet er iets anders uit dan bij een ‘normale’ draaitabel. Alle tabellen uit het gegevensmodel zijn beschikbaar met alle bijbehorende kolommen. Hiernaast zijn alle 4 gebieden gevuld met gegevens uit 3 verschillende tabellen. NB1 bevat de werkmap nog meer Excel-tabellen (net als het Voorbeeldbestand), dan zijn die ook in dit overzicht zichtbaar. NB2 heb je velden naar de gebieden versleept en klik je bovenaan op Actief, dan zie je alleen de tabellen die gebruikt zijn in dit overzicht.
Helaas: op deze manier kun je alleen velden uit de basis-tabel (in het voorbeeld tblVerkoop) in het waarden-gebied plaatsen. Sleep je bijvoorbeeld Korting in het waarden-gebied dan krijg je de volgende melding:
Dit scherm krijg je ook als je niet alle tabellen via relaties met elkaar hebt verbonden, maar wel kolommen uit die tabellen gebruikt.
Wat nu wel kan: in de draaitabel kunnen unieke waarden geteld worden. Laten we even via een voorbeeld kijken (tabblad OvzVerkModel2 van het Voorbeeldbestand):
Maak een draaitabel aan zoals hiervoor beschreven.
Plaats Regio en Klant in de Rijen.
Sleep Aantal naar het Waarden-gebied.
Sleep daarna 2x de Datum naar het Waarden-gebied.
Klik op het vinkje achter Datum2 en kies Waardeveldinstellingen.
Helemaal onderaan krijg je nu de mogelijkheid om te kiezen voor Uniek aantal.
Telling van Datum geeft het aantal datums weer per klant, dus eigenlijk het aantal keer dat een klant voorkomt (als de datum is gevuld); het totaal is dan ook precies 1.000.
Unieke telling van Datum2 laat zien hoe vaak een unieke datum voorkomt bij een klant.
Verrijken m.b.v. Power Query
En dan nu een oplossing die als een soort combinatie van de vorige 2 gezien kan worden: Power Query. Binnen dit Excel-onderdeel leggen we de verbanden tussen de tabellen vast én we maken daar allerlei berekeningen die we nodig hebben:
Selecteer een cel in de tabel tblKlant.
Kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
In principe zijn alle kolommen in orde. Aangezien Korting een percentage is, kunnen we de lay-out nog aanpassen: * klik op de 1.2 naast Korting * wijzig de instelling naar Percentage.
Kies dan het vinkje naast Sluiten en laden.
Klik op Sluiten en laden naar.
Zorg dat de optie Alleen verbinding maken is geselecteerd en klik op de button Laden.
Voer de vorige stappen ook uit voor de tabel tblProduct. Zorg dat KostPrijs en VerkPrijs de instelling Decimaal getal krijgen.
Als laatste doet u hetzelfde met de tabel tblVerkoop, maar nog NIET sluiten. Wijzig de opmaak van de kolom Datum in alleen Datum.
Voordat we Power Query afsluiten moeten we nog relaties leggen tussen de 3 tabellen. Kies het vinkje achter Query’s samenvoegen en kies de optie Samenvoegen als nieuw.
Maak de verbindingen zoals hierboven en klik op OK. Wijzig de naam van de nieuwe query in PQtblVerkoop.
Om de gegevens uit tblKlant als extra kolommen toe te voegen klikt u op het symbool rechts van tblKlant. Stel de opties in zoals hierboven en klik OK.
Doe hetzelfde met tblProduct.
Nu gaan we nog wat extra kolommen toevoegen: klik in de kolom Datum, klik op de menutab Kolom toevoegen en kies binnen de optie Datum voor het Jaar. Doe hetzelfde om de maand, de naam van de maand en het kwartaal toe te voegen.
Kies daarna de optie Aangepaste kolom binnen Kolom toevoegen. De naam wordt Omzet en de formule =[Aantal]*[VerkPrijs].
Voeg op dezelfde manier nog 3 kolommen toe: Kosten: =[Aantal]*[KostPrijs] Brutowinst: =[Omzet]-[Kosten] Nettowinst: =[Aantal]*([VerkPrijs]*(1-[Korting])-[Kostprijs])
Zorg dat de 4 toegevoegde kolommen de instelling Decimaal getal hebben.
Kies nu Sluiten en laden en zorg dat er alleen een verbinding tot stand wordt gebracht. LET OP zorg wel dat deze query PQtblVerkoop in de laatste stap wordt toegevoegd aan het gegevensmodel.
Nu staat alles klaar om overzichten te maken. Op het tabblad OvzVerkPQ van het Voorbeeldbestand vindt u een voorbeeld:
De gegevens van de bruto- en nettowinst zijn niet als getallen zichtbaar. Met behulp van Voorwaardelijke opmaak hebben die gegevensbalken gekregen:
Selecteer een cel in een betreffende rij.
Kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak.
Kies dan Gegevensbalken en een kleur(overgang).
Naast de cel staat nu het tekentje van een draaitabel, kies daar de onderste optie.
Via Regels beheren/Regel bewerken de optie Alleen balk weergeven aanvinken.
Pas de rijhoogte naar wens aan.
De Aantallen hebben op een vergelijkbare manier een Voorwaardelijke opmaak gekregen, namelijk Kleurenschalen.
Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd? Door te klikken op de button
Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd? Door te klikken op de button Alles vernieuwen in het blok Verbindingen van de menutab Gegevens, worden alle koppelingen, het gegevensmodel en alle draaitabellen ververst.
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
Vanaf 31 maart publiceert het RIVM andere gegevens dan daarvoor. Helaas is het dus niet meer mogelijk om via het voorbeeldbestand de (ver)spreiding van Corona op een consistente manier te volgen. Op de site https://nlcovid-19-esrinl-content.hub.arcgis.com/ zijn wel nog diverse overzichten en kaarten te vinden.
Corona: de laatste weken beheerst deze crisis niet alleen het nieuws maar ook ons leven. Het einde is nog niet in zicht. Het zou mooi zijn als we met Excel een bijdrage zouden kunnen leveren aan de oplossing er van. Helaas, maar wat we wel kunnen, is proberen inzicht te geven in de omvang en voortgang van de besmettingen en overlevenden.
Het RIVM publiceert dagelijkse nieuwe gegevens en ook een kaartje dat de verspreiding van de besmettingen laat zien.
Voor G-Info is dit een goede aanleiding om eens te kijken hoe we van gegevens, die we over Corona kunnen vinden, informatie kunnen maken. Niet voor niets is onze hoofddoelstelling: van Gegevens naar Informatie.
In dit artikel gaan we eerst op zoek naar gegevens rond Corona. Daarna kijken we welke informatie we daaraan kunnen ontlenen. We zullen daarom diverse (vaak met behulp van draaitabellen gemaakte) overzichten bekijken. Als laatste zult u zien dat Voorwaardelijke opmaak heel handig is om snel binnen een grote hoeveelheid gegevens de uitschieters te signaleren.
Brongegevens
In dit artikel focussen we ons op de situatie in Nederland. Het is dan ook logisch dat we terecht komen bij het RIVM. Dit instituut publiceert iedere dag een update van de situatie op hun website rivm.nl.
Op deze site hebben we vanaf de uitbraak in Nederland kunnen terugvinden hoeveel mensen er besmet zijn geraakt en hoeveel daarvan er ondertussen zijn overleden (zie het tabblad DataNed van het Voorbeeldbestand).
Deze gegevens zijn dagelijks handmatig ingevoerd in een Excel-tabel met de naam tblNed. De kolom Cum bevat een formule, die een lopend cumulatief bepaalt (in cel D9 staat bijvoorbeeld =D8+C9); op een vergelijkbare manier worden ook de 5e en 7e kolom gevuld. In de laatste kolom (Actief) berekenen we het aantal personen, dat op dit moment nog besmet is: =[@Cum]-[@CumHerst]-[@CumOvl].
NB1 het RIVM geeft aan, dat de door hen gehanteerde cijfers geen exacte waarheid weergeven: “Het werkelijke aantal besmettingen met het nieuwe coronavirus ligt hoger dan het aantal dat hier genoemd wordt. Dit komt omdat niet iedereen met mogelijke besmetting getest wordt, maar vooral patiënten die zo ziek zijn dat ze in het ziekenhuis opgenomen worden en zorgverleners. Het aantal gemelde patiënten en overleden patiënten kan per dag verschillen om verschillende redenen. Zo zien we dat overleden patiënten niet altijd op dezelfde dag gemeld worden.” Voor inzicht in de verspreiding lijken ze mij echter significant genoeg.
NB2 gegevens over personen, die hersteld zijn, zijn niet bekend bij het RIVM of worden niet geregistreerd. Op de website van de Johns Hopkins University over Corona worden wel aantal vermeld. Of deze op dit moment de werkelijkheid benaderen betwijfel ik.
NB3 dit artikel is in de loop van enkele dagen geschreven. Aangezien de werkmap in die dagen continu is bijgewerkt kunnen data in de afbeeldingen afwijken van die in het Voorbeeldbestand.
NB4 boven de tabel staat een dynamische kopregel; de inhoud past zich aan aan de datums in de tabel: =”Corona in Nederland van “&TEKST(MIN(tblNed[Datum]);”d mmm jjjj”)&” t/m “&TEKST(MAX(tblNed[Datum]);”d mmm jjjj”) Teksten worden aan elkaar gekoppeld met behulp van het &-teken; de minimum- en maximum-datum wordt met behulp van de functie Tekst van het gewenste formaat voorzien.
Als tweede bron is dagelijks een bestand gedownload van de RIVM-site (via het pijltje naast de kaart met gemelde Corona-gevallen).
In het Voorbeeldbestand is dit geautomatiseerd via de Power Query-tabel op het tabblad DagInput. Het bestand bevat het totaal aantal bekende Corona-gevallen per gemeente. Om de resultaten van de gemeentes met elkaar te kunnen vergelijken is dit aantal genormaliseerd (de kolom Aantal per 100.000 inwoners), namelijk door te delen door het aantal inwoners van die gemeente (en met 100.000 te vermenigvuldigen).
Deze gegevens (zonder de kopregel) zijn iedere dag gekopieerd naar de Excel-tabel tblGem op het tabblad DataGem van het Voorbeeldbestand.
LET OP vanaf 27 maart bevat het bestand van het RIVM 4 namen van gemeenten, die anders gespeld worden dan daarvoor: Bergen (L.) moet zijn: Bergen (L) Bergen (NH.) moet zijn: Bergen (NH) Hengelo moet zijn: Hengelo (O) s-Gravenhage moet zijn: ‘s-Gravenhage (in de Excel-cel moet dus een dubbele ‘ komen) De Power Query routine is hier op aangepast, anders met de hand wijzigen in de tabel tblGem.
Een ander bronbestand (zie het tabblad ProvGemeente) is een overzicht van alle gemeentes in Nederland met daarachter het inwoneraantal, een indeling naar klein, middelgroot en groot en de bijbehorende provincie.
Het overzicht in het tabblad DataNed van het Voorbeeldbestand laat het verloop in de tijd zien van het aantal besmettingen, overledenen en herstelden. Maar hoe de getallen zich tot elkaar verhouden is moeilijk te onderscheiden. Een grafiek is geschikter om dit te laten zien:
De eerste grafiek (staafdiagram) laat het aantal nieuwe geregistreerde besmettingen, herstelden en overledenen per dag zien (tabblad GrafNed1). De lijngrafiek (tabblad GrafNed2) toont de cumulatieven daarvan in de tijd. In de derde grafiek (tabblad GrafNed3) wordt door het gebruik van vlakken de opdeling van het aantal cumulatieve besmettingen zichtbaar gemaakt.
NB1 bij het schrijven van dit artikel was het aantal herstelden nog gering (volgens de gehanteerde bronnen); resultaten daarvan zijn in de grafieken dan ook nauwelijks/niet zichtbaar.
NB2 in de laatste grafiek moet het oranje vlak (de actieve besmettingen) in de loop van de tijd steeds meer de veel besproken ‘uitgevlakte’ curve laten zien. Het groene vlak (herstelden) zal steeds groter moeten worden. Een fictief voorbeeld staat hiernaast.
Overzicht per gemeente, basis
Zoals bij de bronnen aangegeven bevat het tabblad DataGem van het Voorbeeldbestand de totaal-aantallen geregistreerde besmettingen per gemeente per dag.
Om hierna diverse analyses op de gegevens uit te kunnen voeren zijn de brongegevens in deze tabel verrijkt:
in kolom F wordt de dagelijkse groei van het aantal voor een gemeente berekend: =[@Aantal]-SOMMEN.ALS([Aantal];[Datum];[@Datum]-1;[Plaats];[@Plaats]) Trek van het Aantal in een bepaalde rij het Aantal af van de vorige dag bij die gemeente. NBSOMMEN.ALS telt alle Aantallen op waarvan de Datum gelijk is aan de Datum in die regel minus 1 en waar de Plaats gelijk is aan de Plaats in die regel. In principe is er altijd maar 1 geval, die aan deze voorwaarden voldoet.
de indeling van de gemeente naar grootte wordt in kolom G bepaald door dat gegeven op te zoeken in tblGemProv in het tabblad ProvGemeente: =INDEX(tblGemProv[Grootte];VERGELIJKEN([@Plaats];tblGemProv[Gemeentenaam];0))
op een vergelijkbare manier worden in de kolommen H en I respectievelijk de provincie en het aantal inwoners van een gemeente opgehaald.
de genormaliseerde aantallen per gemeente (uitgedrukt in Aantal besmettingen per 100.000 inwoners) wordt door het RIVM aangeleverd; kolom D. Om provincies goed met elkaar te kunnen vergelijken moeten ook de aantallen per provincie worden genormaliseerd. In kolom J wordt de benodigde berekening uitgevoerd: =[@Aantal]/ INDEX($N$5:$N$16;VERGELIJKEN([@Prov];$M$5:$M$16;0)) *100000 NB de kolommen M en N bevatten een draaitabel die het aantal inwoners per provincie bepaald.
Overzicht per provincie (1)
Waar in Nederland zitten de meeste Corona-gevallen? Met behulp van een draaitabel kunnen we snel een overzicht per provincie maken (gerangschikt van noord naar zuid; zie tabblad OvzProv1 van het Voorbeeldbestand):
Uit de bijbehorende draaigrafiek kunnen makkelijker conclusies getrokken worden (tabblad GrafProv1):
in absolute zin is het aantal besmettingen vanaf het begin (van de registratie in dit Excel-bestand, 19 maart) het grootst in Noord-Brabant, gevolgd door Noord- en Zuid-Holland en Gelderland en Limburg.
Deze laatste provincie kende een relatief grote groei op 24 maart.
De hoop dat de groei in Brabant zou gaan afvlakken zien we nog niet terug (dan zouden de lijnen daar steeds dichter bij elkaar moeten gaan liggen; op 27 maart zien we zelfs weer een groei) .
Ook neemt de groei in Noord- en Zuid-Holland toe.
In Limburg blijft de groei de laatste dagen gelijk.
De noordelijke provincies en Flevoland en Zeeland lijken nog weinig ‘geraakt’.
LET OP wanneer er gegevens voor een nieuwe datum zijn toegevoegd dan moet de draaitabel vernieuwd worden. Aangezien alle draaitabellen in deze werkmap dezelfde bron gebruiken (tblGem) worden al deze draaitabellen dan tegelijkertijd ververst.
Overzicht per provincie (2)
Wanneer we eenzelfde draaitabel sorteren van ‘hoog naar laag’ op de gegevens van de laatste dag (tabblad OvzProv2) dan ziet de bijbehorende draaigrafiek er als volgt uit (tabblad GrafProv2):
Noord- en Zuid-Holland gaan ongeveer gelijk op
Gelderland ‘haalt Limburg langzaamaan in’
Overzicht per provincie (3)
Maar als je provincies echt met elkaar wilt vergelijken moet je ook de verschillen in grootte daarbij betrekken. Op het tabblad OvzProv3 is daarom een draaitabel gemaakt van de genormaliseerde aantallen:
relatief zijn er dus in Brabant en Limburg de meeste besmettingen (toch gerelateerd aan Carnaval?)
we horen weinig over Utrecht, maar die komt in dit overzicht op de 3e plaats
Noord-Holland heeft relatief duidelijk meer besmettingen dan Zuid-Holland
de provincie Zeeland kende in het begin relatief evenveel besmettingen als Zuid-Holland; deze laatste vertoont echter in de loop van de tijd een grotere groei.
maar het virus moet ook in de ‘kleinere’ provincies niet onderschat worden
Overzicht per gemeente (1)
Voor diegene die nog wat ‘dieper willen kijken’ kunnen we op basis van de RIVM-cijfers op gemeenteniveau inzoomen. Het mooiste is natuurlijk om dit op een kaart zichtbaar te maken. De stand van 24 maart hebben we met behulp van Datawrapper.de ‘vertaald’:
Nieuwsgierig naar details? Klik op de afbeelding.
‘Uiteraard’ komen daarmee de grote plaatsen boven drijven. Dat is ook de reden, dat het RIVM vanaf het begin een genormaliseerde kaart heeft getoond. Dat hebben we voor 24 maart ook gedaan:
Klik op de afbeelding.
Op deze manier is beter te onderscheiden waar het virus zich vooral heeft verspreid. Naast de bekende plaatsen in Oost-Brabant zien we ook mogelijke haarden in Alphen-Chaam en Peel en Maas.
Aangezien bovenstaande methode nogal bewerkelijk is, zullen we voor een verdere detaillering gebruik maken van draaitabellen. Op het tabblad OvzGem1 van het Voorbeeldbestand ziet u per provincie de verdeling naar gemeente. Om goed zicht te krijgen op de groei per dag kunt u (handmatig) een sortering aanbrengen; in het voorbeeld is dit op de laatste kolom uitgevoerd.
Vooral in de plaatsen aan de oost-kant van Brabant is het aantal besmettingen weer fors toegenomen. Op 27 maart kent ook Boekel weer 9 nieuwe gevallen, terwijl de dagen daarvoor de groei nul was.
NB1 in het voorbeeld hierboven zijn enkele dagen niet zichtbaar; via Beeld/Blokkeren is in cel E7 een Titelblokkering geplaatst.
NB2 wilt u een andere provincie zien? Maak een keuze in cel C2.
Overzicht per gemeente (2)
Grote steden kennen in absolute zin al snel veel besmettingen. Het effect daarvan kunnen we bekijken op het tabblad OvzGem2 van het Voorbeeldbestand.
Overzicht per gemeente (3)
Maar pieken zijn duidelijker te zien wanneer we de genormaliseerde aantallen in een draaitabel zetten (zie tabblad OvzGem3 in het Voorbeeldbestand):
In deze draaitabel is 2 keer het veld Aantal per 100.000 inwoners geplaatst.
Door rechts te klikken op de 2e kolom kunnen de Waardeveldinstellingen aangepast worden. Zoals u hiernaast kunt zien, zal Excel het Verschilmet de VorigeDatum berekenen.
NB De kolomnamen zijn handmatig aangepast.
Voor allebei de waardevelden is een Voorwaardelijke opmaak ingesteld zodat uitschieters snel zichtbaar worden. Kijk in de menutab Start in het blok Stijlen bij Voorwaardelijke opmaak/Regels beheren.
LET OP wanneer er gegevens van een nieuwe datum bij zijn gekomen dan moet ook voor de nieuwe kolommen de Voorwaardelijke opmaak ingeregeld worden in de kolom Van toepassing op.
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
te klikken kom je vanzelf in het Statline-gebied van het CBS, waar je veel meer opties hebt om gegevens te kiezen. 
of 
en ’teken’ met de muis waar je het tekstvak wilt hebben
