Excel

Formato condicional con fórmulas (10 ejemplos)

Conditional Formatting With Formulas

Inicio rápido | Ejemplos | Solución de problemas | Capacitación

El formato condicional es una forma fantástica de visualizar rápidamente datos en una hoja de cálculo. Con el formato condicional, puede hacer cosas como resaltar fechas en los próximos 30 días, marcar problemas de ingreso de datos, resaltar filas que contienen los principales clientes, mostrar duplicados y más.

Excel incluye una gran cantidad de 'ajustes preestablecidos' que facilitan la creación de nuevas reglas sin fórmulas. Sin embargo, también puede crear reglas con sus propias fórmulas personalizadas. Al utilizar su propia fórmula, asume la condición que activa una regla y puede aplicar exactamente la lógica que necesita. Las fórmulas le brindan la máxima potencia y flexibilidad.



Por ejemplo, usando el ajuste preestablecido 'Igual a', es fácil resaltar celdas iguales a 'manzana'.



Pero, ¿qué sucede si desea resaltar celdas iguales a 'manzana' o 'kiwi' o 'lima'? Claro, puede crear una regla para cada valor, pero eso es un gran problema. En su lugar, puede simplemente usar una regla basada en una fórmula con la Función OR :

Una regla para resaltar x, y o z



Aquí está el resultado de la regla aplicada al rango B4: F8 en esta hoja de cálculo:

Formateo condicional con la función OR

Aquí está la fórmula exacta utilizada:



|_+_|

Inicio rápido

Puede crear una regla de formato condicional basada en fórmulas en cuatro sencillos pasos:

1. Seleccione las celdas que desea formatear.

Seleccione las celdas para formatear

2. Cree una regla de formato condicional y seleccione la opción Fórmula

Seleccione la opción de fórmula

3. Ingrese una fórmula que devuelva VERDADERO o FALSO.

Ingrese la fórmula relativa a la celda activa

4. Configure las opciones de formato y guarde la regla.

Establecer opciones de formato

los Función ISODD solo devuelve VERDADERO para números impares, lo que activa la regla:

fórmula de índice en Excel con ejemplo

La función ISODD devuelve VERDADERO para números impares, lo que activa la regla

Video: Cómo aplicar formato condicional con una fórmula

También ofrecemos capacitación en video sobre este tema .

Lógica de fórmula

Las fórmulas que aplican formato condicional deben devolver VERDADERO o FALSO, o equivalentes numéricos. Aquí hay unos ejemplos:

cómo calcular el pago de su hipoteca en Excel
|_+_|

Todas las fórmulas anteriores devuelven VERDADERO o FALSO, por lo que funcionan perfectamente como desencadenante del formato condicional.

Cuando se aplica formato condicional a un rango de celdas, ingrese las referencias de celda con respecto a la primera fila y columna de la selección (es decir, la celda superior izquierda). El truco para comprender cómo funcionan las fórmulas de formato condicional es visualizar la misma fórmula que se aplica a cada celda de la selección , con las referencias de celda actualizadas como de costumbre. Imagine que ingresó la fórmula en la celda superior izquierda de la selección y luego copió la fórmula en toda la selección. Si tiene problemas con esto, consulte la sección sobre Fórmulas ficticias debajo.

Ejemplos de fórmulas

A continuación, se muestran ejemplos de fórmulas personalizadas que puede utilizar para aplicar formato condicional. Algunos de estos ejemplos se pueden crear utilizando los ajustes preestablecidos integrados de Excel para resaltar celdas, pero las fórmulas personalizadas pueden ir mucho más allá de los ajustes preestablecidos, como puede ver a continuación.

Ver también: Más de 30 fórmulas de formato condicional

Destacar pedidos de Texas

Para resaltar filas que representan pedidos de Texas (abreviado TX), use una fórmula que bloquee la referencia a la columna F:

|_+_|

Use una fórmula para resaltar filas donde estado =

Para más detalles, consulte este artículo: Resaltar filas con formato condicional .

Video: Cómo resaltar filas con formato condicional

Resaltar fechas en los próximos 30 días

Para resaltar las fechas que ocurren en los próximos 30 días, necesitamos una fórmula que (1) asegure que las fechas estén en el futuro y (2) se asegure de que las fechas sean 30 días o menos a partir de hoy. Una forma de hacer esto es usar el Y función junto con el Función AHORA como esto:

|_+_|

Con una fecha actual del 18 de agosto de 2016, el formato condicional destaca las fechas de la siguiente manera:

Formato condicional para resaltar fechas en los próximos 30 días

los Función AHORA devuelve la fecha y hora actuales. Para obtener detalles sobre cómo funciona esta fórmula, consulte este artículo: Resaltar fechas en los próximos N días .

Resaltar las diferencias de columna

Dadas dos columnas que contienen información similar, puede usar formato condicional para detectar diferencias sutiles. La fórmula utilizada para activar el formato a continuación es:

|_+_|

Formato condicional para comparar columnas

Ver también: una versión de esta fórmula que usa la función EXACTA para hacer una comparación sensible a mayúsculas y minúsculas .

Resalta los valores perdidos

Para resaltar valores en una lista que faltan en otra, puede usar una fórmula basada en la Función COUNTIF :

|_+_|

Resalte los valores perdidos con formato condicional

Esta fórmula simplemente verifica cada valor en Lista A contra valores en el rango nombrado 'lista' (D5: D10). Cuando el recuento es cero, la fórmula devuelve VERDADERO y activa la regla, que resalta los valores en Lista A que faltan en Lista B .

Video: Cómo encontrar valores perdidos con COUNTIF

Propiedades destacadas con más de 3 habitaciones por menos de $ 350k

Para buscar propiedades en esta lista que tengan al menos 3 dormitorios pero menos de $ 300,000, puede usar una fórmula basada en la función AND:

|_+_|

Los signos de dólar ($) bloquean la referencia a las columnas C y D, y el Y función se utiliza para asegurarse de que ambas condiciones sean VERDADERAS. En las filas donde la función Y devuelve VERDADERO, se aplica el formato condicional:

Formato condicional para resaltar listados de propiedades

Resaltar los valores superiores (ejemplo dinámico)

Aunque Excel tiene ajustes preestablecidos para 'valores superiores', este ejemplo muestra cómo hacer lo mismo con una fórmula y cómo las fórmulas pueden ser más flexibles. Al usar una fórmula, podemos hacer que la hoja de trabajo sea interactiva: cuando se actualiza el valor en F2, la regla responde instantáneamente y resalta los nuevos valores.

Formato condicional dinámico para valores superiores

La fórmula utilizada para esta regla es:

congelar las 3 filas superiores en Excel
|_+_|

Donde 'datos' es el rango con nombre B4: G11 y 'entrada' es el rango con nombre F2. Esta pagina tiene detalles y una explicación completa .

diagramas de Gantt

Lo crea o no, incluso puede usar fórmulas para crear diagramas de Gantt simples con formato condicional como este:

Usar formato condicional para crear un diagrama de Gantt

Esta hoja de trabajo usa dos reglas, una para las barras y otra para el sombreado de fin de semana:

|_+_|

Este artículo explica la fórmula de las barras. , y este artículo explica la fórmula para el sombreado de fin de semana .

Cuadro de búsqueda simple

Un truco interesante que puede hacer con el formato condicional es crear un cuadro de búsqueda simple. En este ejemplo, una regla resalta las celdas de la columna B que contienen texto escrito en la celda F2:

Cuadro de búsqueda de formato condicional

La fórmula utilizada es:

|_+_|

Para obtener más detalles y una explicación completa, consulte:

Solución de problemas

Si no puede hacer que sus reglas de formato condicional se activen correctamente, lo más probable es que haya un problema con su fórmula. Primero, asegúrese de comenzar la fórmula con un signo igual (=). Si olvida este paso, Excel convertirá silenciosamente toda su fórmula a texto, dejándola inútil. Para solucionarlo, simplemente elimine las comillas dobles que Excel agregó a cada lado y asegúrese de que la fórmula comience con igual (=).

Si su fórmula se ingresó correctamente, pero no activa la regla, es posible que deba profundizar un poco más. Normalmente, puede usar la tecla F9 para verificar los resultados en una fórmula o usar la función Evaluar para recorrer una fórmula. Desafortunadamente, no puede usar estas herramientas con fórmulas de formato condicional, pero puede usar una técnica llamada 'fórmulas ficticias'.

Fórmulas ficticias

Las fórmulas ficticias son una forma de probar sus fórmulas de formato condicional directamente en la hoja de trabajo, para que pueda ver lo que realmente están haciendo. Esto puede ser un gran ahorro de tiempo cuando tiene dificultades para que las referencias de celda funcionen correctamente.

En pocas palabras, ingresa la misma fórmula en un rango de celdas que coincide con la forma de sus datos. Esto le permite ver los valores devueltos por cada fórmula, y es una excelente manera de visualizar y comprender cómo funciona el formato condicional basado en fórmulas. Para una explicación detallada, ver este articulo .

Use fórmulas ficticias para verificar fórmulas de formato condicional

Video: Pruebe el formato condicional con fórmulas ficticias

Limitaciones

Existen algunas limitaciones que vienen con el formato condicional basado en fórmulas:

  1. No puede aplicar iconos, escalas de colores o barras de datos con una fórmula personalizada. Está limitado al formato de celda estándar, incluidos los formatos numéricos, la fuente, el color de relleno y las opciones de borde.
  2. No puede usar ciertas construcciones de fórmulas como uniones, intersecciones o constantes de matriz para los criterios de formato condicional.
  3. No puede hacer referencia a otros libros de trabajo en una fórmula de formato condicional.

A veces puede trabajar en torno a los números 2 y 3. Es posible que pueda mover la lógica de la fórmula a una celda de la hoja de trabajo y luego hacer referencia a esa celda en la fórmula. Si está intentando utilizar una constante de matriz, intente crear un rango con nombre en su lugar.

Más recursos sobre fórmulas de FQ

  • Más de 30 ejemplos de fórmulas de formato condicional
  • Entrenamiento en video con hojas de trabajo de práctica
Autor Dave Bruns


^