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

В учебнике основное внимание уделяется тому, как выполнять случайную выборку в Excel без повторений. Вы найдете решения для Excel 365, Excel 2021, Excel 2019 и более ранних версий.

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

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

Работает только в Excel 365 и Excel 2021, которые поддерживают динамические массивы.

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

ИНДЕКС(СОРТИРОВАТЬ(данныеСЛУЧАЙ(СТРОКИ(данные))), ПОСЛЕДОВАТЕЛЬНОСТЬ(н))

Где н – желаемый размер выделения.

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

= ИНДЕКС (СОРТИРОВАТЬ (A2: A10, СЛУЧАЙНЫЙ МАССИВ (СТРОКИ (A2: A10))), ПОСЛЕДОВАТЕЛЬНОСТЬ (5))

Для удобства вы можете ввести размер выборки в предопределенную ячейку, скажем, C2, и указать ссылку на ячейку для функции ПОСЛЕДОВАТЕЛЬНОСТЬ:

= ИНДЕКС (СОРТИРОВАТЬ (A2: A10, СЛУЧАЙНЫЙ МАССИВ (СТРОКИ (A2: A10))), ПОСЛЕДОВАТЕЛЬНОСТЬ (C2))
Случайный выбор Excel из списка без дубликатов

Как работает эта формула:

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

Подробная разбивка приведена ниже:

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

RANDARRAY(СТРОКИ(A2:C10))

Этот массив случайных десятичных знаков используется в качестве массива «сортировки по» функцией SORTBY. В результате исходные данные перемешиваются случайным образом.

Из случайно отсортированных данных вы извлекаете выборку определенного размера. Для этого вы передаете перетасованный массив функции ИНДЕКС и запрашиваете получение первого Н значений с помощью функции ПОСЛЕДОВАТЕЛЬНОСТЬ, которая создает последовательность чисел от 1 до Н. Поскольку исходные данные уже отсортированы в случайном порядке, нам все равно, какие позиции извлекать, имеет значение только количество.

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

Работает только в Excel 365 и Excel 2021, которые поддерживают динамические массивы.

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

ИНДЕКС(СОРТИРОВАТЬ(данныеСЛУЧАЙ(СТРОКИ(данные))), ПОСЛЕДОВАТЕЛЬНОСТЬ(н), {1,2,…})

Где н — размер выборки, а {1,2,…} — номера столбцов для извлечения.

В качестве примера выберем случайные строки из A2:C10 без повторяющихся записей, исходя из размера выборки в F1. Поскольку наши данные находятся в 3 столбцах, мы подставляем эту константу массива в формулу: {1,2,3}

= ИНДЕКС (СОРТИРОВАТЬ (A2: C10, RANDARRAY (СТРОКИ (A2: C10))), ПОСЛЕДОВАТЕЛЬНОСТЬ (F1), {1,2,3})

И получить следующий результат:
Выбор случайных строк в Excel без дубликатов

Как работает эта формула:

Формула работает точно по той же логике, что и предыдущая. Небольшое изменение, которое имеет большое значение, заключается в том, что вы указываете как row_num а также номер_столбца аргументы функции ИНДЕКС: row_num поставляется компанией SEQUENCE и номер_столбца по константе массива.

Как сделать случайную выборку в Excel 2010-2019

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

Предположим, вам нужен случайный выбор из списка в A2:A10. Это можно сделать с помощью двух отдельных формул:

  1. Сгенерируйте случайные числа с помощью формулы Рэнда. В нашем случае мы вводим его в B2, а затем копируем в B10:

    =СЛУЧАЙ()

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

    =ИНДЕКС($A$2:$A$10, РАНГ.EQ(B2, $B$2:$B$10) + СЧЁТЕСЛИ($B$2:B2, B2) – 1)

  3. Скопируйте приведенную выше формулу в столько ячеек, сколько случайных значений вы хотите выбрать. В этом примере нам нужно 4 имени, поэтому мы копируем формулу от E2 до E5.

Сделанный! Наша случайная выборка без дубликатов выглядит следующим образом:
Случайная выборка в Excel 2010–2019 без повторов

Как работает эта формула:

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

Функция RAND заполняет диапазон B2:B10 случайными десятичными знаками.

Функция RANK.EQ вычисляет ранг случайного числа в заданной строке. Например, в E2 RANK.EQ(B2, $B$2:$B$10) оценивает число в B2 относительно всех чисел в B2:B10. При копировании в E3 относительная ссылка B2 меняется на B3 и возвращает ранг числа в B3 и так далее.

Функция СЧЁТЕСЛИ находит, сколько вхождений заданного числа есть в вышеуказанных ячейках. Например, в E2 СЧЁТЕСЛИ($B$2:B2, B2) проверяет только одну ячейку — саму ячейку B2, и возвращает 1. В E5 формула изменяется на СЧЁТЕСЛИ($B$2:B5, B5) и возвращает 2, потому что B5 содержит то же значение, что и B2 (обратите внимание, это только для того, чтобы лучше объяснить логику формулы; в небольшом наборе данных шансы получить повторяющиеся случайные числа близки к нулю).

В результате для всех первых вхождений функция СЧЁТЕСЛИ возвращает 1, из которого вы вычитаете 1, чтобы сохранить исходный рейтинг. Для 2-го вхождения COUNTIF возвращает 2. Вычитая 1, вы увеличиваете рейтинг на 1, тем самым предотвращая дублирование рангов.

Например, для B2 RANK.EQ возвращает 1. Поскольку это первое вхождение, COUNTIF также возвращает 1. RANK.EQ + COUNTIF дает 2. И – 1 восстанавливает ранг 1.

Теперь посмотрим, что произойдет в случае второго случая. Для B5 функция RANK.EQ также возвращает 1, а COUNTIF возвращает 2. Сложение их дает 3, из которых вы вычитаете 1. В конечном результате вы получаете 2, что представляет ранг числа в B5.

Ранг переходит к row_num аргумент функции ИНДЕКС, и она выбирает значение из соответствующей строки (т. номер_столбца аргумент опущен, поэтому по умолчанию он равен 1). Вот почему так важно избегать дублирования ранжирования. Если бы не функция COUNTIF, RANK.EQ выдавала бы 1 как для B2, так и для B5, в результате чего INDEX дважды возвращал бы значение из первой строки (Эндрю).

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

Поскольку все функции рандомизации в Excel, такие как СЛУЧАЙ, СЛУЧМЕЖДУ и СЛУЧАЙ, являются изменчивыми, они пересчитываются при каждом изменении на листе. В результате ваша случайная выборка будет постоянно меняться. Чтобы этого не произошло, используйте функцию «Специальная вставка» > «Значения», чтобы заменить формулы статическими значениями. Для этого выполните следующие шаги:

  1. Выделите все ячейки с вашей формулой (любая формула, содержащая функцию СЛУЧАЙ, СЛУЧМЕЖДУ или СЛУЧАЙ) и нажмите Ctrl + C, чтобы скопировать их.
  2. Щелкните правой кнопкой мыши выбранный диапазон и нажмите Специальная вставка > Ценности. В качестве альтернативы нажмите Shift + F10, а затем V, что является ярлыком для вышеупомянутой функции.

Подробные инструкции см. в разделе Как преобразовать формулы в значения в Excel.

Случайный выбор Excel: строки, столбцы или ячейки

Работает во всех версиях Excel 365 до Excel 2010.

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

  1. На инструменты вкладка, нажмите Рандомизировать > Выберите Случайно.
  2. Выберите диапазон, из которого вы хотите выбрать образец.
  3. На панели надстройки выполните следующие действия.
    • Выберите, хотите ли вы выбрать случайные строки, столбцы или ячейки.
    • Определите размер выборки: это может быть процент или число.
    • Нажмите на Выбирать кнопка.

Вот и все! Как показано на изображении ниже, случайная выборка выбирается непосредственно в вашем наборе данных. Если вы хотите скопировать его куда-нибудь, просто нажмите обычную комбинацию клавиш копирования (Ctrl + C).
Выбор случайной выборки непосредственно в наборе данных

Вот как выбрать случайную выборку в Excel без дубликатов. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Доступные загрузки

Случайная выборка без дубликатов – примеры формул (файл .xlsx)
Ultimate Suite 14-дневная полнофункциональная версия (файл .exe)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *