Hur man använder Excel SUMPRODUCT-funktionen

Innehållsförteckning

Sammanfattning

SUMPRODUCT-funktionen multiplicerar områden eller matriser tillsammans och returnerar produktsumman. Detta låter tråkigt, men SUMPRODUCT är en otroligt mångsidig funktion som kan användas för att räkna och summera som COUNTIFS eller SUMIFS, men med mer flexibilitet. Andra funktioner kan enkelt användas i SUMPRODUCT för att utöka funktionaliteten ytterligare.

Ändamål

Multiplicera, sedan summera matriser

Returvärde

Resultatet av multipla och summerade matriser

Syntax

= SUMPRODUCT (array1, (array2), …)

Argument

  • array1 - Den första arrayen eller intervallet som ska multipliceras och lägg sedan till.
  • array2 - (valfritt) Den andra arrayen eller intervallet som ska multipliceras och lägg sedan till.

Version

Excel 2003

Användningsanmärkningar

SUMPRODUCT-funktionen fungerar med matriser, men den kräver inte den normala array-syntaxen (Ctrl + Shift + Enter) för att ange. Syftet med SUMPRODUCT-funktionen är att multiplicera, sedan summera, matriser. Om bara en matris tillhandahålls summerar SUMPRODUCT helt enkelt objekten i matrisen. Upp till 30 matriser kan levereras.

När du först möter SUMPRODUCT kan det verka tråkigt, komplext och till och med meningslöst. Men SUMPRODUCT är en otroligt mångsidig funktion med många användningsområden. Eftersom den kommer att hantera arrays graciöst och utan klagomål kan du använda den för att bearbeta cellområden på smarta, eleganta sätt (se länkar till formelexempel på denna sida).

För att illustrera hur SUMPRODUCT fungerar, här är några vanliga exempel.

SUMPRODUCT för villkorade summor och räkningar

Antag att du har en del orderdata i A2: B6, med tillstånd i kolumn A, försäljning i kolumn B:

A B
1 stat Försäljning
2 UT 75
3 CO 100
4 TX 125
5 CO 125
6 TX 150

Med SUMPRODUCT kan du räkna den totala försäljningen för Texas ("TX") med denna formel:

=SUMPRODUCT(--(A2:A6="TX"))

Och du kan summera den totala försäljningen för Texas ("TX") med denna formel:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Obs! Förväxla inte det dubbla negativa. Detta är ett vanligt trick som används i mer avancerade Excel-formler för att tvinga Sanna och FALSKA värden till 1 och 0. Se mer nedan …

För summaxemplet ovan är här en virtuell representation av de två matriserna som de först bearbetades av SUMPRODUCT:

array1 array2
FALSK 75
FALSK 100
SANN 125
FALSK 125
SANN 150

Varje matris har 5 objekt. Den första matrisen innehåller SANT / FALSKA värden som härrör från uttrycket A2: A6 = "TX", och den andra matrisen är innehållet i B2: B6. Varje artikel i den första matrisen multipliceras med motsvarande artikel i den andra matrisen. I nuvarande tillstånd kommer emellertid resultatet av SUMPRODUCT att vara noll eftersom SANT och FALSE-värden kommer att behandlas som noll. Vi behöver objekten i array1 vara numeriska - de måste "tvingas" till enar och nollor. Det är här det dubbelnegativa kommer in.

Genom att använda den dubbla negativa - (dubbel unary, för er tekniska typer) kan vi tvinga SANT / FALSKT till de numeriska värdena en och noll, som visas i den virtuella representationen nedan. Den sista kolumnen "Produkt" representerar resultatet av att multiplicera de två matriserna tillsammans. Det summerade resultatet, 275, är det värde som SUMPRODUCT returnerar.

array1 array2 Produkt
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Belopp 275

Med hjälp av den lockiga stagssyntaxen för matriser ser exemplet ut så här efter tvång:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))

och så här efter multiplikation:

=SUMPRODUCT((0,0,125,0,150))

Detta exempel utökar idéerna ovan med mer detaljer.

SUMPRODUKT med andra funktioner

SUMPRODUCT kan använda andra funktioner direkt. Du kanske ser SUMPRODUCT används med LEN-funktionen för att räkna totalt tecken i ett intervall, eller med funktioner som ISBLANK, ISTEXT, etc. Dessa är normalt inte matrisfunktioner, men när de får ett intervall skapar de en "resultatmatris". Eftersom SUMPRODUCT är byggd för att fungera med matriser kan den utföra beräkningar på matriserna direkt. Detta kan vara ett bra sätt att spara utrymme i ett kalkylblad genom att eliminera behovet av en "hjälpar" -kolumn.

Antag till exempel att du har 10 olika textvärden i A1: A10 och att du vill räkna de totala tecknen för alla 10 värdena. Du kan lägga till en hjälpkolumn i kolumn B som använder följande formel: LEN (A1) för att beräkna tecknen i varje cell. Då kan du använda SUM för att lägga till alla 10 siffrorna. Med SUMPRODUCT kan du dock skriva en formel så här:

=SUMPRODUCT(LEN(A1:A10))

När det används med ett intervall som A1: A10 returnerar LEN en matris med 10 värden. Då summerar SUMPRODUCT helt enkelt alla värden och returnerar resultatet, utan någon hjälparpelare.

Se exempel nedan på många andra sätt att använda SUMPRODUCT.

Anmärkningar:

  1. SUMPRODUCT behandlar icke-numeriska objekt i matriser som nollor.
  2. Arrayargument måste ha samma storlek. Annars genererar SUMPRODUCT en #VALUE! felvärde.
  3. Logiska tester i matriser skapar SANTA och FALSKA värden. I de flesta fall vill du tvinga dessa till 1 och 0.
  4. SUMPRODUCT kan ofta använda resultatet av andra funktioner direkt (se formelexempel nedan)

Intressanta artiklar...