Como hacer un diagrama de Gantt en Excel22 AGOSTO, 2015PRODUCTIVIDADEl famoso diagrama de Gantt es una de las mejores aplicaciones gráficas a la hora de gestionar una serie de tareas (por ejemplo en un proyecto) a lo largo del tiempo. Básicamente consta de tres 2 datos fundamentales: duración de la tarea y fecha de comienzo de la tarea.A continuación os mostramos un diagrama de Gantt que hemos construido en Excel:

Este diagrama se ha construido con las siguientes herramientas de Excel:

Fórmulas (funciones): SUMA, SI, Y, HOY, DIASEM Otros: FORMATO CONDICIONAL

Para construir este Excel os vamos a mostrar los pasos necesarios que hay que dar y cual es la lógica subyacente al cálculo de las barras.

Inicio

Este diagrama de Gantt en Excel no tiene nada de VBA por detrás para que sea mucho más fácil de mantener y poder actualizar. Para dibujar las barras obtendremos un valor que nos indique en qué situación se encuentra la celda respecto de los datos aportados de (duración, inicio, fin y % de trabajo realizado).

Estructura

En la siguiente imagen podéis ver la estructura inicial de datos.

En la columna E hacemos lo siguiente: D + C (comienzo de la fase + duración = final de la fase)En la columna D hemos referenciado algunas fechas a comienzos o finales de otras fechas.En la fila 5 hemos escrito las fechas par poder hacer una línea de tiempo.

Crear lógica

Para crear la lógica para dibujar las líneas hemos pensado lo siguiente:En cada fila (ejemplo para la fila 9 Fase 1) hemos decidido dar un valor de 0, 1 ó 2 a cada celda en función de la siguientes variables:

Fecha (fecha correspondiente a la columna de la celda) de la línea temporal (fila 5),

Duración de la fase Comienzo de la fase Final de la fase % Cumplimiento (se supone que puedes ir calculando el grado

de avance)La lógica que hemos usado es:

Lógica 0: Si la fecha se encuentra fuera del comienzo y el final de la fase daremos el valor de 0

Lógica 1: Si la fecha se encuentra entre el comienzo y el final de la fase daremos el valor de 1

Lógica 2: Si la fecha se encuentre entre el comienzo de la fase y la fecha de avance daremos el valor de 2. (La fecha de avance se calcula como Fecha de Inicio+Duración Fase*%Cumplimento)

Para poder hacer estos cálculos tenemos que hacer una función anidada un poco compleja que hace esto:=SI( lógica 2; 2; SI( lógica 1; 1; 0) )En un lenguaje humano esta lógica sería:=SI( fecha>=inicio y fecha<=fecha avance entonces =2;SI(fecha>=inicio y fechaQue tampoco es super fácil de entender pero algo más…Traducido a un lenguaje de Excel=SI(Y(G5>=D9;G5<D9+C9*F9);2;SIY(G5>=D9;G5<E9);1;0))'con las referencias a celdas fijas=SI(Y(G$5>=$D9;G$5<$D9+$C9*$F9);2;SI(Y(G$5>=$D9;G$5<$E9);1;0))

En el fondo esto es tan sencillo como anidar 2 funciones SI. En rojo hemos pintado la lógica 2 y en azúl hemos pintado la lógica 1 y hemos quitado los símbolos de $ (símbolo para fijar celdas a una columna y/o fila) para su mayor comprensión.Una vez creada esta lógica el ejercicio quedaría así:

Como podemos ver en la imagen anterior, hemos creado una matriz con los valores 0, 1 y 2. Al modificar los valores de la iquierda de fechas, duración  o % Cumplimiento veremos como se van cambiando las fórmulas.

Formato inicial

Lo primero que haremos será : ajustar el tamaño de las columnas donde irán las barras del

diagrama de Gantt, luego aplicaremos formato a la fila de total, después a la cabecera y al fondo de las filas superiores, después ajustaremos el formato de fecha para que sea más

legible y lo pondremos en vertical para poder leerlo reducimos el tamaño de la fila 6 para dejar un pequeño hueco quitamos la rejilla o maya de (ver cómo) ponemos borde superior e inferior a todas las filas en un gris

muy claritoNuestro Excel quedaría de la siguiente manera:

Por ahora ya va quedando más decente pero hay que hacer lo más complicado y lo más importante, crear nuestras barras del diagrama.

Formato Condicional

Mediante un formato condicional crearemos las barras del diagrama de Gantt. La lógica es la siguiente:

Donde hay ceros que la fuente se ponga en blanco (para que no se vea el numerito) y en el fondo de la celda nada.

Donde hay 1 que la celda se ponga de un color clarito (yo he usado azúl) y la fuente del mismo color para que no sea vea.

Donde hay 2 que la celda se ponga de un color más oscuro (yo he usado azúl oscuro) y la fuente del mismo color para que no sea vea el número.

Para añadir este formato condicional hacemos (deberemos tener elegido nuestro rango de aplicación):

Pestaña Inicio >> Formato condicional >> Nueva regla >> Aplicar formato a celdas que contengan

Una vez seleccionado elegimos la opción de que el valor de la celda sea igual a [ponemos nuestro número] y formateamos:

Deberemos crear tres reglas para poder aplicar los formatos de colores y crear las barras.El resultado será el siguiente:

Ya hemos hecho lo más difícil pero todavía se puede mejorar.

Mejoras de valor añadido

En la primera imagen de este artículo veíamos algunas mejoras que puede aportar un montón de valor a nuestro gráfico de Gantt y que prácticamente no cuestan nada.

En la imagen anterior hay cuatro recuadros rojos que aportan las siguientes funcionalidades y que describiremos sucintamente en este apartado:

Primer recuadro: Contiene una caja llamada “Intervalo”. Esto nos permite introducir un número de días para las fechas de nuestro diagrama. Así, si queremos ver nuestro diagrama en semanas escribiremos 7 o cada dos días escribiremos 2

Segundo recuadro: “Starting date” nos permite elegir en qué fecha queremos que empiece nuestro línea temporal, es decir, la primera fecha del diagrama.

Recuadro horizontal: Simplemente es cambiar, el color de la barra para la barra de total. Esto, parece una cosa sin importancia, pero facilita la lectura general.

Recuadro vertical: contiene dos elementos. Lo primero es que mediante un formato condicional se señala la fecha del día en el que estamos. Esto se consigue sabiendo le fecha del día de hoy mediante la función HOY() y un formato condicional asociado a la fila de las fechas. Lo segundo es la marca divisoria que señala los lunes. Esta marca se consigue de manera similar a la anterior pero con la función DIASEM.

Se pueden añadir muchas mejoras (no dudes en sugerir todo lo que se te ocurra e intentaremos plasmarlo), mientras tanto os dejo en este link el archivo descargable para que puedas practicar y ver cómo se han ido realizando todas las partes de este diagrama de Gantt.