Si desea enfatizar los valores N superiores o inferiores en un conjunto de datos, la mejor manera es resaltarlos en diferentes colores. Este artículo le enseñará cómo hacer esto con los ajustes preestablecidos de Excel y configurará su propia regla de formato condicional basada en la fórmula.
Resaltar celdas con formato condicional de Excel puede parecer una tarea trivial. Pero no si piensas en cuántas formas puedes hacerlo. ¿Quiere sombrear los números más altos o más bajos en un rango, columna o fila? ¿O tal vez desea mostrar los primeros 3 valores en cada fila? Y sería genial si pudiera controlar la cantidad de celdas resaltadas directamente en la hoja sin tener que ir al administrador de Reglas de formato condicional cada vez. ¡Este tutorial muestra cómo hacer todo eso y un poco más!
Resalte los valores N superiores o inferiores en el rango
En Microsoft Excel, hay varias formas de resaltar los valores más altos y más bajos con formato condicional. A continuación, proporcionamos una descripción general de 3 métodos diferentes para que pueda elegir el que mejor se adapte a sus necesidades.
Resalte los valores superior e inferior con una regla integrada
La forma más rápida de resaltar los valores 3, 5, 10 superiores (o n inferiores) en Excel es usar una regla de formato condicional incorporada. Así es cómo:
- Seleccione el rango en el que desea resaltar los números.
- En El hogar pestaña, en Estilos grupo, haga clic Formato condicional.
- En el menú desplegable, elija Reglas arriba/abajoluego haga clic en cualquiera Los 10 mejores artículos… o Los últimos 10 artículos…
- En el cuadro de diálogo que se abre, especifique el número de elementos para resaltar y elija una de las opciones de formato predefinidas. Para aplicar un formato que no está en la lista, haga clic en Formato personalizado… . Los cambios que realice aquí se reflejan inmediatamente en su conjunto de datos. Entonces, si está satisfecho con los resultados, haga clic en Bien.
Para este ejemplo, elegimos resaltar los 3 primeros valores con defecto Relleno rojo claro y obtener este resultado:
Más opciones de formato para mostrar los valores más altos y más bajos
Si desea más opciones que las proporcionadas en Arriba abajo preestablecidos, puede crear una nueva regla desde cero:
- Seleccione un rango de valores numéricos.
- En El hogar pestaña, haga clic Formato condicional > Nueva regla.
- En el La nueva regla de formato cuadro de diálogo, seleccione Dar formato solo a los valores mejor o peor clasificados.
- En la lista desplegable, seleccione Arriba o Fondoe ingrese cuántos valores resaltar en el cuadro al lado.
- Seguir adelante Formato botón y elija el formato que desee FUENTE, Frontera y Completo.
- Hacer clic Bien.
Por ejemplo, así es como puede resaltar los primeros 5 valores con un color de fondo verde.
Resalta los valores superiores o inferiores con una fórmula
Las reglas integradas descritas anteriormente son prácticas y fáciles de aplicar. Sin embargo, tienen un inconveniente importante: cada vez que desee mostrar una cantidad diferente de valores, deberá cambiar la cantidad en el Administrador de reglas de formato condicional.
Para hacer que la regla sea más robusta, puede basarla en una fórmula. En nuestro caso, utilizaremos:
GRAN función para sombrear los números superiores:
=celda_superior_izquierda>=GRANDE(rango, norte)
Función MIC para sombrear los números inferiores:
=celda_superior_izquierda<=PEQUEÑO(rango, norte)
Para colorear los valores más altos o más bajos utilizando una regla basada en fórmulas, siga estos pasos:
- Ingrese la cantidad de valores para resaltar en una celda de entrada predefinida. Para este ejemplo, ingresamos el número de valores superiores en F2 y el número de valores inferiores en F3.
- Seleccione el rango de números (A2:C8).
- En El hogar pestaña, haga clic Formato condicional > Nueva regla.
- En el La nueva regla de formato cuadro de diálogo, seleccione Use una fórmula para determinar qué celdas formatear.
- En el Valores de formato donde esta fórmula es verdadera cuadro, introduzca una de estas fórmulas:
Para resaltar los primeros n valores:=A2>=LARGE($A$2:$C$8, $F$2)Para resaltar los valores n inferiores:
=A2<=SMALL($A$2:$C$8, $F$3)Donde $A$2:$C$8 es el rango aplicado, A2 es la celda más a la izquierda del rango; F2 y F3 son los valores de n.
Tenga en cuenta que bloqueamos el rango de origen y las celdas de entrada con referencias absolutas y usamos una referencia relativa para la celda superior izquierda.
- Seguir adelante Formato y elija el formato deseado.
- Hacer clic Bien dos veces para cerrar ambas ventanas.
Para obtener instrucciones más detalladas, consulte Crear una regla de formato condicional con una fórmula.
¡Terminado! Los 3 valores superiores e inferiores 3 se resaltan en diferentes colores.
Más tarde, si desea resaltar, digamos, los primeros 5 valores, simplemente ingrese 5 en F2 y Excel aplicará automáticamente el cambio.
Cómo funciona esta fórmula:
La función GRANDE devuelve el valor más grande en el rango especificado. En nuestro caso, encuentre 3calle el valor más grande en A2:C8, que es 92. La fórmula compara cada número en el rango seleccionado con 3calle el valor más alto. Para cualquier celda que sea mayor o igual a 92, devuelve VERDADERO y se aplica la regla de formato condicional.
Cómo sombrear filas que contienen valores N superiores o inferiores
Al analizar datos estructurados, a menudo puede ser útil identificar filas enteras que contienen valores n superiores o inferiores en la columna clave. Para esto, puede configurar una regla de entrenamiento condicional basada en fórmulas, como se explicó en el ejemplo anterior, pero las fórmulas son ligeramente diferentes.
Para mostrar las primeras n filas:
=$B2>=LARGE($B$2:$B$15, $E$2)
Para mostrar las n filas inferiores:
=$B2<=SMALL($B$2:$B$15, $E$3)
Dónde:
- $B2 es la celda superior de la columna de números.
- $B$2:$B$15 son los números a clasificar.
- $E$2 es el número de filas superiores para resaltar.
- $E$3 es el número de filas inferiores para resaltar.
Las reglas se aplican a toda la tabla excepto a la fila del encabezado (A2:B15).
Como en el ejemplo anterior, estas reglas son flexibles. Las fórmulas se volverán a calcular automáticamente tan pronto como cambie los números en las celdas de entrada (E2 y E3), y Excel reflejará inmediatamente esos cambios y resaltará el número correspondiente de filas.
Cómo resaltar los valores N superiores en cada fila
Si su conjunto de datos contiene varias columnas numéricas, es posible que desee mostrar los valores más altos o más bajos en cada fila. Para ello, vuelva a crear una regla de formato condicional de Excel utilizando una fórmula.
Por ejemplo, para resaltar los primeros 3 números de cada fila en la siguiente tabla, la fórmula es:
=B2>=LARGE($B2:$G2, 3)
Los pasos para crear la regla se describen en este ejemplo, por lo que solo mostramos el resultado aquí. La regla se aplica a todas las celdas numéricas (B2:G2):
Para mostrar las tres filas inferiores, la fórmula sería:
=B2<=SMALL($B2:$G2, 3)
Cómo funciona esta fórmula:
Como habrás notado, esta fórmula es muy similar a las utilizadas en los ejemplos anteriores, excepto por las referencias de celda y rango. ¡Y este pequeño cambio hace una gran diferencia!
En el formato condicional de Excel, las referencias son relativas a la celda superior izquierda del rango al que se aplica la regla. Entonces, podemos pretender que escribimos una fórmula para la celda de la izquierda y Excel la «copia» a través de todas las demás celdas en el rango seleccionado.
En este ejemplo, la regla se aplica a todas las celdas numéricas (B2:G10), pero la fórmula está escrita para la fila 2:
=B2>=LARGE($B2:$G2, 3)
La función GRANDE encuentra 3calle el valor más alto en B2:G2, que es 257. La fórmula verifica si B2 es mayor o igual a 275 y, si es VERDADERO, aplica formato condicional a esa celda. Dado que B2 es una referencia relativa, Excel básicamente «copia» la fórmula a C2, D2, etc. Debido a que las coordenadas de la columna están bloqueadas con el signo $ ($B2:$G2), el rango no cambia al comparar otras celdas en la misma fila.
Por ejemplo, para C2, Excel evaluará esta fórmula:
=C2>=LARGE($B2:$G2, 3)
Para la fila 3, la fórmula cambia automáticamente como se muestra a continuación porque todas las coordenadas de fila son relativas:
=B3>=LARGE($B3:$G3, 3)
Etcétera.
El siguiente tutorial puede ayudarlo a comprender mejor la mecánica interna: referencias de celdas relativas y absolutas en formato condicional.
Así es como se resaltan los valores superior e inferior en Excel usando formato condicional. ¡Gracias por leer y espero verte la próxima semana en nuestro blog!
Libro de ejercicios para descargar.
Resalta los valores superiores o inferiores en Excel (archivo .xlsx)
