Генеричка формула
=SUMPRODUCT(expression,range)
Резиме
Да бисте филтрирали резултате СУМПРОДУЦТ-а са одређеним критеријумима, можете применити једноставне логичке изразе директно на низове у функцији, уместо да користите функцију ИФ. У приказаном примеру, формуле у Х5: Х7 су:
=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
Ако радије избегавате именоване опсеге, користите горе наведене уносе као апсолутне референце. Логички изрази у Х6 и Х7 могу се комбиновати, као што је објашњено у наставку.
Објашњење
Овај пример илуструје једну од кључних снага функције СУМПРОДУЦТ - могућност филтрирања података помоћу основних логичких израза уместо функције ИФ. Унутар СУМПРОДУЦТ-а, први низ је логичан израз који се филтрира по боји "црвена":
--(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))
Основно понашање СУМПРОДУЦТ-а је множење, а затим сумирање низова. Будући да радимо са три низа, можемо да визуализујемо операцију као што је приказано у доњој табели, где је колона резултата резултат множења арраи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 |
Обавештење арраи1 ради као филтер - овде нулте вредности „избацују“ вредности у редовима где боја није „црвена“. Враћајући резултате у СУМПРОДУЦТ, имамо:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Што даје коначни резултат од 480.
Додавање додатних критеријума
Критеријуме можете проширити додавањем другог логичког израза. На пример, да бисте пронашли укупну продају где је боја „Црвена“, а држава „ТКС“, Х6 садржи:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Напомена: СУМПРОДУЦТ не разликује велика и мала слова.
Поједностављивање са једним низом
Екцел професионалци ће често мало поједноставити синтаксу унутар СУМПРОДУЦТ-а множењем низова директно унутар поља1 овако:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
То ради јер математичка операција (множење) аутоматски присиљава вредности ТРУЕ и ФАЛСЕ из прва два израза у јединице и нуле.
