Общая формула
=SUMPRODUCT(expression,range)
Резюме
Чтобы отфильтровать результаты СУММПРОИЗВ по определенным критериям, вы можете применять простые логические выражения непосредственно к массивам в функции, вместо использования функции ЕСЛИ. В показанном примере формулы в H5: H7:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
где определены следующие именованные диапазоны:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Если вы предпочитаете избегать именованных диапазонов, используйте указанные выше диапазоны как абсолютные ссылки. Логические выражения в H6 и H7 можно комбинировать, как описано ниже.
Объяснение
Этот пример иллюстрирует одну из сильных сторон функции СУММПРОИЗВ - возможность фильтровать данные с помощью основных логических выражений вместо функции ЕСЛИ. Внутри SUMPRODUCT первый массив - это логическое выражение для фильтрации по "красному" цвету:
--(color="red")
Это приводит к массиву или значениям ИСТИНА ЛОЖЬ, которые переводятся в единицы и нули с помощью операции двойного отрицательного (-). Результатом является такой массив:
(1;0;1;0;0;0;1;0;0;0)
Обратите внимание, что массив содержит 10 значений, по одному для каждой строки. Единица обозначает строку с красным цветом, а ноль обозначает строку с любым другим цветом.
Далее у нас есть еще два массива: один для количества и один для цены. Вместе с результатами первого массива у нас есть:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Раскладывая массивы, получаем:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
Основное поведение SUMPRODUCT - умножение, а затем суммирование массивов. Поскольку мы работаем с тремя массивами, мы можем визуализировать операцию, как показано в таблице ниже, где столбец результата является результатом умножения array1 * array2 * array3 :
| array1 | array2 | array3 | результат |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Обратите внимание, что array1 работает как фильтр - нулевые значения здесь «обнуляют» значения в строках, цвет которых не является «красным». Возвращая результаты в SUMPRODUCT, мы получаем:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Что возвращает окончательный результат 480.
Добавление дополнительных критериев
Вы можете расширить критерии, добавив другое логическое выражение. Например, чтобы найти общий объем продаж, когда цвет «Красный», а состояние «TX», H6 содержит:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Примечание. SUMPRODUCT не чувствителен к регистру.
Упрощение с помощью одного массива
Профессионалы Excel часто упрощают синтаксис внутри SUMPRODUCT, умножая массивы непосредственно внутри array1 следующим образом:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Это работает, потому что математическая операция (умножение) автоматически переводит значения ИСТИНА и ЛОЖЬ из первых двух выражений в единицы и нули.
