Voor het Dashboard Internationale Vrouwendag, wilde ik voor een staafdiagram steeds 1 specifiek punt uitlichten. In dit dashboard kan de gebruiker uit het dropdown-menu een land kiezen. Vervolgens zie je duidelijk hoe het gekozen land scoort op vrouwen in management ten opzicht van andere landen. Ik wil daarom:
- De data van hoog naar laag presenteren. Dan is in 1x duidelijk hoe een land zich verhoudt tot de andere landen uit deze lijst.
- Het geselecteerde land duidelijk uitlichten. Door een andere kleur te gebruiken voor het specifieke land, valt deze goed op.
Hoe licht je nu een specifiek punt uit in een staafdiagram? Terwijl dat punt ook nog eens veranderd afhankelijk van de keuze in het dropdown-menu.
Hoe je dit kunt doen, vond ik in deze blog van Excel of the grid. In deze blogpost laat ik zien hoe ik deze tip heb toegepast om een specifiek punt uit te lichten in een staafdiagram. Er zijn drie stappen; het bewerken van de data, berekeningen maken voor de grafiek en het maken van de grafiek.
Data bewerken
De data komt van de ILO website, de Internationale Arbeidsorganisatie van de VN. De data staat op alfabetische volgorde van de landen en niet van groot naar klein van het percentage.
De 173 landen wil ik niet handmatig op volgorde zetten. Voeg daarom links een nieuwe kolom toe met deze formule:
=RANG(F2,$F$2:$F$174)+AANTAL.ALS($F$2:F2,F2)-1
Deze formule in cel A2 bestaat uit twee delen.
- Met RANG (RANK in het Engels) wordt de positie van het getal in F2 bepaald binnen het bereik F2 tot en met F174.
- Met het AANTAL.ALS (COUNTIF) deel wordt een eventuele dubbele in de ranking voorkomen. Er wordt 1 bij de ranking opgeteld wanneer er een dubbele wordt gevonden. Zo krijgen twee landen met dezelfde score hebben niet dezelfde ranking. Finland en Madagascar hebben bijvoorbeeld beide 31,8% van de vrouwen in management posities en ranken respectievelijk 91e en 92e.
De formule kun je doortrekken naar alle cellen in kolom A. Door het dollar teken ($) blijven deze getallen staan, terwijl andere delen van de formule juist mee veranderen.
Berekeningen maken
Voor deze data is het fijn om de berekeningen in hetzelfde tabblad te maken. Om een bestand met een dashboard overzichtelijk te houden gebruik ik over het algemeen een apart ‘berekeningen’ tabblad.
Maak voor de data voor de grafiek een nieuwe tabel om zo de landen van groot naar klein te kunnen weergeven én om de waarden zo weer te geven dat je straks een specifiek punt kunt uitlichten in je staafdiagram.
Waarden van groot naar klein
Begin met de ranking in kolom H. Om niet alle getallen zelf te typen, begin in cel H2 met 1, in de cel eronder (H3) maak ik de formule =H2+1. Deze trek ik door tot ik bij 173 ben. Dan kopieer en plak ik de getallen als waarde in dezelfde kolom.
Met twee verticaal zoeken (VERT.ZOEKEN of VLOOKUP) formules kun je het betreffende land en het percentage vrouwen in management posities erbij zoeken.
=VERT.ZOEKEN(H2,A$2:F$174,2,ONWAAR)
Voor het land in I2 zoek je met bovenstaande formule naar de waarde in H2, in het bereik A2 tot en met F174 en je wilt de waarde uit de tweede kolom hebben. Kolom B, met het land erin.
=VERT.ZOEKEN(H2,A$2:F$174,6,ONWAAR)
Voor het percentage vrouwen in management posities in J2 zoek je met de bovenstaande formule naar de waarde in H2, in het bereik A2 tot en met F174 en je wilt de waarde uit de zesde kolom hebben. Kolom F, met het totale percentage vrouwen in management.
Je tabel ziet er dan zo uit.
Waarden toevoegen voor het staafdiagram
Met de lijst op volgorde kun je er nu voor zorgen dat het gekozen land uit het dropdown-menu wordt uitgelicht. Daarvoor moet je twee nieuwe kolommen maken; een voor de ‘gewone’ data en een voor het uitgelichte punt in de staafdiagram. In ons geval het gekozen land.
Je kunt hiervoor twee ALS (IF in Engels) functies gebruiken. De formule in K2 voor de ‘gewone’ data is:
=ALS(I2=Dashboard!$C$3,NB(),J2)
In de formule valt als volgt te lezen. Als het land in cel I2 gelijk is aan het gekozen land uit de dropdown-lijst (Dashboard!$C$3), dan krijg je #N/B (of #N/A in het Engels) als resultaat. Anders krijg je de waarde uit J2 als resultaat.
=ALS(I2=Dashboard!$C$3,J2,NB())
Voor de formule in L2 (voor het uitgelichte land) wil je precies het omgekeerde resultaat. Als het land in cel I2 gelijk is aan het gekozen land uit de dropdown-lijst (Dashboard!$C$3), dan krijg je de waarde uit J2 als resultaat. Anders krijg je #N/B.
Dat je waarde #N/B krijgt is belangrijk, deze wordt in de grafiek niet gebruikt. Zou je waarde 0 als resultaat krijgen, dan zou de grafiek waarde 0 laten zien. De tabellen zien er uiteindelijk zo uit.
In dit geval zal in de staafdiagram de score van Jamaica worden uitgelicht. Voor dit land staat een getal in de highlight kolom en niet in de kolom ‘normaal’.
Staafdiagram maken
Nu je de data klaar hebt, kun je het staafdiagram maken waarin je een specifiek punt uitlicht.
- Selecteer de cellen I1 tot en met L174, de ranking uit kolom H heb je niet nodig, omdat de gegevens al op volgorde staan.
- Voeg een grafiek toe en kies voor een gestapelde kolomdiagram. In je grafiek staan nu de gegevens uit de kolommen ‘total management’, ‘normaal’ en ‘highlight’.
- De gegevens ‘total management’ haal je weg, deze informatie is dubbelop en heb je niet meer nodig. Deze heb je immers uitgesplitst naar normaal en highlight.
- Maak de grafiek op naar je eigen wensen. Mijn keuzes waren de volgende:
- Een gecentreerde titel met
vergelijkbare kleur en font als de andere grafiektitels.
- De normale reeks wordt lichtgrijs, de kolom die uitgelicht moet worden wordt roze.
- De legenda gaat weg.
- De landen op de X-as gaan weg. Die doe je bij instellingen, niet bij aanpassingen.
- De rasterlijnen maak ik wit. Deze kan je niet altijd weghalen, maar door ze dezelfde kleur als de achtergrond te maken zijn ze niet meer zichtbaar.
Deze grafiek, waarin een specifiek punt wordt uitgelicht in een staafdiagram is onderdeel van het Dashboard Internationale Vrouwendag (2020).
Het dashboard kun je in Google Sheets bekijken. Wil je meer weten? Schrijf je in voor de nieuwsbrief.