Maandelijks archief: juni 2014

WK-voetbal voorspeller (deel 3, incl VBA)

22 juni 2014Excel, VBA (Visual Basic for Applications)Aselect, Grootste, Index, SOM.ALS, VergelijkenGijs Verbruggen

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.

WK-voetbal voorspeller (deel 2)

15 juni 2014ExcelAselect, Groeperen, Namen, Random, Verschuiving, ZoekenGijs Verbruggen

Spanje – Nederland: 1-5!

Wie had dat verwacht; niemand toch?
Mijn vrienden, met wie ik de wedstrijd samen bekeek, begonnen te twijfelen aan de WK-voetbal voorspeller van G-Info (zie mijn blog van 5 juni).
Ik kan alleen maar ter verdediging aanvoeren, dat in mijn vorige blog ook al stond aangegeven, dat de kwaliteit van ieder Excel-model afhangt van de input en van de gehanteerde systematiek. De systematiek hou ik nog even staande (hoewel er al mensen tips voor verbetering hebben aangeleverd); de input kan iedere gebruiker zelf aanpassen (ik heb een paar ‘kleine’ aanpassingen gedaan en zie hierboven het resultaat voor de verwachtingen voor Nederland!).

Maar ik had beloofd om deze week wat meer uitleg te geven over de opzet van het spreadsheet-model; dus voor diegene, die daar nu nog behoefte aan heeft ….

Werkblad Invoer

Hier valt weinig spannends te beleven; dit blad bevat 2 tabellen, die als basis dienen voor het vervolg.

Groepsindeling
Ik neem aan, dat de opbouw van deze tabel logisch genoeg is.
Zoals vorige keer al aangegeven, leggen we de sterkte van een land (qua voetbal!) vast via een ondergrens en een bovengrens.
Hoe kleiner het verschil tussen die twee grenzen, hoe stabieler de resultaten van het land zullen zijn en omgekeerd.

Het voorspeller-bestand is zodanig opgezet, dat deze voor een volgend toernooi opnieuw bruikbaar is. Dit alles onder de condities, dat er weer 8 groepen zijn met ieder 4 landen/ploegen en dat de 2 beste van de groepen verder gaan naar de achtste finales.
In dat geval hoeven alleen de namen van de deelnemende ploegen te worden gewijzigd in het werkblad Invoer. En uiteraard de grenzen voor de ploegen!

Groepschema

Met behulp van dit schema is vastgelegd in welke volgorde de groepswedstrijden worden gespeeld.
In dit geval speelt eerst ploeg 1 uit de groep tegen ploeg 2, dan 3 tegen 4, 1 tegen 3 etcetera.

Binnen dit werkblad zijn 4 namen voor groepen van cellen gedefinieerd:

Landen (cellen C4:C35)
LandKop (C3)
Sterkte (C4:E35)
GroepSchema (H3:I8)

In het vervolg van de spreadsheet wordt hier naar verwezen (meer over het gebruik van namen: zie het artikel van 31 mei 2014).

Werkblad Groepsfase

Nu begint het al wat leuker te worden! In dit blad zitten her en der wat aardige Excel-mogelijkheden ‘verstopt’.

Groeperen
Waarschijnlijk vallen de +-tekens aan de bovenkant en links meteen op. Hieraan is te zien, dat er (werk)-kolommen en -rijen zijn verborgen, zodat alleen de belangrijkste onderdelen van het overzicht getoond worden.

Klik op een plus-teken en je kunt zien wat er ‘ achter’ zit; klik dan weer op het min-teken en de zaak wordt weer verborgen.
LET OP: ik heb niet de optie Kolommen of Rijen verbergen gebruikt; daarbij valt het te weinig op, dat er iets verborgen is. Nee alles is uitgevoerd met behulp van de optie Groeperen: kies aaneengesloten rijen of kolommen, die je tijdelijk niet wilt zien, door de betreffende koppen te selecteren en ga naar de menu-optie Gegevens. In het onderdeel Overzicht zie je de optie Groeperen.
Maar makkelijker: heb je de kolommen of rijen geselecteerd, druk dan de toetscombinatie Shift-Alt-Rechts in. Degroeperen gaat op een vergelijkbare manier (Shift-ALt-Links). Met Rechts en Links bedoel ik de cursortoets naar rechts cq. links.

Verschuiving
Nu gaan we naar wat steviger kost; kijk eens naar cel F13:

Om deze spreadsheet nog vaker te kunnen gebruiken, moet het systeem zelf afleiden welk land op die plaats moet komen staan: het moet Land2 zijn (info in cel F1) in de 4e wedstrijd (cel C13) van de 2e groep (cel B13).

Uit het groepschema weten we, dat de 2e ploeg in de 4e wedstrijd in iedere groep het 2e land uit die groep is.
In Excel vinden we dat met de formule: INDEX(GroepSchema;WedNr;F1)
(zoek in Groepschema de rij op die overeenkomt met WedNr en daarbinnen de kolom, zoals vermeld in cel F1; in dit geval rij 4, kolom 2; voor meer informatie over zoeken via INDEX, zie ook het artikel Alternatief zoeken).
Ik maar hier gebruik van een heel handige eigenschap van het gebruik van Namen binnen Excel: WedNr is een reeks cellen, die loopt van C4 tot en met C51; wanneer je ergens in een cel daarnaast naar deze range verwijst via de naam (in dit voorbeeld vanuit cel F13), dan haalt Excel alleen de corresponderende cel in dezelfde rij op (dus hier C13, die de waarde 4 heeft), dus niet de hele reeks!

In het werkblad Invoer staan alle landen onder elkaar: eerst Groep A (de eerste groep), dan Groep B etc.
Om het land te vinden, dat in cel F13 moet komen, is het dus niet voldoende om te weten dat het het 2e land is maar moeten we ook nog weten in welke groep.
Het 2e land in de eerste groep staat in de landenkolom op de 2e plaats, het 2e land uit de 2e groep op 6, uit groep 3 op 10 etc.; telkens 4 verder dus.
Vandaar dat bij het hiervoor gevonden landnummer nog (GroepNr-1)*4 opgeteld moet worden om op de juiste plaats in de landenkolom terecht te komen (in dit geval gaat het om groep 2, dus komt er 4 bij en wordt het landnummer 6).

Met behulp van de functie INDEX zouden we nu in de reeks met de naam Landen kunnen zoeken, want we weten in welke rij we moeten zijn.
Ik heb deze keer voor een alternatieve manier gekozen: de functie VERSCHUIVING.

Met VERSCHUIVING geef je naast een verwijzing naar een bepaalde cel ook aan, dat je wat verder naar beneden (of naar boven!) en/of naar rechts of links wilt uitkomen.
In dit geval willen we dus vanuit de LandKop gerekend x rijen naar beneden en 0 kolommen naar links of rechts zoeken, ofwel
=VERSCHUIVING (LandKop; x; 0)

Maar de x kenden we ook al; die hebben we hiervoor afgeleid, dus de functie die we nodig hebben is:

=VERSCHUIVING(LandKop;(GroepNr-1)*4+INDEX(GroepSchema;WedNr;E$1);0)

Nu we deze formule eenmaal hebben, kan die ook naar de overige cellen in E en F gekopieerd worden.

Wie wint een wedstrijd?
In de kolommen G en H staan de grenzen voor het eerste land uit de corresponderende rij (opgezocht m.b.v. INDEX en VERGELIJKEN; zie het artikel Alternatief zoeken), in de kolommen J en K idem voor het tweede land.

Als we kijken naar cel I4, dan zien we de formule =ASELECTTUSSEN(G4;H4).
In dit geval wordt er voor Brazilië willekeurig een getal gekozen tussen zijn onder- en bovengrens (deze keer leverde dat 40 op; druk je op de functietoets F9 dan zal er hoogstwaarschijnlijk een ander getal komen).

In de kolommen M en N verdelen we de punten per wedstrijd via een dubbele ALS-formule; in cel M4 is dat =ALS(I4>L4;3;ALS(I4=L4;1;0)).
Als de sterkte van Brazilië groter is dan die van Kroatië krijgen ze 3 punten, als de sterktes gelijk zijn dan 1 punt, anders 0 punten.

De volgende keer ga ik verder met de uitleg van de overige gebruikte functies en de VBA.

Voortschrijdend inzicht

Nu de eerste wedstrijden zijn gespeeld, is er misschien iets meer duidelijkheid gekomen over de sterkte van de landen. In ieder geval kennen we al wat uitslagen en kent het model dus iets minder onzekerheden.

Wat kun je nu doen:

download eventueel nog een keer de WK-voetbal voorspeller
als je wilt kun je op het werkblad Invoer nog wat sterktes aanpassen (Nederland toch maar gelijk maken aan Brazilië??, de sterkte van Spanje flink verlagen??)
vul op het werkblad Groepsfase de bekende uitslagen in:
Brazilië – Kroatië: 3-1; in cel I4 komt een 3 en in cel L4 een 1
Spanje – Nederland: 1-5; in cel I10 een 1 en in cel L10 een 5
vul ook de andere uitslagen in
wis op het werkblad MC de vorige run(s) en laat Monte Carlo ongeveer 500 keer zijn werk doen; dat is voldoende om een goed beeld te krijgen van de mogelijke resultaten (volgens het model is de kans, dat Nederland wereldkampioen wordt door de uitslagen tot nu toe, verdubbeld!).
sla het spreadsheet onder een andere naam op: WK2014 Uitslagen.xlsm

WK-voetbal voorspeller

5 juni 2014Excel, VBA (Visual Basic for Applications)Aselect, RandomGijs Verbruggen

Het is bijna zover: Nederland wordt wereldkampioen voetbal 2014! Of toch niet?

Heel (?) Nederland is in de ban van Koning Voetbal en menigeen wil zijn kennis van het spelletje te gelde maken door een gokje te wagen in één van de vele poules die georganiseerd worden.

Maar niet voor iedereen is zo’n poule invullen een fluitje van een cent; daarom geeft G-Info je deze keer een handig hulpmiddel cadeau: je hoeft alleen maar op te geven hoe jij de sterkte van de diverse landen inschat en de WK-voorspeller laat zien wat de uitslagen van de diverse wedstrijden zullen zijn en dus ook wie er wereldkampioen wordt!

WK-voetbal en Monte Carlo

Je zou zeggen: deze twee hebben weinig met elkaar te maken. Qua voetbal klopt dat misschien, maar als het op gokken aankomt….

In de bijgaande WK-voorspeller gebruik ik namelijk de zogenaamde Monte Carlo-methode om met behulp van Excel voorspellingen te doen.
In het kort komt het er op neer, dat we Excel per wedstrijd per land willekeurig (random, aselect) een sterkte laten kiezen tussen een onder- en bovengrens. Die twee grenzen moet je zelf opgeven; ik ben benieuwd wat voetbalkenners van mijn poging vinden.
Per wedstrijd wordt de sterkte van de 2 landen vergeleken en de sterkste van de 2 krijgt de 3 punten (even sterk, dan ieder 1 punt).
Laat je één run uitvoeren, dan heb je het resultaat van een heleboel toevalligheden bij elkaar; laat je je PC echter 100 runs draaien (of 1.000 of 10.000!), dan kun je turven hoe vaak Nederland kampioen wordt (of zullen we toch maar Brazilië nemen).
Dat aantal, gedeeld door het aantal runs, geeft de kans van het land op het wereldkampioenschap.
Het is een soort veredeld dobbelsteen gooien, een gokspel, vandaar de naam Monte Carlo.

Ik kan me voorstellen, dat er nu mensen zijn, die zeggen, dat dit wel een heel simpele benadering van het complexe voetbalspel is.
En helaas moet ik dat meteen toegeven, maar als verdediging kan ik aanvoeren, dat het alleen bedoeld is om weer eens een alternatief gebruik van Excel toe te lichten.

Werking programma

Wat moet je doen voordat je je poule gaat invullen?

uiteraard eerst de Excel-sheet downloaden en in Excel openen.
vul op het werkblad Invoer bij alle landen een onder- en bovengrens voor hun sterkte in.
LET OP: als de bovengrens bij een land kleiner is dan de ondergrens van een ander land, dan kan het eerste land dus nooit winnen van het tweede.
ga naar het werkblad Groepsfase en druk op F9 (Excel gaat dan alles opnieuw doorrekenen, inclusief alle willekeurig gekozen getallen) en ziedaar de resultaten van alle groepswedstrijden en dus ook wie er naar de achtste-finales mogen.
Bevalt een uitslag niet: druk opnieuw op F9! Excel zal een nieuwe reeks doorrekenen; doe dat 100 keer en kijk in Groep B hoe vaak Nederland verder gaat (dit aantal gedeeld door 100 is de kans op overleven).
Het is natuurlijk nauwkeuriger als je dit 1.000 of 10.000 keer doet ….
op het werkblad Finales zie je hoe het verder gaat met de eerste en tweede per groep en wie uiteindelijk wereldkampioen zal worden.

Als je al had gedacht , dat je met dit programma goud in handen had gekregen; je zult ondertussen de betrekkelijkheid van onze ‘voorspeller’ hebben doorgrond.
De kwaliteit van zo’n Excel-model zit in de betrouwbaarheid van de aannames (in dit geval de grenzen voor de sterkte per land) en in het benaderen van de werkelijkheid met Excel-formules (het winnen van een wedstrijd hangt niet alleen af van een toevallige steekproef van de sterktes van de 2 partijen).
Toch kan Excel op deze manier, zeker wanneer de aannames beter worden onderbouwd en de formules beter de wekelijkheid nabootsen, een belangrijk hulpmiddel zijn om zogenaamde stochastische processen na te bootsen en met een zekere nauwkeurigheid resultaten te voorspellen.

Monte Carlo-runs

Het ontbreekt me nu aan tijd om het programma verder toe te lichten (even voetbal kijken: Nederland- Wales), maar ik beloof dat ik in mijn volgende blog de wat complexere (en dus leukere!) zaken nog eens de revue zal laten passeren.

Als troost heb ik nog een werkblad MC toegevoegd: hiermee kun je een serie Monte Carlo-runs achter elkaar draaien en kijken welk land de grootste kans heeft om wereldkampioen te worden.

Klik op de button Monte Carlo draaien en bekijk het resultaat.
Je kunt ook het aantal runs per serie aanpassen (dus is het toch simpel om 10.000 runs uit te voeren).
Wil je opnieuw beginnen: druk op de button Vorige run(s) wissen.