---

---

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.

---