Hoe kan ik auto-sorteren in Excel?

Status
Niet open voor verdere reacties.

tomswaelen

Gebruiker
Lid geworden
8 dec 2004
Berichten
349
Ik heb een werkblad in Excel dat scores bevat voor een quiz die ik ga organiseren. Per rij staat een team, per column staan de scores per ronde. Dus kort: A2 bevat de score op ronde 1 voor team 1, B2, bevat de score voor ronde 2 voor team 1, enzovoort.

Nu zou ik dat werkblad automatisch afgaand willen laten sorteren zodra er iets verandert, dus zodra ik een score update of invoer. Uiteindelijk zal de winnaar van de quiz zo bovenaan eindigen, als alle scores zijn ingevuld.
Blijkbaar gaat dit standaard niet in Excel (zou trouwens wel moeten, ik kan verschillende toepassingen hiervoor bedenken), dus het zal in VBA moeten. Spijtig genoeg ken ik hier helemaal niks van.

Dit zijn de bereiken die ik wil laten sorten, de belangrijkste eerst.

Rij 1 is de rij met de kolomtitels (Ronde 1, Ronde 2,...)
C2: C26
N2:N26
M2:M26
L2:L26
K2:K26
J2:J26
I2:I26
H2:H26
G2:G26
F2:F26
E2:E26
D2:D26

Kan iemand mij dus helpen? Dit moet echt stap-voor-stap, want zoals ik al zei, ik ken helemaal niets van VBA...

Can anyone help me? I need to be taken through this step-by-step, as I know literally nothing about VBA...
 
Formules kunnen ook hoor.

Gebruik de GROOTSTE functie en ook VERT.ZOEKEN functies. Heb nu geen tijd om het concreet op te lossen. Maar op mijn site, ga naar Voetbal > Lierse > download het overzichtbestand onderaan, toon de kolommen rechts van de tabel. Daar is het uitgewerkt met zelfs sorteren op meerdere voorwaarden. Bij je kwis heb je dat ook nodig, bv. punten van eerste ronde voor een gelijke stand op het eind.

Wigi
 
Ik zou eerst eens zelf het verloop dat je wilt, op gaan nemen met de macro-recorder.
Extra, Macro, Macro opnemen.

Als je het gewenste verloop stap voor stap opneemt kom je volgens mij al een heel eind.

If statements en lussen kun je er later eventueel inbouwen.
 
Laatst bewerkt:
Je hebt helemaal geen VBA nodig.

Zie bijlage voor uitgewerkt voorbeeld.

Ploegen worden gesorteerd: eerst op punten, dan op punten uit ronde 1, vervolgens op ploegnaam (als je ze alfabetisch zet tenminste).

Wigi
 

Bijlagen

  • Kwisrangschikking.zip
    2,5 KB · Weergaven: 1.502
Je hebt helemaal geen VBA nodig.

Zie bijlage voor uitgewerkt voorbeeld.

Ploegen worden gesorteerd: eerst op punten, dan op punten uit ronde 1, vervolgens op ploegnaam (als je ze alfabetisch zet tenminste).

Wigi

Ik heb je ZIP gedownload, maar geraak er eigenlijk niet wijs uit; je gebruikt een boel formules en berekeningen die ik niet begrijp. Ik ben ook helemaal niet goed in wiskunde :rolleyes:

De realiteit is ook lichtjes anders trouwens.

Ik heb twee bestanden:

- Quiz.xls: deze bevat verschillende tabbladen, waaronder de tabbladen per ronde (het resultaat op elke vraag wordt ingegeven, 1 is correct, 0 is fout) en het samenvattingsblad, met de rondetotalen per team. Het samenvattingsblad bevat referenties naar de rondetotalen in de andere tabs
- Projectie.xls: is eigenlijk bijna hetzelfde als het samenvattingsblad in de andere tab, maar visueel iets aantrekkelijker. Deze XLS bevat bijna uitsluitend referenties naar Quiz.xls. Projectie.xls wordt tijdens de quiz geprojecteerd, door beide bestanden in Excel te openen, het bureaublad horizontaal uit te breiden naar de projector, en Projectie.xls aan de rechterkant te zetten. Elke aanpassing in het samenvattingsblad van Quiz.xls
zal zich dus ook doorvoeren in Projectie.xls.

Het enige wat ik dus wil, is dat Projectie.xls zich automatisch sorteert, zodat het team met de meeste punten altijd bovenaan komt te staan, en zo naar beneden.

Ik heb al een VBA-code gevonden om dit te doen, maar dit maakt het geheel heel traag, want als ik de scores per vraag invoer voor een ronde (dus 1 of 0), past het totaal zich in het samenvattingsblad, en dus ook in Projectie.xls, en dan wordt de autosorteerfunctie ook nog eens geactiveerd. Dit gebeurt telkens als ik een 1 of 0 invoer, dus dat maakt het geheel traag. Eigenlijk zou de auto-sort in Projectie.xls pas moeten worden geactiveerd als ik de laatste 'vraagscore' (dus 1 of 0) van een team invul, dus eigenlijk of het team de laatste vraag van de ronde correct had.
 

Bijlagen

  • Quiz.zip
    34,2 KB · Weergaven: 386
Ik heb je ZIP gedownload, maar geraak er eigenlijk niet wijs uit; je gebruikt een boel formules en berekeningen die ik niet begrijp. Ik ben ook helemaal niet goed in wiskunde :rolleyes:

Vermijd VBA hier, het gaat ook met formules zoals ik in het voorbeeldbestandje gedaan heb.

Het is nochtans echt niet moeilijk te begrijpen.

=INTEGER(ASELECT()*10)

genereert gewoon een random getal tussen 0 en 10, dus een score. Vervang die door de echte punten van de ploegen.

Ik heb 5 ploegen gedaan, breid uit naar het echte aantal.

De bovenste tabel zijn dus gewoon de gegevens, de onderste is de gesorteerde versie. Als je wilt kan je die op een ander tabblad zetten of in een ander bestand.

De coëfficiënt die voor elke ploeg berekend wordt, wordt gebruikt in de sortering. Hoe groter, hoe beter. Tel gewoon de punten op om die coefficient te bekomen. Maar dan kunnen er ex-aequo's zijn. Daarom punten van de eerste ronde erbij optellen. Maar die punten van ronde 1 zijn minder belangrijk. We delen die door een getal dat groot genoeg is zodanig dat ze niet de totale punten kunnen beïnvloeden.

Ik bedoel:

als er een ploeg is met 160 punten en 2 ploegen met 159, dan mogen die laatste 2 minder dan 1 punt bijkrijgen (of ze zouden die eerste ploeg voorbijsteken).

De laatste sleutel om op te sorteren, is alfabetisch. Ploegen met evenveel punten totaal en evenveel in ronde 1 worden dan alfabetisch gesorteerd. 't Is te zeggen, ploeg in rij 2 komt voor die in rij 3, die voor die in rij 4, enz. als er nog een ex-aequo is na puntentotaal en punten eerste ronde. (er is wel een foutje geslopen in de formule: het moet zijn:

=H2+C2/100-RIJ()/1000

en dan naar beneden doorvoeren.

Hoe vind je de 2de tabel:

de GROOTSTE functie geeft het eerste grootste, tweede grootste, derde grootste, vierde grootste, en vijfde grootste getal. Met VERT.ZOEKEN formules halen we de gegevens op uit de eerste tabel. Zie helpfiles als dat niet duidelijk is.

KOLOM() geeft gewoon de kolomnummer van de huidige cel.

Nu duidelijk?

Wigi
 
Vermijd VBA hier, het gaat ook met formules zoals ik in het voorbeeldbestandje gedaan heb.

Het is nochtans echt niet moeilijk te begrijpen.

=INTEGER(ASELECT()*10)

genereert gewoon een random getal tussen 0 en 10, dus een score. Vervang die door de echte punten van de ploegen.

Ik heb 5 ploegen gedaan, breid uit naar het echte aantal.

De bovenste tabel zijn dus gewoon de gegevens, de onderste is de gesorteerde versie. Als je wilt kan je die op een ander tabblad zetten of in een ander bestand.

De coëfficiënt die voor elke ploeg berekend wordt, wordt gebruikt in de sortering. Hoe groter, hoe beter. Tel gewoon de punten op om die coefficient te bekomen. Maar dan kunnen er ex-aequo's zijn. Daarom punten van de eerste ronde erbij optellen. Maar die punten van ronde 1 zijn minder belangrijk. We delen die door een getal dat groot genoeg is zodanig dat ze niet de totale punten kunnen beïnvloeden.

Ik bedoel:

als er een ploeg is met 160 punten en 2 ploegen met 159, dan mogen die laatste 2 minder dan 1 punt bijkrijgen (of ze zouden die eerste ploeg voorbijsteken).

De laatste sleutel om op te sorteren, is alfabetisch. Ploegen met evenveel punten totaal en evenveel in ronde 1 worden dan alfabetisch gesorteerd. 't Is te zeggen, ploeg in rij 2 komt voor die in rij 3, die voor die in rij 4, enz. als er nog een ex-aequo is na puntentotaal en punten eerste ronde. (er is wel een foutje geslopen in de formule: het moet zijn:

=H2+C2/100-RIJ()/1000

en dan naar beneden doorvoeren.

Hoe vind je de 2de tabel:

de GROOTSTE functie geeft het eerste grootste, tweede grootste, derde grootste, vierde grootste, en vijfde grootste getal. Met VERT.ZOEKEN formules halen we de gegevens op uit de eerste tabel. Zie helpfiles als dat niet duidelijk is.

KOLOM() geeft gewoon de kolomnummer van de huidige cel.

Nu duidelijk?

Wigi

Nog steeds niet duidelijk. Ik denk dat die coëficient onnodig is, want ex-aequo's mogen bestaan. Het scorebord dient alleen zodat de ploegen hun eigen score kunnen bijhouden, controleren of de jury geen verbeterfouten heeft gemaakt en om te zien waar ze ongeveer in de rangschikking staan. Pas na de finaleronde worden ex-aequo's beslist, de ploeg die het meeste had in de finaleronde, wint. Maar dat moet niet op het scorebord worden weergegeven, want we zetten de projector af vóór de finaleronde; het moet namelijk nog een beetje spannend blijven.

Ik denk, als je mijn ZIP gedownload hebt, dat je wel weet waar ik naartoe wil... Het enige wat ik dus wil, is dat Projectie.xls zich automatisch sorteert op totaal, zodat het team met de meeste punten altijd bovenaan komt te staan, en zo naar beneden.
 
Als je wilt sorteren en je wilt geen rekening houden met de ex-aequo's, dan kan je gewoon als coëfficiënt het totaal nemen. Kolom E dus.

Vervolgens doe je hetzelfde als ik gedaan heb, dus 2 tabellen. De eerste heb je al, dat is tabblad 6de BBQ-Quiz (14-07-07). Nu de tweede nog.

Ik heb er net een hele pagina aan gewijd:

www.wimgielis.be, dan Excel, dan Vorrbeeldbestanden, dan Automatisch sorteren.

Wigi

PS: haal aub de Auto-sort werkbalk weg die totaal geen nut heeft.
 
Laatst bewerkt:
Als je wilt sorteren en je wilt geen rekening houden met de ex-aequo's, dan kan je gewoon als coëfficiënt het totaal nemen. Kolom E dus.

Vervolgens doe je hetzelfde als ik gedaan heb, dus 2 tabellen. De eerste heb je al, dat is tabblad 6de BBQ-Quiz (14-07-07). Nu de tweede nog.

Ik heb er net een hele pagina aan gewijd:

www.wimgielis.be, dan Excel, dan Vorrbeeldbestanden, dan Automatisch sorteren.

Wigi

PS: haal aub de Auto-sort werkbalk weg die totaal geen nut heeft.

Mooie site, maar sorry, het wil nog steeds niet lukken...

Je spreekt bijvoorbeeld over de GROOTSTE functie, maar die heb ik denk ik niet nodig, want in je tabel vind ik die functie alleen terug in de coëfficient-berekening, en ik heb die coëfficient niet nodig...
Ik begrijp die VERT.ZOEKEN-functie ook grotendeels wel, maar ik zie niet in hoe je die kan aanwenden om iets automatisch te sorteren.

Misschien dat je mij het beste gewoon zegt wat ik in de eerste rij van mijn Projectie.xls moet zetten, en hoe ik dat dan moet doortrekken.

Die Auto-sort werkbalk is ondertussen al weg, was nog een overblijfsel van toen ik het gewoon met een handmatige macro wou proberen...
 
Die Auto-sort werkbalk is ondertussen al weg, was nog een overblijfsel van toen ik het gewoon met een handmatige macro wou proberen...

Nog niet in het bestandje dat je eerder bijvoegde. En dat is wat de mensen hier downloaden om jou te proberen helpen.

Mooie site, maar sorry, het wil nog steeds niet lukken...

Je spreekt bijvoorbeeld over de GROOTSTE functie, maar die heb ik denk ik niet nodig, want in je tabel vind ik die functie alleen terug in de coëfficient-berekening, en ik heb die coëfficient niet nodig...
Ik begrijp die VERT.ZOEKEN-functie ook grotendeels wel, maar ik zie niet in hoe je die kan aanwenden om iets automatisch te sorteren.

Misschien dat je mij het beste gewoon zegt wat ik in de eerste rij van mijn Projectie.xls moet zetten, en hoe ik dat dan moet doortrekken.

Je hebt bij nader inzien wel een soort coëfficiënt nodig, want met VERT.ZOEKEN vind je enkel de eerste "match". Je puntentotaal in Projectie.xls moet met de GROOTSTE functie gebeuren. En die getallen zoek je dan op in het andere bestand. Maar zoals gezegd, het is moeilijker hier omdat er ex-aequo's kunnen voorkomen. Ook al wil je er niet voor corrigeren, je moet wel want de VERT.ZOEKEN functie zal vastlopen op ploegen met gelijk puntentotaal.

Wigi
 
Nog niet in het bestandje dat je eerder bijvoegde. En dat is wat de mensen hier downloaden om jou te proberen helpen.



Je hebt bij nader inzien wel een soort coëfficiënt nodig, want met VERT.ZOEKEN vind je enkel de eerste "match". Je puntentotaal in Projectie.xls moet met de GROOTSTE functie gebeuren. En die getallen zoek je dan op in het andere bestand. Maar zoals gezegd, het is moeilijker hier omdat er ex-aequo's kunnen voorkomen. Ook al wil je er niet voor corrigeren, je moet wel want de VERT.ZOEKEN functie zal vastlopen op ploegen met gelijk puntentotaal.

Wigi

Sorry, ik snap er echt de ballen van. Mijn basiskennis Excel is redelijk, maar zo gauw het met wat rare formules begint te werken, moet ik afhaken. Ik snap wel hoe de gegeven formules ongeveer werken, maar wat ik niet snap, is hoe je er mee kunt automatisch sorteren... :confused:

Wat is bijvoorbeeld het verschil tussen de GROOTSTE en MAX functie?
 
Sorry, ik snap er echt de ballen van. Mijn basiskennis Excel is redelijk, maar zo gauw het met wat rare formules begint te werken, moet ik afhaken. Ik snap wel hoe de gegeven formules ongeveer werken, maar wat ik niet snap, is hoe je er mee kunt automatisch sorteren... :confused:

Wat is bijvoorbeeld het verschil tussen de GROOTSTE en MAX functie?

GROOTSTE(bereik;x)

neemt het x-de grootste getal uit het bereik.

GROOTSTE(bereik;1)

is dus gelijk aan

MAX(bereik)

KLEINSTE is het omgekeerde.

Als je wacht op een oplossing, is dat waarschijnlijk niet voor de komende dagen wegens andere dingen. De uitleg op mijn site is volgens mij echt wel duidelijk genoeg om het te laten werken.

Wigi
 
GROOTSTE(bereik;x)

neemt het x-de grootste getal uit het bereik.

GROOTSTE(bereik;1)

is dus gelijk aan

MAX(bereik)

KLEINSTE is het omgekeerde.

Als je wacht op een oplossing, is dat waarschijnlijk niet voor de komende dagen wegens andere dingen. De uitleg op mijn site is volgens mij echt wel duidelijk genoeg om het te laten werken.

Wigi

Ik denk dat het begint te dagen. Je formules zien er gewoon ingewikkeld uit omdat je ze makkelijker kan doortrekken, anders zouden sommige getallen statisch blijven.

Op zich kan automatisch sorteren dus wel in Excel, met de GROOTSTE functie. Toen ik het onlangs googlede kwam ik echter alleen VBA-code tegen, raar toch dat niemand aan de GROOTSTE functie gedacht heeft. Ofwel is een VBA-code minder werk, dat kan ook...

Ik begrijp nog wel niet goed hoe je bij die formule voor de coefficient komt. Waarom bijvoorbeeld voor je eerste coefficient niet gewoon:

=(H7+C7)+((H7+C7)/1000) ?

Met andere woorden, waarom staat er bij jou nog 'minus rijnummer gedeeld door 10000' bij? (=H7+C7/100-RIJ(A7)/10000)

Dient het gebruiken van het rijnummer (A7 in jouw formule) voor ex-aequo's in de coëfficiënt? Dus de ploeg die later ingeschreven is (heeft dus een hoger rijnummer, is puur chronologisch), heeft een groter 'nadeel' als het op het sorteren aankomt?

Mijn twee bladen werken nu perfect en snel door jouw formules, maar alleen als ik jouw formule voor de coëfficiënt gebruik. Ik zou dus alleen maar willen weten hoe het komt dat jouw coëfficiënt werkt, en de mijne niet... :) Dus, waarom RIJ(A7) en waarom /10000 ?
 
Laatst bewerkt:
Ik denk dat het begint te dagen. Je formules zien er gewoon ingewikkeld uit omdat je ze makkelijker kan doortrekken, anders zouden sommige getallen statisch blijven.

Correct

Op zich kan automatisch sorteren dus wel in Excel, met de GROOTSTE functie. Toen ik het onlangs googlede kwam ik echter alleen VBA-code tegen, raar toch dat niemand aan de GROOTSTE functie gedacht heeft.

Dat pleit voor mij, neem ik aan? ;)

Mijn twee bladen werken nu perfect en snel door jouw formules, maar alleen als ik jouw formule voor de coëfficiënt gebruik. Ik zou dus alleen maar willen weten hoe het komt dat jouw coëfficiënt werkt, en de mijne niet... :) Dus, waarom RIJ(A7) en waarom /10000 ?

Simpel, omdat het nog steeds kan dat ploegen een gelijk puntentotaal hebben en een gelijke eerste ronde. Vraag is dan: waarop ga je vervolgens sorteren? Ronde 2, dan ronde 3, ... etc. Je zal toch een criterium moeten kiezen, dat bovendien nog uniek is. De naam is uniek, dus daar heb ik dan maar op gesorteerd. Waarom gedeeld door 10000? Om de vorige sortering op basis van punten uit ronde 1 niet teniet te doen. Zelfde redenering als punten ronde 1 delen door 1000 (als ik mij niet vergis).

Je mag de coëfficiënt ook anders berekenen, zolang het maar in dezelfde richting gaat: hoger = beter of lager = beter. (Bij lager, gebruik de KLEINSTE functie). De rangorde is belangrijk, niet de absolute waarde van die coëfficiënt.

Wigi
 
Correct



Dat pleit voor mij, neem ik aan? ;)



Simpel, omdat het nog steeds kan dat ploegen een gelijk puntentotaal hebben en een gelijke eerste ronde. Vraag is dan: waarop ga je vervolgens sorteren? Ronde 2, dan ronde 3, ... etc. Je zal toch een criterium moeten kiezen, dat bovendien nog uniek is. De naam is uniek, dus daar heb ik dan maar op gesorteerd. Waarom gedeeld door 10000? Om de vorige sortering op basis van punten uit ronde 1 niet teniet te doen. Zelfde redenering als punten ronde 1 delen door 1000 (als ik mij niet vergis).

Je mag de coëfficiënt ook anders berekenen, zolang het maar in dezelfde richting gaat: hoger = beter of lager = beter. (Bij lager, gebruik de KLEINSTE functie). De rangorde is belangrijk, niet de absolute waarde van die coëfficiënt.

Wigi

A ja, je ging er in je artikel vanuit dat de namen alfabetisch stonden, dat was hetgeen ik nog miste. De ploeg die het eerst inschreef voor de kwis heeft echter een hoger ploegnummer, dus ipv alfabetisch wordt er eigenlijk chronologisch gesorteerd... :thumb:

Ik heb hem nu wel door, bedankt! Ik zal straks een filmpje posten van de spreadsheets in actie. Ik heb toch niks anders te doen... ;)
 
Ik heb wel iets voor jou...

Mijn autootje moet eens gewassen worden :D

Grapje hé.

Wigi

Hier is de link:

http://users.skynet.be/tomswaelen/excel.wmv

Je kan hem gerust ook downloaden om op je site te zetten of zo. Zoals ik al zei, ik geef de punten per vraag in (1 of 0), dan zijn daar makkelijker statistiekjes op te trekken, zoals hoeveel ploegen hadden die ene vraag juist, hoeveel fout, enzovoort. En het zijn twee vensters omdat het rechtse geprojecteerd zal worden.

Nog één vraagje: Gemiddeldes onderaan het scorebord (voor totaal en de rondes) zullen nu niet kloppen tot alle punten ingegeven zijn, omdat niet ingegeven punten een 0 zijn, het gemiddelde neemt deze mee. Is er een manier om de formule aan te passen, of om de 0'en te doen verdwijnen? Probleem is dat een ploeg ook effectief 0 op een ronde kan halen natuurlijk...
 
Doe iets als dit:

=ALS(eerderegemiddeldeformule=0;"";eerderegemiddeldeformule)

bouw dit dus rond de bestaande formule.

Wigi
 
Doe iets als dit:

=ALS(eerderegemiddeldeformule=0;"";eerderegemiddeldeformule)

bouw dit dus rond de bestaande formule.

Wigi

Ik begrijp niet heel goed waar je met die formule naartoe wilt. Ik weet natuurlijk wel hoe ALS werkt, maar zoals ik al zei, mijn wiskundig inzicht is niet denderend... :eek:

Als ik nu een score ingeef, zal het gemiddelde altijd /25 zijn (want er is plaats voor 25 ploegen). Als ik ploeg 1 een score geef, zal het gemiddelde 9/25 zijn, terwijl het eigenlijk 9 moet zijn, want ik heb nog maar één ploeg ingegeven.

Het is geen ramp als het gemiddelde pas op het einde van de score-ingaves per ronde klopt, maar mooi is anders he...
 
Ik begrijp niet heel goed waar je met die formule naartoe wilt. Ik weet natuurlijk wel hoe ALS werkt, maar zoals ik al zei, mijn wiskundig inzicht is niet denderend... :eek:

Als ik nu een score ingeef, zal het gemiddelde altijd /25 zijn (want er is plaats voor 25 ploegen). Als ik ploeg 1 een score geef, zal het gemiddelde 9/25 zijn, terwijl het eigenlijk 9 moet zijn, want ik heb nog maar één ploeg ingegeven.

Het is geen ramp als het gemiddelde pas op het einde van de score-ingaves per ronde klopt, maar mooi is anders he...

Aangezien een ploeg 0 kan hebben, kan je niet

- de som nemen van de punten
- delen door het aantal ploegen met punten > 0.

Daarom zal je de basisgetallen moeten nemen, niet die in het samenvattende bestand, en dan:

- de som nemen van de punten
- delen door het aantal ploegen met een ingevuld getal, een AANTAL functie is daarvoor geschikt.

Wigi
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan