Формулы массива — одна из самых мощных, но часто неправильно понятых возможностей Excel. Если раньше они были чем-то загадочным и «магическим», то с выходом Excel 365 всё изменилось. Новые версии Excel упростили работу с массивами, сделав их интуитивно понятными и удобными.
В этой статье вы:
- разберётесь, что такое формула массива и как она работает;
- узнаете, чем отличаются формулы в старых и новых версиях Excel;
- увидите практические примеры и типовые ошибки;
- научитесь применять их в повседневной работе.
Что такое формула массива
Формула массива — это формула, которая работает не с отдельным значением, а с набором значений одновременно. Такие формулы могут:
- получать массив на вход (например, диапазон из нескольких ячеек);
- создавать массив внутри формулы;
- возвращать массив как результат.
В старых версиях Excel (до 2019 включительно) формулы массива требовали особого способа ввода — через комбинацию Ctrl + Shift + Enter. Поэтому их ещё называют CSE-формулами. Excel обрамлял такие формулы фигурными скобками { }, показывая, что это массив.
В новых версиях Excel (Microsoft 365 и Excel 2021) появился механизм динамических массивов. Теперь достаточно просто нажать Enter — и если формула возвращает массив, Excel сам «прольёт» его в соседние ячейки. Управлять этим можно с помощью новых функций, например SEQUENCE, FILTER, UNIQUE, SORT.
Примеры формул массива
Пример 1. Классическая формула массива (старый Excel):
=СУММ(ЕСЛИ(A1:A10>100; 1; 0))Чтобы она сработала, нужно было нажимать Ctrl + Shift + Enter. Она считает количество значений больше 100 в диапазоне.
В Excel 365 эта же формула работает без специальных клавиш:
=СУММ((A1:A10>100)*1)Пример 2. Формула, возвращающая массив значений (новый Excel):
=ПОСЛЕД(5)Результат: числа от 1 до 5 в вертикальном столбце. Формула занимает не одну ячейку, а сразу пять — это и есть динамический массив.
Пример 3. Извлечение уникальных значений из столбца:
=УНИК(A2:A100)Массив и диапазон: в чём разница
Массив — это внутреннее представление набора значений, которое Excel использует в формулах. Порядок и длина фиксированы. Excel воспринимает массив как цельную структуру: нельзя изменить его часть, вставить строку в середину или отсортировать.
Диапазон — это просто адрес ячеек на листе. Его можно сортировать, расширять, вставлять строки и столбцы. Формула массива может ссылаться на диапазон, но работает с ним иначе: как с последовательностью значений, а не как с набором адресов.
Типовая ошибка: нельзя изменить часть массива
Если вы попытаетесь удалить или отредактировать ячейку, которая входит в диапазон, заполненный формулой массива, Excel выдаст ошибку:
Нельзя изменить часть массива.
Чтобы это исправить, нужно выделить всю область, заполненную формулой, и удалить её целиком, либо изменить саму формулу в верхней ячейке.
Как понять, что формула — это массив?
Если:
- вводили формулу через Ctrl+Shift+Enter — это массив (в старом Excel);
- формула «проливается» вниз или вбок — это динамический массив (в новом Excel);
- возникает ошибка при редактировании одной ячейки — это тоже признак массива.
Размерность массивов
Как массивы, так и диапазоны в Excel могут быть одномерными (вектор), двумерными (таблица) и трехмерными (многослойная таблица).
Одномерные массивы (векторы) в свою очередь могут быть вертикальными и горизонтальными.
Трехмерные массивы – довольно редко используемая на практике сущность.
Виды массивов
По типам элементов в них:
По типу данных –
- числовой массив (массив чисел)
- строковый массив (текстовые значения)
- булевый массив (значения ИСТИНА-ЛОЖЬ)
- смешанного типа
По изменяемости элементов –
- массив констант,
- вычисляемый массив
Методы создания массивов
Создать массив в Excel можно множеством способов. Ниже – подробнее с примерами. Все перечисленные выражения в них являются формулами массива, даже если не содержат в себе функций.
Массив констант, созданный вручную
Одномерные и двумерные массивы констант можно создавать вручную и сразу выводить на лист безо всяких дополнительных операций над ними.
При этом разделителем между элементами слева направо является точка с запятой, а разделителем строк – двоеточие.
Двумерные массивы вводятся построчно, т.е. сначала первая строка через точку с запятой, далее двоеточие, вторая строка через точку с запятой, и так далее.
Именованный массив констант
Если приходится один и тот же набор сущностей использовать часто в формулах, его можно сохранить в книге как именованный массив.
Массив цифр как в примере ниже, поможет быстро найти цифры в текстовых ячейках.
Это позволит больше не вводить его вручную, а обращаться к нему по его имени. Более того, Microsoft Excel помогает при их вводе всплывающими подсказками.
С помощью функций
Указанные выше методы создания массивов довольно редки на практике. Чаще всего массивы создаются автоматически при обращении различных функций к диапазонам в Excel.
Единственное условие для создания массива с помощью функции – функция не должна обладать агрегирующим свойством. Например, СУММ, СРЗНАЧ не создадут массив при обращении к диапазону.
На этом сайте есть статья – как дописать символ, слово или текст к нескольким ячейкам. Там рассматриваются несколько способов, в том числе без создания дополнительного столбца. Но вот еще один – с помощью функции СЦЕПИТЬ и формулы массива:
Кавычки-ёлочки в этой формуле нам помогает создать функция СИМВОЛ.
С помощью математических операторов
Достаточно применить любую из математических операций к диапазону – и будет создан массив, аналогичный по размерам.
С помощью операторов сравнения
Аналогично математическим операторам, массивы создаются при сравнении диапазонов с константой или значением ячейки. Результатом операции сравнения являются значения ИСТИНА или ЛОЖЬ.
С помощью других массивов
В двух предыдущих примерах массивы создавались на основе взаимодействия диапазона ячеек и некой константы.
Но можно создать массив и по принципу наоборот – на основе одной ячейки, произведя ее взаимодействие (операторами или функциями) с массивом констант.
Размер результирующего массива в таком случае будет аналогичен размеру массива констант.
Еще раз обратите внимание на разделители строк и столбцов в массивах. Столбцы разделяются точкой с запятой, строки – двоеточием.
С помощью комбинаций указанных методов
Как вы уже догадались, возможности фантазии безграничны – можно строить какие угодно комбинации перечисленных выше методов, производя операции над массивами и диапазонами.
Вывод массива на лист
Для корректного вывода массива на лист нужно выделить диапазон ячеек эквивалентной размерности и размера, использовать метод создания массива (вручную или формулой), и нажать
Ctrl +Shift +Enter
Если выделить недостаточное количество ячеек – будут выведены не все элементы массива, а только те, что соответствуют по порядку.
Если выделить избыточное количество – лишние ячейки выдадут ошибку #Н/Д.
На примере результирующий массив должен быть размером 5*5, но перед вводом формулы массива был выделен диапазон 6*6.
Взаимодействие функций Excel с массивами
Как мы уже выяснили чуть ранее, некоторые функции могут создавать массивы, если их применить к диапазону ячеек. Они обычно обращаются к одной ячейке, и таких функций в Excel большинство.
Однако, есть ряд функций, называемых агрегирующими. Если им на вход подается диапазон или массив, они возвращают единственное результирующее значение. Самая популярная – функция СУММ. Но есть и множество других.
Из раздела математических функций к таковым также относятся ПРОИЗВЕД, СУММПРОИЗВ, СУММЕСЛИ, СУММЕСЛИМН.
Практически все статистические функции по природе берут на вход диапазон или массив и возвращают одно число. Наиболее популярные из них – СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ.
Среди логических функций агрегирующим свойством обладают функция И и функция ИЛИ.
Ну и особняком можно выделить функции поиска. Их нельзя в полной мере назвать агрегирующими, но их взаимодействие с массивами весьма похоже. Ведь они тоже:
- учитывают весь массив
- выводят одно значение
ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ – эти функции поиска используются наиболее часто.
Формулы массива – примеры формул
Ну что ж, теперь, когда основной понятийный аппарат рассмотрен, приступим к практическому этапу. Я покажу на нескольких задачках из реальной жизни, как их решать, используя комбинации функций и массивов, которые они создают и обсчитывают.
Приступаем к практическим шагам:
Учимся формулам массива 1/4: