Загальна формула
=MATCH(RAND(),cumulative_probability)
Резюме
Щоб сформувати випадкове число, зважене з заданою ймовірністю, ви можете використовувати допоміжну таблицю разом із формулою на основі функцій RAND та MATCH.
У наведеному прикладі формула в F5 має вигляд:
=MATCH(RAND(),D$5:D$10)
Пояснення
Ця формула спирається на допоміжну таблицю, видиму в діапазоні B4: D10. Стовпець B містить шість чисел, які ми хочемо отримати як кінцевий результат. Стовпець C містить вагу ймовірності, присвоєну кожному числу, введену у відсотках. Стовпець D містить сукупну ймовірність, створену за допомогою цієї формули в D5, скопійовану:
=SUM(D4,C4)
Зверніть увагу, ми навмисно зміщуємо сукупну ймовірність вниз на один рядок, так що значення в D5 дорівнює нулю. Це для того, щоб переконатися, що MATCH може знайти позицію для всіх значень до нуля, як це пояснено нижче.
Для генерації випадкового значення, використовуючи зважену ймовірність у допоміжній таблиці, F5 містить цю формулу, скопійовану вниз:
=MATCH(RAND(),D$5:D$10)
Усередині MATCH значення пошуку забезпечується функцією RAND. RAND генерує випадкове значення між нулем і 1. Масивом пошуку є діапазон D5: D10, заблокований, тому він не зміниться, коли формула копіюється вниз у стовпець.
Третій аргумент для MATCH, тип відповідності, опущений. Коли тип відповідності опущений, MATCH повертає позицію найбільшого значення, менше або дорівнює значенню підстановки *. На практиці це означає, що функція MATCH рухається вздовж значень у D5: D10, поки не зустрінеться більше значення, а потім "повернеться назад" до попередньої позиції. Коли MATCH зустрічає значення, яке перевищує найбільше останнє значення в D5: D10 (.7 у прикладі), воно повертає останню позицію (6 у прикладі). Як згадувалося вище, перше значення в D5: D10 навмисно дорівнює нулю, щоб гарантувати, що значення нижче .1 "потрапляють" в таблицю пошуку і повертають позицію 1.
* Значення в діапазоні пошуку потрібно сортувати за зростанням.
Випадкове зважене текстове значення
Щоб повернути випадкове зважене текстове значення (тобто нечислове значення), ви можете ввести текстові значення в діапазоні B5: B10, а потім додати INDEX, щоб повернути значення в цьому діапазоні, на основі позиції, повернутої за допомогою MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Примітки
- Я зіткнувся з цим підходом у дописі на форумі на mrexcel.com
- RAND - це нестабільна функція, і вона перераховуватиметься при кожній зміні аркуша
- Отримавши випадкові значення, використовуйте спеціальні> значення, щоб замінити формулу, якщо це необхідно
