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

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

  • Генератор случайных чисел с использованием функции СЛЧИС
    Генератор случайных чисел от нуля до заданной верхней границы диапазонаГенератор случайных чисел в диапазонеГенератор случайных целых чисел в Excel
  • Генератор случайных чисел от нуля до заданной верхней границы диапазона
  • Генератор случайных чисел в диапазоне
  • Генератор случайных целых чисел в Excel
  • Генератор случайных целых чисел в диапазоне с помощью функции СЛУЧМЕЖДУ
    Создание случайных чисел с заданным количеством знаков после запятой
  • Создание случайных чисел с заданным количеством знаков после запятой
  • Генератор случайных дат в Excel
  • Генератор случайного времени в Excel
  • Генератор случайных букв в Excel
  • Как предотвратить повторное вычисление СЛЧИС и СЛУЧМЕЖДУ
  • Генератор случайных чисел с помощью Анализа данных
    Пример генерации случайных чисел с нормальным распределением
  • Пример генерации случайных чисел с нормальным распределением

zva89ПользовательСообщений: 86Регистрация: 16. 2015

Здравствуйте. Перерыл множество тем в интернете по генерации, но не нашёл ничего подходящего для меня. Помогите пожалуйста заполнить таблицу случайными числами, которые дают в сумме заданное число. Например: Заполнить диапазон от AF6 до DQ12 числами чтобы получилась сумма, которая указана в EB5. Буду очень благодарен за помощь в решении проблемы!Прикрепленные файлыinv_1. xls (46. 5 КБ)

viktturПользовательСообщений: 47202
Регистрация: 15. 2012

Ширину столбцов можно задавать. Вы этого не знали? Для чего столько объединений?

zva89ПользовательСообщений: 86
Регистрация: 16. 2015

Извиняюсь. Таблица не моя. Помогаю своей девушке

viktturПользовательСообщений: 47202
Регистрация: 15. 2012

Кнопка цитирования не для ответа. При такой организации таблицы нужно формулу вставлять в каждую ячейку. =ОКРУГЛ(СУММ/к-во ячеек;3)- значение одной ячейкиВ последней ячейке к этому числу добавить недостающую часть.

zva89ПользовательСообщений: 86Регистрация: 16. 2015

Поправил таблицу. Удалил лишние столбцы. Прикрепленные файлыinv_1. xlsx (12. 94 КБ)

viktturПользовательСообщений: 47202Регистрация: 15. 2012

В 9 ячеек:=СЛЧИС()*$M5/10в десятую:=M5-СУММ(C5:K5)Прикрепленные файлыслчис. xlsx (13. 01 КБ)

zva89ПользовательСообщений: 86Регистрация: 16. 2015

vikttur, Не совсем то, что требуется. Мне не нужно одно и тоже число в каждой ячейке. Мне нужно в каждой ячейке разное число, но чтобы в итоге получилась нужная сумма, которая в ячейке М5. Итак: Диапазон в которых нужны случайные числа — С6-L-12Необходимая сумма случайных чисел — в ячейке М5Прикрепленные файлыinv_1. xlsx (11. 52 КБ)

viktturПользовательСообщений: 47202Регистрация: 15. 2012

#825. 2015 16:26:29Цитатаодно и тоже число в каждой ячейкеЭто Вам Ваша девушка сказала? Нажимать F9 пробовали?Для диапазона строк:=СЛЧИС()*$M5/10  — вместо 10 — количество ячеек диапазона, в одну из ячеек вторую формулу:=сумма-СУММ(все_ячейки_без_одной)Можно немного усложнить, чтобы «поднять» числа выше:=МИН(СЛЧИС()*$M$5/х;$M$5/80)Чем меньше х, тем больше в диапазоне значений $M$5/80Прикрепленные файлыслчис. xlsx (14. 6 КБ)

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

  • 60-дневная безусловная гарантия возврата денег
  • Бесплатные обновления на 2 года
  • Бесплатная и бессрочная техническая поддержка

С помощью генератора случайных чисел вы сможете:Сгенерировать случайные целые числаЗаполните указанные ячейки случайными целыми числами в пределах указанного диапазона, с дубликатами или без них. Заполните диапазон действительными числамиСгенерировать случайные действительные числа (с десятичными разрядами) в выбранном диапазоне. Получить уникальные или повторяющиеся значения. Создавать случайные даты и логические значенияСформируйте список случайных дат, который включает только рабочие дни, выходные или и то, и другое. Это могут быть только уникальные даты. Заполните свой диапазон ячеек логическими значениями: истина / ложь или да / нет. Создавать безопасные паролиСоздавайте наборы символов определенной длины с вашими предпочтениями: из строчных и прописных букв, цифр, специальных символов и т. Создавать случайные строки по маскеИспользуйте эту опцию для создания кодов любого типа: купонов, цветов RGB, идентификаторов продуктов, почтовых индексов, артикулов и т. Создавать собственные списки значений и случайным образом выбирать из нихЗначения из вашего списка будут случайным образом вставлены в выбранные ячейки. При желании вы можете вставить только уникальные значения. Как сгенерировать случайные числа в ExcelПриложение Ultimate Suite уже используют

Что такое генератор случайных чисел и для чего он мне нужен?С помощью инструмента «Генератор случайных чисел для Excel» вы можете легко заполнить диапазон ячеек любыми случайными данными. Всего за несколько кликов вы можете:

  • Создать уникальные случайные числа, целые числа, даты, логические значения, символьные значения и т.д.
  • Генерировать случайные пароли заданной длины с разными кодировками (например, A — Z, 0 — 9, А — Я) или специальными символами.
  • Заполнить выбранный диапазон любыми случайными значениями из вашего собственного настраиваемого списка.

Разве нельзя получить случайные числа при помощи формул Excel?Excel имеет свой генератор случайных чисел. Его возможности и недостатки подробно описаны в этой статье. Однако, предлагаемый генератор создает гораздо лучшую с точки зрения случайности последовательность чисел и букв. Кроме того, он может сделать это без повторов, а также работает с собственными списками. То есть, надёжность существенно выше, а возможностей — намного больше.

Как сгенерировать случайные числа в Excel без дубликатов?С помощью генератора случайных чисел вы можете получать случайные целые или действительные числа с дубликатами или без них. Запустите надстройку, выберите диапазон ячеек, выберите нужный вариант и укажите минимальное и максимальное значения. Если вам нужны случайные числа без повторов, установите флажок Уникальные значения. Нажмите «Generate» и создайте случайные значения. Вы можете найти более подробное описание здесь.

Могу ли я использовать надстройку в качестве генератора паролей?Конечно! Выделите ячейки Excel, в которые вы хотите поместить свои случайные пароли. Откройте панель надстройки и выберите символы, необходимые для вашего пароля. Это может быть A — Z, a — z, 0 — 9, пробел и т. Вы также можете ввести любые другие символы в поле Custom. Установите длину строки, которая может составлять до 99 символов.

Как сгенерировать случайные даты в Excel?В инструменте «Генератор случайных чисел» есть специальная опция для дат. Вы можете указать диапазон дат и выбрать рабочие дни, выходные или и то, и другое. Если вам нужно, чтобы в созданном диапазоне значений не было дубликатов, отметьте  поле «Уникальные значения».

Программа работает как генератор кодов?Да, надстройка создает случайные строки по маске. Например, если вам нужно сгенерировать телефонные номера, вы проверяете набор цифр и используете эту маску +7 — ??? — ??? — ????. Нажмите «Создать», и у вас будут поддельные номера телефонов во всем выбранном диапазоне. Конечно, помимо номеров телефонов, вы можете использовать эту опцию для создания любых других типов кодов, например купонов, цветов RGB, идентификаторов продуктов, почтовых индексов, артикулов и т.

Могу ли я заполнить диапазон случайными значениями из настраиваемого списка?Конечно. В надстройке есть опция Custom list, которая случайным образом заполняет диапазон значениями из стандартных настраиваемых списков Microsoft Excel или из вашего собственного списка. Например, у вас есть 50 имен, и вам нужно выбрать случайные 10. Просто добавьте всех их в настраиваемый список, выберите 10 пустых ячеек в Excel, нажмите «Создать» и получите 10 случайных имен, выбранных из этого перечня. Кроме того, утилита позволяет создать собственный список из ваших случайных данных. Предположим, у вас есть список имен на листе. Вы выбираете этот список и нажимаете Новый список. Вот и все! Всего один щелчок, и вы получите новый список, добавленный в генератор. Теперь он у вас есть среди настраиваемых списков Excel.

Какой алгоритм используется в утилите Random Generator?Программа использует надежный алгоритм Mersenne Twister, версию MT19937, который создает нормально распределенную последовательность 32-битных целых чисел и имеет следующие преимущества:

  • Генерирует лучшую последовательность псевдослучайных чисел, чем формула СЛЧИС() или RAND() в Excel и функция rnd() в VBA.
  • У нее большой период неповторяющейся последовательности случайных чисел:
    2 ^ 19937-1 .
  • Она прошла множество тестов на статистическую случайность, включая набор статистических тестов NIST и тесты Дихарда. Он выполнил большинство, хотя и не все, тесты на случайность TestU01 Crush, которые еще более жесткие.

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

  • Как произвольно выбрать одно значение из списка
  • Как сделать случайный выбор без дубликатов
  • Как перемешать и выбрать строки
  • Инструмент для простого и быстрого случайного выбора ячеек, строк и столбцов

Что такое случайная выборка?Прежде чем обсуждать методы выборки, давайте предоставим небольшую справочную информацию о случайном выборе и о том, когда целесообразно его использовать. В теории вероятностей и статистике случайная выборка — это подмножество данных, выбранных из более крупного набора данных, также известного как генеральная совокупность. Каждый элемент случайной выборки выбирается совершенно произвольно и имеет равную вероятность быть выбранным. Зачем он вам нужен? В основном, чтобы получить непредвзятое представление обо всей совокупности. Например, вы хотите провести небольшой опрос среди своих клиентов. Очевидно, было бы неразумно рассылать анкету каждому человеку в вашей большой базе данных. Итак, кого вы побеспокоите своими вопросами? Будет ли это 100 новых клиентов, или первые 100 клиентов, перечисленные в алфавитном порядке, или 100 человек с самыми короткими именами? Ни один из этих подходов не соответствует вашим потребностям, потому что они изначально субъективны. Чтобы получить беспристрастную выборку, в которой у всех клиентов есть равные возможности быть выбранными, произведите случайный выбор, используя один из методов, описанных ниже. Случайный выбор значения из спискаВ Excel нет встроенной функции для случайного выбора ячеек, но вы можете использовать одну из функций для генерации случайных чисел в качестве временного решения. Их, наверное, нельзя назвать простыми и интуитивно понятными формулами, но они работают. Предположим, у вас есть список имен в ячейках A2:A15, и вы хотите выбрать одно из них. Это можно сделать с помощью одной из следующих формул:

Вот и все! Средство выбора случайных имен для Excel настроено и готово к работе:

Примечание. Имейте в виду, что СЛУЧМЕЖДУ – это непостоянная функция, то есть она будет пересчитываться при каждом изменении, которое вы вносите в рабочий лист. В результате ваш случайный выбор из списка также будет постоянно меняться. Чтобы этого не произошло, вы можете скопировать извлеченное имя и вставить его как значение в другую ячейку (Специальная вставка > Значения). Естественно, эти формулы могут не только выбирать случайные имена, но также и случайные числа, даты или любые другие данные из ячеек. Как работают эти формулыМы используем функцию ИНДЕКС для извлечения значения из списка на основе случайного порядкового номера, возвращаемого СЛУЧМЕЖДУ. То есть, функция СЛУЧМЕЖДУ генерирует случайное целое число между двумя указанными вами значениями. Для нижнего порога вы указываете число 1. Для верхнего — используете СЧЁТЗ() или ЧСТРОК(), чтобы получить общее количество ячеек с данными. В результате СЛУЧМЕЖДУ() возвращает случайный номер ячейки в вашем наборе данных. Этот номер передается в функцию ИНДЕКС, сообщая ей, какую по счёту ячейку выбрать. Второй аргумент (номер столбца) можно не указывать, поскольку он у нас только один. Примечание. Этот метод хорошо подходит для выбора одного случайного значения из списка. Если ваш выбор должен включать несколько результатов, приведенная выше формула может возвращать несколько вхождений одного и того же значения, поскольку функция СЛУЧМЕЖДУ не защищена от дубликатов. Это особенно актуально, когда вы выбираете относительно большую выборку из относительно небольшого списка. Эту задачу можно также решить с помощью формулы

Функция СЛУЧМЕЖДУ() случайным образом выбирает позицию списка, из которой нужно взять одно значение ( для этой функции вероятность выбрать любую строку одинакова). Если скопировать эту формулу вниз по столбцу, то будет выбрано несколько значений из списка. Однако, есть подводный камень: есть вероятность выбора несколько одинаковых значений, то есть получения дубликатов. Особенно, если ваш список относительно небольшой. В следующем примере показано, как сделать случайный выбор в Excel без дубликатов. Случайный выбор без дубликатов. Есть несколько способов выбрать случайные данные без дубликатов в Excel. К примеру, вы должны использовать функцию СЛЧИС, чтобы присвоить случайное число каждой ячейке, а затем выбрать несколько ячеек с помощью формулы индекса ранга. Со списком имен в ячейках A2: A16 выполните следующие действия, чтобы извлечь несколько имен:

  • Введите формулу случайного числа в B2 и скопируйте ее вниз по столбцу:=СЛЧИС()
  • Поместите приведенную ниже формулу в C2, чтобы извлечь случайное значение из столбца A:

Вот и все! Извлекаются пять имен без повторов:

Как работает эта формулаКак и в предыдущем примере, вы используете функцию ИНДЕКС для извлечения значения из столбца A на основе номера позиции. Для этого требуются две разные функции:

  • Формула СЛЧИС() заполняет столбец B случайными числами.
  • Функция РАНГ() возвращает ранг случайного числа из колонки B и из той же строки. Например, РАНГ(B2;$B$2:$B$16) получает ранг числа из B2 (0,188906401). B2 сравнивается со всеми числами из диапазона $B$2:$B$16. Оно занимает по величине 13-е место. При копировании в C3 относительная ссылка B2 изменяется на B3 и возвращает ранг числа из B3 и так далее.
  • Число, возвращаемое функцией РАНГ(), передается функции INDEX, поэтому она выбирает значение из соответствующей позиции. Значит, в С3 нужно поместить 13-е по порядку значение из диапазона $A$2:$A$16.

Предупреждение! Как показано на скриншоте выше, наша случайная выборка Excel содержит только уникальные значения. Теоретически вероятность появления дубликатов здесь очень мала, но все же существует. И вот почему: в очень большом наборе данных СЛЧИС() может сгенерировать повторяющиеся числа, а РАНГ() будет возвращать одинаковое место для этих чисел. Лично у меня во время тестов ни разу не было дубликатов, но теоретически такая вероятность есть. Если вы ищете железобетонно надёжную формулу для случайного выбора только с уникальными значениями, используйте комбинацию РАНГ + СЧЁТЕСЛИ вместо просто РАНГ. Полная формула немного громоздка, но на 100% не содержит дубликатов:

А вот еще одно похожее на предыдущее решение, в котором используется функция НАИМЕНЬШИЙ(). Рядом со столбцом значений для выборки добавляем столбец случайных чисел. Как обычно, используем для этого функцию СЛЧИС(). Предположим, нам нужно выбрать 5 имён. Для этого в колонке С записываем цифры от 1 до 5. Далее используем формулу

Поясним, как это работает. При помощи функции НАИМЕНЬШИЙ() выбираем самое маленькое значение из сгенерированных чисел. Функция ПОИСКПОЗ помогает нам определить его позицию в списке. И затем при помощи ИНДЕКС() извлекаем имя, находящееся в этой позиции. При копировании вниз по столбцу следующая формула будет брать второе наименьшее значение из списка случайных чисел, и так далее.

Но можно сэкономить себе время и не создавать колонку с порядковыми номерами. В качестве счётчика принято использовать функцию СТРОКА(). При копировании вниз СТРОКА(A1) изменится на СТРОКА(А2) и соответственно возвратит номер 2. И так далее. В остальном всё работает точно так же. Думаю, вы понимаете, что вместо НАИМЕНЬШИЙ() можно вполне использовать НАИБОЛЬШИЙ(). Дело вкуса 😊. Как выбрать случайные строки в ExcelЕсли ваш рабочий лист содержит более одного столбца данных, вы можете получить набор значений следующим образом: назначить какое-то число каждой строке, отсортировать эти числа и выбрать необходимое количество строк. Подробные инструкции приведены ниже.

  • Вставьте новый столбец справа или слева от вашей таблицы (столбец D в этом примере).
  • В первой ячейке вставленного столбца, исключая заголовки столбцов, введите формулу =СЛЧИС()
  • Дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу. В результате в каждой строке будет записано случайное число.
  • Сортировка этих чисел от наибольшего к наименьшему (сортировка в порядке возрастания переместит заголовки столбцов в нижнюю часть таблицы, поэтому обязательно выполняйте сортировку по убыванию). Для этого перейдите на вкладку Данные в группу Сортировка и фильтр и нажмите кнопку Сортировка. Excel автоматически расширит выделение и предложит выбрать столбец и порядок сортировки.

Вы можете для экономии времени использовать кнопки сортировки АЯ или ЯА, но при этом курсор нужно обязательно установить на столбец с формулой СЛЧИС(). Заголовок колонки желательно написать по-русски, иначе он имеет шансы переместиться в конец таблицы.

Теперь осталось нажать ОК, и строки таблицы будут пересортированы и произвольно перемешаны. Пусть вас не смущает тот факт, что после сортировки по столбцу D вы видите в нем совершенно не упорядоченные числа. Дело в том, что сортировка меняет порядок строк, и тут же вновь происходит пересчёт всех формул СЛЧИС(). Но ведь наша задача не отсортировать, а произвольно перемешать строки,  не так ли?Если вас не совсем устраивает рандомизация вашей таблицы, нажмите кнопку сортировки еще раз, чтобы вновь перемешать её.

Как случайно выбрать в Excel с помощью инструмента Randomize. Теперь, когда вы знаете несколько формул для получения случайной выборки в Excel, давайте посмотрим, как можно добиться того же результата буквально одним щелчком мыши. Если в вашем Excel установлена надстройка Ultimate Suite , вы можете использовать инструмент случайной сортировки. Для этого делаете следующее:

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

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

Поясним, что скрывается за каждой из цифр.

  • По умолчанию будет выбран весь ваш диапазон данных. Выберите другой диапазон, выделив его при помощи мышки или введя адрес в это поле.
  • Если вы изменили диапазон, снова выделите всю таблицу, щелкнув значок «Развернуть выделение» .
  • Вы можете исключить строки заголовков из выбора, если таковые есть, установив флажок Моя таблица имеет 1 строку заголовка .

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

  • некоторое количество или процент  строк.
  • аналогично — столбцы на листе.
  • несколько или какую-то часть ячеек из вашего диапазона.
  • Ознакомившись с панелью надстройки, выберите, что нужно получить: случайные строки, столбцы или ячейки.
  • Укажите число или процент для желаемого размера выборки.
  • Нажмите кнопку Select.

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

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

В статье объясняются особенности алгоритма получения случайных чисел Excel и показано, как использовать функции СЛЧИС и СЛУЧМЕЖДУ для чисел, дат, паролей и других текстовых выражений. Прежде чем мы углубимся в различные методы производства случайных чисел в Excel, давайте определимся, что они собой представляют. Говоря простым языком, случайные данные – это последовательность цифр, букв или других символов, в которой отсутствуют какой-либо шаблон или закономерность. Случайность имеет множество различных приложений в криптографии, статистике, лотерее, азартных играх и многих других областях. И поскольку она всегда была востребована, с древних времен существовали различные методы создания случайных чисел, такие как подбрасывание монет, бросание кубиков, тасование игральных карт и так далее. Конечно, мы не будем полагаться на такие «экзотические» техники в этом руководстве и сосредоточимся на том, что может предложить генератор случайных чисел Excel.

  • Как работает алгоритм случайных чисел
  • Случайные числа в указанном диапазоне при помощи функции СЛУЧМЕЖДУ
  • Случайные числа с нужным количеством десятичных знаков
  • Случайные даты в Excel
  • Как вставить случайное время в Excel
  • Случайные дата и время
  • Как получить случайные буквы
  • Создание текстовых строк и паролей
  • Как предотвратить постоянный пересчет формул СЛЧИС и СЛУЧМЕЖДУ
  • Как создать уникальные случайные числа в Excel

Создание случайных чисел в Excel – основы. Случайные числа — это без всякой закономерности выбранная последовательность чисел. Но в жизни практически всегда применяются псевдослучайные числа. Их получают по заранее определенному сложному алгоритму. Для постороннего человека они выглядят случайными, но все же их можно предсказать, если узнать этот сложный алгоритм. Псевдослучайные числа применяются во всех компьютерных программах. Хотя функции случайных чисел Excel удовлетворяют всем стандартным тестам на случайность, они все же не выдают истинные случайные числа. Но это не значит, что Excel для этих целей не годится. Псевдослучайные числа, создаваемые функциями Excel, прекрасно подходят для многих целей. Давайте подробнее рассмотрим, как это происходит, чтобы вы знали, чего от стандартных функций Excel можно ожидать, а чего нельзя. Как и большинство компьютерных программ, генератор случайных чисел Excel производит псевдослучайные числа с помощью некоторых математических формул. Для вас это означает, что теоретически эти числа, создаваемые Excel, предсказуемы. Но только при условии, что кто-то знает все детали алгоритма. Это причина того, что это никогда не было задокументировано и вряд ли когда-либо будет сделано. Итак, как можно создавать случайные числа в Excel?

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

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

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

СЛЧИС() * ( B — A ) + A

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

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

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

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

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

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

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

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

ВРЕМЗНАЧ ( время начала ) + СЛЧИС () * (ВРЕМЗНАЧ ( время начала ) — ВРЕМЗНАЧ ( время окончания ))

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

Предположим, вы хотите вставить произвольные даты между 1 июня 2021 года и 31 августа того же года со временем между 8:30 и 17:00. Следующая формула подойдет для вас:=СЛУЧМЕЖДУ(ДАТАЗНАЧ(«1-июн-2021»); ДАТАЗНАЧ(«31-авг-2021»)) + СЛУЧМЕЖДУ(ВРЕМЗНАЧ(«8:30») * 10000; ВРЕМЗНАЧ(«17:00») * 10000) / 10000Вы также можете указать дату и время, используя функции ДАТА и ВРЕМЯ соответственно:=СЛУЧМЕЖДУ(ДАТА(2021;6;1); ДАТА(2021;8;31)) + СЛУЧМЕЖДУ(ВРЕМЯ(8;30;0) * 10000; ВРЕМЯ(17;0;0) * 10000) / 10000

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

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

  • КОДСИМВ() возвращает коды ANSI для указанных букв.
  • СЛУЧМЕЖДУ() принимает код, возвращаемый функцией КОДСИМВ, как нижнее и верхнее значения диапазона.
  • СИМВОЛ() преобразует коды ANSI, возвращаемые СЛУЧМЕЖДУ, в соответствующие буквы.

Примечание. Так как код ANSI различный для прописных и строчных букв, эта формула чувствительна к регистру. Если кто-то помнит таблицу кодов символов ANSI, ничто не мешает вам передать коды букв непосредственно в функцию СЛУЧМЕЖДУ. Например, чтобы получить заглавные буквы между A (код ANSI 192) и Я (код ANSI 223), вы пишете:Чтобы получить строчные буквы от а (код ANSI 224) до я (код ANSI 255), используйте следующую формулу:

  • 192-223  — прописные буквы А-Я
  • 224-255 — строчные буквы а-я

Чтобы вставить случайный специальный символ, например,  ! «# $% & ‘() * +, -. /, используйте функцию СЛУЧМЕЖДУ с первым параметром, установленным на 33 (код ANSI для «!’), и вторым параметром 47 (код ANSI для «/»). Создание текстовых строк и паролей в ExcelЧтобы создать произвольную текстовую строку в Excel, вам просто нужно объединить несколько функций СИМВОЛ и СЛУЧМЕЖДУ. Например, чтобы получить список паролей, состоящих из 4 символов, вы можете использовать формулу, подобную этой:=СЛУЧМЕЖДУ(0;9) & СИМВОЛ(СЛУЧМЕЖДУ(65;90)) & СИМВОЛ(СЛУЧМЕЖДУ(97; 122)) & СИМВОЛ(СЛУЧМЕЖДУ(33;47))Чтобы сделать запись более компактной, я ввел коды ANSI непосредственно в формулу. Четыре функции возвращают следующие случайные значения:

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

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

Внимание! Если вы используете аналогичную формулу для создания случайных паролей, они не будут слишком надежными. Конечно, вы можете создавать более длинные текстовые строки, связывая больше функций. Однако невозможно изменить порядок следования, т. первая по счёту функция всегда возвращает цифру, вторая функция возвращает заглавную букву и так далее. Если вы ищете расширенный генератор случайных паролей в Excel, способный создавать текстовые строки любой длины и шаблона, вы можете проверить возможности Advanced Random Generator для тестовых строк. Кроме того, имейте в виду, что текстовые строки, созданные с помощью приведенной выше формулы, будут изменяться каждый раз, когда ваш рабочий лист пересчитывается. А это автоматически происходит после ввода либо корректировки любого значения либо формулы на листе. Чтобы гарантировать, что ваши строки или пароли остаются неизменными после их создания, вам нужно будет заблокировать функцию СЛУЧМЕЖДУ от обновления значений, что подводит нас непосредственно к следующему разделу. Как предотвратить постоянный пересчет формул СЛЧИС и СЛУЧМЕЖДУ. Если вы хотите получить постоянный набор чисел, дат или текстовых строк, который не будет меняться каждый раз при пересчете таблицы, используйте один из следующих методов:

  • Чтобы функции СЛЧИС или СЛУЧМЕЖДУ не пересчитывались в одной определённой ячейке, выберите её, переключитесь на строку формул и нажмите клавишу F9, чтобы заменить формулу ее значением.
  • Можно использовать специальный инструмент преобразования формул в значения, который является составной частью надстройки Ultimate Suite.

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

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

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

Расширенный генератор случайных чисел для 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.

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

Функция СЛЧИС доступна во всех версиях: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Поскольку функция Excel СЛЧИС не имеет аргументов, вы просто вводите =СЛЧИС() в ячейке и затем копируете формулу на столько ячеек, сколько хотите:

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

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

А теперь давайте сделаем еще один шаг и напишем несколько формул СЛЧИС для генерации случайных чисел в соответствии с определенными условиями.

Генератор случайных чисел от нуля до заданной верхней границы диапазона

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

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

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

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

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

= СЛЧИС() * (B — A) + A

Где A – это нижнее значение границы (наименьшее число), а B – верхнее значение границы (наибольшее число).

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

Примечание. Эта формула генерации случайных чисел никогда не вернет число, равное наибольшему числу указанного диапазона (значение B).

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

Чтобы функция Excel СЛЧИС создавала случайные целые числа, возьмите одну из вышеупомянутых формул и заверните ее в функцию ЦЕЛОЕ.

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

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

= ЦЕЛОЕ (СЛЧИС()*(50-10)+10)

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

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

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

СЛУЧМЕЖДУ (нижняя граница; верхняя граница)

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

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

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

= СЛУЧМЕЖДУ(10; 50)

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

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

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

Функция СЛУЧМЕЖДУ доступна в следующих версиях: Excel 2016, Excel 2013, Excel 2010 и Excel 2007.

В более ранней версии Excel 2003, вы можете использовать формулу СЛЧИС, рассмотренную выше.

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

Создание случайных чисел с заданным количеством знаков после запятой

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

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

Следующая формула СЛУЧМЕЖДУ возвращает случайные десятичные числа от 1 до 50:

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

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

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

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

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

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

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

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

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

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

Во внутренней системе Excel времена хранятся как десятичные числа, и вы можете использовать стандартную функцию Excel СЛЧИС для вставки случайных действительных чисел, а затем просто применить формат времени к ячейкам:

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

Генератор случайных чисел в Excel – Генерация случайного времени функцией СЛЧИС и применение к ней формата Время

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

Генератор случайного времени в указанном диапазоне

Чтобы вставить произвольное время между любыми двумя указанными вами временными интервалами, используйте функцию ВРЕМЯ в сочетании с Excel СЛЧИС:

= ВРЕМЯ (время начала) + СЛЧИС () * (ВРЕМЯ (время начала) — ВРЕМЯ (время окончания))

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

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

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

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

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

Разберем функции, в приведенной выше формуле:

  • КОДСИМВ возвращает числовые коды ANSI для указанных букв.
  • СЛУЧМЕЖДУ принимает числа, возвращаемые функциями КОДСИМВ , как нижнее и верхнее значения диапазона.
  • СИМВОЛ преобразует случайные коды ANSI, возвращаемые СЛУЧМЕЖДУ, в соответствующие буквы.

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

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

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

Если кто-то наизусть знает Коды символов ANSI, ничто не мешает вам передавать коды непосредственно в функцию СЛУЧМЕЖДУ.

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

Чтобы генерировать строчные буквы между а (код ANSI 97) в z (код ANSI 122), вы используете следующую формулу:

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

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

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

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

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

Генератор случайных чисел в Excel – Вставка значений

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

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

Для того чтобы сгенерировать случайные числа нормального распределения, переходим во вкладку «ДАННЫЕ», в группе «Анализ» выбираем «Анализ данных».

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

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

В открывшемся списке выбираем «Генерация случайных чисел» и нажимаем кнопку «ОК».

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

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

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

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

После того, как все данные введены нажимаем кнопку «ОК», и в результате получаем сгенерированные случайные числа нормального распределения.

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

Случайная выборка

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

  • Объем данных слишком велик, поэтому мы удовлетворяемся анализом случайной выборки из полного набора данных.
  • Выбор победителей из числа участников какого-либо конкурса или лотереи.

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

Способ 1. Случайная сортировка

Добавить к нашему списку еще один столбец и вставить в него функцию генерации случайных чисел СЛЧИС (RAND). Затем отсортировать наш список по добавленному столбцу (Данные — Сортировка) и взять N первых элементов из получившейся таблицы:

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

Способ 2. Функция НАИМЕНЬШИЙ

Этот способ заключается в использовании функции НАИМЕНЬШИЙ (SMALL) для выбора из списка N позиций с наименьшим случайным числом в столбце А:

После выбора пяти (в нашем примере) наименьших случайных чисел из столбца А, мы вытаскиваем имена, которые соответствуют этим числам с помощью функции ВПР (VLOOKUP).

Способ 3. Случайная выборка без повторов — функция Lotto на VBA

Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис — Макрос — Редактор Visual Basic), вставим новый модуль через меню Insert — Module и скопируем туда текст вот такой функции:

Function Lotto(Bottom As Integer, Top As Integer, Amount As Integer)
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Dim Out(1000) As Variant

Application. Volatile

ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i

For i = Top To Bottom + 1 Step -1
r = Int(Rnd() * (i — Bottom + 1)) + Bottom
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
j = 0
For i = Bottom To Bottom + Amount — 1
Out(j) = iArr(i)
j = j + 1
Next i

Lotto = Application. Transpose(Out)

End Function

У этой функции будет три аргумента:

  • Bottom — нижняя граница интервала случайных чисел
  • Top — верхняя граница интервала случайных чисел
  • Amount — количество случайных чисел, которое мы хотим отобрать из интервала

, например, чтобы отобрать 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)

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

Обратите внимание, что наша функция Lotto должна быть введена как формула массива, т. сначала необходимо выделить диапазон ячеек результатов (D2:D6) затем ввести нашу функцио Lotto и, после ввода аргументов функции, нажать Ctrl+Shift+Enter, чтобы ввести эту функцию именно как функцию массива во все выделенные ячейки.

Ну, а дальше останется при помощи уже знакомой функции ВПР (VLOOKUP) вытащить имена из списка, соответствующие случайным номерам.

  • Использование функции ВПР (VLOOKUP) для подстановки значений
  • Создание макросов и пользовательских функций на VBA
  • Функция RandomSelect из надстройки PLEX