viernes, mayo 15, 2015

El gráfico (de barras) de Wendy

Hace unos días me preguntaban si se puede construir este gráfico con Excel

La respuesta es si, pero haciendo algunas manipulaciones. Podemos crearlo usando un gráfico de barras apiladas (el proceso puede verse en este video, que también aparece al final de la nota). Los datos originales consisten de una única serie de valores (de color azul en la imagen: 9, 1, 0, 0,0) pero para nuestro gráfico deberemos crear una segunda serie de valores coompletarios (de color gris en la imagen)



Los datos originales están en el rango C3:D7. En la columna E (Auxiliar 1) calculamos los valores de la segunda serie, la complementaria, con la fórmula:
=(MAX($D$3:$D$7)+1)-D3
Empezamos por seleccionar el rango C2:D7 y creamos un gráfico de barras apiladas



resultando el siguiente gráfico


Hacemos los ajustes necesarios:

Abrimos el menú de formato del eje vertical y marcamos "Categorías en orden inverso" y "Eje horizontal cruza: en la categoría máxima".



Seleccionamos la serie Auxiliar 1 y cambiamos el color del relleno



Ahora quitamos las líneas y el relleno del área de trazado y del área del gráfico, la leyenda y las líneas de cuadrículas.
Ocultamos el eje horizontal con el menú Presentación-Ejes



y la línea del eje vertical



El último paso es agregar las etiquetas de valores. Podríamos hacerlo agregándolas y luego arrastrándolas manualmente al extremo derecho del gráfico. Pero, por supuesto, queremos ahorrarnos todo este trabajo manual, en particular si nuestro gráfico tiene una gran cantidad de series.
El truco consiste en agregar otra serie auxiliar, definirla en el eje secundario usando como eje de las categorías los valores de la serie de datos. Luego quitamos el relleno y las líneas de esta serie para volverla invisible y agregamos las etiquetas que muestren los valores de la categoría.

Los  valores de la segunda serie auxiliar los definimos sumando los valores de la serie a los de la serie Auxiliar 1 y agregándole 1


Agregamos la serie al gráfico pero al definir los rótulos del eje seleccionamos los valores de la serie de los datos (el rango D3:D7 en nuestro ejemplo)


Veremos que los valores del eje vertical cambian. Esto será corregido en el próximo paso que es definir la nueva serie auxiliar en el eje secundario.



Como podemos apreciar debemos hacer varias transformaciones. Nuestro gráfico tiene ahora dos ejes horizontales y dos verticales (si alguno de los ejes no aparece, debemos volverlo visible usando el menú Presentación-Ejes). Las transformaciones a realizar son:
  • invertir el eje vertical secundario;
  • cambiar la referencia a las etiquetas del eje vertical primario;
  • cambiar el relleno y la línea de la serie Auxiliar 2 para volverla invisible (sin relleno, sin línea);
  • volver invisible los ejes horizontales;
  • volver invisible el eje vertical secundario;
  • quitar la línea y las marcas del eje vertical primario.
Confío en que el lector ya sabe realizar estas tareas (todo el proceso puede verse en el video abajo). Una vez realizadas estas transformaciones nuestro gráfico se ve así:



Solo nos queda agregar las etiquetas a la serie invisible Auxiliar 2. Empezamos por seleccionar la serie Auxiliar 2 y agregamos las etiquetas. Por defecto Excel introduce los valores de la serie en el centro de la barra. Nosotros queremos mostrar los nombres de la categoría (el eje vertical secundario) en el extremo de la barra. Una vez introducidas las etiquetas modificamos las definiciones con el menú de Formato de Etiquetas de Datos



Finalmente nuestro gráfico se ve así


Todo el proceso de creación puede verse en este video



sábado, mayo 09, 2015

Calcular horas trabajadas en distintos turnos

Esta nota es la continuación del post anterior sobre el cálculo de horas nocturnas con Excel. Supongamos ahora la existencia de tres turnos de trabajo: día, tarde y noche. Nuestra tarea es calcular dentro de las horas trabajadas por cada operario en una jornada, cuántas pertenecen a cada turno.
En el post anterior vimos cómo calcular las horas nocturnas, que de hecho es calcular cuantas de las horas trabajadas caen dentro de un intervalo determinado.

A los efectos de nuestro ejemplo supongamos que el turno tarde comienza a las 18:00 y concluye a las 22:00 y el turno corre de las 22:00 hasta las 06:00. Las restantes horas del día, de 06:00 a 18:00, corresponden al turno día.

Para calcular cuántas horas caen dentro de cada turno usamos este modelo:




Esta tabla es interactiva y el modelo puede decargarse usando el icono de descarga



Las fórmulas en el modelo son:

Total trabajado: =RESIDUO(C7-B7,1), que ya hemos explicado en el post anterior;

Día: =(B7>C7)*MEDIANA(0,C7-$D$4,RESIDUO($D$4-$C$3,1))+MAX(0,MIN($C$3,C7+(B7>C7))-MAX($D$4,B7))

Noche: =RESIDUO(C7-B7,1)-(C7<B7)*($C$4-$D$4)-MEDIANA(C7,$D$4,$C$4)+MEDIANA(B7,$D$4,$C$4) explicada en el post anterior

Tarde: =D7-E7-G7 - sencillamente resstamos los turnos Día y Noche del Total Trabajado.

lunes, abril 27, 2015

Cálculo de horas trabajadas en turno noche

Un tema recurrente en las consultas que recibo es cómo calcular horas trabajadas en turno noche. Supongamos la siguiente situación:

  • un operario trabaja desde las 20:00 hasta las 04:00
  • el turno noche, cuya tarifa es distinta del horario normal, corre de las 22:00 a las 06:00

¿Cómo calculamos con Excel cuántas horas de las 8 horas trabajadas pertenecen al turno noche? Veamos esta tabla con varios ejemplos


La fórmula en la celda E7, que calcula correctamente las horas trabajadas en turno noche tal como ésta definido en las celdas D3 y E3, es:

=RESIDUO(C7-B7,1)+MEDIANA(B7,$E$3,$D$3)-MEDIANA(C7,$E$3,$D$3)-(C7<B7)*($D$3-$E$3)

Esta fórmula se basa en la función MEDIANA y en la función RESIDUO
.
Para explicar esta fórmula vamos a dividirla en tres módulos:

RESIDUO(C7-B7,1)  - esta fórmula, que ya hemos explicado, calcula el total de horas trabajadas.

+MEDIANA(B7,$E$3,$D$3)-MEDIANA(C7,$E$3,$D$3) - Esta expresión nos permite calcular el total de horas trabajadas fuera del turno noche. Este resultado será siempre negativo o cero, de manera que al restarlo de las horas trabajadas (el primer "módulo" de nuestra fórmula), obtenemos las horas que caen dentro del horario nocturno.

(C7<B7)*($D$3-$E$3) - el objetivo de esta expresión es corregir el resultado cuando el horario trabajado pase de un día al otro. Si la hora de salida es mayor que la hora de entrada, todo el trabajo ha sido realizado dentro del mism día; en este caso la expresión dá cero (FALSO) y no varía el resultado. Si la hora de salida es menor que la de entrada, la jornada terminó al día siguiente; la expresión da VERDADERO (que equivale a multiplicar por 1), y el resultado es corregido de acuerdo.

Este cuadro interactivo permite ver como funciona cada módulo de la fórmula (los valores de entrada y salida y los del horario nocturno pueden ser modificados)





En este post pueden ver una solución para el caso de tres turnos (día, tarde y noche).