Tagarchief: Kaartgrafiek

Huizenmarkt

Excel,
Voorbeeldbestand: HuizenMarkt.xlsxDownloaden

Deze week stond er in de Volkskrant een artikel over de heftige ontwikkeling van de huizenprijzen tussen 2020 en 2021.
Er zijn al enkele gemeentes waar de gemiddelde huizenprijs boven de miljoen euro ligt!

Via de link volkskrant.nl/huizenmarkt kun je de gegevens van alle gemeentes van Nederland terugvinden. Het gaat dan over de gemiddelde prijzen in 2020 en 2021, de ontwikkeling tussen die twee jaren en ook de gemeentes met de hoogste en laagste huizenprijzen.

Om het analyseren van de (ontwikkeling van de) huizenprijzen makkelijker te maken hebben we de gegevens in Excel overgenomen en verrijkt met de bijbehorende provincie. In bijgaande werkmap kun je dan ook de gegevens per provincie bekijken.

In dit artikel laten we de diverse elementen van de Excel-sheet de revue passeren.

Brongegevens

De gegevens van de Volkskrant-website zijn opgenomen op het tabblad GemHuisPrijs van het Voorbeeldbestand.

Per gemeente ziet u de gemiddelde huizenprijs in 2020 en 2021.

Het ligt voor de hand om deze gegevens in een kaartgrafiek weer te geven om snel een overzicht over heel Nederland te krijgen.

  1. selecteer de gegevens van de eerste twee kolommen
  2. kies op de menutab Invoegen in het blok Grafieken de optie Kaartgrafiek

De gemeentes met de hoogste huizenprijzen in 2021 vallen direct op.

Helaas zijn er ook ‘blinde vlekken’ te zien; Excel (of Bing?) herkent niet alle gemeentenamen zoals ze in het overzicht zijn opgenomen.

Gegevens transformeren

Zoals hiervoor aangegeven willen we de gegevens nog verrijken met de bijbehorende provincie. Ook het ‘blinde vlekken’-probleem willen we oplossen.

Sinds het beschikbaar zijn van Power Query in Excel wordt ons dit wel heel makkelijk gemaakt!

Op de website van het CBS kun je de de gemeentes per provincie vinden: www.cbs.nl/nl-nl/onze-diensten/…/gemeentelijke-indeling.

In het tabblad Gemeenten_alfabetisch_2021 van het Voorbeeldbestand zijn deze overgenomen.

De kaart-grafiek herkent niet alle namen van de gemeentes (zie ook Excel en kaarten 2).
Via een hulptabel gaan we proberen Excel ‘wat bij te leren’ (zie het tabblad GemNamen; hoe we hier mee omgaan komt later).

Van deze drie overzichten (basisgegevens, gemeentes per provincie en gemeentenamen-correctie) zijn Excel-tabellen gemaakt met respectievelijk de namen tblHuisPrijsGem, tblGemProv en tblGemNamen. Binnen Power Query zijn verbindingen gemaakt met deze drie tabellen (zie voor de techniek bijvoorbeeld het artikel Power Query).

Met de optie Query’s samenvoegen binnen Power Query zijn deze verbindingen aan elkaar gekoppeld (zie de stappen binnen de query q_Resultaat). De laatste stap is het meest interessant: de drie kolommen (2020, 2021 en een berekende kolom Wijz%) worden omgezet naar een database-structuur.
Het resultaat staat in het tabblad NwData van het Voorbeeldbestand:

NB we gaan deze nieuwe tabel gebruiken als bron voor draaitabellen. Om de bestandsgrootte te beperken hadden we er ook voor kunnen kiezen om het resultaat van de query alleen aan het gegevensmodel toe te voegen (zie het artikel Power Query) en daar de draaitabel op te baseren.

Overzichten

Het eerste overzicht (een landkaart met gemeentes) gaan we baseren op een draaitabel met als filters de Provincie en het Kenmerk (2020, 2021 of Wijz%; zie het tabblad Draai van het Voorbeeldbestand).
Aangezien Kaart-grafieken niet rechtstreeks gekoppeld kunnen worden aan een draaitabel maken we naast de draaitabel een hulptabel:

Als we dan toch bezig zijn: als het kenmerk Wijz% is, dan vermenigvuldigen we de waarde met 100, dat is beter interpreteerbaar.

NB achteraf gezien hadden we beter bij de berekening van Wijz% binnen Power Query deze vermenigvuldiging kunnen doen!

Op basis van deze hulptabel hebben we een kaartgrafiek gemaakt en aan de draaitabel zijn 2 slicers toegevoegd (voor de Provincie en het Kenmerk). De grafiek en de slicers zijn ‘geknipt’ en in een nieuw tabblad Dashboard geplakt.

NB1 Aangezien de grafiektitel in een kaartgrafiek niet dynamisch kan zijn (dus gekoppeld aan een cel) is de titel vervangen door een tekstblok. De inhoud daarvan is gelijk aan de cel E3 van het tabblad Draai:
=ALS(C3=”Wijz%”;”Verandering tussen 2020 en 2021″;”Gem. huisprijzen in “&C3)&
ALS(OF(C2=”(Alle)”;C2=”(Meerdere items)”);””;”
(“&C2&”)”)

NB2 de overgang in de formule hierboven van de 2e naar de 3e regel is ingevoerd door de toetscombinatie Alt-Enter, waardoor er in het tekstblok ook altijd op die plaats een regelovergang is.

NB3 voor de kaartgrafiek is een maximaal bereik van cellen geselecteerd, zodat iedere keuze van Provincie (of heel Nederland) meegenomen wordt. De formule in kolom F is daartoe wat uitgebreid anders zou de ondergrens van de legenda altijd 0 zijn; in cel F7 staat:
=ALS(C7=””;MIN($F$6:F6);C7*ALS($C$3=”Wijz%”;100;1))

LET OP Excel kan voor de provincies Drenthe en Groningen geen kaart genereren. Eén of meer plaatsen worden niet herkend?
Het vreemde is, dat het voor Drenthe wel werkt als je ook Overijssel kiest (hou Ctrl ingedrukt bij het selecteren in de slicer)! En Groningen wordt zichtbaar als je de drie andere provincies in de buurt ook kiest.

Dit is het moment om te kijken of de kaart nog ‘blinde vlekken’ heeft. Als je weet welke gemeente(s) het betreft kun je op het tabblad GemNamen proberen of een andere naam of toevoeging aan de naam werkt. In de eerste kolom komt de naam zoals die voorkomt in het eerste bronbestand, in de tweede kolom plaats je een naam waarvan je denkt/hoopt dat Excel die zal herkennen.
Vergeet niet alle verbindingen en draaitabellen te vernieuwen: klik op de button Alles vernieuwen op de menutab Gegevens:

In het tabblad Draai van het Voorbeeldbestand is nog een tweede draaitabel gecreëerd met daarin de Top-10 van de gemeentes (zie voor de werkwijze het artikel Top-5; verschillende methodes). Hier willen we een Bellengrafiek van maken net als in de Volkskrant. Ook dan moeten we met een hulptabel werken:

De draaitabel is automatisch gekoppeld aan de bestaande slicers. Wanneer we op basis van de hulptabel een Bellengrafiek maken en de assen, rasters en overige ‘ballast’ weglaten, kunnen we ook deze grafiek naar het tabblad Dashboard kopiëren.

In de cellen naast de grafiek maken we verwijzingen naar de betreffende cellen in het tabblad Draai. Wel eerst de achtergrond van het teken- en grafiekgebied transparant maken.

Nog een tekstblok met een rand er omheen, et voilà.

Ook voor de laagste prijzen (of wijzigingspercentage) maken we op dezelfde manier een grafiek op basis van een draaitabel en een hulptabel.

Het resultaat mag er zijn (zie het tabblad Dashboard van het Voorbeeldbestand).

En nog interactief ook:

Excel en kaarten 2

Excel, VBA (Visual Basic for Applications), ,
Voorbeeldbestand: ProvGem.zipDownload

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 3D–kaart
  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.