Progress-servis55.ru

Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel случайное число в диапазоне

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

Доброго времени суток, уважаемый, читатель!

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

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

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

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

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

Создаём генератор случайных чисел с помощью функции СЛЧИС

С помощью функции СЛЧИС, мы имеем возможность генерировать любое случайное число в диапазоне от 0 до 1 и эта функция будет выглядеть так:

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

=СЛЧИС()*100; А вот если вам не нравятся дробные числа или просто нужно использовать целые числа, тогда используйте такую комбинацию функций, это позволит вам отсечь значения после запятой или просто отбросить их:

=ОТБР((СЛЧИС()*100);0) Когда возникает необходимость использовать генератор случайных чисел в каком-то определённом, конкретном диапазоне, согласно нашим условиям, к примеру, от 1 до 6 надо использовать следующую конструкцию (обязательно закрепите ячейки с помощью абсолютных ссылок):

  • a – представляет нижнюю границу,
  • b – верхний предел

и полная формула будет выглядеть: =СЛЧИС()*(6-1)+1, а без дробных частей вам нужно написать: =ОТБР(СЛЧИС()*(6-1)+1;0)

Создаём генератор случайных чисел с помощью функции СЛУЧМЕЖДУ

Эта функция более проста и начала нас радовать в базовой комплектации Excel, после 2007 версии, что значительно облегчило работу с генератором, когда необходимо использовать диапазон. К примеру, для генерации случайного числа в диапазоне от 20 до 50 мы будем использовать конструкцию следующего вида:

Создаём генератор с помощью надстройки AnalysisToolPack

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

Для получения доступа к этой, бесспорно, полезной надстройки, нужно, для начала, с помощью диалогового окна «Надстройки» установить этот пакет. Если у вас он уже установлен, то дело за малым, выбираете пункт меню «Данные» – «Анализ» – «Анализ данных», выбираете «Генерация случайных чисел» в предложенном программой списке и жмём «ОК».

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

Читать еще:  Функция подсчета ячеек в excel

А на этом у меня всё! Я очень надеюсь, что вопрос по созданию генератора случайных чисел я раскрыл полностью и вам всё понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

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

Генератор случайных чисел 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. Определим частоту для случайных чисел в каждом диапазоне. Используем формулу массива <=ЧАСТОТА(A2:A201;C2:C11)>.
  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 построен.

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

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

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

  • генерация уникальных случайных кодов для товаров или пользователей
  • назначение людей на задачи (каждому по случайной из списка)
  • перестановки слов в поисковом запросе (привет 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, которая выдает требуемое количество случайных неповторяющихся чисел из заданного интервала.

Рандомизатор чисел в Excel

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

Рандомный выбор чисел от нуля до одного

Функция =СЛЧИС() возвращает дробное число в пределах от 0 до 1. То есть, произвольную десятичную дробь. Функция записывается без аргументов! Вот, как это выглядит:

Результат никогда не будет превышать единицу.

Выбор рандом чисел в заданном диапазоне

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

Например, так можно получить число в промежутке от -100 до 100:

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

А теперь немного примеров использования приведенных функций.

Дробные числа больше единицы

Как вы уже поняли, функция СЛЧИС всегда возвращает дробное число от 0 до 1, а СЛУЧМЕЖДУ – целое в указанном диапазоне. А как выбрать число рандомно, дробное и больше единицы? К примеру, нам нужно случайное дробное число в пределах от 10 до 90. Поможет такая формула:

Как сгенерировать дату в диапазоне

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

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

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

Рандомное четное число

Чтобы гарантированно получить четное или нечетное число, можно воспользоваться функциями округления ЧЁТН и НЕЧЁТ .

Вот, как это будет выглядеть:

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

На этом всё о рандомайзере чисел, буду рад ответить на Ваши вопросы!

Ссылка на основную публикацию
Adblock
detector