---

---

In dit artikel wil ik (aan de hand van cijfers over gemiddelde geboortegewichten) met enkele voorbeelden laten zien hoe je een grafiek aan duidelijkheid kunt laten winnen.

Basis-gegevens

Op zoek naar gegevens over de spreiding van gewichten van baby’s bij hun geboorte kon ik bij het CBS niet veel vinden dat bruikbaar was voor dit artikel. Wel zag ik op de website van het Nederlands Tijdschrift Voor Geneeskunde een onderzoek, waarvan de resultaten wel geschikt waren:

LET OP dit onderzoek stamt uit 1990 (over geboortes in de jaren 70-80); we weten dat het gemiddeld geboortegewicht in de loop van de tijd is gestegen. Volgens het CBS was dit gemiddelde in 2005 ongeveer 60 gram hoger dan in 1990. Wanneer we dit doortrekken zullen de gemiddelde gewichten nu zeker 100 tot 150 gram hoger uitvallen dan de cijfers in bovenstaand onderzoek.

In het tabblad Data van het Voorbeeldbestand zijn deze cijfers overgenomen:

NB1 de cijfers zijn iets anders gerubriceerd, zodat we daar makkelijker een grafiek van kunnen maken.

NB2 Week geeft de zwangerschapsduur aan, n het aantal waarnemingen in de betreffende categorie en de percentielkolommen (P₅, P₁₀ etc) dat gewicht waarbij 5%, 10% etc van de geboortegewichten kleiner zijn. Dus in de eerste regel: van de 65 meisjes (het eerste kind, geboren in de 36e week) is het gemiddelde gewicht 2486 gram, is 5% lichter dan 1790 gram en 95% lichter dan 3250 gram.

De gegevens zijn opgenomen in de Excel-tabel met de naam tblData.

Grafiek 1

Per categorie kunnen we een grafiek maken, maar in het tabblad Selectie van het Voorbeeldbestand staat een interactieve variant: afhankelijk van de keuze voor Geslacht en Eerst/later wordt de grafiek automatisch aangepast.

NB de cellen C2 en C3 zijn met behulp van Gegevensvalidatie afgeschermd, zodat alleen zinvolle invoer mogelijk is.

Op basis van deze cellen (C2 en C3) wordt een hulptabel ingevuld. Hierbij wordt de functie SOMMEN.ALS gebruikt:

• in de eerste parameter wordt aangegeven waar de gegevens kunnen worden gevonden. In dit geval gebruiken we de functie INDIRECT om de juiste kolom in de tabel tblData te benaderen.
• de tweede en derde parameter bepalen het eerste criterium waaraan de gegevens, die we willen ophalen, moeten voldoen (alle regels in de tabel tblData waar in de kolom Geslacht de waarde van cel C2 staat)
• via het vierde en vijfde argument wordt de soort geboorte (al dan niet Eerstgeborene) geselecteerd
• en het zesde en zevende argument bepalen de juiste week

NB door de 3 criteria voldoet altijd maar 1 cel uit de basis-tabel. Het resultaat is dus geen echte som, maar slechts die ene waarde.

Op basis van deze hulptabel kunnen we snel een grafiek genereren:

Maar wat zien we hier nu eigenlijk?
Met enkele aanpassingen krijgen we een grafiek die beter te begrijpen is:

NB misschien moet de betekenis van P5 etc nog toegelicht worden; dit is afhankelijk van de doelgroep.

Wat is er toegevoegd:

1. uiteraard een legenda
2. de reeks Gem is naast de P50 gezet: klik rechts in de grafiek, kies de optie Gegevens selecteren en gebruik in het vervolgscherm de pijltjes:
   
   NB het gemiddelde en de 50-percentiel zijn in de meeste gevallen ongeveer gelijk aan elkaar
3. om duidelijker te maken welke lijn het gemiddelde is, heeft deze lijn markeringen gekregen
4. bij de horizontale as is aangegeven wat de getallen voorstellen
5. en misschien wel het belangrijkste: de grafiektitel, die aangeeft wat er in de grafiek staat en welke selectie daarbij is gemaakt.
   In cel L17 van het tabblad Selectie wordt de tekst voor de titel gemaakt:
   
   Tussen de &-tekens staan verwijzingen naar de cellen met de namen Geslacht (C2) en Eerst_later (C3).
   Achter het woord gram staat een ‘harde return’, druk bij het invoeren op Alt-Enter.
   Maak dan een willekeurige grafiektitel, klik daarin en zet in de formulebalk : =Selectie!L17
   LET OP vergeet niet ook de naam van het werkblad in te voeren; je kunt ook na het intikken van het =-teken met de muis op de cel met de tekst klikken.

Om de gegevens van een pasgeborene makkelijk te kunnen vergelijken is de invoer nog uitgebreid met een geboorteweek en in de hulptabel wordt dan met Voorwaardelijke opmaak de overeenkomende regel geaccentueerd:

Op het tabblad Selectie van het Voorbeeldbestand worden ook diverse gemiddeldes berekend:

Om deze ‘weging’ makkelijk te kunnen uitvoeren is aan de brongegevens een berekende kolom toegevoegd, n*Gem (zie het tabblad Data).

In cel M3 wordt op de juiste manier het gemiddelde bepaald:
=SOM(tblData[n*Gem])/SOM(tblData[n])
ofwel de som van alle waardes in de kolom n*Gem in de tabel tblData gedeeld door de som van alle waardes in de kolom n van die tabel.

NB op de CBS-site is te lezen: “In de periode 1989-1991 woog een baby gemiddeld 3 372 gram bij geboorte, in de periode 2004-2006 was dat 3 434 gram.“
Dus het gemiddelde gewicht van 1990 is ongeveer gelijk aan dat uit het onderzoek met geboortes uit 1970-1980.

Afhankelijk van de waardes in de cellen C2 (met de naam Geslacht) en C3 (met de naam Eerst_later) wordt het gemiddelde van die selectie bepaald:
=SOMMEN.ALS(tblData[n*Gem];tblData[Geslacht];Geslacht;tblData[Eerst/later];Eerst_later)/
SOMMEN.ALS(tblData[n];tblData[Geslacht];Geslacht;tblData[Eerst/later];Eerst_later)
Dus in plaats van een gewone SOM-formule gebruiken we nu SOMMEN.ALS, waarmee we criteria kunnen opgeven voor het optellen.

Op een vergelijkbare manier worden ook de gemiddeldes voor het geselecteerde geslacht en de geselecteerde soort geboorte berekend.

Wil je liever geen hulpkolom (n*Gem) gebruiken dan biedt de functie SOMPRODUCT uitkomst (zie cel N3 in het tabblad Selectie; deze functie vermenigvuldigt alle elementen uit een reeks met de overeenkomende elementen van de andere reeksen en sommeert de resultaten daarvan).
Ook de andere gemiddeldes kunnen op een vergelijkbare manier bepaald worden. In cel N4 staat bijvoorbeeld de formule:
=SOMPRODUCT(tblData[n];tblData[Gem];
1*(tblData[Geslacht]=Geslacht);
1*(tblData[Eerst/later]=Eerst_later))/
SOMPRODUCT(tblData[n];
1*(tblData[Geslacht]=Geslacht);
1*(tblData[Eerst/later]=Eerst_later))

De criteria die we willen meegeven, staan in aparte reeksen; bijvoorbeeld de kolom Geslacht wordt vergeleken met de waarde in de cel Geslacht (dit is cel C2). Dit levert een reeks op van diverse WAAR’s en ONWAAR‘s; door deze met 1 te vermenigvuldigen wordt dit een reeks 1’n en 0‘n.

Grafiek 2

Wanneer je alleen de grafiek toont (en dus niet de hulptabel) dan is het niet altijd even makkelijk om de exacte waardes af te leiden.
Daarom zijn in bovenstaande grafiek (zie het tabblad Selectie2 van het Voorbeeldbestand) ook de waardes van het gemiddelde en van de P25 en P75 opgenomen van de geselecteerde geboorte-week.
De hulptabel heeft daartoe een extra kolom (WkSel) gekregen:

NB de functie NB() levert als resultaat #N/B. Het voordeel hiervan is dat deze waarde in een grafiek niet wordt weergegeven (zie het artikel Grafiek zonder nullen).
In Excel wordt intern WAAR omgezet in de waarde 1. De reeks WkSel is aan de grafiek toegevoegd en aan de secundaire as gekoppeld. Nog een markering toevoegen aan deze ‘lijn’ en op de juiste plaats zien we een signalering.
Door ook een kolom Label toe te voegen aan de hulptabel kunnen we deze extra signalering van een Gegevenslabel voorzien. Voor de exacte implementatie, zie het tabblad Selectie2 van het Voorbeeldbestand.
Kies je nu een andere geboorteweek (cel C4) dan past de signalering zich automatisch aan:

Grafiek 3

Dit is eigenlijk geen andere grafiek, alleen de manier van selecteren is anders (makkelijker; zie het tabblad Selectie3 van het Voorbeeldbestand).

De hoofdselecties worden gemaakt met behulp van Keuzerondjes, de geboorteweek selecteer je met een Schuifbalk.

Deze voeg je toe via de menutab Ontwikkelaars. In het blok Besturingselementen kies je de optie Invoegen. Selecteer de gewenste optie binnen de Formulierbesturingselementen. Na de selectie kun je zo’n element op de gewenste plaats ‘met de muis tekenen’.

Bij Keuzerondjes is de volgende werkwijze het handigst:

1. teken één keuzerondje
2. klik rechts, verander de tekst in m, kies Besturingselement opmaken en maak een koppeling met een cel (in het voorbeeld op tabblad Selectie3 is dat N2)
3. tik Ctrl-C en ergens anders Ctrl-V, verander de tekst in v
4. voeg eventueel een groepsvak toe, die je om deze twee keuzerondjes tekent
5. doe hetzelfde voor het soort geborene; maak daar een koppeling met een andere cel (in het voorbeeld N3)
6. door een keuzerondje aan te klikken wordt er in cel N2, respectievelijk N3 de waarde 1 of 2 geplaatst; in de cellen daarnaast wordt een ‘vertaling’ gemaakt.

De Schuifbalk is eenvoudiger:

1. teken deze op de plaats waar je hem wilt hebben (kun je later natuurlijk nog aanpassen)
2. klik rechts, kies Besturingselement opmaken en maak een koppeling met een cel (in het voorbeeld op tabblad Selectie3 is dat N4).
   Stel ook de minimum- (in het voorbeeld 36) en de maximumwaarde (43) in.

Grafiek 4

Met behulp van het systeem in het tabblad Vergelijken van het Voorbeeldbestand kun je snel een eigen waarneming afzetten tegen de populatie uit het onderzoek.

Voer de naam, de zwangerschapsduur (in weken) en het geboortegewicht in. In de grafiek is direct de relatieve positie tussen de percentiellijnen te zien. Door wat extra berekeningen wordt via een label ook een schatting voor de absolute positie weergegeven.

2. met behulp van deze waarde en de functies VERGELIJKEN en VERSCHUIVING wordt in cel N11 de kolom opgezocht waarvan de score gelijk of kleiner is aan het geboortegewicht uit cel D4.
   LET OP1 de derde parameter in de VERGELIJKEN-functie is weggelaten; meestal is deze parameter gelijk aan 0 (nul; dan wordt een exacte match gezocht). Vaak zal het gezochte geboortegewicht namelijk niet in de tabel voorkomen.
   LET OP2 wanneer je de derde parameter weglaat (beter gezegd: als die niet gelijk is aan 0) dan moet de reeks waarin je zoekt, gesorteerd zijn.
3. in cel N12 bepalen we de percentiel-waarde die bij die kolom hoort
4. ook het bijbehorende gewicht wordt dan opgezocht in cel N13
5. in kolom O worden de vorige 2 stappen uitgevoerd voor een hogere percentielkolom
6. we interpoleren tussen de hiervoor bepaalde waardes om een schatting te krijgen voor het percentiel dat bij het opgegeven geboortegewicht hoort (zie cel O14)

Net als bij de vorige grafiek moet aan de hierbij behorende ‘lijn’ een markering meegegeven worden, voordat het markeringspunt zichtbaar zal zijn. Geef deze reeks ook een label mee (zie kolom VergLabel).

Als laatste moeten nog de verticale en horizontale lijnen ingevoegd worden:

1. de verticale kan het makkelijkst door een nieuwe reeks te maken net als in de vorige grafiek (zie kolom VergWk in de hulptabel van het tabblad Vergelijken). Kies als grafiektype een Kolomdiagram en koppel deze aan de secundaire as.
2. de horizontale lijn wordt geproduceerd door een reeks toe te voegen met voor iedere week dezelfde waarde, namelijk het geboortegewicht uit cel D4.

NB het ‘systeem’ is nog niet fool-proof: vul je een hoog geboortegewicht in, dan zal het zoeken naar het overeenkomende percentiel problemen opleveren.

---