Általános képlet
=SUMPRODUCT(expression,range)
Összegzés
A SUMPRODUCT eredményeinek meghatározott feltételekkel történő szűréséhez az IF függvény használata helyett egyszerű logikai kifejezéseket alkalmazhat közvetlenül a függvény tömbjeire. A bemutatott példában a H5: H7 képletek a következők:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
ahol a következő megnevezett tartományok vannak meghatározva:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Ha inkább el szeretné kerülni a megnevezett tartományokat, használja a fenti beírt tartományokat abszolút referenciaként. A H6 és H7 logikai kifejezései kombinálhatók, az alábbiakban leírtak szerint.
Magyarázat
Ez a példa a SUMPRODUCT függvény egyik legfontosabb erősségét szemlélteti - az IF funkció helyett az alapvető logikai kifejezésekkel történő adatok szűrését. A SUMPRODUCT belsejében az első tömb egy logikai kifejezés, amely a "vörös" színre szűrhető:
--(color="red")
Ez egy tömböt vagy IGAZ HAMIS értéket eredményez, amelyeket kettős negatív (-) művelettel egerekre és nullákra kényszerítenek. Az eredmény ez a tömb:
(1;0;1;0;0;0;1;0;0;0)
Figyelje meg, hogy a tömb 10 értéket tartalmaz, minden sorhoz egyet. Az egyik azt a sort jelöli, ahol a szín "piros", a nulla pedig egy sort minden más színnel.
Ezután még két tömbünk van: egy a mennyiségre és egy az árra. Az első tömb ezen eredményeivel együtt:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
A tömbök kibővítésével rendelkezünk:
=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))
A SUMPRODUCT alapvető viselkedése a tömbök szorzása, majd összeadása. Mivel dolgozunk három tömböt tudunk elképzelni a működését, amint azt az alábbi táblázat mutatja, ahol az eredmény oszlop a szorzata tömb1 * tömb2 * array3 :
| tömb1 | tömb2 | tömb3 | eredmény |
|---|---|---|---|
| 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 |
A Notice array1 szűrőként működik - a nulla érték itt "nullázza" az értékeket azokban a sorokban, ahol a szín nem "piros". Az eredményeket visszatéve a SUMPRODUCT programba:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Ami 480-as végeredményt ad vissza.
További kritériumok hozzáadása
A kritériumokat kibővítheti egy másik logikai kifejezés hozzáadásával. Például, hogy megtalálja a teljes eladást, ahol a szín "Piros" és az állam "TX", a H6 a következőket tartalmazza:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Megjegyzés: A SUMPRODUCT nem különbözteti meg a kis- és nagybetűket.
Egyszerűsítés egyetlen tömb
Excel profik gyakran egyszerűsítik a szintaxis belsejében SUMPRODUCT egy kicsit megszorozzuk tömbök közvetlenül belül tomb1 , mint ez:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Ez azért működik, mert a matematikai művelet (szorzás) az első két kifejezésből az IGAZ és HAMIS értékeket automatikusan egysé és nullává kényszeríti.
