Массивы в Excel

Массивы в Excel — это структуры данных, которые состоят из одного или нескольких значений. Они могут представлять собой диапазон ячеек, введенный вручную набор значений или результат вычисления формулы. Формально массив в Excel — это:

• одномерная последовательность значений (в строку или в столбец);
• двумерная таблица значений (в несколько строк и столбцов);
• формула, возвращающая несколько значений (динамический массив).

Вот примеры массивов:

• {10, 20, 30} — горизонтальный одномерный массив из трех чисел;
• {10; 20; 30} — вертикальный массив;
• {1, 2; 3, 4} — двумерный массив: первая строка — 1 и 2, вторая — 3 и 4.

Основы работы с массивами в Excel

Типы массивов

Ввод формулы массива

Оптимизация вычислений с формулами массива

Способы фильтрации и сортировки данных

Создание динамических диапазонов данных

Анализ данных с функциями массива

Ошибки и их исправление при работе с массивами в Excel

Основы работы с массивами в Excel

Разберем основные моменты, связанные с массивами в Excel.

Типы массивов

В Excel существуют два типа массивов:

• статические. Вручную введенные значения, например {1,2,3};
• динамические. Создаются с помощью формул и автоматически обновляются при изменении источника данных.

В Excel 365 и Excel 2019 появились динамические массивы — функция, при которой формулы возвращают массив значений и результат «разливается» в нужное число ячеек. К примеру, =SEQUENCE(3, 2), результат:

1   2

3   4

5   6

Раньше подобное можно было сделать только через комбинации ROW(), COLUMN(), INDEX() и Ctrl+Shift+Enter.

Ввод формулы массива

До Excel 365, чтобы ввести формулу массива, нужно было нажимать Ctrl+Shift+Enter, иначе результатом была ошибка или неверное значение. Такие формулы называют CSE-формулами.

В новых версиях достаточно обычного Enter: Excel сам вносит результат.

Оптимизация вычислений с формулами массива

Массивы позволяют обрабатывать большие объемы данных, но при неосторожном использовании они могут замедлить работу книги. Чтобы этого избежать, стоит придерживаться принципов оптимизации.

1. Избегайте вычислений на всю колонку. Формула =SUM(A:A) просматривает 1 048 576 ячеек. Это допустимо для простых вычислений, но в формулах массива лучше ограничивать диапазон: =SUM(A2:A1000).

2. Используйте LET для повторяющихся выражений. Функция LET позволяет сохранять подвыражения и использовать их несколько раз. Пример:

=LET(

  объем, A2:A100,

  цена, B2:B100,

  суммировать, объем * цена,

  SUM(суммировать)

)

Это ускоряет выполнение формулы и упрощает ее чтение.

3. Заменяйте ресурсоемкие формулы IF на CHOOSE, XLOOKUP или IFS. Например, вместо =IF(A1=«A»,1,IF(A1=«B»,2,IF(A1=«C»,3,0))) можно использовать =XLOOKUP(A1, {«A»,«B»,«C»}, {1,2,3}, 0) или =CHOOSE(MATCH(A1, {«A»,«B»,«C»},0), 1, 2, 3).

Способы фильтрации и сортировки данных

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

1. Фильтрация с FILTER. Пример: =FILTER(A2:C100, B2:B100=«Москва»). Возвращает все строки из диапазона A2:C100, где во втором столбце указана «Москва». Можно добавлять условия: =FILTER(A2:C100, (B2:B100=«Москва») * (C2:C100=«Оплачено»)).

2. Сортировка с SORT. =SORT(A2:C100, 3, -1). Сортирует по третьему столбцу в порядке убывания.

3. Уникальные значения: UNIQUE. =UNIQUE(A2:A100). Позволяет быстро сформировать список уникальных клиентов, товаров, регионов и других параметров.

4. Комбинирование: SORT(FILTER(…)). =SORT(FILTER(A2:D100, C2:C100=«Склад 1»), 4, -1). Фильтрует по складу и сортирует по столбцу с остатками.

Создание динамических диапазонов данных

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

1. Таблицы Excel. Если вы оформите данные как таблицу (Ctrl+T), то Excel будет использовать структурированные ссылки: =SUM(Таблица1[Сумма]). 

Преимущества:

• автоматически захватывает новые строки;
• можно использовать имена колонок;
• легко подключается к FILTER, SORT, XLOOKUP.

2. OFFSET + COUNTA. =SUM(OFFSET(A1, 0, 0, COUNTA(A:A))). Формула считает число непустых ячеек в столбце A и формирует соответствующий диапазон.

3. INDEX + MATCH. =SUM(A2:INDEX(A:A, MATCH(999999,A:A))). Надежный способ найти последнюю числовую ячейку в столбце A.

4. SEQUENCE + INDEX. =INDEX(A2:A1000, SEQUENCE(COUNTA(A2:A1000))). Создает динамический массив из значений A2:A1000 длиной по числу непустых ячеек.

Анализ данных с функциями массива

Теперь рассмотрим, как массивы позволяют выполнять мощный анализ прямо в формулах Excel.

1. Множественные условия: SUMPRODUCT. =SUMPRODUCT((A2:A100=«Москва»)*(B2:B100=«Категория A»)*(C2:C100)). Суммирует значения в C2:C100 при совпадении условий.

2. Массивные IF. =IF(A2:A100>100, «Крупный заказ», «Обычный»). Выводит массив с описанием для каждого значения.

3. Расчет по категориям: CHOOSE, SWITCH. =CHOOSE(MATCH(B2:B100, {«Эконом»,«Стандарт»,«Премиум»}, 0), 10, 20, 30). Присваивает числовой коэффициент каждой категории.

4. Прогнозы и рост: GROWTH, TREND. Можно использовать с массивами: =TREND(Продажи, Месяцы, SEQUENCE(12)). Строит прогноз на следующие 12 месяцев.

5. Генерация индексов и списков: SEQUENCE, RANDARRAY.

• =SEQUENCE(10,1,100,10). Создает массив: 100, 110, 120, …
• =RANDARRAY(5,1,1,100,TRUE). Случайные целые числа от 1 до 100.

Ошибки и их исправление при работе с массивами в Excel

При работе с массивами в Excel могут возникать специфические ошибки. Разберем основные из них.

1. #SPILL!. Происходит, когда Excel не может «разлить» результат массива, так как мешают другие ячейки. 

Что делать:

• убедитесь, что ячейки под формулой пустые;
• разбейте объединенные ячейки;
• удалите формулы, мешающие разливу.

2. #VALUE!. Возникает при несовместимых типах данных или несогласованных диапазонах в формулах массива. 

Решение:

• проверьте, чтобы массивы имели одинаковую длину;
• используйте IFERROR, IFNA для замены ошибок на понятные значения.

3. #N/A. Значение не найдено при поиске. Исправление: =IFNA(XLOOKUP(«Иванов», A:A, B:B), «Не найдено»).

4. Ошибки из-за несогласованных размеров. Например, =A1:A5 + B1:B3. Массивы должны быть одной длины.