0 просмотров

Microsoft Excel

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

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

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

Чтобы ограничить случайное число целыми числами, используйте функцию ОКРУГЛ:
=ОКРУГЛ((СЛЧИС()*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. Диалоговое окно Генерация случайных чисел

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

Случайные числа без повторов

Постановка задачи

Предположим, что нам необходимо создать набор целых случайных чисел без повторов в заданном интервале значений. Примеры с ходу:

  • генерация уникальных случайных кодов для товаров или пользователей
  • назначение людей на задачи (каждому по случайной из списка)
  • перестановки слов в поисковом запросе (привет seo-шникам)
  • игра в лото и т.д.

Способ 1. Простой

Для начала рассмотрим простой вариант: нам необходимо получить случайный набор из 10 целых чисел от 1 до 10. Использование встроенной в Excel функции СЛУЧМЕЖДУ (RANDBETWEEN) уникальности не гарантирует. Если ввести ее в ячейку листа и скопировать вниз на 10 ячеек, то запросто могут случиться повторы:

Статья в тему:  Заброшенные деревни московской области. Заброшенные города россии, в которых живут люди

Поэтому мы пойдем другим путем.

Во всех версиях Excel есть функция РАНГ (RANG) , предназначенная для ранжирования или, другими словами, определения топовой позиции числа в наборе. Для самого большого числа в списке ранг=1, второе в топе имеет ранг=2 и т.д.

Введем в ячейку А2 функцию СЛЧИС (RAND) без аргументов и скопируем формулу вниз на 10 ячеек. Эта функция сгенерирует нам набор из 10 случайных дробных чисел от 0 до 1:

В соседний столбец введем функцию РАНГ, чтобы определить позицию в рейтинге для каждого полученного случайного числа:

Получим в столбце В то, что хотели — любое нужное количество неповторяющихся случайных целых чисел от 1 до 10.

Чисто теоретически, может возникнуть ситуация, когда СЛЧИС выдаст нам два одинаковых случайных числа в столбце А, их ранги совпадут и мы получим повтор в столбце В. Однако, вероятность такого сценария крайне мала, учитывая тот факт, что точность составляет 15 знаков после запятой.

Способ 2. Сложный

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

Введите в ячейку А2 следующую формулу, нажмите в конце Ctrl+Shift+Enter (чтобы ввести ее как формулу массива!) и скопируйте формулу вниз на требуемое количество ячеек:

Способ 3. Макрос

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

Статья в тему:  Приспособление для переноски дров из ткани. Изготовление переноски для дров своими руками: виды конструкций и фото. Вариант #4 – приспособление из покрышек

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

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

=СЛУЧМЕЖДУ (ДАТА (дата начала); ДАТА (дата окончания))

Например, чтобы получить список дат между 1 сентября 2017 и 20 ноября 2017 включительно, введите следующую формулу на листе:

Не забудьте применить формат даты к ячейке (ячейкам), и вы получите список случайных дат, подобных этому:

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

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

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

В результате получилось следующее:

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

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Статья в тему:  Когда придумали вилку. Происхождение вилки

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

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

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

Себестоимость товара Х – 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 построен.

Ссылка на основную публикацию
Статьи c упоминанием слов:
Adblock
detector