Lottrekking met gewichten
- 25/09/2020
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
In het vorige artikel zagen we hoe je op een eenvoudige manier een lottrekking in Excel kan organiseren. Gedaan met papiertjes en de obligate onschuldige kinderhand. In dit artikel gaan we een stap verder door ook met gewichten te gaan werken. Misschien heb je niet elke optie even graag ? En als je dit visueel voorstelt, zou je denken aan het volgende: elke optie schrijven we op 1 papiertjes, maar voor sommige opties maken we extra papiertjes aan en schrijven er telkens de naam van de optie op. Zo kan het dus zijn dat de meeste opties gewicht 1 hebben, maar andere opties misschien gewicht 2 of 3 of ... Die opties hebben dus een hogere kans om getrokken te worden en zullen vaker in de resultaten van de lottrekking naar boven komen dan opties met gewicht 1. Hoe passen we onze formules aan om dit mee te nemen in de lottrekking ?
Het is nog steeds weekend en we zitten nog steeds met een hongertje. Maar ergens zouden we toch liever een voorkeur hebben voor frietjes, Chinees of paëlla, eerder dan de andere opties. Misschien omdat die 3 opties dichterbij zijn en we sneller eten zullen hebben.
Dit zijn mijn stappen voor de 6 formules:
- maak een dynamisch benoemd bereik aan met de naam "nummers": =VERSCHUIVING(Lottrekking!$B$6;;;AANTAL(Lottrekking!$B:$B))
- we berekenen het relatieve aandeel van elke optie in het totaal: =nummers / SOM( nummers ). Dit wordt nu wel heel interessant, aangezien deze formule gebruik maakt van dynamische matrixfuncties. Je zet de formule in cel L6 en na Enter drukken worden ineens de cellen onder L6 ook gevuld ! Excel kijkt naar de lengte van het bereik "nummers" en past die lengte toe op onze nieuwe formule: het bereik wordt even lang !
- in cel M6 zetten we de moeilijkste formule van dit bestand: =SUBTOTAAL( 9; VERSCHUIVING( L6#; 0; 0; REEKS( AANTAL( nummers )))). Dit behoeft enige uitleg. L6# is de notatie om aan te geven dat we in L6 willen beginnen en doorgaan tot de laatste rij - in dit geval rij 13. In kolom M is alweer een lijstje van formule resultaten te zien, even lang als de nummers in kolom B. De SUBTOTAAL functie met argument 9 geeft ons sommen en doet dat met inachtname van de dynamische matrixfuncties. De REEKS functie is ook gloednieuw in Excel ! Het doel van M6 en onderliggende cellen is de cumulatieve waarschijnlijkheden/kansen optellen, die liggen tussen 0 en 1 en zijn niet-dalend.
- vervolgens hebben we een willekeurig getal nodig tussen 0 en 1, om daarna op te zoeken in kolom M: =ASELECT()
- nu we ons getal verkregen hebben, vragen we de winnende optie op: =ALS.FOUT( X.ZOEKEN(H10; M6#; VERSCHUIVING( nummers; ; 1); NB();1; 1 ); "" ). De X.ZOEKEN functie is ook nieuw in Excel en een ware aanrader !
- als bonus zetten we in cel I13 nog het nummer van de optie: =ALS.FOUT( X.VERGELIJKEN( H10; M6#; 1; 1 ); "" ). Dit laat mij toe om de eveneens nieuwe functie X.VERGELIJKEN te tonen in een praktisch voorbeeld !
Misschien gebruikte ik hier enkele functies die jij nog niet kende, zoals VERSCHUIVING, AANTALARG of ASELECTTUSSEN ? Bekijk dan zeker even het voorbeeldbestandje dat je bovenaan deze pagina kan downloaden.
Zo, dat was al een eerste stap richting een rijkere toepassing. VBA komt ook nog aan bod in een volgend artikel.
