Fórmula genérica
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Resumen
Para clasificar elementos en una lista usando uno o más criterios, puede usar la función CONTAR. En el ejemplo que se muestra, la fórmula en E5 es:
=COUNTIFS(groups,C5,scores,">"&D5)+1
donde "grupos" es el rango con nombre C5: C14 y "puntuaciones" es el rango con nombre D5: D14. El resultado es un rango para cada persona en su propio grupo.
Nota: aunque los datos están ordenados por grupo en la captura de pantalla, la fórmula funcionará bien con datos sin clasificar.
Explicación
Aunque Excel tiene una función RANK, no existe una función RANKIF para realizar un rango condicional. Sin embargo, puede crear fácilmente un RANK condicional con la función COUNTIFS.
La función CONTAR.SI 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 el rango denominado "grupos" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Por sí solo, esto devolverá el total de miembros del grupo en el grupo "A", que es 5.
El segundo criterio restringe el recuento a solo puntuaciones superiores a la "puntuación actual" de D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Los dos criterios funcionan juntos para contar filas en las que el grupo es A y la puntuación es más alta. Para el primer nombre de la lista (Hannah), no hay puntuaciones más altas en el grupo A, por lo que COUNTIFS devuelve cero. En la siguiente fila (Edward), hay tres puntuaciones en el grupo A superiores a 79, por lo que COUNTIFS devuelve 3. Y así sucesivamente.
Para obtener una clasificación adecuada, simplemente agregamos 1 al número devuelto por COUNTIFS.
Orden de clasificación inverso
Para invertir el orden de clasificación y la clasificación en orden (es decir, el valor más pequeño se clasifica en el puesto # 1) simplemente use el operador menor que ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
En lugar de contar puntuaciones mayores que D5, esta versión contará puntuaciones menores que el valor en D5, invirtiendo efectivamente el orden de clasificación.
Duplicados
Al igual que la función RANK, la fórmula de esta página asignará valores duplicados al mismo rango. Por ejemplo, si a un valor específico se le asigna un rango de 3, y hay dos instancias del valor en los datos que se están clasificando, ambas instancias recibirán un rango de 3 y el siguiente rango asignado será 5. Para imitar el comportamiento de la función RANK.
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
El resultado de esta fórmula anterior se puede agregar al rango original para obtener un rango promedio. Cuando un valor no tiene duplicados, el código anterior devuelve cero y no tiene ningún efecto.
