In dit artikel zullen we leren hoe u de N grootste waarden in een bereik in Excel kunt optellen.
Scenario:
In eenvoudige bewoordingen, tijdens het werken met gegevensnummers, soms een voorwaarde gegeven, bijvoorbeeld wanneer we de som van de nummers van de bovenste 5 nummers van de array of de onderkant van de array moeten krijgen. U kunt de oplossing voor dit probleem eenvoudig uitvoeren met behulp van de Excel-functies zoals hieronder wordt uitgelegd.
Som van TOP N-waarden
Hoe het probleem op te lossen?
Voor dit artikel moeten we de functie SOMPRODUCT gebruiken. Nu gaan we een formule maken van deze functies. Hier krijgen we een bereik en we moeten de 5 waarden in het bereik bovenaan zetten en de som van de waarden krijgen.
Generieke formule:
| = SOMPRODUCT ( GROOT ( bereik, { 1 , 2 ,… , n } } ) |
bereik : bereik van waarden
Waarden : getallen gescheiden door komma's, zoals als u de top 3 waarden wilt vinden, gebruik { 1 , 2 , 3 }.
Voorbeeld:
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we deze formule dus testen door deze in het onderstaande voorbeeld uit te voeren.
Hier hebben we een reeks waarden van A1:A23.
Hier wordt het bereik gegeven met behulp van de Excel-tool voor het genoemde bereik.
Ten eerste moeten we de top vijf waarden vinden met behulp van de LARGE-functie en vervolgens de sombewerking uitvoeren over die 5 waarden. Nu zullen we de volgende formule gebruiken om de som te krijgen
Gebruik de formule:
| = SOMPRODUCT ( GROOT (bereik, { 1 , 2 , 3 , 4 , 5 } ) ) |
Uitleg:
- De functie LARGE retourneert de top 5 numerieke waarden en retourneert de array naar de functie SOMPRODUCT.
= SOMPRODUCT ( { 149 , 123 , 100 , 87 , 85 } ) )
- De functie SOMPRODUCT haalt de array van top 5-waarden op, die een array van top 5-getallen heeft, retourneert de SOM van die getallen.
Hier wordt het bereik gegeven als het genoemde bereik. Druk op Enter om de SUM van de top 5 nummers te krijgen.
Zoals je in de bovenstaande momentopname kunt zien, is die som 544. De som van de waarden 149 + 123 + 100 + 87 + 85 = 544.
Het bovenstaande proces wordt gebruikt om de som van een paar getallen vanaf de bovenkant te berekenen. Maar om voor te rekenen N (groot) aantal waarden in een groot bereik.
Gebruik de formule:
| = SOMPRODUCT ( GROOT ( bereik, RIJ ( INDIRECT ( "1:10") ) |
Hier genereren we de som van de top 10 waarden door een array van 1 tot 10 te krijgen { 1; 2 ; 3 ; 4 ; 5; 6 ; 7; 8 ; 9 ; 10 } met behulp van de ROW & INDIRECT Excel-functies.
Hier hebben we de som van de top 10 getallen { 149 ; 123 ; 100; 87 ; 85 ; 82 ; 61; 58 ; 54; 51 }), wat resulteert in 850.
Som van onderste N-waarden
Hoe het probleem op te lossen?
Voor dit artikel moeten we de functie SOMPRODUCT gebruiken. Nu gaan we een formule maken van deze functies. Hier krijgen we een bereik en we moeten de 5 waarden in het bereik verlagen en de som van de waarden krijgen.
Generieke formule:
| = SOMPRODUCT ( KLEIN ( bereik, { 1 , 2 ,… , n } } ) |
bereik : bereik van waarden
Waarden : getallen gescheiden door komma's, zoals als je de onderste 3 waarden wilt vinden, gebruik dan { 1 , 2 , 3 }.
Voorbeeld:
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we deze formule dus testen door deze in het onderstaande voorbeeld uit te voeren.
Hier hebben we een reeks waarden van A1:A23.
Hier wordt het bereik gegeven met behulp van de Excel-tool voor het genoemde bereik.
Eerst moeten we de onderste vijf waarden vinden met behulp van de SMALL-functie en vervolgens de sombewerking uitvoeren over die 5 waarden. Nu zullen we de volgende formule gebruiken om de som te krijgen
Gebruik de formule:
| = SOMPRODUCT ( KLEIN (bereik, { 1 , 2 , 3 , 4 , 5 } ) ) |
Uitleg:
- De functie SMALL retourneert de onderste 5 numerieke waarden en retourneert de array naar de functie SOMPRODUCT.
= SOMPRODUCT ( { 23 , 27 , 28 , 28 , 30 } ) )
- De SOMPRODUCT-functie haalt de array van de onderste 5 waarden op, die een array van de onderste 5 getallen heeft en de SOM van die getallen retourneert.
Hier wordt het bereik gegeven als het genoemde bereik. Druk op Enter om de SOM van de onderste 5 cijfers te krijgen.
Zoals je in de bovenstaande momentopname kunt zien, is die som 136. De som van de waarden 23 + 27 + 28 + 28 + 30 = 136.
Het bovenstaande proces wordt gebruikt om de som van een paar getallen vanaf de onderkant te berekenen. Maar om voor te rekenen N (groot) aantal waarden in een groot bereik.
Gebruik de formule:
| = SOMPRODUCT ( KLEIN ( bereik, RIJ ( INDIRECT ( "1:10") ) |
Hier genereren we de som van de onderste 10 waarden door een array van 1 tot 10 te krijgen { 1; 2 ; 3 ; 4 ; 5; 6 ; 7; 8 ; 9 ; 10 } met behulp van de ROW & INDIRECT Excel-functies.
Hier hebben we de som van de onderste 10 getallen, wat resulteert in 307.
Hier zijn enkele observatienotities die hieronder worden weergegeven.
Opmerkingen:
- De formule werkt alleen met getallen.
- De formule werkt alleen als er geen duplicaten zijn in de opzoektabel
- De functie SOMPRODUCT beschouwt niet-numerieke waarden (zoals tekst abc) en foutwaarden (zoals #GETAL!, #NULL!) als null-waarden.
- De functie SOMPRODUCT beschouwt de logische waarde TRUE als 1 en False als 0.
- De argumentarray moet dezelfde lengte hebben, anders de functie.
Ik hoop dat dit artikel over het optellen van de N grootste waarden in een bereik in Excel verklarend is. Vind hier meer artikelen over het berekenen van waarden en gerelateerde Excel-formules. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite.
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel.
SOM als datum tussen ligt : Retourneert de SOM van waarden tussen bepaalde datums of periode in Excel.
Som als datum groter is dan opgegeven datum: Retourneert de SOM van waarden na de opgegeven datum of periode in Excel.
2 manieren om per maand op te tellen in Excel: Retourneert de SOM van waarden binnen een bepaalde specifieke maand in Excel.
Meerdere kolommen optellen met voorwaarde: Retourneert de SOM van waarden over meerdere kolommen met voorwaarde in Excel
Jokertekens gebruiken in Excel : Tel cellen die overeenkomen met zinnen met behulp van de jokertekens in Excel
Populaire artikels :
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.