Všeobecný vzorec
=MATCH(RAND(),cumulative_probability)
Zhrnutie
Na vygenerovanie náhodného čísla váženého s danou pravdepodobnosťou môžete použiť pomocnú tabuľku spolu so vzorcom založeným na funkciách RAND a MATCH.
V zobrazenom príklade je vzorec v F5:
=MATCH(RAND(),D$5:D$10)
Vysvetlenie
Tento vzorec sa spolieha na pomocnú tabuľku viditeľnú v rozsahu B4: D10. Stĺpec B obsahuje šesť čísel, ktoré chceme ako konečný výsledok. Stĺpec C obsahuje váhu pravdepodobnosti priradenú každému číslu uvedenú v percentách. Stĺpec D obsahuje kumulatívnu pravdepodobnosť vytvorenú pomocou tohto vzorca v D5 a skopírovanú nadol:
=SUM(D4,C4)
Všimnite si, že zámerne posúvame kumulatívnu pravdepodobnosť o jeden riadok nižšie, takže hodnota v D5 je nula. To zaisťuje, že MATCH je schopný nájsť pozíciu pre všetky hodnoty až po nulu, ako je vysvetlené nižšie.
Na generovanie náhodnej hodnoty pomocou váženej pravdepodobnosti v pomocnej tabuľke obsahuje F5 tento vzorec skopírovaný nadol:
=MATCH(RAND(),D$5:D$10)
Vo vnútri MATCH je vyhľadávacia hodnota poskytnutá funkciou RAND. RAND generuje náhodnú hodnotu medzi nulou a 1. Vyhľadávacím poľom je rozsah D5: D10, uzamknutý, takže sa nezmení, keď sa vzorec skopíruje dolu v stĺpci.
Tretí argument pre MATCH, typ zhody, je vynechaný. Keď je typ zhody vynechaný, MATCH vráti pozíciu najväčšej hodnoty menšiu alebo rovnú vyhľadávacej hodnote *. Z praktického hľadiska to znamená, že funkcia MATCH postupuje pozdĺž hodnôt v D5: D10, kým nenarazí na väčšiu hodnotu, potom sa „vráti späť“ na predchádzajúcu pozíciu. Keď MATCH zistí hodnotu väčšiu ako najväčšia posledná hodnota v D5: D10 (0,7 v príklade), vráti poslednú pozíciu (6 v príklade). Ako už bolo spomenuté vyššie, prvá hodnota v D5: D10 je zámerne nulová, aby sa zabezpečilo, že hodnoty pod .1 sú „zachytené“ vyhľadávacou tabuľkou a vrátia pozíciu 1.
* Hodnoty v rozsahu vyhľadávania musia byť zoradené vzostupne.
Náhodne vážená textová hodnota
Ak chcete vrátiť náhodnú váženú textovú hodnotu (tj. Nečíselnú hodnotu), môžete zadať textové hodnoty v rozsahu B5: B10 a potom pridať INDEX, aby ste vrátili hodnotu v tomto rozsahu, na základe polohy vrátenej MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Poznámky
- Na tento prístup som narazil v príspevku na fóre na stránke mrexcel.com
- RAND je volatilná funkcia a bude sa prepočítavať pri každej zmene pracovného hárka
- Keď budete mať náhodné hodnoty, v prípade potreby vzorec nahraďte špeciálnymi> hodnotami
