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