Функция СУММПРОИЗВ незаслуженно забыта многими пользователями, а ведь до появления динамических массивов с ее помощью можно было решать довольно сложные задачи. Это означает, что и сейчас в Excel 2019 и более старых версиях данная функция все еще актуальна. К тому же в некоторых ситуациях она может заменить формулы массива, которые игнорируются многими пользователями из-за довольно сложной для понимания логики их работы.
Итак, функция СУММПРОИЗВ предназначена для перемножения значений в указанных диапазонах с последующим суммированием полученного результата.
Например, есть два столбца со значениями. Первый столбец будет являться первым аргументом функции, а второй вторым.
В результате функция попарно перемножит значения из соответствующих строк, а затем просуммирует эти произведения.
Такое действие на первый взгляд кажется не очень полезным, но давайте рассмотрим применение этой функции на примерах.
Кассовый чек
Классическая задача - кассовый чек. Есть таблица с товарами, их количеством и ценой. Необходимо вычислить общую сумму заказа.
Эта задача решается в том числе и формулой массива, но функция СУММПРОИЗВ как будто под нее создавалась - выбираем диапазон значений столбца Количество, и затем значения столбца Цена. На выходе получаем сумму заказа.
Подсчет по критерию
Еще одним вариантом использования функции СУММПРОИЗВ является подсчет данных по определенному критерию. С такой задачей справляются функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН, но вот если в критерии нужно задействовать логические операторы ИЛИ или И, то простой формулой обойтись не выйдет, так как эти функции имеют ограничения. Например, давайте подсчитаем количество заказов от определенного покупателя (Пятерочка), задействовав функцию СЧЁТЕСЛИ.
А если нужно подсчитать количество заказов от двух покупателей, то как быть? Первое, что приходит на ум - это воспользоваться функцией СЧЁТЕСЛИМН, но вот только она не сможет дважды обработать один и тот же диапазон…
Придется составлять формулу из двух функций СЧЁТЕСЛИ и суммировать их результаты.
А вот функция СУММПРОИЗВ легко решит эту задачу. Для этого придется воспользоваться следующей формулой.
В ней сначала ищем первого покупателя в диапазоне, а затем второго. При этом не забываем отдельные условия заключать в круглые скобки. Это важно!
В данном случае знак плюс является логическим оператором ИЛИ, ведь мы ищем или одного покупателя, или второго. Очень подробно о нюансах логики в Эксель я рассказал в отдельной статье, поэтому не буду подробно останавливаться на этой теме. Упомяну лишь, что логический оператор И в данном случае заменяется знаком умножения.
Например, нам нужно найти, сколько раз определенный заказчик (Пятерочка) покупал конкретный товар (Яблоки). Для этого вместо плюса ставим умножение и тогда будет производиться поиск только сочетания «покупатель-товар».
Суммирование по критерию
Аналогично обстоит дело и с суммированием. Для суммирования данных по критерию в Эксель есть функции СУММЕСЛИ и СУММЕСЛИМН, но также как и в ранее рассмотренной ситуации с их помощью нельзя подсчитать, например, сумму заказа по двум покупателям. Точнее сделать это можно, но придется задействовать формулу из двух одинаковый функций с разными критериями.
В этом случае функция СУММПРОИЗВ будет также полезна. По аналогии находим нужных покупателей в диапазоне (Пятерочка и Перекресток), используя логическую связку ИЛИ в виде плюса, а затем это условное выражение умножаем на значения столбца со стоимостью заказа.
Можно еще усложнить задачу и подсчитать общую сумму заказа конкретного товара (Яблоки) двумя покупателями (Пятерочка и Магнит). Для этого нужно будет по аналогии добавить еще одно условие - товар. Заказчики связываем логическим ИЛИ, а товар - логическим И. Затем умножаем результат на стоимость заказа.
В рассмотренных примерах мы делали выборку по покупателям или товарам, но, используя обычные операторы сравнения, точно также вы можете находить сумму или количество значений, которые будут больше или меньше вашего критерия. Поэтому функция СУММПРОИЗВ в ряде ситуация может быть очень мощным инструментов, значительно упрощающим ваши формулы.
Больше информации по работе в Excel в моих курсах.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм