Как сделать генератор случайных чисел в excel

Случайное число в Excel (генерация случайных чисел)

Иногда появляется такая необходимость придумать несколько случайных чисел, это может быть полезным для случайного распределения пронумевованных задач между сотрудникам, либо использовать генератор случайных чисел при формировании вопросов для тестирования и во многих других ситуациях. В Excel генерация случайных чисел может быть организована двумя способами: сгенерировать число программным методом (т.е. написать на VBA, что мы рассмотрим в следующих статьях) либо получить случайное число, воспользовавшись стандартной (встроенной) функцией Excel, что мы и рассмотрим в этой статье.

Предположим, мы хотим получить случайное число (а не выдумывать его из головы) в интервале от -10 до +10. В этом случае сгенерировать число нам поможет функция СЛУЧМЕЖДУ() — она возвращает случайное число в заданном диапазоне. В нашем примере это будет выглядеть так: =СЛУЧМЕЖДУ(-10;10) теперь можно протянуть эту формулу по необходимым нам ячейкам.

Вторая функция СЛЧИС() — возвращает случайное число большее (либо равное) 0 и меньшее 1 (изменяется при пересчете, F9). У функции СЛЧИС() в отличии от СЛУЧМЕЖДУ() нет аргументов, а значит для получении случайного числа от 0 до 1 нам нужно просто прописать в ячейке = СЛЧИС() .

Случайное число в Excel

Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже, будем благодарны.

Генератор случайных чисел Excel в функциях и анализе данных

У нас есть последовательность чисел, состоящая из практически независимых элементов, которые подчиняются заданному распределению. Как правило, равномерному распределению.

Сгенерировать случайные числа в Excel можно разными путями и способами. Рассмотрим только лучше из них.

Функция случайного числа в Excel

1. Функция СЛЧИС возвращает случайное равномерно распределенное вещественное число. Оно будет меньше 1, больше или равно 0.
2. Функция СЛУЧМЕЖДУ возвращает случайное целое число.

Рассмотрим их использование на примерах.

Выборка случайных чисел с помощью СЛЧИС

Данная функция аргументов не требует (СЛЧИС()).

Чтобы сгенерировать случайное вещественное число в диапазоне от 1 до 5, например, применяем следующую формулу: =СЛЧИС()*(5-1)+1.

Возвращаемое случайное число распределено равномерно на интервале [1,10].

При каждом вычислении листа или при изменении значения в любой ячейке листа возвращается новое случайное число. Если нужно сохранить сгенерированную совокупность, можно заменить формулу на ее значение.

1. Щелкаем по ячейке со случайным числом.
2. В строке формул выделяем формулу.
3. Нажимаем F9. И ВВОД.

Проверим равномерность распределения случайных чисел из первой выборки с помощью гистограммы распределения.

1. Сформируем «карманы». Диапазоны, в пределах которых будут находиться значения. Первый такой диапазон – 0-0,1. Для следующих – формула =C2+$C$2.
2. Определим частоту для случайных чисел в каждом диапазоне. Используем формулу массива .
3. Сформируем диапазоны с помощью знака «сцепления» (=»[0,0-«&C2&»]»).
4. Строим гистограмму распределения 200 значений, полученных с помощью функции СЛЧИС ().

Диапазон вертикальных значений – частота. Горизонтальных – «карманы».

Функция СЛУЧМЕЖДУ

Синтаксис функции СЛУЧМЕЖДУ – (нижняя граница; верхняя граница). Первый аргумент должен быть меньше второго. В противном случае функция выдаст ошибку. Предполагается, что границы – целые числа. Дробную часть формула отбрасывает.

Пример использования функции:

Случайные числа с точностью 0,1 и 0,01:

Как сделать генератор случайных чисел в Excel

Сделаем генератор случайных чисел с генерацией значения из определенного диапазона. Используем формулу вида: =ИНДЕКС(A1:A10;ЦЕЛОЕ(СЛЧИС()*10)+1).

Сделаем генератор случайных чисел в диапазоне от 0 до 100 с шагом 10.

Из списка текстовых значений нужно выбрать 2 случайных. С помощью функции СЛЧИС сопоставим текстовые значения в диапазоне А1:А7 со случайными числами.

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

Чтобы выбрать одно случайное значение из списка, применим такую формулу: =ИНДЕКС(A1:A7;СЛУЧМЕЖДУ(1;СЧЁТЗ(A1:A7))).

Генератор случайных чисел нормального распределения

Функции СЛЧИС и СЛУЧМЕЖДУ выдают случайные числа с единым распределением. Любое значение с одинаковой долей вероятности может попасть в нижнюю границу запрашиваемого диапазона и в верхнюю. Получается огромный разброс от целевого значения.

Нормальное распределение подразумевает близкое положение большей части сгенерированных чисел к целевому. Подкорректируем формулу СЛУЧМЕЖДУ и создадим массив данных с нормальным распределением.

Себестоимость товара Х – 100 рублей. Вся произведенная партия подчиняется нормальному распределению. Случайная переменная тоже подчиняется нормальному распределению вероятностей.

При таких условиях среднее значение диапазона – 100 рублей. Сгенерируем массив и построим график с нормальным распределением при стандартном отклонении 1,5 рубля.

Используем функцию: =НОРМОБР(СЛЧИС();100;1,5).

Программа Excel посчитала, какие значения находятся в диапазоне вероятностей. Так как вероятность производства товара с себестоимостью 100 рублей максимальная, формула показывает значения близкие к 100 чаще, чем остальные.

Перейдем к построению графика. Сначала нужно составить таблицу с категориями. Для этого разобьем массив на периоды:

1. Определим минимальное и максимальное значение в диапазоне с помощью функций МИН и МАКС.
2. Укажем величину каждого периода либо шаг. В нашем примере – 1.
3. Количество категорий – 10.
4. Нижняя граница таблицы с категориями – округленное вниз ближайшее кратное число. В ячейку Н1 вводим формулу =ОКРВНИЗ(E1;E5).
5. В ячейке Н2 и последующих формула будет выглядеть следующим образом: =ЕСЛИ(G2;H1+$E$5;»»). То есть каждое последующее значение будет увеличено на величину шага.
6. Посчитаем количество переменных в заданном промежутке. Используем функцию ЧАСТОТА. Формула будет выглядеть так:

На основе полученных данных сможем сформировать диаграмму с нормальным распределением. Ось значений – число переменных в промежутке, ось категорий – периоды.

График с нормальным распределением готов. Как и должно быть, по форме он напоминает колокол.

Сделать то же самое можно гораздо проще. С помощью пакета «Анализ данных». Выбираем «Генерацию случайных чисел».

О том как подключить стандартную настройку «Анализ данных» читайте здесь.

Заполняем параметры для генерации. Распределение – «нормальное».

Жмем ОК. Получаем набор случайных чисел. Снова вызываем инструмент «Анализ данных». Выбираем «Гистограмма». Настраиваем параметры. Обязательно ставим галочку «Вывод графика».

График с нормальным распределением в Excel построен.

Функция СЛЧИС

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции СЛЧИС в Microsoft Excel.

Описание

Функция СЛЧИС возвращает равномерно распределенное случайное вещественное число, большее или равное 0, но меньшее 1. При каждом пересчете листа возвращается новое случайное вещественное число.

Примечание: начиная с версии Excel 2010, в Excel для генерации случайных чисел используется алгоритм «вихрь Мерсенна» (MT19937).

Синтаксис

У функции СЛЧИС нет аргументов.

Замечания

• Чтобы получить случайное вещественное число в диапазоне между a и b, можно использовать следующую формулу:

• Если требуется применить функцию СЛЧИС для генерации случайного числа, но изменение этого числа при каждом вычислении значения ячейки нежелательно, вы можете ввести в строке формулы =СЛЧИС(), а затем нажать клавишу F9, чтобы заменить формулу случайным числом. Формула вычисляется и оставляет только значение.

Пример

Скопируйте образец данных из приведенной ниже таблицы и вставьте их в ячейку A1 на новом листе Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.

Случайное число, большее или равное 0, но меньшее 1

Случайное число, большее или равное 0 и меньшее 100

Случайное целое число, большее или равное 0 и меньшее 100

Примечание. При пересчете листа после ввода формулы или данных в другую ячейку или при выполнении пересчета вручную (по нажатию клавиши F9) каждая формула, в которой используется функция СЛЧИС, создает новое случайное число.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Генератор случайных чисел в Excel в диапазоне

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

Функция случайного числа в Excel

Предположим, у нас есть набор данных, который должен содержать элементы, которые абсолютно не связаны друг с другом между собой. В идеале, чтобы они были сформированы по закону нормального распределения. Для этого нужно использовать функцию случайного числа. Есть две функции, с помощью которых можно достичь поставленной задачи: СЛЧИСЛ и СЛУЧМЕЖДУ. Давайте детально рассмотрим, как их можно использовать на практике.

Выборка случайных чисел с помощью СЛЧИС

Эта функция не предусматривает наличия каких-либо аргументов. Но несмотря на это она дает возможность настраивать диапазон значений, в рамках которых она должна генерировать случайное число. Например, чтобы получить его в рамках от единицы до пяти, нам необходимо использовать такую формулу: =СЛЧИС()*(5-1)+1.

Если эту функцию распределить на другие ячейки с помощью маркера автозаполнения, то мы увидим, что распределение осуществляется равномерно.

В ходе каждого расчета случайного значения, если в любом месте листа изменить какую-угодно ячейку, числа будут автоматически сгенерированы заново. Поэтому сохраняться эта информация не будет. Чтобы сделать так, чтобы они остались, необходимо вручную написать это значение в числовом формате или же воспользовавшись этой инструкцией.

1. Делаем клик по ячейке, содержащей случайное число.
2. Делаем клик по строке формул, после чего выделяем ее.
3. Нажимаем на кнопку F9 на клавиатуре.
4. Заканчиваем эту последовательность действий нажатием клавиши Enter.

Проверим то, насколько равномерно распределены случайные числа. Для этого нам нужно воспользоваться гистограммой распределения. Чтобы ее сделать, выполняем следующие шаги:

1. Создадим колонку с карманами, то есть, теми ячейками, в которых мы будем держать наши диапазоны. Первый такой – 0-0,1. Формируем следующие с помощью такой формулы: =C2+$C$2.
2. После этого нам надо определить, насколько часто встречаются случайные числа, относящиеся к каждому конкретному диапазону. Для этого мы можем использовать формулу массива .
3. Далее, с использованием знака «сцепления» делаем наши следующие диапазоны. Формула простая =»[0,0-«&C2&»]».
4. Теперь мы делаем диаграмму, описывающую то, как распределяются эти 200 значений.

В описанном нами примере частота соответствует оси Y, а «карманы» – оси X.

Функция СЛУЧМЕЖДУ

Если говорить про функцию СЛУЧМЕЖДУ, то согласно ее синтаксису, у нее есть два аргумента: нижняя граница и верхняя граница. Важно, чтобы величина первого параметра была меньше, чем второго. Допускается, что границами могут быть целые числа, а дробные формулой не учитываются. Давайте посмотрим, как эта функция работает, на этом скриншоте.

Видим, что точность можно регулировать с помощью деления. Можно получать случайные числа с любой разрядностью после запятой.

Видим, что эта функция гораздо более органична и понятна для обычного человека, чем предыдущая. Поэтому в большинстве случаев можно использовать только ее.

Как сделать генератор случайных чисел в Excel

А теперь давайте сделаем небольшой генератор чисел, который будет получать значения, основываясь на определенном диапазоне данных. Для этого применяется формула =ИНДЕКС(A1:A10;ЦЕЛОЕ(СЛЧИС()*10)+1).

Создадим генератор случайных чисел, которые будут создаваться от нуля до 10. С помощью этой формулы мы можем регулировать шаг, с которым они будут создаваться. Например, можно создать генератор, который будет создавать только значения, заканчивающиеся на ноль.

Или же такой вариант. Давайте предположим, что нам надо выделить два случайных значения из перечня текстовых ячеек.

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

Формула, с помощью которой мы это сделали, приведена на скриншоте выше. =ИНДЕКС(A1:A7;СЛУЧМЕЖДУ(1;СЧЁТЗ(A1:A7))) – с помощью этой формулы мы можем создать генератор для одного текстового значения. Видим, что мы спрятали вспомогательную колонку. Так можете сделать и вы.

Генератор случайных чисел нормального распределения

Проблема функций СЛЧИС и СЛУЧМЕЖДУ в том, что они формируют набор чисел, которые находятся очень далеко от целевого показателя. Вероятность того, что появится число, близкое к нижней границе, середине или верхней границе, одинаковая.

Нормальное распределение в статистике – это такой набор данных, в которых по мере большей отдаленности от центра на графике частота, с которой встречается значение в определенном коридоре, уменьшается. То есть, большая часть значений скапливается вокруг центрального. Давайте с помощью функции СЛУЧМЕЖДУ попробуем создать набор чисел, распределение которых относится к разряду нормального.

Итак, у нас есть товар, производство которого стоит 100 рублей. Следовательно, числа должны генерироваться приблизительно такие же. В этом случае средним значением должно быть 100 рублей. Создадим массив данных, и создадим график, в котором стандартное отклонение составит 1,5 рубля, а распределение значений – нормальное.

Для этого нужно использовать функцию =НОРМОБР(СЛЧИС();100;1,5). Далее программа автоматически меняет вероятности, исходя из того, что самый высокий шанс имеют числа, приближенные к сотне.

Теперь нам осталось лишь построить график стандартным способом, выбрав в качестве диапазона набор сгенерированных значений. В результате, мы видим, что распределение действительно является нормальным.

Вот так все просто. Успехов.