Combinatie
Systematische steekproef van facturen met een vaste startwaarde
Trek elke k-de factuur uit een populatie: het interval komt uit AFRONDEN.NAAR.BENEDEN en RIJEN, REEKS telt de trekkingen vanaf een vaste startwaarde die je in een cel typt, en INDEX toont de gekozen rijen.
Gebruikte functies:INDEXREEKSAFRONDEN.NAAR.BENEDENRIJENExcel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
Uitgewerkt voorbeeld
4 verkoopfacturen selecteren uit een populatie van 24
Situatie
Voor de omzetcontrole van Veldhuis Installatie BV heb je de verkoopfacturen over 2025 uit de XAF-auditfile. Je kiest een systematische steekproef van n = 4 facturen die je naloopt naar het contract en de pakbon.
De willekeurige startwaarde trek je één keer (3, binnen het interval) en je typt die in G2, zodat een collega of beoordelaar dezelfde selectie krijgt en het bestand vastlegt hoe de steekproef is getrokken.
De lijst hier telt 24 facturen zodat hij in het raster past; in de praktijk is de populatie duizenden facturen, bijvoorbeeld 6,0006.000 facturen met n = 60 geeft een interval van 100, en dezelfde formule werkt ongewijzigd.
De gegevens
| Rij | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Factuurnummer | Datum | Klant | Bedrag | Steekproefomvang (n) | 44 | ||
| 2 | 25-0101 | 08-01-2025 | D1004 | €4,850.00€ 4.850,00 | Vaste startwaarde | 33 | ||
| 3 | 25-0102 | 22-01-2025 | D1001 | €12,300.00€ 12.300,00 | Geselecteerde facturen | 25-0103=INDEX(A2:A25, G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1, 0))=INDEX(A2:A25; G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1; 0)) | ||
| 4 | 25-0103 | 05-02-2025 | D1007 | €2,175.00€ 2.175,00 | 25-0109 | |||
| 5 | 25-0104 | 19-02-2025 | D1002 | €8,640.00€ 8.640,00 | 25-0115 | |||
| 6 | 25-0105 | 03-03-2025 | D1009 | €15,900.00€ 15.900,00 | 25-0121 | |||
| 7 | 25-0106 | 17-03-2025 | D1001 | €3,420.00€ 3.420,00 | ||||
| 8 | 25-0107 | 31-03-2025 | D1005 | €6,780.00€ 6.780,00 | ||||
| 9 | 25-0108 | 14-04-2025 | D1003 | €9,215.00€ 9.215,00 | ||||
| 10 | 25-0109 | 28-04-2025 | D1008 | €1,980.00€ 1.980,00 | ||||
| 11 | 25-0110 | 12-05-2025 | D1002 | €22,450.00€ 22.450,00 | ||||
| 12 | 25-0111 | 26-05-2025 | D1006 | €5,310.00€ 5.310,00 | ||||
| 13 | 25-0112 | 10-06-2025 | D1004 | €7,860.00€ 7.860,00 | ||||
| 14 | 25-0113 | 24-06-2025 | D1001 | €18,600.00€ 18.600,00 | ||||
| 15 | 25-0114 | 08-07-2025 | D1010 | €2,940.00€ 2.940,00 | ||||
| 16 | 25-0115 | 22-07-2025 | D1003 | €11,275.00€ 11.275,00 | ||||
| 17 | 25-0116 | 05-08-2025 | D1007 | €4,120.00€ 4.120,00 | ||||
| 18 | 25-0117 | 19-08-2025 | D1009 | €9,830.00€ 9.830,00 | ||||
| 19 | 25-0118 | 02-09-2025 | D1005 | €13,700.00€ 13.700,00 | ||||
| 20 | 25-0119 | 16-09-2025 | D1002 | €6,050.00€ 6.050,00 | ||||
| 21 | 25-0120 | 30-09-2025 | D1008 | €3,590.00€ 3.590,00 | ||||
| 22 | 25-0121 | 14-10-2025 | D1006 | €16,480.00€ 16.480,00 | ||||
| 23 | 25-0122 | 28-10-2025 | D1001 | €8,225.00€ 8.225,00 | ||||
| 24 | 25-0123 | 11-11-2025 | D1004 | €5,640.00€ 5.640,00 | ||||
| 25 | 25-0124 | 09-12-2025 | D1010 | €10,760.00€ 10.760,00 | ||||
| 26 |
Veeg opzij om alle kolommen te zien
Formule in G3
=INDEX(A2:A25,G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1, 0))
=INDEX(A2:A25;G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1; 0))
Resultaat: 25-0103 / 25-0109 / 25-0115 / 25-012125-0103 / 25-0109 / 25-0115 / 25-0121
Hoe de onderdelen samenwerken
Let op: De startwaarde moet tussen 1 en het interval liggen: bij een grotere start kan een trekking voorbij het einde van de lijst vallen en geeft INDEX #VERW!.
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:A25)
=RIJEN(A2:A25)
RIJEN ROWSHet aantal rijen in een bereik.Druk op Escape om te sluitenmatrixHet bereik of de matrix waarvan de rijen worden geteld.Kolom Factuurnummer, 24 cellenDruk op Escape om te sluitenTelt de facturen in de populatie:
A2:A25heeft 24 rijen, dus de populatie is 24. Doordat het bereik wordt geteld, past de formule zich aan als je een langere lijst plakt en het bereik uitbreidt.=AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1,
0) =AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1;
0) AFRONDEN.NAAR.BENEDEN ROUNDDOWNRondt af richting nul.Lees meer over AFRONDEN.NAAR.BENEDENDruk op Escape om te sluitenRIJEN ROWSHet aantal rijen in een bereik.Hier gebruikt als: getalDe waarde die je richting nul afkapt; een getal, een cel of een berekening.Druk op Escape om te sluitenmatrixHet bereik of de matrix waarvan de rijen worden geteld.Kolom Factuurnummer, 24 cellenDruk op Escape om te sluiten/DeeltDruk op Escape om te sluitenVerwijzingInvoercel: 4Druk op Escape om te sluitenaantal_decimalenAantal cijfers dat blijft: 0 voor hele eenheden, 2 voor centen, -2 voor honderdtallen.0: heel getalDruk op Escape om te sluitenHet steekproefinterval: de populatie gedeeld door de steekproefomvang, afgerond naar beneden op een heel aantal rijen. 24 / 4 = 6, dus je neemt elke 6e factuur. Naar beneden afronden (niet naar boven) houdt de laatste trekking binnen de populatie.
=REEKS(G1)
=REEKS(G1)
rowsHoeveel rijen het blok heeft.Invoercel: 4Druk op Escape om te sluitenEen kolom die de trekkingen telt: 1, 2, 3, 4. Met alleen het eerste argument begint REEKS bij 1 en gaat telkens 1 omhoog, zoveel rijen als de steekproefomvang in
G1.=G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1,
0) =G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1;
0) VerwijzingInvoercel: 3Druk op Escape om te sluiten+Telt opDruk op Escape om te sluitenrowsHoeveel rijen het blok heeft.Invoercel: 4Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten*VermenigvuldigtDruk op Escape om te sluitenAFRONDEN.NAAR.BENEDEN ROUNDDOWNRondt af richting nul.Lees meer over AFRONDEN.NAAR.BENEDENDruk op Escape om te sluitenRIJEN ROWSHet aantal rijen in een bereik.Hier gebruikt als: getalDe waarde die je richting nul afkapt; een getal, een cel of een berekening.Druk op Escape om te sluitenmatrixHet bereik of de matrix waarvan de rijen worden geteld.Kolom Factuurnummer, 24 cellenDruk op Escape om te sluiten/DeeltDruk op Escape om te sluitenVerwijzingInvoercel: 4Druk op Escape om te sluitenaantal_decimalenAantal cijfers dat blijft: 0 voor hele eenheden, 2 voor centen, -2 voor honderdtallen.0: heel getalDruk op Escape om te sluitenDe posities van de gekozen rijen. De vaste startwaarde in
G2is 3. De tellingen 1 tot en met 4 worden 0 tot en met 3 na aftrek van 1, keer het interval 6 geeft 0, 6, 12, 18, en met de startwaarde erbij krijg je 3, 9, 15, 21.=INDEX(A2:A25,
G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1, 0)) =INDEX(A2:A25;
G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1; 0)) INDEXGeeft de waarde op een opgegeven rij en kolom van een bereik.Lees meer over INDEXDruk op Escape om te sluitenmatrixHet blok cellen waaruit je kiest. Er wordt geteld vanaf de cel linksboven.Kolom Factuurnummer, 24 cellenDruk op Escape om te sluitenrij_getalWelke rij van het blok, geteld vanaf 1. Met 0 krijg je alle rijen.Invoercel: 3Druk op Escape om te sluiten+Telt opDruk op Escape om te sluitenrowsHoeveel rijen het blok heeft.Invoercel: 4Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten*VermenigvuldigtDruk op Escape om te sluitenAFRONDEN.NAAR.BENEDEN ROUNDDOWNRondt af richting nul.Lees meer over AFRONDEN.NAAR.BENEDENDruk op Escape om te sluitenRIJEN ROWSHet aantal rijen in een bereik.Hier gebruikt als: getalDe waarde die je richting nul afkapt; een getal, een cel of een berekening.Druk op Escape om te sluitenmatrixHet bereik of de matrix waarvan de rijen worden geteld.Kolom Factuurnummer, 24 cellenDruk op Escape om te sluiten/DeeltDruk op Escape om te sluitenVerwijzingInvoercel: 4Druk op Escape om te sluitenaantal_decimalenAantal cijfers dat blijft: 0 voor hele eenheden, 2 voor centen, -2 voor honderdtallen.0: heel getalDruk op Escape om te sluitenINDEX haalt de factuurnummers op de posities 3, 9, 15 en 21 uit
A2:A25en toont ze als kolom:25-0103,25-0109,25-0115,25-0121.
Stap voor stap
- RIJEN(A2:A25) telt de populatie: 24 facturen, van
25-0101tot en met25-0124. - AFRONDEN.NAAR.BENEDEN(24/4; 0) is het interval: 24 / 4 = 6 precies, er valt dus niets af en je neemt elke 6e factuur.
- REEKS(4) geeft 1, 2, 3, 4. Min 1 geeft 0, 1, 2, 3.
- Keer het interval: 0 × 6 = 0, 1 × 6 = 6, 2 × 6 = 12, 3 × 6 = 18.
- Met de vaste startwaarde 3 erbij krijg je de posities 3, 9, 15 en 21. Alle vier zijn hoogstens 24, dus elke trekking valt binnen de populatie.
- INDEX(A2:A25; ...) geeft de factuur op elke positie: positie 3 is
25-0103, positie 9 is25-0109, positie 15 is25-0115en positie 21 is25-0121. De vier nummers lopen door vanG3tot en metG6.
Waarom het werkt
Een systematische steekproef neemt per interval één item, steeds op dezelfde afstand, vanaf een startwaarde die je één keer willekeurig trekt. De formule bouwt precies dat: de startwaarde bepaalt de eerste positie, REEKS telt hoeveel stappen je zet en het interval is de staplengte.
Omdat de startwaarde een getypt getal is en geen ASELECT, verandert de selectie niet als het werkblad herberekent, zodat je haar kunt vastleggen en een beoordelaar haar kan reproduceren. Zet je n in G1 op 6, dan wordt het interval 4 en krijg je de posities 3, 7, 11, 15, 19 en 23.
REEKS en het overlopen van de uitkomst hebben Excel 2021, Excel voor Microsoft 365 of nieuwer nodig.
Let op
- Een start van 8 geeft hier de posities 8, 14, 20 en 26, en positie 26 bestaat niet, dus INDEX geeft #VERW!. Trek de startwaarde één keer met een willekeurig getal tussen 1 en het interval en leg vast hoe je dat hebt gedaan.
Gaat de populatie niet precies op, dan laat AFRONDEN.NAAR.BENEDEN een staart over die nauwelijks gekozen kan worden. Bij 26 facturen en n = 4 is het interval 6 en ligt de startwaarde tussen 1 en 6, dus de laatste trekking zit hoogstens op positie 24 (start 6: 6, 12, 18, 24).
Factuur 25 en 26 kunnen nooit worden getrokken, en factuur 22 tot en met 24 alleen bij een startwaarde van 4 tot en met 6. Controleer of dat acceptabel is, of kies een n die past.
- De steekproef is zo goed als de lijst. Controleer eerst de volledigheid van de populatie (vergelijk het aantal en het totaal met het grootboek) en zorg dat de lijst niet is gesorteerd of gegroepeerd in een patroon dat zich op het interval herhaalt, bijvoorbeeld elke 6e factuur altijd dezelfde klant.
Varianten
=INDEX($A$2:$A$25,$G$2+(RIJEN($F$3:F3)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN($A$2:$A$25)/$G$1, 0))
=INDEX($A$2:$A$25;$G$2+(RIJEN($F$3:F3)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN($A$2:$A$25)/$G$1; 0))
De oudere aanpak zonder REEKS: typ de formule in één cel en kopieer hem vier rijen naar beneden. RIJEN($F$3:F3) telt 1, 2, 3, 4 terwijl je kopieert. Dit werkt in elke Excel-versie en geeft dezelfde vier facturen.
=INDEX(D2:D25,G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1, 0))
=INDEX(D2:D25;G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1; 0))
Dezelfde posities op de bedragenkolom D: 2,1752.175, 1,9801.980, 11,27511.275 en 16,48016.480. Samen zijn ze 31,91031.910, de waarde van de geselecteerde items.
=INDEX(A2:A25,REEKS(G1, 1, G2, AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1, 0)))
=INDEX(A2:A25;REEKS(G1; 1; G2; AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1; 0)))
Een kortere vorm: REEKS neemt de startwaarde (derde argument) en de stap (vierde argument) zelf, en geeft direct 3, 9, 15, 21. Zelfde resultaat; de langere hoofdformule laat de rekensom zien, die je beter aan een beoordelaar uitlegt.
Gebruikte functies
- INDEXGeeft de cel op een bepaald rij- en kolomnummer binnen een bereik, of een hele rij of kolom daarvan als een van de nummers 0 is.
- REEKSMaakt een blok opeenvolgende getallen met een beginwaarde en een stap, dat overloopt naar zoveel rijen en kolommen als je opgeeft.
- AFRONDEN.NAAR.BENEDENKapt een getal af op een gekozen positie, in de richting van nul en zonder ooit op te hogen: 7,9 wordt 7 en -7,9 wordt -7.
Gebruikte functies
- INDEXGeeft de cel op een bepaald rij- en kolomnummer binnen een bereik, of een hele rij of kolom daarvan als een van de nummers 0 is.
- REEKSMaakt een blok opeenvolgende getallen met een beginwaarde en een stap, dat overloopt naar zoveel rijen en kolommen als je opgeeft.
- AFRONDEN.NAAR.BENEDENKapt een getal af op een gekozen positie, in de richting van nul en zonder ooit op te hogen: 7,9 wordt 7 en -7,9 wordt -7.