Как использовать функцию СУММПРОИЗВ в Excel
Функция СУММПРОИЗВ (SUMPRODUCT) перемножает соответствующие элементы указанных массивов и возвращает сумму этих произведений. Это универсальный инструмент, который заменяет сложные цепочки формул при расчете итоговых сумм, средневзвешенных значений и данных с несколькими условиями отбора. В отличие от обычных функций суммирования, она позволяет выполнять арифметические действия внутри массива до этапа сложения.
Краткий ответ: Чтобы посчитать общую стоимость товаров, умножьте диапазон «Количество» на диапазон «Цена» одной формулой: =СУММПРОИЗВ(B2:B10; C2:C10). Функция сама перемножит пары ячеек и сложит результаты.
Принцип работы и синтаксис
Логика функции строится на двух этапах: сначала происходит поэлементное умножение, затем — суммирование полученных значений.
Синтаксис в русской версии Excel:
=СУММПРОИЗВ(массив1; [массив2]; [массив3]; ...)
- массив1 — обязательный аргумент. Первый диапазон или массив, элементы которого нужно перемножить.
- массив2, массив3... — необязательные аргументы. От 2 до 255 дополнительных диапазонов.
Важные правила:
- Все массивы должны иметь одинаковые размеры (одинаковое количество строк и столбцов). Если размеры не совпадают, Excel вернет ошибку
#ЗНАЧ!. - Текстовые значения в массивах трактуются как нули.
- Логические значения (ИСТИНА/ЛОЖЬ) также считаются нулями, если их не преобразовать в числа явно.
Базовый пример: расчет общей выручки
Самый частый сценарий использования — подсчет общей суммы заказа или выручки, когда есть колонки с количеством и ценой.
| A | B | C | |
|---|---|---|---|
| 1 | Товар | Количество | Цена за шт. |
| 2 | Яблоки | 10 | 50 |
| 3 | Груши | 5 | 80 |
| 4 | Сливы | 8 | 40 |
Чтобы получить общую сумму, не нужно создавать вспомогательный столбец D с формулой B2*C2 и суммировать его. Достаточно одной ячейки:
=СУММПРОИЗВ(B2:B4; C2:C4)
Как это считает Excel:
(10 * 50) + (5 * 80) + (8 * 40) = 500 + 400 + 320 = 1220
Расчет средневзвешенного значения
СУММПРОИЗВ идеально подходит для нахождения среднего значения, где каждый элемент имеет свой «вес» (значимость). Обычная функция СРЗНАЧ здесь не подойдет, так как она считает все значения равнозначными.
Формула средневзвешенного:
=СУММПРОИЗВ(Значения; Веса) / СУММ(Веса)
Пример: Расчет средней оценки студента с учетом кредитных часов (весов предмета).
| Предмет | Оценка (B) | Кредиты (C) |
|---|---|---|
| Математика | 4 | 5 |
| История | 5 | 2 |
| Физкультура | 3 | 1 |
Формула:
=СУММПРОИЗВ(B2:B4; C2:C4) / СУММ(C2:C4)
Расчет: (4*5 + 5*2 + 3*1) / (5+2+1) = 33 / 8 = 4.125
Использование с условиями (замена СУММЕСЛИМН)
Главная мощь СУММПРОИЗВ раскрывается при фильтрации данных. Она может работать как аналог СУММЕСЛИМН, но с более гибкой логикой, включая операторы сравнения (>, <, <>) и логику «ИЛИ».
Условие с одним критерием
Чтобы просуммировать продажи только по товару «Яблоки», нужно превратить логическое условие в числа (1 и 0). Для этого используется двойное отрицание -- или умножение на 1.
=СУММПРОИЗВ(--(A2:A10="Яблоки"); B2:B10; C2:C10)
--(A2:A10="Яблоки")создает массив из 1 (если условие верно) и 0 (если неверно).- При умножении на 0 строки, не соответствующие условию, обнуляются и не влияют на итоговую сумму.
Несколько условий (логика «И»)
Если нужно найти сумму продаж «Яблок», проданных в «Январе»:
=СУММПРОИЗВ(--(A2:A10="Яблоки"); --(D2:D10="Январь"); E2:E10)
Здесь E2:E10 — диапазон с суммами продаж. Строка учтется только если оба условия истинны (1 * 1 = 1).
Сложные условия (операторы сравнения)
СУММПРОИЗВ позволяет использовать знаки больше/меньше, что сложно реализовать в стандартных функциях суммирования без дополнительных столбцов.
Пример: Посчитать сумму продаж, где количество > 5 И цена < 100.
=СУММПРОИЗВ(--(B2:B10>5); --(C2:C10<100); B2:B10; C2:C10)
Внимание: При использовании логических условий внутри СУММПРОИЗВ обязательно преобразуйте результаты сравнений в числа с помощью двойного минуса -- или умножения на 1. Без этого функция вернет 0 или ошибку, так как не умеет умножать текст «ИСТИНА» на числа.
Частые ошибки и ограничения
| Ошибка | Причина | Решение |
|---|---|---|
| #ЗНАЧ! | Диапазоны имеют разную длину (например, B2:B10 и C2:C11). | Проверьте границы всех массивов. Они должны строго совпадать. |
| 0 (ноль) | Забыли преобразовать логические значения в числа при условиях. | Добавьте -- перед условием: --(A2:A10="Текст"). |
| Медленная работа | Использованы ссылки на целые столбцы (A:A; B:B). | Указывайте конкретные диапазоны (A2:A1000). СУММПРОИЗВ обрабатывает даже пустые ячейки полных столбцов, что нагружает процессор. |
| Неверный результат | В диапазоне есть текстовые данные или ошибки (#Н/Д). | Очистите данные или используйте функцию ЕСЛИОШИБКА внутри массива. Текст считается как 0. |
FAQ
Чем СУММПРОИЗВ лучше СУММЕСЛИМН?
СУММЕСЛИМН проще для базовой фильтрации по равенству. СУММПРОИЗВ выигрывает, когда нужно использовать операторы сравнения (> , <), выполнять математические действия внутри формулы или реализовывать сложную логику «ИЛИ» через сложение условий.
Можно ли использовать СУММПРОИЗВ с одним аргументом?
Да. =СУММПРОИЗВ(A1:A10) просто просуммирует значения диапазона, работая как обычная СУММ. Это удобно для унификации шаблонов формул.
Работает ли функция с вертикальными и горизонтальными массивами одновременно? Да, если их размеры совместимы для поэлементного умножения. Однако чаще всего используются одномерные диапазоны (столбцы или строки) одинаковой длины.