Fórmula genérica
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Resumen
Para ordenar dinámicamente datos que contienen solo valores numéricos, puede usar una columna auxiliar y una fórmula creada con las funciones RANK y COUNTIF. En el ejemplo que se muestra, la fórmula en D5 es:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
donde "ventas" es el rango con nombre C5: C11.
Explicación
Nota: esta fórmula es la configuración de una fórmula que puede extraer y mostrar datos utilizando un orden de clasificación predefinido en una columna auxiliar. Un ejemplo aquí.
El núcleo de esta fórmula es la función RANK, que se utiliza para generar un rango de valores de ventas, donde el número más alto se clasifica como # 1:
=RANK(C5,sales)
Aquí, RANK utiliza el rango denominado "ventas" (C5: C11) por conveniencia. De forma predeterminada, RANK asignará 1 al valor más alto, 2 al segundo valor más alto y así sucesivamente. Esto funciona perfectamente siempre que los valores numéricos sean únicos. Sin embargo, para manejar valores numéricos que contienen duplicados, necesitamos usar la función CONTAR.SI para romper empates. Esto se hace agregando el resultado de este fragmento al valor devuelto por RANK:
COUNTIF($C$5:C5,C5)-1
Observe que el rango se ingresa como una referencia mixta que se expandirá a medida que la fórmula se copia en la tabla. Como está escrito, esta referencia incluirá la fila actual, por lo que restamos 1 para "poner a cero" la primera aparición. Esto significa que la expresión devolverá cero para cada valor numérico hasta que se encuentre un duplicado. En la segunda instancia, la expresión devolverá 1, en la tercera instancia, devolverá 2, y así sucesivamente. Esto efectivamente rompe los lazos y permite que la fórmula genere una lista secuencial de números sin espacios.
Una vez que la fórmula está en su lugar, los datos se pueden ordenar por la columna auxiliar. También se puede recuperar con INDEX utilizando los valores de la columna de ayuda.
Nota: Esta fórmula está adaptada de un ejemplo del excelente libro Control + Shift + Enter, de Mike Girvin.
