(Excel) Kaart maken met verdeling in postcodes

[Ridicuul]

Mijn vraag

Voor mijn werk moet ik een overzicht maken van een bepaalde waarde per postcode groep in de vorm van een kaart. Het is me gelukt om via excel een kaart te maken met alle postcodes in België, maar dat zijn er nogal wat en die kaart is dus totaal niet praktisch.

Dus hebben we nu het idee om te sorteren op de eerste twee getallen van de postcodes en op basis daarvan 1 waarde toe te wijzen per groep. Op deze afbeelding van Wikipedia is deze verdeling goed te zien:

https://en.wikipedia.org/...git_postcode_belgique.png

Ik wil dus via Excel een kaart maken zoals bovenstaand, met de nummers erin (dus de eerste twee getallen van de postcode in die regio) en aan elke regio een waarde toewijzen. De waardes variëren van 1 tot 7. Ik wil dan iedere waarde een kleur geven, zodat je in een oogopslag kan zien welke regio een hoge of lage waarde heeft.

Relevante software en hardware die ik gebruik

Excel (Office 365 abonnement)

Wat ik al gevonden of geprobeerd heb

Ik heb veel video's en geschreven tutorials gekeken/gelezen, maar het is me tot nu toe alleen gelukt om op basis van subdivisions (via data) of alle postcodes (door handmatig een lijst toe te voegen). Maar ik krijg het dus niet voor elkaar om een lijst op basis van de eerste 2 getallen te maken, want dan snapt Excel het niet meer.

Ik had bijv. geprobeerd om de lijst zo op te maken:
10-12
13-14
15-19

[dix-neuf]

Ik begrijp het niet.
Waarom worden 10 en 12, 13 en 14, en 15 en 19 aan elkaar gekoppeld?

[Ridicuul]

dix-neuf schreef op dinsdag 11 oktober 2022 @ 13:37:
Ik begrijp het niet.
Waarom worden 10 en 12, 13 en 14, en 15 en 19 aan elkaar gekoppeld?

Dat maakt voor de kaart eigenlijk niet uit, alleen voor de legenda naast de kaart.
We koppelen de postcodes op basis van regio aan elkaar. Zo zijn alle postcodes die beginnen met 10, 11 en 12 onderdeel van Brussels Gewest. 13 en 14 vormt Waals Brabant en postcodes met de eerste twee cijfers 15 t/m 19 vormen Vlaams Brabant. Maar op de kaart laten we elke regio individueel zien, dus daarom maakt het koppelen van de postcodes niet uit en had ik dat beter achterwege kunnen laten in de openingspost. Excuses.

Maar het wordt duseen enorme rommel als ik voor alle postcodes een kaart maak. Dat zijn namelijk 1200 regio's. Dan wordt alles veel te klein en kan je dus nooit meer het getal van de postcode in de kaart verwerken of op normale manier de kaart lezen. Door alleen de eerste twee getallen te pakken, houden we het overzichtelijk (ongeveer 90 regio's).

Sorteren op provincie of subdivision zoals Excel het noemt is ook geen optie, omdat deze regio's te groot zijn. Het moet echt per eerste twee getallen van de postcode.

[dix-neuf]

En hoe zouden we daarbij kunnen helpen?

[Reptile209]

Ridicuul schreef op dinsdag 11 oktober 2022 @ 15:38:
[...]

Sorteren op provincie of subdivision zoals Excel het noemt is ook geen optie, omdat deze regio's te groot zijn. Het moet echt per eerste twee getallen van de postcode.

Ik volg ook nog niet helemaal wat je probleem nu is.

Maar even een gokje: je hebt nu een enrorme lijst met postcodes, en moet bepalen in welke regio elke code valt (op basis van de eerste twee cijfers). Makkelijkste is dan om in de bestaande lijst een hulpkolom toe te voegen met een "regiocode". En dan een look-up table met je postcodes, waar je met VLOOKUP of VERT.ZOEKEN de juiste regio aan de postcode koppelt:

Postcode voorloop	Regiocode
10	Reg01
11	Reg01
12	Reg01
13	Reg02
14	Reg02
15	Reg03
16	Reg03
17	Reg03
18	Reg03
19	Reg03

En dan moet het niet meer zo ingewikkeld zijn om met een (draai)tabel je gewenste waarde aan elke regio toe te kennen en die op je kaart te zetten.

[Patrick_6369]

Misschien even out of the box gedacht en ik weet niet of dat een optie is, maar PowerBI heeft een functie om data op een kaart te laten zien op basis van de adressen. Op basis van de zoom van de kaart verandert het aggregatieniveau.
Heb de functie zelf nooit serieus gebruikt overigens, dus weet de kwaliteit ook niet.

[ Voor 11% gewijzigd door Patrick_6369 op 12-10-2022 08:12 ]

[Belindo]

Patrick_6369 schreef op woensdag 12 oktober 2022 @ 08:12:
Misschien even out of the box gedacht en ik weet niet of dat een optie is, maar PowerBI heeft een functie om data op een kaart te laten zien op basis van de adressen. Op basis van de zoom van de kaart verandert het aggregatieniveau.
Heb de functie zelf nooit serieus gebruikt overigens, dus weet de kwaliteit ook niet.

Zeker een goede en mooie functie. Echter is het dan wel noodzakelijk om je data goed te structureren en PBI te laten weten wat de basis van de kaart is (lat/lon, city, street, postal code). Net even gekeken, en postalcode zit er ook in. Wel moet je even land Belgium toevoegen aan je data, anders worden de postcodes random in de wereld geplaatst (maar net waar er een match is volgens MS). Wanneer je het land erbij zet, worden alle postcodes netjes in Belgie geplot.

Even een mock-up met aantal klanten en omzet per postcode:


Of per provincie:


Het mooie van Power BI is dat je kunt beginnen met het tonen van je data voor Belgie, dan een drilldown down op Belgie om het per provincie te zien. Vervolgens kun je weer een drilldown doen op de provincie om het per postcode te zien.

[Ridicuul]

Reptile209 schreef op dinsdag 11 oktober 2022 @ 15:58:
[...]

Ik volg ook nog niet helemaal wat je probleem nu is.

Maar even een gokje: je hebt nu een enrorme lijst met postcodes, en moet bepalen in welke regio elke code valt (op basis van de eerste twee cijfers).

Snap ik. Laat me het proberen nog iets te verduidelijken. In ieder geval al bedankt voor het meedenken.

Uiteindelijk wil ik dit kaartje:



Gaan invullen met kleurtjes. Naast dit kaartje komt een legenda. Laten we zeggen: donkerrood = 1, wit = 7 en alles daar tussenin verloopt in een rode tint.

De postcodes heb ik. Inderdaad een lange lijst met getallen, we kijken even niet naar letters. Ik moet die lijst uiteindelijk terugbrengen naar de +/- 90 regio's op bovenstaand kaartje. Hiervoor moet ik dus een waarde toewijzen aan regio 10. Regio 10 bestaat uit alle postcodes die beginnen met 10xx. Laat dat 5 verschillende postcodes zijn. Ik had de hoop dat ik via Excel die regio van 5 postcodes 1 waarde zou kunnen geven en dat het kaartje dan automatisch ingekleurd wordt. Dit is me wel gelukt met alle individuele postcodes, maar ik zoek dus een aggregatieniveau daarboven.

Dit moet ik uiteindelijk voor meerdere landen uit gaan werken en ook met meerdere datasets. Dus een kaartje handmatig inkleuren kan wel, maar het is niet zo efficiënt als ik uiteindelijk pak 'm beet 100 kaartjes handmatig inkleur. En dan moet ik ook nog eens voor alle verschillende landen een vergelijkbaar kaartje vinden. Het ziet er namelijk niet uit als het ene kaartje brede lijnen tussen de regio's heeft lopen en het andere kaartje weer hele dunne.

Lang probleem kort: hoe kleur ik een kaart met geclusterde (en genummerde) postcode in, op zodanige wijze dat ik dit meerdere malen kan reproduceren met verschillende geografische gebieden én datasets?

Ik kreeg de tip dat zoiets via Excel kan, maar ik loop er dus op vast dat ik alleen kan clusteren op 'subdivision' en 'zip code' niveau in Excel. Ik weet ook niet hoe je zo'n regio (van alle postcodes met dezelfde prefix van 2 getallen) noemt eigenlijk, is daar eigenlijk wel een naam voor?

Nogmaals bedankt voor iedereen die meedenkt of al heeft gedacht.

[Lustucru]

Ridicuul schreef op woensdag 12 oktober 2022 @ 14:32:
[...]

Dit is me wel gelukt met alle individuele postcodes, maar ik zoek dus een aggregatieniveau daarboven.

Ik ben vooral benieuwd hoe het je is gelukt voor de individuele postcodes, om die gekleurd op een kaart te plotten. Als je dat kunt uitleggen, verwacht ik dat het een koud kunstje is om het per groep / supergroep whatever te doen.

O wacht, je gebruikt gewoon de kaartgrafiek icm Bing? Laatste keer dat ik die probeerde te gebruiken voor nederlandse postcodes was een ramp. Ik heb ook geen idee welke aanduidingen hij slikt voor Belgie. Het laatste redmiddel wat je dan hebt is de waarde uitrekenen per postcode, zodat hij ze wel per postcode plot, maar het verschil minder zichtbaar wordt. Of je zet het om naar een andere regioindeling die Bing wel snapt.

Wat ook handig is, is als je uitlegt hoe de waardering van een regio tot stand komt. Is dat domweg het gemiddelde van de waarderingen per postcodegebied, moet het getotaliseerd worden, etc. ect.?

[ Voor 23% gewijzigd door Lustucru op 12-10-2022 15:12 ]

[Reptile209]

Ridicuul schreef op woensdag 12 oktober 2022 @ 14:32:
[...]


Hiervoor moet ik dus een waarde toewijzen aan regio 10. Regio 10 bestaat uit alle postcodes die beginnen met 10xx. Laat dat 5 verschillende postcodes zijn. Ik had de hoop dat ik via Excel die regio van 5 postcodes 1 waarde zou kunnen geven en dat het kaartje dan automatisch ingekleurd wordt. Dit is me wel gelukt met alle individuele postcodes, maar ik zoek dus een aggregatieniveau daarboven.

Heb je hier nou naar gekeken? Reptile209 in "(Excel) Kaat maken met verdeling in postcodes"

De eerste twee cijfers van de postcode pak je door de code te delen door 100 met omlaag afronden (3456 / 100 geeft dan 34), of door de eerste twee tekens van de string te splitsen met LEFT() of LINKS(). Via de lookup table die ik suggereerde, kan je dan meerdere codegebieden dezelfde "regiocode" geven, en die kan dan weer naar je kaart.

[Teun_2]

Ridicuul schreef op dinsdag 11 oktober 2022 @ 15:38:
[...]

Dat maakt voor de kaart eigenlijk niet uit, alleen voor de legenda naast de kaart.
We koppelen de postcodes op basis van regio aan elkaar. Zo zijn alle postcodes die beginnen met 10, 11 en 12 onderdeel van Brussels Gewest. 13 en 14 vormt Waals Brabant en postcodes met de eerste twee cijfers 15 t/m 19 vormen Vlaams Brabant. Maar op de kaart laten we elke regio individueel zien, dus daarom maakt het koppelen van de postcodes niet uit en had ik dat beter achterwege kunnen laten in de openingspost. Excuses.

Maar het wordt duseen enorme rommel als ik voor alle postcodes een kaart maak. Dat zijn namelijk 1200 regio's. Dan wordt alles veel te klein en kan je dus nooit meer het getal van de postcode in de kaart verwerken of op normale manier de kaart lezen. Door alleen de eerste twee getallen te pakken, houden we het overzichtelijk (ongeveer 90 regio's).

Sorteren op provincie of subdivision zoals Excel het noemt is ook geen optie, omdat deze regio's te groot zijn. Het moet echt per eerste twee getallen van de postcode.

Ben je bekend met de kaarten in excel? Daar kan je echt perfect postcodes en getallen aan toevoegen. Via mouseover kan je dan de waarde consulteren op de kaart. Provincies of gewesten kan ook, arrondissementen herkent Bing maps helaas niet. Via invoegen -kaarten - choropletenkaart. Deze kan je verder zelf bewerkten als een normale grafiek.