CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA (Y FÓRMULAS)

Hace unos días un lector me solicitó una propuesta de diagrama de Gantt, le era indiferente si lo realizaba con fórmulas, macros o incluso gráficos.

Lo cierto es que después de pensarlo varias veces, me decidí por un desarrollo híbrido, es decir, parte con fórmulas y otra parte con código. Pero veamos poco a poco el diseño (siempre es algo subjetivo) que le he propuesto.

Lo fundamental en un diagrama de Gantt es tener una matriz con la información (en la escala temporal que nos interese) en donde podamos indicar el horizonte temporal de nuestros proyectos. En este caso en particular vamos a utilizar el día como unidad de medida y de referencia, de forma que nuestra hoja Excel nos indique en cada columna el día (en letra) del mes y el número del día, así:

En la primera fila de forma manual vamos a usar celdas combinadas para nombrar todo el mes (Enero).

En la segunda fila como una dato auxiliar vamos a colocar la fecha, desde el 01/01/2016 hasta el final (en este archivo he puesto hasta 31/12/2016, un año completo), es sencillo, solo tendréis que poner las dos primeras fechas y arrastrar la información. Esta información luego la podéis ocultar o poner el texto de color blanco para que no se vea.

En la tercera fila recogeremos la primera letra del día que se trate, para esto en una hoja auxiliar “APOYO” (podéis utilizar la ubicación que mejor os venga), indicamos que el 1 se corresponde con el lunes y el 2 con el martes y así hasta el 7 que es viernes. Y en tercera fila introducimos la siguiente fórmula:

=EXTRAE(BUSCARV(DIASEM(G2;2);APOYO!$A$1:$B$7;2;0);1;1)

Con esta fórmula obtenemos el día de la semana DIASEM de la fecha introducida en la segunda fila y elegimos la modalidad 2 (lunes 1 a domingo 7), con esto obtenemos el número del día de la semana, ahora tan solo tenemos que buscar el nombre en letra en la Hoja “Apoyo” y luego extraer el primer carácter.

En la cuarta línea indicaremos el día según la fecha, es decir los días del mes. Es decir función =DIA(G2).

Todo este proceso lo reproducimos hasta diciembre. Y colocamos las rayas en horizontal que más adelante nos servirán para introducir las líneas de colores del diagrama.

Como habéis podido ver, he comenzado el gráfico en la columna “G” y eso se debe a que ahora debemos introducir una serie de campos necesarios para que la macro funcione correctamente:

En la primera columna indicamos un ID (es igual que sea un número o un nombre, lo importante es que contenga un dato, si no lo tiene la macro no funcionará para esa fila.

En la segunda columna indicaremos la tarea que queremos cuantificar en nuestro diagrama.

En la tercera columna indicaremos la fecha en la que vamos a iniciar la actividad.

En la cuarta columna indicamos la duración (en días)*

En la quinta columna indicamos la fecha fin. * en el momento en que la indiquemos automáticamente el campo DURACIÓN mostrará el número de días entre las dos fechas. Si no indicamos fecha fin, debemos introducir número de días de duración, o de lo contrario la macro no mostrará nada.

Y por último, la sexta columna contiene un campo de comentarios, que se reflejará dentro de las barras de color del diagrama.

Ahora que ya lo tenemos todo, procedemos a incluir la siguiente macro en un módulo estándar:

Sub DiagramaGantt()
'desactivamos actualización de pantalla
Application.ScreenUpdating = False
'para evitar parpadeos en el cursos del ratón lo cambiamos
Application.Cursor = xlNorthwestArrow
'definimos variables
Dim i As Double
Dim j As Double
With ActiveSheet
'indicamos longitud de columnas y filas (con datos)
frow = Application.CountA(.Range("A:A")) + 3
fcol = Application.CountA(.Range("4:4"))
'limpiamos el área del gráfico con color y el contenido de texto cuando actualicemos
.Range("G5:NH" & frow).Interior.Pattern = xlNone
.Range("G5:NH" & frow) = vbNullString
On Error Resume Next
'Iniciamos un doble bucle para "pintar" de azul los días de cada actividad
'teniendo en cuenta el campo duración y fecha
For i = 5 To frow
'si contiene fecha fin calculamos los días entre las fechas
If CDate(.Cells(i, 3).Value) And CDate(.Cells(i, 5).Value) Then
Dias = DateDiff("D", CDate(.Cells(i, 3).Value), CDate(.Cells(i, 5).Value))
.Cells(i, 4).Value = Dias
End If
For j = 5 To fcol
If CDate(.Cells(i, 3).Value) = CDate(.Cells(2, j).Value) And .Cells(i, 4).Value > 0 Then
'color azul de la barra de progreso
.Range(Cells(i, j), .Cells(i, j + .Cells(i, 4))).Interior.Color = RGB(36, 150, 228)
'mayusculas en texto
.Cells(i, j) = UCase(.Cells(i, 6))
'negro en el texto
.Cells(i, j).Font.Color = vbBlack
'resaltado en negrita
.Cells(i, j).Font.Bold = True
End If
Next j
Next i
End With
Application.ScreenUpdating = True
Application.Cursor = xlDefault
End Sub

En la macro estamos haciendo referencia a la hoja activa, y esto es porque ahora debemos incluir la siguiente macro en el módulo de cada hoja:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call DiagramaGantt
End Sub

De manera que no tengamos que estar ejecutando la macro con cada cambio que hacemos, sino que solo con pulsar la hoja, la macro se ejecutará según el evento: Worksheet_SelectionChange(ByVal Target As Range)

Y ya tenemos nuestro diagrama de Gantt perfectamente montado 🙂

Antes de acabar me gustaría comentar algunos temas:

El formato del archivo debe ser .xlsm la razón son las columnas, si guardamos como xls el archivo entrará en modo compatibilidad y responderá al número máximo de columnas permitido para esa versión (256 columnas) y como podéis imaginar un año tiene 365 días, de forma que no funcionaría correctamente.

La estructura de los datos, es decir, las columnas y las filas a partir de donde ingresamos la información interactúan con el código, cualquier modificación hará que no funcione correctamente. Tan solo debéis introducir ID, FECHAS Y DURACIÓN y si procede comentario, el resto lo hará el código.

Descarga el archivo de ejemplo pulsando en: CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA

Como post complementario, también puedes visitar este enlace, donde propongo otra alternativa pero con gráficos: Pulsa aquí