Общая формула
=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)
Внутри ПОИСКПОЗ значение поиска предоставляется функцией СЛЧИС. RAND генерирует случайное значение от нуля до 1. Подстановочный массив - это диапазон D5: D10, заблокированный, поэтому он не будет изменяться при копировании формулы в столбец.
Третий аргумент MATCH, тип соответствия, опускается. Если тип соответствия опущен, ПОИСКПОЗ вернет позицию наибольшего значения, меньшего или равного искомому значению *. На практике это означает, что функция ПОИСКПОЗ перемещается по значениям в D5: D10 до тех пор, пока не встретится большее значение, а затем «возвращается» к предыдущей позиции. Когда ПОИСКПОЗ встречает значение, превышающее наибольшее последнее значение в D5: D10 (0,7 в примере), оно возвращает последнюю позицию (6 в примере). Как упоминалось выше, первое значение в D5: D10 намеренно равно нулю, чтобы гарантировать, что значения ниже .1 «пойманы» поисковой таблицей и вернут позицию 1.
* Значения в диапазоне поиска должны быть отсортированы в порядке возрастания.
Случайное взвешенное текстовое значение
Чтобы вернуть случайное взвешенное текстовое значение (то есть нечисловое значение), вы можете ввести текстовые значения в диапазоне B5: B10, а затем добавить INDEX, чтобы вернуть значение в этом диапазоне на основе позиции, возвращаемой MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Заметки
- Я столкнулся с этим подходом в сообщении на форуме на mrexcel.com
- RAND - это непостоянная функция, которая будет пересчитываться при каждом изменении рабочего листа.
- Если у вас есть случайные значения, используйте специальные> значения для вставки, чтобы при необходимости заменить формулу.
