Generieke formule
=MATCH(RAND(),cumulative_probability)
Samenvatting
Om een willekeurig getal te genereren, gewogen met een bepaalde kans, kunt u een helper-tabel gebruiken samen met een formule op basis van de RAND- en MATCH-functies.
In het getoonde voorbeeld is de formule in F5:
=MATCH(RAND(),D$5:D$10)
Uitleg
Deze formule is gebaseerd op de helper-tabel die zichtbaar is in het bereik B4: D10. Kolom B bevat de zes cijfers die we als eindresultaat willen. Kolom C bevat het waarschijnlijkheidsgewicht dat aan elk getal is toegewezen, ingevoerd als een percentage. Kolom D bevat de cumulatieve kans, gemaakt met deze formule in D5, naar beneden gekopieerd:
=SUM(D4,C4)
Merk op dat we opzettelijk de cumulatieve kans een rij naar beneden verschuiven, zodat de waarde in D5 nul is. Dit is om ervoor te zorgen dat MATCH een positie kan vinden voor alle waarden tot nul, zoals hieronder wordt uitgelegd.
Om een willekeurige waarde te genereren, met behulp van de gewogen kans in de helper-tabel, bevat F5 deze formule, naar beneden gekopieerd:
=MATCH(RAND(),D$5:D$10)
Binnen MATCH wordt de opzoekwaarde geleverd door de RAND-functie. RAND genereert een willekeurige waarde tussen nul en 1. De opzoekmatrix is het bereik D5: D10, vergrendeld zodat deze niet verandert als de formule naar beneden in de kolom wordt gekopieerd.
Het derde argument voor MATCH, zoektype, wordt weggelaten. Als het zoektype wordt weggelaten, retourneert VERGELIJKEN de positie van de grootste waarde kleiner dan of gelijk aan de opzoekwaarde *. In de praktijk betekent dit dat de MATCH-functie langs de waarden in D5: D10 reist totdat een grotere waarde wordt aangetroffen, en vervolgens "terugstapt" naar de vorige positie. Wanneer MATCH een waarde tegenkomt die groter is dan de grootste laatste waarde in D5: D10 (.7 in het voorbeeld), retourneert het de laatste positie (6 in het voorbeeld). Zoals hierboven vermeld, is de eerste waarde in D5: D10 opzettelijk nul om ervoor te zorgen dat waarden onder .1 worden "opgevangen" door de opzoektabel en een positie van 1 retourneren.
* Waarden in het opzoekbereik moeten in oplopende volgorde worden gesorteerd.
Willekeurig gewogen tekstwaarde
Om een willekeurig gewogen tekstwaarde te retourneren (dwz een niet-numerieke waarde), kunt u tekstwaarden invoeren in het bereik B5: B10 en vervolgens INDEX toevoegen om een waarde in dat bereik te retourneren, gebaseerd op de positie die wordt geretourneerd door VERGELIJKEN:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Opmerkingen
- Ik kwam deze benadering tegen in een forumpost op mrexcel.com
- RAND is een vluchtige functie en zal bij elke wijziging van het werkblad opnieuw worden berekend
- Als u eenmaal willekeurige waarde (n) heeft, gebruikt u speciale> waarden plakken om de formule indien nodig te vervangen
