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

Автор: | 06.03.2022
Нет комментариев

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

В этой статье объясняются особенности алгоритма поиска случайных чисел Excel и показано, как использовать функции СЛУЧАЙНОЙ и СЛУЧАЙНОЙ СРЕДИ СРЕДИ чисел, дат, паролей и других текстовых выражений.

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

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

  • Как работает алгоритм случайных чисел
  • СЛУЧАЙНАЯ функция
  • Случайные числа в указанном диапазоне с помощью функции RANDBETWEEN
  • Случайные числа с желаемым количеством десятичных знаков
  • Случайные даты в Excel
  • Как вставить случайное время в Excel
  • Случайная дата и время
  • Как получить случайные буквы
  • Генерация текстовых строк и паролей
  • Как избежать постоянного пересчета формул RAND и RAND BETWEEN
  • Как создать уникальные случайные числа в Excel

Создание случайных чисел в Excel – основы.

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

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

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

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

Так как же сделать случайные числа в Excel?

  • Функции Excel RAND и RANDBETWEEN (RAND и RANDBETWEEN на английском языке) возвращают псевдослучайные числа из равномерного распределения, также известного как прямоугольное распределение, где существует равная вероятность для всех значений, которые может принимать значение. Хорошим примером равномерного распределения является игра в кости. Это извлечение приводит к шести возможным значениям (1, 2, 3, 4, 5, 6), и каждое из них имеет одинаковую вероятность. 
  • Считается, что функции Excel RAND и RANDBETWEEN инициализируются из системного времени компьютера. Технически начальное число — это отправная точка для создания последовательности чисел. И каждый раз, когда вызывается случайная функция Excel, используется новое начальное число, которое возвращает уникальную последовательность. Другими словами, при использовании генератора случайных чисел в Excel вы не можете получить повторяемую комбинацию с помощью функции RAND и RAND BETWEEN или с помощью VBA или любых других средств.
  • В ранних версиях Excel, до Excel 2003, алгоритм случайных чисел имел относительно небольшой диапазон (менее 1 миллиона неповторяющихся числовых последовательностей) и не проходил несколько стандартных тестов на случайность для длинных последовательностей. По этой причине, если кто-то все еще использует более старую версию Excel, лучше не использовать функцию СЛУЧАЙНАЯ с большими макетными моделями.

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

Функция случайного числа — СЛЧИС.

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

RANDOM — непостоянная функция, что означает, что новое случайное число появляется каждый раз при вычислении рабочего листа. И это часто случается: когда вы предпринимаете какое-либо действие, например, изменяете формулу (не обязательно RAND, даже любую другую формулу на листе), вы меняете ячейку или вводите новые данные.

Функция СЛУЧАЙНАЯ доступна во всех версиях Excel, начиная с первой.

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

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

Ограничение верхней границы диапазона случайных чисел.

Чтобы создать массив от нуля до любого значения N, умножьте функцию RANDOM на N:

СЛУЧАЙНЫЙ () * N

Например, чтобы создать последовательность чисел больше или равных 0, но меньше 50, используйте следующую формулу:

= СЛУЧАЙНЫЙ () * 50

Примечание. Значение верхней границы никогда не включается в возвращаемую последовательность. Например, если вы хотите получить случайные числа от 0 до 10, включая 10, правильная формула будет = RAND () * 11.

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

Случайные числа в интервале «от-до».

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

СЛУЧАЙНЫЙ () * (ДА — ЛА) + ЛА

Где A — нижнее предельное значение (наименьшее число), а B — верхнее предельное значение (наибольшее число).

Например, чтобы записать на листе случайные числа от 10 до 50, вы можете использовать следующую формулу:

= СЛУЧАЙНЫЙ () * (50-10) +10

Примечание. Эта формула никогда не вернет число, равное максимальному значению указанного диапазона (то есть B).

Случайные целые числа.

Чтобы функция Excel RAND возвращала целые числа, возьмите одну из приведенных выше формул и оберните ее в функцию INTEGER().

Чтобы сгенерировать случайные целые числа от 0 до 50:

= ЦЕЛОЕ (СЛУЧАЙНОЕ () * 50)

Чтобы получить их от 10 до 50:

= ЦЕЛОЕ (СЛУЧАЙНОЕ () * (50-10) +10)

Теперь давайте посмотрим на более новую функцию — RANDBETWEEN().

Функция Excel СЛУЧМЕЖДУ — как получить случайные целые числа в указанном диапазоне

СЛУЧМЕЖДУ — еще одна функция, предоставляемая Excel для случайных чисел. Возвращает их в указанном диапазоне:

СЛУЧМЕЖДУ (нижнее_значение; высшее_значение)

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

Например, чтобы решить ту же задачу, которую мы рассмотрели чуть выше: чтобы получить случайные целые числа от 10 до 50 (включая 10 и 50), используйте следующую формулу:

= СЛУЧАЙНО МЕЖДУ (10,50)

Функция СЛУЧМЕЖДУ в Excel может генерировать как положительные, так и отрицательные числа. Например, чтобы получить список целых чисел от -10 до 10, введите следующую формулу:

= СЛУЧАЙНОЕ МЕЖДУ (-10; 10)

Функция RANDBETWEEN доступна в Excel 2019–2007. В предыдущих версиях вы можете использовать формулу RANDBETWEEN, которую мы обсуждали выше.

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

Как создавать случайные числа с нужным количеством десятичных знаков.

Хотя функция Excel RANDBETWEEN была разработана для возврата целых чисел, вы можете заставить ее возвращать случайные десятичные числа с любым количеством десятичных знаков.

Например, чтобы получить список чисел с одним десятичным знаком, вы умножаете нижнее и верхнее значения на 10, а затем делите возвращаемое значение на 10:

RANDBETWEEN (низкое значение * 10; высокое значение * 10) / 10

Следующая формула RANDBETWEEN () возвращает случайные десятичные числа от 1 до 50:

= СЛУЧАЙНО МЕЖДУ (1 * 10; 50 * 10) / 10

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

Аналогичным образом, чтобы получить случайные числа от 1 до 50 с двумя десятичными знаками, вы умножаете аргументы функции СЛУЧАЙНА на 100, а затем также делите результат на 100:

= СЛУЧАЙНОЕ МЕЖДУ (1 * 100; 50 * 100) / 100

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

Как известно, даты в Excel — это числа. Поэтому рассматриваемые нами подходы к ним вполне применимы.

Чтобы вернуть список произвольных дат в диапазоне времени, используйте функцию RANDBETWEEN вместе с DATEVALUE:

RANDBETWEEN (DATEVALUE (дата начала), DATEVALUE (дата окончания))

Например, чтобы получить список дат с 1 июня 2015 г по 30 июня 2015 г., введите в лист следующую формулу:

= СЛУЧАЙНОЕ МЕЖДУ (ДАТАЗНАЧЕНИЕ («1 июля 2021 г.»), ДАТАЗНАЧЕНИЕ («31 августа 2021 г»))

В качестве альтернативы вы можете использовать функцию ДАННЫЕ:

= СЛУЧАЙНО МЕЖДУ (ДАТА (2021,7,1), ДАТА (2021,8,31))

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

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

Как вставить случайное время в Excel

Поскольку внутренняя система Excel хранит время как десятичное число от 0 до 1, вы можете использовать стандартную функцию Excel RANDOM для вставки случайных действительных чисел, а затем просто применить формат времени к этим ячейкам:

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

Случайное время в указанном интервале.

Чтобы вставить случайное время между двумя указанными временами, используйте функцию TIME () или TIMVALUE () вместе с RAND():

ВРЕМЯ (время начала) + RAND () * (ВРЕМЯ (время начала) — ВРЕМЯ (время окончания))

 или

TIMVALUE (время начала) + RAND () * (TIMVALUE (время начала) — TIMEVALUE (время окончания))

Например, чтобы ввести время с 6:00 до 20:30, вы можете использовать одну из следующих формул:

= ВРЕМЯ (6,0,0,0) + СЛУЧАЙНОЕ () * (ВРЕМЯ (20,30,0) — ВРЕМЯ (6,0,0))

= ВРЕМЕННОЕ ЗНАЧЕНИЕ («6:00:00») + СЛУЧАЙ () * (ВРЕМЕННОЕ ЗНАЧЕНИЕ («20:30:00») — ВРЕМЕННОЕ ЗНАЧЕНИЕ («6:00:00»))

Случайные дата и время.

Чтобы создать список случайных дат и времени, используйте комбинацию функций RANDBETWEEN и DATEVALUE:

RANDBETWEEN (DATEVALUE (начальная дата); DATEVALUE (конечная дата)) + RANDBETWEEN (TIMVALUE (время начала) * 10000; TIMVALUE (время окончания) * 10000) / 10000

Предположим, мы хотим ввести произвольные даты между 1 июня 2021 года и 31 августа того же года с временем между 8:30 и 17:00. Для вас подойдет следующая формула:

= RANDBETWEEN (DATEVALUE («1 июня 2021 г.»), DATEVALUE («31 августа 2021 г.»)) + RANDBETWEEN (TIMVALUE («8:30») * 10000; TIMEVALUE («17:00») * 10000) / 10000

вы также можете указать дату и время, используя функции ДАТА и ВРЕМЯ соответственно:

= СЛУЧАЙНОЕ МЕЖДУ (ДАТА (2021; 6; 1); ДАТА (2021; 8; 31)) + СЛУЧАЙНОЕ МЕЖДУ (ВРЕМЯ (8; 30; 0) * 10000; ВРЕМЯ (17; 0; 0) * 10000) / 10000

Выберите вариант, наиболее подходящий для вашей задачи.

Как получить случайные буквы в Excel

Для возврата случайной буквы требуется сочетание трех разных функций:

= АВТОМОБИЛЬ (СЛУЧАЙНО МЕЖДУ (КОД («A»); КОД («I»)))

Где A — первый символ, а Z — последний символ в диапазоне букв, который вы хотите использовать (в алфавитном порядке).

В приведенной выше формуле:

  • CODE () возвращает коды ANSI для указанных букв.
  • RANDBETWEEN () принимает код возврата из функции CODE как значения нижнего и верхнего диапазона.
  • CHAR () преобразует коды ANSI, возвращаемые RANDBETWEEN, в соответствующие буквы.

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

Если кто-то помнит таблицу кодов символов ANSI, ничто не мешает вам передать буквенные коды непосредственно в функцию RANDBETWEEN.

Например, чтобы использовать заглавные буквы между A (ANSI 192) и I (ANSI 223), напишите:

= АВТОМОБИЛЬ (СЛУЧМЕЖДУ (192,223))

Чтобы получить строчные буквы от a (код ANSI 224) до z (код ANSI 255), используйте следующую формулу:

= АВТОМОБИЛЬ (СЛУЧМЕЖДУ (224,255))

Коды символов:

  • 192-223 — заглавные буквы AZ
  •  224-255 — строчные буквы az

Чтобы вставить случайный специальный символ, например! «# $% & ‘() * +, -. /, используйте функцию RAND МЕЖДУ первым параметром, установленным на 33 (код ANSI для»! «) и вторым параметром 47 (код ANSI для «/»).

= СИМВОЛ (СЛУЧАЙНЫЙ МЕЖДУ (33; 47))

Создание текстовых строк и паролей в Excel

Чтобы создать произвольную текстовую строку в Excel, вам просто нужно объединить несколько функций CAR и RANDOM BETWEEN.

Например, чтобы получить список паролей из 4 символов, вы можете использовать следующую формулу:

= СЛУЧАЙНО МЕЖДУ (0; 9) & АВТОМОБИЛЬ (СЛУЧАЙНО МЕЖДУ (65; 90)) & АВТОМОБИЛЬ (СЛУЧАЙНО МЕЖДУ (97; 122)) & АВТОМОБИЛЬ (СЛУЧАЙНО МЕЖДУ (33; 47))

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

  • RANDBETWEEN (0; 9) — возвращает числа от 0 до 9.
  • СИМВОЛ (СЛУЧМЕЖДУ (65; 90)) — заглавные буквы от А до Я.
  • СИМВОЛ (СЛУЧМЕЖДУ (97; 122)) — получаем строчные буквы от а до я.
  • СИМВОЛ (СЛУЧМЕЖДУ (33; 47)) — добавить специальные символы.

Текстовые строки, полученные с помощью приведенной выше формулы, будут иметь вид «4Np #» или «2Yu& ».

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

Если вы ищете расширенный генератор случайных паролей Excel, который может генерировать текстовые строки любой длины и шаблона, вы можете проверить возможности тестовой строки Advanced Random Generator.

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

Чтобы убедиться, что ваши строки или пароли остаются неизменными после их создания, вам нужно будет запретить функции RANDBETWEEN обновлять значения, что приведет нас непосредственно к следующему разделу.

Как предотвратить постоянный пересчет формул СЛЧИС и СЛУЧМЕЖДУ.

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

  1. Чтобы предотвратить повторное вычисление функций RANDOM или RANDOM в определенной ячейке, выберите ее, перейдите к строке формул и нажмите F9, чтобы заменить формулу ее значением.
  2. Чтобы избежать пересчета, используйте инструмент Специальная вставка. Выделите все ячейки с формулой, нажмите Ctrl + C, чтобы скопировать их в буфер обмена. Затем щелкните правой кнопкой мыши выбранный диапазон и выберите «Специальная вставка»> «Значения». В качестве альтернативы вы можете нажать Shift + F10, а затем V, что также позволит вам вставить ранее скопированные значения вместо формул.
  3. Вы можете использовать специальный инструмент Формула для значений, который является частью надстройки Ultimate Suite.

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

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

  1. Используйте функцию RANDOM или RANDOM, чтобы создать список чисел. Создайте больше значений, чем необходимо, потому что некоторые из них будут продублированы и удалены позже.
  2. Преобразуйте формулы в значения, как описано выше.
  3. Удалите повторяющиеся значения с помощью встроенного инструмента Excel (см. Как удалить дубликаты в Excel) или специального средства для удаления дубликатов в Excel.

Вы также можете использовать специальный генератор случайных значений в Excel, который может получать случайные текстовые и числовые значения.