Загальна формула
=SUMPRODUCT(expression,range)
Резюме
Щоб відфільтрувати результати SUMPRODUCT за певними критеріями, ви можете застосувати прості логічні вирази безпосередньо до масивів у функції, замість того, щоб використовувати функцію IF. У наведеному прикладі формули в 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 - можливість фільтрувати дані за допомогою основних логічних виразів замість функції IF. Усередині SUMPRODUCT перший масив є логічним виразом для фільтрування за кольором "червоний":
--(color="red")
Це призводить до масиву або значень TRUE FALSE, які приводяться до одиниць та нулів за допомогою операції подвійного від’ємного (-). Результатом є такий масив:
(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 - це множення, а потім суми масивів. Оскільки ми працюємо з трьома масивами, ми можемо візуалізувати операцію, як показано в таблиці нижче, де стовпець результату є результатом множення масиву1 * масив2 * масив3 :
| масив1 | масив2 | масив3 | результат |
|---|---|---|---|
| 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)
Це працює, оскільки математична операція (множення) автоматично примушує значення TRUE і FALSE з перших двох виразів на одиниці та нулі.
