Gennem mit arbejde som instruktør har jeg bemærket en store interesse for funktionen SUMPRODUKT hos mine kursister. Det kan jeg godt forstå, da det er en funktion med mange muligheder. Lad mig illustrere nogle af mulighederne for dig nedenfor:
Til at starte med er funktionen udviklet til at beregne produktet af to områder. Det vil sige tallene i to områder ganget med hinanden og alle resultaterne derefter summeret.
=2*2+8*2+2*2+7*9+7*6+5*8 osv.
eller
=SUMPRODUKT(B2:D9;G2:I9)
Dette er SUMPRODUKT funktionens egentligt formål - men den kan, som sagt, bruges til mange andre spændende ting!
Et godt eksempel er, som erstatning for SUM.HVIS funktionen. Denne funktion har flere svagheder, især når du benytter den på tværs af filer.
Ved arbejde på tværs af filer, bliver SUM.HVIS funktionen umulig at arbejde med. Dette var oprindeligt grunden til at jeg kiggede i andre retninger, og fandt alternative muligheder i SUMPRODUKT.
SUM.HVIS kræver blandt andet at begge filer er åbne, for at kunne opdatere beregningen! Men denne problemstilling har du ikke hvis du bruger SUMPRODUKT i stedet.
Lad os kigge lidt på et datasæt:
Med SUM.HVIS kan jeg nu beregne salget for byen ”Vejle” ved at bygge følgende formel:
=SUM.HVIS(D:D;"Vejle";G:G)
Denne formel kan dog også skrives med SUMPRODUKT, og derved sikrer vi at formlen fungerer bedre ved referencer til andre Excel filer:
=SUMPRODUKT((D:D="Vejle")+0;G:G)
På denne måde laver jeg en matrixformel med SUMPRODUKT. Matrix funktionaliteten bliver blandt andet skabt ved tilføjelsen ”+0” i formlen. Der er flere varianter, som kan være meget interessante.
Det skal nævnes at SUM.HVIS og SUM.HVISER er mere effektive funktioner fx i forhold til [lW1] hvor tung beregningen er i Excel – kaldet Calculation Load. SUMPRODUKT er bare væsentligt mere alsidig.
Et af eksemplerne på denne større alsidighed er, at du kan bruge formler i SUMPRODUKT - det kan du ikke i en SUM.HVISER. Se de to eksempler illustreret nedenfor:
Først en SUM.HVISER, der summerer alt, der er dateret i 2007 i G kolonnen:
=SUM.HVISER(G2:G100;A2:A100;">=" & "01-01-2007";A2:A100;"<=" & "31-12-2007")
Dernæst den samme beregning med SUMPRODUKT, hvor jeg bruger funktionen ÅR til at definere årstallet:
=SUMPRODUKT((ÅR(A2:A100)=2007)+0;G2:G100)
Igen bruger jeg ”+0” for at skabe matrixen, men dette kunne også udføres ved at gange de to dele med hinanden, så den ser ud som nedenfor:
=SUMPRODUKT((ÅR(A2:A100)=2007)*G2:G100)
I dette tilfælde kunne du endda bare bruge SUM funktionen, så det ender med at se sådan her ud:
=SUM((ÅR(A2:A100)=2007)*G2:G100)
Her skaber jeg en MATRIX funktion, men uden at skulle huske at trykker CTRL+ENTER! Hvad man jo normalt gør ved en matrixformel.
Et andet udmærket eksempel, på SUMPRODUKT funktionens fleksibilitet, er at den også kan lave ELLER udtryk. Se eksemplet nedenfor:
=SUMPRODUKT(((B2:B100="Bogart")+(B2:B100="DVD Land"))*(C2:C100="Kim Lund")*(G2:G100))
Plus-stykket I starten af formlen skaber en ELLER betingelse i funktionen.
Men der er også nogle ulemper, som skal nævnes i denne her forbindelse. Hvilket betyder at SUM.HVISER stadig er meget relevant funktion:
- SUM.HVISER koster omkring 30% mindre beregningsressourcer i Excel, end SUMPRODUKT
- SUM.HVISER kan arbejde med referencer til hele kolonner, fx A:A i stedet for A1:A999, uden at dette koster ekstra ressourcer i beregningen
- SUM.HVISER kan ignorere celler i beregningskolonnen som indeholder tekst. Dette ville få SUMPRODUKT til at fejle
Når dette så er skrevet, så er der ræson i at undersøge alle de nye muligheder for matrixformler i Office 365! Der er sket en hel masse og der er mange nye og spændende muligheder!
Her hos 4D A/S kan vi tilbyde dig konsulentassistance, således at du får en konsulent ud, der kan hjælpe med at løse specifikke opgaver. Vi har også en række Excel kurser, som vil give dig overblik og indsigt til at selv kunne optimere dine regneark og løsninger.
+45 33 14 71 44
