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

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

Принципы работы СУММПРОИЗВ с несколькими условиями в Excel

Синтаксис функции СУММПРОИЗВ - следующий:

Первый массив является обязательным. Значения должны быть числовыми, текстовые значения формула посчитает как 0. Массивы 2,3 и так далее являются дополнительными необязательными аргументами и используются в зависимости от постановки задачи. Данная функция по ячейкам или по элементам перемножает несколько массивов и суммирует получившиеся произведения. Самый обычный и простой пример использования функции СУММПРОИЗВ – получить общую стоимость, перемножив цену и количество товаров или услуг. В столбце "В" у нас имеются цены, а в столбце "С" – количество товаров. Нам нужно узнать общую стоимость реализации товаров. В ячейке Е4 пишем формулу:

СУММПРОИЗВ поочередно умножила соответствующие значения из двух массивов (450*3; 340*6 и так далее) и суммировала результаты. Функция СУММПРОИЗВ поддерживает работу с массивами, это означает, что можно использовать логику формул массива, используя СУММПРОИЗВ. Кстати, если помните, для ввода формул массива всегда обязательно следует использовать сочетание клавиш Ctrl+Shift+Enter. В нашем случае надобности в этом нет, поскольку СУММПРОИЗВ работает по логике формул массива по определению. В следующем примере у нас есть таблица, которая содержит информацию о компаниях, регионах их деятельности, плановых продажах и фактических продажах. Наша задача – узнать, сколько компаний выполнили план по реализации товаров или услуг. Эту задачу можно решить и иным способом, например, добавить столбец, в котором сосчитать процент выполнения плана, затем применить СЧЕТЕСЛИ или СЧЕТЕСЛИМН. И это хороший и правильный способ, Однако!!!, можно выполнить задачу проще и быстрее. В ячейке Е23 пишем формулу:

Таким образом, условие D13:D20>=C13:C20 будет по очереди применяться к паре значений по ряду, затем это сравнение будет возвращать логическое значение ложь или истину. Для того чтобы трансформировать ложь и истину в числовое значение, умножаем содержащееся внутри СУММПРОИЗВ условие на 1. Ложь*1=0, истина*1=1. Затем все полученные 1 суммируются:

В предыдущем примере мы использовали одно условие, но при необходимости можно добавлять дополнительные условия. Например, нам нужно найти сколько компаний выполнили план в Северной Европе. Для этого нам следует к имеющийся формуле добавить еще одно условие – диапазон В13:В20, ячейки которого содержат текст «Северная Европа»:

Текст обязательно вставляем в кавычки. Формула нашла компании, которые выполнили план, затем сопоставила найденные результаты с вторым условием, оставила только те, которые соответствуют и возвратила их количество:



Суммирование промежуточных итогов в Excel по условию

Теперь допустим нам нужно сосчитать не количество компаний, а общую сумму фактических продаж компаний, соответствующих заданным условиям промежуточных итогов. Для этого нам нужно, чтобы умножение логических результатов выполнялось не на 1, а на соответствующий объем продаж. Заменяем число 1 на диапазон, который содержит такие значения:

Теперь наша ИСТИНА будет умножаться на соответствующую величину продаж, а ЛОЖЬ при умножении будет возвращать ноль. Функции, работающие с условиями, сопровождаются логическими операторами И, ИЛИ, НЕ и так далее. Если посмотреть на нашу формулу, два заданных условия связаны между собой логическим оператором И, то есть, выполняется поиск компаний, в которых и выполнен план, и расположение – Северная Европа. Что, если нам нужно связать условия логическим оператором ИЛИ? Допустим, нам нужно просуммировать сумму компаний из регионов Северная и Центральная Европа. Тогда между двумя условиями нужно вместо умножения поставить знак «плюс»:

Выборка данных из диапазона по нескольким условиям в Excel

У нас есть таблица с данными о сотрудниках и их баллах, полученные за тестирование навыков. Нам нужно выбрать среди сотрудников тех, чьи результаты оказались лучше остальных. Первым делом нужно посчитать среднее значение. Можно сделать это через функцию СРЗНАЧ и это будет правильно:

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

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

Теперь заново внимательно смотрим результаты – у нас переопределялся лидер. В прошлом примере Джек занимал первое место, теперь у него самый низкий результат. Мария из второго места ушла на 3, а лидером оказался Питер.

Работа функции СУММПРОИЗВ с текстовыми значениями в Excel

Еще один простой, но работающий пример использования:

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

Скачать примеры СУММПРОИЗВ с несколькими условиями в Excel

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