Es muy común en las empresas tener que hacer planes semanales. En este artículo te mostraré cómo transformar los días libres establecidos por los empleados en color en tu calendario.

No se utilizó programación para diseñar este libro de trabajo, pero su construcción no es sencilla y lleva mucho tiempo. ????

Paso 1: Calcula la fecha del próximo lunes

Lo primero que debemos hacer para construir nuestro horario es determinar el valor del próximo lunes. Para comenzar ingresaremos la fecha actual en la celda A2 usando el Función HOY.

Luego, en base a esta información y la función DAYSEM, recuperaremos el próximo lunes usando la siguiente fórmula

=A2+8-DÍASEM(A2;2)

Paso 2: Complete los otros días de la semana

Una vez calculado el lunes, es fácil agregar las siguientes fechas. Simplemente agregue 1 a la fecha anterior.

Paso 3: cambia el formato de fecha

• Dejar la fecha en formato día/mes/año no es muy legible.
• La forma más sencilla es cambiar el formato de visualización de la fecha utilizando un código personalizado para agregar el dia en letras

=ddddmmmm

Paso 4: número de semana

• El número de semana se deduce del primer lunes usando la función NO.SEMANA.ISO.
• Tenga cuidado de no confundir las funciones SEMANA NO. et NO.SEMANA.ISO

=NO.SEMANA.ISO(B4)

Paso 5: crear una clave única

Ahora debemos buscar entre nuestra hoja semanal y la tabla que contiene los días configurados. El calendario se construye en base a fechas (en línea) y nombres de empleados (en columna).

Para poder encontrar en la tabla de días de ausencia de los empleados, el día de ausencia de un empleado en una fecha específica no nos queda otra alternativa que crear una columna adicional que concatenar el nombre del empleado y la fecha de ausencia.

El resultado no es “estético”. Pero eso no importa para nuestra prueba. Lo importante es poder determinar de forma unívoca, en una celda, el día en que un empleado está ausente.

Paso 6: referencia mixta

Ahora necesitamos usar la función BUSCARV que podrá "mirar" si la asociación Empleado + Fecha existe en la tabla de días de ausencia. Pero antes escribiremos una referencia mixta que recuperará el nombre de los empleados asociados con las fechas.

=$A5&B$4

Como puede ver, en cada intersección recuperamos la intersección de la fecha y el nombre del empleado.

Paso 7: Crear la función BUSCARV

El paso anterior puede parecerte trivial pero esta es la clave de toda esta carpeta.

Acabamos de crear una clave única que combina los nombres de los empleados y las fechas. Por lo tanto, es fácil crear el Función BUSCARV que obtendrá información sobre las ausencias de los empleados de la hoja.

=RECHERCHEV($A5&B$4;Tableau1[Code];1;0)

Cuando la función BUSCARV devuelve #N/A (No aplica), significa que la búsqueda no fue exitosa. Pero esto no es necesariamente un error como se vio en el artículo sobre la comparación entre 2 columnas.

Ahora estamos muy cerca del final de la construcción de nuestro cronograma.

Paso 8: muestra VERDADERO para los días libres en el calendario

En el paso anterior pudimos crear una fórmula que detecta si un empleado ha tomado un día de ausencia (La función BUSCARV devuelve un valor) o no (La función BUSCARV devuelve #N/A).

Ahora necesitamos transformar este resultado en una prueba VERDADERO/FALSO (esencial para el formato condicional). Para convertir el resultado de la función BUSCARV en un resultado VERDADERO/FALSO simplemente debes incluir la escritura del Función BUSCARV en la función ESTNA.

=ESTNA(RECHERCHEV($A5&B$4;Tableau1[Code];1;0))

Únicamente, para la realización de nuestro formato condicional, es el resultado opuesto el que queremos. Es decir que la fórmula debe devolver VERDADERO cuando la búsqueda fue exitosa y FALSO cuando la búsqueda no fue exitosa.

Es por esto que debemos incluir la prueba anterior en el Sin función lo que revertirá la prueba (inteligente 😉👍). Los días festivos muestran VERDADERO en el calendario.

=NON(ESTNA(RECHERCHEV($A5&B$4;Tableau1[Code];1;0)))

Paso 9: formato condicional

Acabamos de crear una fórmula que devuelve VERDADERO o FALSO correctamente cuando un empleado se ha tomado un día libre para mostrarlo en el calendario. Ahora integraremos esta fórmula en una formato condicional cambiar el color de las celdas cuando el empleado está ausente.

1. Copia la fórmula anterior.
2. Abre el menu Inicio>Formato condicional>Nueva regla
3. En el cuadro de diálogo, seleccione Utilice una fórmula para determinar a qué celdas se aplicará el formato
4. Allí, copias tu fórmula en el cuadro de texto.
5. Haga clic en el botón Formato
6. Luego en la pestaña relleno, eliges un color que definirá tu fondo cuando la regla sea VERDADERA.

Y....... No funciona 😮😡

Paso 10: hacer visibles las referencias de la tabla

De hecho, cuando pones la referencia de una Tabla en una regla, el formato condicional no puede interpretarla. A menos que incluya la referencia de la tabla en una función INDIRECTA.

INDIRECTO("Matriz[Código]")

Ahora solo queda aplicar esta regla a todas tus celdas.

1. Abre el menu Inicio>Formato condicional>Administrar reglas
2. Asegúrate de seleccionar la opción Esta hoja de cálculo en el menú desplegable Mostrar reglas de formato para
3. Luego en la zona Se aplica a Seleccionar área de celda B5:H8

El resultado es este. Cada día libre es ahora en rojo en el calendario.

Cuando la prueba es VERDADERA, la celda se colorea de rojo.

Ahora puedes eliminar las fórmulas que nos permitieron crear la regla condicional; ya no son útiles.

Paso 11: Fronteras

Mejorar la presentación siempre es recomendable para todos los que trabajarán con su documento. Si pierdes demasiado tiempo dibujando tus fronteras, podrás ver el vídeo de este artículo para mejorar tu técnica.