Случайная ячейка из диапазона в Excel

Обновлено: 03.07.2024

Возможны случаи, когда вам нужно сгенерировать случайные числа в Excel.

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

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

Создание случайных чисел в Excel

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

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

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

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

Функция СЛУЧМЕЖДУ принимает два аргумента — нижнее значение и верхнее значение. Это даст вам целое число только между двумя указанными числами.

Например, предположим, что я хочу сгенерировать 10 случайных чисел от 1 до 100.

Вот шаги для генерации случайных чисел с использованием СЛУЧМЕЖДУ:

Выберите ячейку, в которой вы хотите получить случайные числа.
В активной ячейке введите =СЛУЧМЕЖДУ(1100).
Удерживайте клавишу Control и нажмите Enter.

Это мгновенно даст мне 10 случайных чисел в выбранных ячейках.

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

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

Если вы согласны с дубликатами, СЛУЧМЕЖДУ – это самый простой способ генерировать случайные числа в Excel.

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

Создание уникальных случайных чисел с помощью функций RAND и RANK в Excel

Я несколько раз протестировал функцию СЛЧИС и не нашел повторяющихся значений. Но в качестве предостережения я рекомендую проверять повторяющиеся значения при использовании этой функции.

Предположим, я хочу сгенерировать 10 случайных чисел в Excel (без повторов).

Вот шаги для генерации случайных чисел в Excel без повторения:

Теперь вы можете использовать значения в столбце B в качестве случайных чисел.

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

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

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

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

Что такое случайная выборка?

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

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

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

Случайный выбор Excel с формулами

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

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

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

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

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

Естественно, эти формулы могут выбирать не только случайные имена, но и случайные числа, даты или любые другие случайные ячейки.

Как работают эти формулы

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

В частности, функция СЛУЧМЕЖДУ генерирует случайное целое число между двумя указанными вами значениями. Для нижнего значения вы указываете число 1. Для верхнего значения вы используете COUNTA или ROWS, чтобы получить общее количество строк. В результате СЛУЧМЕЖДУ возвращает случайное число от 1 до общего количества строк в вашем наборе данных. Это число передается в аргумент номер_строки функции ИНДЕКС, сообщая ей, какую строку выбрать. Для аргумента номер_столбца мы используем 1, так как хотим извлечь значение из первого столбца.

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

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

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

Со списком имен в ячейках A2:A16 выполните следующие действия, чтобы извлечь несколько случайных имен:

Введите формулу Rand в ячейку B2 и скопируйте ее вниз по столбцу:
=RAND()
Поместите приведенную ниже формулу в C2, чтобы извлечь случайное значение из столбца A:

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

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

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

Формула RAND заполняет столбец B случайными числами.
Функция RANK возвращает ранг в виде случайного числа в той же строке. Например, RANK(B2,$B$2:$B$16) в ячейке C2 получает ранг числа в B2. При копировании в C3 относительная ссылка B2 изменяется на B3 и возвращает ранг числа в B3 и т. д.
Число, возвращаемое функцией RANK, передается в аргумент номер_строки функции ИНДЕКС, поэтому она выбирает значение из этой строки. В аргументе номер_столбца вы указываете 1, потому что хотите извлечь значение из первого столбца.

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

Если вам нужна надежная формула для получения случайного выбора только с уникальными значениями, используйте комбинацию РАНГ + СЧЁТЕСЛИ или РАНГ.EQ + СЧЁТЕСЛИ вместо просто РАНГ. Подробное объяснение логики см. в разделе Уникальное ранжирование в Excel.

Полная формула немного громоздка, но на 100% не повторяется:

=ИНДЕКС($A$2:$A$16, RANK.EQ(B2, $B$2:$B$16) + СЧЁТЕСЛИ($B$2:B2, B2) - 1, 1)

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

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

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

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

Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец рабочей книги в Excel Random Selection.

Как сделать случайный выбор в Excel с помощью инструмента «Случайный порядок»

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

С нашим Ultimate Suite, установленным в вашем Excel, вы можете делать следующее:

Выберите любую ячейку в таблице.
Перейдите на вкладку Инструменты Ablebits в группу Утилиты и нажмите Рандомизировать > Выбрать случайным образом:
На панели надстройки выберите, что выбрать: случайные строки, случайные столбцы или случайные ячейки.
Укажите число или процент для желаемого размера выборки.
Нажмите кнопку "Выбрать". Готово!

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

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

Теперь вы можете нажать Ctrl + C, чтобы скопировать случайный образец, а затем нажать Ctrl + V, чтобы вставить его в нужное место на том же или другом листе.

Если вы хотите протестировать инструмент рандомизации на своих листах, загрузите пробную версию Ultimate Suite для Excel:

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

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

В наших руководствах мы будем использовать обе функции. Имейте в виду, что сгенерированные значения будут обновляться при каждом расчете.Таким образом, вы можете скопировать сгенерированные элементы и использовать «вставить как значение» («Специальная вставка» > «Значения»).

Выберите случайный элемент из списка

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

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

Допустим, у нас есть список в B3:B16. Наша формула должна быть либо,

Функции ROWS и COUNTA определяют верхний предел случайно сгенерированных чисел. Если вы уверены в количестве элементов в списке, вы можете игнорировать эти функции. Например;

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

Случайный элемент без дубликатов

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

Вспомогательный столбец содержит только функцию RAND.

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

Любой подход работает. В этом примере мы использовали RANK.EQ.

Функция RANK.EQ может проверять ранг случайно сгенерированного числа в списке и возвращать номер строки для использования в функции ИНДЕКС, как в СЛУЧМЕЖДУ.

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

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

Рисунок 1. Конечный результат функций

Синтаксис формулы ИНДЕКС

=INDEX(массив, номер_строки, [номер_столбца])

массив — список значений, в котором мы хотим найти значение из определенной позиции
номер_строки — строка, из которой мы хотим получить значение
[номер_столбца] — столбец, из которого мы хотим получить значение. Это необязательный параметр, и если он опущен, по умолчанию он равен 1.

Синтаксис формулы СЛУЧМЕЖДУ

=RANDBETWEEN(нижний, верхний)

внизу — значение, из которого мы хотим получить случайное значение
top — значение, к которому мы хотим получить случайное значение

Синтаксис формулы ROWS

Параметр функции ROW:

массив — массив, для которого мы хотим получить общее количество строк.

Настройка наших данных для функций

В столбце B («Имена») у нас есть список имен, для которых мы хотим найти случайное имя. Результат будет сохранен в ячейке E3

Рисунок 2. Данные, которые мы будем использовать в примере

Получить случайное имя из списка имен

В ячейке E3 мы хотим получить случайное имя из списка имен в диапазоне B3:B10.

Формула выглядит следующим образом:

=ИНДЕКС(B3:B10, СЛУЧМЕЖДУ(1, СТРОКИ(B3:B10)), 1)

Это массив B3:B10. Номер_строки равен RANDBETWEEN(1, ROWS(B3:B10)), а номер_столбца равен 1 .

Чтобы применить формулу, нам нужно выполнить следующие шаги:

Выберите ячейку E3 и нажмите на нее.
Вставьте формулу: =ИНДЕКС(B3:B10, СЛУЧМЕЖДУ(1, СТРОКИ(B3:B10)), 1)
Нажмите клавишу ввода.

Рисунок 3. Использование функции ИНДЕКС для получения случайного имени из списка

Функция ИНДЕКС выполняет поиск в диапазоне B3:B10. Номер_строки является результатом функции СЛУЧМЕЖДУ, которая возвращает случайное число от 1 до 8. В этом примере эта функция вернула 3, поэтому ИНДЕКС возвращает значение из третьей строки. Следовательно, результатом формулы в ячейке E3 будет «Иаков».

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

Я создал список случайных чисел с помощью randbetween. Я хочу отсортировать список от низкого к высокому, но когда я пытаюсь отсортировать, генерируется другой случайный список чисел. Не могли бы вы помочь?

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

У меня есть список случайно сгенерированных чисел от 1 до 100. Мне нужна формула, которая будет отображать количество экземпляров для каждого числа от 1 до 100. Например: Мой список генерирует 1000 случайно сгенерированных чисел от 1 до 100. Я хотите узнать, сколько раз число 50 выпадает из 1000 случайно сгенерированных чисел.

Читайте также: