Descarga el libro de trabajo de ejemplo
Este tutorial demostrará cómo calcular "si grande" o "si pequeño si", recuperando el enésimo valor más grande (o más pequeño) basado en criterios.
Funciones GRANDES Y PEQUEÑAS
La función LARGE se utiliza para calcular el n-ésimo valor más grande (k) en una matriz, mientras que la función PEQUEÑA devuelve el n-ésimo valor más pequeño.
Para crear un "Si grande", usaremos la función GRANDE junto con la función SI en una fórmula de matriz.
GRANDE SI
Combinando LARGE (o SMALL) e IF en una fórmula de matriz, podemos esencialmente crear una función "LARGE IF" que funciona de manera similar a cómo funciona la fórmula SUMIF incorporada. Veamos un ejemplo.
Tenemos una lista de calificaciones obtenidas por los estudiantes en dos materias diferentes:
Supongamos que se nos pide que encontremos las tres mejores calificaciones obtenidas para cada materia de la siguiente manera:
Para lograr esto, podemos anidar una función SI con el tema como nuestro criterio dentro de la función GRANDE así:
= GRANDE (SI (=,),)
= GRANDE (SI ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Cuando use Excel 2022 y versiones anteriores, debe ingresar la fórmula presionando CTRL + MAYÚS + ENTRAR para obtener las llaves alrededor de la fórmula.
¿Cómo funciona la fórmula?
La fórmula funciona evaluando cada celda en nuestro rango de criterios como VERDADERO o FALSO.
Encontrar el valor de calificación superior (k = 1) en matemáticas:
= GRANDE (SI ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= GRANDE (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 reemplaza cada valor con FALSO si no se cumple su condición.
= GRANDE ({0,81; FALSO; FALSO; 0,42; FALSO; 0,63; FALSO; 0,58; FALSO}, 1)
Ahora, la función GRANDE omite los valores FALSOS y calcula el más grande (k = 1) de los valores restantes (0,81 es el valor más grande entre 0,42 y 0,81).
PEQUEÑO SI
La misma técnica también se puede aplicar con la función PEQUEÑO.
= PEQUEÑO (SI ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
SI GRANDE con múltiples criterios
Para usar LARGE IF con múltiples criterios (similar a cómo funciona la fórmula SUMIFS incorporada), simplemente anide más funciones IF en la función LARGE así:
= GRANDE (SI (=, SI (=,)),)
= GRANDE (SI ($ D $ 2: $ D $ 18 = $ H3, SI ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
Otra forma de incluir varios criterios es multiplicar los criterios como se muestra en este artículo.
Consejos y trucos:
- Siempre que sea posible, siempre haga referencia a la posición (k) de una celda auxiliar y bloquee la referencia (F4), ya que esto facilitará el llenado automático de fórmulas.
- Si está utilizando Excel 2022 o una versión más reciente, puede ingresar la fórmula sin Ctrl + Shift + Enter.
- Para recuperar los nombres de los estudiantes que obtuvieron las mejores calificaciones, combínelos con INDEX MATCH
