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

Например, при тестировании каких-нибудь методик аналитики данных разрабатываемых Вами нужно, чтобы ячейка принимала значения от 10 до 200.

В таком случае Вам следует воспользоваться функцией Excel «СЛУЧМЕЖДУ( ; )». Эта функция присваивает ячейке рандомное/случайное значение из указанного диапазона значений.

Способ применения функции «СЛУЧМЕЖДУ( ; )»:

1. Установить курсор в ячейку, которой присваиваете значение;
2. Выбрать функцию «СЛУЧМЕЖДУ( ; )»;
3. В меню указать начальное и конечное число диапазона или ячейки, содержащие эти числа;
4. Нажать «ОК»

Наряду с функцией «СЛУЧМЕЖДУ» существует «СЛЧИС()», эта функция в отличие от «СЛУЧМЕЖДУ» выбирает случайное число из диапазона от 0 до 1. То есть присваивает ячейке случайное дробное число до единицы.

Способ применения функции «СЛЧИС()»:

1. Установить курсор в ячейку, которой присваиваете значение;
2. Выбрать функцию «СЛЧИС()»;
3. Нажать «ОК»

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

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

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

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

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

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

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

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

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

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

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

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

1. Сформируем «карманы». Диапазоны, в пределах которых будут находиться значения. Первый такой диапазон – 0-0,1. Для следующих – формула =C2+$C$2.
2. Определим частоту для случайных чисел в каждом диапазоне. Используем формулу массива {=ЧАСТОТА(A2:A201;C2:C11)}.
3. Сформируем диапазоны с помощью знака «сцепления» (=»»).
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 построен.

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

Чаще всего эта функция применяется для получения большого количества случайных чисел. Т.е. 2-3 числа всегда можно придумать самому, для большого количества проще всего применить функцию. В большинстве языков программирования подобная функция известная как Random (от англ. случайный), поэтому часто можно встретить обрусевшее выражение «в рандомном порядке» и т.п. В английском Excel функция СЛЧИС числится как RAND

Функция СЛЧИС(). Описание и как найти случайное число в Excel

Начнем с описания функции =СЛЧИС(). Для этой функции не нужны аргументы.

А работает она следующим образом — выводит случайное число от 0 до 1. Число будет вещественное, т.е. по большому счету любое, как правило это десятичные дроби, например 0,0006.

При каждом сохранении число будет меняться, чтобы обновить число без обновления нажмите F9.

Случайное число в определенном диапазоне. Функция

Что делать если вам не подходит имеющийся диапазон случайных чисел, и нужно набор случайных чисел от 20 до 135. Как это можно сделать?

Нужно записать следующую формулу.

=СЛЧИС()*115+20

Т.е. к 20 будет случайным образом прибавляться число от 0 до 115, что позволит получать каждый раз число в нужном диапазоне (см. первую картинку).

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

=СЛУЧМЕЖДУ(20;135)

Просто, но очень удобно!

Если нужно множество ячеек случайных чисел просто протяните ячейку ниже.

Случайное число с определенным шагом

Если нам нужно получить рандомное число с шагом, к примеру пять, то мы воспользуемся одной из функций округления. Это будет ОКРВВЕРХ()

=ОКРВВЕРХ(СЛЧИС()*50;5)

Где мы находим случайное число от 0 до 50, а затем округляем его до ближайшего сверху значения кратного 5. Удобно, когда вы делаете расчет для комплектов по 5 штук.

Как применять рандом для проверки модели?

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

Решено вынести эту тему в отдельную статью. Следите за обновлениями, будет на этой неделе.

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

Если вам необходим записать макрос и вы не знаете как это сделать, то можно прочитать здесь.

В VBA применяется функция Rnd(), при этом она не будет работать без включения команды Randomize для запуска генератора случайных чисел. Рассчитаем случайное число от 20 до 135 макросом.

Sub MacroRand()  Randomize   Range("A24") = Rnd * 115 + 20  End Sub

Вставьте этот код в редактор VBA (Alt + F11)

Как и всегда, прикладываю пример* со всеми вариантами расчета.

*Ссылка с моего Яндекс.Диска, т.к. движок сайта не позволяет выкладывать файлы с макросами из соображений безопасности.

Пишите комментарии, если у вас есть вопросы!

Поделитесь нашей статьей в ваших соцсетях:

(Visited 1 424 times, 6 visits today)

В Excel можно случайным образом выбрать трех победителей — 1-го, 2-го и 3-го — из списка в электронной таблице. Самый простой и честный способ сделать это — использовать функцию СЛЧИС (RAND) Excel совместно с его возможностями сортировки.

Предположим, что в вашей электронной таблице есть таблица из трех столбцов начиная со столбца В с заголовками столбцов Name, Age и ID No. Можно поместить функцию СЛЧИС (RAND) в ячейку А2 и скопировать ее вниз на необходимое количество строк до конца таблицы. Все ячейки в столбце А, в которые вы поместили функцию СЛЧИС (RAND), автоматически вернут случайное число, по которому вы сможете отсортировать таблицу. Другими словами, можно отсортировать столбцы А, В, С и D по столбцу А по возрастанию или убыванию, и тремя победителями будут три верхних имени.

Функция СЛЧИС (RAND) — это пересчитываемая функция, которая автоматически пересчитывается, какое бы действие в Excel ни произошло, — например, ввод данных или принудительный запуск пересчета клавишей F9. Поэтому лучше по-быстрее записать имена победителей.

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

Предположим, например, у вас есть данные в столбцах В, С и D, и в строке 1 содержатся заголовки. Сначала в ячейку А1 поместите заголовок RAND. В ячейку А2 введите формулу =RAND() и скопируйте ее вниз на необходимое количество ячеек. После этого выделите любую ячейку и выберите команду Разработчик → Начать запись (Developer → Record New Macro).

Выделите столбцы А, В, С и D и нажмите клавишу F9 (чтобы запустить пересчет). Выберите команду Данные → Сортировка (Home → Sort) и отсортируйте данные по столбцу А. Остановите запись макроса. Теперь выберите команду Файл → Панели инструментов → Формы (File → Toolbars → Forms). На этой панели инструментов выберите кнопку и поместите ее в любое место на рабочем листе. Назначьте этой кнопке только что записанный макрос и щелкните кнопку ОК. (Если хотите, измените текст на кнопке с Кнопка 1 (Button 1) на что-то более осмысленное.) Можно выделить столбец А и полностью скрыть его, так как пользователям не нужно видеть сгенерированные случайные числа. Каждый раз когда вы будете щелкать кнопку, данные будут сортироваться случайным образом и вы сможете просто прочитать три верхних имени победителей.

У функции

СЛЧИС (RAND) в Excel 2003 есть большой недостаток. Хотя в справке ясно указано, что возвращаются случайные числа от 0 до 1, это не всегда так, в частности, когда функция

СЛЧИС (RAND) используется в нескольких ячейках. Иногда она возвращает числа, меньшие 0. Прочитать объяснение Microsoft по поводу изменения алгоритма можно на сайте по адресу:

http://support.microsoft.com/default.aspx?kbid=828795

.