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

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

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

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

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

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

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

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

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

   =RAND()

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

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

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

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

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

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

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

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

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

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

   =INDEX($B2:$B53,RANDBETWEEN(1,COUNTA($B2:$B53)),1)

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

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

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

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

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

   =RAND()

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

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

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

   =INDEX($B$2:$B$53, RANK.EQ(C2, $C$2:$C$53) + COUNTIF($C$2:C53, C2) - 1, 1)

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

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

Если вы используете Excel 365 или Excel 2021, вы можете легко создать случайную выборку с помощью новых функций «SORTBY» и «RANDARRAY».

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

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

   =SORTBY(B2:B53,RANDARRAY(COUNTA(B2:B53)))

   
   Примечания

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

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

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

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

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

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

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

Результат

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

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