Een dashboard is leuk, maar het wordt voor de gebruiker nog interessanter als het dashboard verandert door de keuze van de gebruiker. Hoe maak je nu zo’n interactieve grafiek met een keuzemenu?
In dit blog leg ik je uit hoe je in Google Sheets interactieve grafieken kunt maken aan de hand van het dashboard Internationale vrouwendag. De gebruiker kiest een land en de data in het dashboard verandert mee.
Je begint met een idee over de layout van het dashboard en de data die nodig is. Vervolgens kun je aan de slag met de grafieken.
Data bewerken
Het is handig om de berekeningen en bewerkingen van de data in een apart tabblad te doen. Op deze manier staan alle berekeningen bij elkaar en als er ergens een fout zit, is deze fout makkelijker te vinden.
Maak in het berekeningen tabblad een verwijzing naar het keuze menu in je dashboard. Als in het dashboard voor Nederland wordt gekozen, dan verschijnt Nederland ook in je berekeningen tabblad. Zo maak je minder fouten. In dit dashboard verwijs ik in cel A2 naar het dropdown menu die in cel C3 in het tabblad Dashboard staat.
Interactieve grafiek met totaal gemiddelde vs geselecteerde land
Het ‘world average female’ percentage op de arbeidsmarkt bereken je door het gemiddelde van alle landen te nemen. Die data staat in het tabblad met de naam females_aged_25_54_labour_force. Voor 1990 neem je het gemiddelde uit kolom B, voor 1991 het gemiddelde uit kolom C, etc.
=GEMIDDELDE(females_aged_25_54_labour_force!B2:B181)
De lege cellen worden in de berekening niet meegenomen. De formule trek door naar rechts met behulp van het plusje wat je krijgt als je rechts onderin de cel staat.
Dit wereldwijde gemiddelde wil je vervolgens vergelijken met het percentage van het gekozen land. Het gemiddelde van een land bereken je met de formule verticaal zoeken.
Bij verticaal zoeken moet je elke keer aangeven uit welke kolom je de data wilt halen (uit de 2e kolom, de 3e of de 26e). Dat betekent dat je elke keer opnieuw de formule zou moeten aanpassen. Dat kan sneller door in rij 1 steeds de getallen van de kolom waarin je wilt zoeken te zetten. En daar in je formule naar te verwijzen.
=VERT.ZOEKEN($A2,females_aged_25_54_labour_force!$A:$AE,B1,ONWAAR)
Zo heb je de data voor de lijngrafiek voor arbeidsparticipatie waarbij je het gemiddelde van alle landen (in rij 3) vergelijkt met het gekozen land (in rij 4).
Interactieve grafiek met 1 land
Voor de ratio meisjes/jongens in het basis en voortgezet onderwijs hoef je alleen de data te laten zien van het gekozen land. In de grafiek wordt dit niet vergeleken met een wereldwijd gemiddelde.
Hier gebruik je ook een vertikaal zoeken formule voor én maak je gebruik van hetzelfde trucje als hierboven, door in de formule te verwijzen naar de getallen in rij 1. Dat scheelt veel typewerk. De formule is als volgt:
=VERT.ZOEKEN($A8,ratio_of_girls_to_boys_in_prima!$A2:$AW191,B1,ONWAAR)
Voor Nederland blijft cel B8 leeg. Hierin zou de data voor 1970 moeten staan, maar er is voor dat jaar geen data. Dat deze cel leeg blijft en niet 0 wordt, is prettig, omdat in de grafiek anders ook 0 als gegeven zou komen te staan.
Gemiddelde zonder #N/B
In de grafiek volksvertegenwoordiging van vrouwen (% vrouwen in het parlement, senaat etc), wordt het wereldwijde gemiddelde vergeleken met het percentage van het gekozen land.
In het data bestand hebben landen waar in een bepaald jaar geen score voor is, #N/B staan. Dat zorgt voor een foutmelding als je het gemiddelde wilt berekenen. Je kunt twee dingen doen:
- Alle #N/B verwijderen in de data. Je past hier wel je brongegevens mee aan en maakt mogelijk een fout.
- De formule voor het gemiddelde aanpassen, zodat #N/B niet wordt gebruikt in de berekening van het gemiddelde.
In plaats van gemiddelde, gebruik je gemiddelde.als. Vervolgens sluit je #N/B uit door er <> voor te zetten. Aangezien het een tekst is en geen getal, moet je dit tussen aanhalingstekens zetten. De formule van het gemiddelde ziet er dan als volgt uit:
=GEMIDDELDE.ALS(‘Percentage of women in chambers’!B2:B194,”<>#N/A”)
Dit gemiddelde wil je vergelijken met de score van het gekozen land. Daarvoor gebruik je een vergelijkbare verticaal zoeken formule:
=VERT.ZOEKEN($A13,’Percentage of women in chambers’!$A2:$BX194,B1,ONWAAR)
Voor 1945 in Nederland is er geen percentage beschikbaar (ik denk dat het land iets anders te doen had dan meten wie er in het parlement zaten) en daarom staat er #N/B. In de grafiek is er voor dit jaartal dan geen getal zichtbaar.
Overige berekeningen
De berekeningen voor vrouwen in management staan in de sheet met de data zelf. Hoe je deze berekeningen maakt voor de staafdiagram waarbij een specifiek land wordt uitgelicht, staat in dit blog beschreven.
De berekeningen voor de stemrecht kun je ook in hetzelfde tabblad doen. Voeg een kolom toe voor het aantal jaren tussen universeel stemrecht en het eerste (mannen) stemrecht door deze van elkaar af te halen. De ranking bereken je door de kolom met universeel stemrecht van laag naar hoog te zetten en deze landen de ranking van 1 tot het einde te geven.
Dashboard maken
Nu de berekeningen af zijn, kan je de grafieken gaan maken. Het dropdown-menu wordt hier uitgelegd.
In dit dashboard heb ik gebruik gemaakt van een vlak grafiek, omdat het wat vulling gaf aan de bovenkant van het dashboard. Wanneer ik de gegevens van het land vergeleek met een wereldwijd gemiddelde, maakte ik gebruik van een lijngrafiek.
Voor de vrouwen in management waren geen historische data beschikbaar en koos ik voor een staafdiagram met de laatste beschikbare data van elk land. De gegevens over stemrecht zetten ik in losse cijfers weer.