Generisk formel
=MATCH(RAND(),cumulative_probability)
Sammanfattning
För att generera ett slumptal, viktat med en given sannolikhet, kan du använda en hjälptabell tillsammans med en formel baserad på RAND- och MATCH-funktionerna.
I exemplet som visas är formeln i F5:
=MATCH(RAND(),D$5:D$10)
Förklaring
Denna formel bygger på hjälptabellen som syns i intervallet B4: D10. Kolumn B innehåller de sex siffror som vi vill ha som slutresultat. Kolumn C innehåller den sannolikhetsvikt som tilldelats varje nummer, angiven i procent. Kolumn D innehåller den kumulativa sannolikheten, skapad med denna formel i D5, kopierad:
=SUM(D4,C4)
Observera att vi avsiktligt flyttar den kumulativa sannolikheten ner en rad så att värdet i D5 är noll. Detta är för att se till att MATCH kan hitta en position för alla värden ner till noll som förklaras nedan.
För att generera ett slumpmässigt värde med hjälp av den viktade sannolikheten i hjälptabellen innehåller F5 denna formel, kopierad:
=MATCH(RAND(),D$5:D$10)
Inuti MATCH tillhandahålls uppslagsvärdet av RAND-funktionen. RAND genererar ett slumpmässigt värde mellan noll och 1. Uppslagsmatrisen är intervallet D5: D10, låst så att det inte ändras eftersom formeln kopieras ner i kolumnen.
Det tredje argumentet för MATCH, matchningstyp, utelämnas. När matchningstyp utelämnas returnerar MATCH positionen för det största värdet mindre än eller lika med uppslagsvärdet *. I praktiken betyder detta att MATCH-funktionen färdas längs värdena i D5: D10 tills ett större värde påträffas och sedan "går tillbaka" till föregående position. När MATCH stöter på ett värde som är större än det största senaste värdet i D5: D10 (0,7 i exemplet), returnerar det den sista positionen (6 i exemplet). Som nämnts ovan är det första värdet i D5: D10 medvetet noll för att säkerställa att värdena under .1 "fångas" av uppslagstabellen och returnerar en position på 1.
* Värden i uppslagsområdet måste sorteras i stigande ordning.
Slumpmässigt viktat textvärde
För att returnera ett slumpmässigt viktat textvärde (dvs. ett icke-numeriskt värde) kan du ange textvärden i intervallet B5: B10 och sedan lägga till INDEX för att returnera ett värde i det intervallet, baserat på positionen som returneras av MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Anteckningar
- Jag stötte på detta tillvägagångssätt i ett foruminlägg på mrexcel.com
- RAND är en flyktig funktion och kommer att räknas om vid varje arbetsbladförändring
- När du har slumpmässiga värden, använd klistra in specialvärden för att ersätta formeln om det behövs
