De World Inequality Database website geeft een mooie overzichtsgrafiek wanneer je de inkomens distributie simulator invult. In Excel kun je dit staafdiagram met verschillende kleuren voor de balken ook maken.
Excel staafdiagram met verschillende kleuren balken
De site van de World Inequality Database is voor liefhebbers van data, infographics en interactieve dashboarden een speeltuin. Om de vraag ‘hoe rijk ben ik eigenlijk?’ te beantwoorden, kun je de inkomens distributie simulator doen. Op basis van je ingevulde gegevens krijg je een overzicht in welke inkomensgroep je je bevindt.
In Excel kun je de balken van zo’n staafdiagram verschillende kleuren geven en in deze blog leg ik uit hoe je dat doet. De uitleg is een aangepaste versie van de blog over hoe je een specifiek punt uitlicht in een staafdiagram. In het dashboard Internationale Vrouwendag stond een staafdiagram waarbij 1 land duidelijk wordt uitgelicht.
De bovenstaande grafiek Vrouwen in Management en de gegevens hiervan gebruiken we in deze blog als basis om de balken in dit staafdiagram verschillende kleuren te geven.
3 verschillende balken, 3 verschillende kleuren, 3 verschillende kolommen
Van 172 landen hebben we het percentage vrouwen in managementposities. Welk land je ook kiest; er zijn drie kleuren balken mogelijk:
1. De uitgelichte balk: het land van jouw keuze (oranje)
2. De balken met een kleinere waardes: de landen die lager scoren dan je keuze (grijs)
3. De balken met een grotere waardes: de landen die hoger scoren dan je keuze (geel)
We kunnen straks drie verschillende kleuren balken maken in het Excel staafdiagram door de gegevens in 3 verschillende kolommen te zetten. In welke kolom de gegevens staan, bepaalt de kleur van die gegevens in de grafiek. Wanneer de gebruiker van je dashboard een land kiest, veranderen op basis daarvan de gegevens in de kolommen.
Kiest de gebruiker voor land ‘Nederland’ dan komen de gegevens van dat land in de highlight kolom te staan en krijgt deze de oranje kleur in de grafiek. Kiest een gebruiker het land ‘Jamaica’, dan komen de gegevens van dit land in de highlight kolom te staan en worden deze gegevens in de grafiek oranje. Dit geldt ook voor de kleinere en grotere waardes van je keuze.
De uitgelichte balk maken
De uitgelichte balk is afhankelijk van de landkeuze. Kiest iemand voor Jamaica, dan worden de managementgegevens van Jamaica uitgelicht in oranje, kiest iemand voor Nederland, dan worden de Nederlandse managementgegevens oranje.
Daarvoor maken we allereerst een dropdown-lijst via Data Validation (uit menu Data).
Vervolgens kiezen we ‘Lijst’ en selecteren we de landen in kolom B als de brongegevens voor de lijst. Zo komen deze landen in de dropdown-lijst te staan en kunnen gebruikers alleen kiezen uit deze lijst.
Vervolgens voegen we met een verticaal zoeken (vlookup) formule de gegevens van het gekozen land toe in cel I4. De formule is =VERT.ZOEKEN(H4;B:C;2;FALSE).
In de highlight kolom willen we alleen dat het uitgelichte land (in de afbeelding Jamaica) de managementdata krijgt. De andere landen krijgen de waarde 0 in deze kolom. Dat kan met een ALS formule. Als de waarde in landenlijst (kolom B), gelijk is aan het gekozen land uit het dropdownmeu (cel H4), dan willen we de managementdata zien in kolom D. Anders willen we waarde 0.
De formule hiervoor is =IF(B2=$H$4;C2;0).
In de afbeelding zie je dat alleen bij Jamaica een waarde in kolom D staat. Voor de andere landen staat er door de formule een 0.
De balk maken met de lagere getallen
De balken met de lagere gegevens zijn eveneens afhankelijk van de landkeuze. Kiest iemand voor Jamaica, met 56,7% vrouwen in management, dan willen we van alle landen met een lagere score een grijze balk in de grafiek. Van de landen met een hogere score willen we de gegevens niet te zien; die maken we 0.
De formule is =IF(C2<$I$4;C2;0)
Met de ALS-formule zetten we gegevens in kolom E. Als het percentage vrouwen in management in dat land (kolom C) kleiner is dan de gegevens van het gekozen land (getal in I4), dan willen we die gegevens ook in de ‘lager’ kolom (kolom E) hebben. Zijn het percentage van het land groter, dan willen we in de ‘lager’ kolom (kolom E) een 0 zien.
In de bovenstaande afbeelding zie je dat waarde voor Côte d’Ivoire (56,9%) hoger is dan de waarde van het uitgelichte land Jamaica (56,7%). Deze krijgt een 0. De waarde voor Indonesia is wél lager (55%) dan dat van Jamaica. Daarom willen we die waarde wel zien in kolom E.
De balk maken met de hogere getallen
De berekening voor de hogere gegevens is vergelijkbaar met wat hierboven is beschreven, maar dan net omgekeerd. In dit geval wil je bij de landkeuze Jamaica alle landen met een hoger score geel maken. De landen met een lagere score krijgen een 0 in deze kolom.
Met deze ALS-formule =IF(C2>$I$4;C2;0) zetten we gegevens in de ‘hoger’ kolom (kolom F). Als de gegevens uit kolom C groter zijn dan het getal in I4 (de managementgegevens van het gekozen land) dan willen we de gegevens uit kolom C in de ‘hoger’ kolom hebben, anders willen we een 0.
Je ziet dat er voor Côte d’Ivoire (56,9%) in deze ‘hoger’ kolom wel een waarde is omdat deze hoger is dan de waarde van het uitgelichte land Jamaica (56,7%). Nu krijgt Indonesia, omdat deze lager is, een 0. Overigens krijgt Jamaica in de ‘lager’ en ‘hoger’ kolom een 0 omdat deze al in de highlight kolom staat.
In de bovenstaande afbeelding zie je dat waarde voor Côte d’Ivoire (56,9%) hoger is dan de waarde van het uitgelichte land Jamaica (56,7%). Deze krijgt een 0. De waarde voor Indonesia is wél lager (55%) dan dat van Jamaica. Daarom willen we die waarde wel zien in kolom E.
Nu de gegevens klaar zijn, kunnen we in Excel het staafdiagram met verschillende kleuren balken gaan maken.
Het staafdiagram met verschillende kleuren balken maken
Selecteer de gegevens (de ranking is niet nodig) en voeg een gestapelde kolomgrafiek toe. Alle gegevens uit de kolommen totaal management, highlight, lager en hoger worden bij elkaar opgeteld per land. Je krijgt dan het onderstaande resultaat.
Om tot het juiste (en mooiere) staafdiagram te komen, zet je de volgende stappen:
1. Haal de gegevens van totaal management weg. In de bovenstaande afbeelding zijn deze blauw. Je kunt deze gegevens weghalen door in de grafiek op deze gegevens te klikken (zorg dat je de hele serie selecteert) en te verwijderen. Gewoon delete.
2. Maak een dynamische grafiektitel door te verwijzen naar cel I4. Selecteer hiervoor de grafiek titel en ga naar de formule balk. Vul in de formulebalk =I4 in.
3. Maak de grafiek op zoals jij ‘m mooi vindt. Ik heb de legenda, landen op de x-as en rasterlijnen weggehaald. De y-as heb ik aangepast (zonder cijfers achter de komma en tot 60%) en de highlight heb ik dikker gemaakt door ook de randen dezelfde kleur te geven.
