Üldine valem
=MATCH(RAND(),cumulative_probability)
Kokkuvõte
Ettenähtud tõenäosusega kaalutud juhusliku arvu loomiseks võite kasutada abitabelit koos funktsioonidega RAND ja MATCH põhineva valemiga.
Näidatud näites on valem F5-s järgmine:
=MATCH(RAND(),D$5:D$10)
Selgitus
See valem tugineb abistajate tabelile, mis on nähtav vahemikus B4: D10. Veerg B sisaldab kuut numbrit, mida soovime lõpptulemuseks. Veerg C sisaldab protsentidena sisestatud igale numbrile omistatud tõenäosuskaalu. Veerg D sisaldab selle valemiga D5-s loodud kumulatiivset tõenäosust, mis on kopeeritud:
=SUM(D4,C4)
Pange tähele, et nihutame tahtlikult kumulatiivset tõenäosust ühe rea võrra allapoole, nii et väärtus D5-s on null. Selle eesmärk on veenduda, et MATCH suudab leida positsiooni kõigi nullini ulatuvate väärtuste jaoks, nagu allpool selgitatud.
Juhusliku väärtuse genereerimiseks abistajate tabeli kaalutud tõenäosust kasutades sisaldab F5 seda valemit, mis on kopeeritud:
=MATCH(RAND(),D$5:D$10)
MATCHi sees annab otsingu väärtuse funktsioon RAND. RAND genereerib juhusliku väärtuse vahemikus null kuni 1. Otsingumassiiv on vahemik D5: D10, lukustatud, nii et see ei muutu, kui valem kopeeritakse veergu.
Kolmas MATCH-i argument, vaste tüüp, on välja jäetud. Kui vaste tüüp on välja jäetud, tagastab MATCH suurima väärtuse positsiooni, mis on väiksem või võrdne otsingu väärtusega *. Praktilises tähenduses tähendab see, et funktsioon MATCH liigub mööda väärtusi D5: D10-s, kuni kuvatakse suurem väärtus, seejärel "astub tagasi" eelmisele positsioonile. Kui MATCH kohtab väärtust, mis on suurem kui D5: D10 suurim viimane väärtus: D10 (näites .7), tagastab see viimase positsiooni (näites 6). Nagu eespool mainitud, on D5: D10 esimene väärtus teadlikult null, et tagada, et alla 1 väärtused on otsingutabelis "kinni" ja tagastavad positsiooni 1.
* Otsinguvaliku väärtused tuleb sortida kasvavas järjekorras.
Juhuslik kaalutud teksti väärtus
Juhusliku kaalutud tekstiväärtuse (st mittearvulise väärtuse) tagastamiseks võite sisestada tekstiväärtused vahemikku B5: B10, seejärel lisada selle vahemiku väärtuse tagastamiseks INDEX, mis põhineb vastendusel MATCH
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Märkused
- Sellele lähenemisele sattusin foorumi postituses saidil mrexcel.com
- RAND on kõikuv funktsioon ja arvutab iga töölehe muudatuse korral uuesti
- Kui teil on juhuslikud väärtused, kasutage vajadusel valemi asendamiseks kleepige spetsiaalsed väärtused
