Использование Excel для вычисления взвешенной вероятности случайных чисел

Контент

Excel может возвращать значение случайным образом в зависимости от его вероятности с помощью функций СУММ, ПОИСКПОЗ и СЛЧИС. Это пошаговое руководство поможет пользователям Excel всех уровней случайным образом создать список значений на основе их вероятности быть выбранным.

Окончательная формула:

Рисунок 1. Генерация случайных значений с использованием функций ПОИСКПОЗ и СЛЧИС.

Создание кумулятивной вероятности

  • Сначала мы настраиваем наши данные так, чтобы у нас было значение, которое должно быть возвращено в столбце A, вероятность в столбце B и пустой столбец C.
  • В ячейке C2 мы набираем «0». В ячейке C3 мы вводим эту формулу = sum (B1, C1)и копируем формулу в конец наших данных.
  • Обратите внимание, как мы намеренно смещаем формулу на 1 строку. Это потому, что 0 начинает диапазон между 0 и 1, поэтому первое значение в столбце C должно быть равно нулю. Затем остальные ячейки в столбце C сложат предыдущие проценты вместе, чтобы сформировать процентное приращение, которое представляет вероятность того, что каждое значение будет выбрано.
  • Возьмите разницу между 85% и 55%, что составляет 30%. Это указанная вероятность выбора значения 4.

Рисунок 2. Генерация случайных значений с использованием функций ПОИСКПОЗ и СЛЧИС.

Теперь, когда у нас создана кумулятивная вероятность и мы знакомы с функцией ПОИСКПОЗ, теперь мы можем использовать функцию СЛЧИС для создания списка случайных чисел от 0 до 1 и нахождения ближайшего нижнего совпадения случайного числа. Увидеть ниже

Поиск значения с помощью функции MATCH

Функция сопоставления возвращает СТРОКУ, в которой найдено наиболее близкое совпадение данных. Мы можем использовать функцию ПОИСКПОЗ, чтобы найти значение, которое находится в диапазоне вероятности. Чтобы выполнить эту задачу с использованием других данных, нам нужно будет использовать функцию ИНДЕКС, которая будет объяснена позже в статье.

Синтаксис функции ПОИСКПОЗ:

= ПОИСКПОЗ (значение поиска, массив таблицы поиска, приблизительное совпадение [Истина / Ложь])

  • Введите вероятность поиска в ячейку E2.
  • В ячейке E4 введите эту формулу

    = соответствие (E2, C2: C6)
  • В этом примере формула вернула значение 2, потому что 16% находится между 15% и 45%. Как только значение в ячейке E2 станет 45% или больше, новое возвращаемое значение в E4 станет значением 3 и т. Д.

Примечание. Это будет работать только в том случае, если данные пронумерованы в соответствии с количеством доступных значений. Следовательно, если бы это было пять строк данных, значения должны были бы быть 1, 2, 3, 4 и 5 в порядке возрастания. Для других типов данных нам нужно будет использовать функцию ИНДЕКС, описанную далее в статье.

* Предыдущая серия шагов была использована, чтобы проиллюстрировать, как функция соответствия ищет данные. В следующей серии шагов мы сгенерируем несколько случайных значений в столбце E, добавив RAND к нашей формуле MATCH, чтобы сгенерировать более динамическую функцию сопоставления.

Поиск значения с помощью функции RAND

Функция СЛЧИС возвращает случайное значение от 0 до 1. Мы будем использовать функцию RAND для разработки случайного поиска.

  • В ячейке E2 введите эту формулу

    = ПОИСКПОЗ (СЛЧИС (), $ C $ 2: $ C $ 6)
  • Обязательно создайте абсолютные ссылки (используя знак '$') в массиве данных и скопируйте формулу в ячейку E10.
  • Формула возвращает соответствующее значение ROW из столбца A на основе ROW, где столбец C соответствует случайному числу.

Бонусный шаг:

Если ваш список значений представляет собой текст вместо чисел или не пронумерован последовательно с данными, вы можете добавить функцию ИНДЕКС в формулу для случайного поиска текстового значения на основе его вероятности.

Поиск текстового значения с помощью функции индекса

  • Функция ИНДЕКС возвращает значение на основе СТРОКИ и СТОЛБЦА, указанных пользователем - Пример: «= Индекс (1,1)» возвращает значение в ячейке A1 (СТРОКА 1, СТОЛБЕЦ 1).
  • Мы можем использовать эту концепцию, чтобы найти соответствующую строку, вложив функции ИНДЕКС, ПОИСКПОЗ и СЛУЧАЙ
  • В ячейке E2 введите эту формулу

    = ИНДЕКС ($ A $ 2: $ A $ 6, MATCH (RAND (), $ C $ 2: $ C $ 6))
  • Формула возвращает текстовое значение в столбце A в зависимости от вероятности его выбора.
  • Обязательно создайте абсолютные ссылки на свои массивы данных и скопируйте формулу в ячейку E10.

Мгновенное соединение с экспертом через нашу службу Excelchat:

В большинстве случаев проблема, которую вам нужно будет решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы на исследованиях и разочарованиях, попробуйте нашу живую службу Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любые ваши вопросы по Excel. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.