Содержание:
Время от времени пользователям Excel необходимо генерировать случайные числа, чтобы использовать их в формулах или для других целей. Для этого в программе предусмотрен целый арсенал возможностей. Генерировать случайные числа можно разными способами. Мы приведем только те, которые показали себя на практике наилучшим образом.
Функция случайных чисел в Excel
Предположим, у нас есть набор данных, который должен содержать элементы, абсолютно не связанные друг с другом. В идеале они должны формироваться по закону нормального распределения. Для этого вам нужно использовать функцию случайных чисел. Есть две функции, с помощью которых вы можете достичь своей цели: РАСЧЕТ и МЕЖДУ ДЕЛОМ. Давайте подробнее рассмотрим, как их можно использовать на практике.
Выбор случайных чисел с помощью RAND
Эта функция не предоставляет никаких аргументов. Но несмотря на это, он позволяет настроить диапазон значений, в пределах которого он должен генерировать случайное число. Например, чтобы получить его в рамках одного к пяти, нам нужно воспользоваться следующей формулой: =СЧЕТ()*(5-1)+1.
Если эту функцию распределить по другим ячейкам с помощью маркера автозаполнения, то мы увидим, что распределение равномерное.
Если во время каждого расчета случайной величины вы измените какую-либо ячейку в любом месте листа, числа будут автоматически сгенерированы заново. Поэтому эта информация не будет сохранена. Чтобы убедиться, что они остались, необходимо вручную записать это значение в числовом формате, либо воспользоваться этой инструкцией.
- Делаем клик по ячейке, содержащей случайное число.
- Делаем клик по строке формул, а затем выделяем ее.
- Нажмите кнопку F9 на клавиатуре.
- Завершаем эту последовательность действий нажатием клавиши Enter.
Проверим, насколько равномерно распределены случайные числа. Для этого нам нужно использовать гистограмму распределения. Чтобы сделать это, выполните следующие действия:
- Давайте создадим столбец с карманами, то есть теми ячейками, в которых мы будем хранить наши диапазоны. Первый – 0-0,1. По этой формуле формируем: =C2+$C$2.
- После этого нам необходимо определить, как часто встречаются случайные числа, связанные с каждым конкретным диапазоном. Для этого мы можем использовать формулу массива {=ЧАСТОТА(A2:A201;C2:C11)}.
- Далее, используя знак «сцепление», делаем наши следующие диапазоны. Формула проста =»[0,0-«&C2&»]».
- Теперь мы составляем диаграмму, описывающую, как распределяются эти 200 значений.
В нашем примере частота соответствует оси Y, а «карманы» — оси X.
МЕЖДУ функцией
Говоря о функции МЕЖДУ ДЕЛОМ, то, согласно его синтаксису, он имеет два аргумента: нижнюю границу и верхнюю границу. Важно, чтобы значение первого параметра было меньше второго. Предполагается, что границы могут быть целыми числами, дробные формулы не учитываются. Давайте посмотрим, как работает эта функция на этом скриншоте.
Мы видим, что точность можно регулировать с помощью деления. Вы можете получить случайные числа с любыми цифрами после запятой.
Мы видим, что эта функция гораздо более органична и понятна обычному человеку, чем предыдущая. Поэтому в большинстве случаев можно использовать только его.
Как сделать генератор случайных чисел в Excel
А теперь давайте сделаем небольшой генератор чисел, который будет получать значения на основе определенного диапазона данных. Для этого применим формулу =ИНДЕКС(A1:A10,ЦЕЛОЕ(СЛЧИС()*10)+1). 
Давайте создадим генератор случайных чисел, который будет генерироваться от нуля до 10. Используя эту формулу, мы можем контролировать шаг, с которым они будут генерироваться. Например, вы можете создать генератор, который будет выдавать только значения с нулевым завершением. 
Или такой вариант. Допустим, мы хотим выбрать два случайных значения из списка текстовых ячеек. 
А чтобы выбрать два случайных числа, нужно применить функцию ИНДЕКС. 
Формула, с помощью которой мы это сделали, показана на скриншоте выше. =ИНДЕКС(A1:A7;СЛУЧМЕЖДУ(1;СЧЁТЗ(A1:A7))) – с помощью этой формулы мы можем создать генератор для одного текстового значения. Мы видим, что скрыли вспомогательный столбец. И ты можешь. 
Генератор случайных чисел с нормальным распределением
Проблема с функцией СЛЧИС и МЕЖДУ ДЕЛОМ в том, что они образуют набор чисел, очень далеких от цели. Вероятность того, что число окажется близким к нижнему, среднему или верхнему пределу, одинакова.
Нормальное распределение в статистике — это набор данных, в котором по мере увеличения расстояния от центра графика частота появления значения в определенном коридоре уменьшается. То есть большая часть значений скапливается вокруг центрального. Давайте воспользуемся функцией МЕЖДУ ДЕЛОМ Попробуем создать набор чисел, распределение которых относится к категории нормального.
Итак, у нас есть товар, производство которого обходится в 100 руб. Поэтому цифры должны генерироваться примерно одинаковыми. В этом случае среднее значение должно составлять 100 рублей. Создадим массив данных и создадим график, на котором стандартное отклонение равно 1,5 рубля, а распределение значений нормальное.
Для этого нужно использовать функцию =НОРМОНИМ(SLNUMBER();100;1,5). Далее программа автоматически меняет вероятности, исходя из того, что числа, близкие к сотне, имеют наибольшую вероятность.
Теперь нам просто нужно стандартным способом построить график, выбрав в качестве диапазона набор сгенерированных значений. В результате мы видим, что распределение действительно нормальное.
Это так просто. Удачи.