Generička formula
=SUMPRODUCT(expression,range)
Sažetak
Da biste filtrirali rezultate SUMPRODUCT-a s određenim kriterijima, možete primijeniti jednostavne logičke izraze izravno na nizove u funkciji, umjesto da koristite funkciju IF. U prikazanom primjeru formule u H5: H7 su:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
gdje su definirani sljedeći imenovani rasponi:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Ako radije izbjegavate imenovane raspone, koristite gore unesene raspone kao apsolutne reference. Logični izrazi u H6 i H7 mogu se kombinirati, kako je objašnjeno u nastavku.
Obrazloženje
Ovaj primjer ilustrira jednu od ključnih prednosti funkcije SUMPRODUCT - sposobnost filtriranja podataka s osnovnim logičkim izrazima umjesto funkcije IF. Unutar SUMPRODUCT-a, prvi je niz logičan izraz koji se filtrira na boju "crvena":
--(color="red")
To rezultira nizom ili TRUE FALSE vrijednostima, koje se prisiljavaju na jedinice i nule operacijom dvostrukog negativnog (-). Rezultat je ovaj niz:
(1;0;1;0;0;0;1;0;0;0)
Primijetite da niz sadrži 10 vrijednosti, po jednu za svaki redak. Jedan označava red u kojem je boja "crvena", a nula označava red s bilo kojom drugom bojom.
Dalje, imamo još dva polja: jedan za količinu i jedan za cijenu. Zajedno s ovim rezultatima iz prvog niza, imamo:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Proširujući nizove, 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))
Osnovno ponašanje SUMPRODUCT-a je množenje, a zatim zbrajanje nizova. Budući da radimo s tri polja, možemo vizualizirati operaciju kao što je prikazano u tablici u nastavku, gdje je kolona rezultat je rezultat pomnoži polje1 * array2 * array3 :
| niz1 | niz2 | niz3 | proizlaziti |
|---|---|---|---|
| 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 |
Obavijest array1 radi kao filtar - ovdje nula vrijednosti "poništavaju" vrijednosti u redovima gdje boja nije "crvena". Vraćajući rezultate natrag u SUMPRODUCT, imamo:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Što daje konačni rezultat od 480.
Dodavanje dodatnih kriterija
Kriterije možete proširiti dodavanjem drugog logičkog izraza. Na primjer, da biste pronašli ukupnu prodaju gdje je boja "Crvena", a država "TX", H6 sadrži:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Napomena: SUMPRODUCT ne razlikuje velika i mala slova.
Pojednostavljivanje s jednim nizom
Excel profesionalci često će pojednostavniti sintaksu unutar SUMPRODUCT-a množenjem nizova izravno unutar polja1 poput ovog:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
To djeluje jer matematička operacija (množenje) automatski prisiljava vrijednosti TRUE i FALSE iz prva dva izraza u jedinice i nule.
