Работа с функцией СУММПРОИЗВ на примерах.
В процессе работы нам часто приходится сталкиваться с необходимостью выполнения суммирования по одному или нескольким признаками. Чаще всего если признак для суммирования только один, то используют функцию СУММЕСЛИ, о которой мы рассказывали. Если условий для проверки несколько , то применяют функцию СУММЕСЛИМН. Данная функция хорошо справляется с работой, но имеет важный недостаток. Если различные условия проверяются в одном и том же диапазоне, то они взаимоисключаются. Ну не может значение в одной и той же ячейке быть разным одновременно.
В примере ниже происходит суммирование одного и того же диапазона. В первом случае это делает функция СУММ. Во втором работаем СУММЕСЛИМН. Обратите внимание, что второй случай приводит к нулевому результату, хотя оба слова находятся в указанном диапазоне.
Рисунок 1. Ошибка СУММЕСЛИМН
В качестве альтернативы применяется функция БДСУММ. Она позволяет отследить выполнение нескольких условий в одном диапазоне, но требует заранее заданного диапазона с критериями, что не всегда возможно.
Рисунок 1. Работа функции БДСУММ
В то же время существует функция, которая лишена недостатков СУММЕСЛИМН и не требует дополнительных построений для указания условий как функций БДСУММ. Эта функция, незаслуженно многими игнорируемая, позволяет обработать любую комбинацию условий в любых диапазонах для определения критериев суммирования и называется СУММПРОИЗВ.
Рассмотрим ее более подробно. Откроем справочную информацию, в которой написано буквально следующее:
«Функция СУММПРОИЗВ возвращает сумму произведений соответствующих диапазонов или массивов.»
Другими словами, надо указать два или более (до 255) диапазонов, а Excel перемножит ячейки, находящиеся на одинаковых позициях в этих диапазонах, после чего результат будет просуммирован.
В качестве примера приведем вычисление итоговой суммы по известным значениям цены и количества для каждого товара. Если применить функцию СУММ, то потребуется создания отдельной графы «Всего» для вывода суммы по каждой строке отдельно. Только после этого возможно суммирование. Функция СУММПРОИЗВ позволила сразу найти общую итоговую сумму без промежуточных вычислений.
Рисунок 2. Классическое применение СУММПРОИЗВ
Обычно на этом работа заканчивается. Однако если прочитать справку для этой функции более подробно, то можно обнаружить интересную вещь.
«Операция по умолчанию — умножение, но также возможны сложение, вычитание и деление. …
Выполнение других арифметических операций
Используйте СУММПРОИЗВ, как обычно, но замените запятые, разделяющие аргументы массива, арифметическими операторами (*, /, +, -). После выполнения всех операций результаты суммируются в обычном режиме.»
Оказывается, операцией умножения работа с ячейками диапазонов не ограничивается! Вы можете не только перемножать ячейки диапазонов, но и делить, вычислять разность между ними, суммировать. Но и это не самое главное, что не учитывают при работе с СУММПРОИЗВ. Самое важное – возможность сравнить значения диапазонов с указанными критерием:
Диапазон=условие
Рассмотрим указанную особенность на примерах.
Пример1. Заменяем функцию СУММЕСЛИ
Необходимо рассчитать общее количество для выбранного товара, например брюк в таблице, указанной ниже
Рисунок 3. Исходная таблица
Конечно, мы можем применить функцию СУММЕСЛИ. Однако тот же самый результат даст функция СУММПРОИЗВ! Применим формулу следующего вида:
=СУММПРОИЗВ((A2:A13=”брюки”)*B2:B13)
Обратите внимание – я намеренно не указываю в качестве критерия значение из ячейки для большей наглядности вычислений.
Результаты вычисления:
Рисунок 4.СУММЕСЛИ и СУММПРОИЗВ
Что же произошло при вычислении? Все очень просто. Вначале выполнилась операция сравнения, указанное в ее правой части. В результате все ячейки графы «Наименование», содержащие слово «Брюки» были заменены на единицы, а остальные – не совпадающие с этим значением – в нули. Виртуально таблица приняла следующий вид (хотя конечно внешне все осталось как есть):
Рисунок 5. Замена значений наименований
Далее начали работать правила математики. При умножении любого числа на ноль результатом будет ноль, при умножении любого числа на единицу остается то же число. В итоге все значения графы «Количество», не соответствующие позициям ячеек со словом брюки обнулились! После этого была выполнена последняя операция – сложение полученных в результате проведенной выборки значений графы «Количество».
Пример 2. Вычисли сумму при отсутствии предварительного расчета.
О чем речь? Предположим, нам надо найти общую итоговую сумму для тех же брюк. Да, конечно, с этим неплохо работает СУММЕСЛИ. Однако фишка СУММПРОИЗВ в том, что колонка «Итого» ей не очень и нужна. Собственно формула:
=СУММПРОИЗВ((A2:A13=”брюки”)*B2:B13*C2:C13)
Рисунок 6 Замена диапазона формулой в СУММПРОИЗВ
В дальнейших примерах заменим явные адреса каждой колонки таблицы на соответствующие имена. В частности, предыдущая формула тогда примет вид:
=СУММПРОИЗВ((наименование=”Брюки”)*кол_во*стоимость)
Более подробно о именах, правилах их назначения и применении в вычислениях смотрите в наших материалах.
Рисунок 7. Использование имен в формуле
Пример 3. Выполним суммирование при учете нескольких условий, отслеживаемых в разных диапазонах.
Поставим задачу. Необходимо найти общее количество для брюк, нао при этом стоимость брюк не должна быть меньше 5000. В этом случае имеется два условия – для наименования и цены. Применим предыдущую формулу, но теперь укажем два диапазона для проверки.
=СУММПРОИЗВ((наименование=”Брюки”)*(стоимость>5000)*кол_во)
Рисунок 8. Проверка условий по нескольким диапазонам
Значения по количеству, указанные красным цветов шрифта, исключены из расчета, так как хотя название товара совпадает с критерием, но стоимость товара ниже указанной.
Пример 4. Используем СУММЕСЛИ при соблюдении нескольких критериев, отслеживаемых по одному диапазону поверки.
Предположим, теперь задача – найти общее количество для геля и лосьона. Если бы мы применяли СУММЕСЛИ, то пришлось бы выполнить два расчета, а результаты сложить ( это если не учитывать возможности массивов, о чем расскажем позже). В данном же случае с задачей прекрасно справилась СУММПОРИЗВ, только теперь для проверки используем не умножение , а сложение.
=СУММПРОИЗВ(((наименование=”Лосьон”)+(наименование=”Гель”))*кол_во)
Рисунок 9. Проверка условий по одному диапазону.
Запомните главное в последних двух примерах. Если надо проверить условия в нескольких диапазонах, применяем логическую операцию «И», заменяемую умножением результатов сравнения. Если надо применить проверку условий в одном диапазоне, используем логическую операцию «ИЛИ», которую заменяем на сложение результатов сложения.
Естественно, оба варианта можно применять вместе. В примере ниже найдем общее количество геля и лосьона с учетом того, что стоимость не должна превысить 4500.
Рисунок 10 Проверка условий по одному и нескольким диапазонам в СУММПРОИЗВ
Пример 5. Заменим функцию ВПР при работе по нескольким условиям.
Да, это тоже возможно. Предположим, у нас есть штатное расписание, котором указаны тарифные ставки по разным отделам и должностям. Естественно, что в каждом отделе эти ставки различаются в зависимости от должно. Так же очевидно , что в разных отделах есть одинаковые должности с разными ставками.
Рисунок 11. Штатное расписание
Обычно, с учетом того, что в такие документы изменения, в том числе добавление промежуточных расчетов, вносить нельзя, применяют формулу массива с формированием виртуального диапазона
{=ВПР(D8;ЕСЛИ(ОТДЕЛ=Табель2020!C8;штатное_расписание_2020!$C$15:$I$92);7;0)}
Рисунок 12. Штатное расписание
Ту же самую задаче можно решит с помощью СУММПРОИЗВ. Применим формулу из третьего примера:
=СУММПРОИЗВ((ОТДЕЛ=Табель2020!C8)*(ДОЛЖНОСТЬ=Табель2020!D8)*штатное_расписание_2020!I15:I92)
Как видите, сама формула горазда проще, несмотря на длину, и дает тот же результат.
Рисунок 13. Заменяем ВПР
Подведем итог.
НА нашем занятии мы ознакомились с различными вариантами применения функции СУММПРОИЗ на различных практических примерах. Было наглядно рассмотрено ее применение в различных ситуациях. Вы можете самостоятельно повторить применение примеры, скачав учетный файл СУММПРОИЗВ.
Функция не с успехом заменила другие, более часто применяемые варианты применяемых формул, но и превзошла их по простоте и удобству работы. Более того. Функция СУММПРОИЗВ с успехом извлекает данные из закрытых книг, тогда как та же функция СУММЕСЛИ выдает ошибку, если книга, по значениям которой выполнялся расчет, будет закрыта.
На этом наш урок подошел к концу. Желаем всем удачного дня, плодотворных начинаний и конечно хорошего настроения.
