In het artikel Eerbetoon aan Rosling is gebruik gemaakt van zogenaamde bel-diagrammen, ook vaak bel-grafiek genoemd.
Met behulp van dit soort grafieken is het mogelijk om 3 dimensies weer te geven: via de x- en y-as en door de grootte van de bellen.
Het artikel was aanleiding voor een vraag, die er op neer kwam of er ook nog een vierde dimensie mogelijk was door de kleur van de bellen te variëren.
Met de hand is dit mogelijk maar er is geen standaard-optie, die bij wijzigingen in de sheet de kleuren automatisch zal aanpassen (wat wel gebeurt met de grootte van de bellen).
Kleuren in Excel
Voordat we verder gaan met een mogelijke oplossing voor het probleem van Sander moeten we eerst iets meer weten over de codering van de kleuren in Excel.
Alle kleuren worden opgebouwd door een combinatie van Rood, Groen en Blauw (de zogenaamde RGB-codering). Ieder van die 3 basis-kleuren kan in 256 stappen worden toegevoegd (lopend van 0 tot 255).
Zwart is dan opgebouwd uit 0 delen van iedere kleur, terwijl wit ontstaat door het mengen van alle drie de kleuren met een intensiteit van 255. Door alleen Rood en Groen te mengen (met een intensiteit van 255) ontstaat geel.
Door de blauw-code met 65536 te vermenigvuldigen, de groen-code met 256 en deze 2 dan op te tellen met de rood-code krijgen we een decimale kleurcode.
In het schema hierboven en op het tabblad Param van het Voorbeeldbestand staan enkele voorbeelden.
De decimale codes zullen we hierna gaan gebruiken.
Probleem
Als je een bellengrafiek maakt krijgen de bellen standaard allemaal dezelfde kleur. Deze kunnen gewijzigd worden door één voor één de bellen te selecteren (klik op een bel, dan wordt de hele serie geselecteerd; klik nogmaals op de bel dan wordt de selectie beperkt tot de betreffende bel) en dan via rechts-klikken de opmaak aan te passen.
Maar de vraag is: kan dit geautomatiseerd, waarbij de kleur afhankelijk is van de inhoud van bepaalde Excel-cellen?
Hieronder volgt een oplossing waarbij gebruik wordt gemaakt van VBA; een zelf-geschreven routine controleert van iedere bel wat de corresponderende kleurcode moet zijn en past die aan. Wel moet de routine na iedere wijziging handmatig aangeroepen worden; daar hebben we dan weer een button voor gecreëerd.
Oplossing
Zoals voor ieder bel-diagram hebben we naast waarden voor de x- en y-as ook waarden nodig die de grootte van de bellen zullen bepalen.
NB1 een bel-grafiek is een speciale vorm van een spreiding- of xy-grafiek en kan dus op de assen alleen maar met getallen werken, geen teksten.
NB2 we kunnen niet exact de grootte van de bellen bepalen; Excel bepaalt zelf de grootte relatief ten opzichte van elkaar.
In het tabblad Data van het Voorbeeldbestand is dit tabelletje opgenomen en is een bel-grafiek gemaakt.
Maar nu komt de vraag van Sander: kunnen de bellen een eigen kleur krijgen afhankelijk van het aantal keren, dat een combinatie van x en y voorkomt. Hij zou graag zien, dat een bel rood is als het aantal 1 tot 5 is, geel bij 5 tot 10 en groen bij 10 of meer.
Daarom eerst even een hulptabel opgezet (zie tabblad Param in het Voorbeeldbestand):
- een kolom voor de ondergrens voor een bepaalde kleur
- een kolom met de decimale code voor de gewenste kleur (zie hierboven)
- de 8 cellen zijn in een Excel-tabel geplaatst (via Invoegen/Tabel) en deze tabel heeft de naam tbKleur gekregen.
Aan de gegevens heb ik een vierde kolom toegevoegd met een fictief aantal en nog een kolom die uit Param afleidt welke kleurcode de bel moet krijgen. De gegevens zijn in een Excel-tabel opgenomen met de naam tbData.
De formule in de kolom Kleur ziet er als volgt uit:
=VERT.ZOEKEN([@Aantal];tbKleur;2;WAAR)
Ofwel: neem de waarde uit de corresponderende kolom Aantal, zoek in de Excel-tabel tbKleur deze waarde op en geeft de waarde uit de 2e kolom terug (de kleurcode). We zoeken geen exacte match, maar een ‘benadering’ (de grootste waarde die nog voldoet); vandaar WAAR als laatste parameter in de functie VERT.ZOEKEN.
Alles staat nu klaar om de bellen de juiste kleurcodes te geven.
VBA-routine
Zoals hiervoor aangegeven, kent Excel geen automatische koppeling tussen de kleur van de bellen en de waarde in bepaalde cellen. Dus zullen we het zelf moeten doen:
- in de eerste regel geven we ons programma (subroutine) een naam: BelKleur
- in de 2e en 3e regel zorgen we er voor dat de variabelen pts en x netjes worden gedefinieerd (mbv Dim): in de eerste variabele gaan we punten bewaren, in de tweede gehele getallen.
- in de 4e regel vullen we de variabele pts met de punten (Points) uit de eerste serie (SeriesCollection(1)) van de grafiek (Chart) met de naam grBel (ChartObjects(“grBel”)) in het actieve Excel-tabblad (ActiveSheet)
- dan komt er een For-Next-loop waarbij x loopt van 1 tot het aantal bellen in de grafiek (pts.Count). Alles tussen For en Next wordt dus net zo vaak herhaald als er bellen zijn.
- regel 6 en 7 horen bij elkaar (door de Underscore en een spatie op het einde van regel 6).
De opmaak (Format) van punt x (pts(x)) wordt ingesteld; de bel wordt gevuld (Fill) met een kleurcode (ForeColor.RGB) gelijk aan de inhoud van de cel x+1 (de kop telt ook mee, vandaar +1) uit de kolom Kleur van de tabel tbData uit de actieve sheet (ActiveSheet)
Bellen kleuren
Iedere keer als er iets aan de gegevens wordt gewijzigd (nieuwe regels toegevoegd, aantallen aangepast) zal bovenstaande routine moeten worden uitgevoerd.
Dat kan op verschillende manieren:
- klik op de button Bellen kleuren op het tabblad Data van het Voorbeeldbestand
- kies op de menu-tab Ontwikkelaars de optie Macro’s, selecteer de routine BelKleur en klik op Uitvoeren
- Druk op Alt-F8, selecteer de routine BelKleur en klik op Uitvoeren