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

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

• Выбор случайной выборки с помощью формул
  • Выбор случайных значений/строк
  • Выбор случайных значений из списка
  • Выбор случайных значений из списка без дубликатов
  • Выбор случайных значений из списка в Excel 365/2021
• Несколько кликов для выбора случайной выборки с помощью удобного инструмента

Выбор случайной выборки с помощью формул

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

Выбор случайных значений/строк с помощью функции СЛЧИС

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

Шаг 1: Добавление вспомогательного столбца

1. Сначала вам нужно добавить вспомогательный столбец к вашему диапазону данных. В данном случае я выбираю ячейку E1 (ячейка рядом с заголовком последнего столбца диапазона данных), ввожу заголовок столбца, а затем ввожу следующую формулу в ячейку E2 и нажимаю Enter чтобы получить результат.
   Совет: Функция СЛЧИС генерирует случайное число между 0 и 1.

   =СЛЧИС()

   
2. Выберите ячейку с формулой. Затем дважды щелкните маркер заполнения (зеленый квадрат в правом нижнем углу ячейки), чтобы заполнить эту формулу в остальные ячейки вспомогательного столбца.

Шаг 2: Сортировка вспомогательного столбца

1. Выберите как диапазон данных, так и вспомогательный столбец, перейдите на вкладку Данные, нажмите Сортировка.
   
2. В окне Сортировка вам нужно:
   1. Сортировать по вашему вспомогательному столбцу ("Вспомогательный столбец" в нашем примере).
   2. Сортировать по значениям ячеек.
   3. Выберите нужный порядок сортировки.
   4. Нажмите кнопку ОК. Смотрите скриншот.
      

Теперь весь диапазон данных отсортирован по вспомогательному столбцу.

Шаг 3: Копирование и вставка случайных строк или значений для получения результатов

После сортировки строки в вашем исходном диапазоне данных будут в случайном порядке. Теперь вы можете просто выбрать первые n строк, где n — это количество случайных строк, которые вы хотите выбрать. Затем нажмите Ctrl + C, чтобы скопировать выбранные строки, и вставьте их куда хотите.

Совет: Если вы хотите случайным образом выбрать значения только из одного из столбцов, просто выберите первые n ячеек в этом столбце.

Выбор случайных значений из списка с помощью функции СЛУЧМЕЖДУ

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

1. В данном случае мне нужно сгенерировать 7 случайных значений из диапазона B2:B53. Я выбираю пустую ячейку D2, ввожу следующую формулу и нажимаю Enter чтобы получить первое случайное значение из столбца B.

   =ИНДЕКС($B2:$B53;СЛУЧМЕЖДУ(1;СЧЁТЗ($B2:$B53));1)

   
2. Затем выберите ячейку с формулой и перетащите маркер заполнения вниз, пока не будут сгенерированы оставшиеся 6 случайных значений.
   

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

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

Шаг 1: Добавление вспомогательного столбца

1. Сначала вам нужно создать вспомогательный столбец рядом со столбцом, из которого вы хотите выбрать случайную выборку. В данном случае я выбираю ячейку C2 (ячейка рядом со второй ячейкой столбца B), ввожу следующую формулу и нажимаю Enter.
   Совет: Функция СЛЧИС генерирует случайное число между 0 и 1.

   =СЛЧИС()

   
2. Выберите ячейку с формулой. Затем дважды щелкните маркер заполнения (зеленый квадрат в правом нижнем углу ячейки), чтобы заполнить эту формулу для остальных ячеек во вспомогательном столбце.

Шаг 2: Получение случайных значений из списка без дубликатов

1. Выберите ячейку рядом с первой ячейкой результата во вспомогательном столбце, введите следующую формулу и нажмите Enter чтобы получить первое случайное значение.

   =ИНДЕКС($B$2:$B$53; РАНГ.EQ(C2; $C$2:$C$53) + СЧЁТЕСЛИ($C$2:C53; C2) - 1; 1)

   
2. Затем выберите ячейку с формулой и перетащите маркер заполнения вниз, чтобы получить случайное количество значений.
   

Выбор случайных значений из списка в Excel 365/2021

Если вы используете Excel 365 или 2021, вы можете применить новые функции «СОРТИРОВАТЬПО» и «МАССИВСЛЧИС», чтобы легко сгенерировать случайную выборку в Excel.

Шаг 1: Добавление вспомогательного столбца

1. Сначала вам нужно добавить вспомогательный столбец к вашему диапазону данных. В данном случае я выбираю ячейку C2 (ячейка рядом со второй ячейкой столбца, из которого вы хотите выбрать случайные значения), ввожу следующую формулу и нажимаю Enter чтобы получить результаты.

   =СОРТИРОВАТЬПО(B2:B53;МАССИВСЛЧИС(СЧЁТЗ(B2:B53)))

   
   Примечания

   • В формуле B2:B53 — это список, из которого вы хотите выбрать случайную выборку.
   • Если вы используете Excel 365, список случайных значений будет сгенерирован автоматически после нажатия клавиши Enter.
   • Если вы используете Excel 2021, после получения первого случайного значения выберите ячейку с формулой и перетащите маркер заполнения вниз, чтобы получить желаемое количество случайных значений.
   • Чтобы обновить случайные значения, нажмите клавишу F9.
   • Каждый раз при обновлении листа, например при добавлении новых данных, изменении ячеек, удалении данных и т.д., случайные результаты автоматически изменятся.

Шаг 2: Копирование и вставка случайных значений для получения результатов

Во вспомогательном столбце теперь вы можете просто выбрать первые n ячеек, где n — это количество случайных значений, которые вы хотите выбрать. Затем нажмите Ctrl + C, чтобы скопировать выбранные значения, щелкните правой кнопкой мыши пустую ячейку и выберите Значения в разделе Параметры вставки в контекстном меню.

Несколько кликов для выбора случайной выборки с помощью удобного инструмента

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

После установки Kutools для Excel нажмите Kutools > Выбрать > Случайно выбрать, затем вам нужно настроить следующее.

• Выберите столбец или диапазон, из которого вы хотите выбрать случайные значения, строки или столбцы.
• В диалоговом окне Сортировка/Выбор диапазона случайным образом укажите количество случайных значений для выбора.
• Выберите вариант в разделе Тип выбора.
• Нажмите ОК.
  

Результат

Я указал число 5 в разделе "Количество ячеек для выбора" и выбрал опцию "Выбрать случайные строки" в разделе "Тип выбора". В результате 5 строк данных будут случайным образом выбраны в указанном диапазоне. Затем вы можете скопировать и вставить эти выбранные строки куда хотите.

Связанные статьи