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.
.
.
