Rangschikking in Excel: een complete gids met voorbeelden

Rangschikken helpt je waarden te vergelijken en patronen in je datasets te herkennen. Excel biedt krachtige tools zoals RANK(), RANK.EQ() en RANK.AVG() om rangschikken makkelijker te maken. Deze functies zijn handig in allerlei situaties, van het rangschikken van deelnemers aan een sporttoernooi op punten tot het analyseren van markttrends op basis van gerangschikte verkoopdata.

In dit artikel bekijken we alle drie de rangfuncties in Excel en leggen we uit hoe je ze gebruikt met praktische voorbeelden. We duiken ook in het combineren van rangfuncties met andere Excel-functies en het berekenen van percentielrangen. 

Het korte antwoord: zo bereken je rangen in Excel 

Gebruik de volgende formule om rangen in je gewenste datasets te berekenen:

=RANK(number, ref, [order])

De RANK()-functie in Excel begrijpen

Stel, je hebt een competitie georganiseerd onder scholieren en je wilt nu duizenden leerlingen rangschikken op basis van hun prestaties. 

Zou je uren besteden aan het sorteren van data? Nee, want Excel kan helpen. Een van de krachtige functies is de RANK()-functie, waarmee je iemands positie met één klik bepaalt. 

Om de rang van een waarde binnen een bereik te berekenen, voer je de formule in:

RANK(number, ref, [order])

In de bovenstaande formule:

• number: is het getal dat je wilt rangschikken.
• ref: is het bereik met getallen dat je meeneemt in de rangschikking.
• order: bepaalt de sorteervolgorde. Voor oplopend gebruik je “1”; voor aflopend gebruik je “0” (standaard).

Zo gebruik je de RANK()-formule in Excel

Laten we de functie RANK() toepassen op voorbeelden uit de praktijk:

Cijfers van leerlingen rangschikken

Cijfers berekenen is uitdagend en tijdrovend als je duizenden leerlingen hebt. Stel dat je de eindresultaten voorbereidt en ze op basis van scores wilt rangschikken. 

Maak daarvoor eerst je dataset klaar.

Voorbeeld van een dataset met leerlingen in Excel. Bron: afbeelding door auteur.

Voer de formule =RANK(B3,($B$3:$B$7)) in één cel in en sleep deze naar de laatste gevulde cel om de rang te krijgen.

De RANK()-functie gebruiken om de rang van leerlingen te berekenen. Bron: afbeelding door auteur.

Je hebt nu met één klik de rang van de leerling.

Maar valt je op dat twee leerlingen met dezelfde punten ook dezelfde rang 4 hebben? De volgende leerling, met 23 punten, springt naar rang 6 en slaat rang 5 over. Dat betekent dat de formule dezelfde rang aan dubbele scores toekent. 

Dit kan de volgorde verstoren bij dicht bij elkaar liggende scores. Je kunt deze gelijke standen oplossen met de functie COUNTIF() . Voer de volgende formule in: 

=RANK(B3,($B$3:$B$8)) + COUNTIF($B$3:B3, B3)-1

De functie COUNTIF() past de rang aan voor dubbele waarden. Hij telt hoe vaak de punten van de huidige leerling hierboven in de lijst voorkomen.

Gelijke standen oplossen met de functie COUNTIF() in Excel. Bron: afbeelding door auteur.

De waarde 28 komt twee keer voor en COUNTIF() houdt het aantal voorkomens bij. Dit aantal wordt gebruikt om de rang aan te passen. De formule telt het aantal voorkomens min één op bij de oorspronkelijke rang. Zo krijgt elke dubbele waarde een unieke rang.

Verkoopcijfers in een dataset rangschikken

Laten we nog een voorbeeld bekijken om te laten zien hoe je de Excel-functie RANK() in oplopende of aflopende volgorde gebruikt. 

Als je de prestaties van je verkoopteam wilt beoordelen, kun je hun maandelijkse verkoopcijfers rangschikken met de Excel-functie RANK().

Hier hebben we een dataset met maandelijkse verkoopcijfers per verkoper. We gebruiken de functie RANK() om ze te rangschikken op wie de meeste omzet heeft binnengehaald (van hoog naar laag).

Lijst met verkoopcijfers om te rangschikken. Bron: afbeelding door auteur.

Om de verkopen in oplopende volgorde te rangschikken, gebruikten we de formule =RANK(B2, ($B$2:$B$6), 1). Hier geeft de waarde “1” voor de parameter order de oplopende volgorde aan voor de rangschikking. 

Waarden rangschikken in oplopende volgorde. Bron: afbeelding door auteur.

Zodra je de formule hebt ingevoerd, houd je de cursor vast, sleep je de hoek van de cel omlaag en pas je hem toe op de rest van je verkoopdata.

Wil je de waarden in aflopende volgorde rangschikken, zet dan “0” als waarde voor de parameter order in de formule, zo: =RANK(B2, ($B$2:$B$6), 0)

Je ziet het verschil in de resultaten:

Waarden rangschikken in aflopende volgorde. Bron: afbeelding door auteur.

Je ziet misschien dollartekens in de verwijzingen ($B$2:$B$6). Dit zijn absolute verwijzingen. Ze zorgen ervoor dat de formule altijd naar hetzelfde verkoopbereik verwijst, ook wanneer je hem naar beneden kopieert, om fouten te voorkomen.

Als je geen absolute verwijzing gebruikt, zoals =RANK(A2, A2:A10), past het bereik zich aan terwijl je naar beneden sleept:

• Formule in G3: =RANK(G3,(G3:G7), 1)
• Formule in G4: =RANK(G4,(G4:G8), 1)

Gebruik absolute verwijzingen met “F4” om dit te voorkomen. De formule wordt dan: =RANK(G3,($G$3:$G$7), 1)

Als je de bovenstaande formule naar de laatste gevulde cel sleept, veranderen relatieve verwijzingen op basis van de positie van elke cel. Je ziet het verschil in de resultaten wanneer je absolute verwijzingen gebruikt:

Verschil tussen rangen met en zonder absolute verwijzing. Bron: afbeelding door auteur.

Geavanceerde Excel-rangfuncties: RANK.EQ() en RANK.AVG()

Wanneer je dubbele waarden in Excel tegenkomt, kan het lastig worden. Dan gebruik ik liever de functies RANK.EQ() en RANK.AVG(). Ze gaan er allebei net iets anders mee om. Laten we kijken hoe ze werken.

De Excel-functie RANK.EQ()

RANK.EQ() werkt net als de gewone RANK()-functie, maar heeft een duidelijkere naam. Als er dubbelen zijn, kent RANK.EQ() dezelfde rang toe aan alle duplicaten en slaat de volgende rang(en) over.

Syntax: =RANK.EQ(number, ref, [order])

In de bovenstaande formule:

• number: is het getal dat je wilt rangschikken.
• ref: is het bereik met getallen dat je meeneemt in de rangschikking.
• order: bepaalt de sorteervolgorde. Voor oplopend gebruik je “1”; voor aflopend gebruik je “0” (standaard).

Laten we dit met een voorbeeld begrijpen: ik had een dataset met de bevolkingsomvang van verschillende landen en wilde deze landen rangschikken op basis van hun populatie.

Zo deed ik dat met de functie RANK.EQ():

De RANK.EQ()-functie gebruiken. Bron: afbeelding door auteur.

In de afbeelding hierboven krijgt het land met de hoogste bevolking rang 1, enzovoort. Nu zie je hoe RANK.EQ() omgaat met gelijke standen. 

Omdat de VS en Indonesië identieke bevolkingsaantallen hebben, delen ze dezelfde rang 2. Dit laat zien hoe RANK.EQ() dezelfde rang toekent aan duplicaten en de volgende rang in de reeks overslaat.

De Excel-functie RANK.AVG()

De functie RANK.AVG() pakt gelijke standen anders aan. In plaats van dezelfde rang toe te kennen aan alle duplicaten, berekent hij het gemiddelde van de rangen die ze afzonderlijk zouden hebben gekregen.

Syntax: RANK.AVG(number, ref, [order])

In de bovenstaande formule:

• number: is het getal dat je wilt rangschikken.
• ref: is het bereik met getallen dat je meeneemt in de rangschikking.
• order: bepaalt de sorteervolgorde. Voor oplopend gebruik je “1”; voor aflopend gebruik je “0” (standaard).

Laten we dit begrijpen met een voorbeeld: ik had een dataset met scores en paste de functie RANK.AVG() toe om te zien hoe die met gelijke scores omgaat.

De RANK.AVG()-functie gebruiken. Bron: afbeelding door auteur.

Met =RANK.AVG(D3,$D$3:$D$6) krijgt de score 88 een rang van 2,5. Dat betekent dat de functie het gemiddelde neemt van de rangen die afzonderlijk zouden zijn toegekend. 

Deze aanpak houdt de verdeling van rangen evenwichtiger, vooral in datasets met veel gelijke standen.

RANK() combineren met andere Excel-functies

De functie RANK() combineren met andere Excel-functies is in sommige scenario’s handig. Laten we een paar veelvoorkomende use-cases bekijken.

RANK() en ROUNDUP() combineren om data te categoriseren

Zo kun je de functie ROUNDUP() combineren met de functie RANK():

De functies RANK() en ROUNDUP() combineren in Excel. Bron. Afbeelding door auteur.

Ik gebruikte de formule =ROUNDUP(RANK(B2, $B$2:$B$7, 1)/3, 0), die de rang berekent van de waarde in cel B2 binnen het bereik B2 in oplopende volgorde. Vervolgens deelt hij deze rang door drie en rondt het resultaat naar boven af op het dichtstbijzijnde gehele getal.

Met de bovenstaande formule categoriseer je de gegevens effectief in duidelijke groepen of niveaus, wat kan helpen bij het herkennen en analyseren van verschillende prestatieniveaus of bij het categoriseren van data voor verdere analyse.

RANK.EQ() en COUNTIFS() combineren om gelijke standen te breken

We hebben een paar manieren besproken om gelijke standen te behandelen met de Excel-functie RANK(). Stel echter dat je producten uitsluitend op prijs wilt rangschikken. Dat kan willekeurig aanvoelen wanneer meerdere items dezelfde prijs hebben. 

Laten we daarom een secundair criterium introduceren om gelijke standen te doorbreken en een objectiever rangsysteem te maken.

Neem een lijst met klanten die koptelefoons hebben gekocht. We willen ze rangschikken op prijs (primair criterium) en vervolgens de klantbeoordeling (secundair criterium) gebruiken om bij gelijke prijs te onderscheiden.

Voorbeeldlijst met klanten. Bron: afbeelding door auteur 

Om dit te bereiken combineren we de functie RANK.EQ() met de functie COUNTIFS(). Zo doe je dat:

=RANK.EQ($B2,$B$2:$B$9) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, ">"& $C2)

Rang berekenen met RANK.EQ() en COUNTIFS(). Bron: afbeelding door auteur.

Het deel COUNTIFS($B$2:$B$9,$B2) telt hoe vaak de huidige prijs in de lijst voorkomt. We gebruiken absolute verwijzingen voor het bereik, $B$2:$B$9, om consistentie te behouden, maar laten de criteriacel, $B2, relatief zodat de formule elke rij afzonderlijk controleert.

Het tweede deel, $C$2:$C$9,">"&$C2, zoekt het aantal klantbeoordelingen dat hoger is dan de beoordeling van de huidige koptelefoon, C2.

COUNTIFS() volgt “EN”-logica: het telt alleen cellen die aan alle voorwaarden voldoen. Als er geen andere koptelefoons met dezelfde prijs een hogere beoordeling hebben, geeft COUNTIFS() 0 terug. Daarom blijft de door RANK.EQ() bepaalde rang ongewijzigd.

Percentielrang in Excel berekenen

Omdat de functie RANK() een numerieke waarde rangschikt, kun je met een Excel-formule voor percentielrang de rang van een waarde binnen een dataset berekenen als percentage van de totale dataset. 

Excel biedt twee functies om percentielrangen te berekenen. Laten we ze allebei bekijken. 

De Excel-functie PERCENTRANK.INC()

PERCENTRANK.INC() berekent de percentielrang van een waarde, inclusief de kleinste en grootste waarden in de dataset.

Laten we deze functie met een voorbeeld begrijpen: ik had een dataset met de examenresultaten van 10 leerlingen en wilde de percentielrang van elke score berekenen, inclusief de kleinste en grootste waarden. 

Daarom gebruikte ik de formule =PERCENTRANK.INC($A$2:$A$11, A2):

De rang berekenen met de functie PERCENTRANK.INC(). Bron: afbeelding door auteur

In het bovenstaande voorbeeld zie je dat de functie PERCENTRANK.INC() de kleinste en grootste waarden in de dataset meeneemt. De score 45 (kleinste waarde) heeft een percentielrang van 0 en de score 90 (grootste waarde) heeft een percentielrang van 1.

De Excel-functie PERCENTRANK.EXC()

PERCENTRANK.EXC() berekent de percentielrang van een waarde, exclusief de kleinste en grootste waarden in de dataset.

Laten we deze functie met een voorbeeld begrijpen:  Ik paste de functie PERCENTRANK.EXC() toe op dezelfde leerlingendataset uit de vorige sectie:

De rang berekenen met de functie PERCENTRANK.EXC(). Bron: afbeelding door auteur

In het bovenstaande voorbeeld sluit de functie PERCENTRANK.EXC() de kleinste en grootste waarden in de dataset uit. Dat betekent dat de score 45 (kleinste waarde) geen percentielrang van 0 heeft en de score 90 (grootste waarde) geen percentielrang van 1.

Veelvoorkomende fouten met de Excel-functie RANK() en hoe ze te vermijden

Excel-functies zijn handig en eenvoudig in gebruik voor al je werk. Maar soms maken zelfs de meest ervaren gebruikers een paar veelvoorkomende fouten. Daarom deel ik een aantal valkuilen om op te letten en hoe je ze voorkomt:

Formules kopiëren en plakken

Als je je rangschikformule over cellen kopieert, zorg er dan voor dat je niet per ongeluk absolute verwijzingen vergeet te gebruiken. Elke cel kan anders gaan verwijzen en je rangschikking verstoren. 

Gebruik om dit te voorkomen absolute verwijzingen, zoals $A$1:$A$10, wanneer je je formules opstelt, zodat het juiste bereik in al je rangschikkingen vergrendeld is.

Gelijke waarden niet afhandelen

Stel dat twee verkopers bovenaan gelijk eindigen. Hoe je gelijke standen afhandelt in je rangschikking kan veel uitmaken: de functies RANK() en RANK.EQ() behandelen gelijke standen op vergelijkbare wijze.

Bepaal dus of gelijke waarden dezelfde of een verschillende rang moeten krijgen, of dat hun rangen gemiddeld moeten worden met RANK.AVG(). Gebruik daarna de juiste functie. 

Verkeerde volgorde 

Het lijkt simpel, maar het vergeten instellen van het argument order in je functie kan rangen in de verkeerde richting sorteren (oplopend of aflopend). 

Controleer dit argument daarom altijd: gebruik “0” (of laat het leeg) voor aflopend en “1” voor oplopend. Zorg dat de volgorde overeenkomt met hoe jij je data wilt rangschikken.

Verkeerde functie kiezen

Als je met verschillende datasets werkt, is het cruciaal de juiste rangfunctie te kiezen, want doorgaan met de verkeerde functie levert ongewenste resultaten op.

Gebruik om dit te voorkomen RANK.EQ() voor standaard rangschikking en RANK.AVG() voor situaties waarin de afhandeling van gelijke standen het resultaat beïnvloedt, zoals bij statistische analyses. 

Verouderde formules gebruiken 

Naarmate je data groeit, moet je de bereikverwijzingen in je formules bijwerken. Doe je dat niet, dan worden nieuwe datapunten niet meegenomen in je rangschikkingen en vraag je je af waarom je resultaat niet klopt.

Werk daarom regelmatig de bereiken in je formules bij en controleer ze. Zo zorg je dat al je waardevolle data wordt meegenomen in de rangschikking.

Tot slot

Je weet nu hoe je functies als RANK(), RANK.EQ() en RANK.AVG() gebruikt om gelijke standen af te handelen en verschillende rangproblemen op te lossen. Deze functies helpen in uiteenlopende scenario’s, zoals het analyseren van cijfers van leerlingen of het ordenen van verkoopcijfers.

Wil je je Excel-vaardigheden verder uitbouwen, dan raad ik je aan om enkele uitstekende cursussen en resources op Datacamp te bekijken: 

• Begin met de cursus Introduction to Excel om basiskennis op te doen en ga daarna door met Data Analysis in Excel en Excel Fundamentals.  
• Overweeg de cursus Data Visualization in Excel om je vaardigheid in datavisualisatie te vergroten. 
• De cursus Introduction to PowerQuery in Excel is zeer waardevol voor meer geavanceerde datamanipulatie. 
• Wil je je Excel-expertise formaliseren, bekijk dan deze uitgebreide gids: How to Earn a Microsoft Excel Certification.