¿Intenta obtener un valor mínimo basado en una o más condiciones? Simplemente use la función PEQUEÑO junto con IF. Este tutorial muestra cómo crear una solución que funcione y explica su lógica interna, para que pueda descifrar fácilmente la fórmula y ajustarla a sus necesidades.
Microsoft Excel tiene una serie de funciones para realizar cálculos «condicionales» como MAXIFS, MINIFS, AVERAGEIF y similares. Lamentablemente, la función SMALL IF no existe. Sin embargo, nada le impide construir su propia fórmula para encontrar el n-ésimo valor más pequeño con criterios. Si aún no está familiarizado con la función PEQUEÑA de Excel, es posible que desee comenzar con lo básico y leer primero el tutorial vinculado anteriormente.
Fórmula PEQUEÑA SI en Excel
Para obtener el n-ésimo valor más bajo que coincida con los criterios que especifique, puede usar la siguiente fórmula genérica:
{=PEQUEÑO(SI(rango_de_criterios=criterios, valores), n)}
Donde n es el primer, segundo, tercer, etc. valor más bajo a devolver.
Para que la fórmula funcione correctamente, debe ingresarla como una fórmula de matriz presionando las teclas Ctrl + Shift + Enter simultáneamente. Cuando haga esto, Excel rodeará la fórmula entre corchetes como se muestra en la captura de pantalla a continuación. En Excel 365, también funciona como una fórmula regular debido a la compatibilidad con matrices dinámicas.
De nuestra muestra de la tabla, seleccionemos las 3 calificaciones más bajas en un tema específico, digamos Arte. Con la lista de materias (criteria_range) en B2:B15, puntajes en C2:C15 (valores) y n en E3, la fórmula toma esta forma:
=SMALL(IF($B$2:$B$15="art", $C$2:$C$15), $E3)
Esta fórmula devuelve la puntuación de arte más pequeña en F3. Cópielo con F5 y obtendrá el segundo y el tercer resultado más bajo.
Para mayor comodidad, también puede ingresar los nombres de los sujetos objetivo en celdas predefinidas (F2 – Arte y G2 – Ciencia) y referirse a esas celdas como criterios:
=SMALL(IF($B$2:$B$15=F$2, $C$2:$C$15), $E3)
Si el diseño de su hoja de trabajo no proporciona n números, puede generarlos directamente en la fórmula utilizando la función FILAS con una referencia de rango en expansión como esta:
=SMALL(IF($B$2:$B$15=E$2,$C$2:$C$15), ROWS(A$2:A2))
Debido al uso inteligente de referencias absolutas y relativas, la referencia del rango se expande automáticamente a medida que la fórmula se copia en las celdas siguientes. En E3, ROWS(A$2:A2) genera n igual a 1, y la fórmula devuelve la puntuación más pequeña para Art. En E4, la referencia cambia a A $ 2: A3, lo que hace que ROWS devuelva 2, por lo que obtenemos la segunda puntuación más pequeña, y así sucesivamente.
En caso de que no se encuentre el n-ésimo valor más pequeño con los criterios especificados, la fórmula SMALL IF devolvería un error #NUM. Para atrapar este error y reemplazarlo con cualquier valor que encuentre apropiado («-» en nuestro caso), puede usar la función IFERROR:
=IFERROR(SMALL(IF($B$2:$B$15=$F$2, $C$2:$C$15), $E3), "-")
Cómo funciona esta fórmula:
A partir de una matriz proporcionada para el primer argumento, la función PEQUEÑA devuelve el enésimo valor más pequeño especificado en el segundo argumento. El problema es que no queremos que se procesen todos los valores de la matriz, sino solo las puntuaciones de una determinada materia. Para limitar la matriz a los valores de Art, le decimos a la función IF que compare la lista (B2:B15) con el tema de destino:
IF($B$2:$B$15="Art", $C$2:$C$15)
Debido a que la prueba lógica se realiza en una matriz de números, el resultado también es una matriz, donde los números representan las puntuaciones de Arte y FALSO valora cualquier otra puntuación:
{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;210;FALSE;125}
Como la función PEQUEÑA ignora los errores (y cualquier otra cosa que no sea un número), el resultado es el n-ésimo valor más pequeño de las puntuaciones de Arte en la matriz anterior.
Excel SMALL IF con múltiples criterios
Para hacer que una fórmula SMALL IF evalúe varias condiciones, elija uno de los siguientes enfoques.
Proporcione varios criterios mediante el uso de declaraciones IF anidadas:
{=PEQUEÑO(SI(criterios_rango1=criterios1, SI(criterios_rango2=criterios2, valores)), n)}
Multiplica las expresiones lógicas:
{=PEQUEÑO(SI((criterios_rango1=criterios1) * (criterios_rango2=criterios2), valores), n)}
Tenga en cuenta que ambas son fórmulas de matriz, así que no olvide presionar Ctrl + Shift + Enter para completarlas correctamente. En Excel 365, estos también funcionarán como fórmulas regulares.
Para probar la fórmula «en el campo», ampliaremos nuestra tabla de muestra con la columna Escuela e ingresaremos 2 criterios en celdas separadas como se muestra a continuación.
Ahora, tenemos todos los parámetros necesarios para nuestra fórmula SMALL IF:
- Criteria_range1 – lista de materias (B2:B15)
- Criterio1 – sujeto objetivo (G1)
- Criteria_range2 – tipos de escuela (C2:C15)
- Criterio 2 – escuela de destino (G2)
- Valores – puntuaciones (D2:D15)
- N – números 1 a 3 en F6, F7 y F8
Poniendo los argumentos juntos, obtenemos estas fórmulas:
=SMALL(IF($B$2:$B$15=$G$1, IF($C$2:$C$15=$G$2, $D$2:$D$15)), F6)
=SMALL(IF(($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2), $D$2:$D$15), F6)
Ingrese cualquiera de los dos en G6, arrástrelo a través de G8 y obtendrá el siguiente resultado.
Cómo funcionan estas fórmulas:
En esencia, ambas fórmulas usan la función SI para probar múltiples condiciones, de modo que solo los valores para los cuales todas las condiciones son VERDADERAS entran en el argumento de matriz de la función PEQUEÑA.
IF anidados:
En la prueba lógica de la primera función IF, comparamos la lista de sujetos con el objetivo (Art): $B$2:$B$15=$G$1. El resultado de esta operación es una matriz de valores VERDADERO y FALSO, donde VERDADERO corresponde a los valores de Arte en la columna B:
{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
La segunda declaración IF verifica qué celdas en el rango C2: C15 coinciden con el criterio en G2 (Junior), y también devuelve una matriz de VERDADERO y FALSO:
{FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE}
Para el argumento value_if_true del segundo IF, proporcionamos las puntuaciones (D2:D15). Esto asegura que solo los elementos que tienen VERDADERO en las dos matrices anteriores «sobreviven»; todos los demás puntajes se reemplazan con los valores FALSO:
{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;FALSE;FALSE;125}
Esta matriz final va a la función PEQUEÑA, desde la cual devuelve el valor n-ésimo más bajo.
Criterios de multiplicación:
La segunda fórmula verifica ambas condiciones dentro de una sola prueba lógica, donde la operación de multiplicación funciona como operador AND.
Cuando los elementos de dos matrices VERDADERO/FALSO se multiplican, los valores lógicos se convierten en 1 (VERDADERO) y 0 (FALSO). Como multiplicar por 0 da cero, la matriz resultante tiene 1 para los datos que cumplen ambos criterios:
{0;1;0;1;0;0;1;1;0;1;0;0;0;1}
La función IF evalúa esta matriz de 1 y 0 en la prueba lógica y pasa las puntuaciones correspondientes a 1 a SMALL.
Fórmula PEQUEÑA IF con múltiples criterios OR
El ejemplo anterior muestra cómo encontrar valores inferiores basados en múltiples criterios utilizando la lógica AND, es decir, cuando se cumplen todas las condiciones. Para obtener los valores más pequeños que satisfagan ningún de las condiciones, debe crear una fórmula PEQUEÑA IF con lógica OR. Para ello, sume los criterios en lugar de multiplicarlos.
{=PEQUEÑO(SI((criterios_rango1=criterios1) + (criterios_rango2=criterios2), valores), n)}
Como ejemplo, escojamos las puntuaciones más bajas en dos materias diferentes, digamos Historia y Literatura. En términos de Excel, la fórmula devolverá la n-ésima puntuación más pequeña si el tema es Historia O Literatura.
Con las materias en B2:B15 y las puntuaciones en C2:C15, esta es la fórmula para obtener la puntuación más baja:
=SMALL(IF(($B$2:$B$15="History") + ($B$2:$B$15="Literature"), $C$2:$C$15), 1)
Por supuesto, puede ingresar los criterios y n números en celdas separadas, complete la fórmula presionando Ctrl + Shift + Enter y obtenga este resultado:
=SMALL(IF(($B$2:$B$15=$E$2) + ($B$2:$B$15=$F$2), $C$2:$C$15), E6)
Cómo funciona esta fórmula:
La lógica de la fórmula es muy similar a lo que se discute en PEQUEÑO IF con múltiples criterios AND ejemplo. La diferencia es que las ecuaciones range=criteria se unen con la operación de suma que funciona como el operador OR en las fórmulas de matriz:
Verificar la lista de sujetos contra 2 criterios diferentes produce 2 matrices de valores VERDADERO y FALSO. Agregar los elementos de esos arreglos produce un arreglo de 1 y 0, donde los 1 corresponden a los elementos que cumplen al menos una condición (sin importar cuál) y los ceros corresponden a los elementos que no cumplen ninguna condición. Esta matriz final pasa a la prueba lógica de IF:
{0;1;0;1;0;0;1;1;0;1;0;1;0;1}
La función SI evalúa cada elemento de la matriz anterior y pasa los puntajes correspondientes a 1 a la función PEQUEÑA, de la cual toma el valor n-ésimo especificado:
{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;210;FALSE;125}
Cómo encontrar los valores más pequeños ignorando ceros
Aunque su conjunto de datos a veces puede contener algunos valores cero, no siempre tiene sentido incluir esos ceros en el resultado de la fórmula. Para omitir todos los valores 0, usaremos las ya conocidas fórmulas SMALL IF con el criterio «distinto de cero».
Fórmula 1. PEQUEÑO SI no es cero
Para obtener el enésimo valor más pequeño ignorando 0, use esta fórmula de matriz genérica:
{=PEQUEÑO(SI(valores<>0, valores), n)}
Supongamos que algunos estudiantes se perdieron algunos exámenes y tienen 0 puntajes en esas materias. Para obtener los 3 puntajes más bajos que son mayores que 0, la fórmula es:
=SMALL(IF($C$2:$C$15<>0, $C$2:$C$15), ROWS(A$2:A2))
Recuerde presionar CTRL + Shift + Enter para completarlo correctamente.
Esta fórmula va a la celda superior (E2), en la que extrae la puntuación más baja. Y luego, arrastra la fórmula hacia abajo a través de dos celdas más para extraer el segundo y el tercer puntaje más bajo. La función FILAS (A $ 2: A2) genera automáticamente n números, por lo que no necesita escribirlos en ninguna parte de la hoja.
Fórmula 2. PEQUEÑO SI mayor que cero con condición
Para encontrar el n-ésimo valor más pequeño mayor que cero según los criterios, coloque criterios adicionales de esta manera:
{=PEQUEÑO(SI((valores<>0) * (criterios_rango=criterios), valores), n)}
Como ejemplo, extraigamos los 3 puntajes más bajos para cada materia individualmente. Para esto, ingresamos los nombres de las materias en E2 (Arte) y F2 (Ciencias) y usamos esta fórmula para extraer los puntajes de Arte más pequeños:
=SMALL(IF(($C$2:$C$15<>0) * ($B$2:$B$15=E$2), $C$2:$C$15), ROWS(A$2:A2))
Arrastre la fórmula hacia la derecha y también obtendrá las puntuaciones de Ciencias más bajas:
Filtrar los valores más pequeños según los criterios
Esta solución solo funciona en compilaciones recientes de Excel 365 donde las funciones de matriz dinámica están disponibles.
Otra forma de obtener el n-ésimo valor inferior en Excel según las condiciones es usar la función PEQUEÑO junto con FILTRO. Esto funciona a la perfección como una fórmula normal que se completa con la tecla Intro.
La lógica de la fórmula es la misma que en los ejemplos anteriores. La diferencia es que utiliza la función FILTRO en lugar de SI para aplicar los criterios.
Fórmula 1. Encuentre el valor inferior n-ésimo con una condición
Si solo se debe cumplir una condición, puede obtener el enésimo valor más pequeño con esta fórmula:
PEQUEÑO(FILTRO(valores, criterio_rango=criterio), n)
Para nuestro conjunto de datos de muestra, la fórmula es la siguiente:
=SMALL(FILTER($C$2:$C$15, $B$2:$B$15=F$2), $E3)
Donde B2:B15 es el rango de criterios (lista de temas), C2:C15 son los valores (puntuaciones), F2 es el criterio (el tema de interés) y E3 es la n-ésima puntuación más pequeña a devolver.
Fórmula 2. Obtenga el valor n-ésimo más pequeño con múltiples criterios
Para probar múltiples condiciones, aquí está la fórmula a usar:
PEQUEÑO(FILTRO(valores, (criterios_rango1=criterios1) * (criterios_rango2=criterios2)), n)
Suponiendo que busca encontrar la puntuación más baja enésima en una materia específica (G1) entre los estudiantes de una escuela determinada (G2), la fórmula es:
=SMALL(FILTER($D$2:$D$15, ($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2)), $F6)
Donde B2:B15 es la lista de materias, C2:C15 son los tipos de escuelas, D2:D15 son las puntuaciones y F6 es el enésimo valor más pequeño que se devuelve.
Fórmula 3. Filtrar el n-ésimo valor más pequeño con criterios OR
Para filtrar el n-ésimo número más pequeño cuando esta o aquella condición es VERDADERA, la fórmula es:
PEQUEÑO(FILTRO(valores, (criterios_rango1=criterios1) + (criterios_rango2=criterios2)), n)
Por ejemplo, puede encontrar el puntaje más bajo en materias humanitarias (Historia o Literatura) usando esta fórmula:
=SMALL(FILTER($C$2:$C$15, ($B$2:$B$15=$E$2) + ($B$2:$B$15=$F$2)), $E6)
Donde B2:B15 es la lista de materias, C2:C15 son las puntuaciones, E2 y F2 son las materias de interés y E6 es el n-ésimo valor más pequeño.
Así es como se usa SMALL IF en Excel para encontrar los valores más pequeños según las condiciones. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Cuaderno de ejercicios para descargar
(archivo .xlsx)