Hvordan bruke Excel SUMPRODUCT-funksjonen

Innholdsfortegnelse

Sammendrag

SUMPRODUCT-funksjonen multipliserer områder eller arrays sammen og returnerer summen av produkter. Dette høres kjedelig ut, men SUMPRODUCT er en utrolig allsidig funksjon som kan brukes til å telle og summere som COUNTIFS eller SUMIFS, men med mer fleksibilitet. Andre funksjoner kan enkelt brukes i SUMPRODUCT for å utvide funksjonaliteten ytterligere.

Hensikt

Multipliser, og deretter summere matriser

Returverdi

Resultatet av multipliserte og summerte matriser

Syntaks

= SUMPRODUCT (array1, (array2),…)

Argumenter

  • array1 - Det første arrayet eller området som multipliseres, og deretter legger du til.
  • array2 - (valgfritt) Det andre arrayet eller området som skal multipliseres, og legg deretter til.

Versjon

Excel 2003

Bruksanvisninger

SUMPRODUCT-funksjonen fungerer med matriser, men den krever ikke den normale array-syntaksen (Ctrl + Shift + Enter) for å komme inn. Formålet med SUMPRODUCT-funksjonen er å multiplisere, deretter summere, matriser. Hvis bare en matrise leveres, vil SUMPRODUCT ganske enkelt summere elementene i matrisen. Opptil 30 matriser kan leveres.

Når du første gang møter SUMPRODUCT, kan det virke kjedelig, komplisert og til og med meningsløst. Men SUMPRODUCT er en utrolig allsidig funksjon med mange bruksområder. Fordi den vil håndtere matriser elegant og uten klage, kan du bruke den til å behandle celleområder på smarte, elegante måter (se lenker til formeleksempler på denne siden).

For å illustrere hvordan SUMPRODUCT fungerer, er det noen vanlige eksempler.

SUMPRODUCT for betingede summer og tellinger

Anta at du har noen 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 å bruke SUMPRODUCT kan du telle totalsalget for Texas ("TX") med denne formelen:

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

Og du kan oppsummere det totale salget for Texas ("TX") med denne formelen:

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

Merk: Ikke bli forvirret av det dobbelte negative. Dette er et vanlig triks som brukes i mer avanserte Excel-formler for å tvinge SANNE og FALSE verdier til 1-tallet og 0-tallet. Se mer nedenfor …

For sumeksemplet ovenfor er her en virtuell fremstilling av de to matriser som først behandlet av SUMPRODUCT:

matrise1 matrise2
FALSK 75
FALSK 100
EKTE 125
FALSK 125
EKTE 150

Hver matrise har 5 elementer. Den første matrisen inneholder de SANNE / FALSE verdiene som kommer fra uttrykket A2: A6 = "TX", og den andre matrisen er innholdet i B2: B6. Hvert element i den første matrisen vil bli multiplisert med det tilsvarende elementet i den andre matrisen. I den nåværende tilstanden vil imidlertid resultatet av SUMPRODUCT være null fordi de SANNE og FALSE verdiene blir behandlet som null. Vi trenger at elementene i array1 skal være numeriske - de må "tvinges" til ener og nuller. Det er her det dobbelte negative kommer inn.

Ved å bruke den dobbelte negative - (dobbel unary, for dere tekniske typer) er vi i stand til å tvinge SANN / FALSK til de numeriske verdiene en og null, som vist i den virtuelle representasjonen nedenfor. Den siste kolonnen "Produkt" representerer resultatet av å multiplisere de to gruppene sammen. Det summerte resultatet, 275, er verdien SUMPRODUCT returnerer.

matrise1 matrise2 Produkt
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Sum 275

Ved å bruke den krøllete avstivningssyntaks for matriser, ser eksemplet slik ut etter tvang:

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

og slik etter multiplikasjon:

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

Dette eksemplet utvider ideene ovenfor med mer detaljer.

SUMPRODUCT med andre funksjoner

SUMPRODUCT kan bruke andre funksjoner direkte. Det kan hende du ser SUMPRODUCT brukt sammen med LEN-funksjonen til å telle totalt antall tegn i et område, eller med funksjoner som ISBLANK, ISTEXT, etc. Dette er vanligvis ikke matrisefunksjoner, men når de får et område, oppretter de et "resultatmatrise". Fordi SUMPRODUCT er bygget for å fungere med matriser, kan den utføre beregninger på matriser direkte. Dette kan være en god måte å spare plass på et regneark ved å eliminere behovet for en "hjelper" -kolonne.

Anta for eksempel at du har 10 forskjellige tekstverdier i A1: A10, og at du vil telle de totale tegnene for alle de 10 verdiene. Du kan legge til en hjelpekolonne i kolonne B som bruker denne formelen: LEN (A1) for å beregne tegnene i hver celle. Deretter kan du bruke SUM for å legge sammen alle de 10 tallene. Ved å bruke SUMPRODUCT kan du imidlertid skrive en formel som denne:

=SUMPRODUCT(LEN(A1:A10))

Når det brukes med et område som A1: A10, vil LEN returnere en matrise med 10 verdier. Da vil SUMPRODUCT ganske enkelt oppsummere alle verdiene og returnere resultatet, uten hjelpekolonne.

Se eksempler nedenfor på mange andre måter å bruke SUMPRODUCT på.

Merknader:

  1. SUMPRODUCT behandler ikke-numeriske elementer i matriser som nuller.
  2. Arrayargumenter må være av samme størrelse. Ellers vil SUMPRODUCT generere en #VALUE! feilverdi.
  3. Logiske tester i matriser vil skape SANNE og FALSE verdier. I de fleste tilfeller vil du tvinge disse til 1 og 0.
  4. SUMPRODUCT kan ofte bruke resultatet av andre funksjoner direkte (se formeleksempler nedenfor)

Interessante artikler...