Как работают динамические массивы: одна формула вместо протягивания

Раньше одна формула давала одно число, и для столбца из сотни значений её протягивали на каждую строку. Справка Microsoft называет формулы, способные выдавать массивы переменного размера, формулами динамического массива^[1], а вывод в соседние ячейки — переносом.

Работа с ними идёт по знакомому пути: найти диапазон-источник, ввести формулу в одну ячейку, проверить, что результат разлился целиком, исправить ошибку #ПЕРЕНОС!, если ей мешают соседние данные, и сослаться на итог со своими данными через оператор #. В обиходе перенос чаще называют разливом, по-английски — spill.

Ключевые факты: динамические массивы

• Динамический массив — результат формулы, который Excel выводит в соседние ячейки, причём редактировать можно только первую из них^[1].
• ПОСЛЕДОВ подставляет 1 вместо любого пропущенного необязательного аргумента^[2].
• Формулы перенесённого массива не работают внутри таблиц Excel, поэтому их ставят в сетку рядом^[1].
• Справка Microsoft относит УНИК и ПОСЛЕДОВ к Microsoft 365, Excel 2024 и Excel 2021 с версиями для Mac^[2][3].
• Устаревшая формула с Ctrl+Shift+Enter в отличие от новой всегда возвращает результат фиксированного размера^[1].

Что такое динамические массивы и разлив результата

Динамический массив выводит результат одной формулы в соседние ячейки и сам меняет размер, когда меняются данные. Формулу пишут только в верхнюю левую ячейку, а при выделении любой ячейки результата Excel обводит его рамкой^[1].

Формула =B2:B6*C2:C6 в E2 сразу заполнит E2:E6 значениями 1000, 1280, 950, 1230 и 600 — протягивать её не нужно. Запись =СУММ(B2:B6*C2:C6) в одной ячейке вернёт общий итог 5060 без вспомогательного столбца.

Разлитый результат живёт по своим правилам. Редактировать можно только первую ячейку с формулой: остальные видны в строке формул, но недоступны для правки^[1]. После изменения верхней ячейки и нажатия Enter Excel обновляет всю область целиком.

Динамические массивы работают и с обычными операторами, и с функциями, которые принимают диапазоны. Дальше — шесть функций, которые справка Microsoft выделяет специально под разлив^[1].

Функции УНИК, СОРТ и ПОСЛЕДОВ на примерах

Разлив стоит на функциях УНИК, СОРТ, СОРТПО, ФИЛЬТР, ПОСЛЕДОВ и СЛУЧМАССИВ: каждая принимает массив и возвращает массив^[1]. Поэтому их легко вкладывать друг в друга, как СОРТ(УНИК(…)).

УНИК умеет больше, чем убирать дубли. Третий аргумент со значением ИСТИНА возвращает только те значения, которые встречаются ровно один раз^[3]: =УНИК(A2:A6;;ИСТИНА) на таблице менеджеров даст одного Сидорова. Справка Microsoft приводит такой пример для клиентов, которых обслужили только однажды^[3].

ПОСЛЕДОВ заменяет ручную нумерацию. Аргументы по порядку — строки, столбцы, начало и шаг, а для любого пропущенного необязательного аргумента функция подставляет 1^[2]. Пример из справки =ПОСЛЕДОВ(5;1;1001;1000) строит список кодов 1001, 2001, 3001, 4001 и 5001.

В Google Таблицах есть свои FILTER и UNIQUE: справка Google описывает UNIQUE как функцию, которая возвращает уникальные строки диапазона без дубликатов в исходном порядке^[8]. Подробно отбор строк по условиям разобран на странице о функции ФИЛЬТР с условиями И и ИЛИ, а удаление повторов без формул — в разборе удаления дубликатов. Однако одна формула ещё не отчёт: чтобы другие формулы подхватывали результат любой длины, нужна ссылка на весь разлив.

Как сослаться на разлив: оператор # по шагам

Весь разлив возвращает оператор #, поставленный после адреса первой ячейки: ссылка G2# охватывает результат, сколько бы строк в нём ни было. По справке Microsoft при изменении размера массива такая ссылка подстраивается автоматически^[5].

Пример из справки: в A2 стоит =ПОСЛЕДОВ(10), и =СУММ(A2#) равна =СУММ(A2:A11), то есть 55^[5]. На таблице менеджеров тот же приём собирает маленький отчёт без сводной таблицы.

1. Введите в E2 формулу =B2:B6*C2:C6 — суммы разольются в E2:E6.
2. В G2 наберите =УНИК(A2:A6) — список менеджеров займёт G2:G4.
3. В H2 введите =СУММЕСЛИ(A2:A6;G2#;E2#) и нажмите Enter.
4. Проверьте итоги: Иванов — 1950, Петрова — 1880, Сидоров — 1230.
5. Расширьте исходные диапазоны в E2 и G2 до новой строки данных — столбцы G и H вырастут сами, а формулу с решёткой править не придётся.

Такой отчёт из УНИК и СУММЕСЛИ со ссылками на разлив растёт вместе с данными без правки формул. Формула =СЧЁТЗ(G2#) в соседней ячейке вернёт 3 — число менеджеров. Ссылка с решёткой работает только на разлитый результат: на обычный диапазон её не поставить. У оператора есть и граница: со ссылками на закрытые книги он не работает и возвращает #REF!^[5].

Соберите такой отчёт в тренажёре: урок подменяет данные и проверяет, что формулы со ссылкой # подхватили новый размер.

Именно поэтому старые книги, где формулы массива вводили иначе, требуют отдельного разбора при переходе на новые версии.

Старые и новые формулы массива: Ctrl+Shift+Enter и знак @

Динамические массивы отличаются от устаревших формул массива размером результата и способом ввода. Справка Microsoft называет старые формулы формулами CSE: их подтверждали сочетанием Ctrl+Shift+Enter, заранее выделив выходной диапазон^[7].

Динамический массив, напротив, возвращает результат того размера, какой нужен данным, и подстраивает его при пересчёте^[1]. Устаревшие формулы по-прежнему работают ради обратной совместимости, но всегда возвращают результат фиксированного размера^[1]. Чтобы перевести такую формулу на новый механизм, справка советует скопировать её из первой ячейки, удалить старый массив и ввести формулу заново в верхнюю левую ячейку^[1].

Знак @ в формулах из старых книг — оператор неявного пересечения. Он сводит диапазон к одному значению из той же строки или столбца, а массив — к левому верхнему элементу^[4]. Excel добавляет @ к функциям, которые возвращают диапазоны, например ИНДЕКС и СМЕЩ, если формула создана в старой версии; результат вычислений при этом не меняется^[4].

Если удалить @ и формула вернёт диапазон, результат начнёт выводиться в соседние ячейки^[4]. Остаётся понять, что мешает разливу и в каких задачах динамические массивы лучше не применять.

Ошибка #ПЕРЕНОС! и когда динамические массивы не подойдут

Разлив упирается в ошибку #ПЕРЕНОС!, когда формула не может вывести посчитанный массив: формулы с переносом не подойдут там, где выходной диапазон занят или запрещён. Таблица собирает причины из справки Microsoft^[6].

Пример выхода за границу листа приводит сама справка: =ВПР(A:A;A:C;2;ЛОЖЬ) пытается вернуть 1 048 576 результатов и упирается в край сетки^[6]. Исправить формулу можно ссылкой на одну строку или оператором @: =ВПР(@A:A;A:C;2;ЛОЖЬ).

• Если формула должна стоять внутри умной таблицы, вывод не сработает: формулы с переносом в таблицах Excel не работают^[1].
• Если книгу откроют в Excel без динамических массивов, смешанные формулы отобразятся с _xlfn.SINGLE() и не будут поддерживаться^[4].
• При связи с другой книгой результат обновится, только если открыты обе книги, иначе формулы вернут #REF!^[1]
• Когда размер результата меняется между пересчётами, как у =ПОСЛЕДОВ(СЛУЧМЕЖДУ(1;1000)), Excel покажет #ПЕРЕНОС! из-за неопределённого размера^[6].

Функции УНИК и ПОСЛЕДОВ справка Microsoft указывает для Microsoft 365, Excel 2024 и Excel 2021, включая версии для Mac, iPad, iPhone и Android^[2][3]. Как работают умные таблицы и почему перенос в них не помещается, объяснено на странице об умной таблице и структурированных ссылках. Другие функции и приёмы собраны в обзоре формул Excel для массивов и отчётов.