SOMPRODUCT functie

Inleiding tot SOMPRODUCT:

De SOMPRODUCT functie behoort tot een van de favorieten functies voor gegevensanalyse, met name wanneer je jezelf de vraag stelt zoals “hoeveel”, “hoe frequent”, het vinden van de som en gemiddelden met een criteria.  Dus we zullen beginnen met een korte introductie van de SOMPRODUCT functie met behulp van een voorbeeld om het gebruik uit te leggen.

De syntaxis:

Bij de SOMPRODUCT functie is alleen het eerste argument verplicht, de rest van de argumenten zijn optioneel. De functie neemt een aantal matrices van gelijke grootte die het vermenigvuldigt en retourneert vervolgens de som van de resulterende matrix. De matrices moeten van gelijke grootte zijn, anders retourneert de formule een fout. De functie heeft de volgende syntax.

SOMPRODUCT(array1, [array2], [array3], …)

De syntax is overgenomen van MS Help en we kunnen zien dat alleen de eerste reeks van array is vereist, de rest is optioneel (optionele argumenten worden vertegenwoordigd door vierkante haken). We kunnen tot 255 dergelijke argumenten in SOMPRODUCT functie hebben.

Onze gegevens

We hebben als voorbeeld verkoopgegevens voor onze analyse. De gegevens hebben 6 velden namelijk Produkt, datum, aantal, verkoper ID, betaald via, bedrag. we zullen deze gegevensset gebruiken om verschillende toepassingen van SOMPRODUCT uit te leggen.

Voorbeelden:

SOMPRODUCT GEBRUIKEN OM EEN ARTIKEL  OP TE TELLEN:

Misschien is het eenvoudigste gebruik van SOMPRODUCT om een bepaald bereik op te tellen. Als u bijvoorbeeld naar de bovenstaande gegevens verwijst, kunnen we het aantal verkochte potloden optellen met behulp van de volgende formule:

=SOMPRODUCT((A2:A20=”HB potlood”)*(C2:C20))

In deze formule controleren we het bereik A2: A20 voor het artikel HB potlood. De gevonden waarde wordt vermenigvuldigd met array de aantallen, de beide array’s worden opgeteld om de totale verkoophoeveelheid voor potlood te geven.

Tip: we kunnen de vaste verwijzing in de formule vervangen door een celverwijzing zoals een in het onderstaande voorbeeld.

=SOMPRODUCT((A2:A20=H3)*(C2:C20))

We kunnen de waarde “HB potlood” vervangen door celverwijzing H3.

SOMPRODUCT GEBRUIKEN VOOR OMZET VAN EEN VERKOPER

We hebben drie verkopers in de gegevens geïdentificeerd door hun IDs 1, 2 en 3. We kunnen de totale omzet optellen door opnieuw SOMPRODUCT te gebruiken. De formule die wordt gebruikt, is:

=SOMPRODUCT((D2:D20=H4)*(C2:C20))

In deze formule wordt het eerste deel gebruikt om de verkoper id’s te vergelijken. We hebben dus een vergelijking van een array met Id’s met verkoper-ID = 3. Het resultaat word vermenigvuldigd met de respectievelijke waarden van de verkochte hoeveelheid. De uitkomst van de array wordt vervolgens door de SOMPRODUCT function gegeven.

SOMPRODUCT GEBRUIKEN MET OPERATOREN VOOR VERGELIJKING (GROTER DAN OPERATOR):

De volgenden operators worden in Excel gebruikt voor vergelijkingen:

= (gelijkteken)                  >= (groter-dan-of-gelijk-aan-teken)
> (groter-dan-teken)         <= (kleiner-dan-of-gelijk-aan-teken)
< (kleiner-dan-teken)        <> (niet-gelijk-aan-teken)

Bijvoorbeeld voor het vinden van minder dan 3 verkocht door verkoper kan worden gedaan met de volgende formule:

=SOMPRODUCT((D2:D20=H6)*(C2:C20<=4)*1)

De bovenstaande formule onderzoekt eerst het bereik van Id’s in het celbereik D2: d20 en retourneert WAAR voor gevallen waarin ID overeenkomt. Deze vergelijking wordt gevolgd door vergelijking van de verkoophoeveelheid met onze criteria, d.w.z. 4. Deze tweede vergelijking vergelijkt de verkochte hoeveelheid met het minimum dat is ingesteld door gebruiker d.w.z. 4. Elke verkochte hoeveelheid die kleiner is dan 4, retourneert een WAAR. Deze twee arrays zullen opnieuw worden vermenigvuldigd om nog een array van WAAR en ONWAAR te geven – het vermenigvuldigen van die array met 1 zal dan worden samengevat om het uiteindelijke antwoord te krijgen.

Tellen met behulp van SOMPRODUCT  met operatoren voor vergelijkingen (ongelijk aan operator):

We willen misschien tellen hoe vaak een verkoper een ander artikel dan potlood heeft verkocht, in dat geval zullen we de “ongelijk aan” operator gebruiken (“< >”). We kunnen de volgende formule gebruiken om alle verkopen van verkoper 1 op te tellen exclusief het potlood:

=SOMPRODUCT((A2:A20<>H8)*(D2:D20=1)*(C2:C20))

We kunnen zien dat de formule “ongelijk aan” operator in de eerste deel gebruikt. Deze operator zorgt ervoor dat er een WAAR geretourneerd wordt voor andere items dan onze criteria. De tweede vergelijking in het tweede deel bevestigt de juiste ID van de verkoper en het product van deze twee matrices wordt vermenigvuldigd met de hoeveelheid in de derde array om het resultaat te geven. In dit voorbeeld heeft verkoper 1 44 artikelen verkocht.

SOMPRODUCT  om de betaling via contant geld of credit card te vinden:

Een soortgelijke formule kan worden gebruikt om de wijze van betaling te vinden creditcard of contant.

De formule die wordt gebruikt, is:

=SOMPRODUCT((E2:E20=H11)*(F2:F20))

In deze formule evalueert het eerste deel aan de linkerkant of de waarde gelijk is aan onze keuze creditcard of cash, dit retourneert een array van WAAR en ONWAAR en die wordt vermenigvuldigd met de overeenkomende waarde van de verkopen die zijn gemaakt.

SOMPRODUCT om de verkopen in een maand te vinden:

We kunnen de dag-en maand functie van Excel gebruiken om de betaling in een bepaalde maand te vinden. Of als alternatief kunnen we de begin-en einddatum van de maand instellen om de verkoop in die specifieke maand te vinden. Laten we aannemen dat we de begindatum van de maand willen instellen. We zullen de einddatum berekenen met behulp van de formule.

De begindatum is aanwezig in cel H15, de volgende cel vindt de einddatum door gebruik te maken van de formule LAATSTE.DAG()

=LAATSTE.DAG(H15;0)

De volgende cel vindt de totale verkoop voor een maand (tussen begin en einddatum) met de volgende formule:

=SOMPRODUCT((B2:B20>=H15)*(B2:B20<=H16)*(F2:F20))

Het eerste deel evalueert het datumbereik voorwaarden groter dan begin van de maand, het tweede deel hetzelfde bereik voor datums die kleiner zijn dan of gelijk zijn aan de einddatum en vervolgens wordt de resulterende matrix met waarden vermenigvuldigd met de totale verkoop om ons het totaal te geven al antwoord.

 

 

 

 

 

 

 

 

 

 

 

Download het voorbeeld bestand SOMPRODUCT.xlsx

Download pdf bestand SOMPRODUCT.pdf

Er zijn nog veel meer manieren waarop we SOMPRODUCT kunnen gebruiken.

Succes met deze functie,

Paul van de Laar