Excellerend
Kwartaaltip 2013-3
Excellerend Heemraadweg 21 2741 NC Waddinxveen 06 – 5115 97 46
[email protected] BTW: NL0021459225 ABN/AMRO: NL72ABNA0536825491 KVK: 24389967
Praktische toepassing van functies De laatste twee functies uit de vorige kwartaaltip waren ASELECT en ASELECTTUSSEN. In deze tip wil ik op verzoek drie voorbeelden geven van toepassingen van deze functies. Het drie-deuren-probleem Iedereen die op TV wel eens naar een quiz heeft gekeken, weet dat het vaak eindigt in een drie-deuren-probleem:
Er zijn drie gesloten deuren. Achter één deur is een hoofdprijs verborgen, maar achter de andere twee deuren een troostprijs. De quizmaster laat de kandidaat een van de drie deuren kiezen. De quizmaster weet achter welke deur de hoofdprijs staat, maar om de spanning en daarmee kijkcijfers te verhogen opent hij een deur die de kandidaat niet gekozen heeft, en waarachter een troostprijs staat. Vervolgens krijgt de kandidaat de keuze: 1) Hij blijft bij zijn keuze en kiest niet alsnog de andere nog gesloten deur; 2) Hij verandert wel van keuze en kiest de andere nog gesloten deur. Op internet zijn enorme discussies op diverse wiskundige en filosofische fora te vinden over de beste optie voor de kandidaat. De één zegt dat hij bij zijn eerste keuze 33% kans had op Pagina 1 van 8
www.excellerend.nl
Excellerend
Kwartaaltip 2013-3
de juiste deur en dat wisselen geen zin heeft. Een ander zegt dat bij kiezen uit twee deuren de kans 50% is en het dus ook niet uitmaakt om te wisselen. Excel kan je helpen om de juiste keuze te beargumenteren. Laten we een modelletje opzetten om te kijken wat verstandig is. In cel A1 komt het nummer te staan van de deur waarachter de hoofdprijs staat en dat doen we met de functie =ASELECTTUSSEN(1;3). Deze functie levert altijd een geheel getal tussen het minimum 1 en het maximum 3, dus 1 of 2 of 3. In cel B1 komt het nummer van de deur te staan die de kandidaat kiest, met dezelfde functie. In cel C1 wordt de vergelijking gemaakt of B1 en A1 hetzelfde zijn: =ALS(A1=B1;1;0). Als de kandidaat de juiste deur heeft gekozen, komt er in C1 een 1 te staan, anders een 0. Door de functietoets F9 ingedrukt te houden kun je zien dat er vaker nullen dan enen komen:
Om te weten te komen hoe vaak er een 0 en een 1 komt te staan, kunnen we de functies doortrekken tot aan de onderste rij van het werkblad. We hebben dan meer dan een miljoen simulaties en dat geeft een aardig beeld van zijn kansen op de hoofdprijs wanneer hij niet verandert van keuze. In cel D1 zetten we dan de functie =GEMIDDELDE(C:C) en geven het de opmaak % mee met een paar decimalen. Het zal niemand verbazen dat die kans rond de 33 1/3 ligt:
Dit is vrij eenvoudig, maar welke situaties hebben we allemaal als de kandidaat besluit wel te veranderen van keuze?
Pagina 2 van 8
www.excellerend.nl
Excellerend
Kwartaaltip 2013-3
In een tabel ziet het er zo uit:
Er zijn 12 situaties te bedenken waarbij de kandidaat 6 keer de hoofdprijs binnenhaalt als hij wisselt van keuze. Kun je hiermee concluderen dat het niet uitmaakt of je nu wisselt of niet? Blijft de kans op een prijs 50% omdat er nog maar twee deuren gesloten zijn? Ik denk het niet. Laten we een model opzetten om ook hier weer een miljoen van deze situaties na te bootsen. De deur met de hoofdprijs en de deur die de kandidaat in eerste instantie kiest staan in cellen A2 en B2. De deur die de quizmaster opent om de spanning te verhogen is: 1) Nooit de deur die in cel A2 staat; 2) Nooit de deur die de kandidaat gekozen heeft (B2). De mogelijke situaties zijn:
De geel gearceerde cellen zijn situaties waarin de kandidaat gelijk de juiste deur kiest. De quizmaster kan dan kiezen uit twee deuren om te openen.
Pagina 3 van 8
www.excellerend.nl
Excellerend
Kwartaaltip 2013-3
In de andere situaties kunnen we de som van de twee deuren aftrekken van 6 om de enige deur te bepalen die de quizmaster kan openen. Voor de zes niet-gele situaties geldt: =ALS(A2<>B2;6-A2-B2;……..) Om de eerste gele situatie mee te nemen, breiden we de functie uit: =ALS(A2<>B2;6-A2-B2;ALS(A2=1;ASELECTTUSSEN(2;3);……………) In woorden: Als de hoofdprijs achter deur 1 zit en de kandidaat kiest deur 1, dan kan de quizmaster vrijelijk kiezen uit deuren 2 en 3 om te openen. De situatie voor deur drie is zo ook toe te voegen: =ALS(A2<>B2;6-A2-B2;ALS(A2=1;ASELECTTUSSEN(2;3);ALS(A2=3;ASELECTTUSSEN(1;2); De laatst overgebleven situatie is die waarbij achter deur 2 de hoofdprijs staat en de kandidaat kiest ook deze deur. De quizmaster moet dan kiezen tussen deur 1 en 3 om te openen. Ook dat is te bepalen met dezelfde functie. Zoals ik in de vorige kwartaaltip uitlegde, kun je berekeningen loslaten op de uitkomst van de functies ASELECT() en ASELECTTUSSEN(). We kiezen voor ASELECTTUSSEN(0;1). De uitkomst van deze functie is altijd een 0 of een 1. Dit vermenigvuldigen we met 2 en tellen er 1 bij op zodat de uitkomst altijd een 1 of 3 is: ASELECTTUSSEN(0;1)*2+1. De hele formule wordt dan: =ALS(A2<>B2;6-A2-B2;ALS(A2=1;ASELECTTUSSEN(2;3);ALS(A2=3;ASELECTTUSSEN(1;2); ASELECTTUSSEN(0;1)*2+1))) Vervolgens verandert de kandidaat van deur. We weten hiervan het volgende: 1) Het kan niet meer de deur zijn die de quizmaster geopend heeft; 2) Het is niet de deur die hij in eerste instantie geopend heeft, want hij wisselt. 3) We kunnen de optelling van de nummers van 6 aftrekken om zijn keuze te bepalen: =6-C2-B2 Tenslotte kunnen we in kolom E weer bepalen of de kandidaat de hoofdprijs wint of niet: =ALS(A2=D2;1;0) En in F1 bepalen we weer het gemiddelde over een miljoen simulaties door de formules te kopiëren en te plakken tot aan het einde van het werkblad:
Pagina 4 van 8
www.excellerend.nl
Excellerend
Kwartaaltip 2013-3
Wat we hiermee bewezen hebben, is dat de kandidaat er goed aan doet altijd te veranderen van keuze. Je hebt dan een kans van ongeveer twee derde op de hoofdprijs. Rekentafels van de lagere school Vorig jaar zat mijn dochter in groep 4 en daar leerden ze de tafels van 1 t/m 5 en de tafel van 10. Op een regenachtige middag wilde ze de tafels oefenen, maar het rekenblad van school had ze al af. Ik heb toen in Excel het volgende gemaakt. Een som van de tafel is altijd 1 t/m 10 maal de tafel, in dit geval 1, 2, 3, 4, 5 of 10. Het eerste getal is eenvoudig te bepalen via: =ASELECTTUSSEN(1;10) Met de ampersand en twee keer een spatie plakken we daar het maal-teken aan vast: =ASELECTTUSSEN(1;10)&” X “ Vervolgens moet ik at random kiezen tussen de eerste vijf getallen en het getal tien. Dat doe ik als volgt: =ALS(ASELECTTUSSEN(0;10)=10;10;ASELECTTUSSEN(1;5) Als de functie ASELECTTUSSEN() het getal 10 oplevert, zet dan een 10 neer, pak anders at random een getal tussen 1 en 5. En met de ampersand plak ik daar weer het =-teken achter, zodat de totale functie wordt: =ASELECTTUSSEN(1;10)&" x "&ALS(ASELECTTUSSEN(0;10)=10;10;ASELECTTUSSEN(1;5))&" =" In bovenstaande formule komt de tafel van tien dus maar in één op de tien keer voor, terwijl er uit zes tafels gekozen kan worden. Als ik kies voor: =ASELECTTUSSEN(1;10)&" x "&ALS(ASELECTTUSSEN(0;10)>5;10;ASELECTTUSSEN(1;5))&" =", dan komt de tafel van tien in de helft van de gevallen voor. Om deze tafel evenredig terug te laten komen, heb ik uiteindelijk gekozen voor: =ASELECTTUSSEN(1;10)&" x "&ALS(ASELECTTUSSEN(0;6)=6;10;ASELECTTUSSEN(1;5))&" =". In woorden, als de at random gekozen waarde tussen 1 en 6 een 6 oplevert, zet dan een 10 neer, anders een getal at random tussen 1 en 5.
Pagina 5 van 8
www.excellerend.nl
Excellerend
Kwartaaltip 2013-3
Bingo Een derde voorbeeld betreft de bingokaart. Een Bingokaart bestaat uit vijf kolommen met at random nummers uit de reeks 1 t/m 75. De middelste cel is doorgaans een plaatje, maar in de andere kolommen staan cijfers:
We weten van een bingokaart het volgende: 1) Dubbele cijfers komen niet voor; 2) De kolom met de B bevat cijfers uit de reeks 1 t/m 15; 3) De kolom met de I bevat cijfers uit de reeks 16 t/m 30; 4) De kolom met de N bevat cijfers uit de reeks 31 t/m 45; 5) De kolom met de G bevat cijfers uit de reeks 46 t/m 60; 6) De kolom met de O bevat cijfers uit de reeks 61 t/m 75. Voor kolom B kan dan gelden: =ASELECTTUSSEN(1;15) Maar de kans bestaat dat in de vijf cellen met deze functie een getal meer dan één keer voorkomt. Nu kun je het aantal unieke getallen op de kaart tellen met een functie en net zolang op F9 klikken (F9 betekent: bereken alle functies opnieuw) totdat je 24 unieke cijfers hebt, maar dat kan wel even duren. Dit aantal unieke cijfers kun je controleren met de functie: =SOM(N(INTERVAL(A2:E6;A2:E6)>0))
Je kunt tevens een macro schrijven die doorgaat totdat de cel met bovenstaande formule de waarde 24 heeft en dat gaat dan weer best snel. Maar ik heb altijd de voorkeur om een vraagstuk met formules en functies op te lossen in plaats van VBA te gaan gebruiken. Voor de liefhebbers:
Pagina 6 van 8
www.excellerend.nl
Excellerend
Kwartaaltip 2013-3
Sub Bingo() Do Until [I2].Value = 24 Calculate Loop End Sub
Om er ook met functies voor te zorgen dat ik altijd unieke getallen heb, maak ik een hulptabel met de cijfers 1 t/m 75 en naast deze cijfers plaats ik de functie =ASELECT(). Deze functie kiest een getal tussen 0 en 1 met maximaal 15 decimalen. Dat betekent dat er een getal gekozen wordt uit een biljard (een biljard is een miljoen maal een miljard).
De functie ASELECT combineer ik hier met INDEX en RANG. Deze functie kent twee varianten, waarvan we de eerste gebruiken. De functie INDEX indexeert een reeks getallen. Ze worden op volgorde gezet. De functie RANG bepaalt de rang van een getal in een reeks getallen. De syntax van de functie INDEX die we gebruiken is: =INDEX(matrix, rij_getal, [kolom_getal]) Het argument matrix bevat de cellen met de cijfers voor één kolom op de Bingokaart èn de aselecte getallen die ernaast staan. In bovenstaande tabel bestaat de matrix uit de gele cellen voor de eerste kolom met de B erboven. Het argument rijgetal gaan we bepalen met de functie RANG. We willen de rang weten van één van de getallen tussen 1 t/m 15. In dit geval staat de 1 in cel J9 en de 15 in cel J23 en de rang wordt bepaalt door het at random gekozen getal in het blauwe bereik I9:I23 RANG(J9;I9:I23) De functie voor de vijf getallen onder de B wordt dan: =INDEX($J$9:$J$23;RANG(I9;$I$9:$I$23)) Voor de I schuiven de bereiken twee kolommen op naar rechts: =INDEX($L$9:$L$23;RANG(K9;$K$9:$K$23)) Pagina 7 van 8
www.excellerend.nl
Excellerend
Kwartaaltip 2013-3
Voor de N: =INDEX($N$9:$N$23;RANG(M12;$M$9:$M$23)) Voor de G: =INDEX($P$9:$P$23;RANG(O9;$O$9:$O$23)) Voor de O: =INDEX($R$9:$R$23;RANG(Q9;$Q$9:$Q$23)) Ik hou er zelf niet van om voor ieder bereik de functie handmatig te moeten aanpassen, maar als ik deze functies ga uitbreiden met de functie INDIRECT, wordt het wat onleesbaar. Meer uitleg over de functie INDIRECT is op mijn site te vinden. De scherpe lezer zal nu zeggen dat er nog steeds een kans bestaat dat er niet 24 unieke getallen op de kaart staan. Maar die kans is uiterst miniem. Wil je toch met een functie controleren of er 24 verschillende getallen op de Bingokaart staan, dan kun je werken met de eerder genoemde functie: =SOM(N(INTERVAL(A2:E6;A2:E6)>0)) Einde kwartaaltip 2013-03
De kwartaaltip(s) zijn als PDF te downloaden via: www.excellerend.nl\kwartaaltips.html Wilt u een op maat gemaakte cursus Excel voor uw organisatie regelen? Zoekt u ondersteuning in het bouwen van rekenmodellen, controles, of denkt u dat iets mogelijk is in Excel maar u weet niet hoe? Neem dan contact op met Richard Meijles: 06 – 5115 9746 of via e-mail:
[email protected]. Wilt u geen kwartaaltips meer ontvangen? Klik dan op:
[email protected] Hebt u een verzoek voor een kwartaaltip? Klik dan op:
[email protected] en vermeld tevens uw Excelversie. Richard Meijles <><
Pagina 8 van 8
www.excellerend.nl
