Fórmula genérica
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Resumen
Para obtener el nombre del enésimo valor más grande, puede utilizar INDICE y COINCIDIR con la función LARGE. En el ejemplo que se muestra, la fórmula en la celda H5 es:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
donde nombre (B5: B16) y puntuación (D5: D16) son rangos con nombre.
Explicación
En pocas palabras, esta fórmula utiliza la función LARGE para encontrar el enésimo valor más grande en un conjunto de datos. Una vez que tenemos ese valor, lo conectamos a una fórmula estándar de INDICE y COINCIDIR para recuperar el nombre asociado. En otras palabras, usamos el enésimo valor más grande como una "clave" para recuperar la información asociada.
La función LARGE es una forma sencilla de obtener el enésimo valor más grande de un rango. Simplemente proporcione un rango para el primer argumento (matriz) y un valor para n como segundo argumento (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Trabajando desde adentro hacia afuera, el primer paso es obtener el "primer" valor más grande en los datos con la función GRANDE:
LARGE(score,F5) // returns 93
En este caso, el valor en F5 es 1, por lo que pedimos la primera puntuación más grande (es decir, la puntuación más alta), que es 93. Ahora podemos simplificar la fórmula para:
=INDEX(name,MATCH(93,score,0))
Dentro de la función INDICE, la función COINCIDIR está configurada para ubicar la posición de 93 en la puntuación del rango con nombre (D5: D16):
MATCH(93,score,0) // returns 3
Dado que 93 aparece en la tercera fila, COINCIDIR devuelve 3 directamente a ÍNDICE como número de fila, con nombre como matriz:
=INDEX(name,3) // Hannah
Finalmente, la función INDICE devuelve el nombre en la tercera fila, "Hannah".
Observe que estamos recogiendo los valores de n del rango F5: F7, para obtener los puntajes más altos 1, 2 y 3 a medida que se copia la fórmula.
Recuperar grupo
La misma fórmula básica funcionará para recuperar cualquier información asociada. Para obtener el grupo de los valores más grandes, simplemente puede cambiar la matriz proporcionada a INDEX con el grupo de rango con nombre :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Con el valor 1 en F5, LARGE obtendrá la puntuación más alta y la fórmula devolverá "A".
Nota: con Excel 365, puede usar la función FILTRO para enumerar los resultados superiores o inferiores de forma dinámica.
Con XLOOKUP
La función XLOOKUP también se puede utilizar para devolver el nombre del enésimo valor más grande como este:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE devuelve el valor más grande, 93, directamente a XLOOKUP como valor de búsqueda:
=XLOOKUP(93,score,name) // Hannah
Con la puntuación del rango con nombre (D5: D16) como la matriz de búsqueda y el nombre (B5: B16) como la matriz de retorno, XLOOKUP devuelve "Hannah" como antes.
Manejo de corbatas
Los valores duplicados en los datos numéricos crearán un "empate". Si ocurre un empate en los valores que se clasifican, por ejemplo, si el primer y el segundo valor más grande son iguales, LARGE devolverá el mismo valor para cada uno. Cuando este valor se pasa a la función COINCIDIR, COINCIDIR devolverá la posición de la primera coincidencia, por lo que verá el mismo (primer) nombre devuelto.
Si existe la posibilidad de empates, es posible que desee implementar algún tipo de estrategia de desempate. Un enfoque es crear una nueva columna auxiliar de valores que se han ajustado para romper los lazos. Luego, use los valores de la columna auxiliar para clasificar y recuperar información. Esto hace que la lógica utilizada para romper los lazos sea clara y explícita.
Otro enfoque es romper los empates basándose únicamente en la posición (es decir, el primer empate "gana"). Aquí hay una fórmula que adopta ese enfoque:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365.
Aquí, usamos MATCH para encontrar el número 1, y construimos una matriz de búsqueda usando lógica booleana que (1) compara todas las puntuaciones con el valor devuelto por LARGE:
score=LARGE(score,F5)
y (2) utiliza una verificación de rango en expansión si el nombre ya está en la lista clasificada:
COUNTIF(H$4:H4,name)=0
Cuando un nombre ya está en la lista, la lógica lo "cancela" y el siguiente valor (duplicado) coincide. Observe que el rango de expansión comienza en la fila anterior, para evitar una referencia circular.
Este enfoque funciona en este ejemplo porque no hay nombres duplicados en la columna de nombre. Sin embargo, si aparecen nombres duplicados en valores clasificados, el enfoque debe ajustarse. La solución más sencilla es asegurarse de que los nombres sean únicos.
Notas
- Para obtener el nombre del enésimo valor con criterios (es decir, limitar los resultados al grupo A o B), deberá ampliar la fórmula para utilizar una lógica adicional.
- En Excel 365, la función FILTRO es una mejor manera de enumerar los resultados superiores o inferiores de forma dinámica. Este enfoque manejará automáticamente las ataduras.
