Обща формула
=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 е да се умножава, след което да се сумират масиви. Тъй като работим с три масива, можем да визуализираме операцията, както е показано в таблицата по-долу, където колоната с резултати е резултат от умножаване на array1 * array2 * array3 :
| масив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 от първите два израза в единици и нули.
