Nog gauw even verder met de uitleg van het model uit de blog van 5 juni voordat het WK voorbij is!
Voor diegene die onderstaande uitleg willen volgen en het model niet (meer) paraat hebben: WK-voetbal voorspeller.
Werkblad Groepsfase
Vorige keer heb ik laten zien hoe we tot de puntenverdeling in de groepsfase komen.
We kijken nu verder naar de consequenties daarvan voor het vervolg van het toernooi.
Per land in kolom P (simpele verwijzingen naar kolom E en F) wordt gekeken hoeveel punten ze volgens het model hebben verdiend in de groepswedstrijden: in bijvoorbeeld cel Q4 moet het aantal punten komen, dat het land uit P4 heeft verzameld. Maar aangezien een land ‘uit en thuis’ kan spelen kunnen de verdiende punten in kolom M en in kolom N staan.
Om de relevante punten uit kolom M op te tellen kent Excel een mooie functie SOM.ALS. De functie kent de volgende vorm: =SOM.ALS(Bereik1;Voorwaarde;Bereik2)
Wat betekent: als een cel uit het Bereik1 gelijk is aan Voorwaarde, tel dan de overeenkomstige cel uit Bereik2 bij het totaal op.
In dit geval: =SOM.ALS($E$4:$E$51;P4;$M$4:$M$51)
Door de verwijzingen van Bereik1 en Bereik2 ‘absoluut’ te maken (dus met $-tekens voor de kolom-letter en het rij-nummer) en de Voorwaarde ‘relatief’ kan deze formule eenvoudig zonder aanpassingen naar beneden gekopieerd worden.
PS laat me weten als er behoefte is aan uitleg over het verschil tussen absolute en relatieve verwijzingen.
Maar ook zijn er punten te verdienen als ‘uitspelende’ partij, dus ook de punten uit kolom N moeten worden geteld.
De formule in Q4 wordt:
=SOM.ALS($E$4:$E$51;P4;$M$4:$M$51) + SOM.ALS($F$4:$F$51;P4;$N$4:$N$51)
Wat te doen als nummer 2 en 3 evenveel punten heeft; wie gaat dan door naar de achtste-finales?
Aangezien het hier maar een simpel model betreft en we niet hebben gemodelleerd hoeveel doelpunten ieder land maakt, moeten we een beetje truken: niet alleen krijgt een land de betreffende punten uit kolom M en N, maar we geven hem ook nog een willekeurig heel klein getal extra: + ASELECT()/10000.
Aangezien de functie ASELECT() een willekeurige getal tussen 0 en 1 genereert, krijgt een land dus een getal tussen 0 en 1/10.000 extra. Net genoeg om bij een ‘gelijke stand’ toch een verschil te hebben.
Volgorde binnen de groep
Nu we de resultaten van de groepen hebben opgezocht, moeten we nog bepalen wie de nummers 1 en 2 (en ook 3 en 4) zijn geworden.
In kolom U gaan we van iedere groep eerst het hoogst aantal punten ophalen met behulp van de functie =GROOTSTE(Bereik;Volgnummer); oftewel zoek in Bereik (gesorteerd van grootste naar kleinste) naar de met Volgnummer corresponderende waarde. Dus als Volgnummer 1 is, dan krijg je als resultaat de hoogste waarde; is het 2, dan de een na hoogste enzovoort.
Nu we de volgorde van punten per groep hebben gevonden, zoeken we in kolom T het bijbehorende land op met behulp van de functies INDEX en VERGELIJKEN (zie voor een verder uitleg hiervan het artikel Alternatief voor Vert.Zoeken).
Om in het volgende werkblad makkelijk verwijzingen naar de nummers 1 en 2 per groep te kunnen maken, geven we die cellen een naam (zie het artikel Namen ipv celverwijzingen): cel T4 krijgt de naam GA_1 (afkorting voor Groep A, nr 1), cel T5 de naam GA_2 (Groep A, nummer 2), T10 wordt GB_1 etc.
LET OP: je kunt NIET de naam GA1 gebruiken, omdat dit al een echte celverwijzing is; vandaar de _ (underscore).
Werkblad Finales
In kolom B nemen we de landen, die als eerste en tweede in een groep zijn geëindigd over. Eerst de winnaar van Groep A (in cel B3 tikken we in: =GA_1; een naamverwijzing dus), dan de 2e van Groep B (die spelen tegen elkaar in de achtste finales), winnaar Groep C, 2e van Groep D etc.
De rest van dit werkblad zou ondertussen voor zichzelf moeten spreken: we zoeken de sterktes van de landen weer op (kolommen C en D), bepalen aselect wie dan wint (kolom E) en zetten de winnaar in kolom F. Deze winnaars geven we weer een naam (AF_1, AF_2 etc. AF betekent dan Achtste Finale).
Het stramien is bij de kwart-, halve- en echte finale hetzelfde. Op die manier komen we uiteindelijk in cel Y18, de Winnaar.
Werkblad MC
Op de vorige werkbladen wordt iedere keer één Monte Carlo-run weergegeven: bij iedere wedstrijd wordt een ‘dobbelsteen’ gegooid en gekeken wie er wint. Al dit gedobbel levert dan uiteindelijk een winnaar op (meestal Brazilië, omdat we die bij Invoer het sterkste hebben ingeschat).
Door op F9 te drukken (of ergens iets te wijzigen op een werkblad) gaat Excel opnieuw met de dobbelstenen gooien en komen er overal andere resultaten tevoorschijn.
Door dit 100 keer te doen en te turven hoe vaak bijvoorbeeld Nederland winnaar wordt, weten we hoe groot de kans is dat Nederland Wereldkampioen wordt (op basis van de aannames in het werkblad Invoer en het gehanteerde model!!!).
Door het “drukken op F9” te automatiseren is het bepalen van deze kansen een stuk eenvoudiger geworden.
Allereerst maken we een tabel, waarin we kunnen turven hoe vaak een land wint:
- tik in cel B4 in: =Landen
Dit is een verwijzing naar een reeks cellen in het werkblad Invoer; Excel neemt dan de waarde over die in die reeks in dezelfde rij staat als waar nu de verwijzing wordt getypt. - kopieer deze formule naar beneden tot en met cel B35.
- kolom C moet door de PC worden gevuld; in C36 tellen we wel alvast het aantal runs op: =SOM(C4:C35)
- in D4: =C4/$C$36; ofwel welk percentage van het totaal aantal runs komt ten gunste van dit land.
- de kop boven de landen (cel B3) geven we ook een naam (LandWin); die kunnen we straks bij het turven goed gebruiken.
De grafiek is een weergave van deze tabel.
Nu nog even een klein programmaatje schrijven om de PC te laten ‘dobbelen’ en we zijn klaar!
Dat programma moet natuurlijk wel weten hoe vaak er een run gedraaid moet worden; dat zetten we in cel I5, die we de naam AantalRuns geven.
VBA-programma
Uitleg over gebruik van de VBA-editor kun je vinden in de blog van 5 mei 2014.
Hier zullen we volstaan met een korte uitleg van wat het programma doet:
1: definieer een programma (subroutine) met de naam MC
2, 3, 4: definieer variabelen, die we dadelijk gaan gebruiken
6: de programmavariabele iAantRuns krijgt de waarde van de Excel-cel met de naam AantalRuns
8: het tellertje i laten we lopen van 1 tot en met iAantRuns
9: we gaan dadelijk zoeken of we de winnaar ergens in ons lijstje tegenkomen (zou gek zijn als het niet zo was!!); we beginnen met zoeken in de regel 1, daarom geven we de variabele iRegel die waarde alvast
10: we hebben het land nog niet gevonden, dat leggen we ook even vast
12: wie is eigenlijk de winnaar van de run die net heeft gedraaid? Die staat in de Excel-cel met de naam Winnaar en dat stoppen we in de variabele strWin
13: zolang nog niet gevonden EN de cel iRegel’s onder de kop LandWin (Offset = Verschuiving) niet leeg is
14: als in die cel de winnaar van deze run staat dan
15: tel bij de waarde in de cel iRegel’s onder de kop en 1 kolom naar rechts (dus in kolom C) 1 op
16: gevonden!!!
17: een screenupdate binnen de applicatie zorgt er voor dat Excel alles doorrekent; in dit geval opnieuw gaat dobbelen bij alle wedstrijden
19: als we misschien nog verder moeten gaan zoeken, waar de winnaar staat, dan wel natuurlijk 1 regel lager
20: ga weer naar 13; als daar niet aan de voorwaarde wordt voldaan gaat het programma verder met 21
21: Next hoort bij de For van regel 8, volgende run dus
23: als het aantal runs doorlopen is, stopt het programma
Dit programma kun je laten runnen door op Alt-F8 te drukken, de macro MC te selecteren en OK te klikken, maar handiger is het om die met behulp van een button/knop te starten.
Zo’n knop maak je als volgt:
- kies in de menu-balk de optie Ontwikkelaars
- dan Invoegen
- en klik op het eerste besturingselement: Knop
- ’teken’ met de cursor de omtrek voor de nieuwe knop
- wijs de macro MC toe aan het object
- en verander de tekst op de knop
Om ook de resultaten van één of meerdere runs te wissen, heb ik een tweede macro gemaakt (MC_Opnieuw) en die aan een knop toegewezen.
De werking van de macro zou met bovenstaande toelichting duidelijk moeten zijn.