Неочевидная сила Excel: константы массивов, которые решат кучу задач

Если вы часто работаете с формулами в 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.