Склейка диапазонов новыми функциями VSTACK и HSTACK

8464 03.09.2022 Скачать пример


Обычно для сборки данных из нескольких диапазонов в один используют либо , либо макросы или запросы Power Query. В 2022 году к этому списку добавился ещё один весьма изящный способ - новые функции VSTACK и HSTACK для вертикальной и горизонтальной склейки (stack = стопка, пачка, штабель). В русской версии эти функции получили немного странные названия ВСТОЛБИК и ГСТОЛБИК соответственно.

Впервые эти функции были анонсированы в марте 2022 года, а затем после тестирования среди добровольцев-инсайдеров, начали постепенно разливаться с обновлениями всем пользователям Office 365 (он же Microsoft 365) по всему миру. Во всех остальных версиях Excel (не 365) эти функции пока недоступны, к сожалению.

Синтаксис обеих функций прост:

=ВСТОЛБИК(массив1; массив2; массив3...)

=ГСТОЛБИК(массив1; массив2; массив3...)

Т.е. в аргументах здесь просто перечисляются диапазоны (максимум аж 254 штуки!), которые мы хотим подклеить сверху-вниз друг под друга (VSTACK) или слева-направо друг рядом с другом (HSTACK).

Склейка диапазонов функцией ВСТОЛБИК VSTACK

Обе функции замечательно работают с любыми типами ссылок и понимают:

  • прямые ссылки стандартного вида (например, A1:D5)
  • прямые ссылки на диапазоны с других листов или из других книг (они потом, кстати, отлично работают, даже если эти внешние книги закрыты)
  • ссылки на "умные" динамические таблицы (например Таблица1) или их фрагменты (например, Таблица1[#Заголовки])
  • именованные диапазоны, созданные, например, через Формулы - Диспетчер имён (Formulas - Name Manager)
  • динамические именованные диапазоны, созданные через Диспетчер имён с помощью функций ИНДЕКС (INDEX) или СМЕЩ (OFFSET).

Если собираемые функций ВСТОЛБИК диапазоны будут с разным количеством столбцов, то на выходе мы получим диапазон-результат с максимальным по ширине диапазоном, а "лишние" пустые ячейки из более узких исходных диапазонов будут содержать ошибку #Н/Д. 

Сборка таблиц разного размера

Само собой, её можно перехватить стандартным образом - функцией ЕСЛИОШИБКА (IFERROR) - и заменить, например, на 0 или пустую текстовую строку.

Также обе эти функции поддерживают трёхмерные ссылки на группу листов, что позволяет с лёгкостью собирать данные оптом - сразу с большого количества листов не перечисляя их в формуле поименно. Для этого:

  1. Добавляем в книгу два пустых вспомогательных листа (можно назвать их, например, Старт и Финиш) и помещаем все листы с данными между ними.
  2. Вводим нашу функцию ВСТОЛБИК (VSTACK) и задаём трёхмерный диапазон для сборки с запасом, чтобы он был по высоте больше, чем таблица с любого из листов.
  3. От попавших в сборку лишних строк (т.к. выделяли с запасом) можно впоследствии легко избавиться с помощью функции ФИЛЬТР (FILTER):

Трехмерные ссылки на группу листов

Красота!

Функция ГСТОЛБИК (HSTACK) работает совершенно аналогично.

Ссылки по теме


Ованес
27.09.2022 10:21:57
конструкция типа:
=LET(а;ВСТОЛБИК(Таблица1;Таблица2;...);
б;ВСТОЛБИК(Таблица1[Дата поставки];Таблица2[Дата поставки];...);
СОРТПО(а;б;1))
работает прекрасно
СПАСИБО!
Ссылка
Ованес
13.10.2022 11:19:03
Теперь можно в лямбду заворачивать несколько массивов как один!
=BYROW(ГСТОЛБИК(AZ1729#;BE1729#);LAMBDA(а;ОБЪЕДИНИТЬ(", ";ИСТИНА;а)))
Ссылка
Наверх