Tagarchief: Kaartgrafiek

Excel en kaarten 2



In een vorig artikel (Excel en kaarten) hebben we laten zien hoe je gegevens op een landkaart kunt plotten; beter gezegd: op ieder willekeurig plaatje.

De daarbij gehanteerde methode maakt het mogelijk om op detailniveau gegevens op een kaart te plaatsen.
Microsoft heeft vanaf versie 2016 een ingebouwde grafiek-optie waarmee gegevens op land, provincie en gemeente-niveau als een landkaart kunnen worden weergegeven. Hoe dat in zijn werk gaat (en welke problemen je daarbij moet zien te tackelen) komt in dit artikel aan bod.

Basisgegevens

Bij het CBS zijn heel veel gegevens te vinden, zoals bijvoorbeeld een overzicht van alle gemeentes per provincie (zie het tabblad Gemeenten_alfabetisch_2020 van het Voorbeeldbestand waar de stand van 1 jan 2020 is opgenomen).

We zijn eigenlijk alleen maar geïnteresseerd in de provincie- en gemeentenamen. Met Power Query is dat zo omgezet. Het resultaat staat in de tabel tblGem op het tabblad Data van het Voorbeeldbestand.

Willen we weten hoeveel gemeentes Nederland telde op 1 jan 2020 dan tellen we het aantal niet-lege cellen in de kolom Gemeentenaam met behulp van de functie AANTALARG.

LET OP de functie AANTAL telt alleen gevulde cellen mee, als daar getallen in staan.

In cel C3 wordt op een zelfde manier het aantal cellen in de kolom Provincienaam geteld. Dat levert hetzelfde resultaat op!
Hoe bepalen we nu hoeveel provincies er in Nederland zijn (we weten natuurlijk allemaal wel dat het antwoord 12 moet zijn)? De tabel tblGem heeft daartoe een hulpkolom gekregen waarin het volgnummer van de gemeente binnen de provincie wordt geturfd. In cel D8 is daartoe de formule =AANTAL.ALS($B$8:B8;[@Provincienaam]) ingevoerd.

NB de formule wordt in de Excel-tabel automatisch in de hele kolom doorgevoerd. In D9 staat dezelfde formule, maar de laatste B8 is dan B9.

In cel C4 bepalen we het aantal provincies door te tellen hoe vaak het eerste volgnummer voorkomt: =AANTAL.ALS(tblGem[GemPerProv];1)

Voor de liefhebbers staat in cel C5 een formule die het aantal provincies bepaalt zonder gebruik te maken van een hulpkolom:
=SOM(ALS(
INTERVAL(
VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0);
VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0)
)>0;1))

In een van de volgende artikelen zal ik de functie INTERVAL eens onder de loep nemen.

Kaartgrafiek 1

Als eerste gaan we een kaartgrafiek maken, waarin per provincie is aangegeven hoeveel gemeentes het bevat.
Daartoe maken we op basis van de gegevens uit de Excel-tabel tblGem een draaitabel (zie het tabblad ProvGem van het Voorbeeldbestand).

We maken nu de grafiek als volgt:

  1. plaats de cursor op een van de provincienamen
  2. kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
  3. op dit moment kent Excel daarbinnen maar één mogelijkheid, de zogenaamde Choropletenkaart

(“Thematische kaart waarop gemiddelde intensiteiten, dichtheden of relatieve waarden van verschijnselen binnen van te voren begrensde gebieden (meestal administratieve eenheden) zijn weergegeven door middel van vlaksymbolen die verschillen in grijswaarde.“)

En dan het resultaat:

Helaas dus, we zullen de gegevens van de draaitabel eerst moeten kopiëren. Dat hebben we in het tabblad ProvGem gedaan naar de kolommen F en G. De werkwijze wordt dan:

  1. selecteer alle cellen met gegevens, inclusief een kopregel. In het voorbeeld is dat het bereik F3:G15
  2. kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
  3. klik op Choropletenkaart

Na de eerste euforie zien we ineens een ‘grijze’ vlek rechtsboven. Wanneer je met de muis over de kaart beweegt zie je per provincie het bijbehorende aantal, maar bij Groningen krijgen we dit:

Excel heeft zelf geen info over landen, gemeentes en dergelijke vastliggen. Om de kaart te kunnen maken worden gegevens van Bing en TomTom opgehaald wanneer je een grafiek probeert te maken. In de praktijk blijkt dit ophalen lang niet altijd vlekkeloos te gaan. In dit geval weet Excel (of Bing?) niet of we de gemeente of de stad Groningen bedoelen en laat deze gegevens dan weg. Maar waarom gaat het bij Utrecht dan wel goed??? Wijzig je cel F8 in: Provincie Groningen dan worden alle provincies ingetekend.

NB bij het experimenteren met de Kaartgrafiek blijkt dat deze ingebouwde optie nog niet geheel stabiel en betrouwbaar is. In het begin worden plaatsnamen of provincies of andere geografische indelingen soms niet direct herkend. Het blijkt te helpen om te blijven proberen met andere en meer namen tot het systeem deze herkend. Daarna wil het vaak ook met namen lukken, die eerst niet geplot werden.

Welke gegevens er gebruikt kunnen worden is ook niet helemaal duidelijk; het is mij gelukt om de regio’s van Frankrijk ‘in te kleuren’ maar niet de departementen (zie het tabblad Frankrijk van het Voorbeeldbestand). En er gebeuren nog meer ‘vreemde’ dingen: bijvoorbeeld de regio in het noord-oosten heb ik Alsace genoemd, maar Excel/Bing vertaalt dit naar Grand Est.

Een goede methode om Excel (of Bing) te helpen bij het analyseren van de gegevens is om bij de categorie ook het hogere niveau op te nemen. In dit voorbeeld is het hogere niveau van de provincies het land.
Klik rechts op een van de provincies in de kaartgrafiek en kies Gegevens selecteren:

Kies bij de aslabels Bewerken en zorg dat ook de kolom met landnamen wordt meegenomen. Ook op deze manier wordt de provincie Groningen herkend:

De standaard-grafiek is op de volgende manier aangepast:

  1. de grafiektitel is aangepast door er in te klikken en wijzigingen aan te brengen.
    LET OP een dynamische grafiektitel (een verwijzing naar een cel in de Excel-sheet) is hierbij niet mogelijk. Uiteraard kun je nog wel een Tekstvak toevoegen, waarin je een dynamische verwijzing maakt (zie het tabblad GemPerProv van het Voorbeeldbestand)
  2. door rechts te klikken op één van de provincies kun je de Gegevensreeks opmaken
  1. de kaartprojectie laten we voor wat het is; voor echte geografen is dit waarschijnlijk wel interessant
  2. normaal blijft het Kaartgebied ook op Automatisch staan.
    Soms is één van de overige instellingen wel handig:

    Experimenteren dus.
  1. bij Kaartlabels heb je 3 mogelijkheden: Geen (dit is de standaardwaarde), Alleen best passend en Alles weergeven. Bij de 2e optie wordt er alleen een label geplaatst wanneer er voldoende ruimte is; bij de 3e manier zul je af en toe een label afgekapt zien (dmv …..)
  2. bij Reekskleur heb ik gekozen voor 3 kleuren; de precieze kleuren en de grenswaarden kunnen daaronder gewijzigd worden
  3. als laatste: klik rechts op één van de provincies en kies Gegevenslabels toevoegen. Door Waarden aan te vinken wordt de Kaartgrafiek aangevuld met de exacte waardes.

Kaartgrafiek 2

Een overzicht van de provincies van Nederland is leuk, maar we willen ook graag per provincie inzoomen.

Het eerste wat we doen is (natuurlijk) een draaitabel maken waarmee we per provincie een overzicht van de gemeentes krijgen (zie het tabblad GemPerProv van het Voorbeeldbestand).

Zoals hiervoor al aangegeven moeten we nog een tussenstap maken voordat we naar de Kaartgrafiek kunnen:
daarom hebben we een paar hulpkolommen (F en G) ingevoegd met daarin een formule =ALS(OF(B5=””;B5=”Eindtotaal”);””;B5)

NB omdat een Kaartgrafiek ook niet kan omgaan met dynamische bereiken (waarbij we gebruik maken van de functie Verschuiving) is er in de hulpkolommen ruimte gemaakt voor 100 gemeentes.

In de hulpkolom E is een verwijzing gemaakt naar de gekozen provincie.

Op basis van deze hulpkolommen (met 100 regels) is de kaartgrafiek gemaakt. Kies je in cel C2 een andere provincie dan zal de kaart automatisch aangepast worden.

NB1 wel zal rechtsboven in de grafiek altijd een waarschuwingsteken staan Klik je daar op, dan zul je zien dat maar een (klein) gedeelte van de 100 regels in de grafiek kunnen worden weergegevens; maar dat is ook logisch.

NB2 helaas, niet voor alle provincies gaat het (direct) goed. Kies je bijvoorbeeld Drenthe dan zie je alleen de provincie en niet de gemeentes.
Dit is weer op te lossen door bij de aslabels de kolom met provincie NIET mee te nemen.
Bij Groningen wordt er helemaal niets getoond! In Noord-Brabant wordt de gemeente Best niet ingevuld, in Limburg hebben we een probleem met de naam Bergen (L).

In de draaitabel wordt in de 2e kolom het aantal gemeentes per gemeente geteld; dat levert natuurlijk altijd als resultaat een 1 op. Ook in de hulpkolom G komt dus een 1. Maar de gebruikte formule daar is iets aangepast: =ALS(OF(B5=””;B5=”Eindtotaal”);””;C5+ALS(RijCur=RIJ();10;0))
Bij de waarde uit kolom C wordt 10 opgeteld als de waarde in de cel met de naam RijCur (dat is hier cel F2) gelijk is aan de Rij waarin de formule staat.
De waarde in cel RijCur wordt aangepast door een kleine VBA-routine die aan dit werkblad is gekoppeld:

Zo gauw de selectie in dit tabblad wordt gewijzigd (door het verplaatsen van de cursor of het klikken met de muis) wordt de waarde van de geselecteerde rij in de cel RijCur geplaatst. Op dat moment komt in kolom G in die rij een hogere waarde te staan, die er automatisch voor zorgt dat deze gemeente in de grafiek een andere kleur krijgt.
Door met de muis op één van de gemeentenamen te klikken kunnen we snel onze topografische kennis opvijzelen!

Kaartgrafiek 3

Een kaartgrafiek kan ook gebruikt worden om een indeling te laten zien.
Als de waardes voor de ‘y-as’ geen getallen zijn maar teksten kiest Excel automatisch dit type grafiek (zie het tabblad Utrecht van het Voorbeeldbestand).

De keuze welk type moet worden gegenereerd zit wat verstopt:

  1. klik rechts op een van de gekleurde vlakken en kies Gegevens selecteren
  2. kies bij de Legendagegevens de optie Bewerken
  1. via de radio-buttons kun je dan een van de twee grafiektypen kiezen

Kaartgrafiek 4

Als voorbeeld staan in het tabblad Corona van het Voorbeeldbestand de aantallen Corona-besmettingen van 17 nov voor de verschillende gemeentes in Zuid-Limburg. Niet alleen de aantallen sec, maar ook het aantal besmettingen per 100.000 inwoners. Dit om de gemeentes met elkaar te kunnen vergelijken.

NB de brongegevens van de eerste grafiek worden gevormd door alle gegevens van de kolommen B, C en D. Een kaartgrafiek kan maar één legendawaarde weergeven, dat zal hier dus kolom C zijn. Wanneer je nu deze kolom verbergt (klik op het -teken boven de D) worden de gegevens van de volgende kolom gebruikt.
Kolom C is in een groep geplaatst: selecteer de hele kolom door op de letter C te klikken en kies dan Groeperen in de menutab Invoegen.

3D-kaartgrafiek

Dit is een combinatie van een 3D– en een Kaartgrafiek.
Microsoft heeft dit niet onder de kaartgrafieken gerubriceerd, althans het zit helemaal ergens anders in de menu-structuur.

  1. plaats de cursor ergens in het bereik met gegevens, die geplot moeten worden. In het voorbeeld is dat in de Excel-tabel tblCorona op het tabblad Corona van het Voorbeeldbestand.
  2. kies dan in de menutab Invoegen de optie 3Dkaart
  3. vul de diverse items als volgt in:
  4. ‘speel’ wat met de zoom- en navigeer-knoppen:

Het onderdeel Rondleidingen kent nog veel meer mogelijkheden, maar het voert voor dit artikel te ver om daar op in te gaan.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: