Generička formula
=MATCH(RAND(),cumulative_probability)
Sažetak
Za generiranje slučajnog broja, ponderiranog s danom vjerojatnosti, možete koristiti pomoćnu tablicu zajedno s formulom koja se temelji na funkcijama RAND i MATCH.
U prikazanom primjeru formula u F5 je:
=MATCH(RAND(),D$5:D$10)
Obrazloženje
Ova se formula oslanja na pomoćnu tablicu vidljivu u rasponu B4: D10. Stupac B sadrži šest brojeva koje želimo kao konačni rezultat. Stupac C sadrži ponder vjerojatnosti dodijeljen svakom broju, upisan kao postotak. Stupac D sadrži kumulativnu vjerojatnost, stvorenu ovom formulom u D5, kopiranu:
=SUM(D4,C4)
Primijetite, kumulativnu vjerojatnost namjerno pomičemo prema dolje za jedan redak, tako da vrijednost u D5 bude nula. Ovo je kako bi se osiguralo da MATCH može pronaći položaj za sve vrijednosti na nulu kako je objašnjeno u nastavku.
Da bi generirao slučajnu vrijednost, koristeći ponderiranu vjerojatnost u pomoćnoj tablici, F5 sadrži ovu formulu, kopiranu dolje:
=MATCH(RAND(),D$5:D$10)
Unutar MATCH vrijednost pretraživanja pruža funkcija RAND. RAND generira slučajnu vrijednost između nule i 1. Niz pretraživanja je raspon D5: D10, zaključan, tako da se neće mijenjati dok se formula kopira niz stupac.
Treći argument za MATCH, vrsta podudaranja, izostavljen je. Kada je vrsta podudaranja izostavljena, MATCH će vratiti položaj najveće vrijednosti manje ili jednako vrijednosti pretraživanja *. Praktično, to znači da funkcija MATCH putuje duž vrijednosti u D5: D10 dok se ne naiđe na veću vrijednost, a zatim "odstupi" na prethodni položaj. Kada MATCH naiđe na vrijednost veću od najveće posljednje vrijednosti u D5: D10 (.7 u primjeru), vraća posljednju poziciju (6 u primjeru). Kao što je gore spomenuto, prva vrijednost u D5: D10 namjerno je nula kako bi se osiguralo da vrijednosti ispod .1 "uhvate" tablica pretraživanja i vrate položaj 1.
* Vrijednosti u rasponu pretraživanja moraju se sortirati uzlazno.
Slučajno ponderirana vrijednost teksta
Da biste vratili slučajnu ponderiranu tekstualnu vrijednost (tj. Nenumeričku vrijednost), možete unijeti tekstualne vrijednosti u raspon B5: B10, a zatim dodati INDEX da biste vratili vrijednost u tom rasponu, na temelju pozicije koju vraća MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Bilješke
- Na ovaj sam pristup naišao u postu na forumu na mrexcel.com
- RAND je hlapljiva funkcija i preračunavat će se sa svakom promjenom radnog lista
- Nakon što imate slučajne vrijednosti, upotrijebite posebne> vrijednosti da biste zamijenili formulu ako je potrebno
