Generieke formule
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Samenvatting
Om de naam van de op één na grootste waarde te krijgen, kunt u INDEX en MATCH gebruiken met de functie LARGE. In het weergegeven voorbeeld is de formule in cel H5:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
waarbij naam (B5: B16) en score (D5: D16) bereiken worden genoemd.
Uitleg
In een notendop: deze formule gebruikt de functie LARGE om de op één na grootste waarde in een set gegevens te vinden. Zodra we die waarde hebben, pluggen we deze in een standaard INDEX- en MATCH-formule om de bijbehorende naam op te halen. Met andere woorden, we gebruiken de op één na grootste waarde als een "sleutel" om bijbehorende informatie op te halen.
De LARGE-functie is een eenvoudige manier om de op één na grootste waarde in een bereik te krijgen. Geef gewoon een bereik op voor het eerste argument (array) en een waarde voor n als het tweede argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Als u van binnen naar buiten werkt, is de eerste stap om de "1ste" grootste waarde in de gegevens te krijgen met de functie LARGE:
LARGE(score,F5) // returns 93
In dit geval is de waarde in F5 1, dus we vragen om de 1ste hoogste score (dwz de hoogste score), die 93 is. We kunnen de formule nu vereenvoudigen tot:
=INDEX(name,MATCH(93,score,0))
Binnen de functie INDEX, is de MATCH functie opgericht om de positie van de 93 vinden in het benoemde bereik score (D5: D16):
MATCH(93,score,0) // returns 3
Aangezien 93 in de derde rij verschijnt, geeft VERGELIJKEN 3 direct terug naar INDEX als het rijnummer, met naam als matrix:
=INDEX(name,3) // Hannah
Ten slotte retourneert de functie INDEX de naam in de derde rij, "Hannah".
Merk op dat we de waarden voor n ophalen uit het bereik F5: F7, om de 1e, 2e en 3e hoogste scores te krijgen terwijl de formule naar beneden wordt gekopieerd.
Groep ophalen
Dezelfde basisformule zal werken om alle bijbehorende informatie op te halen. Om de groep voor de grootste waarden te krijgen, kun je gewoon veranderen de INDEX geleverd met het benoemde bereik reeks groep :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Met de waarde 1 in F5 krijgt LARGE de hoogste score en levert de formule "A" op.
Opmerking: met Excel 365 kunt u de FILTER-functie gebruiken om de bovenste of onderste resultaten dynamisch weer te geven.
Met XLOOKUP
De functie XLOOKUP kan ook worden gebruikt om de naam van de op één na grootste waarde als volgt te retourneren:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE retourneert de grootste waarde, 93, rechtstreeks naar XLOOKUP als de opzoekwaarde:
=XLOOKUP(93,score,name) // Hannah
Met het benoemde bereik score (D5: D16) als lookup array, en de naam (B5: B16) als de terugkeer array, XLOOKUP returns "Hannah" als voorheen.
Omgaan met banden
Dubbele waarden in de numerieke gegevens zullen een "gelijkspel" creëren. Als er een gelijkspel optreedt in de waarden die worden gerangschikt, bijvoorbeeld als de eerste en de tweede grootste waarde hetzelfde zijn, retourneert LARGE dezelfde waarde voor elk. Wanneer deze waarde wordt doorgegeven aan de MATCH-functie, zal MATCH de positie van de eerste overeenkomst retourneren, zodat u dezelfde (eerste) naam terugziet.
Als er de mogelijkheid bestaat dat er een gelijkspel bestaat, wilt u misschien een of andere strategie voor het doorbreken van een stropdas implementeren. Eén benadering is om een nieuwe hulpkolom met waarden te creëren die zijn aangepast om de banden te verbreken. Gebruik vervolgens de helperkolomwaarden om informatie te rangschikken en op te halen. Dit maakt de logica die wordt gebruikt om banden te verbreken duidelijk en expliciet.
Een andere benadering is om gelijkspel te verbreken op basis van alleen positie (dwz het eerste gelijkspel "wint"). Hier is een formule die die benadering volgt:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter, behalve in Excel 365.
Hier gebruiken we MATCH om het nummer 1 te vinden, en we construeren een lookup-array met behulp van booleaanse logica die (1) alle scores vergelijkt met de waarde die wordt geretourneerd door LARGE:
score=LARGE(score,F5)
en (2) gebruikt een uitbreidende bereikcontrole als de naam al in de gerangschikte lijst staat:
COUNTIF(H$4:H4,name)=0
Als een naam al in de lijst staat, wordt deze "geannuleerd" door de logica en wordt de volgende (dubbele) waarde aangepast. Merk op dat het uitbreidende bereik begint op de vorige rij, om een kringverwijzing te vermijden.
Deze benadering werkt in dit voorbeeld omdat er geen dubbele namen in de naamkolom staan. Als er echter dubbele namen voorkomen in gerangschikte waarden, moet de aanpak worden aangepast. De eenvoudigste oplossing is om ervoor te zorgen dat namen uniek zijn.
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.
