Tagarchief: Rang

Tour de France 2020



De Tour de France: ieder jaar kijk ik er weer naar uit, deze keer wat langer dan anders!
Een artikel op de site van G-Info met de Tour-gegevens als basis mag dan ook niet ontbreken.

Overal vind je wel standen en overzichten, dus dat gaan we niet overdoen. Het leek me wel aardig om te kijken of we een overzicht kunnen maken van de meest constant-presterende renners.
We zullen daarbij allerlei manieren van tellen en zoeken gaan gebruiken, variërend van de functie AANTAL.ALS, de combinatie van Index en Vergelijken tot het gebruik van Gegevensvalidatie en Keuzelijsten.

Doel

Dit jaar gaat de Tour al direct los: de eerste dagen moeten veel ‘heuvels’ en bergen bedwongen worden (zie het tabblad Etappes in het Voorbeeldbestand). De sprinters hebben dan niet al te veel kans. Misschien vallen er zelfs al renners uit die categorie uit voordat ze aan een massa-sprint kunnen beginnen.

Daarom gaan we in dit artikel eens kijken welke renners zich het meest in de top-10 laten zien: dat noemen we dan maar de meest-constante renners.

Bron-gegevens

Etappes

Onder andere op www.touretappe.nl kun je een overzicht van de etappes vinden. Op het tabblad Etappes van het Voorbeeldbestand heb ik die overgenomen. Alle etappes zijn via de optie Koppeling (rechts klikken op een cel) aan een pagina van die site gelinked, zodat de details van een etappe direct zijn te vinden.
Met behulp van voorwaardelijke opmaak zijn de soorten etappes zichtbaar gemaakt. Onderaan wordt het aantal per soort geteld. In cel H26 staat daartoe de formule:
=AANTAL.ALS(tblEtappes[Type];G26)
Tel het Aantal Als in de kolom Type van de Excel-tabel tblEtappes de waarde uit cel G26 (hier Bergen) voor komt.
Van de 21 etappes zijn er dus 9 als berg-etappe gekwalificeerd!

Teams en renners

Op het tabblad Teams van het Voorbeeldbestand staat de definitieve deelnemerslijst (in een Excel-tabel tblTeams) zoals die op de site wielerflits.nl is terug te vinden. Op die site zijn de ploegen en renners voorzien van een landen-vlaggetje; bij het plakken in Excel wordt dit vertaald naar een code. Die kunnen we goed gebruiken om ons overzicht te verrijken met echte landnamen.

Aan de tabel tblTeams zijn daarom 2 kolommen toegevoegd:

  • Nr: ieder team en renner krijgt een nummer: het team van de vorige winnaar heeft nummer 0, de kopman van dat team krijgt nummer 1 en de overige renners krijgen hun nummer in alfabetische volgorde.

NB1 is Dumoulin bijgelovig? Hij heeft nummer 13 geruild met de Noor Grøndahl Jansen.

NB2 de kolom Nr kun je handigst op de volgende manier vullen: de eerste cel (D6) krijgt nummer 0 en in de cel daaronder plaatsen we de formule =D6+1. Deze formule doorvoeren naar alle cellen daaronder. Wis dan in de lege regels de cel in kolom D en vul bij het team het volgende tiental in (Jumbo krijgt dan nummer 10, BORA 20 etc).

  • Land: aan de hand van de vlagcode uit de eerste kolom bepalen we uit welk land het team of renner komt (zie tabblad Landen). Dat zou met VERT.ZOEKEN kunnen, maar we gebruiken liever de universeel toepasbare INDEX-VERGELIJKEN-methode (zie het artikel Zoeken: index en vergelijken, inclusief de avz-truc).

Landen

Aan iedere unieke VlagCd uit het tabblad Teams hebben we een land-omschrijving gekoppeld (in de Excel-tabel tblLand van het tabblad Landen in het Voorbeeldbestand).

In de derde kolom van die tabel (AantRenners) bepalen we het aantal renners per land: =AANTAL.ALS(tblRenners[Land];[@Land])
Turf het Aantal Als in de kolom Land van de tabel tblRenners de waarde uit de kolom Land in deze regel (vandaar de @) voor komt.

NB de tabel tblRenners is terug te vinden op het tabblad Renners van het Voorbeeldbestand; zie hierna.

Punten

Op het tabblad Punten van het Voorbeeldbestand hebben we vastgelegd hoe de puntenverdeling voor de eerste 10 renners van iedere etappe moet zijn.

NB1 mocht het eindresultaat straks niet bevallen, dan kunt u natuurlijk proberen uw favoriete renner te helpen door de puntenverdeling aan te passen 😉

NB2 een totaal-regel onder een Excel-tabel wordt automatisch gegenereerd als de betreffende optie is aangevinkt op de menutab Ontwerpen.

Uitslagen

Uitslag 2e etappe op letour.fr

De uitslagen verwerken is heel eenvoudig: vul van de eerste 10 renners hun rugnummers in bij de betreffende etappe (zie het tabblad Uitslagen van het Voorbeeldbestand).
Op de officiële tour-site www.letour.fr kun je die rugnummers in de uitslagen vinden.

Maar wat als je alleen maar de namen hebt?
(Er zijn waarschijnlijk nog wel meer mensen die dan meteen aan Theo Koomen, of was het Barend Barendse, moeten denken: “Aan namen heb ik niks. Rugnummers moet ik hebben“).
In Excel zijn er dan allerlei opties om het rugnummer te vinden. Hier komen er een paar:

  1. ga naar het tabblad Teams van het Voorbeeldbestand, druk in Ctrl-F, tik een gedeelte van de naam in en klik op Alles zoeken. In het onderste gedeelte van het zoek-scherm komen alle cellen die voldoen.

    Klik op de gewenste naam en u ziet het rugnummer daarnaast staan.
  2. gebruik Index en Vergelijken:

    In cel O2 van het tabblad Uitslagen wordt eerst met behulp van de functie Vergelijken gekeken op welke positie in de kolom Naam van de tabel tblTeams de invoer in cel H2 staat. Deze functie kent zogenaamde ‘wildcards’, dus we hoeven maar een gedeelte van de naam in te tikken (de *’s geven aan dat het er niet toe doet, wat er voor en achter de inhoud van cel H2 staat). Daarna wordt deze positie gebruikt om met behulp van de functie Index het betreffende Nr op te halen.
    Ter controle halen we in cel P2 op een vergelijkbare manier de naam op die hoort bij het rugnummer.
    LET OP Vergelijken geeft de eerste positie terug waarvan de naam voldoet aan de voorwaarde. Is het niet de juiste naam? Tik meer letters in, bijvoorbeeld daniel f om de renner met nummer 76 op te zoeken.
  3. denk je het rugnummer wel ongeveer te weten omdat je het team kent en je weet welk tiental bij deze ploeg hoort:

    De ploeg van Jumbo-Visma begint met renner 11, Dumoulin zit vooraan in het alfabet (en hij is geen kopman!), dus zal het wel 13, 14 of 15 zijn.
    Tik het nummer in in cel O4 en je ziet of je goed hebt gegokt.
  4. Via de menutab Gegevens in het blok Hulpmiddelen voor gegevens is aan cel H6 een Gegevensvalidatie toegewezen:

    Alleen gegevens uit kolom G van het tabblad Teams zijn toegestaan.
    In die kolom G staat voor iedere renner (en team) een koppeling van nummer en naam met een extra spatie daartussen:

    NB kolom G is standaard niet zichtbaar; via Groeperen kan de kolom ‘ingeklapt’ worden.
    LET OP je kunt een kolom ook Verbergen (via rechtsklikken op een kolomletter) maar ik ben daar geen voorstander van: het zichtbaar maken is niet zo makkelijk en vaak zie je niet dat er een kolom verborgen is.
  1. een andere, minder gebruikte, optie is een keuzelijst (met invoervak).
    Kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen.

Klik op de 2e optie binnen de Formulierbesturingselementen.

‘Teken’ nu met de cursor het gebied waar de keuzelijst moet komen.

Dan komt de vraag om het besturingselement op te maken: zorg dat in het Invoerbereik de cellen geselecteerd worden met de namen van de renners en dat er een Koppeling komt met de cel naast het invoervak (zie het tabblad Uitslagen in het Voorbeeldbestand).

Wanneer je nu een naam selecteert dan komt in de gekoppelde cel de positie van deze renner in de lijst te voorschijn. Met behulp van de formule =INDEX(tblTeams[Nr];Uitslagen!N8) wordt het rugnummer opgehaald.

  1. een andere keuzelijst maakt gebruik van Active-X; iets ingewikkelder maar wel een stuk flexibeler.

Kies opnieuw in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen. Maar, let op, klik dan op de 2e optie binnen de Active-X besturingselementen.
‘Teken’ weer met de cursor het gebied waar de keuzelijst moet komen. Nu moet je de Eigenschappen aanpassen: klik op de betreffende button in de menubalk en vul de 4 eigenschappen in zoals hiernaast (achter de pijltjes).

LET OP bij het gebruik van Active-X-elementen moet je de Ontwerpmodus uitzetten, wanneer je deze wilt gebruiken (en andersom als je de eigenschappen wilt aanpassen).

Resultaten per renner

In het tabblad Renners van het Voorbeeldbestand worden de resultaten per renner ‘automatisch’ bepaald; alleen de kolom Nr bevat harde waarden, de overigen worden afgeleid of berekend:

  1. in cel H6 staat de formule:
    =ALS.FOUT(
    INDEX(tblPunten[Punten];
    VERGELIJKEN([@Nr];INDIRECT(“tblUitslagen[“&H$4&”]”);0));
    “”)

    Aangezien cel H4 de waarde 1 bevat, wordt de 2e parameter binnen de Vergelijken-functie INDIRECT(“tblUitslagen[1]”); Excel vertaalt dit dan naar een bereik van cellen en wel de eerste kolom in de tabel tblUitslagen.
    De Vergelijken-functie kijkt dan of het rugnummer in die kolom voorkomt. De positie daarvan (1 tot 10) wordt gebruikt om met behulp van de functie Index het daarbij behorende aantal punten te genereren. Als een renner geen top-10-resultaat in een etappe heeft behaald, dan zou er een foutmelding komen; met de functie Als.Fout zorgen we er voor dat in dat geval de cel gevuld wordt met een lege tekst.
    Deze formule kan naar beneden en rechts gekopieerd, zodat voor alle renners voor alle etappes de resultaten worden bepaald.
  2. in de kolommen Naam en Land worden de gegevens opgehaald uit het tabblad Teams
  3. zo ook voor de kolom Team, behalve dat daarvoor een berekening rond het rugnummer plaats vindt:
    =INDEX(tblTeams[Naam];
    VERGELIJKEN(AFRONDEN.BENEDEN([@Nr];10);tblTeams[Nr];0)
    )
    Het rugnummer wordt dus naar beneden afgerond op het dichtstbijzijnde veelvoud van 10.
  4. in de kolom TotaalPunten wordt het totaal van de renner over alle etappes berekend: =SOM(tblRenners[@[Etap1]:[Etap21]])
  5. Dan blijft er nog 1 kolom over: Rang.
    Via de formule =RANG.GELIJK([@TotaalPunten];[TotaalPunten]) wordt in die kolom per renner de rangorde in het totaal bepaald.

Kies met het driehoekje achter Rang de gewenste sortering en u weet welke renner(s) bovenaan staat/staan.

LET OP wanneer er weer nieuwe uitslagen zijn toegevoegd, worden alle formules automatisch herberekend, maar …. de sortering wordt niet vanzelf aangepast. Die moet u zelf nogmaals uitvoeren.

Resultaten per team

In het tabblad Teams van het Voorbeeldbestand wordt op de ondertussen bekende manier per renner de TotaalPunten van die renner opgehaald. Het totaal per team berekenen we met een gewone SOM-formule.

In datzelfde tabblad staat ook een ranglijst van de teams. De formules daarin mogen geen verrassing meer zijn.

Boven die tabel staat een controlegetal: het totaal aantal punten van alle renners gedeeld door het totaal aantal dat per etappe verdiend kan worden. Dit moet een geheel getal zijn. Met voorwaardelijke opmaak krijgt de cel een kleur.

Resultaten per land

Om het totaal aantal punten per land te bepalen gebruiken we op het tabblad Landen van het Voorbeeldbestand de formule:
=SOM.ALS(tblRenners[Land];[@Land];tblRenners[TotaalPunten])

LET OP gebruik de gegevens van tblRenners en niet van tblTeams anders worden ook de totalen van de teams meegeteld.

Frankrijk heeft de meeste renners rond rijden, logisch (?) dat dit land dan bovenaan staat.

We delen het aantal punten door het aantal renners per land en we krijgen een andere ranglijst.

Resultaten per land en team

In het tabblad OvzLandTeam van het Voorbeeldbestand staat een draaitabel op basis van de tabel tblRenners. En de rijen én de kolommen worden daarin automatisch gesorteerd (zie ook het artikel Kindernamen).

Bovenin ziet u ook weer een controlegetal; als de uitslagen compleet zijn ingevuld zal dit een geheel getal zijn.

Genormeerde resultaten per land

Het tabblad OvzLandTeam van het Voorbeeldbestand bevat ook een draaitabel, nu op basis van de tabel tblLand.

Per land wordt het aantal renners geteld met daarnaast het aantal genormeerde punten (ofwel het totaal aantal punten gedeeld door het aantal renners).

NB in het hele Tour de France-systeem worden alle overzichten direct geactualiseerd na invoer van een uitslag, omdat die allemaal met formules zijn opgebouwd. Dat geldt niet voor de 2 Ovz-tabbladen: dat zijn draaitabellen en die moeten na het opvoeren van nieuwe uitslagen handmatig Vernieuwd worden (met de muis rechtsklikken op een cel in de draaitabel).

LET OP allebei de draaitabellen dienen Vernieuwd te worden aangezien ze op verschillende bronnen zijn gebaseerd.


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


Rang(orde)



Hoe vaak gebeurt het niet?
Heb je een lijstje met scores, bedragen, rondetijden. Dan wil je natuurlijk ook weten hoe iedere regel zich verhoudt tot de anderen ofwel wat de rangorde van iedere score is.
Excel heeft daar een mooie functie voor, Rang (in de Engelstalige versies Rank).

Dit artikel gaat in op de werking van deze Excel-functie en zal ook laten zien hoe dit probleem getackeld kan worden als je de volgorde bijvoorbeeld per geslacht wilt weten.

Brongegevens

Als voorbeeld nemen we een score-overzicht van diverse personen, verdeeld naar afdeling en geslacht; zie het Voorbeeldbestand, tabblad Data.
We zullen hierna dit overzicht gaan sorteren naar rangorde van de score en dat ook nog op verschillende manieren.
Om het oorspronkelijke overzicht weer te kunnen herstellen hebben de namen ook een volgnummer meegekregen; deze kan in een sortering gebruikt worden.

De gegevens zijn in een Excel-tabel geplaatst met de naam tblNamen.

NB voor demonstratie-doeleinden (zie hierna) wordt de kolom Afd telkens opnieuw gevuld: =KIEZEN(ASELECTTUSSEN(1;4);”Afd1″;”Afd2″;”Afd3″;”Afd4″)

Rangorde 1: sortering

Sortering is natuurlijk de meest gehanteerde methode om een rangorde te bepalen:

  • klik met de rechter muisknop op één van de cellen in de score-kolom
  • kies de optie Sorteren
  • in het vervolgmenu kiest u de optie Oplopend of Aflopend

Uiteraard kunt u ook de knop Sortering uit de menubalk gebruiken.

NB Aangezien de gegevens in een Excel-tabel vastliggen hoeven we niet bang te zijn dat niet alle gegevens worden meegenomen bij de sortering.

Rangorde 2: Rang.Gelijk

De vorige methode heeft als nadeel, dat we niet direct zien wat de exacte plaats van een persoon is in de rangorde (behalve misschien de eerste 5).

Daarom is de tabel uitgebreid met een aparte kolom met de naam Rang.Gelijk.
De kolom is gevuld met de volgende formule:
=RANG.GELIJK([@Score];[Score];$G$2)

De functie Rang.Gelijk bepaalt aan de hand van de waarde in de Score-kolom in de betreffende regel (vandaar het @-teken in de eerste parameter) de rangorde in de totale Score-kolom (tweede parameter).
Zijn 2 of meer scores Gelijk dan krijgen de betreffende regels allemaal dezelfde rang toegewezen en wel de eerste (dus in het voorbeeld levert de score 62 voor alle betreffende regels de rang 11 op).
De derde parameter geeft aan of de rangorde van hoog naar laag bepaald moet worden of niet. Is deze parameter 0 of ONWAAR (of wordt dit derde argument weggelaten) dan zal de rangorde van hoog naar laag worden berekend; is deze parameter gelijk aan een ander getal of WAAR dan andersom.

In het voorbeeld wordt deze derde parameter gevuld met de waarde in cel G2.

Deze cel is op zijn beurt gekoppeld aan een Selectievak, zodat we de volgorde met het aan- en uitzetten van een vinkje kunnen beïnvloeden.

Hoe voeg je een Selectievak toe?

  • kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  • kies in het vervolgscherm binnen de Formulierbesturingselementen de derde optie (Selectievakje)
  • ‘teken’ met de linkermuisknop ingedrukt op de gewenste plaats de omtrek van het selectievak
  • de grootte, plaats en tekst-inhoud kunnen nu nog aangepast worden
  • klik rechts op het nieuwe vakje en kies de optie Besturingselement opmaken
  • nu kunnen de kleuren en lijnen ingesteld worden en ook de koppeling met een cel. In het voorbeeld hebben het selectievakje gekoppeld aan de cel G2.

In het voorbeeld hiernaast is de tabel gesorteerd op de score-kolom.
De 2 regels met score 47 hebben allebei de rang 8 gekregen; score 48 heeft de rang 10.

Iets vergelijkbaars zien we bij de score 62: drie regels krijgen de rang 16; de eerstvolgende heeft rang 19.

NB Excel kent ook een functie Rang (zie de laatste kolom van de tabel in het tabblad Data van het Voorbeeldbestand).
De werking hiervan is exact gelijk aan de functie Rang.Gelijk; Rang is vanwege compatibiliteit met oude versies nog steeds beschikbaar.

Rangorde 3: Rang.Gemiddelde

De werking hiervan is vergelijkbaar met de functie Rang.Gelijk.
Maar in het geval van gelijke scores wordt nu niet de laagste rang toegekend maar het gemiddelde van de posities van die score. Dus hiernaast krijgt de score 83 niet op 2 plaatsen de rang 4 maar het gemiddelde van rang 4 en 5, dus 4½.

Rangorde 4: Draaitabel1

Een rangorde kunnen we ook aanbrengen met behulp van een draaitabel (zie tabblad Draai in het Voorbeeldbestand).

  • maak een draaitabel op basis van de Excel-tabel tblNamen
  • plaats het veld Naam in de Rijlabels
  • Plaats het veld Score in het waardengebied
  • idem voor het veld Rang
  • en nogmaals voor het veld Score
  • wijzig de koppen in de draaitabel door in de betreffende cellen een nieuwe tekst te tikken (eventueel extra spatie aan de tekst toevoegen om deze uniek te maken)
  • klik rechts op een van de waarden in de kolom RangNeer, kies de optie Waarden weergeven als en kies dan de optie Positie van groot naar klein.
  • klik rechts op één van de namen, selecteer de optie Sorteren en dan Meer sorteeropties
  • vul het vervolgscherm in zoals hiernaast weergegeven
  • klik op OK

LET OP Zoals in het voorbeeld te zien is werkt de draaitabel-functionaliteit iets anders dan de functie Rang!
Gelijke scores krijgen ook eenzelfde rangnummer, maar de volgende score krijgt gewoon het volgende rangnummer (zie bijvoorbeeld hierboven bij score=82).

NB als de brongegevens wijzigen of de volgorde van ranking, dan moet de draaitabel vernieuwd worden (rechts klikken en Vernieuwen kiezen).

Rangorde 5: Draaitabel2

Maar….
het mooie van de draaitabel is wel, dat wanneer er nog meer velden in de Rijlabels worden geplaatst, de ranking rekening houdt met deze indeling: de rangnummers worden binnen de categorieën bepaald (hiernaast een aparte ranking voor de mannen en voor de vrouwen).

Bekijk ook het resultaat per afdeling op het tabblad Draai van het Voorbeeldbestand.

Aangezien de afdeling telkens aselect wordt toegekend, zal Vernieuwen iedere keer een ander resultaat opleveren.


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

Voetbalcompetities



Je hoeft niet per se van voetballen te houden om dit artikel te kunnen waarderen.
Ik kreeg van Nico van der Meer een mooi Excel-systeem toegestuurd, dat hij graag met anderen wil delen: het verwerken van voetbaluitslagen tot overzichten en standen, inclusief consequenties voor nacompetities, Champions League etcetera.

In eerste instantie dacht ik: deze sheet bevat wel veel formules, daar zullen we snel wat fouten of mogelijke problemen uit kunnen halen.
Maar niets was minder waar; dus ik geef de werkmap door met maar een paar kleine aanpassingen (verbeteringen?).

Met het systeem van Nico kun je de hele competitie 2018-2019 plezier hebben; alles is al voorbereid, inclusief speeldata en zelfs tijden!
Maar, zoals uit het plaatje hierboven mag blijken, zijn dit soort competities behoorlijk onderhevig aan wijzigingen, dus voor volgend jaar zul je wel weer flink wat aanpassingen moeten doen.

Hieronder zal ik de werking van het systeem kort toelichten en ook een handreiking geven over de aanpassingen voor een volgende competitie-ronde.
Uiteraard zal ik ook de meest gebruikte Excel-functies en de gebruikte VBA-routines toelichten.

Voetbal-competitie

Voordat er zo’n mooi overzicht ontstaat moet er natuurlijk wat voorwerk verricht worden.
In het tabblad Info van het Voorbeeldbestand heeft Nico al diverse aanwijzingen gegeven.

Het systeem omvat 3 competities, Ere-, Eerste – en Tweede Divisie (de EersteDivisie heeft nu de naam KeukenKampioen).
In het tabblad Data liggen de gegevens van de competitieronde en van de teams per divisie vast. Ook kan daar aangegeven worden of de beveiliging (ter voorkoming van het overschrijven van formules) wel of niet moet worden aangezet.

Het tabblad Speelschema bevat per divisie een overzicht van alle rondes en de wedstrijden die daarbinnen gespeeld moeten worden; dit alles inclusief de betreffende datum en tijd. Deze gegevens zijn op internet terug te vinden; via de hyperlink boven iedere divisie zijn de relevante zaken te benaderen.

Klik rechts op een Hyperlink en dan Hyperlink bewerken om te zien hoe deze opgebouwd is.

LET OP om een hyperlink te bekijken moet de beveiliging van het tabblad verwijderd zijn. Dit kan via rechtsklikken op de tab onderaan of door in het tabblad Data aan te geven dat alle beveiligingen verwijderd moeten worden.

Wilt u met dit systeem de resultaten van een competitie volgen dan moeten op dit tabblad Speelschema de uitslagen handmatig ingevoerd worden (voor de Eredivisie in de kolommen C en D).

Resultaten en stand Eredivisie

In het tabblad Eredivisie van het Voorbeeldbestand worden de ingevulde wedstrijdresultaten automatisch verwerkt.

In cel G6 wordt bijvoorbeeld het aantal doelpunten opgehaald voor de thuisclub van de wedstrijd PSV – AZ Alkmaar mbv de formule
=ALS(ISLEEG(VERT.ZOEKEN(TEKST($B6;)&” – ” & TEKST($G$5;);SchemaED;2;0)); “”;VERT.ZOEKEN(TEKST($B6;)&” – “&TEKST($G$5;);SchemaED;2;0))

Via Verticaal Zoeken wordt de wedstrijd (B6, dus PSV, tegen G5, dus AZ) opgezocht in het bereik met de naam SchemaED. De inhoud van de 2e kolom wordt als resultaat teruggegeven. De derde parameter (een 0, nul) geeft aan dat een exacte zoekactie naar de wedstrijdnaam moet worden uitgevoerd.
Als de zoekactie niets oplevert (er is nog niets ingevuld) dan komt er in dit schema ook niets te staan (de dubbele aanhalingstekens), anders wordt het resultaat van de zoekactie in de cel geplaatst.

NB1 de namen van de teams worden door een spatie, een min-teken en opnieuw een spatie van elkaar gescheiden. Bij de opvoer in Speelschema moet dit ook exact zo zijn gedaan.

NB2 Nico heeft de team-namen niet rechtstreeks aan elkaar gekoppeld, maar heeft de functie Tekst gebruikt. Waarschijnlijk om er zeker van te zijn dat Excel deze betreffende cellen zeker als tekst zal zien. Naar mijn idee overbodig.

De overige formules onder en naast het schema wijzen zichzelf.

Ook de standentabel wordt automatisch ingevuld.
Via Vergelijken wordt gekeken op welke positie het betreffende team in de vorige tabel staat en dan wordt via Index het gewenste aantal winst-punten opgehaald (uit en thuis opgeteld).

De formules in de overige kolommen zijn vergelijkbaar of ‘rechttoe-rechtaan’.

In kolom BW staat een code, die gebruikt wordt om de teams in de juiste volgorde te kunnen sorteren en de Plaats in de stand te bepalen.
Wanneer namelijk nieuwe resultaten worden toegevoegd in Speelschema, dan worden wel alle berekeningen direct uitgevoerd en ook de Plaats wordt bepaald, maar de volgorde in de standenlijst verandert niet. Wil je ook de volgorde juist hebben, klik dan op de knop Standen bijwerken. De betreffende VBA-routine (zie hierna) zal dan de sortering uitvoeren.

In cel BW6 staat de formule =WAARDE( TEKST(BS6;”000″)&TEKST(100+BT6;”000″)&TEKST(BU6;”000″)&TEKST(100-BN6;”00″)&TEKST(AANTAL.ALS($BM$6:$BM$23;”>=”&BM6);”00″))

Ook hier wordt de functie Tekst gebruikt; in dit geval om zeker te weten dat het betreffende getal altijd met exact hetzelfde aantal cijfers wordt weergegeven.
Met de functie Waarde wordt van de resulterende tekst weer een getal gemaakt.

Nico heeft in een opmerking bij Code netjes vermeld hoe de code is opgebouwd.

NB bij de Eredivisie zijn na 3 speelrondes al geen gelijke standen meer; bij de KK-divisie na 2 speelrondes nog wel.

In kolom BL wordt de plaats van het team in de stand bepaald: =RANG(BW6;$BW$6:$BW$23)
Met behulp van Voorwaardelijke opmaak krijgen bepaalde Plaatsen ook nog een kleurcode; dit is dus onafhankelijk van de sortering.

Overige divisies en nacompetitie

Bij de KK-divisie heeft Nico ook nog overzichten gemaakt ten behoeve van de bepaling van de periode-kampioenen. Zover ik nu heb kunnen zien heeft hij alle lastige afwijkingen en problemen daarmee uitstekend opgelost.

Wat de nacompetitie betreft: de maker van het systeem is nog niet helemaal zeker over de juistheid daarvan.

“De nacompetitie is voor mij nog experimenteel gezien de complexe regels.
Ik had niet verwacht, dat ik tot iets zou kunnen komen, maar al werkende daaraan ontstond er toch iets”.

Nieuwe competitieronde

Wilt u dit systeem ook een volgend seizoen gebruiken, dat moet er nogal wat aangepast worden:

  1. in het tabblad Data van het Voorbeeldbestand het seizoen aanpassen
  2. ook in Data de teams in de drie divisies aanpassen (hoeven niet per se gesorteerd te zijn)
  3. op het tabblad Data de teams van de Eredivisie selecteren en Ctrl-C (kopiëren) drukken; in het tabblad Eredivisie op de cel BM6 rechts klikken en Plakken speciaal/Waarden kiezen
  4. dezelfde werkwijze hanteren voor de teams van de eerste en tweede divisie
    LET OP bij de eerste divisie moeten de teams vijf keer geplakt worden.
  5. in het tabblad Speelschema alle uitslagen wissen en de wedstrijden die gespeeld zullen gaan worden overschrijven.
    LET OP zorg dat de namen van de clubs exact gelijk zijn aan die in het tabblad Data en dat de 2 ploegen gescheiden worden door een spatie, min-teken en nog een spatie

VBA

Met behulp van de knoppen Standen bijwerken worden bijbehorende VBA-routines uitgevoerd.
Voor de Eredivisie ziet die er als volgt uit:
Het bereik met de naam StandEredivisie wordt gesorteerd op kolom BW  en wel in Aflopende volgorde (grootste getal eerst) en het bereik heeft geen Kop.

Alle andere sorteringen gaan op dezelfde manier. Bij de KK-divisie worden 5 sorteringen achter elkaar uitgevoerd; dus voor alle schema’s van het betreffende tabblad.

In het Excel-object ThisWorkbook staat de volgende routine:

De For-Next-loop loopt alle tabbladen langs en afhankelijk van de inhoud van de cel met de naam BevJN (deze staat in het tabblad Data) wordt de beveiliging aan of uit gezet (zonder wachtwoord).

Application.ScreenUpdating zorgt er voor, dat het scherm niet gaat ‘flikkeren’ bij het selecteren van een tabblad.


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