En este breve tutorial, hablaremos sobre la función PEQUEÑA de Excel, cómo funciona y cómo usarla para encontrar el enésimo número, la fecha o la hora más pequeños.
¿Necesita encontrar algunos números más bajos en una hoja de trabajo? Esto es bastante fácil de hacer con la función Ordenar de Excel. ¿No quiere perder el tiempo reordenando sus datos con cada cambio? La función PEQUEÑO lo ayudará a encontrar rápidamente el valor más bajo, el segundo más pequeño, el tercero más pequeño, etc.
Función PEQUEÑA de Excel
SMALL es una función estadística que devuelve el n-ésimo valor más pequeño de un conjunto de datos.
La sintaxis de la función PEQUEÑA incluye dos argumentos, ambos necesarios.
PEQUEÑO(matriz, k)
Dónde:
- Formación – una matriz o un rango de celdas de las que extraer el valor más pequeño.
- k – un número entero que indica la posición desde el valor más bajo a devolver, es decir, el k-ésimo más pequeño.
La función está disponible en todas las versiones de Excel para Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 y versiones anteriores.
Fórmula pequeña básica en Excel
Una fórmula PEQUEÑA en su forma básica es muy fácil de construir: solo especifica el rango y la posición desde el elemento más pequeño para devolver.
En la lista de números en B2:B10, supongamos que desea extraer el tercer valor más pequeño. La fórmula es tan simple como:
=SMALL(B2:B10, 3)
Para facilitarle la verificación del resultado, la columna B está ordenada en orden ascendente:
4 cosas que debes saber sobre la función SMALL
Las siguientes notas de uso lo ayudarán a comprender mejor el comportamiento de la función PEQUEÑA y evitar confusiones al crear sus propias fórmulas.
- Ningún celdas en blanco, texto valores, y lógico los valores VERDADERO y FALSO en el argumento de la matriz se ignoran.
- Si la matriz contiene uno o más erroresse devuelve un error.
- En caso de que haya duplicados en matriz, su fórmula puede dar como resultado «empates». Por ejemplo, si dos celdas contienen el número 1 y la función PEQUEÑA está configurada para devolver el valor más pequeño y el segundo más pequeño, obtendrá 1 en ambos casos.
- Suponiendo que n es el número de valores en la matriz, SMALL(array,1) devolverá el valor más bajo y SMALL(array,n) elegirá el valor más alto.
Cómo usar la función PEQUEÑA en Excel – ejemplos de fórmulas
Y ahora, veamos algunos ejemplos más de la función PEQUEÑA de Excel que van más allá de su uso básico.
Encuentre los valores inferiores 3, 5, 10, etc.
Como ya sabe, la función PEQUEÑA está diseñada para calcular el valor n-ésimo más bajo. Este ejemplo muestra cómo hacer esto de la manera más efectiva.
En la siguiente tabla, suponga que desea encontrar los 3 valores inferiores. Para esto, escriba los números 1, 2 y 3 en celdas separadas (D3, D4 y D5 en nuestro caso). Luego, ingrese la siguiente fórmula en E3 y arrástrela hacia abajo a través de E5:
=SMALL($B$2:$B$10, D3)
En E3, la fórmula extrae el valor más pequeño usando el número en D3 para el argumento k. La clave es proporcionar referencias de celda adecuadas debido a que la fórmula se copia correctamente en otras celdas: absoluta para matriz y relativa para k.
¿No quiere molestarse en escribir los rangos manualmente? Utilice la función FILAS con un rango de expansión referencia para proporcionar el valor de k. Para esto, hacemos una referencia absoluta para la primera celda (o solo bloqueamos la coordenada de la fila como B$2) y una referencia relativa para la última celda:
=SMALL($B$2:$B$10, ROWS(B$2:B2))
Como resultado, la referencia del rango se expande a medida que la fórmula se copia en la columna. En D2, ROWS(B$2:B2) produce 1 para k y la fórmula devuelve el costo más bajo. En D3, ROWS(B$2:B3) produce 2 y obtenemos el segundo costo más bajo, y así sucesivamente.
Simplemente copie la fórmula a través de 5 celdas y obtendrá los 5 valores inferiores:
Sumar valores N inferiores
¿Quiere encontrar un total de los valores n más pequeños en un conjunto de datos? Si ya ha extraído los valores como se muestra en el ejemplo anterior, la solución más fácil sería una fórmula SUMA como:
=SUM(E3:E5)
O puede hacer una fórmula independiente usando la función PEQUEÑO junto con SUMAPRODUCTO:
SUMAPRODUCTO(PEQUEÑO(matriz, {1, …, n}))
Para obtener la suma de los 3 valores inferiores en nuestro conjunto de datos, la fórmula toma esta forma:
=SUMPRODUCT(SMALL(B2:B10, {1,2,3}))
La función SUMA producirá el mismo resultado:
=SUM(SMALL(B2:B10, {1,2,3}))
Nota. Si utiliza referencias de celda en lugar de constante de matriz para k, debe presionar Ctrl + Shift + Enter para convertirla en una fórmula de matriz. En Excel 365 que admite matrices dinámicas, SUM SMALL funciona como una fórmula regular en cualquier caso.
Cómo funciona esta fórmula:
En una fórmula normal, PEQUEÑO devuelve un solo k-ésimo valor más pequeño en un rango. En este caso, proporcionamos una constante de matriz como {1,2,3} para el argumento k, obligándolo a devolver una matriz de los 3 valores más pequeños:
{29240, 43610, 58860}
La función SUMPRODUCT o SUM suma los números de la matriz y genera el total. ¡Eso es todo!
Fórmula INDEX MATCH SMALL para obtener coincidencias más pequeñas
En una situación en la que desee recuperar algunos datos asociados con el valor más pequeño, use la combinación clásica ÍNDICE COINCIDIR con PEQUEÑO para el valor de búsqueda:
ÍNDICE(return_array, COINCIDIR(PEQUEÑO(lookup_array, n), lookup_array, 0))
Dónde:
- Retorno_matriz es un rango desde el cual extraer los datos asociados.
- Lookup_array es un rango donde buscar el valor n-ésimo más bajo.
- norte es la posición del menor valor de interés.
Por ejemplo, para obtener el nombre del proyecto que tiene el costo más bajo, la fórmula en E3 es:
=INDEX($A$2:$A$10, MATCH(SMALL($B$2:$B$10, D3), $B$2:$B$10, 0))
Donde A2:A10 son los nombres de los proyectos, B2:B10 son los costos y D3 es el rango desde el más pequeño.
Copie la fórmula en las celdas siguientes (E4 y E5) y obtendrá los nombres de los 3 proyectos más baratos:
Notas:
- Esta solución funciona bien para un conjunto de datos que no tiene duplicados. Sin embargo, dos o más valores duplicados en una columna numérica pueden crear «empates» en la clasificación, lo que conducirá a resultados erróneos. En este caso, utilice una fórmula un poco más sofisticada para desempatar.
- En Excel 365, esta tarea se puede realizar con la ayuda de las nuevas funciones de matriz dinámica. Además de ser mucho más simple, este enfoque resuelve automáticamente el problema de las ataduras. Para obtener detalles completos, consulte Cómo filtrar los valores inferiores de N en Excel.
Ordenar números de menor a mayor con una fórmula
Creo que todo el mundo sabe cómo poner los números en orden con la función de clasificación de Excel. Pero, ¿sabe cómo realizar la clasificación con una fórmula? Los usuarios de Excel 365 pueden hacerlo de forma sencilla con la nueva función ORDENAR. En Excel 2019, 2016 y versiones anteriores, SORT no funciona, por desgracia. Pero ten un poco de fe, y SMALL vendrá al rescate 🙂
Como en el primer ejemplo, usamos la función FILAS con una referencia de rango en expansión para incrementar k en 1 en cada fila donde se copia la fórmula:
=SMALL($A$2:$A$10, ROWS(A$2:A2))
Ingrese la fórmula en la primera celda y luego arrástrela hasta tantas celdas como valores haya en el conjunto de datos original (C2:C10 en este ejemplo):
Consejo. Para ordenar descendiendoutilice la función GRANDE en lugar de PEQUEÑO.
Fórmula PEQUEÑA de Excel para fechas y horas
Debido a que las fechas y las horas también son valores numéricos (en el sistema interno de Excel, las fechas se almacenan como números secuenciales y las horas como fracciones decimales), la función PEQUEÑA también puede manejarlas sin ningún esfuerzo adicional de su parte.
Como puede ver en las capturas de pantalla a continuación, una fórmula básica que usamos para números también funciona maravillosamente para fechas y horas:
=SMALL($B$2:$B$10, D2)
Fórmula PEQUEÑA para encontrar las primeras 3 fechas:
Fórmula PEQUEÑA para obtener los 3 tiempos más cortos:
El siguiente ejemplo muestra cómo la función PEQUEÑA puede ayudarlo a realizar una tarea más específica relacionada con las fechas.
Encuentre una fecha anterior más cercana a hoy o una fecha especificada
En una lista de fechas, supongamos que desea encontrar la fecha más cercana antes de una fecha específica. Esto se puede hacer usando la función PEQUEÑO en combinación con CONTAR.SI.
Con la lista de fechas en B2:B10 y la fecha objetivo en E1, la siguiente fórmula devolverá una fecha anterior más cercana a la fecha objetivo:
=SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1))
Para extraer una fecha que es dos fechas antes de la fecha en E1, es decir, una fecha anterior pero una, la fórmula es:
=SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1)
Para encontrar una fecha pasada más cercano a hoyuse la función HOY para los criterios de COUNTIF:
=SMALL(B2:B10, COUNTIF(B2:B10, "<"&TODAY()))
Consejo. Para evitar errores en situaciones en las que no se encuentra una fecha que coincida con sus criterios, puede ajustar la función IFERROR alrededor de su fórmula, así:
=IFERROR(SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1), "Not Found")
Cómo funcionan estas fórmulas:
La idea general es contar el número de fechas menores que la fecha objetivo con COUNTIF. Y este conteo es exactamente lo que la función PEQUEÑA necesita para el argumento k.
Para comprender mejor el concepto, veámoslo desde otro ángulo:
Si el 1 de agosto de 2020 (la fecha objetivo en E1) apareciera en nuestro conjunto de datos, sería la séptima fecha más grande de la lista. En consecuencia, hay seis fechas más pequeñas que él. Es decir, la sexta fecha más pequeña es la fecha anterior más cercana a la fecha objetivo.
Entonces, primero calculamos cuántas fechas son más pequeñas que la fecha en E1 (el resultado es 6):
COUNTIF(B2:B10, "<"&E1)
Y luego, inserte el conteo en el segundo argumento de PEQUEÑO:
=SMALL(B2:B10, 6)
Para obtener la fecha anterior pero una (que es la quinta fecha más pequeña en nuestro caso), restamos 1 del resultado de CONTAR.SI.
Cómo resaltar valores inferiores en Excel
Para resaltar los valores n más pequeños en su tabla con el formato condicional de Excel, puede usar una opción superior/inferior integrada o configurar su propia regla basada en una fórmula PEQUEÑA. El primer método es más rápido y fácil de aplicar, mientras que el segundo brinda más control y flexibilidad. Los siguientes pasos lo guiarán a través de la creación de una regla personalizada:
- Seleccione el rango en el que desea resaltar los valores inferiores. En nuestro caso, los números están en B2:B10, por lo que lo seleccionamos. Si desea resaltar filas enteras, seleccione A2:B10.
- En la pestaña Inicio, en el grupo Estilos, haga clic en Formato condicional > Nueva regla.
- En el cuadro de diálogo Nueva regla de formato, elija Usar una fórmula para determinar a qué celdas dar formato.
- En el cuadro Formatear valores donde esta fórmula es verdadera, ingrese una fórmula como esta:
=B2<=SMALL($B$2:$B$10, 3)Donde B2 es la celda más a la izquierda del rango numérico que se va a comprobar, $B$2:$B$10 es el rango completo y 3 son los n valores inferiores que se van a resaltar.
En su fórmula, tenga en cuenta los tipos de referencia: la celda más a la izquierda es una referencia relativa (B2) mientras que el rango es la referencia absoluta ($B$2:$B$10).
- Haga clic en el botón Formato y elija el formato que desee.
- Haga clic en Aceptar dos veces para cerrar ambas ventanas de diálogo.
¡Hecho! Los 3 valores inferiores en la columna B están resaltados:
Para obtener más información, consulte Formato condicional de Excel basado en fórmula.
La función Excel SMALL no funciona
Como acaba de ver en nuestros ejemplos, usar la función PEQUEÑA en Excel es bastante fácil y es poco probable que tenga dificultades con ella. Si su fórmula no funciona, lo más probable es que sea un #NUM. error, que puede ocurrir debido a las siguientes razones:
- La matriz está vacía o no contiene un solo valor numérico.
- El valor k es menor que cero (¡un error tipográfico tonto puede costarle horas de resolución de problemas!) o excede la cantidad de valores en la matriz.
Así es como se usa una fórmula PEQUEÑA en Excel para encontrar y resaltar los números inferiores en un conjunto de datos. Si conoce otros escenarios en los que la función es útil, puede compartirlos en los comentarios. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Cuaderno de ejercicios para descargar
(archivo .xlsx)