Sådan bruges Excel SUMPRODUCT-funktionen

Indholdsfortegnelse

Resumé

SUMPRODUCT-funktionen multiplicerer områder eller arrays sammen og returnerer summen af ​​produkter. Dette lyder kedeligt, men SUMPRODUCT er en utrolig alsidig funktion, der kan bruges til at tælle og summe som COUNTIFS eller SUMIFS, men med mere fleksibilitet. Andre funktioner kan let bruges i SUMPRODUCT for at udvide funktionaliteten endnu mere.

Formål

Multiplicer, og sum derefter arrays

Returneringsværdi

Resultatet af flere og summerede arrays

Syntaks

= SUMPRODUCT (array1, (array2),…)

Argumenter

  • array1 - Det første array eller interval, der skal ganges, og tilføj derefter.
  • array2 - (valgfrit) Det andet array eller interval, der skal multipliceres, og tilføj derefter.

Version

Excel 2003

Brugsanvisninger

SUMPRODUCT-funktionen fungerer med arrays, men den kræver ikke den normale array-syntaks (Ctrl + Shift + Enter) for at komme ind. Formålet med SUMPRODUCT-funktionen er at formere, derefter summe, arrays. Hvis der kun leveres en matrix, summerer SUMPRODUCT simpelthen elementerne i matrixen. Der kan leveres op til 30 arrays.

Når du første gang støder på SUMPRODUCT, kan det virke kedeligt, komplekst og endda meningsløst. Men SUMPRODUCT er en utrolig alsidig funktion med mange anvendelser. Fordi det vil håndtere arrays yndefuldt og uden klage, kan du bruge det til at behandle celleområder på smarte, elegante måder (se links til formeleksempler på denne side).

For at illustrere, hvordan SUMPRODUCT fungerer, er her et par almindelige eksempler.

SUMPRODUCT for betingede summer og optællinger

Antag, at du har nogle ordredata i A2: B6, med tilstand i kolonne A, salg i kolonne B:

EN B
1 Stat Salg
2 UT 75
3 CO 100
4 TX 125
5 CO 125
6 TX 150

Ved hjælp af SUMPRODUCT kan du tælle det samlede salg for Texas ("TX") med denne formel:

=SUMPRODUCT(--(A2:A6="TX"))

Og du kan sammenfatte det samlede salg for Texas ("TX") med denne formel:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Bemærk: Bliv ikke forvirret af det dobbelte negative. Dette er et almindeligt trick, der bruges i mere avancerede Excel-formler til at tvinge SANDE og FALSKE værdier til 1'er og 0'er. Se mere nedenfor …

For eksemplet ovenfor er her en virtuel repræsentation af de to arrays som først behandlet af SUMPRODUCT:

matrix1 matrix2
FALSK 75
FALSK 100
SAND 125
FALSK 125
SAND 150

Hver matrix har 5 elementer. Den første matrix indeholder de SANDE / FALSE-værdier, der er resultatet af udtrykket A2: A6 = "TX", og den anden matrix er indholdet af B2: B6. Hvert element i det første array multipliceres med det tilsvarende element i det andet array. I den aktuelle tilstand vil resultatet af SUMPRODUCT imidlertid være nul, fordi de SANDE og FALSKE værdier behandles som nul. Vi har brug for elementerne i array1 for at være numeriske - de skal "tvinges" til ener og nuller. Det er her det dobbeltnegative kommer ind.

Ved at bruge den dobbelte negative - (dobbelt unary, for dig tekniske typer) er vi i stand til at tvinge TRUE / FALSE til de numeriske værdier en og nul, som vist i den virtuelle repræsentation nedenfor. Den sidste kolonne "Produkt" repræsenterer resultatet af at multiplicere de to arrays sammen. Det summerede resultat, 275, er den værdi, som SUMPRODUCT returnerer.

matrix1 matrix2 Produkt
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Sum 275

Ved hjælp af den krøllede afstivningssyntaks til arrays ser eksemplet sådan ud efter tvang:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))

og sådan her efter multiplikation:

=SUMPRODUCT((0,0,125,0,150))

Dette eksempel udvider idéerne ovenfor med flere detaljer.

SUMPRODUCT med andre funktioner

SUMPRODUCT kan bruge andre funktioner direkte. Du kan muligvis se SUMPRODUCT brugt sammen med LEN-funktionen til at tælle samlede tegn i et interval eller med funktioner som ISBLANK, ISTEXT osv. Disse er normalt ikke arrayfunktioner, men når de får et interval, opretter de et "resultat array". Da SUMPRODUCT er bygget til at arbejde med arrays, er det i stand til at udføre beregninger på arrays direkte. Dette kan være en god måde at spare plads på et regneark ved at eliminere behovet for en "hjælper" -søjle.

Antag for eksempel, at du har 10 forskellige tekstværdier i A1: A10, og at du vil tælle de samlede tegn for alle 10 værdier. Du kan tilføje en hjælpekolonne i kolonne B, der bruger denne formel: LEN (A1) til at beregne tegnene i hver celle. Derefter kan du bruge SUM til at tilføje alle 10 numre. Men ved hjælp af SUMPRODUCT kan du skrive en formel som denne:

=SUMPRODUCT(LEN(A1:A10))

Når det bruges med et område som A1: A10, returnerer LEN en matrix med 10 værdier. Derefter summerer SUMPRODUCT simpelthen alle værdier og returnerer resultatet uden hjælpekolonne.

Se eksempler nedenfor på mange andre måder at bruge SUMPRODUCT på.

Bemærkninger:

  1. SUMPRODUCT behandler ikke-numeriske emner i arrays som nuller.
  2. Arrayargumenter skal have samme størrelse. Ellers genererer SUMPRODUCT en #VALUE! fejlværdi.
  3. Logiske tests inde i arrays opretter SANDE og FALSKE værdier. I de fleste tilfælde vil du tvinge disse til 1'er og 0'er.
  4. SUMPRODUCT kan ofte bruge resultatet af andre funktioner direkte (se formeleksempler nedenfor)

Interessante artikler...