Förstå PRODUKTSUMMA – möjligheternas funktion

PRODUKTSUMMA (SUMPRODUCT) är en kraftfull funktion i Excel som multiplicerar motsvarande komponenter i angivna matriser och returnerar summan av dessa produkter. I denna post visar jag hur PRODUKTSUMMA kan användas för att lösa en rad olika beräkningsproblem, från att räkna intäkter till att beräkna viktad kapitalkostnad (WACC). Jag visar också hur PRODUKTSUMMA kan användas som ersättning för en massa andra funktioner som SUMMA.OM, SUMMA.OMF, ANTAL.OM, ANTAL.OMF och till och med iställer för LETARAD och XLETAUPP. Längst ner i denna bloggpost hittar du en länk till den arbetsbok jag använder för mina exempel och som underlag för min film om detta på YouTube.

PRODUKTSUMMA (SUMPRODUCT) är en funktion i Excel som multiplicerar motsvarande komponenter i angivna matriser och returnerar summan av dessa produkter. Den grundläggande syntaxen är:

Syntax

=PRODUKTSUMMA(matris1; [matris2]; [matris3]; …)

Varje matris måste ha samma dimensioner, och funktionen multiplicerar motsvarande poster i matriserna tillsammans och summerar sedan dessa produkter. Omman bara anger en matris så summerar funktionen uttrycken i denna. Till matrisreferensen kan man lägga olika beräkningar och filter.

Exempel 1: Grunderna med PRODUKTSUMMA

Här är ett klassiskt exempel på hur PRODUKTSUMMA kan användas.

Vi har en tabell med antal sålda produkter (B2:B6) och pris per styck (C2:C6).

Med PRODUKTSUMMA (gul cell) kan vi räkna ut summa intäkter med en enda funktion.  =PRODUKTSUMMA(B2:B6;C2:C6) tar första värdet i matris  (B2) och multiplicerar detta med första värdet i matris2 (C2) detta adderas med B3 * C3, B4 * C4 osv ner till B6*C6. Med andra ord: PRODUKTSUMMA adderar produkterna av de två matriserna.

Alternativet till PRODUKTSUMMA hade varit att göra denna beräkning i två steg. Förs multiplicera varje antal med pris (det gröna området) och därfter summera detta med en SUMMA-funktion (röd cell, E8).

Exempel 2: Beräkna viktad kapitalkostnad (WACC) med produktsumma

Vid företagsvärdering används ofta viktad kapitalkostnad (WACC) som diskonteringsränta när man nuvärdesberäknar kassaflöden.

WACC beräknas genom att man tar hänsyn till både kostnaden för eget kapital och kostnaden för lånat kapital (skulder). Man väger sedan samman dessa kostnader baserat på hur stor andel av företagets kapitalstruktur de utgör.

I exemplet nedan så utgör eget kapital 80% av företagets kapital och skulder 20%. Kostnaden för eget kapital är 10 procent och kostnaden för skulder är 4 procent. Den viktade kapitalkostnaden blir då: 0,80,1+0,20,04 = 8,80 %

Med funktionen PRODUKTSUMMA kan detta räknas ut direkt:

Formel i cell D5 (gul cell): =PRODUKTSUMMA(C2:C3;D2:D3)

Alternativet till detta hade varit att först räkna ut andel * kostnad i en kolumn och sedan summera resultatet av denna beräkning, dvs

Exempel med tre matriser

Alla matriser i PRODUKTSUMMA multiplicerar och adderas. I PRODUKTSUMMA kan man ha upp till 255 matrisargument.

Här är ett exempel med tre matrisargument:

1. I tabellen har vi antal sålda enheter, pris per styck och vilken rabatt kunden kommer få på det totala inköpet.
2. I kolumn D räknar vi först ut totalt inköp (antal sålda * pris per styck).
3. I kolumn E räknar vi ut inköp efter rabatt genom att multiplicera värdet i kolumn D med 1 minus rabatt, t.ex. för rad 2: 100 * (1-0,05) ger 95.
4. Slutligen summerar vi kolumn E för att komma fram till total försäljning efter rabatt.

Alla dessa steg kan lösas med följande PRODUKTSUMMA:
=PRODUKTSUMMA(A2:A6;B2:B6;1-C2:C6)

• Matris 1: Antal sålda enheter (A2:A6)
• Matris 2: Pris per styck (B2:B6)
• Matris 3: 1 minus rabatten i kolumn C (1-C2:C6)).

När alla tre matriser multipliceras får vi total försäljning efter rabatt (648,05).

 Hur PRODUKTSUMMA kan användas som ersättare för andra funktioner

Även om grundfunktionen med PRODUKTSUMMA är att den multiplicerar och summerar tal i matriser så kan funktionen lätt manipuleras att utföra en mäng olika åtgärder som man annars använder andra funktioner till. Till exempel kan man med PRODUKTSUMMA göra samma sak som man kan göra med funktioner som SUMMA.OM och SUMMA.OMF, ANTAL.OM och ANTALOMF. Man kan till och med använda PRODUKTSUMMA i stället för LETARAD eller XLETAUPP.

Varför skulle man då använda PRODUKTSUMMA när det finns andra funktioner som gör samma sak. Ja, för de flesta så finns det kanske ingen anledning till att krångla till det (om man nu anser att använda en enda funktion i stället för flera är att krångla till det). Men jag ser framför allt tre anledningar:

• Flexibilitet: Den kan anpassas till en mängd olika beräkningar genom att justera villkoren och värdena som används. Detta gör den till ett kraftfullt verktyg för att tackla en bred variation av problemställningar. Dessutom erbjuder den fördelen att kunna hantera komplexa och mångsidiga villkor och beräkningar inom samma formel.
• Kompakthet: En enda PRODUKTSUMMA-funktion kan ersätta flera separata funktioner, vilket resulterar i en kortare och mer överskådlig formel. Detta förbättrar läsbarheten och underhållbarheten av kalkylblad.
• Effektivitet: PRODUKTSUMMA kan vara mer effektiv än att använda separata funktioner, speciellt vid beräkningar på stora datamängder. Detta beror på att den utför beräkningen i ett enda steg, vilket kan spara tid och resurser.

Funktionalitet som SUMMA.OMF

SUMMA.OMF (och SUMMA.OM) summerar värden i ett intervall som uppfyller ett villkor specificerat i ett annat intervall. Skillnaden mellan SUMMA.OMF och SUMMA.OM är att SUMMA.OMF har möjligheten att använda flera villkor.

Med PRODUKTSUMMA kan uppnå samma sak genom att använda matriser inom sina argument för att skapa villkor:

I cell C11 (grön cell) använder jag SUMMA.OMF för att summera försäljningen av äpplen. Summaområdet B2:B7 summerar de rader där ”Äpple” förekommer i villkorsområdet A2:A7.

I gul cell C13 använder jag PRODUKTSUMMA för att utföra samma beräkning som med SUMMA.OMF.

=PRODUKTSUMMA((A2:A7=”Äpple”)*B2:B7)

Förklaring:

• Villkorsutvärdering: (A2:A7 = ”Äpple”) skapar en matris av booleska värden (SANT eller FALSKT) där varje cell i området A2:A7 jämförs med värdet ”Äpple”. Resultatet blir en matris av 1:or och 0:or, där 1 representerar ”SANT” (dvs. när cellen är ”Äpple”).
• Multiplicering: Denna booleska matris multipliceras sedan elementvis med värdena i B2:B7. När en produkt i A-kolumnen inte är ”Äpple”, multipliceras dess försäljningsvärde med 0, vilket resulterar i 0. När produkten är ”Äpple”, multipliceras dess försäljningsvärde med 1, vilket behåller det ursprungliga värdet.
• Summering: PRODUKTSUMMA summerar sedan alla produkterna från steg 2, vilket ger den totala försäljningen för ”Äpple”.

Detta exempel visar hur PRODUKTSUMMA kan användas flexibelt för att utföra villkorsstyrd summering, likt funktionen SUMMA.OMF eller SUMMA.OM, med fördelen av att kunna hantera mer komplexa och mångsidiga villkor och kalkyler inom samma formel.

Funktionalitet som ANTAL.OM

ANTAL.OM räknar hur många värden i ett intervall som uppfyller ett visst villkor.

Med PRODUKTSUMMA kan uppnå samma sak genom att använda matriser inom sina argument för att skapa villkor:

I cell B11 (grön cell) använder jag ANTAL.OMF för att räkna ut förekomsten av ”Äpple” i området A2:A7.

I gul cell B13 använder jag PRODUKTSUMMA för att utföra samma beräkning som med ANTAL.OM.

Förklaring av =PRODUKTSUMMA(–(A2:A7=”Äpple”))

1. Villkorsutvärdering: (A1:A6 = ”Äpple”) skapar en boolesk matris där varje cell i området A1 till A6 jämförs med strängen ”Äpple”. Resultatet är en matris av booleska värden (SANT/FALSKT).
2. Konvertering till tal: Operatorn — är en dubbel negation som omvandlar booleska värden (SANT/FALSKT) till numeriska värden (1/0). SANT blir 1 och FALSKT blir 0.
3. Summering: PRODUKTSUMMA summerar sedan alla värden i den numeriska arrayen. Eftersom varje ”SANT” har omvandlats till en 1, blir summan antalet gånger ”Äpple” förekommer i kolumnen.

I Excel är booleska värden som SANT och FALSKT primärt avsedda för logiska tester och villkor, inte direkt för aritmetiska beräkningar. När du försöker utföra en aritmetisk operation som addition eller multiplikation på ett booleskt värde direkt, hanterar Excel dessa som om de vore text. Men genom att lägga till den dubbla negationen så kringgår du detta och gör SANT/FALSKT till 1 eller 0. Om du bara hade ett minustecken så blir svaret i exemplet ovan minus 3, men med dubbla negationer så får du ett korrekt svar, dvs 3.

Det är därför det är viktigt att inkludera — när du använder PRODUKTSUMMA för att räkna booleska uttryck i Excel. Detta steg säkerställer att dina booleska värden behandlas som numeriska data, vilket möjliggör korrekt aritmetisk beräkning i funktionen. Ett alternativ för en dubbel negation är att multiplicera uttrycket med 1 och du får samma resultat, dvs 1*(A2:A7=”Äpple”).

Funktionalitet som ANTAL.OMF

Skillnaden mellan ANTAL.OM och ANTAL.OMF är att man med ANTAL.OMF kan hantera flera villkor.

Även i detta fall kan PRODUKTSUMMA användas som en alternativ funktion.

I cell C12 (grön cell) använder jag ANTAL.OMF för att räkna ut förekomsten av ”Äpple” i området A2:A7 där försäljningen i B2:B7 överstiger 400 (2 gånger).

I gul cell C14 använder jag PRODUKTSUMMA för att utföra samma beräkning som med ANTAL.OMF.

Förklaring av =PRODUKTSUMMA((A2:A7 = ”Äpple”) * (B2:B7 > 400))

• Villkorsutvärderingar:
  • (A2:A7 = ”Äpple”) skapar en boolesk matris där varje cell i området A2 till A7 jämförs med strängen ”Äpple”. Resultatet är en matris av booleska värden (SANT/FALSKT).
  • (B2:B7 > 400) skapar en annan boolesk matris där varje cell i området B2 till B7 testas om den är större än 400. Återigen, resultatet är en matris av booleska värden.
  • Elementvis multiplikation av booleska matriser:
  • De två booleska matriserna multipliceras elementvis. Endast när båda villkoren är uppfyllda (produkt är ”Äpple” och försäljningen är över 400 SEK) kommer produkten av motsvarande element att vara 1 (SANT * SANT = SANT, konverterat till 1); annars blir det 0.
• Summering:
  • PRODUKTSUMMA summerar sedan alla produkterna från den elementvisa multiplikationen. Resultatet blir antalet gånger som både produktkriteriet och försäljningskriteriet är uppfyllda.

Genom att använda PRODUKTSUMMA på detta sätt kan du effektivt ersätta ANTAL.OMF för scenarier som kräver flera villkor. Detta ger en kraftfull flexibilitet i datanalys och rapportering där mer komplexa villkor måste tillämpas.

Funktionalitet som XLETAUPP (eller LETARAD)

Att använda PRODUKTSUMMA för att efterlikna funktionen XLETAUPP (XLOOKUP på engelska) i Excel kan vara lite mer komplicerat än att bara använda XLETAUPP direkt, eftersom PRODUKTSUMMA inte naturligt hanterar uppslag av icke-numeriska värden eller ger alternativa resultat när inga matchningar hittas. Men, det är möjligt att använda PRODUKTSUMMA för att utföra enkla uppslag när du arbetar med numeriska data. Låt oss gå igenom ett exempel där vi vill hitta ett specifikt numeriskt värde baserat på ett matchande villkor i en annan kolumn.

I tabellen har vi produkter och pris. Antag att vi vill leta upp priset för en viss produkt (Päron).

Med funktionen =XLETAUPP(B9;A2:A6;B2:B6) så får vi svaret (gul cell). Funktionen letar efter värdet i B9 (Päron) i kolumnen A2:A6 och returnerar det första värdet på samma rad från kolumn B2:B6.

Med PRODUKTSUMMA kan vi lösa samma problem:

=PRODUKTSUMMA((A2:A6 = ”Päron”) * B2:B6)

Här multipliceras SANT (1) / FALSKT (0) med motsvarande värde i B2:B6.

Här finns dock två stora skillnader mellan XLETAUPP och PRODUKTSUMMA. XLETAUPP letar upp det första värde i tabellen som motsvarar värdet (eller det sista) samt att XLETAUPP även kan returnera textvärden.

Om det finns flera likadana värden (t.ex. ”Äpple” eller ”Banan” i tabellen) så kommer PRODUKTSUMMA att summera priset.

=PRODUKTSUMMA((A2:A6 = ”Äpple”) * B2:B6) kommer t.ex. ge svaret 24.

Ett alternativ här kan vara att returnera det genomsnittliga priset:

=PRODUKTSUMMA((A2:A6 = ”Äpple”) * B2:B6) / PRODUKTSUMMA(–(A2:A6 = ”Äpple”)) kommer t.ex. returnera värdet 12.

Här delar vi summan av alla pris med antal förekomster av ”Äpple” i tabellen, dvs PRODUKTSUMMA(–(A2:A6 = ”Äpple”).

Detta skulle tekniskt sett ge dig genomsnittspriset för ”Äpple”, inte priset på den första förekomsten. För att strikt hitta det första värdet, skulle en mer komplex matris-formel krävas, eventuellt kombinerat med andra funktioner som INDEX och MATCH, vilket gör XLETAUPP mycket mer lämplig för ändamålet.

PRODUKTSUMMA kan såldes egentligen bara användas som ersättare för XLETAUPP om:

• Att ”letauppvärdet” är unikt (dvs det förekommer inga dubbletter).
• Att det värde som skall returneras är ett tal – annars kommer man få ”0” som svar.

Slutsats

PRODUKTSUMMA kan användas för numeriska uppslag men är inte ett optimalt verktyg för att exakt replikera XLETAUPP, särskilt när det kommer till att hantera textbaserade uppslag eller krav på att returnera alternativa resultat vid icke-matchningar. För dessa ändamål är XLETAUPP mycket mer effektiv och direkt i sin tillämpning.

Förutom de som nämnts här kan man även använda PRODUKTSUMMA som ersättare för databasfunktionen DSUMMA, för att beräkna regressionskoefficienter (linjär regression), besvärliga matrisfunktioner och rent allmänt för att hantera beräkningar som involverar flera villkor över olika dimensioner

PRODUKTSUMMA är alltså ett kraftfullt verktyg i Excel som kan ersätta många funktioner under rätt omständigheter, vilket ofta kan förenkla arbetet genom att minska antalet använda formler och därmed förenkla modellbyggande och analys. Det är dock viktigt att komma ihåg att PRODUKTSUMMA främst hanterar numeriska data och kan kräva kreativa lösningar för att helt efterlikna andra mer specialiserade funktioner.

Film: Lär dig PRODUKTSUMMA (möjligheternas funktion!) och släng ut SUMMA.OM, ANTAL.OM och LETARAD!

Förstå PRODUKTSUMMA (möjligheternas funktion) och lär dig hur den till och med kan ersätta SUMMA.OM, ANTAL.OM och LETARAD / XLETAUPP i Excel för effektivare arbete. I filmens beskrivning finns tidsstämplar om du vill hoppa till något speciellt kapitel. Länk till arbetsboken jag använder finns längre ner på denna sida.

Underlag:

Här är excel-underlaget till min film (och denna post): produktsumma.xls