Formuła ogólna
=MATCH(RAND(),cumulative_probability)
Podsumowanie
Aby wygenerować liczbę losową ważoną z zadanym prawdopodobieństwem, można użyć tabeli pomocniczej wraz z formułą opartą na funkcjach RAND i MATCH.
W pokazanym przykładzie formuła w F5 to:
=MATCH(RAND(),D$5:D$10)
Wyjaśnienie
Ta formuła opiera się na tabeli pomocniczej widocznej w zakresie B4: D10. Kolumna B zawiera sześć liczb, które chcemy uzyskać jako wynik końcowy. Kolumna C zawiera wagę prawdopodobieństwa przypisaną każdej liczbie, wprowadzoną w procentach. Kolumna D zawiera skumulowane prawdopodobieństwo, utworzone za pomocą tej formuły w D5, skopiowane w dół:
=SUM(D4,C4)
Zauważ, że celowo przesuwamy skumulowane prawdopodobieństwo o jeden wiersz w dół, tak aby wartość w D5 wynosiła zero. Ma to na celu upewnienie się, że funkcja MATCH jest w stanie znaleźć pozycję dla wszystkich wartości aż do zera, jak wyjaśniono poniżej.
Aby wygenerować wartość losową, używając prawdopodobieństwa ważonego w tabeli pomocniczej, F5 zawiera tę formułę skopiowaną w dół:
=MATCH(RAND(),D$5:D$10)
Wewnątrz MATCH wartość wyszukiwania jest udostępniana przez funkcję RAND. RAND generuje losową wartość z przedziału od zera do 1. Tablica wyszukiwania to zakres D5: D10, zablokowana, więc nie zmieni się, gdy formuła jest kopiowana w dół kolumny.
Trzeci argument MATCH, typ dopasowania, jest pomijany. Jeśli typ dopasowania zostanie pominięty, PODAJ.POZYCJĘ zwróci pozycję największej wartości mniejszej lub równej wartości wyszukiwania *. W praktyce oznacza to, że funkcja MATCH przemieszcza się wzdłuż wartości w D5: D10 aż do napotkania większej wartości, a następnie „cofa się” do poprzedniej pozycji. Kiedy PODAJ.POZYCJĘ napotka wartość większą niż największa ostatnia wartość w D5: D10 (.7 w przykładzie), zwraca ostatnią pozycję (6 w przykładzie). Jak wspomniano powyżej, pierwsza wartość w D5: D10 jest celowo równa zero, aby zapewnić, że wartości poniżej .1 zostaną „przechwycone” przez tablicę przeglądową i zwrócą pozycję 1.
* Wartości w zakresie wyszukiwania muszą być sortowane w kolejności rosnącej.
Losowa ważona wartość tekstowa
Aby zwrócić losową ważoną wartość tekstową (tj. Wartość nienumeryczną), możesz wprowadzić wartości tekstowe z zakresu B5: B10, a następnie dodać INDEKS, aby zwrócić wartość z tego zakresu, na podstawie pozycji zwróconej przez PODAJ.POZYCJĘ:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Uwagi
- Wpadłem na to podejście w poście na forum na mrexcel.com
- RAND jest funkcją zmienną i będzie obliczana ponownie przy każdej zmianie arkusza
- Gdy masz już losowe wartości, użyj opcji wklej wartości specjalne>, aby w razie potrzeby zastąpić formułę
