Мастер-класс по функции СУММПРОИЗВ в Excel

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

Базовый синтаксис и простой расчет

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

Синтаксис функции выглядит так: =СУММПРОИЗВ(массив1; [массив2]; [массив3]; ...)

Пример расчета общей суммы заказа

Представим таблицу:

• Столбец A (A2:A10): Количество товаров.
• Столбец B (B2:B10): Цена за единицу.

Чтобы узнать общую сумму всех товаров, введите формулу: =СУММПРОИЗВ(A2:A10; B2:B10)

Как это работает внутри:

1. Excel берет A2 и умножает на B2.
2. Берет A3 и умножает на B3.
3. Продолжает до последней строки (A10 * B10).
4. Складывает все полученные произведения.

Использование с условиями (замена СУММЕСЛИМН)

Одно из самых мощных свойств СУММПРОИЗВ — возможность выполнять суммирование по нескольким условиям без использования функций семейства СУММЕСЛИ (SUMIF). Это особенно полезно, когда нужно применить логику «И» (все условия должны выполняться одновременно) или сложные математические операции над отфильтрованными данными.

Для работы с условиями мы используем логические выражения, которые возвращают ИСТИНА (1) или ЛОЖЬ (0). При умножении на них массива данных, ненужные строки обнуляются.

Пример 1: Сумма по одному критерию

Допустим, нужно посчитать общую выручку только по товарам категории «Фрукты».

• Столбец A: Категория.
• Столбец B: Количество.
• Столбец C: Цена.

Формула: =СУММПРОИЗВ((A2:A100="Фрукты")*B2:B100*C2:C100)

Логика работы:

1. (A2:A100="Фрукты") создает массив из единиц и нулей (1, если условие верно, 0 если нет).
2. Этот массив умножается на Количество и Цену.
3. Строки, где категория не «Фрукты», умножаются на 0 и исчезают из итоговой суммы.

Пример 2: Несколько условий (логическое «И»)

Нужно найти сумму продаж менеджера «Иванов» за регион «Москва».

• Столбец A: Менеджер.
• Столбец B: Регион.
• Столбец C: Сумма продажи.

Формула: =СУММПРОИЗВ((A2:A100="Иванов")*(B2:B100="Москва")*C2:C100)

Здесь оба условия заключены в скобки и перемножены между собой. Строка учтется в сумме только если оба условия истинны (1 * 1 = 1). Если хотя бы одно ложно (1 * 0 = 0), произведение станет нулем.

Продвинутые техники: ИЛИ и сложные вычисления

Функция позволяет реализовывать логику «ИЛИ» и выполнять вычисления прямо внутри формулы.

Логика «ИЛИ» (сложение условий)

Если нужно посчитать сумму продаж для категорий «Фрукты» ИЛИ «Овощи», условия складываются через знак плюс +:

=СУММПРОИЗВ(((A2:A100="Фрукты")+(A2:A100="Овощи"))*B2:B100*C2:C100)

Здесь, если ячейка содержит «Фрукты» (1) или «Овощи» (1), сумма будет больше 0, и строка учтется.

Вычисления внутри формулы

Вы можете не просто суммировать готовые числа, а сначала произвести с ними действия. Например, посчитать сумму с учетом НДС 20%, который хранится отдельно или задан константой:

=СУММПРОИЗВ(A2:A10; B2:B10 * 1,2)

В этом примере каждый элемент второго массива (цена) сразу умножается на 1.2 перед перемножением с количеством.

Таблица: Сравнение подходов к суммированию

Частые ошибки и способы их устранения

Даже опытные пользователи часто сталкиваются с типичными проблемами при работе с этой функцией.

1. Ошибка #ЗНАЧ! (#VALUE!)

   • Причина: Диапазоны имеют разную длину. Например, первый аргумент A1:A10, а второй B1:B11.
   • Решение: Проверьте и выровняйте все диапазоны. Они должны начинаться и заканчиваться на одних и тех же строках.

2. Неверный результат (ноль или слишком маленькое число)

   • Причина: В числовых диапазонах встречаются текстовые значения (например, пробелы, символы валюты, записанные как текст). СУММПРОИЗВ считает текст равным 0.
   • Решение: Используйте «Текст по столбцам» или функцию ЗНАЧЕН, чтобы убедиться, что данные являются числами.

3. Ошибка в логике условий

   • Причина: Использование точки с запятой вместо умножения для разделения условий: СУММПРОИЗВ((A1="X"); (B1="Y"); C1).
   • Решение: Уберите лишние точки с запятой между условиями и соедините их звездочкой: СУММПРОИЗВ((A1="X")*(B1="Y")*C1).

FAQ

В чем разница между СУММПРОИЗВ и обычным умножением? Обычное умножение (=A1*B1) работает только с одной парой ячеек. Чтобы получить сумму по всему столбцу, пришлось бы создавать формулу для каждой строки и потом суммировать их. СУММПРОИЗВ делает это одной формулой для всего диапазона.

Можно ли использовать СУММПРОИЗВ с датами? Да. Даты в Excel хранятся как числа, поэтому вы можете умножать их на коэффициенты или суммировать с условиями по датам (например, продажи за январь).

Работает ли функция в старых версиях Excel? Да, СУММПРОИЗВ доступна во всех версиях Excel, начиная с самых ранних, и является стандартной функцией, не требующей специального включения массивов (как Ctrl+Shift+Enter в старых версиях для обычных формул массива).

Как игнорировать ошибки в исходных данных? Сама по себе функция не игнорирует ошибки типа #Н/Д в диапазонах. Если в исходных данных есть ошибки, формула вернет ошибку. В таких случаях данные нужно предварительно очистить или обернуть диапазон в функцию ЕСЛИОШИБКА (что потребует ввода как формулы массива в старых версиях).