Generisk formel
=SUMPRODUCT(expression,range)
Resumé
For at filtrere resultater af SUMPRODUCT med specifikke kriterier kan du anvende enkle logiske udtryk direkte på arrays i funktionen i stedet for at bruge IF-funktionen. I det viste eksempel er formlerne i H5: H7:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
hvor følgende navngivne områder er defineret:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Hvis du hellere vil undgå navngivne områder, skal du bruge de ovennævnte intervaller som absolutte referencer. De logiske udtryk i H6 og H7 kan kombineres som forklaret nedenfor.
Forklaring
Dette eksempel illustrerer en af nøglefærdighederne i SUMPRODUCT-funktionen - evnen til at filtrere data med basale logiske udtryk i stedet for IF-funktionen. Inde i SUMPRODUCT er den første matrix et logisk udtryk, der skal filtreres på farven "rød":
--(color="red")
Dette resulterer i en matrix eller SAND FALSKE værdier, der tvinges til en og nuller med den dobbelte negative (-) operation. Resultatet er denne matrix:
(1;0;1;0;0;0;1;0;0;0)
Bemærk, at matrixen indeholder 10 værdier, en for hver række. En angiver en række, hvor farven er "rød", og et nul angiver en række med en hvilken som helst anden farve.
Dernæst har vi to flere arrays: en for mængde og en for pris. Sammen med disse resultater fra den første matrix har vi:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Udvidelse af arrays 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 kerneadfærd er at formere sig og derefter summe arrays. Da vi arbejder med tre arrangementer, kan vi visualisere operationen som vist i tabellen nedenfor, hvor resultatet kolonne er resultatet af at multiplicere matrix1 * matrix2 * array3 :
| matrix1 | matrix2 | matrix3 | 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 |
Bemærk array1 fungerer som et filter - nul værdier her "nul ud" værdier i rækker, hvor farven ikke er "rød". At sætte resultaterne tilbage i SUMPRODUCT har vi:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Hvilket returnerer et slutresultat på 480.
Tilføjelse af yderligere kriterier
Du kan udvide kriterierne ved at tilføje et andet logisk udtryk. For eksempel for at finde det samlede salg, hvor farven er "rød" og tilstanden er "TX", indeholder H6:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Bemærk: SUMPRODUCT er ikke store og små bogstaver.
Forenkling med et enkelt array
Excel-proffer vil ofte forenkle syntaksen inde i SUMPRODUCT lidt ved at multiplicere arrays direkte inde i array1 sådan:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Dette fungerer, fordi matematikoperationen (multiplikation) automatisk tvinger de SANDE og FALSE-værdier fra de to første udtryk til ener og nuller.
