In het vorige artikel op deze website (Frequentietabel en histogram) hebben we laten zien hoe je op verschillende manieren een frequentietabel kunt maken. Door daar dan een grafiek op te baseren krijg je een zogenaamd histogram.
Als je alleen in de grafische weergave van de frequentietabel bent geïnteresseerd dan zijn er enkele alternatieve mogelijkheden om een histogram te maken. Hieronder bespreken we er drie. Lees om de gebruikte terminologie te begrijpen eerst het vorige artikel.
Ingebouwde grafiek
Sinds versie 2016 heeft Excel een ingebouwd grafiek-type om een histogram te maken. Klik in de menutab Invoegen in het blok Grafieken op Statistische grafieken en kies daarna Histogram.
Om dit soort grafieken te kunnen maken moeten de te turven getallen allemaal onder elkaar (of naast elkaar) staan. De proefwerkcijfers uit het vorige artikel zijn dan ook omgezet naar een zogenaamde database-indeling (zie het tabblad Data van het Voorbeeldbestand).
Selecteer alle gegevens (inclusief de koppen) en kies de Histogram-grafiek zoals hiervoor aangegeven.
Excel bepaalt zelf de grenzen voor én aantal van de intervallen (zie het tabblad Hist van het Voorbeeldbestand). In dit geval krijgen we drie intervallen. Onder de horizontale as staan de grenzen van deze intervallen weergegeven.
Daarbij worden 2 symbolen gebruikt: rechte en ronde haken. Een rechte haak geeft aan dat de bijbehorende waarde bij het interval hoort, bij een ronde haak doet de waarde niet meer mee; het tweede interval loopt dus van 4,8 tot en met 7,6.
Meestal voldoen die grenzen niet aan onze wensen, maar daar is gelukkig nog wel wat aan te doen.
Klik met de muis rechts op een van de waarden van de horizontale as en kies As opmaken.
Binnen Excel worden de intervallen Bins genoemd. Als voorbeeld maken we de interval-breedte gelijk aan 1.
NB zie je het invulveld niet op het scherm, maak dan het vak met As opmaken breder door de linkerkant te verschuiven.
Het resultaat is bijna gelijk aan wat we in het vorige artikel zelf hebben gemaakt, behalve het eerste interval; we willen de 2 en 3 in een aparte kolom.
LET OP we hebben hier te maken met een onhebbelijkheid van de ingebouwde grafiek: de onder- en bovengrens kunnen niet buiten het bereik van de onderliggende getallen liggen! Het Aantal bins wijzigen helpt niet en ook niet het aanpassen van de boven- en/of ondergrens (in het eigenschappenscherm Overloop van bin en Negatieve overloop van bin genoemd)
Maar als we de ondergrens instellen op 2,1 dan begint het er op te lijken! Maar nu ziet de as er niet uit; helaas is daar verder niets aan te doen.
Uiteraard kan de lay-out van de grafiek nog aangepast worden door bijvoorbeeld een titel toe te voegen en de breedte van de kolommen aan te passen (klik rechts op één van de kolommen en kies Gegevensreeks opmaken)
NB vanwege de problemen met de indeling van de intervallen is de bruikbaarheid van dit grafiektype naar mijn mening beperkt.
Gegevensanalyse
Excel heeft een ingebouwde analyse-tool. Klik op de menutab Gegevens. In het blok Analyse zit de optie Gegevensanalyse.
Deze optie is standaard niet actief. Het activeren gaat als volgt:
klik op de menutab Bestand
kies Opties
kies dan Invoegtoepassingen
bij de inactieve toepassingen ziet u Analyses Toolpak
selecteer die en klik op Start
vink in het vervolgscherm de optie Analysis Toolpak aan
Hoe maak je hiermee een histogram?
plaats eerst ergens in de sheet de data voor de gewenste intervallen (beter gezegd de bovengrens van de intervallen). Op het tabblad VerzBereik van het Voorbeeldbestand staan die in de cellen D3:D12.
klik op de optie Gegevensanalyse in de menutab Gegevens
selecteer in het vervolgscherm Histogram
vul bij Invoerbereik de cellen in die de getallen bevatten waarvan een histogram gemaakt moet worden (dus zonder de teksten daarnaast en ook zonder de kopregel).
het Verzamelbereik is het gebied met de intervallen uit punt 1. NB als je Labels aanvinkt zorg er dan voor dat én het Invoerbereik een kopregel bevat én het Verzamelbereik.
geef dan bij het Uitvoerbereik de cel aan, waar de uitvoer zal komen. NB zorg voor voldoende lege ruimte rechts van deze cel en naar beneden. De uitvoer heeft minstens 2 kolommen en het aantal regels is gelijk aan het aantal intervallen + 2
wil je niet alleen de frequenties maar ook een cumulatief, vink dan de betreffende optie aan
we willen natuurlijk een grafiek, dus plaats een vinkje bij de laatste optie
klik dan op OK
Het resultaat van bovenstaande staat in de cellen D2:E13. Het Verzamelbereik is gelijk aan onze opgave bij de invoer, maar met één extra regel, aangeduid met Meer. In deze categorie komen alle getallen die groter zijn dan de laatst opgegeven bovengrens. Deze werking komt overeen met die van de functie INTERVAL (zie het artikel Frequentietabel en histogram); deze analyse-tool zal intern ongetwijfeld van deze functie gebruik maken.
NB deze methode om een histogram te maken is aanzienlijk flexibeler dan het ingebouwde grafiektype. Een groot nadeel is wel dat, wanneer er aanvullende gegevens zijn of als je andere intervallen wilt gebruiken, alle handelingen opnieuw moeten worden uitgevoerd.
Draaigrafiek
maak een draaitabel op basis van alle gegevens in de kolommen B:D van het tabblad Data van het Voorbeeldbestand.
vul het schema in zoals hiernaast (zie het tabblad Draai)
om de draaigrafiek te maken kies je in de menutab Analyseren van Hulpmiddelen voor draaitabellen in het blok Extra de optie Draaigrafiek
Dit levert een frequentietabel en histogram op voor alle proefwerken. Wil je de resultaten van één speciaal proefwerk zien, selecteer dit dan bij Proefwerk in de draaitabel of de draaigrafiek. Dit kun je bij geen enkele van de voorgaande methodes gemakkelijk doen.
NB1 wil je een andere indeling van de intervallen maak dan gebruik van de groepeer-mogelijkheden binnen een draaitabel (zie het artikel Groeperen in een draaitabel).
NB2 zijn de brongegevens aangevuld, dan volstaat het om de draaitabel of draaigrafiek te vernieuwen (via rechts klikken). Wel moeten deze gegevens dan in een Excel-tabel zijn opgenomen anders moet eerst de bron uitgebreid worden (via de optie Andere gegevensbron).
NB3 met deze derde methode maken we toch eerst een frequentie-tabel en het voorbeeld hoort dus eigenlijk in dit rijtje niet thuis. Maar het is de meest flexibele, handigste en betrouwbaarste methode!
Andere voorbeelden van histogrammen
Histogrammen gebruik je als je frequenties van bepaalde gebeurtenissen zichtbaar wilt maken.
We hebben hiervoor al gezien, dat het bijvoorbeeld een handige manier is om de verdeling van proefwerkresultaten van leerlingen weer te geven. Daarnaast is het een goed hulpmiddel om te zien hoe de gewichten en/of lengtes van een bepaalde groep mensen zijn verdeeld.
LET OP als je histogrammen gebruikt is het van het grootste belang dat de onderliggende populatie qua samenstelling ‘homogeen’ is. Toon je bijvoorbeeld de verdeling van de gewichten van diverse personen dan is het raadzaam om per geslacht een andere grafiek te maken. Maar als de leeftijden ver uit elkaar lopen, dan moet je ook daarvoor verschillende categorieën maken.
Een ander voorbeeld: als fietstraining heb ik geprobeerd 30 km op een constante, (voor mij) hoge trapfrequentie van 90 omwentelingen per minuut te rijden. Op een koude, regenachtige dag lekker binnen met behulp van Zwift. Hierboven zie je het eerste gedeelte van het resultaat (zie het tabblad Zwift van het Voorbeeldbestand). Iedere seconde legt dat programma vast op welke virtuele hoogte je fietst, welke afstand je in die seconde hebt afgelegd etc. Ook de trapfrequentie ofwel de cadans is geregistreerd. Deze output is niet direct bruikbaar, maar daar kunnen we met behulp van Power Query wel iets aan doen (zie het tabblad Zwift):
Maken we daar een frequentietabel en een histogram van (tabblad ZwiftOverz), dan blijkt het niet helemaal gelukt om in de buurt van die 90 te blijven. Was dat nou in het begin van de ’tocht’, op het einde of tijdens beklimmingen? Deze vragen kunnen niet via een histogram beantwoord worden; dat vergt een ander soort analyse.
De omvormer van mijn zonnepanelen levert een overzicht van de opbrengst op dagbasis. Ook die dagresultaten nodigen uit tot het maken van een histogram (zie het tabblad ZonPanelen van het Voorbeeldbestand).
Kort samengevat: van ieder overzicht waarin per persoon of per seconde/minuut/uur/dag of per 100m/km of per klas of per …. gegevens vastliggen kan makkelijk een histogram gemaakt worden. Wel zul je voor nadere analyse vaak nog dieper op de gegevens moeten inzoomen.
Overal kom je ze tegen, histogrammen: een grafische weergave van een frequentietabel.
Iedereen die met Excel werkt, heeft wel eens zoiets gemaakt.
In dit artikel laten we diverse mogelijkheden de revue passeren om zo’n overzicht te maken. We zoomen daarbij vooral in op verschillende methoden om een frequentietabel te maken. Volgende keer komen diverse alternatieven voor het histogram aan bod.
Voor het maken van een frequentietabel is de functie INTERVAL heel erg handig. Maar ook enkele ‘vreemde’ eigenschappen daarvan komen aan bod (zoals beloofd in Excel en kaarten 2).
Basis
Zoals gezegd: je komt ze bijna dagelijks tegen, histogrammen (en dus ook frequentietabellen). Ieder krantenartikel, waarin aantallen voorkomen, wordt wel verduidelijkt (?) met een grafiekje. Maar ook HR-medewerkers zijn er verzot op (leeftijds-, functie- en salarisopbouw kunnen daar goed mee worden geïllustreerd) en ook leerkrachten in het onderwijs gebruiken het veel (om de verdeling van proefwerkpunten te bepalen bijvoorbeeld).
In het Voorbeeldbestand op het tabblad Basis staat een fictief overzicht van de resultaten van 5 proefwerken van 10 leerlingen. Met behulp van de functie ASELECTTUSSEN worden (bij een wijziging in Excel of het drukken op F9) iedere keer nieuwe resultaten tussen 2 en 10 gegeneerd (inclusief die 2 grenzen).
Op hetzelfde tabblad staat een vergelijkbaar overzicht maar daar krijgen de proefwerkresultaten 1 decimaal. De gebuikte formule wordt dan: =ASELECTTUSSEN(20;100)/10
Deze overzichten gebruiken we in het vervolg van het artikel als voorbeeld-input.
Frequentietabel 1
We beginnen met een proefwerkoverzicht zonder decimalen. Daarvan gaan we een frequentietabel maken: hoe vaak komt ieder cijfer voor.
NB direct is te zien dat het voorbeeld niet echt reëel is: het cijfer 10 komt wel heel vaak voor! Maar hoe de verdeling van de overige cijfers is, is zelfs met dit kleine aantal moeilijker te beoordelen.
Op het tabblad FreqTabel1 van het Voorbeeldbestand staat ons eerste resultaat. In kolom J wordt de absolute frequentie van ieder mogelijk cijfer geturfd: =AANTAL.ALS($C$3:$G$12;I3)
In cel J3 tellen we het aantal keren, dat de waarde uit I3 voorkomt in het bereik C3:G12.
NB ter controle tellen we onderaan in kolom J de frequenties op, zodat we zeker weten dat alle cijfers zijn meegenomen. Selecteer de cel onder de frequenties en druk op Alt-= (dus de Alt-toets vasthouden en op = drukken). Deze sneltoets werkt ook als je getallen in een rij wilt optellen.
In kolom K bepalen we de cumulatieve absolute frequentie door bij het vorige resultaat in die kolom het resultaat uit kolom J op te tellen (in cel K4 staat de formule =K3+J4).
De formules in de kolommen L en M, waarin de relatieve en cumulatieve relatieve frequenties worden bepaald, spreken voor zich.
Absolute frequentie 2
Een alternatieve methode voor het bepalen van de absolute frequentie is het gebruik van de Excel-functie Interval (zie het tabblad FreqTabel1 van het Voorbeeldbestand).
Allereerst de volgende waarschuwing: Interval is een ‘vreemde’ functie. Niet alleen de input wordt gevormd door een gebied/bereik van cellen (net als bij Som bijvoorbeeld) ook het resultaat bestaat uit meer dan één getal. Het is een zogenaamde matrix-functie.
selecteer eerst de cellen waar het resultaat moet komen. Interval levert de frequenties op, horend bij een gewenst interval. In dit geval bij de cijfers 1 t/m 10. Dus het resultaat moet uit 10 cellen bestaan. LET OP het resultaat van de functie Interval moet altijd in een kolom komen, dus selecteer cellen ONDER ELKAAR. In het voorbeeld de cellen N3:N12.
tik in =interval(
voer dan de eerste parameter van de functie in, de Gegevensmatrix. Dit zijn de brongegevens, in dit geval het bereik C3:G12.
tik in ;
dan komt de tweede parameter, de Interval_verw. Dit moeten cellen zijn die de gewenste intervallen aangeven; in het voorbeeld de cellen I3:I12.
tik in )
sluit de functie NIET af door op Enter te drukken, maar tegelijkertijd op Ctrl-Shift-Enter. Op deze manier wordt een matrix-formule ingevoerd, ook wel een CSE-formule genoemd. In de 10 cellen staat nu overal exact dezelfde formule: {=INTERVAL(C3:G12;I3:I12)} LET OP de accolades hebben we niet zelf ingevoerd; dit is een indicatie dat hier een matrix-formule staat. Onderdelen van een matrix-formule kunnen niet worden gewijzigd, er kunnen geen regels tussengevoegd worden etc. Wil je een wijziging doorvoeren: selecteer alle bij elkaar horende cellen, klik in de Formulebalk en druk op Ctrl-Enter. Nu wordt in alle cellen dezelfde formule ingevoerd, maar niet als matrix-formule en dus horen ze niet meer bij elkaar. NB in de Engelstalige Excel-versies heet deze functie Frequency; dit geeft beter de bedoeling van de functie aan dan Interval.
NB1 het is in een matrix-formule niet nodig om de bereiken absoluut (met $-tekens) in te voeren.
NB2 hiervoor hebben we net gedaan of de Interval-functie de frequenties van de diverse cijfers heeft geturfd, maar eigenlijk zijn de waardes in het bereik Interval_verw de te hanteren bovengrenzen van de diverse intervallen. Maar aangezien de proefwerkpunten geen decimalen bevatten, zijn de bovengrenzen ook de enige cijfers die in dat interval voorkomen.
Cumulatieve absolute frequentie 2
In cel O3 van het tabblad FreqTabel1 van het Voorbeeldbestand hebben we de formule =INTERVAL($C$3:$G$12;I3) geplaatst. Er is maar 1 bovengrens van een interval, dus ook maar 1 resultaat. De formule hoeft dan ook niet met CSE afgesloten te worden, een Enter volstaat.
Deze formule turft het aantal keren, dat een getal uit het bereik van de eerste parameter kleiner of gelijk is aan de waarde in cel I3. In dit geval dus <=1; dat komt in het voorbeeld niet voor. Wanneer deze formule naar beneden gekopieerd wordt krijgen we de gewenste cumulatieve resultaten.
Histogram
Bij een frequentietabel hoort ook de grafische weergave, een histogram.
Maak een grafiek met daarin de absolute frequentie en de cumulatie daarvan, zorg dat op de x-as de intervalgrenzen komen (dus de cijfers 1 t/m 10) en zorg dat het een combinatiegrafiek wordt met de cumulatieve waarden uitgezet op de secundaire as.
LET OP kies bij het gebruik van een secundaire as de maximale waarden van de assen zodanig dat de horizontale rasterlijnen aan allebei de kanten bij weergegeven getallen uitkomen. In dit geval is het maximum rechts 4x groter dan links.
In een volgend artikel zullen we diverse andere methoden voor het maken van een histogram de revue laten passeren met daarbij de voor- en nadelen van de diverse alternatieven.
NB wanneer de brongegevens in de loop van de tijd nog uitgebreid worden, plaats deze dan in een Excel-tabel. Na uitbreiding hiervan wordt automatisch de frequentietabel geactualiseerd (zie het tweede blok op het tabblad FreqTabel1 van het Voorbeeldbestand). In het voorbeeld was het dan wel logischer geweest als ik de proefwerken in de rijen had geplaatst en de leerlingen in de kolommen! Maar in mijn-tijd-als-docent was dit wel de indeling in de lerarenagenda:
Frequentietabel 2
We gebruiken hetzelfde soort proefwerkoverzicht als in het vorige hoofdstuk, maar nu met 1 decimaal (zie het tabblad FreqTabel2 van het Voorbeeldbestand). Dankzij de voorwaardelijke opmaak van Excel zien we snel waar de hoge en lage punten zitten.
Hadden we dat vroeger in de lerarenagenda ook maar zo makkelijk gehad. Toen gebruikten we gekleurde pennen (exacter uitgedrukt: pennen met gekleurde inkt) met alle problemen van dien, als een cijfer achteraf nog aangepast moest worden.
Om de voorwaardelijke opmaak te kunnen toepassen gebruiken we een hulpkolom I; in de cellen I3:I6 staan de cijfers 4, 6, 8 en 10. De regels voor de voorwaardelijke opmaak zien er als volgt uit:
De vorige opmaak leverde wel een bonte kermis op. Het mag wel wat subtieler. Maar daardoor is het iets moelijker om te zien in welke categorie een cijfer valt.
De gebruikte regels:
Hé, wat vreemd. Alle cijfers krijgen de goede kleur maar zijn allemaal cursief en vet, terwijl dat in de opmaakregels alleen voor de laagste en hoogste punten is ingesteld. Als deskundige Excel-ler hebt u natuurlijk al gezien hoe dat komt. We nemen als voorbeeld het cijfer 5,6 : dit voldoet niet aan de eerste regel maar wel aan de tweede, dus krijgt het een oranje opmaak. Maar hij voldoet ook aan de derde regel! Dus zou het cijfer een licht-groene opmaak moeten krijgen, maar dat kan Excel niet: én oranje én licht-groen. Het cijfer krijgt de eerste kleur. 5,6 voldoet ook aan regel 4. Donker-groen maken kan Excel niet meer, maar wel cursief en vet.
We moeten ook de laatste kolom binnen de opmaak-regels gebruiken (Stoppen indien Waar). Als een cijfer aan een regel voldoet dan worden de volgende regels niet meer uitgevoerd.
NB1 Het laatste vinkje hoeft niet meer; de opmaak-routine stopt toch al
NB2 bij het opmaken van het ‘kermis-overzicht’ is de stop-optie niet nodig omdat ook hier geldt dat Excel maar één opvulkleur aan een cel kan toewijzen.
Even genoeg over de opmaak; dit artikel gaat over frequentietabellen. Om de proefwerkresultaten met decimalen in de juiste categorie onder te brengen gebruiken we weer de functie Aantal.Als.
Maar wel iets ingewikkelder dan hierboven: in cel K3 staat de formule =AANTAL.ALS($C$3:$G$12;”<=”&I3) Turf het aantal getallen in het bereik C3:G12, die voldoen aan de voorwaarde dat ze kleiner of gelijk zijn aan de waarde in cel I3.
LET OP de voorwaarde moet een tekst-vorm hebben: dus de tekst <= (zie de “-tekens) wordt met behulp van & samengevoegd met de inhoud van cel I3.
Deze is formule is naar beneden gekopieerd. Helaas, nu hebben we niet de frequenties maar de cumulatieven. Geen nood, in kolom L gaan we de frequenties bepalen. De eerste berekening is oké. Dus cel L3 is hetzelfde als K3. In cel L4 plaatsen we de formule =AANTAL.ALS($C$3:$G$12;”<=”&I4)-L3. Maar die voldoet niet als we die “naar beneden kopiëren”: we hadden =AANTAL.ALS($C$3:$G$12;”<=”&I5)-SOM($L$3:L3) moeten gebruiken.
In kolom M hebben we nog een alternatieve berekening voor de frequenties: M3 is weer gelijk aan K3, in M4 staat de formule =AANTALLEN.ALS($C$3:$G$12;”<=”&I4;$C$3:$G$12;”>”&I3) Dus een Als met 2 voorwaarden, dat kan alleen met de functie AantalLEN.als. Deze formule kan naar beneden gekopieerd worden.
NB wil je toch Aantal.Als gebruiken: in cel M4 had ook de formule =AANTAL.ALS($C$3:$G$12;EN(“<=”&I4;”>”&I3)) kunnen staan. Hierbij zijn met behulp van de functie EN 2 voorwaarden gecombineerd tot 1.
Zoals uit de vorige alinea’s mag blijken is het maken van een frequentietabel met behulp van Aantal.Als niet altijd even makkelijk. We mogen blij zijn dat Excel de functie Interval kent:
selecteer de cellen N3:N6
tik in de de formulebalk in: =INTERVAL(C3:G12;I3:I6)
druk op Ctrl-Shift-Enter
klaar!
NB1 niet tevreden met de grenzen van de intervallen? Is alles boven 5,5 een voldoende? Wijzig de inhoud van cel I4 in 5,5 en de hele sheet wordt automatisch aangepast. Niet alleen de frequentietabellen maar ook de voorwaardelijke opmaak.
Maar wat gebeurt er als je de 10 in cel I6 wijzigt in 9? Dan worden niet alle punten in de frequentietabel meegeteld; dat is niet de bedoeling. Gelukkig heeft Microsoft daar rekening mee gehouden.
Ter verduidelijking bevat het tabblad FreqTabel2 nog een ander overzicht:
selecteer de cellen J10:J22
tik in de de formulebalk in: =INTERVAL(C3:G12;I10:I19)
druk op Ctrl-Shift-Enter
Bewust is het resultaatbereik groter gekozen dan we gewend zijn; de eerste 10 regels zijn logisch: eerst wordt het aantal proefwerkresultaten kleiner of gelijk aan 1 geturfd (0 dus), dan hoeveel groter dan 1 en kleiner of gelijk aan 2 etc. Maar dan komt er nog een resultaat; in dit geval 0. Excel levert via de Interval-functie altijd één waarde meer dan het aantal gedefinieerde intervallen. Hier wordt geteld hoeveel brongegevens groter zijn dan de hoogst gedefnieerde intervalgrens. Wijzig de 10 in bijvoorbeeld 9,5 en je ziet het volgende resultaat:
De laatste 2 regels laten zien, dat de Interval-functie geen resultaat meer oplevert, dus we hadden hiervoor bij punt 1 beter de cellen J10:J20 kunnen kiezen.
Interval-functie
Laten we de Interval-functie nog eens even verder onder de loep nemen. In het tabblad Interval van het Voorbeeldbestand ziet u weer dezelfde brongegevens staan. Op basis van de 10 intervalgrenzen in kolom I zijn de bijbehorende frequenties in kolom J met behulp van de Interval-functie bepaald, inclusief 1 extra cel (en 2 niet-nuttige cellen).
De frequentietabel in de kolommen L en M laat zien dat de Interval-functie ook werkt wanneer de grenzen in de volgorde hoog-laag staan (in de kolom M staat de formule .
En nog mooier: de intervalgrenzen mogen ook willekeurig door elkaar staan (zie de kolommen O en P)! Maar wanneer zou je zoiets nu doen?
De kolommen R en S laten nog een andere eigenschap van de functie zien: wanneer een interval een tweede keer voorkomt dan wordt de bijbehorende frequentie 0, zodat we geen dubbeltelling krijgen (dit geldt ook als zo’n grens nog vaker voorkomt). Consequentie is wel dat de frequenties van andere intervallen veranderen. En dat is natuurlijk terecht omdat de getallen in kolom R de bovengrens van een interval aanduiden.
Interval-functie 2
Hiervoor hebben we al regelmatig de functie Interval gebruikt, waarbij de werking (na wat oefening) ‘normaal’ begint aan te voelen.
We oefenen nog even met een nieuw voorbeeld (zie het tabblad Interval2 van het Voorbeeldbestand): van enkele leerlingen hebben we scores verzameld. De data zijn vastgelegd in een Excel-tabel met de naam tblLLscore. Het aantal records staat onder de kolom LLnr, daarnaast is het totaal van de scores bepaald met de formule . De twee totalen zijn (bijna) automatisch gegenereerd door in de menutab Hulpmiddelen voor tabellen de Totaalrij te activeren:
Een overzicht van de verdeling van de scores is nu snel gemaakt:
plaats de scores 0 tot en met 10 in een kolom (hier kolom E)
selecteer daarnaast de cellen in kolom F, tik in de formule =INTERVAL(tblLLscore[Score];E3:E13) en druk op Ctrl-Shift-Enter
in kolom G sommeren we de scores als de score overeenkomt met cel E3, E4 etc.
onderaan sommeren we de resultaten van de kolommen F en G
LET OP de resultaat-tabel in de kolommen E:G kan niet de vorm van een Excel-tabel krijgen; Excel staat een combinatie van zo’n tabel en een matrix-formule niet toe!
We weten nu hoe de verdeling over de verschillende scores is, maar hoe is de verdeling over de leerlingen? In het voorbeeld is het aantal leerlingen beperkt; we zien in één oogopslag dat alleen de nummers 1 tot en met 5 voorkomen. Maar hoe weet je dat wanneer de bron veel uitgebreider is? Een oplossing is het gebruik van Filter:
kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
vul het scherm van het Uitgebreid filter in zoals hiernaast
klik op OK
Het klopt, er zijn 5 verschillende leerlingennummers. We hebben de kolom I aangevuld met J en K en daar de Excel-tabel tblLLresult van gemaakt. In kolom J staat de formule =AANTAL.ALS(tblLLscore[LLnr];[@LLnr]) en in K: =SOM.ALS(tblLLscore[LLnr];[@LLnr];tblLLscore[Score])
En de tabel heeft ook een Totaalrij gekregen.
Een andere methode om vanuit de basis tot een leerlingen-overzicht te komen is door het gebruik van Power Query. In de kolommen M:O staat het resultaat daarvan.
Wat gebeurt er als er nieuwe scores bij komen?
selecteer met de cursor cel C16 (de laatste score)
druk op de Tab-toets
de Totaalrij van de tabel verschuift automatisch naar beneden
voeg nu nieuwe gegevens toe: leerling 3 heeft een 7 gescoord.
Alle overzichten zijn overeenkomstig aangepast. Het resultaat van Power Query hebben we wel moeten Vernieuwen (rechts klikken op één van de cellen in M:O).
Voeg op dezelfde manier voor leerlingnummer 6 de score 8 toe:
Helaas: ons leerlingenoverzicht is niet meer compleet. We zullen handmatig het leerlingnummer 6 moeten toevoegen om alles weer kloppend te maken. Fijn dat we controle-totalen hebben toegevoegd!
NB misschien toch handiger om voor dit soort overzichten draaitabellen te gebruiken?
Interval-functie-3a
Maar de functie heeft een speciale eigenschap, die goed gebruikt kan worden bij het turven van het aantal unieke gegevens (en dat was uiteindelijk de aanleiding voor dit artikel).
Op het tabblad Uniek van het Voorbeeldbestand hebben we de kolom met leerlingnummers gekopieerd naar kolom B.
Daarnaast hebben we met behulp van de Interval-functie een frequentieoverzicht voor deze leerlingen gemaakt door de Gegevensmatrix en Interval_verw gelijk te maken.
Leerling 1 komt 4 keer voor, de volgende 3 keer etc. Komt het leerlingnummer nogmaals voor dan is de bijbehorende frequentie 0; we krijgen dus geen dubbeltellingen!
Door alle frequenties in kolom C op te tellen weten we dus ook het totaal aantal vastgelegde scores.
Maar hoe bepalen we nu het aantal unieke leerlingnummers? Met de volgende formule is dit eenvoudig: LET OP om Excel te ‘dwingen’ om de ALS uit te voeren voor alle cellen in C3:C16 moet de formule afgesloten worden met Ctrl-Shift-Enter.
We hebben de frequenties van kolom C niet per se nodig. In cel G3 staat de formule =SOM(ALS(INTERVAL(B3:B16;B3:B16)>0;1;0)) die ook het juiste aantal unieke leerlingen oplevert. NB deze formule kan met Enter afgesloten worden; blijkbaar snapt Excel door het gebruik van Interval dat hij/zij de ALS vaker moet uitvoeren.
Nog korter (in cel H3): =SOM(1*(INTERVAL(B3:B16;B3:B16)>0))
NB1 denk aan de extra haakjes na 1*.
NB2 wil je kijken hoe deze formules werken? Gebruik de optie Formules/Formules evalueren.
Interval-functie-3b
Maar de weg van een Excel-ler is niet altijd even geplaveid, dat weet u ongetwijfeld. Wanneer we niet te maken hebben met leerlingnummers maar met namen (zoals in het tabblad Uniek2 van het Voorbeeldbestand) dan werkt de Interval-functie niet meer!
Gelukkig is daar wat op te vinden. Ter verduidelijking hebben we in kolom E een formule staan met de functie Vergelijken: vergelijk de naam van de leerling in dezelfde regel (zie de @) met ALLE namen in de Leerling-kolom. Als die te vinden is (en dat is hier natuurlijk altijd zo!) dan levert de functie de positie op van deze naam; komt de naam vaker voor dan wordt telkens dezelfde positie opgeleverd. Op deze manier vertalen we de namen dus naar getallen, waarbij dezelfde namen dezelfde getallen opleveren. U voelt het al aankomen: op deze hulpkolom kunnen we wel de Interval-functie loslaten!
We hebben de hulpkolom E niet nodig: zoals te zien is levert de formule in cel J3 direct de juiste informatie.
In een vorig artikel (Excel en kaarten) hebben we laten zien hoe je gegevens op een landkaart kunt plotten; beter gezegd: op ieder willekeurig plaatje.
De daarbij gehanteerde methode maakt het mogelijk om op detailniveau gegevens op een kaart te plaatsen. Microsoft heeft vanaf versie 2016 een ingebouwde grafiek-optie waarmee gegevens op land, provincie en gemeente-niveau als een landkaart kunnen worden weergegeven. Hoe dat in zijn werk gaat (en welke problemen je daarbij moet zien te tackelen) komt in dit artikel aan bod.
Basisgegevens
Bij het CBS zijn heel veel gegevens te vinden, zoals bijvoorbeeld een overzicht van alle gemeentes per provincie (zie het tabblad Gemeenten_alfabetisch_2020 van het Voorbeeldbestand waar de stand van 1 jan 2020 is opgenomen).
We zijn eigenlijk alleen maar geïnteresseerd in de provincie- en gemeentenamen. Met Power Query is dat zo omgezet. Het resultaat staat in de tabel tblGem op het tabblad Data van het Voorbeeldbestand.
Willen we weten hoeveel gemeentes Nederland telde op 1 jan 2020 dan tellen we het aantal niet-lege cellen in de kolom Gemeentenaam met behulp van de functie AANTALARG.
LET OP de functie AANTAL telt alleen gevulde cellen mee, als daar getallen in staan.
In cel C3 wordt op een zelfde manier het aantal cellen in de kolom Provincienaam geteld. Dat levert hetzelfde resultaat op! Hoe bepalen we nu hoeveel provincies er in Nederland zijn (we weten natuurlijk allemaal wel dat het antwoord 12 moet zijn)? De tabel tblGem heeft daartoe een hulpkolom gekregen waarin het volgnummer van de gemeente binnen de provincie wordt geturfd. In cel D8 is daartoe de formule =AANTAL.ALS($B$8:B8;[@Provincienaam]) ingevoerd.
NB de formule wordt in de Excel-tabel automatisch in de hele kolom doorgevoerd. In D9 staat dezelfde formule, maar de laatste B8 is dan B9.
In cel C4 bepalen we het aantal provincies door te tellen hoe vaak het eerste volgnummer voorkomt: =AANTAL.ALS(tblGem[GemPerProv];1)
Voor de liefhebbers staat in cel C5 een formule die het aantal provincies bepaalt zonder gebruik te maken van een hulpkolom: =SOM(ALS( INTERVAL( VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0); VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0) )>0;1))
In een van de volgende artikelen zal ik de functie INTERVAL eens onder de loep nemen.
Kaartgrafiek 1
Als eerste gaan we een kaartgrafiek maken, waarin per provincie is aangegeven hoeveel gemeentes het bevat. Daartoe maken we op basis van de gegevens uit de Excel-tabel tblGem een draaitabel (zie het tabblad ProvGem van het Voorbeeldbestand).
We maken nu de grafiek als volgt:
plaats de cursor op een van de provincienamen
kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
op dit moment kent Excel daarbinnen maar één mogelijkheid, de zogenaamde Choropletenkaart
(“Thematische kaart waarop gemiddelde intensiteiten, dichtheden of relatieve waarden van verschijnselen binnen van te voren begrensde gebieden (meestal administratieve eenheden) zijn weergegeven door middel van vlaksymbolen die verschillen in grijswaarde.“)
En dan het resultaat:
Helaas dus, we zullen de gegevens van de draaitabel eerst moeten kopiëren. Dat hebben we in het tabblad ProvGem gedaan naar de kolommen F en G. De werkwijze wordt dan:
selecteer alle cellen met gegevens, inclusief een kopregel. In het voorbeeld is dat het bereik F3:G15
kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
klik op Choropletenkaart
Na de eerste euforie zien we ineens een ‘grijze’ vlek rechtsboven. Wanneer je met de muis over de kaart beweegt zie je per provincie het bijbehorende aantal, maar bij Groningen krijgen we dit:
Excel heeft zelf geen info over landen, gemeentes en dergelijke vastliggen. Om de kaart te kunnen maken worden gegevens van Bing en TomTom opgehaald wanneer je een grafiek probeert te maken. In de praktijk blijkt dit ophalen lang niet altijd vlekkeloos te gaan. In dit geval weet Excel (of Bing?) niet of we de gemeente of de stad Groningen bedoelen en laat deze gegevens dan weg. Maar waarom gaat het bij Utrecht dan wel goed??? Wijzig je cel F8 in: Provincie Groningen dan worden alle provincies ingetekend.
NB bij het experimenteren met de Kaartgrafiek blijkt dat deze ingebouwde optie nog niet geheel stabiel en betrouwbaar is. In het begin worden plaatsnamen of provincies of andere geografische indelingen soms niet direct herkend. Het blijkt te helpen om te blijven proberen met andere en meer namen tot het systeem deze herkend. Daarna wil het vaak ook met namen lukken, die eerst niet geplot werden.
Welke gegevens er gebruikt kunnen worden is ook niet helemaal duidelijk; het is mij gelukt om de regio’s van Frankrijk ‘in te kleuren’ maar niet de departementen (zie het tabblad Frankrijk van het Voorbeeldbestand). En er gebeuren nog meer ‘vreemde’ dingen: bijvoorbeeld de regio in het noord-oosten heb ik Alsace genoemd, maar Excel/Bing vertaalt dit naar Grand Est.
Een goede methode om Excel (of Bing) te helpen bij het analyseren van de gegevens is om bij de categorie ook het hogere niveau op te nemen. In dit voorbeeld is het hogere niveau van de provincies het land. Klik rechts op een van de provincies in de kaartgrafiek en kies Gegevens selecteren:
Kies bij de aslabels Bewerken en zorg dat ook de kolom met landnamen wordt meegenomen. Ook op deze manier wordt de provincie Groningen herkend:
De standaard-grafiek is op de volgende manier aangepast:
de grafiektitel is aangepast door er in te klikken en wijzigingen aan te brengen. LET OP een dynamische grafiektitel (een verwijzing naar een cel in de Excel-sheet) is hierbij niet mogelijk. Uiteraard kun je nog wel een Tekstvak toevoegen, waarin je een dynamische verwijzing maakt (zie het tabblad GemPerProv van het Voorbeeldbestand)
door rechts te klikken op één van de provincies kun je de Gegevensreeks opmaken
de kaartprojectie laten we voor wat het is; voor echte geografen is dit waarschijnlijk wel interessant
normaal blijft het Kaartgebied ook op Automatisch staan. Soms is één van de overige instellingen wel handig: Experimenteren dus.
bij Kaartlabels heb je 3 mogelijkheden: Geen (dit is de standaardwaarde), Alleen best passend en Alles weergeven. Bij de 2e optie wordt er alleen een label geplaatst wanneer er voldoende ruimte is; bij de 3e manier zul je af en toe een label afgekapt zien (dmv …..)
bij Reekskleur heb ik gekozen voor 3 kleuren; de precieze kleuren en de grenswaarden kunnen daaronder gewijzigd worden
als laatste: klik rechts op één van de provincies en kies Gegevenslabels toevoegen. Door Waarden aan te vinken wordt de Kaartgrafiek aangevuld met de exacte waardes.
Kaartgrafiek 2
Een overzicht van de provincies van Nederland is leuk, maar we willen ook graag per provincie inzoomen.
Het eerste wat we doen is (natuurlijk) een draaitabel maken waarmee we per provincie een overzicht van de gemeentes krijgen (zie het tabblad GemPerProv van het Voorbeeldbestand).
Zoals hiervoor al aangegeven moeten we nog een tussenstap maken voordat we naar de Kaartgrafiek kunnen: daarom hebben we een paar hulpkolommen (F en G) ingevoegd met daarin een formule =ALS(OF(B5=””;B5=”Eindtotaal”);””;B5)
NB omdat een Kaartgrafiek ook niet kan omgaan met dynamische bereiken (waarbij we gebruik maken van de functie Verschuiving) is er in de hulpkolommen ruimte gemaakt voor 100 gemeentes.
In de hulpkolom E is een verwijzing gemaakt naar de gekozen provincie.
Op basis van deze hulpkolommen (met 100 regels) is de kaartgrafiek gemaakt. Kies je in cel C2 een andere provincie dan zal de kaart automatisch aangepast worden.
NB1 wel zal rechtsboven in de grafiek altijd een waarschuwingsteken staan Klik je daar op, dan zul je zien dat maar een (klein) gedeelte van de 100 regels in de grafiek kunnen worden weergegevens; maar dat is ook logisch.
NB2 helaas, niet voor alle provincies gaat het (direct) goed. Kies je bijvoorbeeld Drenthe dan zie je alleen de provincie en niet de gemeentes. Dit is weer op te lossen door bij de aslabels de kolom met provincie NIET mee te nemen. Bij Groningen wordt er helemaal niets getoond! In Noord-Brabant wordt de gemeente Best niet ingevuld, in Limburg hebben we een probleem met de naam Bergen (L).
In de draaitabel wordt in de 2e kolom het aantal gemeentes per gemeente geteld; dat levert natuurlijk altijd als resultaat een 1 op. Ook in de hulpkolom G komt dus een 1. Maar de gebruikte formule daar is iets aangepast: =ALS(OF(B5=””;B5=”Eindtotaal”);””;C5+ALS(RijCur=RIJ();10;0)) Bij de waarde uit kolom C wordt 10 opgeteld als de waarde in de cel met de naam RijCur (dat is hier cel F2) gelijk is aan de Rij waarin de formule staat. De waarde in cel RijCur wordt aangepast door een kleine VBA-routine die aan dit werkblad is gekoppeld: Zo gauw de selectie in dit tabblad wordt gewijzigd (door het verplaatsen van de cursor of het klikken met de muis) wordt de waarde van de geselecteerde rij in de cel RijCur geplaatst. Op dat moment komt in kolom G in die rij een hogere waarde te staan, die er automatisch voor zorgt dat deze gemeente in de grafiek een andere kleur krijgt. Door met de muis op één van de gemeentenamen te klikken kunnen we snel onze topografische kennis opvijzelen!
Kaartgrafiek 3
Een kaartgrafiek kan ook gebruikt worden om een indeling te laten zien. Als de waardes voor de ‘y-as’ geen getallen zijn maar teksten kiest Excel automatisch dit type grafiek (zie het tabblad Utrecht van het Voorbeeldbestand).
De keuze welk type moet worden gegenereerd zit wat verstopt:
klik rechts op een van de gekleurde vlakken en kies Gegevens selecteren
kies bij de Legendagegevens de optie Bewerken
via de radio-buttons kun je dan een van de twee grafiektypen kiezen
Kaartgrafiek 4
Als voorbeeld staan in het tabblad Corona van het Voorbeeldbestand de aantallen Corona-besmettingen van 17 nov voor de verschillende gemeentes in Zuid-Limburg. Niet alleen de aantallen sec, maar ook het aantal besmettingen per 100.000 inwoners. Dit om de gemeentes met elkaar te kunnen vergelijken.
NB de brongegevens van de eerste grafiek worden gevormd door alle gegevens van de kolommen B, C en D. Een kaartgrafiek kan maar één legendawaarde weergeven, dat zal hier dus kolom C zijn. Wanneer je nu deze kolom verbergt (klik op het -teken boven de D) worden de gegevens van de volgende kolom gebruikt. Kolom C is in een groep geplaatst: selecteer de hele kolom door op de letter C te klikken en kies dan Groeperen in de menutab Invoegen.
3D-kaartgrafiek
Dit is een combinatie van een 3D– en een Kaartgrafiek. Microsoft heeft dit niet onder de kaartgrafieken gerubriceerd, althans het zit helemaal ergens anders in de menu-structuur.
plaats de cursor ergens in het bereik met gegevens, die geplot moeten worden. In het voorbeeld is dat in de Excel-tabel tblCorona op het tabblad Corona van het Voorbeeldbestand.
kies dan in de menutab Invoegen de optie 3D–kaart
vul de diverse items als volgt in:
‘speel’ wat met de zoom- en navigeer-knoppen:
Het onderdeel Rondleidingen kent nog veel meer mogelijkheden, maar het voert voor dit artikel te ver om daar op in te gaan.
Op de site van G-Info hebben we het al heel vaak gehad over allerlei methoden om met Excel gegevens te analyseren.
Uit alle vragen die we krijgen blijkt dat de meeste mensen daarbij worstelen met Voorwaardelijke opmaak, grafieken en draaitabellen. Daar hebben we dan ook al diverse keren aandacht aan besteed.
Waar we het nog niet over hebben gehad, is dat je Excel vaak automatisch het (voor)werk kunt laten doen! Microsoft heeft (vanaf versie 2013) een optie ingebouwd, die ze Snelle Analyse genoemd hebben. Naar mijn idee zijn ze daarbij eigenlijk te bescheiden: ik zou dat eerder Zeer snelle analyse met heel erg veel mogelijkheden hebben genoemd, maar dat is natuurlijk wat lang in een menu 😉
De diverse resultaten van Snelle analyse kunnen nog handmatig naar wens worden aangepast; ook kan het een goede start zijn om de diverse onderdelen van Excel die daarbij gebruikt worden, beter te begrijpen.
Basis-gegevens
In het Voorbeeldbestand staat op het tabblad Data een blokje gegevens dat we in dit artikel zullen gebruiken om te laten zien hoe snel (en goed!) Snelle Analyse werkt.
De getallen worden door de functie ASELECTTUSSEN bij iedere wijziging in de werkmap (of na het drukken op F9) opnieuw gegenereerd. Het effect daarvan zie je dan direct in de verschillende analyses terug.
Snelle analyse starten
Selecteer je in Excel meer dan één cel tegelijk dan zie je rechtsonder bij die selectie de button Snelle analyse tevoorschijn komen. Zoals je kunt zien kun je ook de toetscombinatie Ctrl-Q gebruiken.
NB de button komt alleen tevoorschijn als je aaneengesloten cellen selecteert.
Klik je op de button dan verschijnt het Snelle analyse-menu:
Je kunt dus op 5 manieren je gegevens analyseren: met Opmaak (beter gezegd Voorwaardelijke opmaak), door middel van Grafieken, door automatisch Totalen (en andere statistieken) toe te laten voegen, door het gebruik van Tabellen (inclusief draaitabellen) of met Sparklines.
De mogelijkheden binnen deze opties kunnen afhankelijk zijn van de geselecteerde cellen.
Opmaak
De (voorwaardelijke) opmaak willen we alleen toepassen op de getallen in het overzicht. Dus selecteer eerst de cellen met die getallen en klik dan op de Analyse-button (of druk op Ctrl-Q). De Opmaak-optie is al geselecteerd. Wanneer je nu met de muis over de 6 verschillende menu-keuzes gaat zie je direct het resultaat daarvan in de brongegevens.
Wanneer je de opmaak daadwerkelijk aan je gegevens wilt toevoegen dan moet je op de betreffende keuze klikken (zie het tabblad Opmaak van het Voorbeeldbestand). Ook kun je een combinatie van voorwaardelijke opmaak toevoegen door meerdere keuzes achter elkaar te maken. Wil je de opmaak verwijderen kies dan de laatste optie in het Opmaak-menu.
Gegevensbalk
Via de eerste keuzemogelijkheid worden Gegevensbalken aan de geselecteerde cellen toegevoegd. Hierdoor krijg je snel inzicht in hoe de getallen zich ten opzichte van elkaar verhouden.
NB Excel zal om de lengte van de gegevensbalken te maken alle getallen uit de geselecteerde cellen vergelijken. Wil je dat Excel per kolom (of rij) de waardes vergelijkt dan moet je de opmaak voor iedere kolom (of rij) apart instellen.
Kleurschalen en Pictogrammen
Ook via de opties Kleurschalen en Pictogrammen kun je de onderlinge verhoudingen van de geselecteerde getallen zichtbaar maken.
NB de voorwaardelijke opmaak kan nog naar wens worden aangepast:
selecteer één van de cellen met opmaak
kies in de menutab Start in het blok Stijlen de optie Voorwaardelijkeopmaak
kies Regels beheren….
kies Regel bewerken
pas de diverse opties naar wens aan
Groter dan
Door middel van de vierde opmaak-optie kun je getallen die groter zijn dan een bepaalde waarde een opmaak meegeven.
Kies je deze mogelijkheid dan moet je een grenswaarde opgeven; in het voorbeeld verwijzen we daarbij naar cel D32. Door deze cel te wijzigen zal de opmaak zich automatisch aanpassen.
Bovenste 10%
Deze optie spreekt voor zich. De cellen die waarden bevatten die bij de hoogste 10% horen worden gemarkeerd.
Door de opmaak-regel aan te passen kun je ook een heel ander gedeelte van de getallen een opmaak geven:
Opmaak wissen
Met de 6e keuzemogelijkheid kun je bestaande opmaak wissen.
NB op het tabblad Opmaak van het Voorbeeldbestand staan ‘gewone’ Excel-overzichten. Wanneer je deze uitbreidt met nieuwe gegevens moet je de opmaak zelf nog aan die nieuwe gegevens toevoegen. In het tabblad Opmaak2 staan dezelfde overzichten, maar dan in de vorm van een Excel-tabel. Als je daar gegevens toevoegt, krijgen de nieuwe gegevens automatisch de bijbehorende opmaak.
Grafieken
Grafieken worden veel gebruikt als rapportagetool, maar kunnen ook een goed analyse-hulpmiddel zijn.
Met Snelle analyse is een grafiek maken een fluitje van een cent:
voor een grafiek hebben we niet alleen de getallen nodig maar ook de omschrijvingen daar omheen.
selecteer alle benodigde cellen en klik op de button Snelle analyse of selecteer één van de cellen met getallen en druk op Ctrl-Q
klik in het submenu op de optie Grafieken en ga met de muis over de voorgestelde grafieken. Welke dit zijn is afhankelijk van de brongegevens.
Klik op een van de grafieken, et voilà! Zie het tabblad Grafieken van het Voorbeeldbestand.
Komen er nieuwe gegevens bij, dan zul je óf de de brongegevens van de grafiek moeten aanpassen (rechtsklikken op de grafiek en Gegevens selecteren kiezen) óf de grafiek op bovenstaande manier opnieuw moeten maken.
Dat kun je ondervangen door de brongegevens als Excel-tabel vast te leggen (zie het tabblad Grafieken2 van het Voorbeeldbestand). Helaas, dan werkt het selecteren van de gegevens met Ctrl-Q niet goed. Doe dan het volgende:
selecteer één van de cellen met getallen en druk op Ctrl-A
maar we hebben ook de kopregel nodig; druk nogmaals op Ctrl-A
druk dan Ctrl-Q of kies de Snelle analyse-button.
Totalen
we willen natuurlijk alleen van de getallen in het binnenblok de totalen berekenen (en bijvoorbeeld niet van de datums ook al zijn dat voor Excel ook getallen); dus selecteer de cellen C4:F15 (zie het tabblad Totalen van het Voorbeeldbestand)
druk op Ctrl-Q of klik op de analyse-button
kies de optie Totalen en klik op de gewenste functie (Som, Gemiddelde, Aantal, Totaal % of Voorlopig …)
Excel plaatst formules onder het geselecteerde blok getallen en voert daarmee de gewenste berekening uit.
NB1 zijn de cellen onder de selectie niet leeg, dan krijg je een waarschuwing of je deze wilt overschrijven. Wil je deze gegevens bewaren maak dan eerst ruimte door een lege regel in te voegen voordat je bovenstaande handelingen uitvoert.
NB2 bij de eerste 5 opties is een regel blauw gekleurd; dit betekent dat Excel de formules onderaan in een regel plaatst (en totalen per kolom bepaalt). De 6e optie (en verder, klik op het pijltje aan de rechterkant) laten een gekleurde kolom zien. Als je die optie gebruikt zal Excel formules aan de rechterkant plaatsen en dus totaalberekeningen over de rij uitvoeren.
NB3 Excel plaatst formules in het tabblad. Deze zijn zodanig opgezet dat bij het toevoegen van nieuwe gegevens de berekeningen met een paar kleine aanpassingen weer kloppen.
Lopend totaal
In rij 19 staat een formule die het lopend totaal bepaalt (in Excel wordt deze analyse-optie met Voorlopig … aangeduid). In cel C19 staat het totaal van de regio Noord, in D19 het totaal van Noord én Oost etc.
In kolom J staat een lopend totaal over alle regio’s. Wil je per regio een lopend totaal dan moet je eerst tussen C en D een nieuwe kolom invoegen, de gegevens van kolom C selecteren en dan het lopend totaal invoegen. Doe dat ook voor de andere kolommen; zie het tabblad Totalen:
NB plaats je de gegevens in een Excel-tabel dan zien de ingevoegde formules er heel anders uit (zie het tabblad Totalen2 van het Voorbeeldbestand). Bij het toevoegen van nieuwe gegevens hoeft dan niets (of veel minder) aan de formules gewijzigd te worden.
Tabellen
Met de eerste keuze binnen de analyse-optie Tabellen wordt een gewoon bereik van cellen omgezet naar een Excel-tabel. Maar ik gebruik die (bijna) nooit; die optie kennen we al via Invoegen of door Ctrl-L te tikken.
In het tabblad Tabellen van het Voorbeeldbestand is het gebruikte bronbestand omgezet naar een database-vorm; dit om de mogelijkheden van een draaitabel makkelijker te benutten.
Per combinatie van kenmerken (Regio, Maand en Product) wordt het Aantal vastgelegd.
selecteer één van de cellen in de Excel-tabel en druk op Ctrl-Q
kies de analyse-optie Tabellen
op basis van deze brongegevens stelt Excel 2 draaitabellen voor: Som van Aantal per regio en per product
klik op één van de 2 mogelijkheden (of de 3e om zelf een draaitabel te maken)
NB de 2 draaitabellen zijn gebaseerd op dezelfde brongegevens maar laten ieder een ander totaal zien! Dit is een inconsistentie binnen Excel. Maak je zelf verschillende draaitabellen op basis van dezelfde brongegevens dan worden alle draaitabellen tegelijkertijd vernieuwd. Zijn de draaitabellen via Snelle analyse aangemaakt dan moet iedere draaitabel afzonderlijk vernieuwd worden. Maar dan worden de brongegevens tussentijds aangepast door de Aselect-formule.
Op het tabblad Tabellen staat ook een draai-grafiek; deze is gemaakt op basis van de gegevens in de eerste kolommen door in de analyse-optie een grafiek te kiezen met het -teken.
Sparklines
Niets nieuws meer onder zon: selecteer alle cellen met getallen (dus niet de maanden en de kopregels), Ctrl-Q, kies de optie Sparklines en klik op één van de drie mogelijkheden (zie het tabblad Sparklines in het Voorbeeldbestand).
Het eerste type sparkline laat het globale verloop als lijngrafiek zien, de tweede globaal het verloop als kolomgrafiek en de derde (Winst/verlies) laat alleen maar zien of het resultaat positief of negatief is.
NB1 het uiterlijk van de sparklines kan makkelijk aangepast worden: klik op een cel die een sparkline bevat en kies bij Hulpmiddelen voor sparklines de optie Ontwerpen.
NB2 of je nu met een bereik van cellen werkt of met een Excel-tabel (zie het tabblad Sparklines2 van het Voorbeeldbestand), bij het uitbreiden van gegevens zul je de sparklines ook moeten aanpassen (of opnieuw maken). Het aanpassen gaat simpel door de vulgreep rechtsonder in de onderste cel met een sparkline naar beneden te trekken.
“Een grafiek zegt meer dan 1000 getallen“. Met deze parafrasering van een bekende zegswijze ben ik het meestal eens, maar soms valt de uitwerking tegen.
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.
NB2Week geeft de zwangerschapsduur aan, n het aantal waarnemingen in de betreffende categorie en de percentielkolommen (P5, P10 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
Door de combinatie van 2 soorten geslacht en 2 soorten geborenen kent het onderzoek dus 4 verschillende categorieën.
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:
uiteraard een legenda
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
om duidelijker te maken welke lijn het gemiddelde is, heeft deze lijn markeringen gekregen
bij de horizontale as is aangegeven wat de getallen voorstellen
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:
In M2 wordt het gemiddelde bepaald van alle gemiddelde gewichten uit het onderzoek. Maar …. dit is niet juist! Per geboorteweek is het aantal baby’s niet gelijk. De betreffende gemiddelde gewichten moeten gewogen worden met deze aantallen.
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:
teken één keuzerondje
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)
tik Ctrl-C en ergens anders Ctrl-V, verander de tekst in v
voeg eventueel een groepsvak toe, die je om deze twee keuzerondjes tekent
doe hetzelfde voor het soort geborene; maak daar een koppeling met een andere cel (in het voorbeeld N3)
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:
teken deze op de plaats waar je hem wilt hebben (kun je later natuurlijk nog aanpassen)
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.
Een korte toelichting op de berekening:
zoek eerst de rij op van de geboorteweek (in cel N10 met de functie VERGELIJKEN)
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.
in cel N12 bepalen we de percentiel-waarde die bij die kolom hoort
ook het bijbehorende gewicht wordt dan opgezocht in cel N13
in kolom O worden de vorige 2 stappen uitgevoerd voor een hogere percentielkolom
we interpoleren tussen de hiervoor bepaalde waardes om een schatting te krijgen voor het percentiel dat bij het opgegeven geboortegewicht hoort (zie cel O14)
Om de markering op de juiste plaats te krijgen voegen we een nieuwe kolom in de hulptabel toe met voor elke geboorteweek de waarde NB() en alleen in de betreffende geboorteweek komt het geboortegewicht (zie de kolom Verg in de hulptabel op het tabblad Vergelijken).
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:
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.
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.
.
.
Klik je daar op, dan zul je zien dat maar een (klein) gedeelte van de 100 regels in de grafiek kunnen worden weergegevens; maar dat is ook logisch.
kun je bestaande opmaak wissen.
-teken.
