Mere om SUMPRODUKT()

Jeg har tidligere skrevet en artikel om funktionen SUMPRODUKT() og dens mulighed for at teste for flere betingelser på samme tid. Jeg har også refereret til funktionen i fx artikler om løsninger på konkrete problemstillinger, blandt i Opslag til højre og venstre, Find værdi på baggrund af to andre værdier, Tæl tal i intervaller, samt i to småtip, Tæl unikke celleværdier i et område og Sammenlægge hver anden kolonne.

I denne artikel følger flere eksempler på, hvordan SUMPRODUKT kan anvendes.

Flere måder at opnå det samme på
De hidtidige eksempler hvor der testes på flere forskellige betingelser, har alle betingelserne skulle opfyldes samtidigt. Faktisk kan dette gøres på flere måder. Den allerede skitserede i den ovennævnte artikel:

=SUMPRODUKT((A1:A6="bil")*(B1:B6="blå")*(C1:C6)) eller

=SUMPRODUKT((A1:A6="bil")*(B1:B6="blå");(C1:C6)) eller

=SUMPRODUKT((A1:A6="bil")*1;(B1:B6="blå")*1;(C1:C6)) eller

=SUMPRODUKT(--(A1:A6="bil");--(B1:B6="blå");(C1:C6)) eller bare

=SUMPRODUKT(-(A1:A6="bil");-(B1:B6="blå");(C1:C6))

Man kan faktisk bruge flere andre metoder, men forskellen er, at den første arbejder på alle matricerne på én gang, den anden på de to første samtidigt og separat på den sidste, mens de tre sidste løsninger arbejder separat på hver enkelt matrice. Resultatet bliver det samme, uanset hvad man vælger, så det er et spørgsmål om smag. Selv foretrækker jeg den første, selv om der er situationer, hvor den ikke kan anvendes, fx hvis formlen kun indeholder en enkelt matrice. Til gengæld er der også situationer, hvor det er den eneste mulighed.

Sumprodukt til ELLER betingelser.
Funktionen også anvendes til at regne på forhold, hvor bare denne ene betingelse er opfyldt (altså ELLER betingelser i stedet for OG betingelser) samt på kombinationer af disse.

I tidligere eksempler har vi skullet finde summen a C-kolonnen, for fx de tilfælde, hvor hvor der stod Bil i A-kolonnen og Blå i B-kolonnen. Imidlertid kan vi også bruge funktionen til at finde summen i c-kolonnen, hvor der enten står Bus i A-kolonnen ELLER Rød i B-kolonnen. Dette kan gøres med

=SUMPRODUKT(((A1:A6="Bus")+(B1:B6="Rød"))*(C1:C6))

i det "+" giver et "ELLER" kriterium. Da der skal tælles alle i C, hvor ENTEN A er Bus ELLER B er Rød skal de to udtryk "pakkes ind" i en samlet parentes som så "sættes sammen med et OG kriterium, repræsenteret ved * inden sidste matrice. Vil jeg bare tælle Bus og Rød er dette nok:

=SUMPRODUKT((A1:A6=Bus")+(B1:B6="Rød")).

SUMPRODUKT() kombineret med andre funktioner
SUMPRODUKT() kan løse endnu flere opgaver, hvis man kombinerer den med andre funktioner. Her skal jeg kun nævne nogle få eksempler, men flere kommer nok til efterhånden som konkrete situationer opstår i dagligdagen.

Eksempel 1
Skal man tælle alle celler, hvor der står Bil, kan man bruge

=SUMPRODUKT(--(A1:A6="Bil")).

Da der kun er tale om en enkelt matrice, duer * ikke her. Hvis man også vil have de celler med, hvor der måske står en blank før eller efter Bil, altså " Ole" eller "Ole ", kan i stedet bruge:

=SUMPRODUKT(--(FJERN.OVERFLØDIGE.BLANKE(A1:A6)="Bil"))

Læg mærke til placeringen af parenteserne. Det er først, når eventuelle overflødige blanke er "væk", at der sammenlignes med Bil.

Eksempel 2
Opslag i en beregnet matrice kan give problemer. En beregnet matrice skal her forstås som en matrice, hvor resultaterne er beregnet ved hjælp af Tabel funktionen i Excel (findes i menuen Data i Excel 2003). Almindelig opslags- eller sammenligningsfunktioner vil ikke altid virke, men i stedet give fejlen #I/T!, selv om værdien eksisterer. Dette skyldes måden tabelfunktionen virker på. I stedet kan man bruge:

=INDEKS(C2:I14;SUMPRODUKT((RÆKKE(C2:H13)-RÆKKE(C2)+1)*(C2:H13=MAKS(C2:H13));7)

Formlen finder den største værdi i en beregnet matrice, placeret i cellerne C2 til H13. Derefter returneres den værdi, der står i I-kolonnen ud for den række, der indeholder den største værdi i den beregnede celle.

Eksempel 3
Af og til har man brug for at finde antallet af en bestemt ugedag mellem to givne datoer. Hvis vi forudsætter at Startdato står i A1 og Slutdato i A2 kan denne formel løse opgaven:

=SUMPRODUKT(--(UGEDAG(RÆKKE(INDIREKTE(A1&":"&A2)))=3))

I dette tilfælde findes tirsdage, da 1 = Søndag, 2 = Mandag, 3 = Tirsdag osv. Ugedagen repræsenteres af det sidste 3-tal i formlen. Funktionen virker ved, at INDIREKTE (virtuelt) udfylder rækker med alle datoer mellem start og slutdato. For hver "række" med en dato, undersøges rækkenummeret, og om det er en tirsdag. Derefter optælles alle de, der opfylder begge betingelser (rækkenummer og tirsdag). Da der i Excel 2003 kun er 65536 rækker, kan der ikke tælles længere end til 5. juni 2079, hvilket svarer til dag nummer 65535 (se evt. artiklen om Dato- og tidsberegninger.

- Retur til Excel -