В этой статье объясняются особенности алгоритма генерации случайных чисел в Excel и показано, как использовать функции СЛУЧАЙ и СЛУЧАЙНАЯ МЕЖДУ для чисел, дат, паролей и других текстовых выражений.
Прежде чем мы углубимся в различные методы создания случайных чисел в Excel, давайте определим, что они из себя представляют. Проще говоря, случайные данные — это последовательность цифр, букв или других символов, не имеющих шаблона или шаблона.
Случайность имеет множество различных применений в криптографии, статистике, лотереях, азартных играх и многих других областях. А так как оно всегда было востребовано, то с давних времен существовали различные способы генерации случайных чисел, такие как подбрасывание монет, бросание игральных костей, тасование игральных карт и так далее. Конечно, в этом уроке мы не будем полагаться на такие «экзотические» методы, а сосредоточимся на том, что может предложить генератор случайных чисел Excel.
- Как работает алгоритм случайных чисел
- СЛУЧАЙ функция
- Случайные числа в указанном диапазоне с использованием функции СЛУЧМЕЖДУ
- Случайные числа с правильным количеством знаков после запятой
- Случайные даты в Excel
- Как вставить случайное время в Excel
- Случайная дата и время
- Как получить случайные буквы
- Создание текстовых строк и паролей
- Как предотвратить постоянный пересчет формул RAND и RANDBETWEEN
- Как генерировать уникальные случайные числа в Excel
Создание случайных чисел в Excel – основы.
Случайные числа — это случайно выбранные последовательности чисел. Но в жизни почти всегда используются псевдослучайные числа. Они достигаются по заранее заданному сложному алгоритму. Для постороннего они выглядят случайными, но их все же можно предсказать, если вы знаете этот сложный алгоритм. Псевдослучайные числа используются во всех компьютерных программах.
Хотя функции случайных чисел Excel проходят все стандартные тесты на случайность, они по-прежнему не производят настоящие случайные числа. Но это не значит, что Excel не подходит для этих целей. Псевдослучайные числа, генерируемые функциями Excel, хороши для многих целей.
Давайте подробнее рассмотрим, как это происходит, чтобы вы знали, чего можно и чего нельзя ожидать от стандартных функций Excel.
Как и большинство компьютерных программ, генератор случайных чисел Excel генерирует псевдослучайные числа, используя некоторые математические формулы. Для вас это означает, что эти числа, сгенерированные Excel, теоретически предсказуемы. Но только при условии, что кто-то знает все детали алгоритма. Вот почему это никогда не было задокументировано и вряд ли когда-либо будет сделано.
Итак, как вы можете генерировать случайные числа в Excel?
- Функции Excel СЛУЧАЙ и СЛУЧМЕЖДУ (RAND и RANDBETWEEN на английском языке) возвращают псевдослучайные числа из равномерного распределения, также известного как прямоугольное распределение, где все значения, которые может принимать значение, равновероятны. Хорошим примером равномерного распределения является бросок одной кости. Результатом такого розыгрыша является шесть возможных значений (1, 2, 3, 4, 5, 6), и каждое из них имеет одинаковую вероятность.
- Ходят слухи, что функции Excel RAND и RANDOMBETWEEN инициализируются из системного времени компьютера. Технически начальное число является отправной точкой для создания последовательности чисел. И каждый раз, когда вызывается случайная функция Excel, используется новое начальное число, которое возвращает уникальную последовательность. Другими словами, при использовании генератора случайных чисел в Excel нельзя получить повторяющуюся комбинацию ни с помощью функции СЛУЧАЙ И СЛУЧМЕЖДУ, ни с помощью VBA, ни каким-либо другим способом.
- В более ранних версиях Excel, до Excel 2003, алгоритм случайных чисел имел относительно небольшой диапазон (менее 1 миллиона неповторяющихся последовательностей чисел) и не прошел несколько стандартных тестов на случайность для длинных последовательностей. По этой причине, если кто-то все еще работает со старой версией Excel, лучше не использовать функцию СЛЧИС с большими моделями моделирования.
Это довольно длинное техническое введение закончено и мы переходим к более практичным и полезным вещам.
Функция случайного числа — СЛЧИС.
Функция СЛЧИС в Excel — одна из двух функций, специально разработанных для случайных чисел. Он возвращает произвольное десятичное число (действительное число) от 0 до 1.
СЛУЧАЙ — непостоянная функция, означающая, что новое случайное число генерируется каждый раз при вычислении рабочего листа. И это происходит часто — когда вы выполняете действие, например, редактируете формулу (не обязательно RAND, даже любую другую формулу на листе), редактируете ячейку или вводите новые данные.
Функция СЛЧИС доступна во всех версиях Excel, начиная с самых ранних.
Поскольку у него нет аргументов, вы просто записываете его в ячейку, а затем копируете туда, куда вам нужно:
А теперь давайте создадим несколько формул RAND для генерации случайных чисел в соответствии с вашими условиями.
Ограничение верхней границы диапазона случайных чисел.
Чтобы создать массив от нуля до любого значения N, умножьте функцию RAND на N:
СЛУЧАЙ() * N
Например, чтобы создать последовательность чисел больше или равно 0, но меньше 50, используйте следующую формулу:
=СЛУЧАЙ()*50
Примечание. Верхнее предельное значение никогда не включается в возвращаемую последовательность. Например, если вы хотите получить случайные числа от 0 до 10, включая 10, правильной формулой будет =СЛУЧИС()*11.
Также обратите внимание, что количество отображаемых десятичных разрядов определяется форматом ячеек. При этом устанавливаются 2 знака после запятой, хотя на самом деле их намного больше. Мы поговорим об их округлении чуть позже.
Случайные числа в интервале «от - до».
Чтобы сгенерировать случайное число между двумя введенными вами значениями, используйте следующую формулу RAND:
СЛУЧАЙ() * (В - А) + А
Где A — значение нижнего предела (наименьшее число), а B — значение верхнего предела (наибольшее).
Например, чтобы записать в электронную таблицу случайные числа от 10 до 50, вы можете использовать следующую формулу:
=СЛУЧАЙ()*(50-10)+10
Примечание. Эта формула никогда не вернет число, равное максимальному значению указанного диапазона (то есть B).
Случайные целые числа.
Чтобы функция RAND в Excel производила целые числа, возьмите одну из приведенных выше формул и оберните ее функцией INT().
Как генерировать случайные целые числа от 0 до 50:
=ЦЕЛОЕ(СЛЧИС()*50)
Чтобы получить их от 10 до 50:
=ЦЕЛОЕ(СЛУЧАЙ()*(50-10)+10)
Теперь давайте рассмотрим более новую функцию — СЛУЧМЕЖДУ().
Функция Excel СЛУЧМЕЖДУ — как получить случайные целые числа в указанном диапазоне
СЛУЧМЕЖДУ — еще одна функция, предоставляемая Excel для случайных чисел. Он возвращает их в указанном вами диапазоне:
СЛУЧМЕЖДУ(нижнее_значение, верхнее_значение)
Как и СЛУЧАЙ, СЛУЧМЕЖДУ в Excel — это непостоянная функция, которая возвращает новый результат каждый раз, когда ваш рабочий лист пересчитывается. И это происходит при любом изменении на листе.
Например, чтобы решить ту же проблему, которую мы обсуждали чуть выше: получить случайные целые числа от 10 до 50 (10 и 50 включительно), используйте следующую формулу:
= СЛУЧАЙНО МЕЖДУ (10, 50)
Функция СЛУЧМЕЖДУ в Excel может создавать как положительные, так и отрицательные числа. Например, чтобы получить список целых чисел от -10 до 10, введите следующую формулу:
= СЛУЧАЙНО МЕЖДУ (-10, 10)
Функция СЛУЧМЕЖДУ доступна в Excel 2019–2007. В более ранних версиях вы можете использовать формулу СЛУЧАЙ, которую мы обсуждали выше.
Далее в этом руководстве вы найдете еще несколько примеров формул, показывающих, как использовать функцию СЛУЧМЕЖДУ для записи случайных нецелочисленных значений.
Как создавать случайные числа с нужным количеством десятичных знаков.
Хотя функция СЛУЧМЕЖДУ в Excel предназначена для возврата целых чисел, вы можете заставить ее возвращать случайные десятичные дроби с любым количеством знаков после запятой.
Например, чтобы получить список чисел с одним десятичным знаком, умножьте нижнее и верхнее значения на 10, затем разделите возвращаемое значение на 10:
СЛУЧМЕЖДУ(нижнее значение * 10; верхнее значение * 10) / 10
Следующая формула СЛУЧМЕЖДУ() возвращает случайные десятичные числа от 1 до 50:
= СЛУЧАЙНО МЕЖДУ (1*10; 50*10)/10
Однако обратите внимание, что среди полученных результатов есть и значения без знаков после запятой, то есть запятая может быть нулевой.
Точно так же, чтобы получить случайные числа от 1 до 50 с двумя десятичными знаками, умножьте аргументы функции СЛУЧМЕЖДУ на 100, а затем разделите результат на 100:
= СЛУЧАЙНО МЕЖДУ (1*100; 50*100) / 100
Как получить случайные даты в Excel
Как известно, даты в Excel — это числа. Поэтому рассмотренные нами подходы вполне к ним применимы.
Чтобы получить список произвольных дат в определенном интервале времени, используйте функцию СЛУЧМЕЖДУ в сочетании с ЗНАЧЕНИЕМ ДАТЫ:
СЛУЧМЕЖДУ(ДАТАЗНАЧ(дата начала), ДАТАЗНАЧ(конечная дата))
Например, чтобы получить список дат с 1 июня 2015 г по 30 июня 2015 г., введите в электронную таблицу следующую формулу:
= СЛУЧАЙНОЕ МЕЖДУ(ДАТАЗНАЧ("1 июля 2021 г."); ДАТАЗНАЧ("31 августа 2021 г"))
Кроме того, вы можете использовать функцию DATE:
= СЛУЧАЙНО МЕЖДУ(ДАТА(2021;7;1);ДАТА(2021;8;31))
Не забудьте использовать формат даты, и вы получите список случайных дат, например:
Для некоторых дополнительных опций, таких как получение списка будних или выходных дней, вы можете использовать расширенный генератор случайных чисел и дат .
Как вставить случайное время в Excel
Учитывая, что Excel внутренне хранит время в виде десятичного числа от 0 до 1, вы можете использовать стандартную функцию Excel RAND для вставки случайных действительных чисел, а затем применить формат времени к этим ячейкам:
Чтобы вернуть случайное время в соответствии с вашими критериями, требуются более сложные формулы, как показано ниже.
Случайное время в указанном интервале.
Чтобы вставить случайное время между двумя указанными значениями времени, используйте функцию TIME() или TIMEVALUE() вместе с RAND():
ВРЕМЯ (время начала) + RAND() * (ВРЕМЯ (время начала) - ВРЕМЯ (время окончания))
или
ЗНАЧЕНИЕ ВРЕМЕНИ (время начала) + RAND() * (ЗНАЧЕНИЕ ВРЕМЕНИ (время начала) - ЗНАЧЕНИЕ ВРЕМЕНИ (время окончания))
Например, чтобы вставить время между 06:00 и 20:30, можно использовать одну из следующих формул:
=ВРЕМЯ(6;0;0) + СЛУЧАЙ() * (ВРЕМЯ(20;30;0) - ВРЕМЯ(6;0;0))
=ЗНАЧ ВРЕМЕНИ("6:00:00") + СЛУЧАЙ() * (ЗНАЧ ВРЕМЕНИ("20:30:00") - ЗНАЧ ВРЕМЕНИ("6:00:00"))
Случайные дата и время.
Чтобы создать список случайных дат и времени, используйте комбинацию функций СЛУЧМЕЖДУ и ДАТАЗНАЧ:
ПРОБЕЛ ДИАПАЗОНА (ЗНАЧЕНИЕ ДАТЫ (дата начала); ЗНАЧЕНИЕ ДАТЫ (дата окончания)) + ПРОСТРАНСТВО ДИАПАЗОНА (ЗНАЧЕНИЕ ВРЕМЕНИ (время начала) * 10000; ЗНАЧЕНИЕ ВРЕМЕНИ (время окончания) * 10000) / 10000
Допустим, вы хотите вставить любую дату между 1 июня 2021 года и 31 августа того же года со временем между 08:30 и 17:00. Вам подойдет следующая формула:
= RANDOMINTERVAL(DATEVALUE("1 июня 2021"), DATEVALUE("31 августа 2021")) + RANDOMINTERVAL(TIMEVALUE("8:30") * 10000, TIMEVALUE("17:00") * 10000) / 10000
Вы также можете указать дату и время, используя функции DATE и TIME соответственно:
= СЛУЧАЙНЫЙИНТЕРВАЛ(ДАТА(2021;6;1);ДАТА(2021;8;31)) + СЛУЧАЙНЫЙИНТЕРВАЛ(ВРЕМЯ(8;30;0) * 10000; ВРЕМЯ(17;0;0) * 10000) / 10000
Выберите вариант, наиболее подходящий для вашей задачи.
Как получить случайные буквы в Excel
Для возврата случайного письма требуется комбинация трех разных функций:
=СИМВОЛ(СЛУЧАЙНЫЙ ПРОБЕЛ(КОД("A"),КОД("I")))
Где A — первый символ, а Z — последний символ в диапазоне букв, которые вы хотите использовать (в алфавитном порядке).
В формуле выше:
- CHARCODE() возвращает коды ANSI для указанных букв.
- СЛУЧМЕЖДУ() принимает код, возвращаемый функцией КОД, как нижнее и верхнее значения диапазона.
- CHAR() преобразует коды ANSI, возвращаемые функцией СЛУЧМЕЖДУ, в соответствующие литералы.
Примечание. Поскольку код ANSI чувствителен к регистру, эта формула чувствительна к регистру.
Если кто-то помнит таблицу кодов символов ANSI, ничто не мешает вам передавать коды букв непосредственно в функцию СЛУЧМЕЖДУ.
Например, чтобы использовать заглавные буквы между A (код ANSI 192) и Z (код ANSI 223), введите:
=СИМВОЛ(СЛУЧМЕЖДУ(192,223))
Чтобы получить строчные буквы от a (код ANSI 224) до z (код ANSI 255), используйте следующую формулу:
=СИМВОЛ(ДИАПАЗОН(224,255))
Коды символов:
- 192-223 - заглавные буквы AZ
- 224-255 - строчные буквы аз
Чтобы вставить случайный специальный символ, например ! "# $% & '() * +, -. /, используйте функцию Random с первым параметром, равным 33 (код ANSI для "!"), и вторым параметром, равным 47 (код ANSI для "/").
=СИМВОЛ(СЛУЧМЕЖДУ(33,47))
Создание текстовых строк и паролей в Excel
Чтобы создать произвольную текстовую строку в Excel, вам просто нужно объединить несколько функций CHAR и RANDOMBETWEEN.
Например, чтобы получить список 4-значных паролей, вы можете использовать такую формулу:
= СЛУЧАЙНОЕ МЕЖДУ (0; 9) И СИМВОЛ (СЛУЧАЙНОЕ МЕЖДУ (65; 90)) и СИМВОЛ (СЛУЧАЙНОЕ МЕЖДУ (97; 122)) и СИМВОЛ (СЛУЧАЙНОЕ МЕЖДУ (33; 47))
Чтобы сделать запись более компактной, я вставил коды ANSI непосредственно в формулу. Четыре функции возвращают следующие случайные значения:
- RANDBETWEEN(0;9) — возвращает числа от 0 до 9.
- CHAR(RANDOMBETWEEN(65,90)) - заглавные буквы от A до Z.
- CHAR(RANDOMBETWEEN(97, 122)) - получаем строчные буквы от a до z.
- CHAR(RANDOMBETWEEN(33;47)) - добавить специальные символы.
Текстовые строки, полученные по приведенной выше формуле, будут иметь вид «4Np#» или «2Yu& ».
Обратите внимание на следующее! Если вы используете аналогичную формулу для генерации случайных паролей, они не будут очень надежными. Конечно, вы можете создавать более длинные текстовые строки, объединяя несколько функций. Однако изменить порядок невозможно, т.е первая функция всегда возвращает число, вторая функция возвращает прописную букву и так далее.
Если вы ищете расширенный генератор случайных паролей в Excel, способный генерировать текстовые строки любой длины и шаблона, ознакомьтесь с функциями тестовой строки Advanced Random Generator.
Также помните, что текстовые строки, созданные с помощью приведенной выше формулы, будут меняться каждый раз при пересчете электронной таблицы. И это происходит автоматически после ввода или корректировки значений или формул на листе.
Чтобы гарантировать, что ваши строки или пароли останутся прежними после их создания, вам необходимо запретить функции СЛУЧМЕЖДУ обновлять значения, что приводит нас непосредственно к следующему разделу.
Как предотвратить постоянный пересчет формул СЛЧИС и СЛУЧМЕЖДУ.
Если вам нужен постоянный набор чисел, дат или текстовых строк, которые не меняются при каждом пересчете таблицы, используйте один из следующих методов:
- Чтобы предотвратить пересчет функций СЛУЧАЙ или СЛУЧМЕЖДУ в одной конкретной ячейке, выберите ее, переключитесь на строку формул и нажмите F9, чтобы заменить формулу ее значением.
- Для предотвращения пересчета используйте специальный инструмент Вставить. Выделите все ячейки с формулами, нажмите Ctrl + C, чтобы скопировать их в буфер обмена. Затем щелкните правой кнопкой мыши выбранную область и выберите «Специальная вставка» > «Значения». Либо можно нажать Shift+F10, а затем V, что также позволяет вставлять ранее скопированные значения вместо формул.
- Вы можете использовать формулу для преобразования значения инструмента, который является частью надстройки Ultimate Suite.
Как создать уникальные случайные числа в Excel
Ни одна из случайных функций Excel не может генерировать уникальные случайные значения. Совпадения всегда возможны, особенно если вы получаете целые числа с помощью округления. Чтобы создать список без дубликатов, сделайте следующее:
- Используйте функцию СЛУЧАЙ или СЛУЧМЕЖДУ, чтобы создать список чисел. Создавайте больше значений, чем вам нужно на самом деле, потому что некоторые из них будут продублированы и удалены позже.
- Преобразуйте формулы в значения, как описано выше.
- Удалите повторяющиеся значения с помощью встроенных инструментов Excel (см. Как удалить дубликаты в Excel) или специального инструмента для удаления дубликатов в Excel.
