Mostrar y ocultar series en gráficos de Excel dinámicamente

¿Cómo podemos hacer para mostrar u ocultar series de datos en un gráfico de Excel dinámicamente? Con controles de la barra de formularios, que son sencillos de usar y no requieren programación.
Por ejemplo, supongamos este gráfico

Nuestro objetivo es añadir un elemento que nos permita con un clic mostrar u ocultar cada una de las series.

Veamos los pasos a dar:

Agregamos tres casillas de verificación de la barra de formularios, una para cada serie

Reemplazamos el texto de cada casilla por el nombre de la serie.

Definimos la celda vinculada al control

En este caso he elegido una celda visible, para mostrar el funcionamiento del control. En una implementación real, usaremos una celda oculta.

Hacemos lo mismo con los restantes controles.

Cuando la casilla está activada, el valor de la celda vinculada pasa a ser VERDADERO; cuando no lo está el valor será FALSO. Vamos a aprovechar estos valores en nuestro mecanismo.

El próximo paso consiste en crear cuatro nombres definidos, uno para cada serie y uno para los valores de las categorías (el eje de las X, los meses en nuestro ejemplo).

Para los valores del eje de las X

Mes=dinamico!$B$3:$B$14

Para las series

Ventas =SI(dinamico!$G$20,dinamico!$C$3:$C$14,dinamico!$F$3:$F$14)
Costos=SI(dinamico!$H$20,dinamico!$D$3:$D$14,dinamico!$F$3:$F$14)
Ganancia=SI(dinamico!$I$20,dinamico!$E$3:$E$14,dinamico!$F$2:$F$14)

Como puede verse, estos nombres contienen fórmulas condicionales. Si el valor de la casilla de verificación es VERDADERO, la condición se cumple y el rango es el definido en la tabla de datos; si el valor es FALSO, el rango elegido es F3:F14 que no contiene ningún valor (y por lo tanto no tiene representación en el gráfico).

Ahora tenemos que reemplazar los rangos relevantes en la función SERIES de cada una de las series con los nombres definidos. Esto puede hacerse de varias maneras, por ejemplo:

Seleccionamos la serie VENTAS para que en la barra de las fórmulas aparezca la fórmula SERIES

Reemplazamos los rangos relevantes por los nombres tomando en cuenta no eliminar el signo de admiración (!) que aparece a la izquierda del rango

Apretamos Enter. En lugar del nombre de la hoja aparecerá ahora el nombre del cuaderno

También podemos usar el formulario Modificar Serie de la opción Seleccionar datos

La ventaja de esta técnica es que podemos usar Pegar Nombres (F3) en lugar de tener que escribirlos manualmente. La otra ventaja es que podemos reemplazar el rango de la categorías en el formulario de Editar Rótulos de Eje, sin necesidad de tener que reemplazarlo en la fórmula SERIES de cada serie.

Finalmente, ocultamos las celdas vinculadas, agregamos un cuadro de grupo para agrupar los controles (sólo por cuestiones estéticas) y ya hemos completado nuestro modelo



Descargar el ejemplo.

Calcular con Excel horas trabajadas con intervalos

Una consulta frecuente es cómo calcular con Excel las horas trabajadas en un lapso determinado, tomando en cuenta sólo los días hábiles y las horas de trabajo.

Por ejemplo, cuantas horas de trabajo hay desde el 29/09/2010 hasta el 01/10/2010, tomando en cuenta que la jornada de trabajo es de 08:00 a 17:00.

Excel nos permite hacer el cálculo con relativa facilidad si tomamos en cuenta ciertos detalles.
En nuestro ejemplo, el total trabajado es 90 horas que calculamos con esta fórmula

=DIAS.LAB(B1,B2)*(B4-B3)

En nuestra fórmula usamos DIAS.LAB para calcular los días hábiles entre la fecha inicial y la final. DIAS.LAB no toma en cuenta los sábados y domingos. Las horas trabajadas cada día las calculamos fácilmente como la diferencia entre la hora de cierre y la de comienzo.

Un detalle importante es que la celda que muestra el total de horas (B5) debe tener el formato [hh]:mm

Una situación distinta es cuando el turno de trabajo concluye al día siguiente. Por ejemplo, un turno que empieza a las 22:00 y termina a las 06:00

Recordemos que Excel no puede calcular diferencias de horas negativas.

Modificamos nuestra fórmula para que también pueda calcular las horas en casos como ese

=DIAS.LAB(B1,B2)*RESIDUO(B4-B3,1)

En lugar de la fórmula con RESIDUO, podemos usar también

=DIAS.LAB(B1,B2)*(B4-B3+(B4<B3))

 En caso de querer hacer el cálculo complementario, horas trabajadas descontando las horas de descanso, modificamos levemente nuestra fórmula =DIAS.LAB(B1,B2)*(1-RESIDUO(B4-B3,1))

En este caso, el trabajo se interrumpe de 22:00 a las 06:00 del día siguiente, lo que nos deja un día de trabajo de 16 horas que multiplicado por 10 días laborales nos da 160 horas.

Diseño clásico de tablas dinámicas en Excel 2007

En relación a la nota anterior sobre el tema, un lector me consultaba si era posible cambiar el diseño por defecto de las tablas dinámicas en Excel 2007 de manera que se abrieran con el diseño "clásico" (Excel 2003) y que también se pudieran arrastrar los campos, como era posible en el Excel Clásico.

En la nota anterior pasé por alto un detalle importante que da respuesta a las dos inquietudes de mi lector.

Al crear una tabla dinámica en Excel 2007, el diseño por defecto es el siguiente

Para cambiar el diseño al del Excel Clásico, en lugar de usar los estilos de tablas dinámicas como mostramos en la nota anterior, podemos usar el formulario Opciones de Tablas Dinámicas

En la pestaña "Mostrar" marcamos la opción "Diseño de tabla dinámica clásica". Esta opción no sólo cambia el diseño de la tabla sino que también permite arrastrar los campos, incluidos los campos de página, tal como lo hacíamos en las versiones anteriores de Excel. Una vez activada esta opción la tabla se verá así

Nótese que ahora los rótulos de los campos de columnas son visibles y es posible arrastrarlos. Lo mismo con los campos de página.

Este video muestra el proceso



Otro "tip" para los nostálgicos del Excel Clásico. En Excel 2007 se puede activar el "viejo" asistente de tablas dinámicas apretando el atajo de teclado Alt+T+B.