Combinatie
Aselecte steekproef van journaalregels met NEMEN, SORTEREN.OP en ASELECT.MATRIX
Trek n journaalregels aselect, nooit twee keer dezelfde regel: ASELECT.MATRIX geeft elke regel een willekeurige sleutel, SORTEREN.OP zet de regels in die volgorde en NEMEN houdt de eerste n over.
Gebruikte functies:NEMENSORTEREN.OPASELECT.MATRIXRIJENExcel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
Uitgewerkt voorbeeld
4 journaalregels voor detailcontrole selecteren uit een populatie van 12
Situatie
Voor de journaalpostcontrole van Veldhuis Installatie BV heb je 12 handmatige journaalregels over 2025 uit de XAF-auditfile. Je besluit n = 4 regels aselect na te lopen naar de onderliggende stukken en de autorisatie. Niemand mag de selectie selectief kunnen noemen en dezelfde regel mag niet twee keer worden getrokken.
De lijst hier telt 12 regels zodat hij in het raster past; in de praktijk is de populatie duizenden regels en dezelfde formule werkt ongewijzigd. De uitkomst is willekeurig: de getoonde regels zijn één illustratieve trekking, op jouw werkblad staan andere regels.
De gegevens
| Rij | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Regelnr. | Datum | Omschrijving | Bedrag | Steekproefomvang (n) | 44 | ||
| 2 | 25-5101 | 17-01-2025 | Omzet installatie | €14,250.00€ 14.250,00 | Geselecteerde regels | 25-5111=NEMEN(SORTEREN.OP(A2:A13, ASELECT.MATRIX(RIJEN(A2:A13))), G1)=NEMEN(SORTEREN.OP(A2:A13; ASELECT.MATRIX(RIJEN(A2:A13))); G1) | ||
| 3 | 25-5102 | 28-02-2025 | Overlopende passiva | -€6,800.00€ -6.800,00 | 25-5103 | |||
| 4 | 25-5103 | 31-03-2025 | Afschrijving | €3,920.00€ 3.920,00 | 25-5108 | |||
| 5 | 25-5104 | 09-05-2025 | Voorziening dubieuze debiteuren | €5,400.00€ 5.400,00 | 25-5105 | |||
| 6 | 25-5105 | 30-06-2025 | Vooruitbetaalde verzekering | -€2,150.00€ -2.150,00 | ||||
| 7 | 25-5106 | 18-07-2025 | Herclassificatie huur | €8,300.00€ 8.300,00 | ||||
| 8 | 25-5107 | 29-08-2025 | Bonusreservering | €12,600.00€ 12.600,00 | ||||
| 9 | 25-5108 | 30-09-2025 | Voorraadcorrectie | -€4,750.00€ -4.750,00 | ||||
| 10 | 25-5109 | 15-10-2025 | Correctie btw | €1,875.00€ 1.875,00 | ||||
| 11 | 25-5110 | 28-11-2025 | Herwaardering apparatuur | €9,100.00€ 9.100,00 | ||||
| 12 | 25-5111 | 23-12-2025 | Vrijval voorziening | -€7,350.00€ -7.350,00 | ||||
| 13 | 25-5112 | 31-12-2025 | Jaareindereservering | €15,480.00€ 15.480,00 | ||||
| 14 |
Veeg opzij om alle kolommen te zien
Formule in G2
=NEMEN(SORTEREN.OP(A2:A13,ASELECT.MATRIX(RIJEN(A2:A13))), G1)
=NEMEN(SORTEREN.OP(A2:A13;ASELECT.MATRIX(RIJEN(A2:A13))); G1)
Resultaat: 25-5111 / 25-5103 / 25-5108 / 25-510525-5111 / 25-5103 / 25-5108 / 25-5105Willekeurig: jouw uitkomst wijkt af
Hoe de onderdelen samenwerken
Let op: De formule is vluchtig: elke wijziging ergens in de werkmap, en elke keer dat je het bestand opent, trekt een nieuwe steekproef. Leg nooit de formule zelf vast als bewijs.
De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.
=RIJEN(A2:A13)
=RIJEN(A2:A13)
RIJEN ROWSHet aantal rijen in een bereik.Druk op Escape om te sluitenmatrixHet bereik of de matrix waarvan de rijen worden geteld.Kolom Regelnr., 12 cellenDruk op Escape om te sluitenTelt de journaalregels in de populatie:
A2:A13heeft 12 rijen, dus de populatie is 12. Doordat het bereik wordt geteld, volgen de volgende formules mee als je een langere lijst plakt en het bereik uitbreidt.=ASELECT.MATRIX(RIJEN(A2:A13))
=ASELECT.MATRIX(RIJEN(A2:A13))
ASELECT.MATRIX RANDARRAYEen overlopend blok willekeurige getallen.Lees meer over ASELECT.MATRIXDruk op Escape om te sluitenRIJEN ROWSHet aantal rijen in een bereik.Hier gebruikt als: rijenHoeveel rijen willekeurige getallen; standaard 1.Druk op Escape om te sluitenmatrixHet bereik of de matrix waarvan de rijen worden geteld.Kolom Regelnr., 12 cellenDruk op Escape om te sluitenEén willekeurige sleutel per journaalregel: 12 decimale getallen tussen 0 en 1. Ze lopen over als kolom van 12 en veranderen bij elke herberekening.
Dit is willekeurig, dus bij jou staan andere getallen. Stel dat de trekking 0,4127, 0,8530, 0,0961, 0,6742, 0,2318, 0,9075, 0,5586, 0,1803, 0,7394, 0,3269, 0,0447 en 0,6015 is, van regel
25-5101tot en met25-5112.=SORTEREN.OP(A2:A13,
ASELECT.MATRIX(RIJEN(A2:A13))) =SORTEREN.OP(A2:A13;
ASELECT.MATRIX(RIJEN(A2:A13))) SORTEREN.OP SORTBYSorteert een bereik op waarden in een ander bereik.Lees meer over SORTEREN.OPDruk op Escape om te sluitenarrayWat je gesorteerd terug wilt; de sleutels hoeven er niet in te zitten.Kolom Regelnr., 12 cellenDruk op Escape om te sluitenASELECT.MATRIX RANDARRAYEen overlopend blok willekeurige getallen.Hier gebruikt als: by_array1De waarden die de volgorde bepalen, één per rij van de matrix.Lees meer over ASELECT.MATRIXDruk op Escape om te sluitenRIJEN ROWSHet aantal rijen in een bereik.Hier gebruikt als: rijenHoeveel rijen willekeurige getallen; standaard 1.Druk op Escape om te sluitenmatrixHet bereik of de matrix waarvan de rijen worden geteld.Kolom Regelnr., 12 cellenDruk op Escape om te sluitenAlle 12 regels in willekeurige volgorde. SORTEREN.OP sorteert de regelnummers oplopend op de sleutels hierboven. Sorteren verandert alleen de volgorde, dus elke regel komt precies één keer voor.
De kleinste sleutel is 0,0447 (
25-5111), daarna 0,0961 (25-5103), 0,1803 (25-5108), 0,2318 (25-5105), 0,3269 (25-5110), 0,4127 (25-5101), 0,5586 (25-5107), 0,6015 (25-5112), 0,6742 (25-5104), 0,7394 (25-5109), 0,8530 (25-5102) en 0,9075 (25-5106).=NEMEN(SORTEREN.OP(A2:A13,
ASELECT.MATRIX(RIJEN(A2:A13))), G1) =NEMEN(SORTEREN.OP(A2:A13;
ASELECT.MATRIX(RIJEN(A2:A13))); G1) NEMEN TAKEDe eerste of laatste rijen of kolommen van een bereik.Lees meer over NEMENDruk op Escape om te sluitenSORTEREN.OP SORTBYSorteert een bereik op waarden in een ander bereik.Hier gebruikt als: matrixHet bereik of de matrix waaruit je rijen of kolommen neemt.Lees meer over SORTEREN.OPDruk op Escape om te sluitenarrayWat je gesorteerd terug wilt; de sleutels hoeven er niet in te zitten.Kolom Regelnr., 12 cellenDruk op Escape om te sluitenASELECT.MATRIX RANDARRAYEen overlopend blok willekeurige getallen.Hier gebruikt als: by_array1De waarden die de volgorde bepalen, één per rij van de matrix.Lees meer over ASELECT.MATRIXDruk op Escape om te sluitenRIJEN ROWSHet aantal rijen in een bereik.Hier gebruikt als: rijenHoeveel rijen willekeurige getallen; standaard 1.Druk op Escape om te sluitenmatrixHet bereik of de matrix waarvan de rijen worden geteld.Kolom Regelnr., 12 cellenDruk op Escape om te sluitenrijenHoeveel rijen je houdt; negatief telt vanaf het einde.Invoercel: 4Druk op Escape om te sluitenNEMEN houdt de eerste 4 van de geschudde regels over, de steekproefomvang in
G1. Met de trekking hierboven zijn dat25-5111,25-5103,25-5108en25-5105. Ze lopen over vanG2tot en metG5.Een andere trekking geeft een andere steekproef, maar altijd 4 verschillende regels.
Stap voor stap
- RIJEN(A2:A13) telt de populatie: 12 journaalregels, van
25-5101tot en met25-5112. - ASELECT.MATRIX(12) trekt 12 willekeurige decimale getallen tussen 0 en 1, één per regel. Dit is het willekeurige deel, dus bij jou zijn ze anders. Stel dat de trekking 0,4127 is voor
25-5101, 0,8530 voor25-5102, 0,0961 voor25-5103, 0,6742 voor25-5104, 0,2318 voor25-5105, 0,9075 voor25-5106, 0,5586 voor25-5107, 0,1803 voor25-5108, 0,7394 voor25-5109, 0,3269 voor25-5110, 0,0447 voor25-5111en 0,6015 voor25-5112. - SORTEREN.OP sorteert de 12 regelnummers oplopend op die sleutels: 0,0447, 0,0961, 0,1803, 0,2318, 0,3269, 0,4127, 0,5586, 0,6015, 0,6742, 0,7394, 0,8530, 0,9075. De geschudde volgorde is
25-5111,25-5103,25-5108,25-5105,25-5110,25-5101,25-5107,25-5112,25-5104,25-5109,25-5102,25-5106. - NEMEN(...; G1) houdt de eerste 4 van die 12 over, omdat
G1de n = 4 bevat:25-5111,25-5103,25-5108en25-5105. Ze lopen over vanG2tot en metG5. - Wat de trekking ook is, het resultaat is 4 verschillende regels uit de 12: sorteren verandert alleen de volgorde, dus geen enkele regel kan twee keer voorkomen. Alleen de keuze van de regels hangt van de trekking af.
- Zet de selectie vast: kopieer
G2:G5en plak als waarden, en schrijf de datum en de omvang van de populatie (12) ernaast. Zonder deze stap veranderen de regels bij de volgende herberekening en laat het bestand niet meer zien wat je hebt getest.
Waarom het werkt
Elke regel een onafhankelijke willekeurige sleutel geven en daarop sorteren maakt elke volgorde even waarschijnlijk, dus de eerste n regels zijn een eerlijke aselecte steekproef zonder teruglegging: elke regel heeft dezelfde kans en geen enkele komt twee keer voor. RIJEN koppelt het aantal sleutels aan de lijst, dus de sleutelmatrix heeft altijd de grootte die SORTEREN.OP eist.
ASELECT.MATRIX, SORTEREN.OP en NEMEN hebben Excel 2024, Excel voor Microsoft 365 of nieuwer nodig.
Let op
Leg in plaats van de formule zelf vastgezette waarden vast:
- kopieer de geselecteerde regels
- plak ze als waarden
- noteer de datum en de omvang van de populatie (hier 12 regels) ernaast
Dan kan een beoordelaar zien wat er is geselecteerd en uit hoeveel.
- De trekking is niet te herhalen. Moet het bestand aantonen dat de selectie eerlijk was, bewaar dan de vastgezette waarden samen met de populatie, of kies een methode met een vastgelegde startwaarde, zoals de systematische steekproef. Een aselecte trekking die je niet kunt herhalen is alleen verdedigbaar als de geplakte waarden en de populatie in het bestand staan.
- Is n groter dan de populatie, dan is de steekproef naar verwachting gelijk aan de hele populatie, zonder foutmelding (de pagina van Microsoft over NEMEN beschrijft dit geval niet; test het in je eigen Excel). Controleer of
G1kleiner is dan RIJEN(A2:A13); is n gelijk aan de populatie, dan is de steekproef ook gelijk aan de populatie.
Varianten
=INDEX(A2:A13,ASELECTTUSSEN(1, RIJEN(A2:A13)))
=INDEX(A2:A13;ASELECTTUSSEN(1; RIJEN(A2:A13)))
Eén willekeurige regel: ASELECTTUSSEN kiest een positie van 1 tot en met 12 en INDEX geeft de regel op die positie. Stel dat de trekking 7 is: het resultaat is 25-5107. Bij jou zal de trekking anders zijn.
ASELECTTUSSEN alleen kan dezelfde regel twee keer trekken: kopieer je deze formule naar vier cellen, dan trekt elke cel onafhankelijk en kunnen twee cellen dezelfde regel tonen. Gebruik voor een steekproef van meerdere verschillende regels de hoofdformule.
=NEMEN(SORTEREN.OP(A2:D13,ASELECT.MATRIX(RIJEN(A2:D13))), G1)
=NEMEN(SORTEREN.OP(A2:D13;ASELECT.MATRIX(RIJEN(A2:D13))); G1)
De hele regels in plaats van alleen de regelnummers: regelnummer, datum, omschrijving en bedrag van de 4 geselecteerde regels, zodat je direct vanuit het resultaat kunt gaan natrekken. De schudding geldt voor de rijen als geheel, dus elke regel houdt zijn eigen datum en bedrag.
Verwante combinaties
Gebruikte functies
- NEMENHoudt alleen de eerste of laatste rijen en kolommen van een bereik of matrix over en laat ze als blok overlopen, waarbij een negatief aantal vanaf het einde telt.
- SORTEREN.OPSorteert een bereik op de waarden van een of meer andere bereiken of matrices, elk met een eigen richting, en laat de gesorteerde kopie overlopen.
- ASELECT.MATRIXVult een blok van elke gewenste grootte met willekeurige getallen, decimaal of heel, tussen grenzen die jij kiest, en trekt ze opnieuw bij elke herberekening.
Gebruikte functies
- NEMENHoudt alleen de eerste of laatste rijen en kolommen van een bereik of matrix over en laat ze als blok overlopen, waarbij een negatief aantal vanaf het einde telt.
- SORTEREN.OPSorteert een bereik op de waarden van een of meer andere bereiken of matrices, elk met een eigen richting, en laat de gesorteerde kopie overlopen.
- ASELECT.MATRIXVult een blok van elke gewenste grootte met willekeurige getallen, decimaal of heel, tussen grenzen die jij kiest, en trekt ze opnieuw bij elke herberekening.