Tagarchief: Random

Steekproeven

14 november 2014Access, ExcelAbs, Aselect, Random, RND, Steekproef, willekeurigGijs Verbruggen

Steekproeven worden bij veel werkzaamheden gebruikt; of het nu gaat om kwaliteitsonderzoek, control, accountancy: regelmatig is er behoefte om uit een voorraad gegevens een willekeurige steekproef te kunnen trekken.

Heb je een overzicht in Excel, dan is dat snel geregeld; voor een tabel in een Access-database is het wat ingewikkelder.

Excel

Als voorbeeld nemen we een ‘SAP-bestand’: 2000 regels met boekingen in 2014 op vijf verschillende rekeningen.
De accountant komt langs en wil een willekeurige steekproef van 10 regels hebben om te kunnen controleren.

Met de volgende stappen is dit zo geregeld:

kopieer het SAP-werkblad (de tab onderaan ‘vastpakken’ met de muis en, terwijl je de Ctrl-toets vasthoudt, het tabblad verslepen)
voeg in de kopie achter Bedrag een nieuw kopje toe: Aselect
tik in de eerste cel daaronder de formule: =ASELECT()
(denk aan de 2 haakjes op het einde; iedere functie in Excel dient vergezeld te gaan van een open- en sluit-haak).
Deze functie genereert een willekeurig getal tussen 0 en 1.
kopieer deze formule naar beneden, zodat alle regels een willekeurig getal krijgen toegewezen
plaats de cursor ergens in de Aselect-kolom en sorteer: kies in de menubalk Gegevens en in het blok Sorteren en filteren:
de steekproef staat klaar: de eerste 10 regels moeten worden gecontroleerd.

LET OP: nadat de sortering is uitgevoerd zal Excel alle cellen met de formule Aselect() opnieuw berekenen! Opnieuw sorteren levert dan weer een andere steekproef op.
Wil je dit voorkomen, dan moeten de willekeurige waarden ‘vast’ gezet worden vóór stap 5: selecteer alle cellen in kolom E, klik rechts, kies kopiëren en dan direct (dus op dezelfde plaats) weer rechts klikken en dan de 2e plakoptie kiezen (Waarden plakken).

LET OP2: er is een HEEEEL kleine kans, dat Aselect in deze serie één of meerdere keren precies hetzelfde getal genereert. Als dat ook nog net bij de 10e en 11e regel het geval is, klopt bovenstaande niet helemaal. Maar dat vergeten we maar even!

Access

We starten in Access met hetzelfde SAP-bestand (nog wel even ontZIPpen!!).

Een mogelijke oplossing (vergelijkbaar met Excel) is nu om deze tabel (of beter: een kopie daarvan) uit te breiden met een veld Aselect en dit veld voor ieder record te vullen met een willekeurig getal met behulp van een bijwerk-query.

Het kan gelukkig korter en zonder iets aan te passen in het origineel:

kies Maken/Queryontwerp
voeg de tabel SAP aan het ontwerp toe
sleep de drie velden naar het ontwerp:
voeg een nieuw veld toe achter Bedrag:
Aselect: Rnd()
LET OP: in Access worden Engelstalige functies gebruikt
kies linksboven de Gegevensbladweergave:
waarschijnlijk is de vierde kolom niet leesbaar: maak die breder.
Schrik: alle records hebben dezelfde willekeurige waarde!!!!

Waar Excel de functie Aselect() voor alle regels iedere keer opnieuw berekent, blijkt Access de Rnd()-functie één keer uit te rekenen en het resultaat in ieder record te gebruiken.

Dit is op te lossen door aan de functie Rnd() voor ieder record een (andere) parameter mee te geven. De meest eenvoudige oplossing is om tussen haakjes een NUMERIEK veld uit de betreffende tabel op te nemen.
Punt 4 wordt dan: Aselect: Rnd([Bedrag])

Nu krijgt ieder record een ander willekeurig getal mee en kunnen we, na sorteren op het veld Aselect, weer de eerste 10 records selecteren.

LET OP: ook hier zal NA de sortering de functie Rnd() opnieuw worden uitgevoerd en ziet de sortering er ‘vreemd’ uit.
Druk op F5 en Access zal opnieuw de sortering uitvoeren, andere records bovenaan zetten en dan weer alle Rnd()’s opnieuw uitrekenen.

Wanneer we in de query direct de sortering opgeven, dan hoeft dat niet meer handmatig te gebeuren:

Wanneer we, in combinatie met de sortering, ook nog de Top-eigenschap van de query vullen met de waarde 10 zijn we in één keer klaar:

klik rechts in het lege gebied naast de tabel en kies Eigenschappen
tik in het Eigenschappenvenster bij Top-waarden 10 in
kies linksboven de Gegevensbladweergave
het resultaat is, dat je 10 willekeurige records krijgt te zien.
Wil je andere records, druk op F5 en de query wordt vernieuwd.

De Rnd()-functie

De Rnd()-functie in Access (en ook in VBA) is uitgebreider in zijn mogelijkheden dan de tegenhanger, Aselect(), in Excel.
Om echt iedere keer andere, willekeurige getallen te krijgen, moet aan de functie Rnd() een positief getal meegegeven worden.

In het huidige voorbeeld (alle bedragen zijn positief) voldoet de hierboven genoemde functie (Rnd([Bedrag]) prima, maar meestal kun je daar niet vanuit gaan.

Beter is het dan ook om er zeker van te zijn dat er een positief getal als parameter wordt doorgegeven: Rnd(Abs([Bedrag])+1)
Dus:

zorg dat het bedrag groter dan (of gelijk is aan) nul: Abs zorgt er voor, dat een eventueel min-teken verdwijnt
tel er 1 bij op, zodat nul ook niet meer voorkomt
geef dit door aan Rnd en deze zal een nieuw, willekeurig getal opleveren!

LET OP: ook hier bestaat er een kleine kans, dat Rnd() een keer dezelfde getallen oplevert; als dit op de 10e en 11e plaats gebeurt, zal Access 11 records laten zien.

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.