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.