Ladda ner exempelarbetsboken
Denna handledning visar hur du använder Excel SUMPRODUCT -funktion i Excel.
SUMPRODUCT Funktionsöversikt
SUMPRODUCT -funktionen multiplicerar matriser med siffror och summerar den resulterande matrisen.
Om du vill använda funktionen SUMPRODUCT Excel -kalkylblad väljer du en cell och skriver:
(Lägg märke till hur formelinmatningarna ser ut)
SUMPRODUCT -funktion Syntax och ingångar:
| 1 | = SUMPRODUCT (array1, array2, array3) |
array1 - Matriser med siffror.
Vad är SUMPRODUCT -funktionen?
SUMPRODUCT -funktionen är en av de mer kraftfulla funktionerna i Excel. Det är namn, kan få dig att tro att det bara är avsett för grundläggande matematiska beräkningar, men det kan användas för så mycket mer.
Matriser
SUMPRODUCT kräver inmatningar av matriser.
Så först, vad menar vi med "array"? En array är enkel en grupp objekt (ex. Nummer) arrangerade i en specifik ordning, precis som ett cellintervall. Så om du hade siffrorna 1, 2, 3 i cellerna A1: A3, skulle Excel läsa detta som array {1,2,3}. Faktum är att du kan ange {1,2,3} direkt i Excel -formler så känner den igen matrisen.
Vi kommer att prata mer om matriser nedan, men låt oss först titta på ett enkelt exempel.
Grundläggande matematik
Låt oss titta på ett grundläggande exempel på SUMPRODUCT och använda det för att beräkna total försäljning.
Vi har vårt produktbord och vi vill beräkna den totala försäljningen. Du frestas att bara lägga till en ny kolumn, ta mängden sålt * pris och summera sedan den nya kolumnen. Istället kan du dock helt enkelt använda SUMPRODUCT -funktionen. Låt oss gå igenom formeln:
| 1 | = SUMPRODUKT (A2: A4, B2: B4) |
Funktionen laddar antalet intervall i grupper, multiplicerar dem mot varandra och summerar sedan resultaten:
| 1234 | = SUMPRODUKT ({100, 50, 10}, {6, 7, 5})= SUMPRODUKT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUKT ({600, 350, 50}= 1000 |
SUMPRODUCT Funciton kunde multiplicera alla siffror för oss OCH göra summeringen.
Vägt genomsnitt
Ett annat fall där det är bra att använda SUMPRODUCT är när du behöver beräkna ett vägt genomsnitt. Detta inträffar oftast när det gäller skolarbete, så låt oss överväga följande tabell.
Vi kan se hur mycket frågesporter, tester och läxor är värda för det övergripande betyget, liksom vad det nuvarande genomsnittet är för varje enskild artikel. Vi kan beräkna det totala betyget sedan genom att skriva
| 1 | = SUMPRODUKT (B2: B4, C2: C4) |
Vår funktion multiplicerar igen varje objekt i matriserna innan summan summeras. Det här fungerar så
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUKT ({22%, 45%, 19%})= 86% |
Flera kolumner
En annan plats vi kan använda SUMPRODUCT är med ännu fler kolumner som alla måste multipliceras mot varandra. Låt oss titta på ett exempel där vi behöver beräkna volymen i timmer.
I stället för att skapa en hjälparkolumn för att beräkna den totala försäljningen för varje rad, kan vi göra detta med en enda formel. Vår formel kommer att vara
| 1 | = SUMPRODUKT (B2: B5, C2: C5, D2: D5) |
Varje arrays första objekt multipliceras mot varandra (t.ex. 4 * 2 * 1 = 8). Sedan 2: a (4 * 2 * 2 = 16) och 3rd, etc. Sammantaget kommer detta att producera en rad produkter som ser ut som {8, 16, 16, 32). Då skulle den totala volymen vara summan av den matrisen, 72.
Ett kriterium
Okej, låt oss lägga till ytterligare ett lager av komplexitet. Vi har sett att SUMPRODUCT kan hantera siffror, men vad händer om vi vill kontrollera om det finns kriterier? Tja, du kan också skapa matriser för booleska värden (booleska värden är värden som är SANT eller FALSKA).
Ta till exempel en grundläggande matris {1, 2, 3}. Låt oss skapa en motsvarande matris som anger om varje tal är större än 1. Denna matris skulle se ut som {FALSE, TRUE, TRUE}.
Detta är oerhört användbart i formler, eftersom vi enkelt kan konvertera SANT / FALSKT till 1 / 0. Låt oss titta på ett exempel.
Med hjälp av tabellen nedan vill vi beräkna "Hur många sålda enheter var röda?"
Vi kan göra det med denna formel:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Röd")) |
"Vänta! Vad är det med den dubbla minussymbolen där? " du säger. Kommer du ihåg hur jag sa att vi kunde konvertera från sant/falskt till 1/0? Vi gör detta genom att tvinga datorn att utföra en matematisk operation. I det här fallet säger vi "ta det negativa värdet och ta sedan det negativa igen". När vi skriver ut det kommer vår grupp att förändras så här:
| 123 | {Sant, sant, falskt}{-1, -1, 0}{1, 1, 0} |
Så, tillbaka till hela SUMPRODUCT -formeln, det kommer att laddas i våra matriser och sedan multiplicera, så här
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Notera hur 3rd objektet blev 0, eftersom allt multiplicerat med 0 blir noll.
Flera kriterier
Vi kan ladda upp till 255 matriser i vår funktion, så vi kan säkert ladda in fler kriterier. Låt oss titta på detta större bord där vi har lagt till den sålda månaden.
Om vi vill veta hur många artiklar som såldes var röda och var i februari månad, kunde vi skriva vår formel som
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Röd"), -(C2: C4 = "Feb")) |
Datorn skulle sedan utvärdera våra matriser och multiplicera över. Vi har redan täckt hur True/False -arrays ändras till 1/0, så jag hoppar över det steget för tillfället.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Vi hade bara en rad i vårt exempel som matchade alla kriterier, men med verklig data kan du ha lagt till flera rader som du behövde läggas ihop.
Komplexa kriterier
Okej, fram till nu är du kanske inte imponerad eftersom alla våra exempel kunde ha gjorts med andra funktioner som SUMIF eller COUNTIF. Nu ska vi göra något de andra funktionerna kan inte do. Tidigare hade vår månadskolumn de faktiska namnen på månader. Tänk om det istället hade datum?
Vi kan inte göra en SUMIF nu, eftersom SUMIF inte kan hantera de kriterier vi behöver. SUMPRODUCT kan dock hantera oss att manipulera arrayen och göra ett djupare test. Vi har redan manipulerat matriser när vi har översatt det sanna/falska till 1/0. Vi kommer att manipulera denna array med MONTH -funktionen. Här är hela formeln vi ska använda
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Röd"), -(MÅNAD (C2: C4) = 2)) |
Låt oss titta på 3rd array närmare. Först kommer vår formel att extrahera månadsnumret från varje datum i C2: C4. Detta ger oss {1, 2, 2}. Därefter kontrollerar vi om värdet är lika med 2. Nu ser vår array ut som {Falskt, sant, sant}. Vi gör det dubbla minuset igen och vi har {0, 1, 1}. Vi är nu tillbaka på en liknande plats som vi hade i exempel 3, och vår formel kommer att kunna berätta att det såldes 50 enheter i februari som var röda.
Dubbel minus kontra att multiplicera
Om du har sett SUMPRODUCT -funktionen tidigare har du kanske sett en något annorlunda notation. Istället för att använda ett dubbel minus kan du skriva
| 1 | = SUMPRODUCT (A2: A4*(B2: B4 = "Röd")*(MÅNAD (C2: C4) = 2)) |
Formeln kommer fortfarande att fungera på samma sätt, vi berättar bara manuellt för datorn att vi vill multiplicera matriserna. SUMPRODUCT tänkte göra detta ändå, så det är ingen förändring där hur matematiken fungerar. Genom att utföra matematiken omvandlas vår sanna/falska till 1/0 samma. Så varför skillnaden?
För det mesta spelar det ingen roll för mycket, och det beror på användarens preferenser. Det finns dock minst ett fall där multiplikation behövs.
När du använder SUMPRODUCT förväntar datorn sig att alla argument (array1, array2, etc.) ska ha samma storlek. Det betyder att de har samma antal rader eller kolumner. Du kan dock göra det som är känt som en tvådimensionell matrisberäkning med SUMPRODUCT som vi kommer att se i nästa exempel. När du gör det har matriserna olika storlekar, så vi måste kringgå den "alla samma storleken" -kontrollen.
Två dimensioner
Alla tidigare exempel hade våra matriser i samma riktning. SUMPRODUCT kan hantera saker som går åt två håll, som vi kommer att se i nästa tabell.
Här är vår tabell över sålda enheter, men data omarrangeras där kategorier går överst. Om vi vill ta reda på hur många objekt som var röda och i kategori A kan vi skriva
| 1 | = SUMPRODUCT ((A2: A4 = "Röd")*(B1: C1 = "A")*B2: C4) |
Vad händer här?? Det visar sig att vi kommer att multiplicera i två olika riktningar. Att visualisera detta är svårare att göra med bara en skriven mening, så vi har några bilder som hjälper oss. Först kommer våra radkriterier (är det rött?) Att multiplicera över varje rad i gruppen.
| 1 | = SUMPRODUCT ((A2: A4 = "RÖD")*B2: C4) |
Därefter kommer kolumnkriterierna (är det kategori A?) Att multiplicera ner varje kolumn
| 1 | = SUMPRODUCT ((A2: A4 = "Röd")*(B1: C1 = "A")*B2: C4) |
Efter att båda dessa kriterier har gjort sitt arbete, är de enda icke-nollorna kvar 5 och 10. SUMPRODUCT ger oss totalt 15 som vårt svar.
Kommer du ihåg hur vi pratade om att matriserna måste ha samma storlek om du inte gör två dimensioner? Det var delvis korrekt. Tittar igen på de matriser vi använde i vår formel. De höjd av två av våra matriser är desamma, och bredd av två av våra matriser är desamma. Så du måste fortfarande se till att saker och ting stämmer korrekt, men du kan göra det i olika dimensioner.
Två dimensioner och komplexa
Många gånger presenteras vi med data som inte är i den bästa layout som passar våra formler. Vi kan försöka omorganisera det manuellt, eller så kan vi vara smartare med våra formler. Låt oss överväga följande tabell.
Här har vi data för våra artiklar och försäljning blandade för varje månad. Hur skulle vi gå tillväga för att ta reda på hur många artiklar Bob har sålt under hela året?
För att göra detta använder vi ytterligare två funktioner: SÖK och ISNUMBER. SEARCH -funktionen kommer att låta oss leta efter vårt sökord ”objekt” i rubrikcellerna. Utmatningen från denna funktion kommer antingen med ett tal eller ett fel (om sökordet inte hittas). Sedan använder vi ISNUMBER för att konvertera den där utgång i våra booleska värden. Vår formel kommer att se ut nedan.
Du borde vara ganska bekant med den första matrisen nu. Det kommer att skapa en utdata som {0, 1, 0, 1}. Nästa kriterieuppsättning vi just pratade om. Det kommer att skapa ett nummer för alla celler med "Artiklar" i dem och ett fel för de andra {5, #N/A !, 5, #N/A!}. ISNUMBER omvandlar sedan detta till booleska {True, False, True, False}. När vi sedan multiplicerar kommer det bara att behålla värden från den första och tredje kolumnen. När alla matriser multiplicerar mot varandra är de enda icke-nolltal som vi har de markerade här:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (SÖK ("Artiklar", B1: E1))*B2: E5)) |
SUMPRODUCT kommer sedan att lägga till allt detta, och vi får vårt slutresultat på 29.
SUMPRODUCT Or
Många situationer uppstår där vi skulle vilja kunna summera värden om vår kriteriekolumn har ett värde ELLER ett annat värde. Du kan åstadkomma detta i SUMPRODUCT genom att lägga till två kriterier med varandra.
I det här exemplet vill vi lägga till enheter som säljs för både rött och blått.
Vår formel kommer att se ut så här
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Red")+(B2: B7 = "Blue")) |
Låt oss titta på den röda kriterien. Det kommer att producera en matris som ser ut så här: {1, 1, 0, 0, 0, 0}. Den blå kriterierna ser ut som {0, 0, 1, 0, 1, 0}. När du lägger till dem kommer den nya matrisen att se ut som {1, 1, 1, 0, 1, 0}. Vi kan se hur de två matriserna har smält ihop till en enda kriteriematris. Funktionen multiplicerar sedan det med vår första array, och vi får {100, 50, 10, 0, 75, 0}. Lägg märke till att värdena för Green har nollställts. SUMPRODUKTENS sista steg är att lägga ihop alla siffror för att nå vår lösning på 235.
Ett ord av försiktighet här. Var försiktig när kriterierna inte utesluter varandra. I vårt exempel kan värdena i kolumn B vara antingen röda eller blåa, men vi visste att det aldrig skulle kunna vara båda. Fundera på om vi hade skrivit denna formel:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "Blue")) |
Vår avsikt är att hitta blå objekt som såldes eller var i en mängd mer än 50. Dessa villkor är dock inte exklusiva, eftersom en enda rad kan vara både över 50 i kolumn A och vara blå. Detta skulle resultera i att den första kriteriematrisen ser ut som {1, 1, 0, 1, 1, 0}, varvid den andra kriteriematrisen är {0, 0, 1, 0, 1, 0}. Genom att lägga ihop dem skapades {1, 1, 1, 1, 2, 0}. Ser du hur vi har en tvåa där inne nu? Om den lämnas ensam skulle SUMPRODUCTEN dubbla värdet i den raden, ändra 75 till 150, och vi skulle få fel resultat. För att korrigera för detta placerar vi en yttre kriteriekontroll på vår array, så här:
| 1 | = SUMPRODUCT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "Blue")> 0)) |
Nu, efter att de två inre kriterierna har lagts ihop, kontrollerar vi om resultatet är större än 0. Detta blir av med de 2 vi hade tidigare, och istället har vi en array som {1, 1, 1 , 1, 1, 0} vilket ger rätt resultat.
SUMPRODUKT Exakt
De flesta funktioner i Excel är inte skiftlägeskänsliga, men ibland måste vi kunna leta med skiftlägeskänslighet i åtanke. När det önskade resultatet är numeriskt kan vi åstadkomma detta genom att använda EXAKT inuti SUMPRODUCT -funktionen. Tänk på följande tabell:
Vi vill hitta poängen för artikel “ABC123”. Normalt jämför EXAKT -funktionen två objekt och returnerar en boolsk utgång som anger om de två objekten är exakt det samma. Men eftersom vi är inne i en SUMPRODUCT, kommer vår dator att veta att vi har att göra med matriser och kommer att kunna jämföra ett objekt med varje objekt i en array. Vår formel kommer att se ut så här
| 1 | = SUMPRODUCT (-EXAKT ("ABC123", A2: A5), B2: B5) |
EXAKT -funktionen kontrollerar sedan varje objekt i A2: A5 för att se om det matchar värde och bokstav. Detta ger en matris som ser ut som {0, 1, 0, 0}. När den multipliceras mot B2: B5 blir matrisen {0, 2, 0, 0}. Efter den sista summeringen får vi vår lösning på 2.
SUMPRODUCT i Google Kalkylark
SUMPRODUCT -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
SUMPRODUCT Exempel i VBA
Du kan också använda SUMPRODUCT -funktionen i VBA. Typ: application.worksheetfunction.sumproduct (array1, array2, array3)
Utför följande VBA -uttalanden
| 1 | Range ("B10") = Application.WorksheetFunction.SumProduct (Range ("A2: A7"), Range ("B2: B7")) |
kommer att ge följande resultat
För funktionsargumenten (array1, etc.) kan du antingen ange dem direkt i funktionen eller definiera variabler som ska användas istället.
