Bijna iedereen kent wel het gezelschapsspel Yahtzee. In 3 worpen met 5 dobbelstenen moet je of zo hoog mogelijke aantal dezelfde ogen gooien van één soort of een combinatie zoals Three of a kind, Carre, Full House of kleine of grote straat. Uiteraard mag de Yahtzee daarbij niet ontbreken, dat is 5 maal dezelfde aantal ogen. Er zijn vele varianten van het spelregels.
Yahtzee scoreblok
In deze eerste tutorial in de Excel-lent serie gaan we een Yahtzee scoreblok maken. Dit Yahtzee scoreblok telt automatisch de score op, laat zien hoeveel speelbeurten je hebt gegooid en bij de bovenste helft toont het hoeveel punten je te kort komt. Uiteindelijk kun je het formulier resetten met behulp van knop gekoppeld aan een macro. Na het invullen van de laatste score wordt ook je totaal score van het hele scoreblok getoond.
Stap 1: Bereik speelbeurten vaststellen met functie AANTALARG()
Als u het bron bestand opent ziet u de basis van het scoreblok en een reset knop. In cel C2 gaan we vermelden hoeveel beurten we hebben gegooid. Dat doen we door middel van het aantallen cellen te tellen dat is ingevuld met een getal. We gebruiken hiervoor de formule =AANTALARG(waarde1;[waarde2];…). Met deze formule tellen binnen het bereik dat we opgeven het aantal cellen met een waarde. Als er in een cel binnen dit bereik een waarde is ingevuld telt hij deze als 1. Is er niets ingevuld dan is de waarde 0.
Waarde1 in de formule is verplicht. Je kunt meerdere waarden invullen gescheiden door een ; teken. Uiteindelijk sluit je af met een haakje sluiten ). Een bereik kun je ook definiëren door van cel A1 tot en met cel A20. Je hoeft niet alle cellen in te vullen, je gebruikt dan het : teken. In dit voorbeeld is dat =AANTALARG(A1:A20).
Wij gaan tellen in twee bereiken want we hebben een scorelijst boven en onder. Boven is vanaf cel D7 tot en met I12 en onder is van D16 tot en met I22. Deze twee bereiken moeten we scheiden door een ; teken. Onze formule zal worden: =AANTALARG(D7:I12;D16:I22).
Selecteer cel C2, kopieer deze formule in de formulebalk en druk enter.
Stap 1 is daarbij afgerond. Elke score die we invullen zal worden opgeteld en kunnen we direct zien hoeveel speelbeurten we hebben gehad. Bij de volgende stap gaan we de de scores bij elkaar optellen aan de van de functie SOM().
Stap 2: Optellen score met de functie SOM() en ALS().
In stap 2 gaan we alles scores bij elkaar op tellen. Hiervoor gebruiken we de functie =SOM(getal1;[getal2];…).
Om de formule direct te controleren is het handig om alvast een fictieve score in te vullen. Zet in de bovenste helft de maximale score die te behalen is achtereenvolgend 3, 6, 9, 12, 15 en 18. We hebben deze nodig om bonus te berekenen.
2.1 SOM() functie sub-totaal bovenste helft
De SOM() functie werkt hetzelfde als de AANTAL() functie. Tussen de haakjes zetten we het bereik van de getallen die opgeteld moeten worden gescheiden door een ; teken of een : teken voor een reeks. We gaan in cel D13 alle getallen optellen van cel D7 tot en met cel D12. De formule voor het sub-totaal is: =SOM(D7:D12).
Selecteer cel D13, kopieer =SOM(D7:D12) in de formulebalk en druk enter
2.2 ALS() functie om de bonus te bepalen
Om het totaal van de boven helft te bepalen moeten we eerst weten of we recht hebben op de bonus. Om de bonus te ontvangen moeten we 63 punten hebben of meer. Deze punten behaal je als je alle getallen minimaal 3 keer hebt gegooid. We moeten nu dus een vergelijking in cel D14 gaan maken die vergelijkt dat als we 63 punten of meer hebben de bonus ontvangen. Indien we niet aan die voorwaarde voldoen dan krijgen we 0 punten bonus.
Vergelijken doen we met de functie =ALS(). De formule die we gaan maken is:
Als de waarde in cel D13 63 of groter is dan moet er 35 weergegeven worden, anders 0.
De formule is: =ALS(D13>=63;35;0)
- De cel waar het om gaat is D13.
- De vergelijking is gelijk of groter dan >=
- het getal 63
- Achter de eerste ; teken de waarde als de vergelijking waar is
- Achter de tweede ; de waarde als de vergelijking niet waar is
Selecteer cel D14, kopieer =ALS(D13>=63;35;0) in de formulebalk en druk enter
2.3 SOM() functie totaal bovenste helft
Nu we het sub-totaal en de bonus punten hebben hebben bepaald kunnen de totaal score voor de bovenhelft bepalen. Dit is vrij gemakkelijk door de functie SOM() te gebruiken en de cellen D13 en D14 bij elkaar op te tellen. De formule voor het totaal is =SOM(D13;D14).
Selecteer cel D15, kopieer =SOM(D13;D14) in de formulebalk en druk enter
2.4 SOM() functie onderste helft
Om het totaal van de onderste helft op te tellen kunnen we weer de SOM() functie gebruiken. We gaan in cel D23 alle getallen optellen van cel D16 tot en met cel D22. De formule voor het sub-totaal is: =SOM(D16:D22).
Selecteer cel D22, kopieer =SOM(D16;D22) in de formulebalk en druk enter
2.5 D24 is hetzelfde als cel D15
Bij stap 2.3 hebben we al de totaal score van de bovenste helft berekend. We gaan deze score nu 1 op 1 overnemen. Dat doen we door in cel D24 de volgende formule te zetten: =D15.
Selecteer cel D24, kopieer =D15 in de formulebalk en druk enter
2.6 SOM() functie totaal score spel 1
Nu we alle scores hebben kunnen we de totaal score optellen van heel spel 1. De totaal score komt in cel D24. We gebruiken hiervoor wederom de SOM() functie. We tellen hiervoor cel D23 en D24 bij elkaar op. De formule voor de totaal score is: =SOM(D23:D24).
Selecteer cel D24, kopieer =SOM(D23:D24) in de formulebalk en druk enter
We hebben het optellen van de scores voor spel 1 voltooid. Hiervoor hebben we met name de SOM() functie en ALS() gebruikt. De laatste praktijk oefening van stap 2 is om deze formule’s toe te passen op spel 2 tot en met 6.
TIP: U kunt formule’s kopiëren door de cel met de formule te kopiëren naar de naastliggende cel. Selecteer cel D13 en kopieer deze (CTRL + C). Ga naar cel E13 en doe plakken (CTRL + V).
Als u alle stappen heeft herhaald voor spel 2 tot en met 6 kunt u door naar stap 3.
Stap 3: Voorwaardelijke opmaak bij bonus
Het behalen van de hoogste score is mede afhankelijk van of je de bonus hebt behaald. Om de bonus te behalen moet je van de bovenste helft alles minimaal drie keer gooien. 3 maal de enen, 3 maal de tweeën, 3 maal de drieën enzovoorts. Om je niet te vergissen bij het spelen bij gebruik van het hele scoreblok schrijf je boven het betreffende spel de + of de – score.
Bijvoorbeeld, in plaats van 9 punten bij de drieën heb je maar 6 punten gescoord. Dat betekent een min score van 3 punten. Je schrijft dan boven het betreffende spel -3. Je moet bij de 4, 5 of 6 minimaal 4 maal gelijke dobbelstenen gooien om de score weer in de + te krijgen. Gooi je vervolgens vier maal de 4, scoor je 16 punten. Dat is 4 punten meer dan nodig voor de vieren. Als je die in hetzelfde spel noteert heb je eerst een min score 3, tel daar 4 extra bij op en je totaal score is +1.
3.1 ALS() en ISLEEG() formule
Met behulp van het samenvoegen van formules gaan we de + of – score berekenen. We controleren eerst of de betreffende cel leeg is. Daarvoor gebruiken de we de formule ISLEEG(). Die gaan we uiteindelijk combineren met de formule ALS(). We willen namelijk dat als de cel nog leeg is dat er niets gebeurd. Laten we beginnen bij de enen van spel 1.
De +- score zetten we in cel D6. We gaan controleren of er iets is ingevuld. Dat doen we door de formule ISLEEG(). Tussen de haakjes zetten we de cel die we willen controleren. In ons geval is dat cel D7. De formule zal worden =ISLEEG(D7).
De volgende stap is om aan te geven wat te doen als er wel of niet een waarde is. Dat doen we met de ALS() functie welke we in stap 2.2 al hebben uitgelegd. Als de cel D7 leeg is dan is de waarde 0. Als er een waarde is dan dan trekken we de minimale score van de enen van die waarde af namelijk 3 punten. Bij de enen moeten we namelijk minimaal 3 punten scoren om de bonus te behalen. Scoren we 2 punten zal de som 2-3 worden en is de uitkomst -1. Bij een score van 4 punten is de som 4-3 en is de uitkomst +1. De formule zal zijn: =ALS(ISLEEG(D7);0;D7-3).
- Als cel D7 leeg is
- Achter de eerste ; teken de waarde 0
- Achter de tweede ; teken de som D7-3
We hebben nu een formule om te controleren hoeveel + of – score we hebben op de enen. Deze moeten we herhalen voor elk getal in de bovenste helft voor spel 1. Het enige wat we moeten aanpassen in de formule is de cel waar het om gaat en de te behalen score per getal. Hierbij de formule’s:
Enen: =ALS(ISLEEG(D7);0;D7-3)
Tweeën: =ALS(ISLEEG(D8);0;D8-6)
Drieën =ALS(ISLEEG(D9);0;D9-9)
Vieren =ALS(ISLEEG(D10);0;D10-12)
Vijfen =ALS(ISLEEG(D11);0;D11-15)
Zessen =ALS(ISLEEG(D12);0;D12-18)
Nu we alle formule’s van alle getallen hebben moeten we deze bij elkaar gaan optellen. De formule voor het optellen is simpel, we zetten ze allemaal achter elkaar zonder het = teken maar met een + teken.
ALS(ISLEEG(D7);0;D7-3)+ALS(ISLEEG(D8);0;D8-6)+ALS(ISLEEG(D9);0;D9-9)+ALS(ISLEEG(ALS(ISLEEG(D10);0;D10-12)+ALS(ISLEEG(D11);0;D11-15)+ALS(ISLEEG(D12);0;D12-18)
We gaan deze nu in de ALS() formule gieten. We willen namelijk dat als de som 0 is dat er niets wordt getoond en als er wel een waarde uit komt dat deze getoond wordt. Dus ALS( als som = 0 ; “” ; de som ; )
De uiteindelijke formule is:
=ALS(ALS(ISLEEG(D7);0;D7-3)+ALS(ISLEEG(D8);0;D8-6)+ALS(ISLEEG(D9);0;D9-9)+ALS(ISLEEG(D10);0;D10-12)+ALS(ISLEEG(D11);0;D11-15)+ALS(ISLEEG(D12);0;D12-18)=0;"";ALS(ISLEEG(D7);0;D7-3)+ALS(ISLEEG(D8);0;D8-6)+ALS(ISLEEG(D9);0;D9-9)+ALS(ISLEEG(D10);0;D10-12)+ALS(ISLEEG(D11);0;D11-15)+ALS(ISLEEG(D12);0;D12-18))
Herhaal deze stappen voor spel 2 tot en met 6 in cel E6 tot en met I6. U kunt hierbij de formule weer kopiëren net als in stap 2.6.
3.2 Voorwaardelijk opmaak
Om het formulier iets meer glans te geven gaan we een voorwaardelijk opmaak toevoegen aan de D6 tot en met I6. Met een voorwaardelijke opmaak kun je de opmaak van een cel aanpassen als er aan een vooraf ingestelde voorwaarde is voldaan. Het doel is om de achtergrondkleur en de tekstkleur aan te passen als de formule van stap 3.1 negatief is.
Selecteer cel D6 en klik op de knop Voorwaardelijke opmaak boven in het lint in het menu start. Klik op nieuwe regel. Selecteer de 2e regel “Alleen cellen opmaken met”. Onderin het scherm kun je nu de voorwaarde opgeven. We gaan alleen cellen opmaken met een cel waarde kleiner dan 0. Selecteer kleiner dan in de dropdown box en vul als waarde 0 in.
Voordat we op OK klikken gaan we eerste de opmaak aanpassen. Klik op de knop opmaak. Ga naar het tabblad Lettertype en zet de tekstkleur op wit.
Selecteer nu het tabblad Opvulling en klik op de kleur rood. Klik vervolgens op OK.
We hebben nu voor de cel D6 de voorwaardelijke opmaak bepaald. Probeer maar eens door bijvoorbeeld een score van 2 bij de enen te noteren. Er komt nu -1 te staan boven spel 1 in witte tekst met rode achtergrond.
Nu willen we echter de regel toepassen op de hele rij D6 tot en met I6. Selecteer cel D6 en klik op Voorwaardelijke opmaak in het menu in het lint en selecteer nu Regels beheren.
Het scherm met de zojuist gemaakte regel is hier zichtbaar. In het blokje “Van toepassing op” staat voor welke cel deze regel van toepassing is. Dit bereik gaan we aanpassen van =$D$6naar =$D$6:$I$6. Door dit bereik aan te passen zeggen we nu dat deze regel van toepassing is op cel D6 tot en met I6.
Stap 3 is daarmee klaar. We hebben uitgerekend hoeveel +- score we hebben en als we in de – komen zal de teller aangeven hoeveel en een rode achtergrond krijgen met witte letters. We kunnen nu door naar stap 4.
Stap 4: Yahtzee-bonus via gegevensvalidatie
Als je geluk hebt scoor je een Yahtzee-bonus! Voorwaarde is dat je alle Yahtzee’s hebt ingevuld zonder kruisjes, streepjes of 0. De Yahtzee-bonus bedraagt dan 100 punten voor elke volgende Yahtzee. In stap 4 gaan we de bonus Yahtzee invullen met behulp van een dropdown-lijstje.
4.1 Gegevensvalidatie
Het aantal Yahtzee-bonussen gaan we noteren in cel J21. Selecteer deze cel en ga boven in het lint naar het tabblad “Gegevens“. Klik op de knop “Gegevensvalidatie“. Selecteer onder toestaan de optie “Lijst“.
Bij 1 vinken we beide vinkjes aan. Bij 2 voeren we het bereik in van de bron lijst. We kunnen een bereik selecteren van een lijstje wat we in het excel bestand hebben of we kunnen een eigen bereik creëren. We gaan het laatste doen. Elk teken dat we in de lijst willen hebben scheiden we door het ; teken.
Wij willen alleen maar getallen in het bereik hebben van 0 tot en met 9. Je moet wel heel gelukkig zijn wil je meer dan 9 Yahtzee’s-bonussen gooien! Ons bereik zal worden: 0;1;2;3;4;5;6;7;8;9
We hebben nu een dropdown-lijstje met getallen 0 tot en met 9 waarmee we kunnen aangaven hoeveel Yahtzee-bonussen we hebben gegooid. Deze gaan we in de volgende stap gebruiken om de eindscore te berekenen van alle 6 de spellen.
In stap 5 gaan we de eindscore bepalen en wanneer deze zichtbaar wordt.
Stap 5: Yahtzee eindscore optellen na laatste worp
Om de spanning nog een beetje vast te houden tot het einde wordt pas na de laatste worp de eindscore zichtbaar. We gaan eerst controleren of alle score vakjes zijn ingevuld. Dan gaan we de scores bij elkaar optellen. Als laatste gaan we de Yahtzee-bonus aantal uit stap 6 vermenigvuldigen met 100 en er bij optellen.
5.1 AANTALARG() functie voor controle of we klaar zijn
Als je alle 6 de spellen speelt heb je in totaal 78 beurten (zonder Yahtzee-bonus). Het aantal scores gaan we tellen met behulp van de AANTALARG() functie. Deze is bijna gelijk aan die we in stap 1 ook hebben gebruikt om het aantal beurten te tellen. De formule was: =AANTALARG(D3:I8;D11:I17) en zal nu worden =ALS(AANTALARG(D3:I8;D11:I17). De reden dat we nu AANTALARG() gebruiken is omdat deze telt of er een waarde is ingevuld, ongeacht welke. Als we een score niet behalen zet je een kruisje, streepje of een 0. AANTALARG() telt die ook mee in het aantal beurten.
Nu gaan we deze functie combineren met de ALS() functie. We willen namelijk pas wat tonen als we alle beurten (78) hebben ingevuld. De formule hiervoor is =ALS(AANTALARG(D3:I8;D11:I17)=78;waarde1;waarde2)
Als we 78 beurten hebben gehad is zal waarde 1 worden getoond. Hebben we nog geen 78 beurten gehad zal waarde 2 worden getoond. We gaan bij waarde 1 de som van de totaal score plaatsen. De totaal scores per spel staan in de cellen D25 tot en met I25. De formule voor de som zal dan worden: SOM(D25:I25)
De totaal formule tot nu toe is dus: =ALS(AANTALARG(D3:I8;D11:I17)=78;SOM(D25:I25);waarde2)
Nu moeten we hierbij de het aantal Yahztee-bonussen bij optellen. Deze berekenen we door cel J21 te vermenigvuldigen met 100 en bij deze som op te tellen. De formule hiervoor is J21*100.
De totaal formule tot nu toe is dus: =ALS(AANTALARG(D3:I8;D11:I17)=78;SOM(D25:I25)+(J21*100);waarde2)
Nu moeten we alleen nog de waarde bepalen voor waarde 2. De waarde als we nog niet alle beurten hebben gegooid. Deze zetten we op 0.
De totaal formule is: =ALS(AANTALARG(D3:I8;D11:I17)=78;SOM(D25:I25)+(J21*100);0)
Selecteer cel H2, kopieer =ALS(AANTALARG(D3:I8;D11:I17)=78;SOM(D25:I25)+(J21*100);0) in de formulebalk en druk enter
5.2 Voorwaardelijke opmaak voor totaal score
De cel H2 met de formule is groen. In stap 3.2 hebben we al de voorwaardelijke opmaak gebruikt voor de +- score. Deze gaan we nu gebruiken voor de totaal score in cel H2. Selecteer cel H2 en in het lint onder “Start” op “voorwaardelijke opmaak“. Klik op nieuwe regel. Selecteer de 2e regel “Alleen cellen opmaken met“. Onderin het scherm kun je nu de voorwaarde opgeven. We gaan de cel alleen opmaken als de er een score is vermeld, dus als het getal groter is dan 0.
Klik op de knop “opmaak” om de opmaak van de cel te bepalen als aan de voorwaarde is voldaan. Ik heb gekozen voor een witte achtergrond en groen letters.
Het scoreblok is nu helemaal klaar voor gebruik. De score kan bijgehouden worden en de totaal score wordt zichtbaar aan het einde van het spel. In de laatste stap, stap 6 gaan we als laatste nog een knop toevoegen met een macro om het spel te resetten.
Stap 6: Het formulier resetten met een macro
Als we helemaal zijn uitgedobbeld kunnen we het formulier met behulp van een simpele druk op de knop resetten. Alle scores worden verwijderd en we kunnen opnieuw beginnen. Eerst moeten we de macro maken. Een macro is geschreven in VBA en dat is een programmeertaal die bepaalde handelingen automatiseert.
Wat wij willen gaan doen is een bereik bepalen en en binnen dat bereik moeten alle cellen worden gewist. De macro ziet er als volgt uit:
Sub reset()
Range("D7:I12,D16:J22").Select
Selection.ClearContents
Range("B5").Select
End Sub
We hebben hiermee een Sub gemaakt. Met een Sub kunnen we acties uitvoeren. De naam van de Sub is reset.
Op de tweede regel geven we aan om welk bereik het het gaat en dat we die willen selecteren. Dat doen we door Range( het bereik ).Select. Zie dat het bereik in het voorbeeld het bereik is van de Yahtzee score kaart.
Op de derde regel geven we uiteindelijk aan wat er moet gebeurd, het geselecteerde moet gewist worden (ClearContents).
Op de vierde regel geven we aan dat de cel B5 willen selecteren. Dat is de cel waar de naam staat, wat betekent dat je gelijk een nieuwe naam kunt invullen na de reset.
Met End Sub wordt de Sub beëindigd en geeft die aan dat hij klaar is.
6.1 VBA code toevoegen in Microsoft Visual Basic for Applications
Om Microsoft VBA te openen druk je op de toetsencombinatie ALT + F11. Dubbelklik vervolgens links in de mappen op Blad 1 (Yahtzee). In het witte venster onder Algemeen kun je dan de code in kopiëren.
Kopieer de VBA code in het veld Algemeen en sluit het venster.
Nu moeten we alleen nog de knop koppelen aan de macro. Rechtsklik op de knop en klik op Macro toewijzen.
Er opent een scherm met Macro toewijzen aan object. Selecteer de macro Blad1.reset en klik OK.
De macro is nu gekoppeld aan de knop. Als je nu op de knop klikt zal de inhoud van het formulier worden gewist.
Yahtzee scoreblok in Excel
We hebben nu een compleet bestand werkend bestand. Wat hebben we in het kort gedaan:
- Stap 1: Aantal beurten bereken
- Stap 2: Scores berekenen en bonus bepalen
- Stap 3: Berekenen +- score en voorwaardelijke opmaak
- Stap 4: Dropdown-lijstje voor Yahtzee-bonus
- Stap 5: Totaal score na laatste worp
- Stap 6: VBA gebruikt om formulier te resetten
