Массивы в Excel: VSTACK, HSTACK, TOROW и другие

Quick Links

• Combine Arrays

• Reshape Arrays

• Resize Arrays

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

Эти 11 функций стали доступны в Excel с августа 2022 года. Они постепенно распространяются среди пользователей, начиная с Office Insiders.

Объединение массивов

Объединять данные на листе проще с функциями VSTACK и HSTACK. VSTACK складывает массивы вертикально, HSTACK — горизонтально.

Синтаксис одинаков для обеих:

VSTACK(array1, array2,...)

HSTACK(array1, array2,...)

Должен быть минимум один массив, остальные необязательны.

Чтобы объединить массивы в диапазонах B2:F3 и H2:L3 вертикально, используйте формулу:

=VSTACK(B2:F3,H2:L3)

Чтобы объединить их горизонтально, примените:

=HSTACK(B2:F3,H2:L3)

Советы по использованию

• Порядок аргументов определяет порядок блоков в итоговом массиве.
• Если размеры блоков различаются, Excel заполнит отсутствующие ячейки ошибкой или пустыми значениями в зависимости от операции.
• Для вставки заголовков используйте HSTACK или VSTACK с одиночными строками/столбцами.

Когда это не подходит

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

Альтернатива

• Ранее часто использовали Power Query или формулы INDEX/SEQUENCE/INDIRECT для похожих задач. Power Query остаётся подходящим выбором для сложной предобработки и автоматизации.

Изменение формы массива

Если нужно не объединять, а изменить форму массива, доступны четыре функции: TOROW, TOCOL, WRAPROWS и WRAPCOLS.

Related: 12 Basic Excel Functions Everybody Should Know

Преобразование массива в строку или столбец

TOROW и TOCOL преобразуют двухмерный массив в одну строку или один столбец. Синтаксис:

TOROW(array, ignore, by_column)

TOCOL(array, ignore, by_column)

Коротко про аргументы

• ignore — как обрабатывать значения: 0 по умолчанию (ничего не игнорировать), 1 игнорировать пустые, 2 игнорировать ошибки, 3 игнорировать и пустые, и ошибки.
• by_column — если TRUE, сканирование идёт по столбцам; по умолчанию FALSE (по строкам). Это влияет на порядок элементов.

Примеры

=TOROW(B2:F3)

=TOCOL(B2:F3)

Советы

• Используйте ignore=1, если в исходном диапазоне много пустых ячеек и вы хотите их исключить.
• Выберите by_column, чтобы сохранить порядок, удобный при работе с категориями по столбцам.

Преобразование строки или столбца в массив

WRAPROWS и WRAPCOLS превращают одномерный диапазон в двумерный массив, разбивая данные на группы.

Синтаксис:

WRAPROWS(reference, wrap_count, pad)

WRAPCOLS(reference, wrap_count, pad)

Коротко про аргументы

• wrap_count — число элементов в каждой строке/столбце.
• pad — значение для заполнения пустых ячеек, если длина не кратна wrap_count.

Примеры

=WRAPROWS(B2:K2,3,"empty")

=WRAPCOLS(B2:K2,3,"empty")

Советы

• pad можно оставить пустым (“”), если хотите просто пустые ячейки.
• WRAPROWS подходит для создания таблиц из длинных списков, WRAPCOLS — для трансформирования в столбцы.

Когда это не работает

• Если reference содержит формулы с относительными ссылками, при реорганизации порядка элементов могут появиться логические ошибки.

Изменение размера массива

Если нужно добавить или убрать строки и столбцы, есть пять функций: TAKE, DROP, CHOOSEROWS, CHOOSECOLS и EXPAND.

Related: 13 Essential Excel Functions for Data Entry

Взять или отбросить строки и столбцы

TAKE сохраняет указанное число строк или столбцов. DROP удаляет их. Положительные числа берут/удаляют от начала массива, отрицательные — от конца.

Синтаксис:

TAKE(array, rows, columns)

DROP(array, rows, columns)

Примеры

=TAKE(B2:F5,2)

=TAKE(B2:F5,,2)

=DROP(B2:F5,2)

=DROP(B2:F5,,2)

Советы

• Параметры rows и columns необязательны, но должен присутствовать хотя бы один.
• Проверьте знак числа, чтобы брать/удалять с нужного края.

Выбрать конкретные строки или столбцы

CHOOSEROWS и CHOOSECOLS позволяют явно указать номера строк или столбцов внутри массива.

Синтаксис:

CHOOSEROWS(array, row_num1, row_num2,...)

CHOOSECOLS(array, column_num1, column_num2,...)

Примеры

=CHOOSEROWS(B2:F5,2,4)

=CHOOSECOLS(B2:F5,3,5)

Важно

Используйте номера относительно массива, а не листа. То есть первая строка массива — 1, даже если фактически это строка 2 на листе.

Развернуть массив до конкретных размеров

EXPAND позволяет задать нужное число строк и столбцов, добавив заполнение при необходимости.

Синтаксис:

EXPAND(array, rows, columns, pad)

Если rows или columns пропущены, Excel не расширит в соответствующем направлении. pad указывает значение для пустых ячеек.

Примеры

=EXPAND(B2:F5,10,10)

=EXPAND(B2:F5,10,10,"empty")

Советы

• pad полезен, чтобы избежать ошибок #REF или отображения формул в пустых областях.
• EXPAND удобен для подготовки диапазонов под условное форматирование или таблицы с фиксированным размером.

Частые ошибки и как их исправлять

• Ошибка из-за отсутствия функции в вашей версии Excel. Проверьте версию и статус обновлений. Если функции недоступны — используйте Power Query, классические формулы INDEX/SEQUENCE или макросы.
• Неправильный порядок элементов при использовании TOROW/TOCOL. Проверьте аргумент by_column.
• Ошибки при объединении массивов разного размера. Перед объединением нормализуйте размеры или добавьте pad/заполнители.
• Ошибки из-за относительных ссылок в исходных формулах. Конвертируйте в значения перед реструктуризацией, если требуется сохранить поведение.

Важно: перед массовыми преобразованиями создайте резервную копию листа.

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

Аналитик данных

• Проверить версию Excel и наличие функций.
• Оценить размер и форму исходных массивов.
• Выбрать VSTACK/HSTACK или WRAP*/TAKE/CHOOSEROWS в зависимости от задачи.
• Запустить формулы на тестовом отрезке данных.
• Проверить итог на пустые/ошибочные значения.

Бизнес-пользователь

• Подготовить список полей и заголовков.
• Использовать HSTACK для добавления столбцов с метаданными.
• Использовать EXPAND для форматирования отчёта.

Разработчик отчётов

• Автоматизировать процессы через Power Query, если требуется регулярная агрегация.
• Включать проверки целостности данных после применения функций.

Методология быстрого выбора функции

1. Нужна ли вам одна строка/столбец как результат? Да → TOROW/TOCOL. Нет → шаг 2.
2. Нужно объединить блоки? Да → VSTACK/HSTACK.
3. Нужна конкретная выборка строк/столбцов? Да → CHOOSEROWS/CHOOSECOLS.
4. Нужно удалить/сохранить с начала или конца? TAKE/DROP.
5. Нужно ровно n×m размер для форматирования? EXPAND.

Эта последовательность помогает быстро подобрать функцию для 80–90% задач с массивами.

Примеры сценариев и тесты приёмки

Сценарий: Объединить две таблицы продаж по вертикали и удалить пустые строки.

Шаги теста

• Применить =VSTACK(Table1,Table2)
• Применить =TOCOL(полученный_массив,1) для удаления пустых и приведения в колонку, если нужно.
• Проверка: итоговая таблица должна содержать сумму строк Table1+Table2 без пустых строк.

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

• Результат корректно объединяет все строки.
• Отсутствуют пустые строки, если это требовалось.
• Формулы не возвращают ошибок #REF или #VALUE.

Примеры сниппетов и шпаргалка

Шпаргалка

• VSTACK — вертикальный стек
• HSTACK — горизонтальный стек
• TOROW — массив в строку
• TOCOL — массив в столбец
• WRAPROWS — одномерный → строки
• WRAPCOLS — одномерный → столбцы
• TAKE — взять n строк/столбцов
• DROP — отбросить n
• CHOOSEROWS — выбрать строки по номерам
• CHOOSECOLS — выбрать столбцы по номерам
• EXPAND — расширить до размера

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

=VSTACK(TOROW(B2:F3,1),TOROW(H2:L3,1))

Этот приём сначала превращает блоки в строки, удаляет пустые, затем объединяет их.

Совместимость и заметки по локализации

• Функции начали появляться в Excel с августа 2022 и доставляются по обновлениям Microsoft 365. Если у вас локальная стабильная версия Office 2019/2016, функции могут быть недоступны.
• Имена функций международны в русской версии Excel остаются английскими (VSTACK и т.д.) или могут быть локализованы в будущем. Всегда проверяйте подсказку Excel при вводе функции.

Риски и рекомендации

• Риск: несогласованность размеров массивов при объединении. Митигция: нормализуйте размеры или используйте pad/заполнители.
• Риск: потеря относительных ссылок. Митигция: преобразуйте формулы в значения при необходимости.
• Риск: отсутствие поддержки в старых версиях Excel. Митигция: предусмотреть альтернативы через Power Query или макросы.

Краткая сводка

Эти 11 функций значительно упрощают работу с массивами: от простой конкатенации до точного выбора строк и расширения диапазонов. Попробуйте их на тестовом наборе данных и используйте чек-листы перед применением в рабочих отчётах.

Важно: всегда делайте резервную копию листа перед массовыми преобразованиями.

Summary

• Новые функции дают гибкие способы объединять и преобразовывать массивы.
• Перед применением убедитесь в совместимости версии Excel.
• Используйте pad и ignore, чтобы управлять пустыми или ошибочными значениями.

Related: How to Fix Common Formula Errors in Microsoft Excel