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

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

Пример №1: Проверка возраста

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

1. Выделяем ячейку для записи результата и вводим формулу СУММПРОИЗВ. В качестве первого аргумента указываем диапазон, в котором будет проверяться условие. В нашем случае возраст указан в столбце «С». Сразу же прописываем условие проверки — «<18».
2. Теперь нужно завершить конструкцию, добавив умножение на 1. Это делается для того, чтобы формула считала число ячеек, удовлетворяющих заданному условию.
3. В результате формула вернет число пользователей, возраст которых меньше 18. Если выражаться более строго, то вычислительная конструкция подсчитывает количество ячеек, в которых записаны числа меньше 18.

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

Пример №2: Подсчет ячеек с определенным числом символов

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

1. Выделяем ячейку и вводим СУММПРОИЗВ. Вместо обычного аргумента в виде диапазона сначала добавляем формулу Ч, а затем в открытых скобках функцию ДЛСТР.
2. Теперь внутри скобок прописываем условие для проверки — диапазон и критерий. В нашем случае диапазон А2:А10, а критерий >30. Закрываем скобки и нажимаем кнопку «Enter».
3. В результате конструкция вернет число ячеек, удовлетворяющих заданному условию. Функция сумма произведений определяет их количество, а за проверку отвечает ДЛСТР. Вспомогательная формула Ч нужна для преобразования результата проверки в число.

Пример №3: Подсчет с проверкой значения

В следующем примере рассмотрим, как рассчитывается сумма произведения чисел в ячейках, соответствующих определенному текстовому критерию. В нашем случае это список товаров, принадлежащих определенным категориям. Для примера определим, сколько продано и какая прибыль от продажи товаров, соответствующих категории «Овощи».

1. Выделяем ячейку для записи результата. Затем вводим формулу СУММПРОИЗВ или SUMPRODUCT, если используется Excel на английском. В качестве первого аргумента указываем диапазон с категориями товаров и условие — принадлежность к группе «Овощи».
2. Далее закрываем скобку, ставим оператор «*», который в данном случае выполняет функцию амперсанда или оператора «И». После этого вводим второй аргумент — диапазон ячеек, в которых выполняется подсчет значений. В нашем случае нужно узнать количество проданных овощей, поэтому берем диапазон из соответствующего столбца.
3. В результате этого мы определим общее количество проданных овощей. С использованием этой конструкции можно вычислить и прибыль от продажи этой группы товаров. Разница только в том, что в качестве второго аргумента будет указан другой диапазон ячеек — столбец «Прибыль».
4. В результате мы определим произведение в ячейках, соответствующих заданному параметру. Это очень удобно для выборки данных, так как в этом случае не придется использовать функции поиска ВПР или ГПР.

Пример №4: Проверка двух условий

Немного усложним предыдущую формулу и выполним поиск, подсчет с несколькими условиями. На первый взгляд может показаться, что это трудно. На самом же деле, определив принцип построение такой конструкции, можно добавлять в нее несколько критериев без каких-либо трудностей.

В данном примере задача будет состоять в том, чтобы рассчитать количество проданных овощей и прибыль от продажи определенного товара при условии, что его стоимость выше 100 рублей за 1 кг. Рассмотрим, как выполнить вычисление с учетом двух критериев:

1. Вводим формулу СУММПРОИЗВ. В качестве первого аргумента указываем условие для проверки принадлежности к заданной категории товаров.
2. Далее ставим оператор «*» и прописываем в скобках второе условие — стоимость выше 100.
3. Третий аргумент — диапазон ячеек, значение в которых нужно просуммировать в том случае, если выполняются указанные условия. В нашем случае это значения, которые содержатся в столбце «C».
4. Аналогичным образом можно посчитать и прибыль по двум условиям. Разница будет заключаться только в том, что в конце конструкции указывается другой диапазон ячеек.

В результате всего этого нам удалось получить сумму значений из ячеек, удовлетворяющих заданным условиям. Надо отметить, что можно добавлять большое количество критериев для проверки — до 255.

Заключение

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

Видео по теме