Tagarchief: Grootste

Voetbal en gegevensvalidatie

Excel, , , , , , ,

Hoewel de titel van dit artikel misschien anders doet vermoeden (het gaat dus NIET over het checken van persoonsbewijzen bij een voetbalwedstrijd), gaan we het hebben over het valideren van de invoer van gegevens in een cel in Excel.

De aanleiding hiervoor was een discussie op een internet-forum over “Lege velden in keuzelijst met invoervak“.
Al snel kwam daar de opmerking langs, dat het onderliggende probleem waarschijnlijk makkelijker en beter met gegevensvalidatie kon worden opgelost; dan was er ook geen VBA nodig.
Het probleem in het kort: de toegestane invoer in een cel is afhankelijk van de keuze, die in een andere cel is gemaakt.

voetbalcompetitieOm de daar geschetste oplossing in de praktijk te kunnen laten zien, heb ik de stand van de twee Nederlandse profvoetbalcompetities genomen (Bron: www.voetbaluitslagen.nl).
NB Het valt me nu pas op hoe goed de stad Eindhoven het doet!

Stand voetbalcompetities

Dus eerst maar eens een voorbeeld opzetten (zie Voorbeeldbestand):voetbalcompetitie

  1. eerst de standen van de twee competities onder elkaar gezet, gesorteerd op clubnaam, waarbij de kolom met de punten (Pnt.) voor ons het belangrijkste is
  2. dan een schema opzetten van de beste 3 per competitie (zie hierboven).
    Daarbij maak ik gebruik van de functie Grootste:
    =GROOTSTE(H15:H32;1)
    Dit levert het maximum op van de Eredivisiepunten; in het Voorbeeldbestand, tabblad CompOvz1, heb ik deze reeks een naam gegeven, ErePnt, zodat de bedoeling van de formule duidelijker is.
    Door de 1 te veranderen in 2 vinden we het één na hoogste puntentotaal enz.
    Via Index en Vergelijken (zie het artikel over Verticaal zoeken) vinden we de bijbehorende clubs.
    NB zoals uit de stand hierboven mag blijken gaat het bij een gelijk aantal punten niet altijd goed; er wordt geen rekening gehouden met doelsaldo.
  3. dan nog wat Voorwaardelijke opmaak ‘strooien’ over het overzicht (zie mijn vorige blog) en we krijgen wat beter inzicht in de voetbal-verhoudingen in Nederland.
    NB we zien nu wel dat AZ en Feyenoord een gelijk aantal punten hebben
  4. voetbalcompetitiede verhoudingen tussen de clubs kunnen ook grafisch worden weergegeven.
    Op de x-as staan de gegevens van de kolommen B en C; Excel zorgt zelf voor een duidelijke lay-out wat betreft de indeling van de twee competities.
    De titel is dynamisch, wat in dit geval inhoudt dat deze verandert wanneer de datum in cel B2 wordt gewijzigd:
    * maak een willekeurig titel aan,
    * kvoetbalcompetitielik ergens in de titel,
    * kies de formulebalk en tik een verwijzing naar de cel met de gewenste titel-tekst in, inclusief de naam van het tabblad en een !
    In het voorbeeld staat de titel-tekst in cel N27, waar met behulp van de functie Teken een scheiding tussen de twee elementen van de titel is gemaakt.

Combinatie van grafieken

De grafiek kan wel wat duidelijker: een betere scheiding tussen de twee competities, wie staan bovenaan, hoe staat mijn favoriete club er voor?voetbalcompetitie

  1. in het Voorbeeldbestand, op het tabblad CompOvz2, is het onderscheid tussen de competities geregeld door de behaalde punten in 2 verschillende kolommen te plaatsen, een extra kolomgrafiek toe te voegen, de overlap van de grafieken op 100% te zetten en de kleuren aan te passen
  2. daarna is er een label toegevoegd aan de nummers 1 tot en met 3: achter iedere club (in kolom F) staat een formule, die kijkt of de club bij de eerste 3 hoort:
    =ALS(D15=Ere_1;1;ALS(D15=Ere_2;2;ALS(D15=Ere_3;3;””)))
    Ere_1 is de naam van de cel, die het aantal punten van de aanvoerder van de ranglijst bevat etc.
    Het bereik F15:F52 is als een nieuwe grafiek toegevoegd; bij Opmaak is gekozen voor Geen opvulling maar wel zijn Gegevenslabels toegevoegd
  3. om onze favoriete club er te laten uitspringen, voegen we een nieuwe grafiek toe, die alleen de punten van deze club bevat; zie kolom G:
    =ALS(C15=Voorkeur;D15+E15;””)
    Geef deze grafiek een afwijkende kleur.

Gegevensvalidatie

In het vorige voorbeeld staat de naam van de favoriete club in cel J4, die de naam Voorkeur heeft gekregen.
De (groene) kolom in de grafiek bij de favoriet wordt natuurlijk alleen maar zichtbaar, wanneer die cel een bestaande clubnaam bevat. In Excel dwingen we dat af via Gegevensvalidatie.

  1. voetbalcompetitieselecteer cel J4 in het tabblad CompOvz2 van het Voorbeeldbestand
  2. kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. er opent zich een keuzescherm:
    voetbalcompetitie
  4. bij Toestaan kiezen we de optie Lijst
  5. en als Bron geven we het bereik op, waar alle clubnamen staan; in het voorbeeld heeft dat bereik de naam Teams.
    LET OP: denk aan het =-teken

voetbalcompetitieSelecteer J4 en tik een clubnaam in; komt deze niet (precies) in de clublijst voor dan krijgt u een foutmelding. Het is daarom handiger om het ‘vinkje’ achter de cel te gebruiken, zodat u de keuzelijst kunt gebruiken.

‘Meervoudige’ keuzelijst

In het vorige voorbeeld bestaat de lijst, waaruit een favoriete club gekozen kan worden, uit 38 teams.
Via scrollen in de zijbalk van de keuzelijst is de gewenste club nog vrij snel te vinden. Bij langere lijsten is dat vaak onhandig.

Als voorbeeld zou het in dit geval makkelijker zijn om eerst een competitie te kiezen (Eredivisie of Jupiler) en daarna pas een club uit de gekozen competitie.voetbalcompetitie
Dat is wat ik bedoel met ‘meervoudige’ keuzelijst: de inhoud van de tweede wordt bepaald door de keuze in de eerste.

Op de volgende manier is dit in te regelen (zie het Voorbeeldbestand, tabblad CompOvz3):

  1. voor de competitie-keuze voegen we weer een gegevensvalidatie toe (aan cel J3): kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  2. bij Toestaan kiezen we opnieuw de optie Lijst
  3. voetbalcompetitiebij Bron wordt meestal een celbereik van toegestane elementen opgegeven.
    In dit geval hebben we maar 2 mogelijkheden: Eredivisie of Jupiler. De namen van de betreffende clubs liggen vast in de celbereiken EreTeams, respectievelijk JupTeams. Deze 2 namen geven we als bron van de lijst op, gescheiden door een ; (punt-komma).
  4. aan cel J4 geven we weer een gegevensvalidatie.
    Bij Toestaan kiezen we weer Lijst; bij Bron zijn we geneigd om de cel J3 mee te geven, maar helaas: Excel zal dan alleen de letterlijke tekst uit die cel aan de keuzelijst meegeven (dus EreTeams of JupTeams).
    Nee, bij Bron moeten we invullen
    =INDIRECT(J3)
    De functie Indirect zorgt er voor, dat niet de inhoud van J3 zelf wordt gebruikt, maar de inhoud van het bereik waar J3 naar verwijst (EreTeams of JupTeams dus).

WK-voetbal voorspeller (deel 3, incl VBA)

Excel, VBA (Visual Basic for Applications), , , ,

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:

  1. 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.
  2. kopieer deze formule naar beneden tot en met cel B35.
  3. kolom C moet door de PC worden gevuld; in C36 tellen we wel alvast het aantal runs op: =SOM(C4:C35)
  4. in D4: =C4/$C$36; ofwel welk percentage van het totaal aantal runs komt ten gunste van dit land.
  5. 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.
VBA Monte CarloHier 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:

  1. kies in de menu-balk de optie Ontwikkelaars 
  2. dan Invoegen
  3. en klik op het eerste besturingselement: Knop
  4. ’teken’ met de cursor de omtrek voor de nieuwe knop
  5. wijs de macro MC toe aan het object
  6. 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.