Все формулы РФ

Excel и Google Workspace / Базовые формулы Excel

HSTACK в Excel: объединить массивы по столбцам

HSTACK ставит массивы рядом и возвращает общий разлившийся диапазон. Высота равна максимуму высот аргументов, а недостающие строки получают #N/A.

Опубликовано: Обновлено:

ExcelОфисные расчетыСтраницы с задачами и решениямиЕсть историческая справка

Формула

$$\texttt{=HSTACK(A2:A4,C2:D3)}$$
Схема Поля добавляются по номеру строки
Поля добавляются по номеру строкиПоля добавляются по номеру строкиКодСтатусСумма501Оплачен1200502Ожидает800503#N/A#N/AHSTACK не сопоставляет коды автоматически

Отсутствующие поля третьей строки остаются ошибками #N/A.

Обозначения

$A2:A4$
первый столбец из трёх записей, не применяется
$C2:D3$
два дополнительных поля для первых двух записей, не применяется

Условия применения

Microsoft указывает поддержку в Microsoft 365 и Excel 2024 для Windows и Mac. Примеры написаны английскими именами с запятыми. Место для разлива должно быть свободно. Перед соединением проверяют соответствие строк: первые строки всех аргументов будут поставлены рядом независимо от их ключей.

Ограничения

Списки разной высоты дополняются #N/A снизу, а не автоматически выравниваются по значениям. Функция не сортирует, не ищет совпадающие коды и не удаляет строки. Если источники описывают разные объекты в одной позиции, получится формально корректный, но содержательно неверный отчёт.

Подробное объяснение

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

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

Если один блок короче, нижние отсутствующие позиции получают #N/A. Это позволяет отличить недостающую строку от существующей записи. Простая замена ошибки нулём может превратить неизвестную сумму в реальный нулевой показатель, поэтому выбор заполнения должен соответствовать смыслу отчёта.

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

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

Как пользоваться формулой

  1. Сверьте порядок записей во всех массивах; при разных ключах сначала выполните отдельное сопоставление строк.
  2. Сложите ширины аргументов и возьмите максимальную высоту, чтобы определить размер будущего отчёта.
  3. Разместите HSTACK в свободной области, перечисляя блоки в том порядке, в каком нужны столбцы результата.
  4. Проверьте последнюю строку каждого блока и не превращайте отсутствующие значения #N/A в нули без основания.

Историческая справка

HSTACK была представлена Microsoft вместе с другими функциями преобразования массивов в 2022 году. Её роль отличается от вертикального объединения списков: она собирает поля одного представления рядом, сохраняя позиционный характер таблицы. Эта операция стала естественной частью работы с динамическими массивами, где промежуточный результат одной формулы можно передать другой без ручного копирования. В современной справке отдельно указаны суммарная ширина, максимальная высота и заполнение недостающих строк. Исторически это развитие языка формул Excel, а не новый алгоритм объединения баз данных. Поэтому ожидание автоматического соединения по ключу ошибочно: продуктовая функция сознательно выполняет более простую операцию над формой массивов.

Историческая линия формулы

HSTACK относится к средствам Microsoft Excel для горизонтального соединения массивов. Её история связана с развитием динамических формул; она не является реализацией произвольного соединения таблиц по ключам и не имеет отдельного общепринятого математического автора.

Пример

В A2:A4 находятся коды 501,502,503. В C2:D3 записаны две строки полей: Оплачен — 1200 и Ожидает — 800, уже относящиеся к первым двум кодам. Формула =HSTACK(A2:A4,C2:D3) создаёт строки [501,Оплачен,1200], [502,Ожидает,800], [503,#N/A,#N/A]. Высота max(3,2)=3, ширина 1+2=3. Проверяем, что сумма 800 осталась рядом с кодом 502. Для кода 503 функция не знает ни статуса, ни суммы, поэтому не должна выдавать ноль. Если правый блок был отсортирован иначе, HSTACK этого не заметила бы: соответствие кодов надо доказать до соединения.

Частая ошибка

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

Практика

Задачи с решением

Два одинаковых столбца по высоте

Условие. Слева [1;2], справа [10;20]. Найдите HSTACK.

Решение. Первые строки ставятся рядом, затем вторые: [1,10] и [2,20]. Высота 2, ширина 2.

Ответ. Массив [1,10;2,20]

Короткий правый блок

Условие. Слева [1;2;3], справа [9]. Сколько #N/A появится?

Решение. Правый блок имеет одну строку, а итог три. В правом столбце строк 2 и 3 отсутствуют позиции.

Ответ. Два #N/A

Дополнительные источники

  • https://support.microsoft.com/en-us/excel/functions/hstack-function
  • https://techcommunity.microsoft.com/blog/excelblog/announcing-new-text-and-array-functions/3186066

Связанные формулы

Excel и Google Workspace

TOCOL в Excel: диапазон в один столбец

$\texttt{=TOCOL(A1:B2)}$

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