Generieke formule
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Samenvatting
Om de naam van de n-de grootste waarde met criteria te krijgen, kunt u INDEX en MATCH, de LARGE-functie en een filter gebruiken die is gemaakt met de IF-functie. In het weergegeven voorbeeld is de formule in cel G5, naar beneden gekopieerd,:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
waarbij naam (B5: B16), groep (C5: C16) en score (D5: D16) benoemde bereiken zijn. De formule retourneert de naam die is gekoppeld aan de 1e, 2e en 3e hoogste waarden in groep A.
Opmerking: dit is een matrixformule die moet worden ingevoerd met control + shift + enter, behalve in Excel 365.
Uitleg
De LARGE-functie is een gemakkelijke manier om de op één na grootste waarde in een bereik te krijgen:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
In dit voorbeeld kunnen we de LARGE-functie gebruiken om een hoogste score te krijgen, en vervolgens de score als een "sleutel" gebruiken om de bijbehorende naam met INDEX en MATCH op te halen. Merk op dat we de waarden voor n ophalen uit het bereik F5: F7, om de 1e, 2e en 3e hoogste scores te krijgen.
De twist in dit geval is echter dat we onderscheid moeten maken tussen scores in groep A en groep B. Met andere woorden, we moeten criteria toepassen. We doen dit met de IF-functie, die wordt gebruikt om waarden te "filteren" voordat ze worden geëvalueerd met LARGE. Als algemeen voorbeeld, om de grootste waarde (dat wil zeggen de eerste waarde) in bereik2 te krijgen, waarbij bereik 1 = "A", kunt u een formule als deze gebruiken:
LARGE(IF(range="A",range2),1)
Opmerking: als u op deze manier IF gebruikt, wordt dit een matrixformule.
Als u van binnen naar buiten werkt, is de eerste stap om de "1ste" grootste waarde te krijgen in de gegevens die zijn gekoppeld aan groep A met de functie LARGE:
LARGE(IF(group="A",score),F5)
In dit geval is de waarde in F5 is 1, dus we vragen om de hoogste score in groep A. Wanneer de IF-functie wordt berekend, is het test elke waarde in het benoemde bereik groep . Het benoemde bereik score is voorzien voor waarde_indien_waar. Dit genereert een nieuwe array, die rechtstreeks naar de LARGE-functie wordt geretourneerd:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Merk op dat de enige scores die het filter overleven, afkomstig zijn uit groep A. LARGE retourneert vervolgens de hoogste resterende score, 93, rechtstreeks naar de MATCH-functie als een opzoekwaarde. We kunnen de formule nu vereenvoudigen om:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Nu kunnen we zien dat de MATCH-functie is geconfigureerd met dezelfde gefilterde array die we hierboven hebben gezien. De ALS-functie filtert opnieuw ongewenste waarden uit en het MATCH-gedeelte van de formule lost het volgende op:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Aangezien 93 op de 3e positie verschijnt, geeft MATCH 3 direct terug naar de functie INDEX:
=INDEX(name,3) // Hannah
Ten slotte retourneert de functie INDEX de naam in de derde rij, "Hannah".
Met XLOOKUP
De functie XLOOKUP kan ook worden gebruikt om dit probleem op te lossen, met dezelfde aanpak als hierboven uitgelegd:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Zoals hierboven is LARGE geconfigureerd om te werken met een array gefilterd door IF, en retourneert het resultaat 93 naar XLOOKUP als de opzoekwaarde:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
De lookup-array wordt ook gemaakt door IF te gebruiken als een filter op scores uit groep A. Met de return-array als naam (B5: B16). XLOOKUP retourneert "Hannah" als het eindresultaat.
Opmerkingen
- Om de naam van de n-de waarde met criteria te krijgen (dwz de resultaten beperken tot groep A of B), moet u de formule uitbreiden om aanvullende logica te gebruiken.
- In Excel 365 is de FILTER-functie een betere manier om de bovenste of onderste resultaten dynamisch weer te geven. Deze aanpak zal automatisch banden afhandelen.
