Obecný vzorec
=MATCH(RAND(),cumulative_probability)
souhrn
Chcete-li vygenerovat náhodné číslo vážené s danou pravděpodobností, můžete použít pomocnou tabulku spolu se vzorcem založeným na funkcích RAND a MATCH.
V zobrazeném příkladu je vzorec v F5:
=MATCH(RAND(),D$5:D$10)
Vysvětlení
Tento vzorec spoléhá na pomocnou tabulku viditelnou v rozsahu B4: D10. Sloupec B obsahuje šest čísel, která chceme jako konečný výsledek. Sloupec C obsahuje pravděpodobnostní váhu přiřazenou každému číslu zadanou v procentech. Sloupec D obsahuje kumulativní pravděpodobnost vytvořenou tímto vzorcem v D5, zkopírovanou dolů:
=SUM(D4,C4)
Všimněte si, že záměrně posouváme kumulativní pravděpodobnost o jeden řádek dolů, takže hodnota v D5 je nula. Tím je zajištěno, že MATCH je schopen najít pozici pro všechny hodnoty až na nulu, jak je vysvětleno níže.
Chcete-li vygenerovat náhodnou hodnotu, pomocí vážené pravděpodobnosti v pomocné tabulce obsahuje F5 tento vzorec zkopírovaný dolů:
=MATCH(RAND(),D$5:D$10)
Uvnitř MATCH je vyhledávací hodnota poskytována funkcí RAND. RAND generuje náhodnou hodnotu mezi nulou a 1. Vyhledávací pole je rozsah D5: D10, uzamčeno, takže se nezmění při kopírování vzorce dolů do sloupce.
Třetí argument pro MATCH, typ shody, je vynechán. Když je typ shody vynechán, MATCH vrátí pozici největší hodnoty menší nebo rovnou vyhledávací hodnotě *. Z praktického hlediska to znamená, že funkce MATCH cestuje podél hodnot v D5: D10, dokud nenarazí na větší hodnotu, a poté „ustoupí“ na předchozí pozici. Když MATCH narazí na hodnotu větší než největší poslední hodnota v D5: D10 (0,7 v příkladu), vrátí poslední pozici (6 v příkladu). Jak již bylo zmíněno výše, první hodnota v D5: D10 je záměrně nulová, aby bylo zajištěno, že hodnoty pod .1 jsou „zachyceny“ vyhledávací tabulkou a vrátí pozici 1.
* Hodnoty v rozsahu vyhledávání musí být seřazeny vzestupně.
Náhodně vážená textová hodnota
Chcete-li vrátit náhodnou váženou textovou hodnotu (tj. Nečíselnou hodnotu), můžete zadat textové hodnoty v rozsahu B5: B10 a poté přidat INDEX pro vrácení hodnoty v tomto rozsahu na základě pozice vrácené MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Poznámky
- Na tento přístup jsem narazil v příspěvku na fóru na mrexcel.com
- RAND je volatilní funkce a přepočítá se při každé změně listu
- Jakmile budete mít náhodné hodnoty, použijte v případě potřeby vložení speciálních hodnot> k nahrazení vzorce
