{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Steekproefberekening","np-tabel","benadering"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":11,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Steekproefberekening","index":0,"n_rows":50,"n_cols":48,"rows_kept":50,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Steekproefomvangcalculatie"]
[null,"drs. N.S. van Leeuwen"]
[]
[]
[null,"Toelichting"]
[null,"Dit rekenblad is bedoeld om je inzicht te geven in de manieren om tot een steekproefomvang te komen."]
[null,"In dit rekenblad zijn drie methodes uitgewerkt. Algoritmisch, Via benadering en gebruik maken van de inverse gamma functie."]
[null,"Voor alle methodes houden we de onderstaande parameters aan."]
[null,"Populatie omvang",1000000]
[null,"Uitvoeringsmaterialiteit",50000]
[null,"Verwachte fout in %",0]
[null,"Betrouwbaarheidsinterval",0.95]
[]
[null,"Algoritmisch"]
[null,"Deze manier maakt gebruik van User Difende Funcsions (UDFs) om tot een uitkomst te komen"]
[null,"Dit zijn VBA-scripts of Lambda-funcites die ertoe resulteren dat je een zelfgeschreven functie in Excel kunt gebruiken om tot de steekproefomvang te komen."]
[null,"De Lambda-functie is een nieuwe functionaliteit in Office 365 die werkt zonder VBA. VBA is in sommige organisaties uitgeschakeld vanwege veiligheidoverwegingen."]
[null,"Je ziet kleine verschillen tussen de uitkomsten. Dit komt omdat de berekeningen bij benadering gelijk aan elkaar zijn maar in sommige gevallen zie je grotere verschillen. Je zult echter zien dat Poisson veelal op een hogere steekproefomvang uit komt."]
[null,"Steekproefomvang:","VBA",null,null,"Lambda"]
[null,"Binominaal",{"$f":"BinomSample(C10,C11,C12,C13)","v":59},null,null,{"$f":"Binom.sample(1,C11/C10,C12,C13)","v":59}]
[null,"Poisson",{"$f":"PoissonSample(C10,C11,C12,C13)","v":60},null,null,{"$f":"Poisson.sample(1,C11/C10,C12,C13)","v":60}]
[null,"Hypergeometrisch",{"$f":"HypergeoSample(C10,C11,C12,C13)","v":59},null,null,{"$f":"Hypergeom.Sample(1,C10,C11,C12,C13)","v":59}]
[]
[]
[null,"Benadering"]
[null,"Deze manier maakt inzichtelijk hoe je via benadering het de juiste steekproefomvang kunt bepalen."]
[null,"Deze tabel laat zien wat de betrouwbaarheid is voor de verdeling die links staat aangegeven, bij de steekproefomvang bij horizontaal staat aangegeven."]
[null,"Je ziet dat wanneer de steekproefomvang toe neemt, de betrouwbaarheid toe neemt."]
[null,"De cellen waarbij de betrouwbaarheid groter is dan de vereiste uit cel C13 worden groen gearceerd."]
[null,"Zo zie je dat wanneer je handmatig de steekproefomvang vergroot je uiteindelijk op een voldoende betrouwbare steekproefomvang uit komt."]
[null,"In het tabblad benadering is een vereenvoudigde versie hiervan opgenomen."]
[]
[null,"Steekproef omvang",{"$f":"C22-3","v":57},{"$f":"C22-2","v":58},{"$f":"C22-1","v":59},{"$f":"C22","v":60},{"$f":"F34+1","v":61},{"$f":"G34+1","v":62},63,64,65,66,67,68,69,70,71,72,73,74,{"$f":"T34+1","v":75},{"$f":"U34+1","v":76},77,78,79,80,81,82,83,{"$f":"AC34+1","v":84},85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102]
[null,"Binomiaal",{"$f":"1-_xlfn.BINOM.DIST(C41,C34,C40,TRUE)","v":0.94626645401725962},0.94895313131639658,0.95150547475057679,0.95393020101304793,0.95623369096239552,0.95842200641427577,0.96050090609356198,0.96247586078888392,0.96435206774943971,0.9661344643619677,0.96782774114386927,0.96943635408667583,0.97096453638234204,0.972416309563225,0.97379549408506372,0.97510571938081048,0.97635043341176997,0.97753291174118151,0.97865626615412238,0.97972345284641627,0.98073728020409545,0.9817004161938907,0.98261539538419618,0.9834846256149864,0.98431039433423706,0.98509487461752521,0.9858401308866489,0.98654812434231653,0.98722071812520062,0.9878596822189406,0.98846669810799359,0.98904336320259389,0.98959119504246418,0.99011163529034096,0.99060605352582398,0.99107575084953281,0.99152196330705611,0.99194586514170335,0.99234857188461811,0.99273114329038725,0.99309458612586787,0.99343985681957447,0.99376786397859573,0.99407947077966596,0.99437549724068264,0.99465672237864855]
[null,"Poisson",{"$f":"1-_xlfn.POISSON.DIST(C41,C40*C34,TRUE)","v":0.94215567912516152},0.94497677994359275,0.9476602940515676,0.95021293163213605,0.95264107560885913,0.95495079760644219,0.95714787313295979,0.95923779602163384,0.96122579216827797,0.96311683259876002,0.96491564589915502,0.96662673003967392,0.96825436362193207,0.96980261657768152,0.97127536034576056,0.97267627755270747,0.97400887122124469,0.97527647352966063,0.97648225414399092,0.97762922814383446,0.97872026356162278,0.97975808855419566,0.98074529822461309,0.98168436111126578,0.98257762536050652,0.98342732459823878,0.98423558351514551,0.9850044231795223,0.98573576609100078,0.98643144098779911,0.98709318741952012,0.98772266009693155,0.98832143302960451,0.98889100346175773,0.98943279561614739,0.98994816425536647,0.99043839806945655,0.99090472289830422,0.99134830479687941,0.99177025295097998,0.99217162245077428,0.99255341692907562,0.99291659107094787,0.99326205300091452,0.99359066655374362,0.99390325343448438]
[null,"Hypergeometrisch",{"$f":"1-_xlfn.HYPGEOM.DIST(C41,C34,C40*$C$10,$C$10,TRUE)","v":0.94627096762003537},0.94895757237550471,0.9515098417883554,0.95393449275334452,0.95623790632049133,0.95842614448699492,0.96050496614958825,0.96247984225930372,0.96435597021852792,0.96613828755822861,0.96783148493134386,0.96944001845652283,0.97096812144469991,0.97241981553935741,0.97379892129979273,0.97510906825523691,0.97635370445628167,0.97753610554874726,0.97865938339387037,0.9797264942574917,0.98074024658979442,0.98170330841606523,0.98261821435792551,0.98348737230350691,0.9843130697441268,0.98509747979413453,0.98584266690977129,0.98655059232209197,0.9872231191982449,0.98786201754469216,0.98846896886527114,0.98904557058635734,0.98959334026077073,0.99011371956148997,0.99060807807568174,0.99107771690903146,0.99152387210985837,0.99194771792202607,0.9923503698752083,0.99273288772064328,0.99309627822009905,0.99344149779539281,0.99376945504543368,0.99408101313741315,0.99437699207843655,0.99465817087357311]
[]
[null,"Verhoudingen binominaal"]
[null,"P: materialiteitspercentage",{"$f":"$C$11/$C$10","v":0.05},{"$f":"$C$11/$C$10","v":0.05},0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,"k: Verwachte fout in de steekproef, afgerond naar boven",{"$f":"ROUNDUP($C$12*C34,0)","v":0},{"$f":"ROUNDUP($C$12*D34,0)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[]
[null,"Inverse gamma functie"]
[null,"Deze manier maakt gebruk van de inverse gamma functie."]
[null,"Je berekend hiermee de np-waarde uit. Dit is het gemiddelde aantat fouten in de steekproef."]
[null,"Je kunt de steekproefomvang uitrekenen dooor de np-waarde te delen door p."]
[null,"Je ziet dat je hiermee op dezelfde steekproefomvang uit komt als via de benaderingsmethode."]
[null,"Aantal fouten",{"$f":"ROUNDUP(C12*C22,0)","v":0}]
[null,"NP-factor",{"$f":"_xlfn.GAMMA.INV(C13,C48+1,1)","v":2.99573227355399}]
[null,"n",{"$f":"(C49/C40)","v":59.914645471079794}]

{"sheet":"np-tabel","index":1,"n_rows":24,"n_cols":13,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,"np-tabel"]
[]
[]
[]
[null,null,"p",0.05]
[null,null,null,"1-beta"]
[null,null,"k",0.99,"n",0.97499999999999998,"n",0.95,"n",0.92500000000000004,"n",0.9,"n"]
[null,null,0,{"$f":"_xlfn.GAMMA.INV(D$8,$C9+1,1)","v":4.6051701859880909},{"$f":"ROUNDUP(D9/$D$6,0)","v":93},{"$f":"_xlfn.GAMMA.INV(F$8,$C9+1,1)","v":3.6888794541139354},{"$f":"ROUNDUP(F9/$D$6,0)","v":74},2.99573227355399,{"$f":"ROUNDUP(H9/$D$6,0)","v":60},2.5902671654458271,{"$f":"ROUNDUP(J9/$D$6,0)","v":52},2.3025850929940459,{"$f":"ROUNDUP(L9/$D$6,0)","v":47}]
[null,null,1,{"$f":"_xlfn.GAMMA.INV(D$8,$C10+1,1)","v":6.6383520679938073},{"$f":"ROUNDUP(D10/$D$6,0)","v":133},5.571643390938898,112,4.7438645183905788,{"$f":"ROUNDUP(H10/$D$6,0)","v":95},4.2481411039027535,85,3.8897201698674295,{"$f":"ROUNDUP(L10/$D$6,0)","v":78}]
[null,null,2,8.4059469148854671,169,7.2246876677239582,145,6.2957936218719883,{"$f":"ROUNDUP(H11/$D$6,0)","v":126},5.7329727538608619,115,5.3223203378342099,{"$f":"ROUNDUP(L11/$D$6,0)","v":107}]
[null,null,3,10.04511751483161,201,8.7672730697423145,176,7.7536565279327245,{"$f":"ROUNDUP(H12/$D$6,0)","v":156},7.1348712033768527,143,6.6807830682558631,{"$f":"ROUNDUP(L12/$D$6,0)","v":134}]
[null,null,4,11.604625579477174,233,10.241588675403698,205,9.1535190266375697,{"$f":"ROUNDUP(H13/$D$6,0)","v":184},8.4856826253850208,170,7.9935895860526305,{"$f":"ROUNDUP(L13/$D$6,0)","v":160}]
[null,null,5,13.108483652767932,263,11.668332079322671,234,10.513034908741531,{"$f":"ROUNDUP(H14/$D$6,0)","v":211},9.8009850952541342,197,9.274673893351622,{"$f":"ROUNDUP(L14/$D$6,0)","v":186}]
[null,null,6,{"$f":"_xlfn.GAMMA.INV(D$8,$C15+1,1)","v":14.570618870336393},292,13.059474022518685,262,11.842395652420286,{"$f":"ROUNDUP(H15/$D$6,0)","v":237},11.089764999267906,222,10.53207210649853,{"$f":"ROUNDUP(L15/$D$6,0)","v":211}]
[null,null,7,15.999963454407588,320,14.422675361702382,289,13.148113802432119,{"$f":"ROUNDUP(H16/$D$6,0)","v":263},12.357760935526786,248,11.770914461548056,{"$f":"ROUNDUP(L16/$D$6,0)","v":236}]
[null,null,8,17.402652867352533,349,15.763189220193315,316,14.434649715196313,{"$f":"ROUNDUP(H17/$D$6,0)","v":289},13.608900927639461,273,12.994711541318607,{"$f":"ROUNDUP(L17/$D$6,0)","v":260}]
[null,null,9,18.783117393312519,376,17.084803451419173,342,15.705216422115463,{"$f":"ROUNDUP(H18/$D$6,0)","v":315},14.846013149558365,297,14.205990292152816,{"$f":"ROUNDUP(L18/$D$6,0)","v":285}]
[null,null,10,20.144680218796942,403,18.390356042017775,368,16.962219235721903,{"$f":"ROUNDUP(H19/$D$6,0)","v":340},16.071213914691803,322,15.406641171976517,{"$f":"ROUNDUP(L19/$D$6,0)","v":309}]
[null,null,11,21.489910069675808,430,19.682038513301954,394,18.207514250903657,{"$f":"ROUNDUP(H20/$D$6,0)","v":365},17.286135714693739,346,16.59812214431409,{"$f":"ROUNDUP(L20/$D$6,0)","v":332}]
[null,null,12,22.820841333141569,457,20.961585048176953,420,19.44256932991502,{"$f":"ROUNDUP(H21/$D$6,0)","v":389},18.492069170864688,370,17.781585635961733,{"$f":"ROUNDUP(L21/$D$6,0)","v":356}]
[null,null,13,24.139117885157756,483,22.230395918158877,445,20.668569075713695,{"$f":"ROUNDUP(H22/$D$6,0)","v":414},19.690055559164385,394,18.957961272348534,{"$f":"ROUNDUP(L22/$D$6,0)","v":380}]
[null,null,14,25.446090655758553,509,23.489621121835579,470,21.886485912871095,{"$f":"ROUNDUP(H23/$D$6,0)","v":438},20.880949439214511,418,20.128011869355902,{"$f":"ROUNDUP(L23/$D$6,0)","v":403}]
[null,null,15,26.742885918117665,535,24.740218871485844,495,23.097129760139232,{"$f":"ROUNDUP(H24/$D$6,0)","v":462},22.065462406131449,442,21.292372541490419,{"$f":"ROUNDUP(L24/$D$6,0)","v":426}]

{"sheet":"benadering","index":2,"n_rows":21,"n_cols":4,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Steekproefomvangcalculatie"]
[null,"drs. N.S. van Leeuwen"]
[]
[]
[null,"Algoritmisch"]
[null,"Populatie omvang",1000000]
[null,"Uitvoeringsmaterialiteit",50000]
[null,"Verwachte fout in %",0.01]
[null,"Betrouwbaarheidsinterval",0.95]
[]
[]
[null,"Benaderingsmethode"]
[null,"Steekproef omvang",95,"Verhoog de steekproefomvang tot het gewenste betrouwbaarheidspercentage wordt bereikt."]
[null,"Binominaal",{"$f":"1-_xlfn.BINOM.DIST(C21,C14,C20,TRUE)","v":0.95409143130770901}]
[null,"Poisson",{"$f":"1-_xlfn.POISSON.DIST(C21,C20*C14,TRUE)","v":0.95025275258205633}]
[null,"Hypergeometrisch",{"$f":"1-_xlfn.HYPGEOM.DIST(C21,C14,C20*$C$7,$C$7,TRUE)","v":0.95409843432288999}]
[]
[null,"Verhoudingen binominaal"]
[null,"P: materialiteitspercentage",{"$f":"$C$8/$C$7","v":0.05}]
[null,"k: Verwachte fout in de steekproef, afgerond naar boven",{"$f":"ROUNDUP(C9*C14,0)","v":1}]