Функция СУММПРОИЗВ в Excel для отчёта продаж по регионам

Выручку по таблице «количество × цена» обычно считают через вспомогательный столбец: перемножают каждую строку, а потом складывают. Справка Microsoft показывает, что одна функция даёт тот же итог, что и длинная запись =C2*D2+C3*D3+C4*D4+C5*D5^[1]. Вспомогательный столбец при этом не нужен, и формула не растёт вместе с таблицей.

Путь к рабочей формуле такой: найти столбцы количества и цены, ввести формулу СУММПРОИЗВ с двумя диапазонами, проверить итог на ручной сумме произведений, исправить #ЗНАЧ! из-за разных размеров и затем скопировать формулу на свои данные с другими критериями. СУММПРОИЗВ в Excel после этого заменяет и вспомогательный столбец, и часть формул с условиями.

Ключевые факты: СУММПРОИЗВ в Excel

• СУММПРОИЗВ перемножает элементы массивов, стоящие на одинаковых местах, и возвращает сумму произведений^[1].
• В формуле можно перемножить от 2 до 255 массивов, обязателен только первый^[1].
• Массивы разного размера дают ошибку #ЗНАЧ!, а нечисловые элементы функция считает нулями^[1].
• Скобки со сравнениями внутри СУММПРОИЗВ перемножают для логики «И» и складывают для логики «ИЛИ».
• Справка Microsoft не рекомендует ссылки на целые столбцы: функция перемножит 1 048 576 ячеек каждого^[1].

Как работает СУММПРОИЗВ в Excel: синтаксис и сумма произведений

СУММПРОИЗВ перемножает элементы диапазонов, стоящие на одинаковых местах, и складывает все произведения: =СУММПРОИЗВ(массив1;[массив2];[массив3];…)^[1]. Обязателен только первый массив, а перемножить можно от 2 до 255 массивов^[1]. В английском интерфейсе функция называется SUMPRODUCT, а сама операция — сумма произведений, отсюда и русское имя.

Все примеры страницы построены на одной таблице продаж:

Формула =СУММПРОИЗВ(C2:C6;D2:D6) вернёт 11900. Excel считает 10×250 + 4×900 + 6×250 + 20×80 + 3×900 — ровно то, что дала бы запись =C2*D2+C3*D3+C4*D4+C5*D5+C6*D6. Разница в длине: при новой строке достаточно поправить два адреса, а не дописывать ещё одно слагаемое. Порядок аргументов значения не имеет — от перестановки множителей сумма произведений не меняется. Удобно ставить первым столбец количества, вторым цену, как в шапке таблицы: формулу тогда легче читать и проверять.

Справка Microsoft ставит одно требование: массивы-аргументы должны иметь одинаковые размерности^[1]. Нечисловые элементы массива функция считает нулями^[1], поэтому пустая ячейка в столбце количества просто обнулит свою строку. По справке СУММПРОИЗВ есть в Excel 2016, Excel 2019, Excel 2021, Excel 2024 и Microsoft 365, включая Mac^[1].

Разобравшись с базовой записью, переходим к отбору строк — ради него функцию чаще всего и выбирают.

Пошагово: выручка по региону через СУММПРОИЗВ со сравнением

СУММПРОИЗВ в Excel учитывает только нужные строки, если добавить в формулу сравнение в скобках: оно даёт для каждой строки ИСТИНА или ЛОЖЬ. При умножении ИСТИНА превращается в 1, ЛОЖЬ — в 0, и строки с нулём выпадают из суммы.

1. Выберите ячейку для итога, например F5.
2. Введите =СУММПРОИЗВ((B2:B6="Север")*C2:C6*D2:D6) — сравнение по региону стоит в скобках первым множителем.
3. Нажмите Enter и проверьте результат: 6800. Сумма произведений здесь собрана только по строкам, где сравнение дало единицу.
4. Сверьте с ручным расчётом: строки 2, 5 и 6 дают 2500 + 1600 + 2700 = 6800.
5. Замените «Север» ссылкой на ячейку с названием региона, чтобы формулу можно было скопировать на другие регионы.

Если регион записан в ячейке F2, формула примет вид =СУММПРОИЗВ((B2:B6=F2)*C2:C6*D2:D6), и итог по другому региону появится после смены одного слова в F2. Справка описывает тот же приём: массивы с условиями берут в скобки и перемножают, например продажи по региону и товару — (B2:B9=B12)*(C2:C9=C12)*D2:D9^[1]. Вместо точки с запятой между массивами можно ставить и другие операторы: звёздочку, косую черту, плюс или минус^[1].

Пошаговый расчёт такой формулы показывает команда вычисления формулы на вкладке «Формулы»^[1]: видно, как сравнение превращается в набор ИСТИНА и ЛОЖЬ, а потом в единицы и нули. Соберите выручку района в тренажёре: урок меняет регион и проверяет, что сравнение стоит внутри СУММПРОИЗВ, а не в отдельном столбце.

Условие внутри скобок может применять функцию к каждой ячейке: =СУММПРОИЗВ((ЛЕВСИМВ(A2:A6;1)="К")*C2:C6) сложит количество товаров на букву «К» и вернёт 7. Однако одно сравнение — только начало: два и больше сравнений соединяют двумя разными способами.

Логика «И» или «ИЛИ» в СУММПРОИЗВ: чем отличаются умножение и сложение

Логика «И» в СУММПРОИЗВ отличается от «ИЛИ» знаком между скобками: условия «И» перемножают, а условия «ИЛИ» складывают. Произведение равно единице, только когда выполнены все условия; сумма больше нуля, когда выполнено хотя бы одно.

Для «ИЛИ» важны внешние скобки вокруг суммы условий: без них Excel сначала умножит второе условие на количество, а потом прибавит первое. Если оба условия «ИЛИ» могут выполниться в одной строке, сумма станет равна двум, и строка посчитается дважды. В примере с чаем и кофе так не бывает: в одной ячейке может стоять только одно название. Проверить итог легко по частям: сумма произведений по чаю равна 4000, по кофе — 6300, вместе 10300.

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

Остаётся понять, как та же функция считает средневзвешенную цену и количество строк.

Средневзвешенная цена и подсчёт строк через СУММПРОИЗВ: примеры

СУММПРОИЗВ считает средневзвешенную цену, если разделить сумму произведений на сумму весов: =СУММПРОИЗВ(C2:C6;D2:D6)/СУММ(C2:C6). Для таблицы продаж результат — 276,74, столько в среднем стоит одна проданная единица с учётом количества. СУММПРОИЗВ в Excel здесь заменяет сразу два вспомогательных столбца: произведение строки и общий итог.

Простое среднее цен в столбце D дало бы другую цифру, потому что не учитывает, что сахара продано больше всего. Подробнее о разнице простого и взвешенного вариантов — на странице о среднем значении и средневзвешенном.

Подсчёт строк по условию делается тем же приёмом, но без столбца значений, и функция возвращает уже не сумму, а количество. Двойной минус превращает ИСТИНА и ЛОЖЬ в 1 и 0: =СУММПРОИЗВ(--(B2:B6="Север")) вернёт 3. Если сравнение уже умножается на столбец, двойной минус не нужен — умножение само переводит логические значения в числа.

Внутри скобок можно проверять и даты: сравнение (МЕСЯЦ(A2:A100)=3), умноженное на столбец количества, оставит только мартовские строки, если в столбце A стоят настоящие даты, а не текст. Для отчёта по месяцам это заменяет вспомогательный столбец с номером месяца.

Именно поэтому разные способы записи требуют отдельного разбора ошибок: одна и та же таблица может дать ноль или #ЗНАЧ!.

Когда СУММПРОИЗВ не подойдёт: #ЗНАЧ!, целые столбцы и текст

СУММПРОИЗВ не подойдёт, если диапазоны разного размера, ссылки ведут на целые столбцы или в перемножаемых ячейках стоит текст. Справка Microsoft разбирает эти случаи отдельной страницей про ошибку #ЗНАЧ!^[2].

• Если у диапазонов разные последние строки, как D2:D13 и E2:E10, функция вернёт #ЗНАЧ!^[2]: выровняйте адреса до E13.
• При ссылках вида A:A функция перемножит 1 048 576 ячеек каждого столбца^[1], и книга заметно замедлится: указывайте точные границы таблицы.
• Если в столбце, который умножают звёздочкой, есть текст «нет», аргумент C2:C3*E2:E3 вернёт #ЗНАЧ!, а те же диапазоны через точку с запятой дадут 0.
• Когда нужна простая сумма по одному-двум условиям без умножения столбцов, понятнее функция с условием в аргументах.

В Google Таблицах функция называется SUMPRODUCT и тоже требует массивов одного размера; второй массив там можно не указывать — он считается набором единиц^[3]. Обычное сложение столбцов разобрано на странице о сумме столбца и Автосумме, а другие функции для расчётов собраны в каталоге формул Excel с разбором функций.