Formato condicional de Excel basado en otra celda
Cuando desee dar formato a una celda según el valor de una celda diferente, por ejemplo, para dar formato a una fila del informe según el valor de una sola columna, puede usar la función de formato condicional para crear una fórmula de formato. Esta publicación explora los detalles de cómo formatear una celda o rango según el valor de otra celda.
Objetivo
Aquí hay un ejemplo que nos permitirá poner esta característica en contexto. Digamos que tienes un listado de facturas y tu objetivo es identificar las facturas abiertas. Aquí hay una captura de pantalla de nuestra lista de facturas de muestra:
Como se trata de Excel, hay muchas formas de realizar cualquier tarea determinada. Una forma de identificar las facturas abiertas es simplemente ordenar la lista por la columna Estado para que las facturas abiertas aparezcan en un grupo. Otra forma es filtrar el listado para mostrar sólo las facturas abiertas. Estas técnicas son bastante sencillas, así que exploremos otro método. Resaltaremos las filas de transacciones con formato de celda… o, más precisamente, una fórmula de formato condicional.
Video
Narrativa de formato condicional
Usando formato condicional, sería bastante fácil resaltar solo la columna Estado. Sería sencillo porque las celdas que estamos formateando son las mismas celdas que tienen los valores a evaluar. Es decir, estaríamos formateando una celda según el valor dentro de esa celda. Para realizar esto, simplemente podríamos resaltar la columna Estado y usar el siguiente comando de la cinta:
- Inicio Formato condicional Reglas de celda Igual a
En el cuadro de diálogo Igual a, podemos ingresar la palabra “Abrir”, elegir el formato deseado y hacer clic en Aceptar. Luego, Excel aplicaría el formato a las celdas dentro de la columna Estado que son iguales a Abierto. Si bien esta técnica es sencilla, no cumple con nuestro objetivo, que es resaltar toda la fila de la transacción, no solo la columna Estado.
Para resaltar toda la fila de la transacción es necesario que formatemos una celda según el valor de otra celda. Es decir, queremos formatear las columnas TID, Fecha, Estado, CustID y Monto según el valor de la columna Estado. Considerando una sola celda por un momento, queremos formatear B7 según el valor en D7. Esto significa que queremos formatear una celda, B7, según el valor de una celda diferente, D7. Expandiendo esto a toda la fila, queremos formatear B7:F7 según el valor en D7.
Excel facilita a los usuarios formatear una celda según el valor de esa celda, y las reglas de formato condicional integradas utilizan esta lógica. Cuando queramos formatear una celda según el valor de una celda diferente, necesitaremos usar una fórmula para definir la regla de formato condicional. Afortunadamente, no es muy difícil configurar dicha fórmula de formato.
Primero resaltemos toda la lista de transacciones (B7:F36) y luego abramos el cuadro de diálogo de formato condicional usando el siguiente ícono de cinta:
- Inicio Formato condicional Nueva regla
El cuadro de diálogo Nueva regla de formato tiene muchas opciones, lo que le permite, por ejemplo, formatear una celda según el valor, si contiene un valor, los valores clasificados en la parte superior o inferior, valores que están por encima o por debajo del promedio y únicos o duplicados. valores. Al final de la lista encontramos la opción que necesitamos. Queremos usar una fórmula para determinar qué celdas formatear.
La fórmula de formato debe configurarse para que devuelva un valor verdadero o falso. Si la fórmula devuelve verdadero, entonces se aplica el formato deseado. Si la fórmula devuelve falso, no se aplica el formato.
Lo clave que hay que entender al escribir la fórmula es que la celda activa es el punto de referencia de la fórmula. Dado que este concepto es absolutamente crítico, no quiero simplemente omitirlo, quiero desglosarlo por un momento.
Desea escribir la fórmula de formato como si la estuviera escribiendo en la celda activa y utilizar las referencias de celda y los estilos de referencia apropiados, como absoluto, relativo y mixto. Si puede visualizar la idea de que está escribiendo la fórmula en la celda activa y la fórmula se completará en el rango seleccionado, entonces escribir la fórmula será más fácil. La fórmula que escriba no se usará para calcular el valor de la celda, sino que se usará solo para formatear.
Supongamos que cuando seleccionamos todo el rango de transacciones, B7:F36, la celda activa es B7. Cuando selecciona un rango, todavía hay una sola celda activa. Consulte la captura de pantalla a continuación para ver que el rango está seleccionado, pero B7 sigue siendo la celda activa:
Con esta idea en mente, ahora es fácil escribir la fórmula de formato condicional. Necesitamos escribir la siguiente fórmula en el cuadro de diálogo Nueva regla de formato, como si la estuviéramos escribiendo en la celda B7:
=$D7="Abrir"
Esta sencilla fórmula de comparación devuelve verdadero cuando D7 es igual a Abrir y, por tanto, se aplicará el formato deseado. Echemos un vistazo rápido a la referencia de celda por un momento. Usamos una referencia de celda mixta, $D7, donde la parte de la columna (D) es absoluta y la parte de la fila (7) es relativa. Este es el por qué. Recuerde que queremos fingir que estamos escribiendo la fórmula en la celda activa, en este caso, B7. Si solo estuviéramos formateando B7, entonces, podríamos haber usado una referencia relativa D7, o una referencia absoluta $D$7, o una referencia mixta. El estilo de referencia no importaría porque la fórmula se usó solo en una sola celda y no se completó en ninguna parte.
Sin embargo, en el momento en que completamos una fórmula hacia abajo o hacia la derecha, debemos tener cuidado de utilizar los estilos de referencia de celda adecuados. En nuestro caso, como la fórmula de formato se completa correctamente, no queremos que cambie la referencia de la columna D. Es decir, para todas las celdas, queremos hacer referencia a la columna Estado, columna D. Para evitar que Excel cambie la referencia de la columna cuando la fórmula se presenta a la derecha, la bloqueamos con el signo de dólar, lo que da como resultado $D. A medida que la fórmula de formato se completa en todo el rango seleccionado, queremos asegurarnos de que la referencia de la fila se actualice en consecuencia. Al formatear B7, queremos ver la columna Estado dentro de la misma fila, o D7. Sin embargo, cuando la fórmula se completa hasta la fila 8, queremos formatear B8 según el valor en D8. Como queremos que Excel actualice la referencia de la fila, la dejaremos relativa y no usaremos el signo de dólar para bloquearla.
Aquí hay una captura de pantalla del cuadro de diálogo de formato con la fórmula:
Una vez ingresada la fórmula, simplemente use el botón Formato para especificar el formato deseado.
Una vez que se aplica el formato condicional al rango, la hoja de trabajo resultante se muestra a continuación:
Bam… ¡lo tenemos!
GRATIS: Desafío de velocidad de Excel
Si te ha gustado esta publicación, consulta nuestro desafío gratuito de velocidad de Excel.
Mire un vídeo corto de Excel al día durante 5 días. El tiempo total del video es de solo 45 minutos. Aprenda las habilidades de Excel que pueden ayudarle a ahorrar una hora a la semana.
Información del desafío
Otras Consideraciones
En la fórmula anterior, codificamos el valor, “Abrir”, sin embargo, podríamos haber colocado fácilmente este valor en una celda y luego hacer referencia a la celda por nombre o notación de estilo A1. Además, además de simplemente determinar si el valor de la celda es igual a un valor, se admiten otros operadores de comparación, por ejemplo, mayor que () y menor que (). Podríamos haber resaltado todas las filas donde el valor es mayor que $5000 usando la siguiente fórmula de comparación:
=$F75000
Dado que el formato se aplica en función de una fórmula, podemos ser muy creativos y utilizar funciones de hoja de trabajo. Podríamos formatear filas alternas de la hoja de trabajo usando las funciones MOD y FILA. O bien, podríamos resaltar transacciones antiguas calculando la diferencia entre la fecha de la transacción y la fecha de hoy con la función HOY. De hecho, esta fórmula de formato ofrece muchas opciones divertidas.
Múltiples condiciones
También podemos usar las funciones lógicas Y y O en caso de que queramos considerar múltiples condiciones. Por ejemplo, formatear aquellas filas donde el estado está abierto y el monto es mayor que 5000 usando la siguiente fórmula:
=Y($D7="Abrir",$F75000)
Dado que la función AND devuelve verdadero cuando todos sus argumentos son verdaderos, el formato se aplica solo cuando el estado es abierto y la cantidad es mayor que 5000.
Si quisiéramos formatear la fila si se cumpliera alguna de las condiciones, entonces querríamos usar la función O en lugar de la función Y, ya que devuelve verdadero si algún argumento es verdadero.
Conclusión
Tener la capacidad de formatear una celda según el valor de otra celda es bastante útil. La clave es imaginar que estás escribiendo una fórmula en la celda activa. La fórmula debe utilizar los estilos de referencia de celda apropiados (absoluto, relativo, mixto) para que, a medida que la fórmula de formato se complete en todo el rango seleccionado, se consideren las celdas adecuadas. El archivo de muestra a continuación incluye las reglas de formato condicional, así que no dude en consultarlo.
Espero que esto ayude y recuerde, ¡las reglas de Excel!
Archivo de muestra
Aquí está el archivo de muestra en caso de que desee abrirlo y hacer referencia a él.
FormatoFórmula
Deja una respuesta