Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de Excel SOMPRODUCT Functie in Excel.
SOMPRODUCT Functieoverzicht
De functie SOMPRODUCT Vermenigvuldigt reeksen getallen en telt de resulterende reeks op.
Om de SOMPRODUCT Excel-werkbladfunctie te gebruiken, selecteert u een cel en typt u:
(Let op hoe de formule-invoer verschijnt)
SOMPRODUCT-functie Syntaxis en invoer:
| 1 | =SOMPRODUCT(matrix1,matrix2,matrix3) |
array1 - Arrays van getallen.
Wat is de SOMPRODUCT-functie?
De SOMPRODUCT-functie is een van de krachtigere functies binnen Excel. De naam doet je misschien denken dat het alleen bedoeld is voor elementaire wiskundige berekeningen, maar het kan voor zoveel meer worden gebruikt.
Arrays
SOMPRODUCT vereist invoer van arrays.
Dus eerst, wat bedoelen we met "array"? Een array is eenvoudig een groep items (bijvoorbeeld getallen) die in een specifieke volgorde zijn gerangschikt, net als een reeks cellen. Dus als u de nummers 1, 2, 3 in cellen A1:A3 had, zou Excel dit lezen als array {1,2,3}. U kunt {1,2,3} zelfs rechtstreeks in Excel-formules invoeren en het zal de array herkennen.
We zullen hieronder meer over arrays praten, maar laten we eerst een eenvoudig voorbeeld bekijken.
Basis wiskunde
Laten we eens kijken naar een eenvoudig voorbeeld van SOMPRODUCT, waarmee we de totale verkoop berekenen.
We hebben onze producttabel en we willen de totale verkoop berekenen. U komt in de verleiding om gewoon een nieuwe kolom toe te voegen, de verkochte hoeveelheid * prijs te nemen en vervolgens de nieuwe kolom op te tellen. In plaats daarvan kunt u echter gewoon de SOMPRODUCT-functie gebruiken. Laten we de formule doornemen:
| 1 | =SOMPRODUCT(A2:A4,B2:B4) |
De functie laadt de reeksen getallen in arrays, vermenigvuldigt ze met elkaar en telt vervolgens de resultaten op:
| 1234 | =SOMPRODUCT({100, 50, 10}, {6, 7, 5})=SOMPRODUCT({100 * 6, 50 * 7, 10 * 5})=SOMPRODUCT({600, 350, 50}=1000 |
De SOMPRODUCT Funciton was in staat om alle getallen voor ons te vermenigvuldigen EN de sommatie te doen.
Gewogen gemiddelde
Een ander geval waarin het handig is om SOMPRODUCT te gebruiken, is wanneer u een gewogen gemiddelde moet berekenen. Dit komt het vaakst voor bij schoolwerk, dus laten we de volgende tabel eens bekijken.
We kunnen zien hoeveel de quizzen, tests en huiswerk waard zijn voor het totale cijfer, evenals wat het huidige gemiddelde is voor elk specifiek item. We kunnen dan het totaalcijfer berekenen door te schrijven
| 1 | =SOMPRODUCT(B2:B4, C2:C4) |
Onze functie vermenigvuldigt opnieuw elk item in de arrays voordat het totaal wordt opgeteld. Dit lukt zo
| 123 | =SOMPRODUCT({30%, 50%, 20%}, {73%, 90%, 95%})=SOMPRODUCT({22%, 45%, 19%})=86% |
Meerdere kolommen
Een andere plaats waar we SUMPRODUCT zouden kunnen gebruiken, is met nog meer kolommen die allemaal met elkaar moeten worden vermenigvuldigd. Laten we eens kijken naar een voorbeeld waarbij we het volume in stukken hout moeten berekenen.
In plaats van een hulpkolom te maken om de totale verkoop voor elke rij te berekenen, kunnen we dit doen met een enkele formule. Onze formule zal zijn:
| 1 | =SOMPRODUCT(B2:B5, C2:C5, D2:D5) |
De eerste items van elke array worden met elkaar vermenigvuldigd (bijvoorbeeld 4 * 2 * 1 = 8). Dan de 2e (4 * 2 * 2 = 16), en 3rd, enz. Over het algemeen zal dit de reeks producten produceren die eruitzien als {8, 16, 16, 32). Dan zou het totale volume de som van die array zijn, 72.
één criterium:
Oké, laten we nog een laag complexiteit toevoegen. We hebben gezien dat SUMPRODUCT arrays van getallen aankan, maar hoe zit het als we criteria willen controleren? Welnu, u kunt ook arrays maken voor Booleaanse waarden (Booleaanse waarden zijn waarden die WAAR of ONWAAR zijn).
Neem bijvoorbeeld een basisarray {1, 2, 3}. Laten we een corresponderende array maken die aangeeft of elk getal groter is dan 1. Deze array ziet eruit als {FALSE, TRUE, TRUE}.
Dit is erg handig in formules, omdat we TRUE / FALSE gemakkelijk kunnen converteren naar 1 / 0. Laten we een voorbeeld bekijken.
Met behulp van de onderstaande tabel willen we berekenen "Hoeveel verkochte eenheden waren rood?"
We kunnen dit doen met deze formule:
| 1 | =SOMPRODUCT(A2:A4, --(B2:B4="Rood")) |
"Vasthouden! Wat is daar met het dubbele minteken?” jij zegt. Weet je nog hoe ik zei dat we True/False konden omzetten in 1/0? Dit doen we door de computer te dwingen een wiskundige bewerking uit te voeren. In dit geval zeggen we "neem de negatieve waarde en neem dan opnieuw de negatieve". Als we dat uitschrijven, gaat onze array als volgt veranderen:
| 123 | {Waar, waar, niet waar}{-1, -1, 0}{1, 1, 0} |
Dus, terug naar de volledige SOMPRODUCT-formule, deze wordt in onze arrays geladen en vervolgens vermenigvuldigd, zoals dit
| 123 | =SOMPRODUCT({100, 50, 10}, {1, 1, 0})=SOMPRODUCT({100, 50, 0})=150 |
Let op hoe de 3rd item werd een 0, omdat alles vermenigvuldigd met 0 nul wordt.
Meerdere criteria
We kunnen tot 255 arrays in onze functie laden, dus we kunnen zeker meer criteria laden. Laten we eens kijken naar deze grotere tafel waar we de Maand hebben toegevoegd.
Als we willen weten hoeveel verkochte items rood waren en in de maand februari waren, konden we onze formule zo schrijven
| 1 | =SOMPRODUCT(A2:A4, --(B2:B4="Rood"), --(C2:C4="Feb")) |
De computer zou dan onze arrays evalueren en zich vermenigvuldigen. We hebben al besproken hoe True/False-arrays worden gewijzigd in 1/0, dus ik sla die stap voor nu over.
| 123 | =SOMPRODUCT({100, 50, 10}, {1, 1, 0}, {0, 1, 1})=SOMPRODUCT({0, 50, 0})=50 |
We hadden in ons voorbeeld slechts één rij die aan alle criteria voldeed, maar met echte gegevens had u mogelijk meerdere rijen die u bij elkaar moest optellen.
Complexe criteria
Oké, tot nu toe ben je misschien niet onder de indruk omdat al onze voorbeelden met andere functies zoals SUMIF of AANTAL.ALS hadden kunnen worden gedaan. Nu gaan we iets doen met die andere functies kan niet doen. Voorheen had onze Maand-kolom de werkelijke namen van maanden. Wat als het in plaats daarvan datums had?
We kunnen nu geen SUMIF maken, omdat SUMIF de criteria die we nodig hebben niet aankan. SUMPRODUCT kan ons echter aan om de array te manipuleren en een diepere test uit te voeren. We hebben arrays al gemanipuleerd toen we de True/False in 1/0 hebben vertaald. We gaan deze array manipuleren met de functie MAAND. Dit is de volledige formule die we gaan gebruiken
| 1 | =SOMPRODUCT(A2:A4, --(B2:B4="Rood"), --(MAAND(C2:C4)=2)) |
Laten we eens kijken naar de 3rd reeks nader. Ten eerste gaat onze formule het maandnummer extraheren van elke datum in C2:C4. Dit geeft ons {1, 2, 2}. Vervolgens controleren we of die waarde gelijk is aan 2. Nu ziet onze array eruit als {False, True, True}. We doen de dubbele min opnieuw, en we hebben {0, 1, 1}. We zijn nu terug op een vergelijkbare plek als in voorbeeld 3, en onze formule kan ons vertellen dat er in februari 50 eenheden zijn verkocht die rood waren.
Dubbel min vs. vermenigvuldigen
Als je de SOMPRODUCT-functie eerder in gebruik hebt gezien, heb je misschien een iets andere notatie gezien. In plaats van een dubbele min te gebruiken, kun je schrijven
| 1 | =SOMPRODUCT(A2:A4*(B2:B4="Rood")*(MAAND(C2:C4)=2)) |
De formule werkt nog steeds op dezelfde manier, we vertellen de computer alleen handmatig dat we de arrays willen vermenigvuldigen. SUMPRODUCT zou dit sowieso doen, dus er is geen verandering in hoe de wiskunde werkt. Het uitvoeren van de wiskundige bewerking converteert onze True/False naar 1/0 hetzelfde. Dus, waarom het verschil?
Meestal maakt het niet zoveel uit, en het komt neer op de voorkeur van de gebruiker. Er is echter ten minste één geval waarin vermenigvuldiging nodig is.
Wanneer u SOMPRODUCT gebruikt, verwacht de computer dat alle argumenten (array1, array2, enz.) dezelfde grootte hebben. Dit betekent dat ze hetzelfde aantal rijen of kolommen hebben. U kunt echter doen wat bekend staat als een tweedimensionale matrixberekening met SUMPRODUCT die we in het volgende voorbeeld zullen zien. Wanneer u dat doet, hebben de arrays verschillende groottes, dus we moeten die controle "allemaal dezelfde grootte" omzeilen.
Twee dimensies
In alle voorgaande voorbeelden gingen onze arrays in dezelfde richting. SUMPRODUCT kan dingen aan die in twee richtingen gaan, zoals we in de volgende tabel zullen zien.
Hier is onze tabel met verkochte eenheden, maar de gegevens zijn herschikt waar categorieën bovenaan komen. Als we willen weten hoeveel items rood waren en in categorie A, kunnen we schrijven:
| 1 | =SOMPRODUCT((A2:A4="Rood")*(B1:C1="A")*B2:C4) |
Wat is hier aan de hand?? Het blijkt dat we ons in twee verschillende richtingen gaan vermenigvuldigen. Dit visualiseren is moeilijker te doen met alleen een geschreven zin, dus we hebben een paar afbeeldingen om ons te helpen. Ten eerste zullen onze rijcriteria (is het rood?) zich vermenigvuldigen over elke rij in de array.
| 1 | =SOMPRODUCT((A2:A4="ROOD")*B2:C4) |
Vervolgens worden de kolomcriteria (is het categorie A?) in elke kolom vermenigvuldigd
| 1 | =SOMPRODUCT((A2:A4="Rood")*(B1:C1="A")*B2:C4) |
Nadat beide criteria hun werk hebben gedaan, zijn de enige niet-nullen die nog over zijn de 5 en 10. SOMPRODUCT geeft ons dan het totaal van 15 als ons antwoord.
Weet je nog hoe we het hadden over de arrays die dezelfde grootte moeten hebben, tenzij je twee dimensies doet? Dat klopte gedeeltelijk. Kijkt opnieuw naar de arrays die we in onze formule hebben gebruikt. De hoogte van twee van onze arrays is hetzelfde, en de breedte van twee van onze arrays zijn hetzelfde. Je moet er dus nog steeds voor zorgen dat de dingen correct worden uitgelijnd, maar je kunt het in verschillende dimensies doen.
Twee dimensies en complex
Vaak krijgen we gegevens te zien die niet de beste lay-out hebben die geschikt is voor onze formules. We kunnen proberen het handmatig te herschikken, of we kunnen slimmer zijn met onze formules. Laten we de volgende tabel eens bekijken.
Hier hebben we de gegevens voor onze artikelen en verkopen voor elke maand gemengd. Hoe zouden we te werk gaan om erachter te komen hoeveel items Bob het hele jaar heeft verkocht?
Om dit te doen, gebruiken we twee extra functies: ZOEKEN en ISNUMBER. Met de SEARCH-functie kunnen we zoeken naar ons trefwoord "items" in de koptekstcellen. De uitvoer van deze functie gaat naar een getal of een fout (als het trefwoord niet wordt gevonden). Vervolgens gebruiken we het ISNUMBER om te converteren Dat output naar onze Booleaanse waarden. Onze formule gaat er als volgt uitzien.
Je zou nu redelijk bekend moeten zijn met de eerste array. Het gaat een uitvoer maken zoals {0, 1, 0, 1}. De volgende criteriareeks waar we het zojuist over hadden. Het gaat een nummer maken voor alle cellen met "Items" erin en een fout voor de andere {5, #N/A!, 5, #N/A!}. De ISNUMBER converteert dit vervolgens naar Boolean {True, False, True, False}. Als we ons vermenigvuldigen, worden alleen de waarden uit de eerste en derde kolom behouden. Nadat alle arrays met elkaar zijn vermenigvuldigd, zijn de enige niet-nulgetallen die we hebben de hier gemarkeerde getallen:
| 1 | =SOMPRODUCT((A2:A5="Bob")*(ISNUMMER(ZOEK("Artikelen",B1:E1))*B2:E5)) |
Het SOMPRODUCT zal die dan allemaal optellen en we krijgen ons eindresultaat van 29.
SOMPRODUCT Of
Er doen zich veel situaties voor waarin we waarden willen kunnen optellen als onze criteriakolom één waarde OF een andere waarde heeft. U kunt dit in SUMPRODUCT bereiken door twee criteriaarrays tegen elkaar op te tellen.
In dit voorbeeld willen we de verkochte eenheden voor zowel rood als blauw optellen.
Onze formule ziet er zo uit
| 1 | =SOMPRODUCT(A2:A7, (B2:B7="Rood")+(B2:B7="Blauw")) |
Laten we eens kijken naar de rode criteria-array. Het zal een array produceren die er als volgt uitziet: {1, 1, 0, 0, 0, 0}. De blauwe criteriaarray ziet eruit als {0, 0, 1, 0, 1, 0}. Als je ze bij elkaar optelt, ziet de nieuwe array eruit als {1, 1, 1, 0, 1, 0}. We kunnen zien hoe de twee arrays zijn samengevoegd tot één enkele criteriaarray. De functie vermenigvuldigt dat vervolgens met onze eerste array en we krijgen {100, 50, 10, 0, 75, 0}. Merk op dat de waarden voor Groen op nul zijn gesteld. De laatste stap van het SOMPRODUCT is het optellen van alle getallen om onze oplossing van 235 te bereiken.
Een woord van waarschuwing hier. Wees voorzichtig wanneer de criteriaarrays elkaar niet uitsluiten. In ons voorbeeld kunnen de waarden in kolom B Rood of Blauw zijn, maar we wisten dat het nooit beide kon zijn. Overweeg of we deze formule hadden geschreven:
| 1 | =SOMPRODUCT(A2:A7, (A2:A7>=50)+(B2:B7="Blauw")) |
Het is onze bedoeling om blauwe artikelen te vinden die zijn verkocht of in een hoeveelheid van meer dan 50 waren. Deze voorwaarden zijn echter niet exclusief, aangezien een enkele rij beide meer dan 50 kan zijn in kolom A en blauw zijn. Dit zou ertoe leiden dat de eerste criteriaarray eruitziet als {1, 1, 0, 1, 1, 0}, terwijl de tweede criteriaarray {0, 0, 1, 0, 1, 0} is. Door ze bij elkaar op te tellen, produceerde {1, 1, 1, 1, 2, 0}. Zie je hoe we daar nu een 2 hebben? Als het alleen gelaten zou worden, zou het SOMPRODUCT de waarde in die rij verdubbelen, de 75 veranderen in een 150, en we zouden het verkeerde resultaat krijgen. Om dit te corrigeren, plaatsen we een buitenste criteriacontrole op onze array, zoals:
| 1 | =SOMPRODUCT(A2:A7, --((A2:A7>=50)+(B2:B7="Blauw")>0)) |
Nu, nadat de twee arrays van de binnenste criteria bij elkaar zijn opgeteld, controleren we of het resultaat groter is dan 0. Dit verwijdert de 2 die we eerder hadden, en in plaats daarvan hebben we een array zoals {1, 1, 1 , 1, 1, 0} die het juiste resultaat zal opleveren.
SOMPRODUCT Exact
De meeste functies in Excel zijn niet hoofdlettergevoelig, maar soms moeten we een zoekopdracht kunnen uitvoeren met hoofdlettergevoeligheid in gedachten. Wanneer het gewenste resultaat numeriek is, kunnen we dit bereiken door EXACT te gebruiken in de SOMPRODUCT-functie. Beschouw de volgende tabel:
We willen de score vinden voor item "ABC123". Normaal gesproken vergelijkt de functie EXACT twee items en retourneert een Booleaanse uitvoer waarin staat of de twee items zijn precies hetzelfde. Omdat we ons echter in een SOMPRODUCT bevinden, weet onze computer dat we te maken hebben met arrays en kan hij één item vergelijken met elk item in een array. Onze formule ziet er zo uit
| 1 | =SOMPRODUCT(--EXACT("ABC123", A2:A5), B2:B5) |
De EXACT-functie controleert vervolgens elk item in A2:A5 om te zien of het overeenkomt met waarde en hoofdletter. Dit levert een array op die eruitziet als {0, 1, 0, 0}. Wanneer vermenigvuldigd met B2:B5, wordt de array {0, 2, 0, 0}. Na de laatste sommatie krijgen we onze oplossing van 2.
SOMPRODUCT in Google Spreadsheets
De SOMPRODUCT-functie werkt precies hetzelfde in Google Spreadsheets als in Excel:
SOMPRODUCT Voorbeelden in VBA
U kunt ook de SOMPRODUCT-functie in VBA gebruiken. Type: application.worksheetfunction.sumproduct(array1,array2,array3)
De volgende VBA-instructies uitvoeren:
| 1 | Range("B10") = Application.WorksheetFunction.SumProduct(Range("A2:A7"), Range("B2:B7")) |
zal de volgende resultaten opleveren:
Voor de functieargumenten (array1, enz.), kunt u ze ofwel rechtstreeks in de functie invoeren, of variabelen definiëren om in plaats daarvan te gebruiken.
