Что означает разлив формулы

Большинство формул, которые Вы писали раньше, возвращают одно значение в одну ячейку. Динамические массивы меняют это. Одна формула может возвращать целый список или таблицу результатов, а электронная таблица автоматически помещает каждый результат в отдельную ячейку.

Такое автоматическое размещение называется разливом. Вы вводите формулу один раз, нажимаете Enter, и результаты распространяются по соседним ячейкам, словно вода, заполняющая поддон.

Предположим, в диапазоне A1:A5 находятся числа от 1 до 5. Введите в ячейку C1 формулу =A1:A5*10 и нажмите Enter.

Вместо одного ответа Вы получите пять: 10, 20, 30, 40, 50 — в ячейках C1:C5 сверху вниз. Вы ввели формулу только в C1, но результат автоматически разлился в ячейки ниже.

Блок ячеек, который заполняет формула, называется её диапазоном разлива. Только верхняя левая ячейка, называемая опорной, фактически содержит формулу. Остальные ячейки заполняются автоматически и при выборе опорной ячейки обозначаются бледно-синей рамкой.

Нельзя напрямую редактировать ячейки разлива. При попытке это сделать возникнет ошибка. Чтобы изменить результат, отредактируйте формулу в опорной ячейке.

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

Если формула в C1 разливается до C5, запись C1# означает весь диапазон разлива. Используйте её в других формулах, например =SUM(C1#), и итог всегда будет включать все разлившиеся значения, даже если позже диапазон увеличится.

Главное преимущество разлива в том, что он динамический. Если добавить новое число в исходный диапазон, диапазон разлива автоматически увеличится. Если удалить значение, разлив уменьшится.

Вам больше не придётся снова копировать формулу вниз. Одна формула в опорной ячейке сама изменяет размер результата в соответствии с прочитанными данными.

Многие современные функции возвращают массивы и поэтому разливаются:

• FILTER возвращает подходящие строки.
• UNIQUE возвращает список уникальных значений.
• SORT и SORTBY изменяют порядок элементов диапазона.
• SEQUENCE создаёт список чисел.

В этом курсе Вы познакомитесь с FILTER и UNIQUE. Каждая из них выводит множество результатов из одной формулы.

Представьте, что в столбце A перечислены регионы: East, West, East, North, West. Чтобы получить аккуратный список уникальных регионов, введите одну формулу:

=UNIQUE(A2:A6)

Она разольётся в три результата: East, West, North. Добавьте ещё один регион в исходный диапазон — и список разлива увеличится сам. Одна формула — список, который всегда актуален.

Разлив не ограничивается распространением вниз. Формула может разливаться по столбцам или даже заполнять двумерную таблицу.

Например, =SEQUENCE(3,4) разливается в блок высотой 3 строки и шириной 4 столбца, заполняя 12 ячеек числами от 1 до 12. Направление и форма полностью зависят от массива, который создаёт формула.

Динамические массивы и разлив работают в Excel for Microsoft 365 и Excel 2021 and later. Google Sheets также уже много лет поддерживает разлив массивов.

Более старые версии, например Excel 2016 и 2019, не поддерживают разлив. В них формулы массивов нужно было подтверждать с помощью Ctrl+Shift+Enter, и они не могли автоматически увеличиваться. Если формулы не разливаются, сначала проверьте версию программы.

До появления динамических массивов Вы вводили =B2*C2 в одну ячейку, а затем перетаскивали формулу вниз на сотни строк. С разливом достаточно ввести её один раз.

Формула =B2:B100*C2:C100 разливается, возвращая по одному результату для каждой строки всего диапазона. Сопровождение становится простым: нужно читать и исправлять одну формулу, а не сотни копий, которые могут начать отличаться.

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

Ваша задача — убедиться, что для размещения этой формы достаточно свободного места. В следующих уроках Вы узнаете, как создавать полезные формы с помощью FILTER и UNIQUE и что делать, если место назначения занято.

Проверьте, насколько хорошо Вы поняли принцип разлива.

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

• Только опорная ячейка содержит формулу.
• Для ссылки на весь разлив используйте оператор #, например C1#.
• Разлив автоматически увеличивается и уменьшается вместе с исходными данными.
• Такие функции, как FILTER, UNIQUE, SORT и SEQUENCE, поддерживают разлив.

Далее Вы воспользуетесь FILTER, чтобы возвращать только нужные строки.