En este post mostraré un escenario más complejo (más real) donde nuestra misión es consolidar informes mensuales de varios empleados en un único informe. Y ésto debe suceder automáticamente y con sólo apretar un botón.
En este ejemplo cada empleado envía un informe semanal de las horas trabajadas. En este caso tenemos tres empleados pero, por supuesto, podrían ser cinco, veinte o cien.
La misión es:
- calcular las horas trabajadas de cada empleado;
- descontar media hora de descanso/comida cada día excepto que el empleado haya trabajado menos de siete horas;
- crear un informe que totalice las horas netas trabajadas por cada empleado en la semana.
Empezamos por crear una carpeta para guardar los informes semanales de los empleados que recibimos en hojas de Excel, como ésta (al final del post hay un enlace al video que muestra todo el proceso)
Abrimos un cuaderno Excel y creamos una consulta que consolide todos los archivos (informes) guardados en la carpeta de los informes con Obtener Datos-Desde un archivo-Desde una Carpeta
Vamos a resistir la tentación de apretar el botón Combinar y en su lugar apretamos Editar
Eliminamos todas las columnas con excepción de Content y Name. Name contiene el nombre del archivo que es a su vez el nombre del empleado pero con la terminación .xlsx. Para quitarla y que nos nos quede sólo el nombre del empleado usamos Reemplazar
con este resultado
Ahora expandimos la columna Content (apretando la doble flecha a la derecha del encabezado) para combinar los archivos. En la ventana del editor veremos que la hoja contiene dos objetos
Elegimos "Tabla1" que es el rango que contiene la tabla de fechas y horas (más explicaciones en el video al final de este post donde muestro todo el proceso). Después de apretar Aceptar veremos esta tabla de datos combinados
¡La columna con los nombres de los empleados ha desaparecido! Para recuperarla hacemos un clic en el "engranaje" a la derecha del paso aplicado "Otras columnas quitadas1" y marcamos "Name"
y luego volvemos a seleccionar el último paso con un clic
Ahora nuestros datos están completos y podemos empezar a transformarlos. Empezamos por cambiar el nombre de la columna Name a Empleado. Como en el ejemplo del post anterior cambiamos el tipo de datos de Entrada y Salida a número decimal. Ésto nos permite realizar operaciones aritméticas sencillas con los datos evitando usar funciones DateTime y Time de Power Query con las que no estamos familiarizados.
y luego creamos la columna personalizada Horas Trabajadas
La columna personalizada aparece con el formato "Cualquiera" por lo que tenemos que transformarlo a número decimal. La forma más fácil es con el menú Transformar-Tipo de Datos-número decimal.
Ahora tenemos que descontar media hora de cada fila donde el trabajador trabajo siete horas o más. Nuevamente agregamos una columna calculada con una fórmula condicional
Ahora eliminamos las columnas Entrada y Salida y cambiamos el tipo de datos de Horas Trabajadas y Horas Trabajadas Neto a Hora
Cargamos esta consulta a una hoja de Excel y tendremos un reporte detallado. Seguidamente vamos a crear un reporte agrupado por empleado.
Empezamos por crear una consulta cuyo origen es la consulta anterior usando Referencia en la ventana de las consultas
Empezamos por crear una consulta cuyo origen es la consulta anterior usando Referencia en la ventana de las consultas
En la nueva consulta transformamos las columnas de la horas al tipo de datos número decimal y luego agrupamos por Empleado creando dos columnas: Horas Trabajadas - bruto y Horas Trabajadas - neto
con este resultado
Para que nuestro informe sea legible queremos que los datos aparezcan como horas, no como números decimales (en el video de la nota agrego una explicación sobre el tipo de datos Duración de Power Query).
Para expresar los datos como horas acumuladas usamos el formato personalizado [hh:mm] en la tabla de la hoja
con el resultado esperado
Este video muestra todo el proceso
En el próximo post mostraré como hacer el cálculo tomando en dos turnos (diurno y nocturno).
Para expresar los datos como horas acumuladas usamos el formato personalizado [hh:mm] en la tabla de la hoja
Este video muestra todo el proceso
Excelente ejercicio y gracias por mostrarnos las bondades de estas nuevas herramientas tan maravillosas que Microsoft ha creado.
ResponderBorrarExcelente solución llevaba varios días peleándome con los tipos de de datos, pero tengo una duda , en mi caso hay que descontar 1,30 por dia , pero los sábados no haría falta como podría filtrar los sabados
ResponderBorrarUna posibilidad sería crear una columna con el número de día de la semana (usando Agregar columna-Fecha-Día) y filtrando la tabla de manera de quitar los sábados; otra posibilidad, suponiendo que los sábados deben ser tenidos en cuenta, es agregar una condición en la columna personalizada de manera que si es sábado (usando al columna número de día) no descontar, caso contrario descontar.
ResponderBorrarMuchas gracias Jorge, la segunda opción me parece mas razonable , mi intención es que sea lo mas automático posible de cara a los gerentes , voy a probar de hacerlo , si me atasco pediré ayuda :)
ResponderBorrar