Мастер-класс по функции СУММПРОИЗВ для сложных расчетов
Функция СУММПРОИЗВ (англ. SUMPRODUCT) — это универсальный инструмент в Excel, который позволяет суммировать произведения массивов данных с применением любых логических условий. В отличие от стандартных функций суммирования, она не требует создания дополнительных столбцов для промежуточных вычислений и легко обрабатывает сложные критерии фильтрации («И», «ИЛИ»). Если вам нужно быстро посчитать выручку по конкретному товару в определенном городе или проанализировать продажи за период, эта функция станет оптимальным решением.
Главное преимущество: Функция работает с массивами напрямую, преобразуя логические значения ИСТИНА/ЛОЖЬ в 1 и 0, что позволяет гибко фильтровать данные внутри одной формулы.
Принцип работы и базовый синтаксис
Суть функции заключается в перемножении соответствующих элементов указанных диапазонов и последующем суммировании полученных результатов.
Базовая формула:
=СУММПРОИЗВ(массив1; [массив2]; ...)
Где каждый аргумент — это диапазон ячеек одинакового размера.
Простой пример без условий: Представим таблицу, где в столбце A указано количество товара, а в столбце B — его цена. Чтобы узнать общую стоимость всей партии:
=СУММПРОИЗВ(A2:A10; B2:B10)
Excel выполнит действие: (A2×B2) + (A3×B3) + ... + (A10×B10).
Использование одного условия
Чаще всего функцию применяют для выборочного суммирования. Допустим, у нас есть таблица продаж:
| Товар (A) | Кол-во (B) | Цена (C) | Город (D) |
|---|---|---|---|
| Яблоки | 10 | 50 | Москва |
| Апельсины | 7 | 60 | СПб |
| Яблоки | 5 | 50 | Москва |
| Груши | 8 | 70 | Москва |
Задача: Посчитать общую выручку только от продажи «Яблок».
Формула:
=СУММПРОИЗВ((A2:A5="Яблоки") * B2:B5 * C2:C5)
Логика работы:
- Выражение
(A2:A5="Яблоки")создает массив{1; 0; 1; 0}(где 1 — совпадение, 0 — нет). - Этот массив умножается на количество и цену. Строки с нулем обнуляют произведение, оставляя только нужные значения.
- Результат:
10×50 + 0 + 5×50 + 0 = 750.
Важно: При использовании условий диапазоны должны быть строго одного размера. Если столбец с товаром имеет 100 строк, а столбец с ценой — 99, формула вернет ошибку #ЗНАЧ!.
Работа с несколькими условиями (логическое «И»)
Функция идеально подходит для фильтрации по двум и более критериям. Условия просто перемножаются друг с другом.
Задача: Найти выручку по «Яблокам», проданным именно в «Москве».
Формула:
=СУММПРОИЗВ((A2:A5="Яблоки") * (D2:D5="Москва") * B2:B5 * C2:C5)
Здесь умножаются три логических массива и два числовых. Строка учитывается в сумме только если оба условия истинны (дают 1).
Числовые условия и сравнения
Вы можете использовать операторы сравнения (>, <, >=, <>) для работы с числами и датами.
Пример: Сумма продаж товаров, цена которых выше 55 рублей.
=СУММПРОИЗВ((C2:C5>55) * B2:B5 * C2:C5)
Пример с датами: Выручка за январь 2026 года (диапазон дат в столбце A, суммы в B).
=СУММПРОИЗВ((A2:A100>=ДАТА(2026;1;1)) * (A2:A100<=ДАТА(2026;1;31)) * B2:B100)
Реализация логики «ИЛИ»
Стандартные функции типа СУММЕСЛИМН работают только по принципу «И» (все условия должны выполняться). СУММПРОИЗВ позволяет реализовать логику «ИЛИ» через сложение условий.
Задача: Посчитать продажи «Яблок» ИЛИ «Груш».
Формула:
=СУММПРОИЗВ(((A2:A10="Яблоки") + (A2:A10="Груши")) * B2:B10 * C2:C10)
Знак плюса + между условиями означает, что строка будет учтена, если верно хотя бы одно из них.
Сравнение: СУММПРОИЗВ против СУММЕСЛИМН
Обе функции решают похожие задачи, но имеют разные сферы применения.
| Характеристика | СУММПРОИЗВ | СУММЕСЛИМН |
|---|---|---|
| Скорость | Медленнее на очень больших массивах (>50 тыс. строк) | Быстрее, оптимизирована для больших данных |
| Гибкость | Высокая: поддерживает математику внутри условий, логику «ИЛИ» | Низкая: только простые критерии «И» |
| Синтаксис | Требует понимания работы с массивами | Проще для новичков |
| Вычисляемые критерии | Поддерживает (например, >СРЗНАЧ(...)) | Ограничена |
Используйте СУММЕСЛИМН для простых отчетов на огромных таблицах ради производительности. Выбирайте СУММПРОИЗВ, когда нужны сложные вычисления внутри условий, логика «ИЛИ» или работа с закрытыми книгами.
Частые ошибки при написании формул
- Разная длина диапазонов. Все массивы в формуле должны начинаться и заканчиваться в одной строке.
- Отсутствие кавычек. Текстовые условия всегда пишутся в двойных кавычках:
"Текст". Числа и ссылки на ячейки — без кавычек. - Лишние пробелы в данных. Если в ячейке написано
"Яблоки "(с пробелом в конце), условие"Яблоки"не сработает. Используйте функциюСЖПРОБЕЛЫдля очистки данных. - Ошибка в логике «ИЛИ». Не используйте знак
+без скобок вокруг каждого условия. Правильно:((Условие1)+(Условие2)).
FAQ: Вопросы и ответы
Можно ли использовать СУММПРОИЗВ для подсчета количества строк?
Да. Если убрать из формулы столбец с суммируемым значением (например, цену), функция просто просуммирует единицы, соответствующие условиям.
=СУММПРОИЗВ((A2:A10="Яблоки")*(C2:C10>50)) вернет количество строк, где товар — яблоки и цена больше 50.
Почему формула возвращает 0, хотя данные есть? Проверьте формат данных. Часто числа сохранены как текст (в ячейке зеленый треугольник). Также проверьте наличие скрытых пробелов в текстовых условиях.
Работает ли функция с данными из других листов?
Да, СУММПРОИЗВ отлично работает со ссылками на другие листы той же книги, например: СУММПРОИЗВ((Лист2!A2:A10="X")*Лист2!B2:B10).