Splošna formula
=SUMPRODUCT(expression,range)
Povzetek
Če želite filtrirati rezultate SUMPRODUCT z določenimi merili, lahko uporabite preproste logične izraze neposredno na polja v funkciji, namesto da uporabite funkcijo IF. V prikazanem primeru so formule v H5: H7:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
kjer so opredeljeni naslednji imenovani obsegi:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Če se raje izognete imenovanim obsegom, uporabite zgoraj vnesene obsege kot absolutne reference. Logične izraze v H6 in H7 je mogoče kombinirati, kot je razloženo spodaj.
Pojasnilo
Ta primer ponazarja eno ključnih prednosti funkcije SUMPRODUCT - zmožnost filtriranja podatkov z osnovnimi logičnimi izrazi namesto s funkcijo IF. Znotraj SUMPRODUCT je prva matrika logični izraz za filtriranje po barvi "rdeča":
--(color="red")
Rezultat tega so vrednosti matrike ali TRUE FALSE, ki se z dvojno negativno (-) operacijo prisilijo v enote in ničle. Rezultat je ta matrika:
(1;0;1;0;0;0;1;0;0;0)
Upoštevajte, da matrika vsebuje 10 vrednosti, po eno za vsako vrstico. Ena označuje vrstico, kjer je barva "rdeča", nič pa vrstico s katero koli drugo barvo.
Nato imamo še dva niza: enega za količino in enega za ceno. Skupaj s temi rezultati iz prvega polja imamo:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Če razširimo polja, imamo:
=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))
Jedro vedenja podjetja SUMPRODUCT je množenje, nato seštevanje nizov. Ker delamo s tremi nizi, lahko vizualiziramo operacijo, kot je prikazano v spodnji tabeli, kjer je kolona rezultat rezultat pomnoži array1 * array2 * array3 :
| matrika1 | polje2 | array3 | rezultat |
|---|---|---|---|
| 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 |
Obvestilo array1 deluje kot filter - nič vrednosti tukaj "izniči" vrednosti v vrsticah, kjer barva ni "rdeča". Če rezultate vrnemo v SUMPRODUCT, imamo:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Kar vrne končni rezultat 480.
Dodajanje dodatnih meril
Merila lahko razširite z dodajanjem drugega logičnega izraza. Če želite na primer najti skupno prodajo, kjer je barva "rdeča" in stanje "TX", H6 vsebuje:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Opomba: SUMPRODUCT ne razlikuje med velikimi in malimi črkami.
Poenostavitev z enim nizom
Excel profesionalci bodo pogosto nekoliko poenostavili sintakso znotraj SUMPRODUCT, tako da bodo množili polja neposredno znotraj array1, kot je ta:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
To deluje, ker matematična operacija (množenje) samodejno prisili vrednosti TRUE in FALSE iz prvih dveh izrazov v eno in ničlo.
