Eurotehnik.ru

Бытовая Техника "Евротехник"
21 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Создаем генератор случайных чисел в Excel

Создаем генератор случайных чисел в Excel

gen sluch chissel 1 Создаем генератор случайных чисел в ExcelДоброго времени суток, уважаемый, читатель!

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

Итак, для чего же собственно мы можем использовать этом механизм:

  • во-первых: мы можем для тестировки формул, заполнить нужный нам диапазон случайными числами;
  • во-вторых: для формирования вопросов различных тестов;
  • в-третьих: для любого случайно распределения заранее пронумерованных задач между вашими сотрудниками;
  • в-четвёртых: для симуляции разнообразнейших процессов;

…… да и во многих других ситуациях!

В этой статье я рассмотрю только 3 варианта создания генератора (возможности макроса, я не буду описывать), а именно:

  1. С помощью функции СЛЧИС;
  2. С помощью функции СЛУЧМЕЖДУ;
  3. С помощью надстройки AnalysisToolPack.

Ранее я рассказывал, что дата – это число, обозначающее количество дней, прошедших от 0 января 1900 года. Можно воспользоваться этим, чтобы сгенерировать произвольную дату, например, в интервале от 1 января 1980 года до 31 декабря 2005.

Действуем по алгоритму:

  1. Запишем в ячейках начальную и конечную даты периода
    крайние даты диапазона
  2. Присвоим ячейкам числовой формат. Видим, что начальной дате соответствует число 29221, а конечной – 38717
    даты в числовом формате
  3. Воспользуемся функцией СЛУЧМЕЖДУ , чтобы выбрать значение в этом диапазоне
    Случайная дата в диапазоне
  4. Присвоим ячейке с формулой формат даты. Готово!

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

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

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

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

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

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

Читайте так же:
Можно ли временно удалить страницу в контакте

Как использовать функцию RANDBETWEEN

Функция RANDBETWEEN почти так же проста, как и функция RAND. Просто введите «RANDBETWEEN ([min],[max]», Заменив min и max наименьшим и наибольшим числами диапазона, для которого вы хотите использовать числа.

Когда вы нажимаете Enter, эта ячейка будет содержать случайное число между этим низким и высоким значением.

Когда использовать функцию RANDBETWEEN

Функция генератора случайных чисел RANDBETWEEN даже более полезна, чем функция RAND. Это потому, что он позволяет вам содержать возвращенные случайные числа в пределах диапазона. Он также возвращает целые числа вместо десятичных.

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

Давайте посмотрим на пример. Допустим, у вас есть список из 12 человек, и вы хотите разделить их на две команды по 6 человек в каждой, не смущаясь, когда вы позволяете людям «выбирать» своих любимых людей для каждой команды.

Сначала создайте электронную таблицу со всеми именами игроков.

Добавьте следующую функцию в следующий столбец рядом с каждым именем игрока. Затем нажмите Enter.

Вы можете ввести это в первую ячейку, а затем заполнить остальные под ней. Это автоматически назначит каждого игрока команде 1 или команде 2.

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

Произвольное дискретное распределение

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

В поле Входной интервал значений и вероятностей необходимо ввести ссылку на двухстолбцовый диапазон (см. файл примера ).

Необходимо следить, чтобы сумма вероятностей модельного распределения была равна 1. Для этого в MS EXCEL имеется специальная функция ВЕРОЯТНОСТЬ() .

СОВЕТ: О генерации чисел, имеющих произвольное дискретное распределение, см. статью Генерация дискретного случайного числа с произвольной функцией распределения в MS EXCEL. В этой статье также рассмотрена функция ВЕРОЯТНОСТЬ() .

трюки • приёмы • решения

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

Использование функции СЛЧИС

Представленная в Excel функция СЛЧИС генерирует равномерное случайное число в промежутке между 0 и 1. Другими словами, любое число от 0 до 1 имеет равную вероятность быть возвращенным этой функцией. Если вам нужны случайные числа с большими значениями, используйте простую формулу умножения. Следующая формула, например, генерирует равномерное случайное число между 0 и 1000:
=СЛЧИС()*1000 .

Читайте так же:
Как foxit reader перевести в word

Чтобы ограничить случайное число целыми числами, используйте функцию ОКРУГЛ:
=ОКРУГЛ((СЛЧИС()*1000);0) .

Использование функции СЛУЧМЕЖДУ

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

В версиях, предшествующих Excel 2007, функция СЛУЧМЕЖДУ доступна только при установке дополнительного пакета анализа. Для совместимости с предыдущими версиями (и чтобы избежать использования этой надстройки) используйте такую формулу, где а представляет нижний, a b — верхний предел: =СЛЧИС()*(b-а)+а . Чтобы сгенерировать случайное число между 40 и 50, используйте следующую формулу: =СЛЧИС()*(50−40)+40 .

Использование надстройки Analysis ToolPack

Другой способ получения случайных чисел в листе состоит в использовании надстройки Analysis ToolPack (которая поставлялась вместе с Excel). Этот инструмент может генерировать неравномерные случайные числа. Они генерируются не формулами, поэтому, если вам нужен новый набор случайных чисел, необходимо перезапустить процедуру.

Получите доступ к пакету Analysis ToolPack, выбрав Данные ► Анализ ► Анализ данных. Если эта команда отсутствует, установите пакет Analysis ToolPack с помощью диалогового окна Надстройки. Самый простой способ вызвать его — нажать Atl+TI. В диалоговом окне Анализ данных выберите Генерация случайных чисел и нажмите ОК. Появится окно, показанное на рис. 130.1.

Рис. 130.1. Диалоговое окно Генерация случайных чисел

Выберите тип распределения в раскрывающемся списке Распределение, а затем задайте дополнительные параметры (они изменяются в зависимости от распределения). Не забудьте указать параметр Выходной интервал, в котором хранятся случайные числа.

Генерация случайных чисел в excel 2016

Обычно, если вы хотите вставить или сгенерировать случайные числа в Excel, вам нужно научиться использовать функцию Excel RAND или RANDBETWEEN для генерации случайных чисел. Вообще говоря, вам непросто быстро сгенерировать случайные числа в Excel. Чтобы улучшить вашу работу, теперь мы рекомендуем вам мощный Вставить случайные данные полезности Kutools for Excel. С помощью этой утилиты вы можете быстро вставить случайные данные в указанный диапазон в Excel.

  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Читайте так же:
Как в таблице excel убрать пустые строки

Нажмите на Kutools >> Вставить >> Вставить случайные данные. Смотрите скриншоты:

документ случайные данные 1

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

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

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

2. Примените эту утилиту, нажав Kutools > Вставить > Вставить случайные данные. В Вставить случайные данные диалоговое окно, вам необходимо:

1). Щелкните значок Целое вкладка;

2). Укажите 100 и 500 отдельно в от и к поля и щелкните Ok or Применить кнопка. Смотрите скриншот:

документ случайные данные 2

Внимание: Нажмите OK Кнопка, диалоговое окно будет закрыто после вставки случайных чисел, но если вы нажмете кнопку Применить , после ввода случайных чисел диалоговое окно будет продолжать открываться для дальнейшего использования.

Вы увидите следующие результаты. см. снимок экрана:

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

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

2. Примените эту утилиту, нажав Kutools > Вставить > Вставить случайные данные. В Вставить случайные данные диалоговое окно, вам необходимо:

1). Щелкните значок Десятичная дробь вкладка;

2). Укажите 100 и 500 отдельно в от и к коробках.

3) Укажите десятичный разряд в Десятичное число текстовое поле и щелкните Ok or Применить кнопка. Смотрите скриншот:

документ случайные данные 3

Внимание: Нажмите OK Кнопка, диалоговое окно будет закрыто после вставки случайных десятичных чисел, но если вы нажмете кнопку Применить кнопка, после ввода случайных чисел диалоговое окно будет продолжать открываться для дальнейшего использования)

Вы увидите следующие результаты, см. Снимок экрана:
документ случайные данные 4

Создать / вставить случайную дату в диапазоне

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

1. Укажите диапазон, в который вы хотите вставить случайную дату.

2. К Вставить случайные данные диалоговое окно, нажмите Времяи измените даты в соответствии со своими потребностями. Смотрите скриншот:

A : Укажите диапазон дат.

B : Если вы проверите Рабочий день поле, все даты рабочих дней между двумя указанными датами будут сгенерированы случайным образом, как и Выходные.

3. Нажмите OK or Применить. Вы увидите следующие результаты. Смотрите скриншот:

документ случайные данные 6

Сгенерировать / вставить случайное время в диапазон

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

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

2. в Вставить случайные данные диалогового окна, нажмите Время на вкладке укажите время отдельно в от и к поля, а затем щелкните OK or Применить кнопка. Смотрите скриншот:

Читайте так же:
Мода в excel формула

документ случайные данные 7

3. Времена между двумя заданными временами вставляются сразу. Смотрите скриншот:

Сгенерировать / вставить пароль (случайные строки данных) или указанные строки данных форматирования в диапазоне

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

1. Выберите диапазон, в котором вы хотите сгенерировать / вставить строки данных (пароль).

2. в Вставить случайные данные диалоговое окно, нажмите строка на вкладке укажите символы и длину строки, а затем щелкните Ok or Применить кнопка. Смотрите скриншот:

документ случайные данные 8

4. Вы можете увидеть вставленные строки данных, как показано на скриншоте ниже.

выстрел-генерировать-случайные-данные8

Кроме того, вы также можете сгенерировать или вставить указанные строки данных форматирования (например, . @. . com) в следующем диапазоне:

1. Выберите диапазон для поиска данных. Под строка вкладку, укажите символы, отметьте По маске поле, а затем введите указанную строку данных в поле По маске коробка. Смотрите скриншот:

документ случайные данные 9

Внимание: Здесь мы используем? символ, чтобы указать цифру случайного символа в окончательной указанной строке форматирования.

2. После нажатия OK or Применить. Указанные строки данных форматирования генерируются случайным образом в выбранном диапазоне следующим образом. Смотрите скриншот:

выстрел-генерировать-случайные-данные10

Создание / вставка настраиваемого списка в диапазон

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

1. Выберите диапазон, в который нужно вставить настраиваемый список.

2. в Вставить случайные данные диалоговое окно, нажмите Пользовательский список меню.

3. Вы можете использовать настраиваемые списки по умолчанию по мере необходимости или создать новый настраиваемый список, нажав кнопку кнопку и введите свои пользовательские данные, и, наконец, нажав кнопку OK в Kutools for Excel диалоговое окно. Смотрите скриншот:

A : Вы можете быстро извлечь данные из ячеек для создания нового настраиваемого списка.

4. Теперь настраиваемый список создан, выберите его и нажмите OK or Применить кнопку, чтобы завершить вставку. Новые значения были вставлены в указанный диапазон, как показано на следующем снимке экрана.

выстрел-генерировать-случайные-данные12

Заметки:

1. Эта утилита поддерживает операцию отмены, поэтому вы можете сразу же нажать кнопку «Отменить» (Ctrl + Z), чтобы восстановить ее.

2. Уникальные ценности опция будет генерировать или вставлять только уникальные значения в диапазоне.

Демонстрация: вставка случайных чисел, даты, времени, текстовых строк или настраиваемого списка данных в рабочий лист

Kutools for Excel

Описанная выше функциональность — лишь одна из 300 мощных функций Kutools for Excel.

Разработано для Excel (Office) 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатная загрузка и использование в течение 60 дней.

Excel works!

menu

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

Читайте так же:
Можно ли использовать гидроаккумулятор без насоса

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector