Формулы массива и динамические диапазоны

Урок про то, как одна формула считает сразу по всему столбцу — вместо того чтобы протягивать её на тысячу строк.

Формула массива — это формула, которая обрабатывает не одну пару ячеек, а целые диапазоны одновременно и возвращает сразу много значений.

До сих пор мы писали формулы для одной строки, а потом протягивали их вниз маркером заполнения. Это работает, но у такого подхода есть слабые места: формул в файле становятся тысячи, при добавлении новой строки её легко забыть «дотянуть», а таблица тяжелеет. Формулы массива решают всё это одним выражением.

Что такое операция над диапазоном целиком

Обычная формула =B2*C2 умножает две конкретные ячейки. Формула массива умеет умножать столбец на столбец: =B2:B100*C2:C100 вернёт сразу 99 произведений. Если записать это как формулу массива, результат «разольётся» по 99 ячейкам вниз автоматически.

Математически это поэлементное действие над двумя векторами. Если у нас цена $p_i$ и количество $q_i$, то выручка по каждой строке — это

$$ s_i = p_i \cdot q_i, \quad i = 1, 2, \dots, n $$

а общая выручка — сумма произведений:

$$ S = \sum_{i=1}^{n} p_i \cdot q_i $$

Именно так работает встроенная функция СУММПРОИЗВ (в английской версии SUMPRODUCT): она сама перемножает диапазоны поэлементно и складывает результат, без вспомогательного столбца.

Пример: выручка магазина

Пусть в столбце B лежат цены, в C — проданные количества:

Общую выручку $300 + 200 + 200 = 700$ можно получить одной формулой =СУММПРОИЗВ(B2:B4; C2:C4) — без столбца «Выручка» вовсе.

ARRAYFORMULA в Google Sheets

В Google Sheets есть специальная обёртка ARRAYFORMULA, которая превращает обычную формулу в формулу массива. Запишите в одну ячейку (например D2):

=ARRAYFORMULA(B2:B100 * C2:C100)

и весь столбец D с D2 по D100 заполнится произведениями автоматически. Добавили новый товар в строку 50 — выручка по нему посчитается сама, ничего протягивать не нужно. Внутри ARRAYFORMULA работают и условия: =ARRAYFORMULA(ЕСЛИ(C2:C100>0; B2:B100*C2:C100; 0)) посчитает выручку только там, где есть продажи.

Динамические массивы

В современных таблицах (новый Excel и Google Sheets) многие функции сами возвращают целый диапазон — это и есть динамические массивы. Например, UNIQUE и SORT в Google Sheets выдают столько строк, сколько нужно, и результат «разливается» (spill) по соседним ячейкам. Формула стоит в одной ячейке, а заполняет много — и сама подстраивается под размер данных.

=SORT(UNIQUE(A2:A100))

Эта вложенная формула сначала уберёт повторы из столбца A, потом отсортирует их по алфавиту — и всё одним выражением, без ручного протягивания.

Как это работает

Когда таблица встречает формулу массива, она не считает один результат, а строит целую таблицу значений в памяти и затем размещает её в ячейках. Поэтому такая формула «занимает» сразу диапазон, а в соседние ячейки вписывать ничего нельзя — там появится ошибка переполнения (в новом Excel это #SPILL!), если место занято.

Чтобы убедиться, что результат сходится с теорией, посчитаем сумму произведений на привычном Python — логика та же, что у СУММПРОИЗВ:

price = [30, 50, 25]
count = [10, 4, 8]
# поэлементное произведение и сумма
revenue = [p * q for p, q in zip(price, count)]
print("По строкам:", revenue)
print("Итого выручка:", sum(revenue))

Вывод:

По строкам: [300, 200, 200]
Итого выручка: 700

Результат 700 совпадает с тем, что даст табличная СУММПРОИЗВ(B2:B4; C2:C4) — формула массива делает ровно это, только внутри таблицы.

Когда это удобнее обычных формул

• Длинные столбцы. Одна ARRAYFORMULA вместо тысячи протянутых формул — файл легче и быстрее.
• Растущие таблицы. Новые строки автоматически попадают под формулу, ничего «дотягивать» не нужно.
• Агрегации с условием. СУММПРОИЗВ считает «сумму произведений» или «количество по нескольким условиям» без вспомогательных столбцов.
• Чистота листа. Формула в одной ячейке вместо формул в каждой — меньше шансов случайно сломать одну из них.

Частые ошибки

• Разные размеры диапазонов. B2:B100*C2:C50 — диапазоны разной длины, будет ошибка. Перемножаемые диапазоны должны совпадать по числу строк.
• Ввод в занятую зону. Если под формулой массива уже есть данные, она не «разольётся» и выдаст ошибку переполнения. Освободите ячейки.
• Ручное дублирование. Не нужно протягивать ARRAYFORMULA вниз — она и так покрывает весь диапазон; протягивание создаст конфликт.
• Путают с обычной суммой. СУММ(B2:B4*C2:C4) без обёртки массива в старых таблицах даёт неверный результат — для суммы произведений берите СУММПРОИЗВ.

Итоги

• Формула массива обрабатывает целые диапазоны сразу и возвращает много значений.
• СУММПРОИЗВ считает $\sum p_i \cdot q_i$ без вспомогательного столбца.
• ARRAYFORMULA в Google Sheets «разливает» формулу на весь столбец автоматически.
• Динамические массивы (UNIQUE, SORT) сами подстраивают размер вывода под данные.
• Это удобнее обычных формул на длинных и растущих таблицах; диапазоны должны быть одинаковой длины.