Diagrama Gantt con tablas dinámicas - versión mejorada

En la nota anterior sobre el tema, mostré las ventajas de crear un diagrama Gantt usando tablas dinámicas. Lo que falta en ese modelo es la posibilidad de mostrar gráficamente el avance del proyecto. La idea es mostrar con colores distintos la duración de la tarea y la parte de la tarea realizada.
Por ejemplo en esta situación

si se ha completado el 50% de la Actividad 1 de la Etapa 1, queremos que se vea así

La porción de color verde de la barra representa la parte de la tarea que ya ha sido completada.

Para poder lograr este efecto vamos a tener que agregar algunos campos en la base de datos de nuestro modelo.
En el modelo anterior la tabla tenía cinco campos (columnas): Etapa, Actividad, Principio, Fin y Duración.

La Duración es el resultado de restar el Fin del Principio.

En nuestro nuevo modelo vamos a agregar tres nuevos campos: % Completado, Completado y A Completar.

El campo "% Completado" es la estimación, en porcentaje, del avance de la tarea; el campo completado se calcula multiplicando "Duración" por "% Completado"

y "A Completar" es la diferencia entre "Duración" y "Completado"

A partir de esta tabla creamos esta tabla dinámica

Para sumarizar los valores (Principio, Completado y A Completar) podemos usar cuañquiera de las funciones Suma, Max o Min, ya que cada valor es único.
Sobre este reporte dinámico creamos un gráfico dinámico de barras apiladas, tal como hicimos en la nota anterior. El primer resultado es éste

Como en el ejemplo de la nota anterior vamos a ocultar los botones, volver "invisible" la serie Principio, corregir la escala del eje de las Y (las fechas) y ubicar la leyenda al pie del gráfico. Una vez hechas todas las correcciones obtenemos este gráfico

Este video muestra el proceso



El archivo con el ejemplo se puede descargar aquí.

Diagrama Gantt con tablas dinámicas

Reconozco que no hay nada novedoso en crear diagramas Gantt con Excel. Este blog aloja varias notas sobre el tema. Las técnicas más comunes para crear un diagrama de Gantt en Excel se basan en formato condicional o en gráficos de barras.

Todos los modelos que he visto hasta ahora, incluyendo los míos, tienen dos inconvenientes:

• por lo general tienen un solo nivel de actividad, es decir, no se puede descomponer una actividad central en varias sub-tareas;

• falta de flexibilidad a la hora de agregar o quitar alguna actividad.

El modelo sencillo que muestro en esta nota soluciona estos dos problemas usando tablas dinámicas.

Supongamos que queremos crear un diagrama de Gantt para un proyecto que se compone de 3 etapas. Cada etapa comprende varias actividades.
Empezamos por organizar los datos en una matriz de datos que convertimos en Tabla. Por ejemplo

A partir de esta tabla creamos esta tabla dinámica

Como puede apreciarse los campos de valores están resumidos con distintas funciones (Suma, Max.). Dado que cada registro aparece una única vez en la tabla de valores (existe una única línea para Etapa 1 - Actividad 1), podemos usar cualquier función que resuma valores excepto Contar.

También establecemos formato de fecha para el campo de fechas (Principio).

La ventaja de usar tablas dinámicas es que no necesitamos guardar ningún orden preestablecido al agregar o quitar filas de la tabla de datos. La tabla dinámica siempre estará agrupada jerárquicamente y también tenemos control total del orden.

Ahora tenemos que crear un gráfico dinámico. Los gráficos dinámicos reflejan la tabla dinámica en la cual se basan, no la base de datos de la tabla dinámica

Eligimos el tipo Barra Apilada y obtenemos este resultado

en el cual introduciremos, obviamente, algunas modificaciones. Empezamos por modificar las definiciones de la serie Principio para volverla "invisible" (ver mi prehistórica nota sobre el tema). Luego quitamos los botones y la leyenda. Este video muestra el proceso




A esta altura de los acontecimientos tenemos que corregir dos aspectos del gráfico: el orden de los valores en el eje de las X y el rango de los valores en el eje de las Y (recordemos que estamos usando un gráfico de barras de manera que el eje de las X es el eje vertical y el de las Y es el horizontal)

Si a pesar de las definiciones de Microsoft, el mínimo del eje de valores no es el mínimo del cuadro de valores, tendremos que hacer la corrección manualmente

En cuanto al eje de las categorías, usamos el menú de formato del eje

El último detalle es actualizar la tabla dinámica cada vez que introduzcamos un cambio en la tabla de datos (el gráfico dinámico que la refleja se actualiza junto con la tabla dinámica).
Si queremos evitar tener que hacer la actualización manualmente con cada cambio, podemos crear un evento usando la técnica que muestro en esta nota.

Planificador de Proyectos JLD

Un mes después del primer anuncio (y vaya aquí mi agradecimiento a los lectores que han colaborado) publico mi Planificador de Tareas JLD.

El modelo permite programar las tareas de un proyecto sencillo, establecer dependencias entre ellas y presentarlas en un diagrama de Gantt
.
Las tareas son introducidas en la hoja de tareas

• Las fechas y la duración son calculadas automáticamente en base a los parámetros introducidos
• La fecha de finalización es calculada automáticamente cuando se introducen la fecha de comienzo y la duración
• La fecha de comienzo es calculada automáticamente cuando se introducen la fecha de finalización y la duración
• La duración es calculada automáticamente cuando se introducen las fechas de comienzo y finalización
• Al establecerse una dependencia la fecha de comienzo es calculada de acuerdo a la fecha de finalización de la tarea precedente

La forma en que las fechas son calculadas se puede establecer en la hoja Definiciones

• tomar en cuenta sólo días hábiles o días corridos
• incluir la fecha de comienzo en la cuenta de los días o no
• definir las fechas de los feriados (si no se definen sólo fines de semana serán descontados de la cuenta de días hábiles)

La hoja del diagrama de Gantt muestra el desarrollo del proyecto en forma gráfico y también estadísticas del proyecto (fechas de comienzo y finalización del proyecto, total de días y total de días hábiles).

La hoja de control permite ver el estado de cada una de las tareas

El costo del modelo es de 7.50 Euros (o el equivalente en otras monedas), y ha sido desarrollado en dos versiones que pueden descargar de estos enlaces

• Versión para Excel 2003
• Versión para Excel 2007 0 Excel 2010

Las hojas y los códigos del modelo están protegidos con contraseñas. Una guía del modelo, que incluye la contraseña, puede descargarse con un costo adicional de 7.50 Euros.

• Guía del modelo Planificador de Tareas JLD (incluye contraseña)

El acceso a las fórmulas, hojas ocultas y códigos permite modificar el modelo para adaptarlo a las necesidades del usuario o para estudiar cómo ha sido construido.

Planificar proyectos – pedido de ayuda a mis lectores

Necesito la ayuda de mis lectores para verificar y corregir, donde haga falta, mi nuevo modelo para planificar actividades. Los primeros cinco lectores que estén dispuestos a ayudarme recibirán un enlace para descargar el modelo.

La ayuda consiste en usar el modelo, para encontrar "bugs" y sugerir mejoras de funcionamiento o diseño.

A cambio de la ayuda, una vez terminadas las correcciones, recibirán una copia del modelo con la contraseña que les permitirá ver todas las fórmulas y códigos utilizados en su desarrollo.

El mail aceptando ayudar debe enviarse a la dirección que figura en el enlace "Ayuda" (de ninguna manera poner la dirección del mail en un comentario!!). Desde ya, muchas gracias.

Y ahora, ¿qué es este modelo de planificación de actividades? Hemos tocado el tema tangencialmente en las notas sobre cómo crear un diagrama de Gantt en Excel. Este modelo va más lejos. La idea es poder definir actividades de un proyecto, crear dependencias entre las actividades (una actividad "precedente" determina la fecha de inicio de la actividad "dependiente"), mostrar la relación de la actividades en un diagrama de Gantt y crear una cuadro de control de la actividades.

Parte de los datos son calculados automáticamente: al determinar la fecha de comienzo y la duración, la fecha de finalización es calculada automáticamente. Lo mismo si se ingresa la fecha de finalización y la duración, etc.

Este modelo tiene dos objetivos:

• Dar una alternativa sencilla a la planificación de proyectos cuando el uso de aplicaciones como MSProject son un "overkill"
• Servir como material para estudiar las distintas técnicas empleadas (controles, nombres dinámicos, eventos, macros, gráficos dinámicos, etc.)

El modelo ha sido desarrollado en tres versiones: Excel 2003, Excel 2007 y Excel 2010.
Está compuesto de cuatro hojas visibles, dos ocultas y una que puede ser creada "al vuelo".

Inicio: definición del nombre del proyecto e instrucciones para el uso del modelo.

Actividades: definición de las actividades del proyecto (nombre, descripción, responsable, fecha inicio, final fin, duración, precedente, estatus de cálculo, estatus de datos). El modelo permite determinar "precedentes", actividades que deben ser completadas antes que otra actividad (la dependiente) pueda comenzar.

Gantt: diagrama de Gantt que se actualiza automáticamente de acuerdo a los cambios en la hoja Actividades. Se muestran también el número de días requerido para completar el proyecto (total y sólo días laborales).

To Do: permite controlar el estado de las actividades en relación a la fecha corriente.

Detalle de actividad: el hacer un doble clic en el nombre de una actividad (en la hoja Actividades), se crea automáticamente una hoja que permite detallar sub-tareas para la actividad. Si la hoja existe, pasa a ser la hoja activa.

Feriados: oculta; se puede acceder desde la hoja de actividades. Aquí se definen los días feriados para el cálculo del total de días laborales entre el principio y fin del proyecto.

Actualización: el modelo está disponible! Los enlaces para la descarga se encuentran en esta página.

Gantt con actividades repetidas en Excel

En el pasado he tocado el tema de construir diagramas Gantt con Excel. Podemos usar, básicamente, dos técnicas: gráficas de barras apiladas o formato condicional (también pueden consultar mi nota sobre diagrama Gantt dinámico).

Las técnicas que muestro en la nota no contemplan el caso en que una tarea se repite a lo largo del proyecto. En esta nota mostraré una técnica sencilla para crear ese tipo de diagrama de Gantt.

Supongamos este ejemplo sencillo

Nuestro proyecto comprende cinco tareas, algunas de las cuales se repiten en la segunda etapa.
Esto es lo que queremos obtener:

Empezamos por construir nuestro diagrama que mostrará la primera etapa del proyecto, seleccionando el rango A2:C7 en nuestro ejemplo e insertando un gráfico de tipo barra apilada

En Excel 2010 obtenemos este resultado, así que tendremos que hacer algunas modificaciones:

Abrimos el asistente Seleccionar origen de datos y:

Editamos el eje de las categorías para que incluya sólo los valores del rango A3:A7

Agregamos la serie Comienzo (el rango B3:B7) y la movemos al primer lugar (usando la flecha indicada en la imagen)

Ocultamos la serie “Comienzo” marcando “Sin relleno” en el asistente de formato de la serie y “Sin línea” en la opción Borde

También el eje de los valores (horizontal) requiere nuestra atención. Cambiamos el valor mínimo del eje de manera que coincida con la primer fecha del proyecto

Finalmente, eliminamos la leyenda y si es necesario cambiamos la orientación de los ejes de manera que la primer tarea quede en la parte superior del diagrama. Para esto seleccionamos el eje de las categorías (el vertical) y cambiamos las definiciones

El resultado es el siguiente

Para poder representar la segunda etapa de la tarea tendremos que crear una columna auxiliar. Podemos incluirla en la tabla, pero mi inclinación personal es ocultarla ya que no aporta ninguna información útil al usuario.

Insertamos una columna a la izquierda de la tabla (o mejor aún dos, Ay B) y creamos la columna auxiliar “Intervalo”

Intervalo cuenta los días transcurridos entre el final de la primer etapa de la tarea y el comienzo de la segunda etapa.

Ahora agregamos dos nueva series a la gráfica: Intervalo y Duración (de la etapa 2)

Dado que vamos a ocultar la columna A, tenemos que cambiar la forma en que Excel representa los datos de columna ocultas apretando el botón “Celdas ocultas y vacías” y marcando “Mostrar datos en filas…”

Ahora tenemos que volver la serie Intervalos invisible, tal como hicimos con la serie Comienzo, cambiando las definiciones de relleno y borde

El último detalle que nos queda son los valores 00/01/1900 (rango I5:I7), Esto se debe a que los valores son ceros, pero el formato es de fecha. Lo que hacemos es cambiar levemente el formato usando formato personalizado “dd/mm/aa;;” (agregamos dos veces punto y coma)

El resultado final

El archivo con el ejemplo se puede descargar aquí (versiones Excel 2003 y Excel 2010).

Diagramas Gantt dinámicos en Excel

En los albores de este blog publiqué una nota sobre cómo construir un diagrama Gantt con Excel. Mostramos allí dos posibilidades: usando formato condicional o usando gráficos.

En esta nota mostraremos como construir un diagrama de Gantt dinámico que nos permite mostrar en pantalla tareas que se extienden por períodos muy largos.

Cuando construimos el diagrama de Gantt en Excel con formato condicional, usamos una fila para cada tarea y una celda para cada unidad de tiempo. El modelo general consiste en usar la primer columna para definir las tareas, la segunda para la fecha de iniciación, la tercera para la duración, la cuarta para la fecha de finalización y a partir de la quinta columna usamos cada celda para representar una unidad de tiempo. Este es un ejemplo clásico

Las barras de color del diagrama las logramos usando estas fórmulas de formato condicional:



para las filas pares =Y($C3=F$2,$E3=F$2,RESIDUO(FILA(),2)=0)

para las filas impares =Y($C3=F$2,$E3;=F$2,RESIDUO(FILA(),2)=1)

Para construir la escala del tiempo (en la fila 2) ponemos en la celda F2 esta fórmula

=MIN(C3:C6)+7

que calcula la primer fecha más una semana de la primer tarea a realizar. En la celda G2 ponemos "=F2+7" y así sucesivamente.

Si observamos atentamente veremos que si bien la primer tarea concluye el 15/02/10, el diagrama parece indicar que lo hace el 12/02/10. Esto se debe a que usamos una resolución semanal para mostrar las tareas.
Podemos solucionar este problema usando una resolución diaria. En total necesitamos 90 días (la última fecha es el 26/03/10).

Si bien esto resuelve el problema, nos crea uno nuevo, el diagrama excede los límites de la pantalla

La solución ideal es, por lo tanto, trabajar con una resolución diaria pero que no exceda el ancho de la pantalla.

Empezamos por insertar algunas filas por encima de nuestro diagrama.

En la celda E3 calculamos la primer fecha del proyecto usando la función MIN. En la celda F5 creamos una referencia a la celda E3; en la celda G5 ponemos "=F5+1" y así sucesivamente para crear la escala de tiempo.

En la celda F6 creamos una referencia a la celda F5, seleccionamos el rango que comprende la semana (F6:L6) y aplicamos "combinar y centrar"

Cambiamos el ancho de las columnas de la selección a 0.6, quitamos los bordes interiores y obtenemos este resultado

Nótese que la fecha 01/01/10 parece ocupar una columna pero en realidad comprende 7 columnas, una para cada día de la semana.

Volvemos a aplicar esta técnica a cada grupo de siete columnas hasta obtener este resultado

Hemos mejorado en buena medida nuestro diagrama, pero aún nos queda una cuestión por solucionar.

Nuestro diagrama cubre ahora 13 semanas, con resolución diaria. Pero, ¿que pasa si una tarea se extienda por más de de 90 días del inicio del proyecto?
Esto lo solucionaremos creando un diagrama Gantt dinámico con fórmulas sencillas y una barra de desplazamiento de la barra de formularios.

Introducimos estos cambios en nuestro modelo:

1 – insertamos una columna a la izquierda de la hoja (la tabla del diagrama empieza ahora en la columna C).

2 – en la celda G3 ponemos la fórmula "=MIN(D5:D8)+A2"; el papel a cumplir por la celda A2 será explicado enseguida

3 –reemplazamos la fórmula en la celda G4 por una referencia a la celda G3

4 – en la pestaña Programador abrimos la etiqueta Insertar e insertamos una barra de desplazamiento

5 – definimos los parámetros de la barra de desplazamiento en la pestaña "control" del menú "formato de control"

Nótese que vinculamos el control con la celda A2. Esto hará que cuando movemos el cursor de la barra, la fecha en la celda G3 se va incrementando y así el resto de las celdas.

Para ejemplificarlo cambiamos la cantidad de días en la duración y podemos ver como al desplazar el cursor de la barra, se van modificando las fechas y actualizando el diagrama




El archivo puede descargarse aquí 
.

Diagramas Gantt con Excel

Todo quien se haya ocupado alguna vez de proyectos sabe qué es un diagrama Gantt. De acuerdo a Wikipedia: el diagrama de Gantt es una popular herramienta gráfica cuyo objetivo es el de mostrar el tiempo de dedicación previsto para diferentes tareas o actividades a lo largo de un tiempo total determinado.

Desde su introducción los diagramas de Gantt se han convertido en una herramienta básica en la dirección de proyectos de todo tipo, con la finalidad de representar las diferentes fases, tareas y actividades programadas como parte de un proyecto o para mostrar una línea de tiempo en las diferentes actividades.

Excel no cuenta con una herramienta propia para desarrollar diagramas Gant. Quien quiera desarrollar un diagrama Gantt profesional, con interdependencias entre actividades y demás elementos, debe usar una herramienta apropiada como MS Project, por ejemplo.

Pero si nuestras aspiraciones son menores y podemos contentarnos con un diagrama Gantt sencillo, podremos hacerlo con Excel.

Hay más de una forma de crear un diagrama Gantt en Excel. Los ejemplos de esta nota se encuentran en este cuaderno.

La más sencilla es ubicando los datos en un rango y luego crear el diagrama usando formato condicional.

Los pasos son los siguientes:

1 – Creamos una tabla con los datos (actividades, fechas de inicio y duración)




2 – Diseñamos un rango que contenga el diagrama Gantt. En nuestro ejemplo, la tabla con los datos ocupa el rango A1:D8. El rango para el diagrama ocupa el rango F1:S8. El rango F2:F8 contiene los nombres de las actividades; el rango G1:S1 contiene las fechas por semana (la primer celda se refiera a la celda B2, las restantes son el resultado de sumar 7 a la celda de la izquierda).

3 – Seleccionamos el rango G2:S8 y abrimos el menú de formato condicional.



En la ventanilla de Condición 1 usamos la opción Fórmula e introducimos esta:
=Y(G$1>=$B2,G$1<$D2)
4 – Para lograr el efecto de colores alternados podemos usar dos condiciones con las siguientes fórmulas:
para la primera condición (color azul) =Y(G$1>=$B2,G$1<$D2,RESIDUO(FILA(),2)=0)
para la segunda condición (color mora) =Y(G$1>=$B2,G$1<$D2,RESIDUO(FILA(),2)=1)


El efecto final es:



Otra alternativa es utilizar los gráficos de Excel para representar el diagrama Gantt. Para lograr esto usaremos el gráfico de tipo barras. Esta técnica requiere usar algunos "trucos".
Los pasos son los siguientes:

1 – Usamos la misma tabla de datos del ejemplo anterior

2 – Seleccionamos el rango A1:C8 (sin incluir la columna "Final"), abrimos el menú de gráficos y seleccionamos el tipo "Barras" subtipo "barra apilada"





3 – El primer truco consiste en hacer desaparecer la serie "comienzo"



Para lograr esto seleccionamos la serie "Comienzo" en el gráfico, abrimos el menú de formato de la serie y seleccionamos Borde--ninguno, Área--ninguna. Esto hará "desaparecer" la barra. También seleccionamos la leyenda y la borramos



4 – Seleccionamos el eje Y (donde aparecen las actividades) y en el menú de formato seleccionamos "Categorías en orden inverso" y "Eje de valores cruza en máxima categoría"



5 – Luego seleccionamos el eje X y lo adaptamos de manera que muestre un rango de fechas apropiado.



Como pueden ver, podemos introducir los valores en formato de fechas.

El formato final de nuestro diagrama es el siguiente



Como pueden ver, este formato es mucho más preciso que el anterior que estaba basado en formato condicional.

Fuentes y enlaces para esta entrada:
Gantt Charts in Microsoft Excel por Jon Peltier
Gantt Charts en MrExcel
Enlaces a notas sobre diagramas Gantt por Jon Peltier
Floating bars tutorial page de Tushar Mehta
Categorías: Graficos_, Varios_

Technorati Tags: Excel, Gantt en Excel