Formula generică
=MATCH(RAND(),cumulative_probability)
rezumat
Pentru a genera un număr aleatoriu, ponderat cu o probabilitate dată, puteți utiliza un tabel de ajutor împreună cu o formulă bazată pe funcțiile RAND și MATCH.
În exemplul prezentat, formula din F5 este:
=MATCH(RAND(),D$5:D$10)
Explicaţie
Această formulă se bazează pe masa asistentă vizibilă în intervalul B4: D10. Coloana B conține cele șase numere pe care le dorim ca rezultat final. Coloana C conține ponderea probabilității atribuită fiecărui număr, introdus ca procent. Coloana D conține probabilitatea cumulată, creată cu această formulă în D5, copiată:
=SUM(D4,C4)
Observați, mutăm intenționat probabilitatea cumulativă în jos cu un rând, astfel încât valoarea din D5 să fie zero. Aceasta este pentru a vă asigura că MATCH este capabil să găsească o poziție pentru toate valorile până la zero, așa cum se explică mai jos.
Pentru a genera o valoare aleatorie, utilizând probabilitatea ponderată în tabelul de ajutor, F5 conține această formulă, copiată:
=MATCH(RAND(),D$5:D$10)
În MATCH, valoarea de căutare este furnizată de funcția RAND. RAND generează o valoare aleatorie între zero și 1. Matricea de căutare este intervalul D5: D10, blocat, deci nu se va modifica deoarece formula este copiată în coloană.
Al treilea argument pentru MATCH, tipul meciului, este omis. Când tipul de potrivire este omis, MATCH va returna poziția celei mai mari valori mai mică sau egală cu valoarea de căutare *. În termeni practici, aceasta înseamnă că funcția MATCH se deplasează de-a lungul valorilor din D5: D10 până când se întâlnește o valoare mai mare, apoi „se întoarce” la poziția anterioară. Când MATCH întâlnește o valoare mai mare decât cea mai mare valoare din D5: D10 (.7 în exemplu), returnează ultima poziție (6 în exemplu). După cum s-a menționat mai sus, prima valoare din D5: D10 este în mod deliberat zero pentru a se asigura că valorile de sub .1 sunt „capturate” de tabelul de căutare și returnează o poziție de 1.
* Valorile din intervalul de căutare trebuie să fie sortate în ordine crescătoare.
Valoarea textului ponderat aleatoriu
Pentru a returna o valoare de text ponderată aleatoriu (adică o valoare nenumerică), puteți introduce valori de text în intervalul B5: B10, apoi adăugați INDEX pentru a returna o valoare în intervalul respectiv, pe baza poziției returnate de MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Note
- Am întâlnit această abordare într-o postare pe forum pe mrexcel.com
- RAND este o funcție volatilă și se va recalcula la fiecare modificare a foii de lucru
- După ce aveți valori aleatorii, utilizați paste speciale> valori pentru a înlocui formula, dacă este necesar
