Splošna formula
=MATCH(RAND(),cumulative_probability)
Povzetek
Če želite ustvariti naključno število, ponderirano z dano verjetnostjo, lahko uporabite pomožno tabelo skupaj s formulo, ki temelji na funkcijah RAND in MATCH.
V prikazanem primeru je formula v F5:
=MATCH(RAND(),D$5:D$10)
Pojasnilo
Ta formula se opira na pomožno tabelo, ki je vidna v območju B4: D10. Stolpec B vsebuje šest številk, ki jih želimo kot končni rezultat. Stolpec C vsebuje utež verjetnosti, dodeljeno vsaki številki, vneseno v odstotkih. Stolpec D vsebuje kumulativno verjetnost, ustvarjeno s to formulo v D5, kopirano:
=SUM(D4,C4)
Upoštevajte, da kumulativno verjetnost namerno premikamo za eno vrstico navzdol, tako da je vrednost v D5 enaka nič. S tem želimo zagotoviti, da MATCH lahko najde položaj za vse vrednosti do nič, kot je razloženo spodaj.
Za generiranje naključne vrednosti s pomočjo utežene verjetnosti v pomožni tabeli F5 vsebuje to kopirano formulo:
=MATCH(RAND(),D$5:D$10)
Znotraj MATCH vrednost iskanja zagotavlja funkcija RAND. RAND ustvari naključno vrednost med nič in 1. Iskalna matrika je obseg D5: D10, zaklenjen, da se ne bo spreminjal, ko bo formula kopirana v stolpec.
Tretji argument za MATCH, vrsta ujemanja, je izpuščen. Ko je vrsta ujemanja izpuščena, MATCH vrne položaj največje vrednosti, ki je manjša ali enaka iskalni vrednosti *. V praksi to pomeni, da funkcija MATCH potuje vzdolž vrednosti v D5: D10, dokler ne zasledimo večje vrednosti, nato pa "stopimo nazaj" v prejšnji položaj. Ko MATCH naleti na vrednost, večjo od največje zadnje vrednosti v D5: D10 (.7 v primeru), vrne zadnji položaj (6 v primeru). Kot smo že omenili, je prva vrednost v D5: D10 namerno nič, da se zagotovi, da iskalne tabele "ujamejo" vrednosti pod .1 in vrnejo položaj 1.
* Vrednosti v območju iskanja je treba razvrstiti po naraščajočem vrstnem redu.
Naključno ponderirana besedilna vrednost
Če želite vrniti naključno tehtano besedilno vrednost (tj. Neštevilsko vrednost), lahko vnesete besedilne vrednosti v obseg B5: B10, nato pa dodate INDEX, da vrnete vrednost v tem obsegu, glede na položaj, ki ga vrne MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Opombe
- Ta pristop sem naletel v objavi na forumu na mrexcel.com
- RAND je spremenljiva funkcija in se bo preračunal z vsako spremembo delovnega lista
- Ko imate naključne vrednosti, uporabite prilepite posebne> vrednosti, da po potrebi zamenjate formulo
