Általános képlet
=MATCH(RAND(),cumulative_probability)
Összegzés
Adott valószínűséggel súlyozott véletlen szám előállításához használhat egy segítő táblázatot, valamint a RAND és a MATCH függvényeken alapuló képletet.
A bemutatott példában az F5 képlete a következő:
=MATCH(RAND(),D$5:D$10)
Magyarázat
Ez a képlet a B4: D10 tartományban látható segítő táblára támaszkodik. A B oszlop tartalmazza azt a hat számot, amelyet végeredményként szeretnénk. A C oszlop az egyes számokhoz rendelt valószínűségi súlyt tartalmazza, százalékban megadva. A D oszlop tartalmazza a kumulált valószínűséget, amelyet ezzel a képlettel hoztak létre a D5-ben, lemásolva:
=SUM(D4,C4)
Figyeljük meg, szándékosan eltoljuk a kumulatív valószínűséget egy sorral lefelé, így a D5 értéke nulla. Ez annak biztosítására szolgál, hogy a MATCH képes megtalálni az összes értéket nulláig, az alábbiakban leírtak szerint.
Véletlenszerű érték előállításához a segítő táblázat súlyozott valószínűségének felhasználásával az F5 ezt a képletet tartalmazza:
=MATCH(RAND(),D$5:D$10)
A MATCH belsejében a keresési értéket a RAND függvény biztosítja. A RAND véletlenszerű értéket állít elő nulla és 1 között. A lekérdező tömb a D5: D10 tartomány, zárva, így nem változik, amikor a képletet az oszlopba másolják.
A MATCH harmadik argumentuma, az egyezési típus, elmarad. Ha az egyezési típus elmarad, a MATCH a legnagyobb értéket adja vissza, amely kisebb vagy egyenlő a keresési értékkel *. Gyakorlatilag ez azt jelenti, hogy a MATCH függvény addig halad a D5: D10 értékek mentén, amíg nagyobb értéket nem talál, majd "visszalép" az előző pozícióba. Amikor a MATCH a D5: D10 legnagyobb utolsó értékénél nagyobb értékkel találkozik (.7 a példában), akkor az utolsó pozíciót adja vissza (a példában 6). Mint fent említettük, a D5: D10 első értéke szándékosan nulla annak biztosítására, hogy a .1 alatti értékeket "elkapja" a keresőtábla, és 1-es pozíciót adjon vissza.
* A keresési tartomány értékeit növekvő sorrendben kell rendezni.
Véletlenszerű súlyozott szövegérték
Véletlenszerű súlyozott szövegérték (azaz nem numerikus érték) visszaadásához megadhat szöveges értékeket a B5: B10 tartományba, majd hozzáadhatja az INDEX értéket, hogy visszatérjen az adott tartomány értékéhez, a MATCH által visszaadott pozíció alapján:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Megjegyzések
- A mrexcel.com fórumbejegyzésében futottam össze ezzel a megközelítéssel
- A RAND volatilis függvény, és minden munkalapváltozáskor újraszámolja
- Ha véletlenszerű értéke (i) van, használja a speciális> értékek beillesztését a képlet helyettesítésére, ha szükséges
