In dit artikel zullen we leren om SUM alleen te retourneren vanuit formules in Excel.
Simpel gezegd, werkend met gedeeltelijk gevulde data. Soms moeten we de som van de waarde met een voorwaarde vinden. Voorwaarde is om de som te krijgen waar waarden alleen met formules worden geëxtraheerd.
Voor dit artikel hebben we de volgende functies nodig:
- SOMPRODUCT-functie
- ISFORMULA-functie
De functie SOMPRODUCT is een wiskundige functie in Excel. Het werkt op meerdere bereiken. Het vermenigvuldigt de overeenkomstige arrays en voegt ze vervolgens toe.
Totaal= ( A1 * B1 * C1 *… + A2 * B2 * C2 *… +… )
Syntaxis:
| = SOMPRODUCT (array1, [array2],…) |
array : Het is het bereik of een lijst met waarden.
De functie ISFORMULA retourneert WAAR waar de formule bestaat in celverwijzing en retourneert anders ONWAAR.
Syntaxis:
| = ISFORMULE ( referentie ) |
verwijzing : verwijzing naar cel, waar formule te controleren
Nu gaan we een formule maken van deze functies. Hier zullen we de gegevens geven en we hadden somresultaten nodig waar de formule wordt toegepast.
Gebruik de formule:
| = SOMPRODUCT (array * ISFORMULA (array)) |
Uitleg:
- De functie ISFORMULA retourneert WAAR & ONWAAR op basis van de voorwaarde die wordt gecontroleerd op cellen.
- De functie SOMPRODUCT houdt rekening met waarde 1 voor elke TRUE-waarde en 0 voor FALSE-waarde.
- Het product dat tussen overeenkomstige waarden wordt genomen, negeert FALSE-waarden omdat de waarden worden vermenigvuldigd met 0s. Alleen TRUE-waarden krijgen SUM omdat de waarden worden vermenigvuldigd met 1s.
Voorbeeld:
Laten we deze formule testen door het op een voorbeeld uit te voeren
Hier hebben we gegevens waarbij de prijs van granen wordt geëxtraheerd door het product van Hoeveelheid tot de eenheidsprijs en sommige prijswaarden worden handmatig ingevoerd. Dus als ik de prijssom moet vinden voor de waarde waar de formule de totale prijs heeft geëxtraheerd.
Nu zullen we de onderstaande formule gebruiken om de SUM te krijgen
Formule:
| = SOMPRODUCT ( E2 : E15 * ISFORMULE ( E2 : E15 ) ) |
Uitleg:
- De functie ISFORMULA retourneert WAAR & ONWAAR op basis van de voorwaarde die wordt gecontroleerd op matrixcellen van E2 tot E15.
- De functie SOMPRODUCT houdt rekening met waarde 1 voor elke ontvangen TRUE-waarde en 0 voor FALSE-waarde, zoals hieronder weergegeven.
=SOMPRODUCT(E2:E15*
{ WAAR; ONWAAR ; ONWAAR ; ONWAAR ; WAAR ; WAAR ; ONWAAR ; WAAR ; ONWAAR ; WAAR ; WAAR ; WAAR ; WAAR ; WAAR } )
- Het product dat tussen overeenkomstige waarden wordt genomen, negeert FALSE-waarden omdat de waarden worden vermenigvuldigd met 0s. Alleen TRUE-waarden krijgen SUM omdat de waarden worden vermenigvuldigd met 1s, zoals hieronder wordt weergegeven.
= SOMPRODUCT ( { 58.41 ; 0 ; 0 ; 0 ; 82.84 ; 95.58 ; 0 ; 90.27 ; 0 ; 37.8 ; 78.48 ; 57.97 ; 97.72 ; 77.88 }
Hier wordt de array naar de functie gegeven als cell_reference. Druk op Enter om het resultaat te krijgen.
Zoals u in de bovenstaande momentopname kunt zien, is de som van de waarden die zijn geëxtraheerd waar de formule bestaat.
Als u de som van waarden zonder formule wilt vinden, gebruikt u de NIET-functie met de ISFORMULA-functie.
Gebruik de formule:
| = SOMPRODUCT ( E2:E15 * NIET ( ISFORMULE ( E2:E15 ) )) |
Gebruik de formule en verkrijg de waarde zoals weergegeven in de onderstaande momentopname.
Zoals u aan de bovenstaande formule kunt zien, kunt u voorwaardelijke waarden krijgen.
Opmerkingen:
- De functie SOMPRODUCT beschouwt niet-numerieke waarden als nullen.
- De functie SOMPRODUCT beschouwt de logische waarde TRUE als 1 en False als 0.
- De argumentarray moet dezelfde lengte hebben, anders retourneert de functie een fout.
Ik hoop dat dit artikel over het retourneren van SUM alleen uit formules in Excel verklarend is. Vind hier meer artikelen over SUMPRODUCT-functies. Deel uw vraag hieronder in het opmerkingenveld. Wij zullen u helpen.
Hoe de SOMPRODUCT-functie in Excel te gebruiken?
Tekst verwijderen in Excel vanaf een positie
Validatie van tekstinvoer
Maak een vervolgkeuzelijst in Excel met kleur
Verwijder voorloop- en volgspaties uit tekst in Excel
populaire artikels
50 Excel-snelkoppeling om uw productiviteit te verhogen
Een vervolgkeuzelijst bewerken
Absolute referentie in Excel
Indien met voorwaardelijke opmaak
Indien met jokertekens
Opzoeken op datum