RANDARRAY в Excel: генерация и перемешивание

3D-иконка Microsoft Excel на зелёном фоне

Краткое введение

RANDARRAY — это функция динамического массива в современных версиях Excel (Microsoft 365 и Excel 2021+). Она возвращает массив случайных чисел заданного размера. «Динамический массив» означает, что результат автоматически «выливается» (spill) в соседние ячейки, занимая столько строк и столбцов, сколько нужно.

Ключевые сценарии использования:

Перемешать список участников для групп или розыгрыша.
Создать рандомизированные тестовые данные для таблиц и визуализаций.
Быстро получать случайные целые числа в диапазоне.

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

Синтаксис функции

=RANDARRAY([rows],[columns],[min],[max],[integer])

Где:

rows — необязательный, число строк в возвращаемом массиве (по умолчанию 1).
columns — необязательный, число столбцов (по умолчанию 1).
min — необязательный, минимальное значение (по умолчанию 0).
max — необязательный, максимальное значение (по умолчанию 1).
integer — необязательный логический аргумент: TRUE возвращает целые числа, FALSE — десятичные (по умолчанию FALSE). Если вы хотите целые числа, укажите этот аргумент.

Если указать только один аргумент, функция всё равно вернёт результат — параметры взаимозаменяемы по смыслу размеров массива или границ диапазона.

Быстрые примеры

1. Сгенерировать 5 строк и 4 столбца случайных чисел

Выберите ячейку B3.
Введите формулу:

=RANDARRAY(5,4)

Нажмите Enter — Excel заполнит блок 5×4 десятичными числами от 0 до 1.

Таблица с результатом RANDARRAY 5 на 4

2. Ограничить диапазон значений от 20 до 100

=RANDARRAY(5,4,20,100)

Эта формула вернёт десятичные числа в интервале [20,100] в массиве 5×4.

Таблица с RANDARRAY в диапазоне 20–100

3. Вернуть только целые числа

=RANDARRAY(5,4,20,100,TRUE)

Результат — целые числа от 20 до 100. Если заменить TRUE на FALSE — снова получим десятичные значения.

Таблица с целыми числами от RANDARRAY

Как случайно отсортировать список имён с помощью RANDARRAY и SORTBY

RANDARRAY генерирует случайные числа, но сортировка по ним делает сам шаг перемешивания. Для этого используется функция SORTBY.

Синтаксис SORTBY:

=SORTBY(array, by_array, [sort_order], ...)

array — массив или диапазон, который нужно отсортировать.
by_array — массив тех же размеров, по которому выполняется сортировка.

Пример. Есть список имён в C3:C12 (10 имён). Чтобы получить случайную перестановку, введите в D5:

=SORTBY(C3:C12, RANDARRAY(10))

SORTBY отсортирует список по значениям RANDARRAY, тем самым вернув случайный порядок.

Список имён и пример SORTBY с RANDARRAY

Практические приёмы и советы

Зафиксировать результат: выделите получившийся диапазон, Ctrl+C → Правый клик → Вставить как значения.
Ограничение повторных вычислений: используйте Опции → Формулы → Отключить автоматический пересчёт, если нужно избежать частых изменений. Но будьте осторожны — это влияет на всю книгу.
Производительность: большое количество RANDARRAY может замедлить книгу, так как функция пересчитывается часто. Для больших массивов генерируйте числа один раз и сохраняйте как значения.

Важно: RANDARRAY доступна не во всех версиях Excel. Если у вас Excel 2016/2019 без современных динамических массивов, используйте альтернативы ниже.

Альтернативные способы и когда использовать их

RANDBETWEEN — возвращает случайное целое между двумя числами. Полезно, если вам нужны только целые и нет поддержки RANDARRAY.

=RANDBETWEEN(20,100)

RAND + SORT — если нужно перемешать список, создайте столбец с =RAND(), затем сортируйте по этому столбцу.
INDEX + RANDBETWEEN + UNIQUE — при выборке уникальных случайных элементов из списка используйте формулы с INDEX и вспомогательной логикой.

Когда RANDARRAY не подходит:

Устаревшие версии Excel без динамических массивов.
Нужна детерминированная воспроизводимая случайность (требуется фиксированное зерно) — Excel не предоставляет простого способа задать seed.
Требуется высокая производительность при миллионах значений — лучше генерировать данные вне Excel.

Ментальные модели и эвристики

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

Шаблон playbook: быстрое перемешивание списка (SOP)

Убедитесь, что у вас поддержка RANDARRAY.
Выберите вспомогательную ячейку для формулы, например D5.
Введите формулу:

=SORTBY(C3:C12, RANDARRAY(ROWS(C3:C12)))

Нажмите Enter. Проверьте результат.
Если нужно зафиксировать порядок, выделите результат и вставьте как значения.

Критерии приёмки

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

Чек-лист по ролям

Для преподавателя:
- Убедиться, что все студенты получили уникальные группы.
- Сохранить список групп как значения.
Для HR или менеджера проектов:
- Перемешать список кандидатов для случайного распределения этапов интервью.
- Проверить, что диапазон охватывает всех участников.
Для аналитика данных:
- Минимизировать количество volatile-функций (RAND, RANDBETWEEN, RANDARRAY).
- Сохранять тестовые данные в отдельной вкладке как зафиксированные значения.

Примеры расширенного использования

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

=TAKE(SORTBY(C3:C12,RANDARRAY(ROWS(C3:C12))), 5)

Эта формула сначала перемешивает весь список, затем функция TAKE (если доступна) берёт первые 5.

Перемешать таблицу с несколькими столбцами (сохраняется связь между столбцами):

= SORTBY(Table1, RANDARRAY(ROWS(Table1)))

Создать массив случайных булевых значений (пример для A/B тестов):

= RANDARRAY(100,1,0,1,TRUE)

Это создаст 100 случайных 0/1, которые можно интерпретировать как группы A/B.

Когда функция даёт неожиданные результаты и как это исправить

Проблема: числа меняются при каждом изменении в книге. Решение: вставьте как значения или переключите расчёт на ручной.
Проблема: нехватка памяти/медленная работа при больших массивах. Решение: генерируйте меньшие пакеты, сохраняйте как значения, избегайте вложенных volatile-функций.
Проблема: нет поддержки RANDARRAY. Решение: используйте RAND()+SORT или скрипты Power Query / VBA.

Пример на VBA для старых версий Excel

Если вы используете Excel без RANDARRAY, можно написать короткий макрос, который перемешивает диапазон:

Sub ShuffleRange(rng As Range)
  Dim i As Long, j As Long
  Dim tmp As Variant
  Dim arr As Variant
  arr = rng.Value
  Randomize
  For i = UBound(arr, 1) To 2 Step -1
    j = Int(Rnd() * i) + 1
    tmp = arr(i, 1)
    arr(i, 1) = arr(j, 1)
    arr(j, 1) = tmp
  Next i
  rng.Value = arr
End Sub

Этот код применяет алгоритм Фишера — Йетса для перемешивания одномерного диапазона.

Дерево решений: какую стратегию выбрать?

flowchart TD
  A[Нужна случайность в Excel?] --> B{Поддерживается RANDARRAY?}
  B -- Да --> C{Нужны целые числа?}
  B -- Нет --> D[Использовать RAND+SORT или VBA/Power Query]
  C -- Да --> E[Использовать RANDARRAY'...,TRUE']
  C -- Нет --> F[Использовать RANDARRAY без TRUE]
  E --> G[Зафиксировать результат как значения]
  F --> G
  D --> G

Совместимость и миграция

Поддерживается в Microsoft 365, Excel 2021 и новее.
В Excel 2019/2016 используйте RANDBETWEEN, RAND+SORT или Power Query.
При миграции файлов с RANDARRAY на старые версии — замените формулы генерации на статичные значения или макросы.

Безопасность и конфиденциальность

RANDARRAY не отправляет данные вне книги и не использует внешние сервисы. Однако будьте осторожны при публикации результатов, если они содержат персональные данные.

Факты для быстрой справки

По умолчанию RANDARRAY генерирует десятичные между 0 и 1.
Указав последний аргумент TRUE, получите целые значения.
Функция динамическая — результат автоматически занимает нужный диапазон.

Частые ошибки и решения

Ошибка #SPILL! — означает, что соседние ячейки не пусты. Очистите их или поместите формулу в другое место.
Неправильные границы min/max — убедитесь, что min <= max.
Слишком большой массив — Excel может исчерпать доступную память; уменьшите размер.

Короткое резюме

RANDARRAY — мощный инструмент для генерации случайных массивов в современных версиях Excel. Он удобен для перемешивания списков, создания тестовых данных и интеграции в более сложные формулы. Если ваша версия Excel не поддерживает динамические массивы, применяйте альтернативы: RAND + SORT, RANDBETWEEN или макросы.

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

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