Как сгенерировать случайные числа в Excel (без дубликатов)

Содержание: [Показать]

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

Концепция «случайности» также играет важную роль при моделировании сложных систем. Это помогает вам проверять данные. Мы также часто используем случайные числа для создания наборов данных для наших руководств!

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

Если вы посмотрите « Как сгенерировать случайные числа без дубликатов в Excel », вы, вероятно, найдете как минимум 10 различных способов сделать это.

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

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

Оглавление

Функции генерации случайных чисел в Excel

В Excel есть в основном 2 функции генерации случайных чисел. Давайте посмотрим на них по очереди:

RANDBETWEEN Формула

Функция RANDBETWEEN - отличная функция, которая помогает вам генерировать случайные целые числа между верхним и нижним пределом.

Синтаксис этой функции:

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

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

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

Формула RAND

Функция СЛЧИС возвращает десятичное случайное число от 0 до 1. Она не принимает аргументов, поэтому ее синтаксис просто

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

Основные различия между RAND () и RANDBETWEEN ():

  • RANDBETWEEN возвращает целое число, а RAND возвращает десятичное число.
  • RANDBETWEEN требует двух параметров, определяющих диапазон, в котором будут генерироваться случайные числа. RAND не принимает никаких параметров. Он может генерировать любое число от 0 до 1.
  • RANDBETWEEN с меньшей вероятностью, чем RAND, генерирует уникальные случайные числа.

И RANDBETWEEN, и RAND пересчитывают значения каждый раз при обновлении рабочего листа.

Это означает, что вы получаете новое случайное число каждый раз, когда изменяете любое значение ячейки на листе или нажимаете клавишу F9.

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

Использование RAND и RANK для получения случайных чисел в Excel без дубликатов

Вы можете комбинировать функции RAND и RANK, чтобы получить набор случайных чисел без дубликатов.

Функция RANK возвращает ранг числа в наборе чисел.

Его синтаксис следующий:

Первый параметр - это номер или ссылка, которую вы хотите ранжировать.

Второй параметр - это набор чисел или диапазон ячеек, по которым вы будете ранжироваться.

Третий параметр необязательный. Он указывает, хотите ли вы ранжироваться в порядке возрастания или убывания. Если это 0, номера ранжируются в порядке убывания. В противном случае номера ранжируются в порядке возрастания. По умолчанию номера ранжируются в порядке убывания.

Допустим, вам нужно 10 случайных чисел от A2 до A11. Вот как вы можете объединить две функции:

  1. В первой ячейке (A2) введите: = RAND (). Это сгенерирует случайное число от 0 до 1.
  2. Потяните вниз маркер заполнения (расположенный в правом нижнем углу ячейки), чтобы скопировать формулу в необходимое количество ячеек. Поэтому, если вам нужно 10 случайных чисел, скопируйте их в ячейку A11.
  3. В соседнем столбце (B) используйте следующую формулу RANK: = RANK (A2, $ A $ 2: $ A $ 11). Второй параметр - это диапазон абсолютных местоположений ячеек. Это гарантирует, что ссылки на ячейки не изменятся при копировании формулы в другие ячейки.
  4. Нажмите клавишу возврата.
  5. Перетащите маркер заполнения ячейки (B2) или просто дважды щелкните по нему.
  6. Сейчас эти числа непостоянны, поскольку функция RAND () всегда пересчитывает каждый раз, когда происходит изменение на листе. Чтобы этого избежать, скопируйте ячейки (B2: B11) и вставьте их в то же место, что и значение.
  7. Удалите первый столбец (A).

Это должно дать вам список из 10 случайных чисел, и это тоже без каких-либо повторений.

Все, что мы здесь сделали, это получили случайный набор десятичных чисел от 0 до 1, а затем ранжировали эти числа от 1 до 10, чтобы получить уникальные случайные целые числа.

Таким образом, мы получили хороший случайный набор чисел от 1 до 10 в столбце B (перед удалением столбца A).

Этот метод должен дать вам полностью случайные и уникальные числа с гарантией 99%. Но могут быть небольшие расхождения, если вам нужно создать гораздо больший набор чисел.

Для этого вы можете использовать опцию «Удалить дубликаты», чтобы избавиться от любых дубликатов.

Выберите диапазон ячеек, содержащий случайный набор чисел.

На вкладке « Данные » выберите « Удалить дубликаты» в группе « Инструменты для работы с данными ».

Использование RAND, LARGE и MATCH для получения случайных чисел в Excel без дубликатов

Аналогичный метод заключается в использовании RAND в сочетании с функциями LARGE и MATCH. Функция НАИБОЛЬШИЙ возвращает k-е по величине значение в списке, например, четвертое по величине значение или пятое по величине значение и т. Д. Синтаксис этой функции:

Здесь первый параметр - это набор чисел или диапазон ячеек, в котором вы хотите найти наибольший

Второй параметр - это целое число, которое определяет позицию от наибольшего значения, то есть k-ю позицию.

Функция ПОИСКПОЗ ищет элемент в диапазоне ячеек, а затем возвращает относительное положение этого элемента. Синтаксис этой функции:

Здесь первый параметр - это значение, которое вы ищете.

Второй параметр - это набор, из которого вы хотите найти позицию значения.

Третий параметр необязательный. В основном он используется для указания типа сопоставления, которое должно быть выполнено (наибольшее значение меньше, наименьшее значение больше или точное совпадение).

Допустим, вам нужно 10 случайных чисел от A2 до A10. Вот как вы можете объединить эти три функции:

  1. В первой ячейке (A2) введите: = RAND (). Это сгенерирует случайное число от 0 до 1.
  2. Потяните вниз маркер заполнения (расположенный в правом нижнем углу ячейки), чтобы скопировать формулу в необходимое количество ячеек. Поэтому, если вам нужно 10 случайных чисел, скопируйте их в ячейку A11.
  3. В соседнем столбце (B) используйте формулу НАИБОЛЬШИЙ, как показано ниже: = НАИБОЛЬШИЙ ($ A $ 2: $ A $ 11, СТРОКА (A1)). Первый параметр - это диапазон абсолютных местоположений ячеек. Это гарантирует, что ссылки на ячейки не изменятся при копировании формулы в другие ячейки. Здесь мы пытаемся найти 1-е наибольшее значение от A2 до A11. СТРОКА (A1) означает номер строки A1, другими словами, цифру 1. Эта формула также могла быть записана как: = НАИБОЛЬШИЙ ($ A $ 2: $ A $ 11,1). Но поскольку мы хотим, чтобы значение изменялось при копировании в каждую строку, вместо этого мы использовали ROW (A1).
  4. Нажмите клавишу возврата
  5. Перетащите маркер заполнения ячейки (B2) или просто дважды щелкните по нему.
  6. В следующем столбце (C) используйте функцию ПОИСКПОЗ следующим образом: = ПОИСКПОЗ (B2, $ A $ 2: $ A $ 11,0). Здесь мы пытаемся найти положение значения в B2 в диапазоне от A2 до A11. Нам нужно точное совпадение, поэтому мы указываем третий параметр как 0.
  7. Потяните вниз маркер заполнения ячейки (C2) или просто дважды щелкните по нему.
  8. В настоящий момент эти числа изменчивы, поскольку функция RAND () всегда пересчитывает каждый раз, когда в таблице происходит изменение. Чтобы этого избежать, скопируйте ячейки (C2: C11) и вставьте их в то же место, что и значение.
  9. Удалите первые два столбца (A и B).

Это должно дать вам список из 10 случайных чисел без каких-либо повторений.

В этом методе мы получили случайный набор десятичных чисел от 0 до 1 в столбце A, а затем расположили их в порядке убывания в столбце B.

Затем мы нашли позицию каждого числа из столбца B в наборе случайных чисел столбца A. Это дало нам случайный набор чисел от 1 до 10 в столбце C (перед удалением столбцов A и B).

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

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

Есть много других способов сделать это. Мы предоставляем вам решать, какой из способов вы найдете наиболее простым и эффективным.