Gracias a las nuevas funciones de Excel 365, es muy fácil crear una hoja de horas dinámica.
- Poner los datos en una tabla.
- Convertir datos a VERDADERO o FALSO
- Extrae nombres y fecha. Gracias a la función ÚNICA, es muy fácil recuperar todos los nombres y fechas.
- Organizar datos. Para simplificar la presentación de datos, podemos usar ORGA.COLS o SEARCHX.
- Aplicar casillas de verificación
Paso 1: poner los datos en una tabla
Nuestra hoja de asistencia tiene sólo 3 columnas:
- Nombre de los empleados
- La fecha
- Indicación de presencia (1) o no (0)
Pon tus datos en una tabla (Insertar > Tabla) haciaventaja de hacer que los rangos de datos sean dinámicos. Lo cual será de gran utilidad en nuestro caso de estudio. Además, le dimos un nombre específico a nuestra Mesa para identificarla mejor después.
Paso 2: convierta 0 y 1 en FALSO y VERDADERO
Para poder utilizar la herramienta casillas de verificación, debes transformar los resultados 0 y 1 en FALSO y VERDADERO. Esto se logra simplemente escribiendo la siguiente prueba
Paso 3: extraiga nombres y fechas de forma única
Para este paso usaremos el Función ÚNICA extraer nombres y fechas solo una vez
Usando referencias de tabla, sin importar la cantidad de filas, nuestra función ÚNICA usará todas las filas en la columna Empleado. Ahora, para las fechas, la función es ligeramente diferente porque queremos escribir el resultado horizontalmente. Para ello utilizaremos la función TRANSPONER.
=TRANSPONER(ÚNICO(tbl_Empleado[Fecha]))
Paso 4: Complete la hoja de asistencia
Se pueden utilizar dos funciones.
- ORGA.COLS
- INVESTIGAR
Si los datos están perfectamente ordenados, por fecha (clave 1) y por nombre (clave 2) podemos aplicar la función ORGA.COLS. Podemos indicar que vamos a reordenar la columna Verdadero o Falso dividiendo cada 7 filas en columnas
=ORGA.COLS(tbl_Empleado[V/F];7)
O, si realmente queremos construir una tabla que se adapte automáticamente al número de empleados
=ORGA.COLS(tbl_Empleado[V / F];LINEAS(F3#))
La referencia propagada F3 # Tomará todos los valores devueltos por la función ÚNICA. Allá Función LÍNEAS simplemente contará el número de elementos devueltos.
Si los datos no están ordenados correctamente, el Función ORGA.COLS no va a ser útil. Sin embargo, podemos utilizar la función BUSCAR XL haciendo un búsqueda de dos columnas. La escritura es bastante compleja pero qué recordar.
- Buscamos en los nombres (F3#) asociados a las fechas (G2#)
- En las columnas Empleado y Fecha (ambas separadas por un guión)
=BUSCARXL($F3#&"-"&G$2#;tbl_Empleado[Empleado]&"-"&tbl_Empleado[Fecha];tbl_Empleado[T / F])
Paso 5: agregue las casillas de verificación
Todo lo que queda es reemplazar VERDADERO y FALSO con casillas de verificación
Y obtenemos el siguiente resultado con formato condicional en las casillas de verificación como se explica en este artículo.
