In het vorige artikel hebben we geleerd hoe we de bovenste of onderste N-waarden kunnen optellen. In dit artikel proberen we de bovenste of onderste N-waarden samen te vatten met een criterium.
Som van TOP N-waarden met criteria
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 een criterium. We moeten de top 5 waarden in het bereik krijgen en de som van de waarden krijgen op basis van de gegeven criteria.
Generieke formule:
= SOMPRODUCT ( GROOT ( (lijst = criteria) * (bereik), { 1 , 2 ,… ,n } } )
lijst: lijst met criteria
criteria: criteria om aan te passen
bereik: bereik van waarden
waarden: getallen gescheiden door komma's, zoals als je de top 3 waarden wilt vinden, gebruik dan { 1 , 2 , 3 }.
Voorbeeld:
Hier hebben we de datasetwaarden van A1:D50.
Eerst moeten we de top vijf waarden vinden met behulp van de LARGE-functie die overeenkomt met de stad "Boston" 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 ( ( Stad = "Boston") * (hoeveelheid) , { 1 , 2 , 3 , 4 , 5 } ))
Uitleg:
- City's "Boston" komt overeen met het genoemde City-assortiment. Dit retourneert een array van waar en onwaar.
- De functie LARGE retourneert de top 5 numerieke waarden uit het hoeveelheidsbereik en retourneert de matrix naar de functie SOMPRODUCT.
= SOMPRODUCT { 193 , 149 , 138 , 134 , 123 }
- De functie SOMPRODUCT krijgt een array van top 5-waarden, waarbij een array van top 5-getallen de SOM van die getallen retourneert.
Hier wordt het bereik Stad & hoeveelheid gegeven als het genoemde bereik. Druk op Enter om de SOM van de top 5 cijfers te krijgen.
Zoals je in de bovenstaande momentopname kunt zien, is die som 737. De som van de waarden 193 + 149 + 138 + 134 + 123 = 737.
U kunt de bovenstaande waarden in de dataset controleren met behulp van de Excel-filteroptie. Pas het filter toe op de kop Stad & aantal en klik op de pijlknop op de kop van de stad die verschijnt. Volg de stappen zoals hieronder weergegeven.
Stappen:
- Selecteer de cel in de koptekst van de stad. Filter toepassen met snelkoppeling Ctrl + Shift + L
- Klik op de pijl die als filteroptie verschijnt.
- Selecteer de optie (Alles selecteren).
- Selecteer alleen de stad Boston.
- Selecteer nu de hoeveelheidskop.
- Sorteer de lijst van groot naar klein en u kunt alle top 5-waarden bekijken die we met de formule hebben berekend.
Zoals je kunt zien in de bovenstaande gif alle 5 waarden die overeenkomen met de opgegeven criteria. Dit betekent ook dat de formule goed werkt om het aantal van deze waarden te krijgen
GROTE N nummers
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 ( ( Stad = "Boston") * (hoeveelheid), 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, wat resulteert in 1147.
Som van onderste N-waarden met criteria
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:
{ = SOM ( KLEIN ( ALS ( Stad = "Boston" , hoeveelheid ) , { 1 , 2 , 3 , 4 , 5 } )) }
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 bereik van waarden van A1:D50.
Hier wordt het bereik Stad en hoeveelheid gegeven met behulp van de Excel-tool voor het genoemde bereik.
Ten eerste moeten we de onderste vijf waarden vinden met behulp van de SMALL-functie die overeenkomt met criteria en vervolgens moet de sombewerking worden uitgevoerd over die 5 waarden. Nu zullen we de volgende formule gebruiken om de som te krijgen
Gebruik de formule:
{ = SOM ( KLEIN ( ALS ( Stad = "Boston " , hoeveelheid ) , { 1 , 2 , 3 , 4 , 5 } )) }
Gebruik accolades NIET handmatig. accolades aangebracht met behulp van de Ctrl + Shift + Enter in plaats van gewoon Binnenkomen.
Uitleg:
- De functie KLEINE met de functie ALS retourneert de onderste 5 numerieke waarden die overeenkomen met de stad "Boston" en retourneert de array naar de functie SOM.
= SOM ( { 23 , 27 , 28 , 28 , 30 } ) )
- De SOM-functie haalt de array van de onderste 5 waarden op, die een array van de onderste 5 getallen heeft, retourneert de SUM van die getallen die worden gebruikt met CTRL + SHIFT + ENTER.
Hier wordt het bereik Stad & hoeveelheid gegeven als het genoemde bereik. druk op Ctrl + Shift + Enter om de SOM van de onderste 5 getallen te krijgen, aangezien dit een matrixformule is.
Zoals je in de bovenstaande momentopname kunt zien, is die som 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:
{ = SOM ( KLEIN ( IF ( Stad = "Boston", hoeveelheid), RIJ ( INDIRECT ("1:10")))) }
Gebruik de accolades NIET handmatig. Gebruik de Ctrl + Shift + Enter in plaats van de Enter.
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 155.
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 als de functie.
Ik hoop dat dit artikel over het retourneren van de som van top 5-waarden of onderste 5-waarden met criteria in Excel verklarend is. Vind hier meer artikelen over SUMPRODUCT-functies. Deel uw vraag hieronder in het opmerkingenveld. Wij zullen u helpen.
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
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
Converteer inches naar feet en inches in Excel 2016
Voeg voor- en achternaam toe in Excel
Tel cellen die overeenkomen met A of B
