Automatisch sorteren
- 13/03/2007
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Automatisch sorteren kan je op meerdere manieren doen:
- het sorteren programmeren in Excel en toewijzen aan een knop (echt automatisch is dit dus niet)
- de gegevens in een draaitabel zetten en die laten updaten via VBA-code
- het sorteren programmeren in Excel en gebruiken in de vorm van een Worksheet_Change event
- de sortering met behulp van formules doen
De laatste optie lijkt mij in veel gevallen aangewezen en heb ik dan ook in een bestandje uitgewerkt. Denk maar aan het rangschikken van personeel naar verkopen, of het rangschikken van ploegen in een competitie, of het opstellen van een rangschikking voor een lokale kwis. Download het bestandje hierboven.
Achtergrond
Wat is mijn gedachtengang geweest? Denk bv. aan het sorteren van ploegen in een kwis. De ploeg met de meeste punten staat bovenaan, vervolgens aflopend naar beneden de andere ploegen. Maar ex-aequo's kunnen ook voorkomen. Vandaar dat er tegelijk ook gesorteerd wordt op de punten van ronde 1. En nog is het niet gedaan, want er kunnen dan nog ex-aequo's voorkomen. Er wordt dan gesorteerd op rijnummer (hogere rijnummers komen eerst - bv. een alfabetische sortering).
Ik heb 5 ploegen genomen, breid dit zelf uit naar het echte aantal ploegen maar pas indien nodig wel de bereiken in de formules aan. Druk daartoe op Ctrl-F3 om naar de benoemde bereiken te gaan. Daar zal je het bereik "data" zien staan. Dat moet goed ingesteld worden om de correcte cellen mee te nemen in de berekeningen.
De bovenste tabel bevat de gegevens, de onderste tabel is de gesorteerde versie met formules. Wie wil mag die gerust op een ander tabblad zetten. =INTEGER(ASELECT()*10) genereert een random getal tussen 0 en 10. Vervang die score door de behaalde punten van de ploegen, of vul zelf fictieve getallen in.
De coëfficiënt die voor elke ploeg berekend wordt, wordt gebruikt in de sortering. Die is uniek. Hoe hoger het getal, hoe beter de ploeg presteerde. Hoe berekenen we die coëfficiënt? Tel gewoon de punten op. Maar dan kunnen er ex-aequo’s zijn. Daarom voeg je de punten van de eerste ronde erbij. Maar die punten van ronde 1 zijn minder belangrijk dan het totaal (ze zijn van de 2de orde). We delen de punten uit ronde 1 daarom door een getal dat groot genoeg is zodanig dat de totale punten niet beïnvloed kunnen worden.
Concreet voorbeeld
Klein voorbeeld. Als er een ploeg is met 60 punten en 2 ploegen met 59, dan mogen die laatste 2 ploegen minder dan 1 punt bijkrijgen (of ze zouden die eerste ploeg voorbijsteken of evenaren). Vandaar de deling door 100. Delen door 10 kan verkeerd aflopen, want een ploeg met 10 op 10 in ronde 1 zou dan 10 / 10 bijkrijgen en kan zo evt. de ploeg voor zich inhalen. Delen door 100 maakt het getal klein genoeg om te eerste sortering niet te verstoren.
De laatste sleutel om op te sorteren, is per rij (alfabetisch eigenlijk als je de ploegen in de gegevenstabel alfabetisch sorteert). Ploegen met evenveel punten in het totaal en ook evenveel in ronde 1 worden gesorteerd zoals ze al stonden: komt men hoger in de gegevenstabel, dan sta je ook hoger in de gesorteerde tabel. De ploeg op plaats 2 komt voor die op plaats 3, komt voor die op plaats 4, enz. als er nog een ex-aequo is na puntentotaal en punten eerste ronde. Weer mag dat de eerdere sorteersleutels niet beïnvloeden, dus wordt er gedeeld door een voldoende groot getal. De formule voor de coëfficiënt wordt naar beneden doorgevoerd voor elke ploeg.
De tweede tabel dan. De GROOTSTE functie geeft het grootste, tweede grootste, derde grootste, vierde grootste, en vijfde grootste getal. Om de formule door te voeren heb ik niet 1 als argument gebruikt, maar wel RIJ(A1). Anders zou de 1 altijd statisch een 1 blijven. Met VERT.ZOEKEN formules halen we de gegevens op uit de eerste tabel. Die functie is voor de meeste Excelgebruikers vrij duidelijk; indien niet, kijk in de helpfiles voor extra uitleg. De KOLOM() functie geeft gewoon de kolomnummer van de cel. Dit deed ik weer om de formules snel te kunnen doortrekken, en niet telkens manueel de argumenten te moeten aanpassen.
Probeer het zelf uit
Een lange uitleg, maar hopelijk is het nu duidelijk. Ontelbare variaties zijn mogelijk, bv. de RANG functie in plaats van de SOM functie. Of oplopend sorteren. Of andere zoekfuncties gebruiken zodat de kolom met coëfficiënt niet als meest linkse kolom moet staan. Speel er maar wat mee, veel succes en vooral… plezier ;-)
