Если вы часто работаете с формулами в Excel, скорее всего, уже сталкивались с ситуациями, где приходится дублировать одни и те же вычисления. Особенно, если нужно оценить сразу несколько условий или получить несколько значений из одного диапазона. В таких случаях на помощь приходят постоянные массивы — простой, но мощный способ передать Excel сразу несколько фиксированных значений в одной формуле. И хотя на первый взгляд они могут показаться пугающими из-за необычного синтаксиса, в действительности это удобный инструмент, который экономит время и делает таблицы компактнее.
Разберёмся, как устроены массивы, чем отличаются горизонтальные и вертикальные константы, как они работают в новых и старых версиях Excel, и как их можно сочетать с функциями SUM, COUNTIF, LARGE, XLOOKUP и даже задавать имена, чтобы использовать в формулах повторно.
Что такое постоянный массив и как он выглядит
Постоянный массив в Excel — это зафиксированный (жёстко заданный) набор значений, который прописывается прямо внутри формулы и заключается в фигурные скобки { }. Такой массив может быть одномерным (по строке или по столбцу) или двумерным (как таблица), и используется, чтобы избежать повторного ввода тех же значений в разных частях формулы.
Например, формула:
={10,20,30,40}
при вводе в ячейку A1 создаёт горизонтальный динамический массив, где значения 10, 20, 30 и 40 автоматически «проливаются» (spill) в соседние ячейки: A1:D1.
Если вы хотите создать вертикальный массив, используйте точку с запятой в качестве разделителя:
={10;20;30;40}
В этом случае данные распределятся по строкам: с A1 по A4.
Можно также комбинировать запятые и точки с запятой, чтобы задать двумерный массив:
={10,20;30,40}
Это приведёт к размещению чисел по сетке 2×2: A1:B2.
Что значит «динамический» массив?
Хотя сами значения массива статичны, при использовании в современных версиях Excel они ведут себя как динамические — результат «растекается» на соседние ячейки автоматически, без ручного растягивания формулы. Это работает в Excel 365, Excel 2021 и Excel для веб. Формула при этом прописана только в верхней левой ячейке, остальные просто отображают значения результата.
В более старых версиях Excel, которые не поддерживают динамические массивы, необходимо вручную выделять диапазон, куда нужно вставить результат, затем ввести формулу и подтвердить её через Ctrl+Shift+Enter — так называемая формула массива CSE . А теперь перейдем к практике: как использовать массивы в формулах.
COUNTIF + массив: одно условие — одна формула
Допустим, у вас есть таблица с задачами и их статусами, и вы хотите узнать, сколько из них имеют статус Complete, Pending или Pitched. Обычно это делается через три отдельных вызова COUNTIF:
=COUNTIF(T_TaskLog[Status], "Complete") + COUNTIF(T_TaskLog[Status], "Pending") + COUNTIF(T_TaskLog[Status], "Pitched")
Но это громоздко. Вместо этого можно передать все условия как массив:
=COUNTIF(T_TaskLog[Status], {"Complete","Pending","Pitched"})
Excel в этом случае вернёт три значения (по одному на каждое условие), и «прольёт» их по ячейкам. Чтобы получить итоговое количество, достаточно обернуть формулу в SUM:
=SUM(COUNTIF(T_TaskLog[Status], {"Complete","Pending","Pitched"}))
LARGE + массив: топ-N значений из диапазона
Предположим, у вас есть таблица T_Profits с колонкой прибыли, и вы хотите получить три наибольших значения. Без массивов вы бы писали:
=LARGE(T_Profits[Profit],1) =LARGE(T_Profits[Profit],2) =LARGE(T_Profits[Profit],3)
А затем, например, использовали XLOOKUP, чтобы найти, какие магазины принесли эту прибыль:
=XLOOKUP(E2:E4, T_Profits[Profit], T_Profits[Shop])
Однако ту же задачу можно решить одной строкой:
=LARGE(T_Profits[Profit], {1;2;3})
Если нужен не список, а сумма топ-значений, можно снова обернуть в SUM:
=SUM(LARGE(T_Profits[Profit], {1,2,3}))
Нужно добавить четвёртое значение? Просто расширьте массив:
=SUM(LARGE(T_Profits[Profit], {1,2,3,4}))
Как задать имя массиву, чтобы не повторяться
Если вы часто используете один и тот же набор значений, можно дать ему имя — и вызывать в формулах без повторного ввода. Перейдите на вкладку Формулы → Диспетчер имён и нажмите «Создать». В поле Присваивается формуле введите, например:
={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}
В поле имени напишите, например, Days — теперь можно использовать массив через =Days. Это также работает внутри формул:
=A1*Fives
Если Fives — массив {5,10,15,20}, то результатом будет массив из четырёх значений, умноженных на A1.
Ограничения и важные нюансы
- Динамические массивы не могут автоматически заполнять ячейки внутри структурированных таблиц, но могут использовать их данные как источник.
- Массивы могут содержать только числа, строки в кавычках и логические значения
TRUE/FALSE. Нельзя использовать ссылки на ячейки, другие массивы или подформулы. - Массивы всегда статичны — если значение нужно менять, придётся править формулу.
- Не стоит вставлять в файл такие формулы или структуры, которые невозможно быстро понять, изменить или проверить — особенно если вы делитесь этим файлом с коллегами.
- В некоторых случаях вместо ручного массива проще использовать функции вроде
SEQUENCEдля генерации чисел — так гибче. - Файлы с массивами, созданные в новой версии Excel, при открытии в старой превращаются в CSE-формулы и требуют
Ctrl+Shift+Enter.
