EXCEL: enésimo valor más grande con duplicados

Para obtener el enésimo valor más grande en un conjunto de datos con duplicados, puede usar una fórmula de matriz basada en las funciones MAX y SI.

Nota: la función K.ESIMO.MAYOR devolverá fácilmente enésimos valores, pero K.ESIMO.MAYOR devolverá duplicados cuando existan en los datos de origen.

En el ejemplo que se muestra, la fórmula en E6 es:

=MAX(SI(rng<E5,rng))

Donde "rng" es el rango nombrado B5: B11

Cómo funciona esta fórmula

Primero, obtenemos el valor más grande usando la función MAX en E5:

=MAX(rng)

Una vez que tenemos establecido el valor más grande, creamos otra fórmula que simplemente verifica todos los valores en el rango nombrado "rng" contra el "último valor más grande":

=MAX(SI(rng<E5,rng))

Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.

Cualquier valor que sea menor que el "último más grande" sobrevive a la prueba, y cualquier valor que no termine es FALSO. La matriz resultante se ve así:

{12;FALSO;FALSO;11;12;12;10}

A medida que la fórmula se copia en la columna, E5 se incrementa en cada fila, de modo que MAX y SI devuelven una nueva matriz. La función MAX continúa devolviendo el valor más grande en cada matriz: el enésimo valor de la serie.

Sería genial tener una fórmula todo en uno que trate n directamente. Si tienes uno, repica a continuación.