El 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 ejemplo de diagrama de Gantt 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.
Ventajas y desventajas del diagrama de gantt
La verdad es que yo soy un amante de los diagrmas de Gantt. A mi me gustan mucho y me parecen súper práticos. El problema de ellos es, entre otros, que hay que mantenerlos actualizados y eso lleva mucho tiempo en general.
Si quieres saber más ventajas y desventajas sobre el diagrama de Gantt te recomiendo este artículo:
<< Diagrama de Gantt: Ventajas y desventajas >>
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).
Ahora vamos con la estructura y la explicación del diagrama de Gantt.
Estructura
En la siguiente imagen podéis ver la estructura inicial de datos de este sencillo diagrama de Gantt.
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 fecha
Que 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 clarito
Nuestro 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 de descarga, un diagrama de Gantt para que puedas practicar y ver cómo se han ido realizando todas las partes de este diagrama de Gantt.
Ahora ya sabes cómo realizar una gráfica de Gantt en Excel.
[sociallocker]
[/sociallocker]