01 van 02
Somcellen die tussen twee waarden vallen
Lifewire
De functie SOMPRODUCT in Excel is een zeer veelzijdige functie die verschillende resultaten geeft, afhankelijk van de manier waarop de argumenten van de functie worden ingevoerd.
Normaal gesproken, zoals de naam al doet vermoeden, vermenigvuldigt SUMPRODUCT de elementen van een of meer arrays om hun product te krijgen en voegt het vervolgens de producten toe of telt ze bij elkaar op.
Door de syntaxis van de functie aan te passen, kan deze echter worden gebruikt om alleen de gegevens in cellen op te tellen die aan specifieke criteria voldoen.
Sinds Excel 2007 bevat het programma twee functies - SUMIF en SUMIFS - die gegevens in cellen zullen optellen die aan een of meer vastgestelde criteria voldoen.
Soms is SOMPRODUCT echter gemakkelijker om mee te werken als het gaat om het vinden van meerdere aandoeningen die betrekking hebben op hetzelfde bereik, zoals wordt weergegeven in de afbeelding hierboven.
SUMPRODUCT Functiesyntaxis om cellen op te tellen
De syntaxis die wordt gebruikt om SOMPRODUCT gegevens te laten optellen in cellen die aan specifieke voorwaarden voldoen, is:
=SOMPRODUCT([voorwaarde1][voorwaarde2][array])
condition1, condition2 - de voorwaarden waaraan moet worden voldaan voordat de functie het product van de array vindt.
array - een aaneengesloten reeks cellen
Voorbeeld: gegevens optellen in cellen die aan meerdere voorwaarden voldoen
Het voorbeeld in de afbeelding hierboven voegt de gegevens toe in cellen in het bereik D1 tot E6 die tussen 25 en 75 liggen.
De SOMPRODUCT-functie invoeren
Omdat dit voorbeeld een onregelmatige vorm van de functie SOMPRODUCT gebruikt, kan het dialoogvenster van de functie niet worden gebruikt om de functie en zijn argumenten in te voeren. In plaats daarvan moet de functie handmatig in een werkbladcel worden ingevoerd.
- Klik op cel B7 in het werkblad om het de actieve cel te maken;
- Voer de volgende formule in cel B7 in: =SUMPRODUCT(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
- Het antwoord 250 zou in cel B7 moeten verschijnen
- Het antwoord werd verkregen door de vijf getallen in het bereik (40, 45, 50, 55 en 60) op te tellen die tussen 25 en 75 liggen. Het totaal hiervan is 250
De formule SOMPRODUCT opsplitsen
Wanneer voorwaarden worden gebruikt voor zijn argumenten, evalueert SOMPRODUCT elk array-element tegen de voorwaarde en retourneert een Booleaanse waarde (TRUE of FALSE).
Ten behoeve van berekeningen wijst Excel een waarde van 1 toe aan die matrixelementen die WAAR zijn (voldoen aan de voorwaarde) en een waarde van 0voor array-elementen die ONWAAR zijn (voldoen niet aan de voorwaarde).
Bijvoorbeeld het getal 40:
- is TRUE voor de eerste voorwaarde, dus een waarde van 1 wordt toegewezen aan de eerste array;
- is TRUE voor de tweede voorwaarde, dus een waarde van 1 wordt toegewezen aan de tweede array.
Het getal 15:
- is ONWAAR voor de eerste voorwaarde, dus een waarde van 0 wordt toegewezen aan de eerste array;
- is TRUE voor de tweede voorwaarde, dus een waarde van 1 wordt toegewezen aan de tweede array.
De corresponderende enen en nullen in elke array worden met elkaar vermenigvuldigd:
- Voor het getal 40 - we hebben 1 x 1 die een waarde van 1 retourneert;
- Voor het getal 15 - hebben we 0 x 1 die een waarde van 0 retourneert.
De enen en nullen vermenigvuldigen met het bereik
Deze enen en nullen worden vervolgens vermenigvuldigd met de getallen in het bereik A2: B6
Dit wordt gedaan om ons de getallen te geven die door de functie worden opgeteld.
Dit werkt omdat:
- 1 keer een willekeurig getal is gelijk aan het oorspronkelijke getal
- 0 keer een willekeurig getal is gelijk aan 0
Dus we eindigen met:
-
140=40
015=0
022=0
145=45
150=50
155=55
025=0
075=0
160=600100=0
De resultaten optellen
SOMPRODUCT somt vervolgens de bovenstaande resultaten op om het antwoord te vinden.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
