Как случайным образом заполнить значения из списка данных в Excel?
Случайный выбор значений из заранее определенного списка в Excel — это распространенная задача с применением в анализе данных, симуляциях, рандомизированном распределении, выборке, тестировании сценариев и многом другом. Например, вы можете захотеть смоделировать розыгрыш призов, назначить случайные тестовые случаи для проверки качества или распределить задачи случайным образом между членами команды. Выполнение этого в Excel может сделать ваш рабочий процесс намного эффективнее и менее подверженным ошибкам по сравнению с ручным выбором.
Это подробное руководство проведет вас через несколько методов для достижения этой цели: от простых подходов на основе формул, подходящих для всех пользователей, до продвинутой автоматизации с помощью VBA, а также с использованием специализированных и удобных инструментов, таких как Kutools для Excel. Каждый метод имеет свои сильные стороны и идеальные сценарии использования, которые будут проанализированы ниже, чтобы помочь вам выбрать лучшее решение для ваших нужд.
Случайное заполнение значений из списка данных с помощью формул
В этом разделе мы проведем вас через несколько практических методов на основе формул для случайного заполнения значений из указанного списка. Эти решения не требуют дополнительной установки и могут быть быстро внедрены в большинстве современных версий Excel.
✅ Формула1: ИНДЕКС + СЛЧИСЛО функции
Комбинация функций ИНДЕКС и СЛЧИСЛО является классическим и совместимым со всеми версиями способом случайного выбора значений из списка. Она подходит для быстрого генерирования одного или нескольких случайных значений, где допускается повторный выбор, например, при случайной выборке или создании имитационных данных.
Чтобы использовать этот метод, просто скопируйте или введите следующую формулу в пустую ячейку (например, B2), затем перетащите маркер заполнения вниз, чтобы заполнить столько случайных значений, сколько необходимо. Помните, что так как формула включает изменчивые функции (например, СЛЧИСЛО), её результат будет меняться каждый раз при пересчете листа.
=INDEX($A$2:$A$15, RANDBETWEEN(1, COUNTA($A$2:$A$15)))
- A2:A15: Представляет список значений, из которого вы хотите случайно выбрать.
- СЧЁТЗ($A$2:$A$15): Динамически подсчитывает количество элементов в вашем списке, обеспечивая надежность формулы при изменении длины списка.
- СЛЧИСЛО(1, n): Генерирует случайное целое число между 1 и n (количество элементов в списке).
- ИНДЕКС(диапазон, номер): Извлекает элемент, соответствующий случайно выбранной позиции в вашем списке.
Меры предосторожности: Поскольку значение обновляется при любом изменении на листе, если вам нужно, чтобы результаты оставались неизменными, обязательно скопируйте заполненные ячейки и вставьте их как значения. Также эта стратегия не исключает дубликаты — если уникальность требуется, рассмотрите методы, описанные в следующих разделах, или выполните постобработку.
✅ Формула2: ИНДЕКС + RANDARRAY функции (Excel 365 / 2021+)
Комбинация функций ИНДЕКС и RANDARRAY подходит для пользователей Excel 365 и Excel 2021. Этот подход использует динамические массивы для вывода пакетов случайных выборов сразу, упрощая рабочие процессы, требующие множества случайных выборок одним шагом. Особенно полезен, когда вам нужно быстро получить определенное количество случайных выборок. Однако, как и в предыдущей формуле, этот метод не гарантирует уникальность результатов в пакете.
Чтобы использовать это решение, введите формулу в пустую ячейку, например B2, и нажмите Enter. Excel автоматически «разольет» сгенерированные случайные значения в последующие строки. Например, следующая формула выводит 5 случайных значений из списка:
=INDEX(A2:A15, RANDARRAY(5, 1, 1, COUNTA(A2:A15), TRUE))
- A2:A15: Назначенный список данных для случайного выбора.
- СЧЁТЗ(A2:A15): Подсчитывает записи в целевом списке.
- RANDARRAY(5,1,1, СЧЁТЗ(...), TRUE): Генерирует 5 случайных целых чисел между 1 и последней позицией в списке, создавая вертикальный массив (1 столбец).
- ИНДЕКС(A2:A15, …): Отображает каждое случайное число как значение из вашего списка.
Советы: Если вам нужно другое количество случайных значений, просто отрегулируйте 5 в RANDARRAY(5,1, ...) соответственно. Всегда помните, что если вам нужно, чтобы результаты оставались фиксированными, вставьте их как значения, поскольку выходные данные на основе формул обновляются при изменениях на листе.
Случайное заполнение значений из списка с помощью VBA (Продвинутое и настраиваемое решение)
Если вам нужно автоматизировать массовое случайное присвоение значений, предотвратить дубликаты или требуется больше настроек (например, применение сложной логики во время выбора), подход с использованием VBA (Visual Basic for Applications) является идеальным. VBA позволяет вам генерировать действительно уникальные случайные выборы, применять пользовательскую логику распределения и повторять задачи одной командой — полезно для продвинутых симуляций, автоматизированного случайного распределения или работы с обширными наборами данных.
Это решение подходит для пользователей, знакомых с макросами, или тех, кто хочет автоматизировать свои рабочие процессы в Excel.
1. Откройте редактор VBA, щелкнув Разработчик > Visual Basic (или нажмите Alt + F11), что откроет окно Microsoft Visual Basic for Applications. Затем перейдите к Вставка > Модуль и вставьте код ниже в окно модуля:
Sub RandomFillFromList_NoDuplicates()
Dim srcRange As Range
Dim destRange As Range
Dim srcValues As Variant
Dim destCount As Integer
Dim usedIndexes As Object
Dim i As Integer
Dim randIndex As Integer
On Error Resume Next
Set srcRange = Application.InputBox("Select source list", "KutoolsforExcel", Type:=8)
If srcRange Is Nothing Then Exit Sub
Set destRange = Application.InputBox("Select destination range (number of random values to fill)", "KutoolsforExcel", Type:=8)
If destRange Is Nothing Then Exit Sub
srcValues = Application.Transpose(srcRange.Value)
destCount = destRange.Cells.Count
Set usedIndexes = CreateObject("Scripting.Dictionary")
If UBound(srcValues) < destCount Then
MsgBox "Not enough unique items in the source list to fill destination without duplicates.", vbExclamation, "KutoolsforExcel"
Exit Sub
End If
Randomize
For i = 1 To destCount
Do
randIndex = Int(Rnd() * UBound(srcValues)) + 1
Loop While usedIndexes.Exists(randIndex)
usedIndexes(randIndex) = True
destRange.Cells(i).Value = srcValues(randIndex)
Next
End Sub2. Запустите макрос, нажав 
кнопку на панели инструментов VBA. Макрос запросит вас выбрать (а) исходный список (диапазон значений, из которых нужно выбрать), и (b) диапазон вывода (Для количества извлеченных случайных значений просто выберите равное количество ячеек). Код гарантирует, что в выводе не будет дубликатов значений, если исходный список достаточно большой. Если нет, он покажет предупреждение.
Этот метод VBA предлагает следующие преимущества и рекомендации:
- Преимущества: Обеспечивает случайные, неповторяющиеся выборки; позволяет работать с очень большими списками и пакетами; легко автоматизирует повторяющиеся задачи.
- Недостатки: Требует файлов Excel с поддержкой макросов. Если ваша книга ограничивает макросы, этот подход может не подойти. Ошибки могут возникнуть, если количество пунктов назначения превышает количество элементов источника.
- Напоминания об ошибках: Макрос уведомит вас, если в исходном списке недостаточно уникальных значений для вашего запроса.
- Советы по настройке: Вы можете дополнительно адаптировать код, чтобы разрешить дубликаты, удалив проверку уникальности, или реализовать весовую или фильтрационную логику для более специализированных сценариев.
Случайный выбор и заполнение значений из списка данных с помощью Kutools для Excel (Все версии)
Kutools для Excel предоставляет доступное и интерактивное решение для случайного выбора и заполнения значений из списка. Это идеально подходит для пользователей, которые хотят справиться с рандомизированным распределением без написания формул или кода, или тем, кому нужно быстро обработать выборки массово с минимальным ручным вводом. Kutools также предлагает параметры управления выводом, такие как количество выбираемых значений, и делает это через простой диалоговый интерфейс.
После установки Kutools для Excel действуйте следующим образом, чтобы использовать его встроенную функциональность случайного выбора:
- Выберите диапазон, содержащий значения, которые вы хотите случайным образом выбрать.
- Нажмите Kutools > Диапазон > Сортировать, выбирать или случайно перемешивать. Обратитесь к скриншоту ниже:
- В Сортировать, выбирать или случайно перемешивать диалоговом окне перейдите к вкладке Выбрать и сделайте следующее:
- Укажите количество ячеек для случайного выбора.
- Обязательно выберите опцию Ячейка в Тип выбора.
- Наконец, нажмите кнопку OK .
- Указанное количество случайных ячеек будет выделено или выбрано. Затем вы можете скопировать и вставить эти ячейки в другое место по мере необходимости.
Помимо простоты, метод Kutools также предотвращает ошибки, обычные при ручной рандомизации, и не требует знания формул Excel или настроек макросов. Если вам нужны уникальные значения в вашем выборе, убедитесь, что исходный список больше, чем количество элементов, которые вы планируете выбрать, и проверьте параметры в диалоге для выбора без дубликатов, если они доступны.
🔚Заключение
Случайное заполнение значений из заранее определенного списка в Excel можно эффективно управлять с помощью различных техник, подходящих для разных уровней знаний и сценариев:
- Для всех версий Excel формула ИНДЕКС плюс СЛЧИСЛО является быстрой и надежной для генерации случайных выборок, особенно в списках, где допустимы дубликаты.
- Если у вас есть Excel 365 или 2021, решение RANDARRAY + INDEX обеспечивает более динамичный пакетный выбор, который ускоряет процессы, когда требуется много выходных данных одновременно.
- Для высокой степени настраиваемости — например, обеспечение отсутствия дубликатов, автоматизация больших случайных распределений или обработка сложной логики выбора — метод VBA дает максимальную гибкость, хотя пользователям следует быть знакомыми с выполнением макросов.
- Если вы предпочитаете подход без кода и удобный для пользователя, Kutools для Excel позволяет вам генерировать случайные выборки через графический интерфейс, что делает его подходящим как для новичков, так и для продвинутых пользователей, которым нужны быстрые результаты.
Важно учитывать, нужно ли вам уникальное выбор или вы можете позволить дубликаты, сколько случайных выборов вам нужно, и ваш уровень комфорта с формулами Excel или макросами. Перед тем как делиться или сохранять случайные результаты, используйте функцию вставить-как-значения, чтобы предотвратить случайный пересчет. Для пользователей, заинтересованных в изучении дополнительных решений Excel, посетите наш раздел учебных пособий по Excel для получения дополнительных практических руководств и советов.
Рекомендации по устранению неполадок: Проверьте диапазоны списка на точность, будьте внимательны к пересчету с изменчивыми функциями и убедитесь, что ваши настройки безопасности макросов позволяют выполнение VBA при использовании решений на основе кода. Если возникают ошибки при использовании VBA (например, недостаточный размер исходного списка), следуйте подсказке и пересмотрите свои диапазоны.
Статьи по теме:
Случайный выбор ячеек на основе критериев в Excel
Случайное добавление цвета фона/заливки для ячеек в Excel
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек