Frequentietabel en histogram



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.

  1. 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.
  2. tik in =interval(
  3. voer dan de eerste parameter van de functie in, de Gegevensmatrix. Dit zijn de brongegevens, in dit geval het bereik C3:G12.
  4. tik in ;
  5. dan komt de tweede parameter, de Interval_verw. Dit moeten cellen zijn die de gewenste intervallen aangeven; in het voorbeeld de cellen I3:I12.
  6. tik in )
  7. 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:

  1. selecteer de cellen N3:N6
  2. tik in de de formulebalk in: =INTERVAL(C3:G12;I3:I6)
  3. druk op Ctrl-Shift-Enter
  4. 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:

  1. selecteer de cellen J10:J22
  2. tik in de de formulebalk in: =INTERVAL(C3:G12;I10:I19)
  3. 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:

  1. plaats de scores 0 tot en met 10 in een kolom (hier kolom E)
  2. selecteer daarnaast de cellen in kolom F, tik in de formule =INTERVAL(tblLLscore[Score];E3:E13) en druk op Ctrl-Shift-Enter
  3. in kolom G sommeren we de scores als de score overeenkomt met cel E3, E4 etc.
  4. 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:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. vul het scherm van het Uitgebreid filter in zoals hiernaast
  3. 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?

  1. selecteer met de cursor cel C16 (de laatste score)
  2. druk op de Tab-toets
  3. de Totaalrij van de tabel verschuift automatisch naar beneden
  4. 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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Geef een antwoord

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *