In dit artikel zullen we leren hoe u de n-de kleinste kunt vinden met criteria en de n-de grootste met criteria uit de gegeven tabel in Excel.
Scenario:
In eenvoudige bewoordingen, tijdens het werken met getallen in gegevensnummers, soms een voorwaarde gegeven, bijvoorbeeld wanneer we moeten zoeken naar de 5e hoogste gegeven waarde . U kunt de oplossing voor dit probleem eenvoudig uitvoeren met behulp van de Excel-functies zoals hieronder wordt uitgelegd.
Nde grootste met criteria
Hoe het probleem op te lossen?
Voor dit artikel moeten we de functie LARGE gebruiken. Nu gaan we een formule maken van deze functies. Hier krijgen we een tabel en moeten we de n-de grootste waarde vinden in het bereik met gegeven criteria.
Generieke formule:
{ = GROOT (ALS (c_bereik = "waarde", bereik), N ) }
c_range : criteriabereik
waarde : criteria komen overeen met waarde
bereik : resultatenarray
N : op één na grootste
Opmerking : Plaats accolades niet handmatig. Dit is een matrixformule, moet worden gebruikt Ctrl + Shift + Enter in plaats van gewoon Enter waar het #GETAL! fout.
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 bestelgegevens met datum van bestelling, regio en bestelprijs.
We moeten de 5e grootste orderprijs uit de regio Oost weten. Hier wordt het bereik gegeven met behulp van de Excel-tool voor het genoemde bereik.
regio (C3:C16)
prijs (D3:D16)
Ten eerste moeten we de vijfde grootste 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:
{ = GROOT (ALS (regio = G5, prijs), F5)}
Uitleg:
- ALS-functie controleert de criteria die overeenkomen met alle waarden in de regio met de gegeven Oost-waarde in G5-cel en retourneert overeenkomstige TRUE-waarden uit het prijsbereik.
= GROOT ( { 58.41; 303.63 ; ONWAAR ; 153.34 ; 82.84 ; ONWAAR ; 520.01 ; ONWAAR ; 177 ; ONWAAR ; ONWAAR ; 57.97 ; 97.72 ; ONWAAR } ) , F5 )
- De functie LARGE neemt de array die alle prijsklassen heeft zoals hierboven weergegeven en retourneert de 5e grootste waarde van de array zoals weergegeven in de onderstaande momentopname.
U kunt de formule in het formulevak bekijken zoals in de bovenstaande momentopname. Gebruik Ctrl + Shift + Enter om het resultaat te krijgen.
Zoals u kunt zien, retourneert de matrixformule de 5e hoogste prijs $ 97,72 met de regio Oost.
U kunt array ook als array, criteria tussen aanhalingstekens en n-waarde rechtstreeks naar de functie voeren in plaats van celverwijzing of benoemd bereik te gebruiken.
Gebruik de formule:
{ = GROOT ( ALS ( C3:C16 = "West" , D3:D16 ) , 3 )}
Gebruik maken van Ctrl + Shift + Enter
U kunt zien dat de formule goed werkt, waar u de op één na grootste waarde vindt met criteria in de tabel.
Op een na laagste met criteria
Hoe het probleem op te lossen?
Voor dit artikel moeten we de SMALL-functie gebruiken. Nu gaan we een formule maken van deze functies. Hier krijgen we een tabel en moeten we de n-de kleinste waarde vinden in het bereik met gegeven criteria.
Generieke formule:
{ = KLEIN (ALS (c_bereik = "waarde", bereik), N ) }
c_range : criteriabereik
waarde : criteria komen overeen met waarde
bereik : resultatenarray
N : op één na grootste
Opmerking : Plaats accolades niet handmatig. Dit is een matrixformule, moet worden gebruikt Ctrl + Shift + Enter in plaats van gewoon Enter waar het #GETAL! fout.
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 bestelgegevens met datum van bestelling, regio en bestelprijs.
We moeten de op één na kleinste bestelprijs uit de regio Oost weten. Hier wordt het bereik gegeven met behulp van de Excel-tool voor het genoemde bereik.
regio (C3:C16)
prijs (D3:D16)
Ten eerste moeten we de vijfde kleinste of laagste waarden vinden met behulp van de SMALL-functie en vervolgens de opzoekbewerking uitvoeren over de waarden. Nu zullen we de volgende formule gebruiken om de som te krijgen
Gebruik de formule:
{ = KLEIN (ALS (regio = G5, prijs), F5)}
Uitleg:
- ALS-functie controleert de criteria die overeenkomen met alle waarden in de regio met de gegeven Oost-waarde in G5-cel en retourneert overeenkomstige TRUE-waarden uit het prijsbereik.
= GROOT ( { 58.41; 303.63 ; ONWAAR ; 153.34 ; 82.84 ; ONWAAR ; 520.01 ; ONWAAR ; 177 ; ONWAAR ; ONWAAR ; 57.97 ; 97.72 ; ONWAAR } ) , F5 )
- De SMALL-functie neemt de array die alle prijsklassen heeft zoals hierboven weergegeven en retourneert de 5e grootste waarde van de array zoals weergegeven in de onderstaande snapshot.
U kunt de formule in het formulevak bekijken zoals in de bovenstaande momentopname. Gebruik maken van Ctrl + Shift + Enter om het resultaat te krijgen.
Zoals u kunt zien, retourneert de matrixformule de 5e laagste prijs $ 153,34 met regio Oost.
U kunt array ook als array, criteria tussen aanhalingstekens en n-waarde rechtstreeks naar de functie voeren in plaats van celverwijzing of benoemd bereik te gebruiken.
Gebruik de formule:
{ = KLEIN ( ALS ( C3:C16 = "west" , D3:D16 ) , 3 )}
Gebruik maken van Ctrl + Shift + Enter
U kunt zien dat de formule goed werkt, waar u de op één na grootste waarde vindt met criteria in de tabel.
Hier zijn enkele observatienotities die hieronder worden weergegeven.
Opmerkingen:
- De formule werkt alleen met getallen.
- Plaats accolades niet handmatig. Dit is een matrixformule, moet worden gebruikt Ctrl + Shift + Enter in plaats van gewoon Enter waar het #GETAL! fout.
- Waarde van kan niet groter zijn dan het aantal criteriawaarde of het retourneert #GETAL! Fout.
- IF criteria komen niet overeen met de formule retourneert een fout.
- De argumentarray moet dezelfde lengte hebben, anders de functie.
Ik hoop dat dit artikel over het vinden van de n-de grootste met criteria en de n-de kleinste met criteria in Excel verklarend is. Bekijk hier meer artikelen over opzoekformules in Excel. 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 : Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe te gebruikende VERT.ZOEKEN-functie in Excel : 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 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.
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.