Быстрая навигация по статье
Массивы-константы в Microsoft Excel — это по-настоящему мощный инструмент, с которым можно в один клик решить сразу несколько задач одной формулой. Вместо длинных, повторяющихся и сложных формул вы просто задаёте постоянные значения и защищаете свои данные от случайных изменений.
Для тех, кто только начинает, массивы-константы могут показаться пугающими: их синтаксис и способ ввода немного необычны. Но я уже разобрал все нюансы — покажу, насколько легко они вписываются в реальные задачи. К финалу статьи вы сами удивитесь, как быстро освоили этот навык!
Что такое массивы-константы в Excel
Массив-константа — это набор неизменяемых значений, которые вы вручную прописываете в фигурных скобках прямо внутри формулы. Например:
Если вбить такой массив в ячейку A1 и нажать Enter, Excel сам разложит числа 10, 20, 30 и 40 по ячейкам от A1 до D1.
В этом примере данные "разливаются" по горизонтали — за это отвечают запятые внутри массива. Excel так создает горизонтальные массивы.
Динамический массив — это когда формула автоматически заполняет нужное количество ячеек, а не только ту, куда вы её написали. Массив-константа сам по себе статичен, но если его результату "есть куда разлиться", Excel покажет все элементы массива в нужном диапазоне.
Если разделять значения точками с запятой — массив заполнит ячейки по вертикали, то есть вниз по столбцу:
Можно комбинировать оба разделителя — запятые и точки с запятой — чтобы получить двухмерный массив:
Массивы-константы: Как изменился их принцип работы в новых версиях Excel?
С выходом новых версий Excel (с 2021 года и в Microsoft 365, а также в веб-версии) появилась поддержка динамических массивов. Теперь, если вы напишите формулу с массивом-константой и нажмёте Enter, Excel сам распределит результаты по необходимому диапазону, а сама формула останется только в верхней левой ячейке.
В прежних версиях Excel, где динамические массивы ещё не поддерживаются, чтобы формула вывела результат в несколько ячеек, нужно предварительно выделить необходимые ячейки, ввести формулу и завершить ввод сочетанием клавиш Ctrl+Shift+Enter. Такой подход называется CSE и считается устаревшим.
Дальнейшие примеры подготовлены для современных версий Excel с поддержкой динамических массивов.
Microsoft 365 Personal
В подписке Microsoft 365 вы получаете доступ к Word, Excel и PowerPoint одновременно на пяти устройствах, плюс 1 ТБ в облаке OneDrive и ещё кучу приятных бонусов.
Как сделать свои формулы в Excel суперудобными с помощью массивов-констант
Массивы-константы просто незаменимы в паре с функциями Excel — никакой рутины и копипаста! Вот два самых наглядных примера.
SUM и COUNTIF: Мгновенный подсчет элементов массива по нескольким условиям
Допустим, у вас есть таблица T_TaskLog, в которой нужно узнать, сколько задач с разными статусами: Выполнено, Ожидает и В работе.
Без массивов пришлось бы сочинять длинную формулу (для наглядности покажу разбивку по строкам):
Здесь вы просто складываете три отдельных COUNTIF с помощью плюса.
Обратите внимание: текстовые значения в формулах Excel регистронезависимы, но их обязательно берём в двойные кавычки.
Можно покороче: используйте массив-константу внутри одной функции COUNTIF — все условия просто перечислите через запятую. Введите название функции, затем диапазон, запятая и дальше массив условий:
Три значения укажите сразу в фигурных скобках, закройте скобки и жмите Enter:
Excel покажет вам сразу три результата в виде "разлившегося" массива: 3 для Выполнено, 2 для Ожидает и 2 для В работе. Только эти числа не суммируются автоматически.
Чтобы получить итог — просто оберните всё в функцию SUM и нажмите Enter:
Теперь не нужно три раза писать COUNTIF и складывать их — массив-константа всё сделает за вас, а функция SUM выдаст итог за одну секунду.
LARGE: Как получить топ-3 значений из диапазона одной формулой
Представьте задачу: вывести три самые большие прибыли из таблицы T_Profits отдельным списком.
Самый очевидный вариант — три раза подряд прописать формулу LARGE для каждой из трёх ячеек.
В ячейку E2 внесите формулу, чтобы получить самую высокую прибыль:
В ячейке E3 просто поменяйте номер (получите второе по величине значение):
В E4 — то же самое для третьего результата:
Можно вывести рядом ID магазинов с помощью XLOOKUP для каждого значения:
Но три одинаковых формулы легко запутаться и это отнимает время. Проще — использовать массив-константу внутри функции LARGE и всё сделать за раз:
LARGE сразу сам возвращает первое, второе и третье по прибыли значение — они выводятся в столбик, потому что внутри массива числа разделены точками с запятой.
Если вас интересует сумма этих трёх максимумов — без массивов пришлось бы вписывать три LARGE и суммировать их плюсом:
А если добавить массив-константу и функцию SUM — получается идеальная, короткая формула:
А если завтра потребуется просуммировать четвертый результат — просто допишите в массив ещё одно число, и всё, формулу переписывать не придётся:
Как присвоить массиву-константе своё имя в Excel
Если вы часто используете одни и те же значения, не обязательно каждый раз вручную их прописывать — сохраните массив-константу под именем и подставляйте в формулы сколько угодно раз.
Для этого откройте вкладку "Формулы" и щёлкните "Диспетчер имен".
В новом окне "Диспетчер имен" нажмите "Создать".
В поле "Присваивается имени" поставьте знак равно и сразу откройте фигурную скобку:
Теперь впишите значения массива: если нужен горизонтальный — используйте запятые, если вертикальный — точки с запятой. Текстовые значения пишутся в двойных кавычках. В конце закройте фигурную скобку.
В поле "Имя" придумайте короткое, понятное название для массива и нажмите "ОК".
Теперь этот массив вы можете подставлять в любую формулу: просто напишите знак равно и имя массива, затем Enter:
Именованные массивы, как и обычные, можно использовать в вычислениях. Например, массив Fives:
Если умножить число из ячейки A1 на этот массив, результаты сразу появятся в четырёх соседних ячейках:
Что важно знать о работе с массивами-константами в Excel
Несмотря на все плюсы, не забывайте и о паре нюансов при работе с массивами-константами:
Массивы-константы отлично подходят для функций, которые сами по себе не разливают значения по диапазону. Но в новых версиях Excel есть специальные функции — FILTER, SORT, SORTBY, UNIQUE, XLOOKUP и другие — которые уже умеют генерировать динамические массивы и автоматически заполняют ячейки.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru