Huizenmarkt



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:

2 gedachten over “Huizenmarkt

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *