---

---

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

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

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.

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

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.

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

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

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.

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.

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.

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:

En de tabel heeft ook een Totaalrij gekregen.

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!

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

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

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.

---