Excelfunksjonen SUMMERPRODUKT multipliserer celleområder eller matriser sammen og returnerer summen av produktet. SUMMERPRODUKT er en utrolig variert funksjon som kan brukes til å beregne og summere på samme måte som ANTALL.HVIS.SETT eller SUMMERHVIS, men med større tilpasningsevne. Andre funksjoner kan meget enkelt brukes inne i SUMMERPRODUKT for å utvide funksjonaliteten ytterligere.
Hensikt
Multipliser og summer matriser
Returverdi
Resultatet av multipliserte og summerte matriser
Syntaks
= SUMMERPRODUKT (matrise1,[matrise2], …)
Argument
matrise1 – Den første matrisen eller cellearealet som skal multipliseres og deretter summeres
matrise2 – [valgfritt] Den andre matrisen eller celleområdet som skal multipliseres og deretter summeres
Bruksnotater
SUMMERPRODUKT-funksjonen multipliserer matriser med hverandre og returnerer summen av produktene. Hvis bare én matrise leveres, summerer funksjonen SUMMERPRODUKT ganske enkelt alle objektene i matrisen. Opptil 30 individuelle celleområder eller matriser kan legges til.
SUMMERPRODUKT er en utrolig allsidig funksjon med mange bruksområder. Fordi den håndterer matriser på en elegant måte, kan den brukes til å behandle og beregne cellearealer på en smart og fleksibel måte.
Matriser og Excel 365
SUMMERPRODUKT kan brukes til å lage matriseformler som ikke krever hurtigtasten Ctrl + Shift + Retur. Dette er en nødvendig grunn til at SUMMERPRODUKT har blitt brukt så ofte for å lage mer avanserte formler. Et vanlig problem med matriseformler er at de kan returnere feil resultater hvis de ikke er spesifisert med snarveien Ctrl + Shift + Retur. Dette betyr at hvis du glemmer å bruke snarveien når du justerer en formel, kan hele resultatet plutselig endres, selv om selve formelen ikke har endret seg i det hele tatt. Å bruke SUMMERPRODUKT betyr at formlene fungerer i alle versjoner av Excel uten spesiell behandling.
I Excel 365 administrerer formelmotoren matriser internt, noe som betyr at du ofte kan bruke SUMMER-funksjonen i stedet for SUMMERPRODUKT i en matriseformel og få samme resultat. Det er dermed ikke nødvendig å legge inn formelen på en spesiell måte. Alternativt, hvis den samme formelen er åpnet i en tidligere versjon av Excel, kreves snarveien Ctrl + Shift + Retur igjen.
Konklusjonen er at SUMMERPRODUKT er et sikrere alternativ dersom et regneark eventuelt skal brukes av noen som har en versjon av Excel før Excel 365.
Eksempel på PRODUKTSUM
Det klassiske SUMMERPRODUKT-eksemplet illustrerer hvordan du kan regne ut en sum direkte uten en såkalt hjelpekolonne. I regnearket nedenfor kan du for eksempel bruke SUMMERPRODUKT for å få summen av alle tallene i kolonne G uten å bruke kolonne G i det hele tatt:
For å utføre denne beregningen bruker SUMMERPRODUKT alle verdiene i kolonnene E og F direkte:
= SUMMERPRODUKT(E5:E14;F5:F14)
Resultatet er det samme som å summere alle verdiene i kolonne G. Formelen vurderes som følger:
= SUMMERPRODUKT(E5:E14;F5:F14)
= SUMMERPRODUKT({10;6;14;9;11;10;8;9;11;10};{150;180;150;160;180;180;150;160;180;160})
= SUMMERPRODUKT({1500;1080;2100;1440;1980;1800;1200;1440;1980;1600})
= 16120
Denne metoden for bruk av SUMMERPRODUKT kan være praktisk, spesielt når det ikke er plass (eller behov) for en hjelpesøyle med mellomregning. Den vanligste bruken av SUMMERPRODUKT i virkeligheten er imidlertid å bruke betinget logikk i situasjoner som krever mer fleksibilitet enn det som kan tilbys av funksjoner som SUMMERHVIS og ANTALL.HVIS.SETT.
SUMMERPRODUKT for betingede beløp og regninger
Anta at du har noen ordredata i A2:B6, med byer i kolonne A og salg i kolonne B:
| By | Salg (millioner) |
| Malmö | 75 |
| Göteborg | 100 |
| Stockholm | 125 |
| Göteborg | 125 |
| Stockholm | 150 |
Ved hjelp av SUMMERPRODUKT kan du beregne det totale salget for Stockholm med denne formelen:
= SUMMERPRODUKT(-(A2:A6=”Stockholm”))
Og du kan oppsummere det totale salget for Stockholm med denne formelen:
= SUMMERPRODUKT (-(A2:A6=”Stockholm”),B2:B6)
Tips: Dobbelt minustegn er et vanlig triks som brukes i mer avanserte Excel-formler for å tvinge SANN- og USANN-utsagn til å bli 1-er og 0-er.
I eksempelet ovenfor har vi en billedlig representasjon av de to matrisene som først behandles av SUMMERPRODUKT:
matrise1 matrise2
USANN 75
USANN 100
SANN 125
USANN 125
SANN 150
Hver matrise har 5 objekter. Matrise1 inneholder SANN/USANN-verdiene som er resultatet av uttrykket A2:A6 = “Stockholm” og matrise2 inneholder alle verdiene i B2:B6. Hver artikkel i matrise1 multipliseres med det tilsvarende objektet i matrise2. Resultatet i gjeldende modus er imidlertid null fordi SANN og USANN-verdiene i matrise1 blir evaluert som null. Vi ønsker at objektene i matrise1 skal være numeriske, og det er her doble minustegn blir nyttige.
Dobbelt minustegn (-)
Dobbelt minustegn (-) er en av flere måter å tvinge SANN og USANN til å bli deres numeriske ekvivalenter, dvs. 1 og 0. Så snart de har blitt 1-er og 0-er, kan vi utføre forskjellige operasjoner på matrisene med boolsk logikk. Tabellen nedenfor viser resultatet i matrise1, basert på formelen ovenfor, etter at de doble minustegnene (-) har endret SANN- og USANN-verdiene til 1s og 0s.
array1 array2 Produkt
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Sum 275
Hvis du oversetter tabellen ovenfor til matriser, blir formelen evaluert som følger:
=SUMMERPRODUKT({0;0;1;0;1};{75;100;125;125;150})
SUMMERPRODUKT multipliserer deretter matrise1 med matrise2, noe som resulterer i en enkelt matrise:
=SUMMERPRODUKT({0;0;125;0;150})
Til slutt returnerer SUMMERPRODUKT summen av alle verdier i matrisen, dvs. 275.
Forkortet syntaks
Du vil ofte se formelen beskrevet ovenfor skrevet på en annen måte:
= SUMMERPRODUKT((A2:A6=”Stockholm”)*B2:B6)
Denne formelen returnerer det riktige svaret – 275.
Det er kun én matrise som legges inn i funksjonen SUMMERPRODUKT. Resultatet er det samme, men syntaksen er mer kompakt fordi det ikke er nødvendig å bruke doble minustegn (-). Dette er fordi den matematiske operasjonen for multiplikasjon (*) automatisk tvinger SANN og USANN-verdiene fra (A2:A6 =”Stockholm”) til 1-ere og 0-er. Denne syntaksen vises ofte i SUMMERPRODUKT-formler, da den viser en måte å bruke boolsk algebra for å lage logikken som trengs i mer komplekse scenarier.
Ignorer tomme celler
Hvis du vil ignorere tomme celler med SUMMERPRODUKT, kan du bruke et slikt uttrykk:
celleområde<>””
I eksemplet nedenfor ignorerer formlene i G5 og G6 de to cellene i kolonne D som ikke inneholder en verdi:
=SUMMERPRODUKT(-(C5:C15<>””))
=SUMMERPRODUKT(-(C5:C15<>”)*D5:D15)
SUMMERPRODUKT med andre funksjoner
SUMMERPRODUKT kan bruke andre funksjoner som argumenter. Du ser ofte SUMMERPRODUKT-funksjoner som bruker LENGDE-funksjonen for å telle totalt antall tegn innenfor et område, eller med funksjoner som ERTOM, ERTALL osv. Normalt er dette ikke matrisefunksjoner, men når de får et celleområde, lager de en “resultatmatrise”. Fordi SUMMERPRODUKT er bygget for å fungere med matriser, kan den utføre beregninger direkte på matrisene. Dette kan være en fin måte å spare plass i et regneark fordi det eliminerer behovet for en hjelpekolonne.
La oss for eksempel si at du har 10 forskjellige tekstverdier i B1:B10 og at du vil legge sammen det totale antallet tegn for alle 10 verdiene. Du kan legge til en hjelpekolonne i kolonne C som bruker denne formelen: LENGDE (B1) for å telle antall tegn i hver celle. Deretter kan du bruke SUM for å summere alle de 10 resulterende tallene. Med SUMMERPRODUKT kan du imidlertid skrive en formel som gjør alt på en gang:
= SUMMERPRODUKT(LENGDE(A1: A10))
Når LENGDE brukes med et område som B1:B10, vil det returnere en matrise med 10 verdier. SUMMERPRODUKT summerer deretter ganske enkelt alle disse verdiene og returnerer resultatet, uten behov for en hjelpekolonne.
Notater
- SUMMERPRODUKT behandler ikke-numeriske objekter i matriser som null.
- Alle matriseargumenter som er spesifisert må ha samme størrelse, ellers vil SUMMERPRODUKT generere en feil.
- Logiske tester i matriser skaper SANN og USANN verdier. I de fleste tilfeller vil du tvinge disse til å bli 1-ere og 0-ere.
- SUMMERPRODUKT kan ofte bruke resultatene av andre funksjoner direkte.
