SUMMERPRODUKT funksjon i Excel – Finn rad nummeret over flere kolonner (og flere triks)

Kort innføring – hva er Excel funksjonen SUMMERPRODUKT?

Opprinnelig skal Excels funksjon SUMMERPRODUKT beregne et summeprodukt. Det betyr at du oppgir to områder som inneholder like mange celler og Excel multipliserer første celle i område 1 med første celle i område 2, andre celle i område 1 med andre celle i område 2, og så videre. Til slutt formes en summe av disse produktene. Ser på eksemplet:

SUMMERPRODUKT setter opp følgende beregning:

1 * 4 + 2 * 5 + 3 * 6 = 32

La oss ha en kort titt på funksjonens syntaks før vi går videre til litt mer avanserte triks.

Syntaks av SUMMERPRODUKT

=SUMMERPRODUKT(matrise1; [matrise2];…)

matrise1: Dette er det første området av celler (hva tror du skjer hvis du bare oppgir en matrise? Riktig, du får same resultatet som SUMMER funksjonen hadde gitt deg)

matrise2, matrise3, osv.: Dette er det andre, tredje, osv. området. Det er fullt mulig å forme produktene med mer enn bare to (matriser) multiplikatorer.

Mye mer enn bare et summeprodukt

Istedenfor cellereferanser eller områder/matriser er det mulig å legge in funksjoner og betingelser (sjekk gjerne operatorer til HVIS funksjonen)

Trik 1: Oppslag i Excel tabell over flere rader eller kolonner

La oss tenke du har en Excel tabell med flere kolonner. Du ønsker å finne radnummeret til en verdi som er i en av kolonnene. FINN.RAD og INDEKS/SAMMENLIGNE kan du bare bruke om du vet i hvilken kolonne verdien befinner seg i. Men med hjelp av SUMMERPRODUKT kan du finne radnummeret i et 2-dimensjonalt område.

I eksemplet har vi en matrise med dager og klokkeslett som kategori. Hvis du nå ønsker å finne klokkeslett og dag for en av navnene i matrisen kan du verken bruke FINN.RAD eller INDEKS/SAMMENLIGNE, rett og slett fordi navnene er fordelt over flere kolonner og flere rader. Men det funker med SUMMERPRODUKT. For å finne radnummeret setter vi opp følgende formel:

=SUMMERPRODUKT((C6:G9=C11)*RAD(C6:G9))

Den funker ganske enkelt. Funksjonen starter i celle C6 og sjekker om C6 er lik C11 (navnet i C11 er oppslagsverdien). Det er ikke tilfellet og dermed blir C6=C11 USANN eller 0. Radnummeret er 6. Dermed ser det slik ut. 0 gang 6 er 0.

Funksjonen går nå videre til G9. Ønsker vi for eksempel å slå opp «Anne» som står i D7, så skjer dette her: D7=C11 er SANN og dermed 1. RAD funksjonen for D7 er selvsagt 7. Da blir resultatet 1 ganger 7. Så lenge vi bare har et eneste resultat for Anne blir regnstykket for SUMMERPRODUKT slik:

0 + 0 + 0 + 0 + 0 + 0 + 7 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 = 7

Så vi fikk radnummeret som er 7. Det er en selvfølge at dette ikke funker når «Anne» forekommer to eller flere ganger.

Du kan bygge same formelen også for kolonnenummeret. Du må bare skifte RAD funksjonen med KOLONNE funksjonen. Da blir formelen slik:

=SUMMERPRODUKT((C6:G9=C11)*KOLONNE(C6:G9))

For å slå opp klokkeslett og dag kan du fint bruke INDEKS funksjonen, bare husk at SUMMERPRODUKT leverer et absolutt rad- og kolonnenummer (telt fra A1), mens INDEKS bruker relative rad- og kolonnenummerer (telt fra første celle i en matrise). Problemet løser du enkelt ved å fratrekke en RAD eller KOLONNE funksjon.

For å få takk i klokkeslett bruker vi følgende formel (C12 er cellen med SUMMERPRODUKT formelen:

=INDEKS(B6:B9;C12-RAD(B5);1)

Lignende formel brukes for å få tak i dagen (her er C12 cellen med SUMMERPRODUKT):

=INDEKS(C5:G5;1;C13-KOLONNE(B5))

Det er mange muligheter for å sette opp avanserte oppslagsformler når du har forstått prinsippet som brukes her.

Trik 2: SUMMERPRODUKT - Et alternativ til SUMMERHVIS?

Du kan faktisk bruker SUMMERPRODUKT på samme måtte som SUMMERHVIS – med en eller flere betingelser.

I et veldig forenklet eksempel har vi en tabell med ansattes fornavn, kjønn og antall timer overtid. Vi ønsker å fine summen av timer overtid for menn som har mer enn 25 timer overtid.

Følgende funksjon i C15 brukes for dette:

=SUMMERPRODUKT((C6:C13="Mann")*(D6:D13>25);D6:D13)

Steg for steg skjer følgende: C6:C13="Mann" går gjennom cellene og når det står «Mann» i en celle så blir resultat SANN eller 1. D6:D13>25 sjekker om antall timer overtid er større enn 25 og hvis det er tilfellets blir også dette resultatet SANN eller 1. Er begge betingelser SANN så blir produktet 1. Og dermed multipliseres 1 med antall timer i kolonne D.

Er bare en av betingelsen USANN blir hele produktet 0. Her er det store muligheter for brukerdefinerte formler.

Trik 3: Tell antall når en betingelse er sann

Vi bruker same eksempel som over, men denne gangen vil vi ikke summere timene men telle antall personer.

=SUMMERPRODUKT((C6:C13="Mann")*(D6:D13>25))

Formelen er ganske likt den vi brukte for å få summen. Første går vi mellom cellene C6:C13 og sjekker om det står «Mann» der. Og deretter gjennom D6:D13 for å sjekke om antall timene er større enn 25. Er begge betingelser SANN blir produktet 1 ganger 1. For hver rad der begge betingelser er SANN adderer vi dermed 1 til summeproduktet.

Trik 4: Oppslag i en tabell basert på to verdier – Avanserte matrise aritmetikk i Excel

Nå tar vi et enkelt eksempel med en matrise der vi har navnene og uketall som overskrifter og antall timer overtid for hvert ansatt. Når vi velger navn og uke ønsker vi å få takk i overtid. Det vi gjør her er også mulig med INDEKS/SAMMENLIGNE men her ønsker jeg å demonstrere hva du kan bruke SUMMERPRODUKT funksjonen til.

Vi setter opp følgende formel:

=SUMMERPRODUKT((B6:B13=C15)*(C5:F5=C16)*C6:F13)

I C15 velger vi navn og i C16 uketall. Begge cellene inneholder rullegardinmenyer. Hvordan funker det nå?

Vi har tre områder her:

• B6:B13 (navnene): 8 celler
• C5:F5 (uketallene): 4 celler
• C6:F13 (matrisen som inneholder antall timene): 32 celler

Når vi multipliserer 8 ganger 4 har vi en matrise som er like stor som matrisen med timene – 32 celler. Excel sjekker alle betingelser, både i uketall og i navnene. Resultatet per celle blir enten 0 eller 1. Vi få dermed to områder som ser slik ut:

Excel former nå en ny matrise med 32 celler (8 x 4):

Som siste steg formerer Excel et summeprodukt av disse to matriser. Resultatet for «Bjørn» og «Uketal4» blir 1.

Det er tydelig at resultatet blir feil hvis en av søkeverdiene (her navn eller uketall fins mer enn en gang).

Her var det nå bare fire ideer hva du kan bruke SUMMERPRODUKT funksjonen til, men du kan være veldig kreativt med denne funksjonen.

Last ned eksemplet her:

Ønsker du hjelp med komplekse Excel formler?

Vi hjelper bedrifter med alle oppgaver rundt Microsoft Excel, blant annet oppsett av komplekse Excel formler, men også dashboards, makroer, kalkyler og alt som henger samen med Microsoft Excel. Finn ut mer om våre Excel konsulent tjenester eller ta kontakt – vi hjelper raskt, rimelig og pålitelig.