СУММПРОИЗВ

Что делает эта функция?

Эта функция использует как минимум два диапазона или массива, перемножая и суммируя их элементы.

Значения в первом диапазоне умножаются на соответствующие по порядку значения во втором и так далее.

Сумма всех таких произведений является результатом расчета.

Наиболее часто функция используется как аналог функции СУММЕСЛИ с множественными условиями.

1. Преимущество СУММПРОИЗВ перед сводными таблицами заключается в том, что в сводных таблицах фильтр для построения среза всегда точное соответствие (как в примере 1). А “фильтр по подписи”, если выводить фильтруемые значения в строки, позволяет фильтровать только по одному условию. СУММПРОИЗВ позволяет использовать все варианты как строкового сравнения (содержит, начинается с, заканчивается на, совпадает), так и численного (больше, меньше, равно, не равно, больше или равно, меньше или равно).
2. Второе преимущество СУММПРОИЗВ перед сводными таблицами в том, что один критерий фильтрации в сводных таблицах нельзя использовать дважды. СУММПРОИЗВ позволяет обращаться к одному и тому же столбцу многократно.
3. Преимущество СУММПРОИЗВ перед функцией СУММЕСЛИМН в том, что условная логика СУММЕСЛИМН – строгое выполнение всех условий, по типу “И”. Возможность использовать любые логические операции над массивами булевых значений дает полнейшую свободу действий по совмещению условий в любых логических комбинациях – И, ИЛИ, ЕСЛИ, НЕ.

Форматирование

Диапазоны должны быть однонаправленными, содержать одинаковое количество элементов и иметь одинаковую размерность по вертикали или горизонтали. Конкретные адреса диапазонов при этом не имеют значения.

Диапазоны могут иметь несколько столбцов и строк одновременно. Обычно используются вертикальные диапазоны шириной 1 столбец.

Синтаксис функции

=СУММПРОИЗВ(ДиапазонИлиМассив1;ДиапазонИлиМассив2;…)

Пример использования

Рассмотрим подробно сложный пример с 4 условиями.

Задача маркетолога – посчитать сумму по четырем условиям:

1. Дата начала недели – 13 апреля или позднее
2. В названии кампании присутствует слово Brand
3. При этом кампания заканчивается на BY (Белоруссия)
4. Или заканчивается на UA (Украина)

Строки, удовлетворяющие всем четырем условиям, выделены зеленым цветом. Как составить формулу, которая учла бы только их?

Формулу можно наблюдать на скриншоте в ячейке H9.

Фактически она является суммой произведения двух диапазонов – массива чисел D3:D34 и массива нулей и единиц. Рассмотрим ее поближе:

Массив нулей и единиц рождается произведением трех формул массива, третья из которых получается сложением двух других формул массива и применением функции ЗНАК.

В каждой строке указано одно из четырех условий, на основе которых эти формулы создаются:

(ЗНАЧЕН(ЛЕВСИМВ($A$3:$A$34;8))>=$H$2)

это выражение создает массив булевых значений ИСТИНА и ЛОЖЬ. Функция ЛЕВСИМВ($A$3:$A$34;8) извлекает из диапазона дат в столбце A первые 8 символов, обозначающие дату, а функция ЗНАЧЕН преобразует их в числовой формат, иначе сравнение с ячейкой H2 не будет происходить корректно, т.к. функция ЛЕВСИМВ – текстовая, и возвращает строку.

НЕ(ЕОШ(ПОИСК($H$4;$B$3:$B$34)))

– это выражение также создает массив булевых значений. Функция ПОИСК ищет внутри каждой ячейки столбца слово “BRAND”, если оно не найдено, возвращается ошибка, а если найдено – позиция его первого символа в строке. Функцией ЕОШ мы превращаем ошибки в единицы, а их отсутствия – в нули. И далее функцией отрицания НЕ получаем нужный нам массив.

Функций “Заканчивается на” в Excel нет, но можно делать простую проверку – если последние символы в строке совпадают со словом, что это, как не вхождение? Эту операцию и производит выражение

(ПРАВСИМВ($B$3:$B$34;ДЛСТР($J$2))=$J$2)

Тут нам помогают функции ДЛСТР и ПРАВСИМВ. Проверяя, совпадают ли последние 2 символа с ячейкой J2, мы также возвращаем массив булевых значений.

Четвертое условие аналогично третьему, как и его выражение:

(ПРАВСИМВ($B$3:$B$34;ДЛСТР($J$4))=$J$4)

Условная логика ИЛИ воспроизводится методом сложения двух булевых массивов. При этом они становятся числами. ИСТИНА+ЛОЖЬ (и наоборот) дают 1, ЛОЖЬ+ЛОЖЬ дают 0, ИСТИНА+ИСТИНА дают 2. Чтобы не исказить данные, мы используем функцию ЗНАК – она возвращает для положительных чисел 1, а для 0 – 0. Таким образом, мы получаем массив нулей и единиц на основе двух булевых массивов.

Произведение трех массивов дает нам единый массив нулей и единиц, умножая который на значения показов, кликов и скачиваний, мы получаем суммы отфильтрованных таким образом значений. Числа, где условия не соблюдены, “уничтожаются” нулями, а числа, где соблюдены – умножаются на 1 и складываются.

У наблюдательного читателя может возникнуть вопрос, почему мы не пользуемся функцией ИЛИ для объединения последних двух булевых массивов. Ответ: к сожалению, она не сочетает массивы попарно (как и функция И), а рассматривает каждую ячейку массива как равноправный элемент.

Именно поэтому используются арифметические функции.

• вместо И – умножение
• а вместо ИЛИ – сложение + функция ЗНАК.

Другие примеры с СУММПРОИЗВ:

Найти повторяющиеся значения в Excel