Funktionen SUMPRODUKT

Funktionen SUMPRODUKT() og lidt til

Af og til har man brug for at udføre beregninger, fx optællinger eller sammenlægninger, der afhænger af et eller flere kriterier. Dette er forholdsvist simpelt, når det gælder beregninger, der afhænger af en enkelt betingelse. Så har Excel nemlig to indbyggede funktioner, der kan løses opgaven, men kan være lidt mere kompliceret, hvis der er mere end et kriterium, der skal være opfyldt.

TÆL.HVIS()
SUM.HVIS()
SUMPRODUKT()
Forklaring på SUMPRODUKT()

- Retur til Excel -

TÆL.HVIS()

Forestil dig følgende regneark.

	A	B
1	1	1
2	1	2
3	2	3

Opgaven går nu ud på, at tælle antallet af 1-taller i A-kolonnen. Det gøres nemt ved brug af funktionen TÆL.HVIS(). Formlen skal se således ud: =TÆL.HVIS(A1:A3;1) og resultatet vil blive 2.

	A	B
1	Bil	1
2	Bil	2
3	Tog	3

Funktionen kan også arbejde med tekst som kriterium. I så fald skal dette skrives i anførselstegn. =TÆL.HVIS(A1:A3;"Bil") Resultatet er stadig 2.

- Til top -

SUM.HVIS()

På samme måde kan man få summeret rækker, hvor en given betingelse er opfyldt. Dette gøres med SUM.HVIS(), der også både kan tage tal og tekst som kriterier.

	A	B
1	1	1
2	1	2
3	2	3

I det første eksempel, skal beregnes summen af de tal i B-kolonnen, hvor det tilsvarende tal i A kolonnen er 1. Formlen er
"=SUM.HVIS(A1:A3;1;B1:B3) og resultatet er 3.

	A	B
1	Bil	1
2	Bil	2
3	Tog	3

I det næste eksempel skal vi finde summen af de tal i B-kolonnen, hvor den tilhørende tekst i A-kolonnen er Bil. Formlen er
"=SUM.HVIS(A1:A3;"Bil";B1:B3) og resultatet er igen 3.

	A	B
1	1	2
2	2	3
3	3	3
4	3	4
5	4	3

SUM.HVIS() har også mulighed for at tage kriterium, fra den kolonne, der skal tælles, hvis denne indeholder tal. Formlen=SUM.HVIS(B1:B5;3;B1:B3) vil sammenlægge alle tre-taller i B1 til og med B5. Resultatet bliver således 9.

- Til top -

SUMPRODUKT()

Af og til er det imidlertid ikke nok at inddrage et enkelt kriterie i sin optælling eller sammenlægning.

	A	B	C
1	Bil	Rød	1
2	Bil	Blå	2
3	Tog	Rød	3
4	Bus	Gul	4
5	Bus	Blå	5
6	Bil	Blå	6

I det første eksempel vil vi gerne tælle alle blå biler. =TÆL.HVIS() kan tælle alle Biler eller alle Blå, men den kan ikke kombinere de to tællinger i en. Til gengæld kan vi bruge funktionen SUM.PRODUKT(). Denne funktions egentlige formål er at finde summen af to eller flere produkter, men den kan altså også bruges til andre ting. Se en nærmere forklaring på funktionen nedenfor.
Formlen =SUMPRODUKT((A1:A6="Bil")*(B1:B6="Blå)) løser opgaven. Resultatet bliver som forventet 2 blå biler.

SUMPRODUKT kan imidlertid også summere. Hvis tallene i C-kolonnen repræsenterer det antal biler, busser osv. der er solgt på enkelte dage, kan vi bruge formlen: =SUMPRODUKT((A1:A6="Bil")*(B1:B6="Blå")*(C1:C6)) og vi får så resultatet 8.

Der kan selvfølgelig også være flere kriterier på. Har vi fx ugedagene stående i kolonne D, kan vi finde ud af, hvor mange blå biler, der er solgt om tirsdagen ud fra denne formel: =SUMPRODUKT((A1:A6="Bil")*(B1:B6="Blå")*(C1:C6)*(D1:D6="Tirsdag")). Der er altså ikke noget krav om, at kolonnen med tal, der skal summeres skal stå sidst.

	A	B	C
1	Bil		1
2	Bil	Blå	2
3	Tog	Rød	3
4	Bus	Gul	4
5	Bus		5
6	Bil	Blå	6

Funktionen kan også kombineres med andre funktioner. I ovenstående model, kan vi forestille os, at farven ikke nødvendigvis udfyldes. Det kunne givet et eksempel som vist til venstre. Hvis vi nu vil finde antallet af busser, hvor der ikke er opgivet farve, kan følgende bruges:
=SUMPRODUKT((A1:A6="Bus")*(ER.TOM(B1:B6))*(C1:C6)) og svaret er 5.

Ved at ændre bus, til en cellereference, kan vi gøre det muligt også at finde biler eller tog uden farve uden at skulle ændre i formlen:
=SUMPRODUKT((A1:A6=D1)*(ER.TOM(B1:B6))*(C1:C6)). Nu kan man i D1 indskrive det transportmiddel man leder efter.

Det er også muligt på en gang at gteste forto kriterier, hvoraf bare det ene eller dem begge, skal være opfyldt.
=SUMPRODUKT((ELLER(A1:A6="Bil";A1:A6="Bus"))*(ER.TOM(B1:B6))*(C1:C6)) undersøger ført om enten Bil eller Bus har en tom farve,, og dernæst sammenlægger den så tallet i c-kolonnen.

Ved brug af SUMPRODUKT(), skal man være opmærksom på, at funktionen ikke kan arbejde på hele kolonner. Formlen
=SUMPRODUKT((A:A=D1)*(B:B="Blå")*(C:C)) vil således give fejlkoden #NUM!.

Holder man en enkelt række uden for formlen, fx

=SUMPRODUKT((A2:A65536=D1)*(B2:B65536="Blå")*(C2:C65536)) eller
=SUMPRODUKT((A1:A65535=D1)*(B1:B65535="Blå")*(C1:C65535))

vil det virke uden fejl.

NB! Husk, at ved brug af SUMPRODUKT, skal alle matricer altid være lige store (og ens). Formlen
=SUMPRODUKT((A1:A5="Bil")*(B2:B6="Rød")*(C3:C7)) vil give fejlagtige resultater, og formlen
=SUMPRODUKT((A1:A5="Bil")*(B1:B5="Rød")*(C1:C6)) vil slet ikke virke, og i stedet give fejlkoden #I/T!

- Til top -

Forklaring på SUMPRODUKT funktionen.

Funktionen SUMPRODUKT() gør præcis det navnet siger. Den finder summen af to eller flere produkter. Antag at du har følgende regneark:

	A	B
1	2	3
2	4	5
3	6	7

Hvis du indsætter formlen =SUMPRODUKT(A1:A3;B1:B3) vil funktionen gange tallet i A1 med tallet i B1 (2 * 3 = 6) og tallet i A2 med tallet i B2 (4 * 5 = 20), og endelig tallet i A3 med tallet i B3 (6 * 7 = 42) så lægge de tre resultater sammen, så funktionens resultat bliver 68.

Funktionen arbejder altså på matricer, og den skal have mindst to matricer, for at give mening. Fx vil =SUMPRODUKT(A1:A3) virke, men den vil bare lægge tallene sammen (12), og det kunne SUM() have gjort mere effektivt. På samme måde vil en funktion, der arbejder på matricer med kun et enkelt tal i hver matrice, fx =SUMPRODUKT(A1;B1) bare gange de tal med hinanden (6) og det kunne have være gjort bedre med PRODUKT() eller nemmere med en simpel regnefunktion =A1*B1.

I eksemplerne ovenfor bruger vi ikke den "rendyrkede" SUMPRODUKT(). Her sætter vi hvert led ind i sin egen selvstændige parentes inde i SUMPRODUKT() parentesen, og sætter gangetegn (*) mellem parenteserne). Det giver en noget anden funktionalitet. De enkelte parenteser "udregnes" nu, før de ganges med hinanden, og SUMPRODUKT() til sidst beregner det endelige resultat.

Når dette giver muligheden for at tælle, henholdsvis summere med kriterier, hænger det sammen med, at der inde i hver lille parentes bliver udført en logisk sammenligning. Vi sammenligner hver celle i matrixen med den værdi, der står efter lighedstegnet. En sådan logisk sammenligning har to mulige resultater, enten er sammenligningen SAND, eller også er sammenligningen FALSK. Prøv fx i to forskellige celler i dit regneark at skrive ="Bil"="Bil" og ="Bil"="Bus", så vil du se at den første celle giver resultatet SAND og den anden FALSK, fordi det er sandt, at Bil=Bil, mens det er FALSK at Bil=Bus.

Disse sandhedsværdier har også en talværdi. SAND svarer til 1, og FALSK svarer til 0. Det er disse talværdier for sandhedsværdierne, vi udnytter i kriterieløsningerne. Lad mig give et par eksempler:

	A	B	C	D
1	A	1	FALSK (0)	0 (1 * 0)
2	B	2	FALSK (0)	0 (2 * 0)
3	C	3	FALSK (0)	0 (3 * 0)
4	D	4	SAND (1)	4 (4 * 1)
5	A	5	FALSK (0)	0 (5 * 0)
6	B	6	FALSK (0)	0 (6 * 0)
7	C	7	FALSK (0)	0 (7 * 0)
8	D	8	SAND (1)	8 (8 * 1)

Kolonnerne C og D er hjælpekolonner, der kun anvendes til forklaringsbrug. Jeg ønsker at finde summen af de tal i B-kolonnen, hvor det tilsvarende bogstav i A kolonnen er D. Til det formål bruger jeg formlen =SUMPRODUKT((A1:A8="D")*(B1:B8). Det skal lige siges, at den konkrete opgave kunne være løst med SUM.HVIS(), men her er SUMPRODUKT() brugt for illustrationens skyld. Tekst (tal) i hjælpekolonne 1 (C-kolonnen), viser sandhedsværdien af den første parentes, altså om bogstavet i A-kolonnen er et D. Tallet i hjælpekolonne 2 (D-kolonnen) er resultatet af udgangen af de enkelte faktorer i matricen. Tallene i hjælpekolonne 2 lægges nu sammen, og det giver resultatet 12, som præcis er det tal, som =SUMPRODUKT((A1:A8="D")*(B1:B8) vil give. Altså først udganges de enkelte elementer i matricen, og dernæst lægges resultaterne sammen.

Lad mig vende tilbage til eksemplet med køretøjerne:

	A	B	C
1	Bil	Rød	1
2	Bil	Blå	2
3	Tog	Rød	3
4	Bus	Gul	4
5	Bus	Blå	5
6	Bil	Blå	6

Vi skal altså finde ud af, hvor mange Biler, Blå, der er solgt/fremstillet/destrureret eller hvad vi nu arbejder med. Formlen var
=SUMPRODUKT((A1:A6="Bil")*(B1:B6="Blå")*(C1:C6))

og det giver nedenstående sandhedstabel

	A	B	C	D	E	F
1	Bil	Rød	1	1 (Det er en bil)	0 (Farven er ikke blå)	0 (1 * 1 * 0)
2	Bil	Blå	2	1 (Det er en bil)	1 (Farven er blå)	2 (2 * 1 * 1)
3	Tog	Rød	3	0 (Det er ikke en bil)	0 (Farven er ikke blå)	0 (3 * 0 * 0)
4	Bus	Gul	4	0 (Det er ikke en bil)	0 (Farven er ikke blå)	0 (4 * 0 * 0)
5	Bus	Blå	5	0 (Det er ikke en bil)	1 (Farven er blå)	0 (5 * 0 * 1)
6	Bil	Blå	6	1 (Det er en bil)	1 (Farven er blå)	6 (6 * 1* 1)

D, E og F er hjælpekolonner. D til sammenligning af første parentes, E til sammenligning af 2. parentes og F til udgangning af matrixelementernes talværdier. Jeg har i kolonne D og E kun skrevet sandhedsværdiens talværdi. Lægger vi nu tallene i kolonne F sammen, ser vi at resultatet bliver 8, det samme tal, som formlen gav i eksemplet ovenfor.

- Til Top -

- Retur til Excel -