Fórmulas K.ESIMO.MAYOR SI y K.ESIMO.MENOR SI en Excel y Google Sheets

Este tutorial demostrará cómo calcular «K.ESIMO.MAYOR SI» o «K.ESIMO.MENOR SI», recuperando el enésimo valor más grande (o más pequeño) basado en criterios.

 

Funciones K.ESIMO.MAYOR y K.ESIMO.MENOR

La función K.ESIMO.MAYOR se utiliza para calcular el enésimo valor más grande (k) de una matriz, mientras que la función K.ESIMO.MENOR devuelve el enésimo valor más pequeño.

=K.ESIMO.MAYOR($D$2:$D$10;1)

 

Para crear un «K.ESIMO.MAYOR SI», utilizaremos la función K.ESIMO.MAYOR junto con la función SI en una fórmula de matriz.

K.ESIMO.MAYOR SI

Combinando la función K.ESIMO.MAYOR (o K.ESIMO.MENOR) y la función SI en una fórmula de matriz, podemos crear esencialmente una función «K.ESIMO.MAYOR SI» que funciona de forma similar a la función SUMAR.SI.CONJUNTO incorporada.

Veamos un ejemplo. Tenemos una lista de calificaciones obtenidas por los estudiantes en dos asignaturas diferentes:

 

Supongamos que se nos pide que encontremos las tres mejores notas conseguidas en cada asignatura, así:

Para conseguirlo, podemos anidar una función IF con la asignatura como criterio dentro de la función LARGE así:

=K.ESIMO.MAYOR(SI(<rango de criterios>=<criterios>, <rango de valores>),<posición>)

=K.ESIMO.MAYOR(SI($C$2:$C$10=$F3;$D$2:$D$10);G$2)

Cuando se utiliza Excel 2019 y anteriores, hay que introducir la fórmula pulsando CTRL + SHIFT + ENTER para que aparezcan las llaves alrededor de la fórmula.

 

¿Cómo funciona la fórmula?

La fórmula funciona evaluando cada celda de nuestro rango de criterios como VERDADERO o FALSO. Encontrando el valor de la nota más alta (k=1) en Math:

=K.ESIMO.MAYOR(SI($C$2:$C$10=$F3;$D$2:$D$10);G$2)

=K.ESIMO.MAYOR(SI({VERDADERO;FALSO;FALSO;VERDADERO;FALSO;VERDADERO;FALSO;VERDADERO;FALSO};{0.81;0.8;0.93;0.42;0.87;0.63;0.71;0.58;0.73});"1")

A continuación, la función SI sustituye cada valor por FALSO si no se cumple su condición.

=K.ESIMO.MAYOR({0.81;FALSO;FALSO;0.42;FALSO;0.63;FALSO;0.58;FALSO};"1")

Ahora la función K.ESIMO.MAYOR se salta los valores FALSO y calcula el mayor (k=1) de los valores restantes (0,81 es el mayor valor entre 0,42 y 0,81).

K.ESIMO.MENOR SI

La misma técnica puede aplicarse también con la función SMALL en su lugar.

=K.ESIMO.MENOR(SI($C$2:$C$10=$F3;$D$2:$D$10);G$2)

 

K.ESIMO.MAYOR SI con múltiples criterios

Para utilizar LARGE IF con múltiples criterios (de forma similar a como funciona la fórmula incorporada SUMIFS), simplemente anide más funciones IF en la función LARGE de la siguiente forma

=K.ESIMO.MAYOR(SI(<rango de criterios1>=<criterio1>; SI(<rango de criterios2>=<criterio2>; <rango de valores>));<posición>)

=K.ESIMO.MAYOR(SI($D$2:$D$18=$H3;SI($B$2:$B$18=$G3;$E$2:$E$18));I$2)

Otra forma de incluir múltiples criterios es multiplicar los criterios juntos, como se muestra en este artículo sobre el cálculo de la MEDIANA SI.

Consejos y trucos:

• Siempre que sea posible, haga referencia a la posición (k) de una celda de ayuda y bloquee la referencia (F4), ya que esto facilitará el autorellenado de las fórmulas.
• Si está utilizando Excel 2019 o más reciente, puede introducir la fórmula sin CTRL + SHIFT + ENTER.
• Para recuperar los nombres de los estudiantes que obtuvieron las mejores calificaciones, combine con esto con INDICE / COINCIDIR.