Возможности !SEMTools
Пример формулы массива в Excel
Пример формулы массива

Формулы массива — одна из самых мощных, но часто неправильно понятых возможностей 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 могут быть одномерными (вектор), двумерными (таблица) и трехмерными (многослойная таблица).

Одномерные массивы (векторы) в свою очередь могут быть вертикальными и горизонтальными.

два одномерных и один двумерный массив
Одномерные и двумерные массивы в Excel

Трехмерные массивы – довольно редко используемая на практике сущность.

Виды массивов

По типам элементов в них:
По типу данных –

  • числовой массив (массив чисел)
  • строковый массив (текстовые значения)
  • булевый массив (значения ИСТИНА-ЛОЖЬ)
  • смешанного типа

По изменяемости элементов –

  • массив констант,
  • вычисляемый массив

Методы создания массивов

Создать массив в Excel можно множеством способов. Ниже – подробнее с примерами. Все перечисленные выражения в них являются формулами массива, даже если не содержат в себе функций.

Массив констант, созданный вручную

Одномерные и двумерные массивы констант можно создавать вручную и сразу выводить на лист безо всяких дополнительных операций над ними.

При этом разделителем между элементами слева направо является точка с запятой, а разделителем строк – двоеточие.

Двумерные массивы вводятся построчно, т.е. сначала первая строка через точку с запятой, далее двоеточие, вторая строка через точку с запятой, и так далее.

Именованный массив констант

Если приходится один и тот же набор сущностей использовать часто в формулах, его можно сохранить в книге как именованный массив.

Массив цифр как в примере ниже, поможет быстро найти цифры в текстовых ячейках.

создание именованного массива констант на примере цифр
Создаем именованный массив в Excel

Это позволит больше не вводить его вручную, а обращаться к нему по его имени. Более того, Microsoft Excel помогает при их вводе всплывающими подсказками.

вывод именованного массива на лист
Используем именованный массив

С помощью функций

Указанные выше методы создания массивов довольно редки на практике. Чаще всего массивы создаются автоматически при обращении различных функций к диапазонам в Excel.

Единственное условие для создания массива с помощью функции – функция не должна обладать агрегирующим свойством. Например, СУММ, СРЗНАЧ не создадут массив при обращении к диапазону.

На этом сайте есть статья – как дописать символ, слово или текст к нескольким ячейкам. Там рассматриваются несколько способов, в том числе без создания дополнительного столбца. Но вот еще один – с помощью функции СЦЕПИТЬ и формулы массива:

Добавляем текст в ячейки формулой массива с функцией СЦЕПИТЬ
Формула массива с функциями СЦЕПИТЬ и СИМВОЛ

Кавычки-ёлочки в этой формуле нам помогает создать функция СИМВОЛ.

С помощью математических операторов

Достаточно применить любую из математических операций к диапазону – и будет создан массив, аналогичный по размерам.

Массивы в Excel, полученные с помощью математических операторов

С помощью операторов сравнения

Аналогично математическим операторам, массивы создаются при сравнении диапазонов с константой или значением ячейки. Результатом операции сравнения являются значения ИСТИНА или ЛОЖЬ.

Массивы в Excel, полученные с помощью операторов сравнения

С помощью других массивов

В двух предыдущих примерах массивы создавались на основе взаимодействия диапазона ячеек и некой константы.

Но можно создать массив и по принципу наоборот – на основе одной ячейки, произведя ее взаимодействие (операторами или функциями) с массивом констант.

Размер результирующего массива в таком случае будет аналогичен размеру массива констант.

Еще раз обратите внимание на разделители строк и столбцов в массивах. Столбцы разделяются точкой с запятой, строки – двоеточием.

С помощью комбинаций указанных методов

Как вы уже догадались, возможности фантазии безграничны – можно строить какие угодно комбинации перечисленных выше методов, производя операции над массивами и диапазонами.

Вывод массива на лист

Для корректного вывода массива на лист нужно выделить диапазон ячеек эквивалентной размерности и размера, использовать метод создания массива (вручную или формулой), и нажать

Ctrl+Shift+Enter

Если выделить недостаточное количество ячеек – будут выведены не все элементы массива, а только те, что соответствуют по порядку.
Если выделить избыточное количество – лишние ячейки выдадут ошибку #Н/Д.

На примере результирующий массив должен быть размером 5*5, но перед вводом формулы массива был выделен диапазон 6*6.

Ошибка НД при несоответствии размера диапазона и массива

Взаимодействие функций Excel с массивами

Как мы уже выяснили чуть ранее, некоторые функции могут создавать массивы, если их применить к диапазону ячеек. Они обычно обращаются к одной ячейке, и таких функций в Excel большинство.

Однако, есть ряд функций, называемых агрегирующими. Если им на вход подается диапазон или массив, они возвращают единственное результирующее значение. Самая популярная – функция СУММ. Но есть и множество других.

Из раздела математических функций к таковым также относятся ПРОИЗВЕД, СУММПРОИЗВ, СУММЕСЛИ, СУММЕСЛИМН.

Практически все статистические функции по природе берут на вход диапазон или массив и возвращают одно число. Наиболее популярные из них – СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ.

Среди логических функций агрегирующим свойством обладают функция И и функция ИЛИ.

Ну и особняком можно выделить функции поиска. Их нельзя в полной мере назвать агрегирующими, но их взаимодействие с массивами весьма похоже. Ведь они тоже:

  • учитывают весь массив
  • выводят одно значение

ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ – эти функции поиска используются наиболее часто.

Формулы массива – примеры формул

Ну что ж, теперь, когда основной понятийный аппарат рассмотрен, приступим к практическому этапу. Я покажу на нескольких задачках из реальной жизни, как их решать, используя комбинации функций и массивов, которые они создают и обсчитывают.

Приступаем к практическим шагам:

Учимся формулам массива 1/4:

Как создать алфавит в Excel