Массив в Excel — это набор значений, с которым формула работает целиком, а не по одной ячейке. Чтобы сделать формулу массива в Excel для Microsoft 365, 2021 или 2024, напишите её в одной ячейке, например =C2:C7*D2:D7, и нажмите Enter: результаты сами заполнят соседние ячейки. В Excel 2019 и более старых версиях сначала выделите диапазон под ответ, введите формулу и нажмите Ctrl+Shift+Enter, тогда Excel возьмёт её в фигурные скобки.
Если адреса ячеек пока путаются, начните с разбора, что такое диапазон в Excel: массив почти всегда строится из диапазона.
CheckroiПодборка курсов по Microsoft Excel684 курса • 64 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Что такое массив в Excel и чем он отличается от диапазона
Диапазон означает ячейки на листе, например B2:B7. Массив же состоит из значений, которые формула достаёт из диапазона или вычисляет сама и хранит в памяти. Формула =C2:C7*D2:D7 берёт два диапазона и получает новый массив из шести произведений, которого на листе раньше не было.
Слово «массив» пришло из программирования, но в Excel за ним стоит обычный набор значений из ячеек. Если интересно, как массивы устроены в коде, есть отдельный материал: массив в программировании и его отличие от списка.
| Вид массива | Как выглядит | Пример |
|---|---|---|
| Одномерный вертикальный | один столбец | B2:B7 или =ПОСЛЕДОВ(5) |
| Одномерный горизонтальный | одна строка | B2:G2 или =ПОСЛЕДОВ(1;5) |
| Двумерный | строки и столбцы сразу | A2:D7 или =ПОСЛЕДОВ(4;5) |
| Константа массива | значения в фигурных скобках прямо в формуле | ={10;20;30} |
Дальше все примеры считаются на одной таблице продаж канцелярии. Скопируйте её на лист с ячейки A1, чтобы повторять шаги.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Менеджер | Товар | Количество | Цена, ₽ |
| 2 | Иванова | Блокнот | 12 | 150 |
| 3 | Петров | Ручка | 40 | 35 |
| 4 | Иванова | Папка | 15 | 90 |
| 5 | Сидоров | Степлер | 4 | 420 |
| 6 | Петров | Блокнот | 8 | 150 |
| 7 | Иванова | Маркер | 20 | 60 |
Как сделать формулу массива в Excel 365, 2021 и 2024
В новых версиях работают динамические массивы. По справке Microsoft, формула, которая возвращает несколько значений, сама раскладывает их по соседним ячейкам. Это называется переносом.
- Встаньте в пустую ячейку F2.
- Введите
=C2:C7*D2:D7. - Нажмите Enter. В ячейках F2:F7 появятся суммы по каждой строке: 1 800, 1 400, 1 350, 1 680, 1 200 и 1 200.
- Щёлкните любую ячейку из F3:F7. Вокруг всего блока появится рамка, а формула в строке формул будет бледной: менять её можно только в первой ячейке, F2.
Перетягивать формулу вниз не нужно. Добавите строку в таблицу и поправите адреса в F2, и ответ пересчитается целиком.
Чтобы сослаться на весь перенесённый блок из другой формулы, поставьте после адреса первой ячейки знак #. Например, =СУММ(F2#) вернёт 8 630. Microsoft называет этот знак оператором диапазона переноса.
Формула массива в одной ячейке
Выводить массив на лист не обязательно. Если обернуть его в функцию, которая сворачивает значения в одно число, ответ будет в одной ячейке:
| Задача | Формула | Ответ |
|---|---|---|
| Выручка за все продажи | =СУММ(C2:C7*D2:D7) |
8 630 |
| Самая крупная продажа | =МАКС(C2:C7*D2:D7) |
1 800 |
| Выручка Ивановой | =СУММ((A2:A7="Иванова")*C2:C7*D2:D7) |
4 350 |
| Сколько продаж дороже 1 300 ₽ | =СУММ(--(C2:C7*D2:D7>1300)) |
4 |
Без массива для той же выручки понадобился бы вспомогательный столбец с =C2*D2 и сумма по нему. Обычный способ разобран в статье как посчитать сумму в столбце Excel, а если условие одно, хватит функции СУММЕСЛИ.
В третьей строке таблицы сравнение A2:A7="Иванова" даёт массив ИСТИНА и ЛОЖЬ. При умножении ИСТИНА превращается в 1, а ЛОЖЬ в 0, поэтому чужие продажи обнуляются. Двойной минус в четвёртой строке делает то же самое без умножения.
Формула массива через Ctrl+Shift+Enter в Excel 2019 и старше
Динамических массивов в Excel 2019, 2016 и более ранних нет. Там формулу массива вводят по-старому, Microsoft называет такие формулы устаревшими формулами массива:
- Выделите ячейки под ответ, причём столько же, сколько значений вернёт формула. Для нашего примера это F2:F7.
- Наберите
=C2:C7*D2:D7, не снимая выделения. - Нажмите Ctrl+Shift+Enter. Excel запишет формулу во все шесть ячеек и покажет её как
{=C2:C7*D2:D7}.
Для одной ячейки шаги те же: встаньте в ячейку, введите =СУММ(C2:C7*D2:D7) и нажмите Ctrl+Shift+Enter.
Фигурные скобки вокруг формулы руками не набирайте. По справке Microsoft, тогда Excel примет формулу за текст и считать её не будет.
В Excel 365 такие формулы по-прежнему работают ради совместимости. Сама Microsoft в англоязычной версии справки пишет, что их больше не стоит применять, и предлагает переписать их в обычный ввод через Enter. Для этого скопируйте текст формулы из первой ячейки, удалите весь старый блок и введите формулу заново в верхнюю левую ячейку.
Как изменить или удалить массив в Excel
Правила зависят от того, как формула введена.
Динамический массив
- Изменить: правьте только первую ячейку блока, остальные обновятся сами.
- Удалить: очистите первую ячейку клавишей Delete, и весь перенесённый блок исчезнет.
Формула массива с фигурными скобками
- Изменить часть массива нельзя. Если щёлкнуть F3 и нажать Delete, Excel покажет сообщение о том, что менять часть формулы массива запрещено.
- Изменить формулу: выделите весь блок, начиная с левой верхней ячейки, нажмите F2, поправьте формулу и снова нажмите Ctrl+Shift+Enter.
- Удалить: выделите весь блок, например F2:F7, и нажмите Delete.
- Уменьшить: так не получится, сначала удалите формулу целиком, а потом введите новую на меньший диапазон.
- Вставить строку внутрь такого блока или удалить из него строку тоже нельзя.
Если таблицу будут править коллеги, эти ограничения лучше записать прямо на листе. Человек, который не знает про Ctrl+Shift+Enter, получит ошибку и решит, что файл сломан.
Константы массива и функция ПОСЛЕДОВ
Константой массива называют значения, записанные прямо в формуле в фигурных скобках. Здесь скобки, наоборот, ставят вручную. Например, =СУММ({10;20;30}) вернёт 60.
Разделители внутри скобок зависят от языка и региональных настроек. В справке Microsoft для русской версии матрица из трёх строк и четырёх столбцов записана так: ={1;2;3;4:5;6;7;8:9;10;11;12}. Точка с запятой разделяет столбцы, двоеточие отделяет одну строку от другой, поэтому ={1:2:3} ляжет в столбец, а ={1;2;3} в строку. В англоязычной справке для того же используют запятую и точку с запятой. Если Excel ругается на константу, первым делом проверьте разделители.
Если массив вышел строкой, а нужен столбец, поверните его функцией ТРАНСП: =ТРАНСП({1;2;3}) разложит те же числа сверху вниз. Другие способы поменять строки и столбцы местами описаны в статье как транспонировать таблицу в Excel.
Константы могут содержать числа, текст в кавычках, ИСТИНА и ЛОЖЬ. Ссылки на ячейки и формулы внутри скобок писать нельзя: на {1;2;A1:D4} Excel выведет предупреждение.
Чтобы не набирать длинные ряды руками, используйте функцию ПОСЛЕДОВ:
=ПОСЛЕДОВ(строки;[столбцы];[начало];[шаг])
=ПОСЛЕДОВ(5) → 1, 2, 3, 4, 5 в столбец
=ПОСЛЕДОВ(1;5;10;10) → 10, 20, 30, 40, 50 в строкуПОСЛЕДОВ работает только там, где есть динамические массивы: в Excel для Microsoft 365, 2021 и 2024, а также в мобильных приложениях.
Функции динамических массивов на примере таблицы продаж
Эти функции сами возвращают массив и раскладывают его по листу. В Excel 2019 и старше их нет, а для кого Excel не установлен, подойдёт Excel онлайн в браузере.
| Функция | Что делает | Формула на нашей таблице | Результат |
|---|---|---|---|
| УНИК | убирает повторы | =УНИК(A2:A7) |
Иванова, Петров, Сидоров |
| ФИЛЬТР | оставляет строки по условию | =ФИЛЬТР(B2:B7;A2:A7="Иванова") |
Блокнот, Папка, Маркер |
| СОРТ | сортирует | =СОРТ(УНИК(B2:B7)) |
Блокнот, Маркер, Папка, Ручка, Степлер |
| СОРТПО | сортирует по соседнему столбцу | =СОРТПО(B2:B7;C2:C7;-1) |
Ручка, Маркер, Папка, Блокнот, Блокнот, Степлер |
| ПОСЛЕДОВ | создаёт ряд чисел | =ПОСЛЕДОВ(6) |
номера строк от 1 до 6 |
Функции вкладываются друг в друга, как в строке с СОРТ. Синтаксис и аргументы смотрите в справке: УНИК, ФИЛЬТР, СОРТ.
Если ФИЛЬТР ничего не нашёл, он вернёт ошибку. Третий аргумент задаёт, что показать вместо неё: =ФИЛЬТР(B2:B7;A2:A7="Орлова";"нет продаж"). Подробнее про отбор строк есть в статье как сделать фильтр по столбцам в Excel, а про повторы есть разбор как посчитать уникальные значения.
Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 800 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в каналЧастые ошибки с массивами и что с ними делать
| Что видите | Почему | Что сделать |
|---|---|---|
| #ПЕРЕНОС! в ячейке с формулой | место под результат занято: в одной из ячеек уже есть данные | щёлкните значок ошибки, выберите «Выделить препятствующие ячейки» и очистите их |
| #ПЕРЕНОС! внутри умной таблицы | в таблицах Excel перенос не поддерживается | вынесите формулу за пределы таблицы или преобразуйте таблицу в диапазон |
| #ПЕРЕНОС! рядом с объединёнными ячейками | в объединённые ячейки перенос не идёт | отмените объединение или перенесите формулу |
#ПЕРЕНОС! у =ВПР(A:A;…) |
ссылка на весь столбец просит больше миллиона ответов, и они не помещаются на лист | ссылайтесь на нужные строки, например A2:A7, или поставьте @ перед ссылкой и протяните формулу |
| #Н/Д в части ответа | в старой формуле выделено больше ячеек, чем значений в массиве | удалите формулу и выделите ровно столько ячеек, сколько значений |
| Ответ только в одной ячейке, а не столбцом | Excel 2019 и старше: формулу ввели через Enter | выделите диапазон и введите заново через Ctrl+Shift+Enter |
| В старой формуле появился знак @ | файл открыли в Excel с динамическими массивами, он показывает неявное пересечение, которое раньше было скрыто | обычно ничего: формула считает так же. Уберёте @, и результат может перенестись в соседние ячейки |
| #ССЫЛКА! после закрытия другой книги | динамический массив ссылается на закрытую книгу | оставьте открытыми обе книги или скопируйте данные к себе |
Причины #ПЕРЕНОС! и способы их снять по шагам описаны в справке Microsoft об этой ошибке. Про знак @ есть отдельная страница: оператор неявного пересечения.
Ещё одна частая ловушка связана с функциями И и ИЛИ внутри формулы массива. Они возвращают одно значение на весь диапазон, поэтому условие «и то, и другое» записывают умножением, а «или» сложением: =СУММ((A2:A7="Иванова")*(B2:B7="Блокнот")*C2:C7) вернёт 12.
На больших таблицах формулы массива пересчитываются медленнее обычных, особенно на слабом компьютере. Если файл на сотни тысяч строк стал тормозить, ссылайтесь на точные диапазоны, а не на столбцы целиком.
Где освоить Excel системно
Массивы закрывают задачи, на которые раньше уходили вспомогательные столбцы и десяток протянутых формул. Чтобы дальше собирать отчёты, сводные таблицы и дашборды без поиска по справке, выберите курс по Excel с практикой на реальных данных. Ниже собраны программы из нашего каталога, а базу полезно освежить по статье основные функции Excel с примерами.
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 35 100 ₽ | 1691 ₽/мес. | 8 недель | Обзор курса |
| Power BI & Excel PRO Перейти на сайт курса | Нетология | 38 500 ₽ | 3208 ₽/мес. | 2 месяца | Обзор курса |
| Excel для работы Перейти на сайт курса |  Эдюсон | 16 500 ₽ | 1375 ₽/мес. | 2 недели | Обзор курса |
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel для аналитика Перейти на сайт курса |  Moscow Business Academy | 17 200 ₽ | 2866 ₽/мес. | 2 месяца | Обзор курса |
Больше программ — в полном каталоге курсов по Microsoft Excel
