Generisk formel
=SUMPRODUCT(expression,range)
Sammendrag
For å filtrere resultatene av SUMPRODUCT med spesifikke kriterier, kan du bruke enkle logiske uttrykk direkte på matriser i funksjonen, i stedet for å bruke IF-funksjonen. I eksemplet vist er formlene i H5: H7:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
der følgende navngitte områder er definert:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Hvis du heller vil unngå navngitte områder, kan du bruke områdene ovenfor som er angitt som absolutte referanser. De logiske uttrykkene i H6 og H7 kan kombineres, som forklart nedenfor.
Forklaring
Dette eksemplet illustrerer en av nøkkelstyrkene til SUMPRODUCT-funksjonen - muligheten til å filtrere data med grunnleggende logiske uttrykk i stedet for IF-funksjonen. Inne i SUMPRODUCT er den første matrisen et logisk uttrykk for å filtrere på fargen "rød":
--(color="red")
Dette resulterer i en matrise eller SANNE FALSE verdier, som blir tvunget til ener og nuller med den doble negative (-) operasjonen. Resultatet er denne matrisen:
(1;0;1;0;0;0;1;0;0;0)
Legg merke til at matrisen inneholder 10 verdier, en for hver rad. En indikerer en rad der fargen er "rød" og en null indikerer en rad med hvilken som helst annen farge.
Deretter har vi to matriser til: en for mengde og en for pris. Sammen med disse resultatene fra den første matrisen har vi:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Utvide matriser har vi:
=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))
SUMPRODUCTs kjerneatferd er å formere seg, og deretter summere matriser. Siden vi jobber med tre rekker, kan vi visualisere driften som vist i tabellen nedenfor, der resultatet kolonnen er resultatet av å multiplisere matrise1 * matrise2 * array3 :
| matrise1 | matrise2 | array3 | resultat |
|---|---|---|---|
| 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 |
Merknad matrise1 fungerer som et filter - null verdier her "null ut" verdier i rader der fargen ikke er "rød". Å sette resultatene tilbake i SUMPRODUCT, har vi:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Som gir et sluttresultat på 480.
Legge til flere kriterier
Du kan utvide kriteriene ved å legge til et annet logisk uttrykk. For eksempel, for å finne totalt salg der fargen er "rød" og tilstanden er "TX", inneholder H6:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Merk: SUMPRODUCT er ikke mellom store og små bokstaver.
Forenkling med en enkelt matrise
Excel-proffer vil ofte forenkle syntaksen i SUMPRODUCT litt ved å multiplisere matriser direkte inne i array1 slik:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Dette fungerer fordi matteoperasjonen (multiplikasjon) automatisk tvinger de SANNE og FALSE verdiene fra de to første uttrykkene til ett og nuller.
