EXCEL: Clasificar elementos usando uno o más criterios

Para clasificar elementos en una lista usando uno o más criterios, puede usar la función CONTAR.SI.CONJUNTO. En el ejemplo que se muestra, la fórmula en E5 es:

=CONTAR.SI.CONJUNTO(groups,C5,scores,">"&D5)+1

donde "grupos" es el rango con nombre C5: C14, y "puntajes" es el rango con nombre D5: D14.
El resultado es un rango para cada persona en su propio grupo. Nota: aunque los datos se ordenan por grupo en la captura de pantalla, la fórmula funcionará bien con datos sin clasificar.

Cómo funciona esta fórmula

Aunque Excel tiene una función JERARQUIA, no hay una función JERARQUIAIF para realizar un rango condicional. Sin embargo, puede crear fácilmente una JERARQUIA condicional con la función CONTAR.SI.CONJUNTO.
La función CONTAR.SI.CONJUNTO puede realizar un recuento condicional utilizando dos o más criterios. Los criterios se ingresan en pares de rango / criterio. En este caso, el primer criterio restringe el recuento al mismo grupo, utilizando los "grupos" de rango con nombre (C5: C14):

=CONTAR.SI.CONJUNTO(groups,C5)

Por sí solo, esto devolverá el total de miembros del grupo en el grupo "A", que es 5.
El segundo criterio restringe el conteo a solo puntajes mayores que el "puntaje actual" de D5:

=CONTAR.SI.CONJUNTO(groups,C5,scores,">"&D5)

Los dos criterios trabajan juntos para contar las filas donde el grupo es A y el puntaje es más alto. Para el primer nombre de la lista (Hannah), no hay puntuaciones más altas en el grupo A, por lo que CONTAR.SI.CONJUNTO devuelve cero. En la siguiente fila (Edward), hay tres puntajes en el grupo A superiores a 79, por lo que CONTAR.SI.CONJUNTO devuelve 3. Y así sucesivamente.
Para obtener un rango adecuado, simplemente agregamos 1 al número devuelto por CONTAR.SI.CONJUNTO.

Orden de rango inverso

Para revertir el orden de clasificación y la clasificación en orden (es decir, el valor más pequeño se clasifica como # 1) solo use el operador menor que (<) en lugar de mayor que (>):

=CONTAR.SI.CONJUNTO(groups,C5,scores,"<"&D5)+1

En lugar de contar puntajes mayores que D5, esta versión contará puntajes menores que el valor en D5, invirtiendo efectivamente el orden de rango.