Обща формула
=MATCH(RAND(),cumulative_probability)
Обобщение
За да генерирате произволно число, претеглено с дадена вероятност, можете да използвате помощна таблица заедно с формула, базирана на функциите RAND и MATCH.
В показания пример формулата във F5 е:
=MATCH(RAND(),D$5:D$10)
Обяснение
Тази формула разчита на помощната таблица, видима в диапазона B4: D10. Колона Б съдържа шестте числа, които искаме като краен резултат. Колона 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 е променлива функция и ще се преизчислява при всяка промяна на работния лист
- След като имате произволни стойности, използвайте paste> special values, за да замените формулата, ако е необходимо
