Hoe de Excel SOMPRODUCT-functie - te gebruiken

Samenvatting

De functie SUMPRODUCT vermenigvuldigt bereiken of matrices met elkaar en retourneert de som van producten. Dit klinkt saai, maar SOMPRODUCT is een ongelooflijk veelzijdige functie die kan worden gebruikt om te tellen en op te tellen, zoals AANTAL.ALS of SOMMEN, maar met meer flexibiliteit. Andere functies kunnen gemakkelijk worden gebruikt in SUMPRODUCT om de functionaliteit nog verder uit te breiden.

Doel

Vermenigvuldig en som arrays op

Winstwaarde

Het resultaat van vermenigvuldigde en gesommeerde arrays

Syntaxis

= SOMPRODUCT (array1, (array2), …)

Argumenten

• array1 - De eerste array of het eerste bereik dat moet worden vermenigvuldigd en vervolgens moet worden opgeteld .
• array2 - (optioneel) De tweede array of reeks die moet worden vermenigvuldigd en vervolgens moet worden opgeteld.

Versie

Excel 2003

Gebruiksopmerkingen

De SUMPRODUCT-functie werkt met arrays, maar de normale array-syntaxis (Ctrl + Shift + Enter) is niet nodig om in te voeren. Het doel van de SOMPRODUCT-functie is om arrays te vermenigvuldigen en vervolgens op te tellen. Als er maar één array is opgegeven, telt SOMPRODUCT eenvoudig de items in de array op. Er kunnen maximaal 30 arrays worden geleverd.

Wanneer je SOMPRODUCT voor het eerst tegenkomt, lijkt het misschien saai, complex en zelfs zinloos. Maar SUMPRODUCT is een verbazingwekkend veelzijdige functie met veel toepassingen. Omdat het arrays netjes en zonder klachten zal behandelen, kunt u het gebruiken om reeksen cellen op slimme, elegante manieren te verwerken (zie links naar formulevoorbeelden op deze pagina).

Om te illustreren hoe SOMPRODUCT werkt, volgen hier een paar veelvoorkomende voorbeelden.

SOMPRODUCT voor voorwaardelijke sommen en tellingen

Stel dat u enkele ordergegevens hebt in A2: B6, met Staat in kolom A, Verkoop in kolom B:

	EEN	B
1	Staat	verkoop
2	UT	75
3	CO	100
4	TX	125
5	CO	125
6	TX	150

Met SOMPRODUCT kunt u de totale verkoop voor Texas ("TX") tellen met deze formule:

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

En u kunt de totale verkoop voor Texas ("TX") optellen met deze formule:

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

Opmerking: laat u niet verwarren door het dubbele negatief. Dit is een veelgebruikte truc die wordt gebruikt in meer geavanceerde Excel-formules om WAAR en ONWAAR waarden in enen en nullen te veranderen. Zie hieronder meer …

Voor het bovenstaande somvoorbeeld is hier een virtuele weergave van de twee arrays zoals ze voor het eerst zijn verwerkt door SUMPRODUCT:

matrix1	matrix2
ONWAAR	75
ONWAAR	100
WAAR	125
ONWAAR	125
WAAR	150

Elke array heeft 5 items. De eerste array bevat de TRUE / FALSE-waarden die het resultaat zijn van de uitdrukking A2: A6 = "TX", en de tweede array is de inhoud van B2: B6. Elk item in de eerste array wordt vermenigvuldigd met het corresponderende item in de tweede array. In de huidige toestand is het resultaat van SOMPRODUCT echter nul, omdat de waarden WAAR en ONWAAR als nul worden behandeld. We hebben de items in array1 nodig om numeriek te zijn - ze moeten worden "gedwongen" tot enen en nullen. Dit is waar het dubbele negatief binnenkomt.

Door de dubbele negatieve - (dubbele unaire, voor jullie technische typen) te gebruiken, zijn we in staat om de WAAR / ONWAAR in de numerieke waarden één en nul te dwingen, zoals weergegeven in de virtuele weergave hieronder. De laatste kolom "Product" vertegenwoordigt het resultaat van het vermenigvuldigen van de twee arrays met elkaar. Het opgetelde resultaat, 275, is de waarde die SOMPRODUCT retourneert.

matrix1		matrix2		Product
0	*	75	=	0
0	*	100	=	0
1	*	125	=	125
0	*	125	=	0
1	*	150	=	150
Som				275

Met behulp van de accolade-syntaxis voor arrays ziet het voorbeeld er na dwang als volgt uit:

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

en zo na vermenigvuldiging:

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

Dit voorbeeld breidt de bovenstaande ideeën met meer detail uit.

SOMPRODUCT met andere functies

SUMPRODUCT kan andere functies rechtstreeks gebruiken. Mogelijk ziet u SOMPRODUCT gebruikt met de LEN-functie om het totale aantal tekens in een bereik te tellen, of met functies zoals ISBLANK, ISTEXT, enz. Dit zijn normaal gesproken geen array-functies, maar wanneer ze een bereik krijgen, creëren ze een "resultaat-array". Omdat SUMPRODUCT is gebouwd om met arrays te werken, kan het rechtstreeks berekeningen op de arrays uitvoeren. Dit kan een goede manier zijn om ruimte in een werkblad te besparen, door de noodzaak van een "hulpkolom" te elimineren.

Stel dat u 10 verschillende tekstwaarden hebt in A1: A10 en dat u het totale aantal tekens voor alle 10 waarden wilt tellen. U zou een hulpkolom in kolom B kunnen toevoegen die deze formule gebruikt: LEN (A1) om de tekens in elke cel te berekenen. Dan zou je SUM kunnen gebruiken om alle 10 getallen bij elkaar op te tellen. Met SOMPRODUCT kunt u echter een formule als deze schrijven:

=SUMPRODUCT(LEN(A1:A10))

Bij gebruik met een bereik als A1: A10, retourneert LEN een array van 10 waarden. Vervolgens zal SOMPRODUCT eenvoudig alle waarden optellen en het resultaat retourneren, zonder dat er een hulpkolom nodig is.

Zie voorbeelden hieronder voor vele andere manieren om SOMPRODUCT te gebruiken.

Opmerkingen:

1. SOMPRODUCT behandelt niet-numerieke items in arrays als nullen.
2. Array-argumenten moeten dezelfde grootte hebben. Anders genereert SUMPRODUCT een #VALUE! foutwaarde.
3. Logische tests binnen arrays zullen TRUE en FALSE waarden creëren. In de meeste gevallen wilt u deze dwingen tot enen en nullen.
4. SOMPRODUCT kan het resultaat van andere functies vaak direct gebruiken (zie onderstaande formulevoorbeelden)