Generisk formel
=MATCH(RAND(),cumulative_probability)
Sammendrag
For å generere et tilfeldig tall, vektet med en gitt sannsynlighet, kan du bruke en hjelpetabell sammen med en formel basert på RAND- og MATCH-funksjonene.
I eksemplet vist er formelen i F5:
=MATCH(RAND(),D$5:D$10)
Forklaring
Denne formelen er avhengig av hjelpetabellen som er synlig i området B4: D10. Kolonne B inneholder de seks tallene vi ønsker som et endelig resultat. Kolonne C inneholder sannsynlighetsvekten tildelt hvert nummer, angitt i prosent. Kolonne D inneholder den kumulative sannsynligheten, opprettet med denne formelen i D5, kopiert ned:
=SUM(D4,C4)
Legg merke til at vi med vilje skifter den kumulative sannsynligheten ned en rad, slik at verdien i D5 er null. Dette er for å sikre at MATCH er i stand til å finne en posisjon for alle verdier ned til null som forklart nedenfor.
For å generere en tilfeldig verdi, ved hjelp av den vektede sannsynligheten i hjelpetabellen, inneholder F5 denne formelen, kopiert ned:
=MATCH(RAND(),D$5:D$10)
Inne i MATCH leveres oppslagsverdien av RAND-funksjonen. RAND genererer en tilfeldig verdi mellom null og 1. Oppslagsmatrisen er området D5: D10, låst slik at den ikke endres ettersom formelen kopieres nedover i kolonnen.
Det tredje argumentet for MATCH, samsvarstype, er utelatt. Når samsvarstype er utelatt, vil MATCH returnere posisjonen til den største verdien mindre enn eller lik oppslagsverdien *. I praksis betyr dette at MATCH-funksjonen beveger seg langs verdiene i D5: D10 til en større verdi oppstår, og deretter "går tilbake" til forrige posisjon. Når MATCH møter en verdi som er større enn den største siste verdien i D5: D10 (.7 i eksemplet), returnerer den siste posisjon (6 i eksemplet). Som nevnt ovenfor er den første verdien i D5: D10 bevisst null for å sikre at verdiene under .1 "fanges" av oppslagstabellen og returnerer en posisjon på 1.
* Verdiene i oppslagsområdet må sorteres i stigende rekkefølge.
Tilfeldig vektet tekstverdi
For å returnere en tilfeldig vektet tekstverdi (dvs. en ikke-numerisk verdi), kan du angi tekstverdier i området B5: B10, og deretter legge til INDEKS for å returnere en verdi i det området, basert på posisjonen som returneres av MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Merknader
- Jeg kjørte inn i denne tilnærmingen i et foruminnlegg på mrexcel.com
- RAND er en ustabil funksjon og vil beregne på nytt med hvert regnearksendring
- Når du har tilfeldige (r) verdier, bruk lim inn spesielle> verdier for å erstatte formelen om nødvendig
