jueves, septiembre 28, 2017

El misterioso caso de los números cambiantes (Excel y las definiciones regionales)

En algún post del pasado he mencionado que, en ciertas circunstancias, Excel tiende a ser más "amable" de lo deseado. En su afán de ayudarnos Excel toma decisiones y transforma datos a medida que los vamos ingresando en una celda. Por ejemplo, si ingresamos el valor 15/9 Excel supondrá que se trata de la fecha 15 de setiembre y agregará el año corriente resultando 15/09/2017.

Esta característica de Excel conlleva problemas potencialmente graves. El resultado de la transformación está ligada a las definiciones regionales del sistema (Windows). Por ejemplo, si las definiciones regionales de nuestro sistema son las de España e ingresamos 10/9 Excel lo transformará en 10/09/2017 de acuerdo al patrón de la definición (día/mes/año)


Pero si nuestras definiciones regionales son las de Estados Unidos


si bien veremos en la celda 10/9/2017, de acuerdo al patrón mes/día/año, la fecha ingresada es el 9 de octubre.

El problema no está limitado solamente a las fechas. Supongamos que nuestro sistema tiene definiciones regionales donde la coma es el separador de miles y el punto de los decimales. Ahora copiamos este valor de la tasa GBP/USD (libra esterlina / dólar americano) y lo pegamos en una celda de Excel


El resultado será el siguiente


El valor fue copiado de un sitio español donde el separador de decimales es la coma; pero en mi sistema la coma es el separador de miles. Si nos fijamos veremos que en la barra de las fórmulas aparece 13845 (trece mil ochocientos cuarenta y cinco) y en la celda Excel ha aplicado el formato de número.
¿Cómo solucionamos este problema? Si se trata de un valor podemos hacer un clic en el icono de las opciones de pegado y usar el asistente para importar texto

Vamos directamente al paso 3


y cambiamos en Avanzadas las definiciones de separador de decimal y separador de miles. Ésto solucionará el problema.

¿Y si se trata de una tabla con muchos valores? Lo solucionamos con la opción Texto en Columnas (Datos-Herramientas de Datos) o con Power Query, por supuesto, como mostraré en una próxima nota.

martes, septiembre 26, 2017

Cálculo de horas trabajadas con Power Query - tercera nota

En la primera nota vimos como calcular horas trabajadas por un empleado usando el Power Query. El objetivo del post era mostrar que lo que ya habíamos mostrado como hacerlo con Excel (fórmulas) podía hacerse también con Power Query.

En la segunda nota vimos las grandes ventajas que nos ofrece el uso de Power Query al hacer los cálculos combinando los informes de varios empleados, en forma automática y sin necesidad de recrear las fórmulas con cada nuevo informe.

En ambas notas mostramos el caso sencillo donde no hay que diferenciar las horas por turnos. En este post vamos a recrear el ejemplo del segundo pero agregando la necesidad de calcular cuántas de las horas trabajadas fueron en el turno diurno y cuántas en el turno nocturno.

De hecho vamos a estar recreando en Power Query el modelo que mostré en este post. La lógica aplicada es la misma pero "traducida al idioma" Power Query. Las ventajas, como ya expliqué en las notas anteriores, son:
  • la robustez del modelo: no hay fórmulas en celdas que puedan ser alteradas desapercibidamente;
  • la facilidad de combinar un gran número de fuentes de datos y crear informes;
  • actualización de los datos e informes con sólo apretar un botón.
Para este ejemplo vamos a usar los mismos archivos del segundo post (los informes de los empleados que guardamos en la carpeta Informes).
Al crear este nuevo modelo el primer paso es definir las horas de comienzo y finalización del turno noche. En una hoja de Excel ponemos esta sencilla tabla

Creamos una consulta a la tabla como "sólo conexión"


Podemos ver  PASOS APLICADOS que Power Query ha cambiado el tipo de dato de las columnas Comienza y Termina a número decimal (identificado por 1.2 a la izquierda del encabezado de la columna). Ésto sirve a nuestros propósitos así que lo aceptamos.

A partir de esta consulta creamos una nueva con la opción Referencia


Abrimos la nueva conulta y cambiamos el nombre a "noche_comienzo".Con un clic del botón derecho del mouse y apuntando al valor en la fila Comienza y activamos la opción "Rastrear desagrupando datos" (en inglés sencillamente, drill down)


con este resultado


Ahora guardamos esta consulta también como "sólo conexión". Repetimos el proceso para crear una consulta para el valor de "noche_final", esta vez seleccionando el valor de la columna Termina.
Nuestra hoja se ve ahora así


Ahora vamos a aplicar los pasos que aplicamos en la segunda nota para crear una consulta que integre todos los informes (archivos xlsx) guardados en la carpeta Informes. Al final de proceso tenemos esta consulta en la ventana del editor de PQ


Aquí vamos a hacer un pequeño cambio a lo que mostramos en la segunda nota. Vamos a extraer de Entrada y Salida le fecha y dejar sólo las horas. Para hacerlo seleccionamos ambas columnas y aplicamos en Transformar-Columna de Fecha y Hora-Solo hora


ahora tendremos


La próxima transformación que hacemos es transformar las horas de Entrada y Salida al tipo de datos número decimal.


Ahora vamos a aplicar la misma lógica que usamos en la solución con Excel tradicional

# - calculamos las horas trabajadas con una columna personalizada con esta fórmula

=if [Salida]<[Entrada] then
1+[Salida]-[Entrada]
else
[Salida]-[Entrada]

# agregamos dos columnas auxiliares "noche-comienzo"


y "noche-final" de la misma manera . Cambiamos el tipo de datos a número decimal.


# creamos una columna personalizada que llamaremos "Mediana - Entrada". Ésta es una columna auxiliar (con el mismo objetivo de la correspondiente columna en el modelo en Excel ya mencionado). Seleccionamos la columna Entrada y las dos columnas que acabamos de crear y creamos una nueva columna con Agregar Columnas-Estadísticas-Mediana


La columna creada recibe, por defecto, el nombre "Median" que vamos a reemplazar con "Mediana-Entrada". Ésto podemos hacerlo agregando un paso o, como muestro en la imagen abajo, reemplazando el valor en la ventana de las fórmulas


Hacemos lo mismo con Salida-noche_comienzo y noche_final para crear la columna "Mediana-Salida".

# creamos una nueva columna auxiliar, "aux1" con esta fórmula condicional

# ahora calculamos las horas trabajadas en el turno noche con esta fórmula:
[Horas trabajadas]+[#"Mediana-Entrada"]-[#"Mediana-Salida"]-[aux1]

# todo lo que nos queda por hacer es calcular las horas diurnas que, lógicamente, son la diferencia entre las horas trabajadas y las horas nocturnas

# Finalmente eliminamos todas las columnas auxiliares que creamos y dejamos solamente aquellas que queremos que aparezcan en nuestro informe


Transformamos las columnas de horas al tipo de dato "Hora" y cargamos la consulta a una hoja del cuaderno


Para crear un informe que resuma los datos por empleados empezamos por crear una nueva consulta que cuyo origen es una referencia a la que acabamos de crear. Cambiamos el tipo de datos de las columnas de horas a número decimal y aplicamos "Agrupar por" de esta manera



Cargamos la consulta a una hoja del cuaderno y cambiamos el formato de las columnas de las horas al tipo personalizado [hh]:mm en el menú de formato de celdas de Excel (ver la explicación en la nota anterior).
Nuestro resumen se verá así




jueves, septiembre 21, 2017

Cálculo de horas trabajadas con Power Query - segunda nota

En la nota anterior mostramos como calcular las horas trabajadas por un empleado a partir de un informe en una hoja de Excel usando Power Query. En un caso sencillo como el del ejemplo la ventaja de usar Power Query en lugar de Excel tradicional (fórmulas) pasará desapercibida para la mayoría de los usuarios que aún no hayan adoptado las nuevas herramientas de Excel (Power Query, PowerPivot).
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



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).