En caso de que desee enfocarse en 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 cómo 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 3 valores principales en cada fila? Y sería muy bueno 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 esto y un poco más!
Resalte los valores N superiores o inferiores en el rango
En Microsoft Excel, hay algunas 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 la regla integrada
La forma más rápida de resaltar los valores 3, 5, 10 principales (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 la pestaña Inicio, en el grupo Estilos, haga clic en Formato condicional.
- En el menú desplegable, seleccione Reglas superior/inferiory luego haga clic en Los 10 artículos principales… o Los 10 últimos 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 en su conjunto de datos de inmediato. Entonces, si está satisfecho con los resultados, haga clic en Aceptar.
Para este ejemplo, elegimos resaltar 3 valores principales con el relleno rojo claro predeterminado y obtenga 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 los ajustes preestablecidos Superior/Inferior, puede crear una nueva regla desde cero:
- Seleccione un rango con valores numéricos.
- En la pestaña Inicio, haga clic en Formato condicional > Nueva regla.
- En el cuadro de diálogo Nueva regla de formato, elija Dar formato solo a los valores mejor o peor clasificados.
- En la lista desplegable, seleccione Parte superior o Abajoy escriba cuántos valores desea resaltar en el cuadro junto a él.
- Haga clic en el Formato y elija el formato que desee para Fuente, Borde y Relleno.
- Haga clic en Aceptar.
Por ejemplo, así es como puede resaltar los 5 valores principales con un color de fondo verde.
Resalte 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 un número diferente de valores, deberá cambiar el número en el Administrador de reglas de formato condicional.
Para que la regla sea más resistente, puede basarla en una fórmula. En nuestro caso, usaremos:
Función GRANDE para sombrear números superiores:
=celda_superior_izquierda>=GRANDE(rango, n)
Función PEQUEÑA para sombrear los números inferiores:
= celda_superior_izquierda<=PEQUEÑO(rango, n)
Para colorear los valores más altos o más bajos usando una regla basada en fórmulas, siga estos pasos:
- Escriba el número 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 la pestaña Inicio, haga clic en Formato condicional > Nueva regla.
- En el cuadro de diálogo Nueva regla de formato, seleccione Use una fórmula para determinar qué celdas formatear.
- En el cuadro Formatear valores donde esta fórmula es verdadera, ingrese una de estas fórmulas:
Para resaltar los n valores principales:=A2>=LARGE($A$2:$C$8, $F$2)Para resaltar los valores inferiores de n:
=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.
- Haga clic en el Formato y elija el formato deseado.
- Haga clic en Aceptar dos veces para cerrar ambas ventanas.
Para obtener instrucciones más detalladas, consulte Crear una regla de formato condicional con fórmula.
¡Hecho! Los 3 valores superiores e inferiores 3 se resaltan en diferentes colores.
Más tarde, si desea resaltar, por ejemplo, los 5 valores principales, simplemente escriba 5 en F2 y Excel aplicará automáticamente el cambio.
Cómo funciona esta fórmula:
La función GRANDE devuelve el valor n-ésimo más grande en el rango especificado. En nuestro caso, encuentra el tercer valor más alto en A2:C8, que es 92. La fórmula compara cada número en el rango seleccionado con el tercer 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 completas que contengan valores n superiores o inferiores en la columna clave. Para esto, puede configurar una regla de formación condicional basada en fórmulas como se explica en el ejemplo anterior, pero las fórmulas son un poco diferentes.
Para mostrar las n primeras 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 numérica.
- $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
En caso de que su conjunto de datos contenga varias columnas numéricas, es posible que desee mostrar los valores más altos o más bajos en cada fila. Para hacerlo, vuelva a crear una regla de formulación condicional de Excel utilizando una fórmula.
Por ejemplo, para resaltar los 3 primeros números en cada fila de 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 aquí solo mostramos el resultado. La regla se aplica a todas las celdas numéricas (B2:G2):
Para mostrar las 3 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 las referencias de celda y rango. ¡Y ese 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 estamos escribiendo una fórmula para la celda más a la izquierda, y Excel la está «copiando» 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 se escribe para la fila 2:
=B2>=LARGE($B2:$G2, 3)
La función GRANDE encuentra el tercer valor más grande en B2:G2, que es 257. La fórmula verifica si B2 es mayor o igual a 275 y, si es VERDADERO, aplica el formato condicional a esa celda. Como B2 es una referencia relativa, Excel virtualmente «copia» la fórmula en C2, D2, etc. Como 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)
Y así.
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 mediante el formato condicional. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Cuaderno de ejercicios para descargar
(archivo .xlsx)