Libro de Excel con 8 hojas que puede descargar en el enlace de parte inferior, en las que se aplican formato condicional con diferentes reglas a través de fórmulas, las cuales nos pueden servir de ayuda o referencia para hacer otras plantillas y adaptarlas a las necesidades de cada uno.
En formato condicional es donde se encuentran las diferentes reglas aplicadas en cada hoja, las podemos ver seleccionando las celdas que contienen los datos y en la pestaña Inicio seleccionamos formato condicional y hacemos clic en Administrar Reglas, donde podemos modificar el formato, el rango de celdas o añadir/modificar las fórmulas.
En cada hoja se aplica la formula on los criterios establecidos en la que determina las celdas que se aplicará el formato, según se indican a continuación:
Hoja 1 : Se aplica formato a las celdas si el contenido es igual a la celda H2 (para dejar está celda como referencia absoluta en las fórmulas se pone el signo ($) antes de la letra y el número ($H$2).
En Formato condicional se aplicó la siguiente regla:
Fórmula: =Y($H$2<>»»;A2=$H$2)
Rango: =$A$2:$G$150000
Formato de las celdas: relleno color amarillo letra color rojo
También podríamos usar la siguiente fórmula =Y($H$2<>»»;CONTAR.SI(A2; $H$2))
imagen hoja 1
Hoja 2: Se aplica formato a la fila (desde la columna A hasta la G), si el contenido de alguna celda es igual a la celda ($H$2).
En formato condicional se aplicó la siguiente regla:
Fórmula: =Y($H$2<>»»;CONTAR.SI($A2:$G2; $H$2))
Rango: =$A$2:$G$150000
Formato de las celdas: relleno color amarillo letra color rojo
imagen hoja 2
Hoja 3: Se aplica formato a las filas si alguna celda en la fila es igual o contiene el dato de la celda ($H$2).
En formato condicional se aplicó la siguiente regla:
Fórmula:
=Y($H$2<>»»;NO(ESERROR(HALLAR($H$2;$A2&$B2&$C2&$D2&$E2&$F2&$G2))))
Rango: =$A$2:$G$150000
Formato de las celdas: relleno color amarillo letra color rojo
Otras fórmulas que podríamos emplear:
=Y(LARGO($H$2)>0;NO(ESERROR(HALLAR($H$2;$A2&$B2&$C2&$D2&$E2&$F2&$G2))))
=Y($H$2<>»»;SUMA(SI(ESERROR(HALLAR($H$2;$A2:$G2));0;1)))
=SI(ESNUMERO($H$2);ENCONTRAR($H$2;$A2&$B2&$C2&$D2&$E2&$F2&$G2);Y($H$2<>»»»»;CONTAR.SI($A2:$G2; «*»&$H$2&»*»)))
imagen hoja 3
Hoja 4: Se aplica formato sólo a celdas que sean iguales o que contengan el dato de la celda ($H$2)
En formato condicional se aplicó la siguiente regla:
Fórmula: =Y($H$2<>»»;Y(ESNUMERO(HALLAR($H$2;A2)))
Rango: =$A$2:$G$150000
Otra fórmula: =Y($H$2<>»»;ENCONTRAR(MINUSC($H$2); MINUSC(A2)))
imagen hoja 4
Hoja 5: En una lista de números se aplica formato a la celda de cada fila que tiene el valor mínimo (desde la fila 2 hasta la 15) y en otra lista al valor máximo (desde la fila 19 hasta la 32).
Para el valor mínimo se aplicó la siguiente regla:
Fórmula: =A2=MIN($A2:$G2)
Rango: =$A$2:$G$15
Para el valor máximo se aplicó la siguiente regla:
Formula: =A19=MAX($A19:$G19)
Rango: =$A$19:$G$32
imagen hoja 5
Hoja 6: Se aplica formato al valor mínimo y máximo de cada columna, (desde la columna A hasta la columna H y desde la fila 2 hasta la 15).
Para el valor mínimo y máximo se aplicó la siguiente regla:
Fórmula valor mínimo: =A2=MIN(A$2:A$15)
Fórmula valor máximo: A2=MAX(A$2:A$15)
El rango para ambas es el mismo: =$A$2:$G$15
imagen hoja 6
Hoja 7: Se aplica formato a valor máximo y mínimo de la lista de números (rango desde A2 hasta G15).
En formato condicional se aplicó la siguiente regla:
Fórmula para el valor máximo: =A2=MAX($A$2:$G$15)
Fórmula para el valor mínimo: =A2=MIN($A$2:$G$15)
Rango aplicado =$A$2:$G$15
imagen hoja 7
Hoja 8: Se aplica formato condicional a las celdas de cada columna que cumplen los criterios establecido (desde la columna A hasta la H y desde la fila 5 hasta 40) y se aplicaron las siguientes reglas:
Columna A: se aplica formato a las celdas si el año es igual a la celda A2 y el mes igual a la celda B2.
Fórmula aplicada: =Y(AÑO($A5)=$A$2;MES($A5)=$B$2)
Rango: =$A$5:$A$40
Columna B: se aplica formato a las fechas que coinciden con los fines de semana (sábado y domingo).
Fórmula aplicada: =Y($B5<>»»;DIASEM($B5;2)>5)
Otra fórmula: ==Y(ELEGIR(DIASEM($B5;2);1;2;3;4;5;6;7)>5)
Rango: =$B$5:$B$40
Si deseamos aplicar formato para los días de la semana, por ejemplo para lunes cambiamos en la fórmula el signo y el número ( >5) por =1), para el martes =2, miércoles =3, jueves =4; viernes =5; sábado = 6 y domingo = 7
También podríamos utilizar la siguiente fórmula poniendo el nombre del día correctamente (Sábado y Miércoles con acento), por ejemplo para lunes “=Lunes” como se indica a continuación:
=Y(ELEGIR(DIASEM($B5;2);»Lunes»;»Martes»;»Miércoles»;»Jueves»;»Viernes»;»Sábado»;»Domingo»)=»Lunes»)
Columna C: Se aplica formato a las celdas cuando la fecha coincide con el día 30
Fórmula aplicada: =Y(DIA($C5)=30)
Rango: =$C$5:$C$40
Columna D: Se aplica formato a las fechas que son el día 1 y 30
Fórmula aplicada: =Y(O(DIA($D5)=1;DIA($D5)=30))
Rango: =$D$5:$D$40
Columna E: Se aplica formato a las fechas que el mes tiene 31 días
Fórmula aplicada: =Y(DIA(FIN.MES($E5;0))=31)
Rango: =$E$5:$E$40
Columna F: Se aplica formato a las fechas que son menores o iguales a la fecha actual.
Fórmula aplicada: =$F5<=HOY() Rango: =$F$5:$F$40 Columna G: Se aplica formato a las fechas que son mayores o iguales a 18 años Formula aplicada: =SIFECHA($G5;HOY();»Y»)>=18
Rango: =$G$5:$G$40
Columna H: Se aplica formato a las fechas si el mes contiene 5 sábados y 5 domingos.
Fórmula aplicada: =Y(Y(TRUNCAR((FIN.MES($H5;0)-FECHA(AÑO($H5);MES($H5);1)-DIASEM(FIN.MES($H5;0)+1)+8)/7)=5);TRUNCAR((FIN.MES($H5;0)-FECHA(AÑO($H5);MES($H5);1)-DIASEM(FIN.MES($H5;0)+0)+8)/7)=5)
Rango: =$H$5:$H$40
Para aplicar a los meses que tengan 5 domingos, a las mismas celdas que la anterior, aplicamos la siguiente fórmula:
=Y(TRUNCAR((FIN.MES($H5;0)-FECHA(AÑO($H5);MES($H5);1)-DIASEM(FIN.MES($H5;0)+0)+8)/7)=5)
imagen hoja 8
A Continuación, haciendo clic en el siguiente enlace puede Descargar el libro de Excel
Clic para descargar libro de Excel : Plantilla diferentes reglas en formato condicional