GENERAR DIAGRAMA DE GANTT EN UN GRÁFICO CON VBA

Hace unas semanas escribí un post acerca de cómo confeccionar un diagrama de Gantt usando únicamente nuestra hoja Excel, fórmulas y vba: CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA (Y FÓRMULAS)

Siguiendo con esta temática, y dadas algunas consultas que he recibido, donde me preguntaban si podría realizarse este mismo trabajo pero empleado un gráfico (de barras) donde se pueda mostrar el tiempo consumido y el tiempo pendiente, me he decidido a realizar otro post complementario del anterior respondiendo a esas consultas.

Veamos, vamos a seguir con mismo ejemplo de proyecto que el post anterior, pero explicaremos dos maneras de realizar el ejemplo, uno donde tendremos que introducir los datos manualmente para tener actualizado nuestro diagrama y otro donde una vez introducidas las fechas programadas, Excel lo hará todo automáticamente.

Primero os mostraré la parte de fórmulas (en la hoja) y luego vemos la macro, que será la misma para ambos casos.

Para el ejemplo de forma manual vamos a realizar en nuestra hoja el siguiente cuadro, con cuatro columnas: Detalle de las tareas, fecha de inicio, tiempo consumido (días) y tiempo restante (días):

Para este tipo de informe, vamos a necesitar que cuando un valor sea “0” no aparezca, que quede en blanco (en las columnas de tiempo consumido y tiempo restante). Esto lo podemos hacer con una macro, pero me ha parecido interesante introducir un pequeño truco para lograrlo de una forma igual de eficiente. Tan solo tenéis que seleccionar las dos columnas y en formato de celda > personalizada > introducís el siguiente formato: #"" de esta manera el cero no aparecerá.

El resultado, una vez aplicada la macro sería este:

Como podéis observar, aquí seremos nosotros los encargados en ir modificando los tiempos de forma manual, es decir que a medida que avance el proyecto el tiempo consumido tenderá a aumentar y el tiempo restante a disminuir, de manera que cuando finalicemos todas las barras de progreso serán de color azul.

Sin duda, el tener que ir modificando los datos de forma manual puede llegar a ser (si el proyecto es muy grande) en un trabajo bastante tedioso, para ello, realizaremos las siguientes modificaciones añadiendo algunas columnas y fórmulas de apoyo. Esta es la segunda forma que os comentaba anteriormente:

Añadimos una columna para determinar la fecha final estimada.
Añadimos otra columna denominada “Control” en la que vamos a introducir la siguiente fórmula:
=SI(E2<HOY();E2;HOY()) de manera que si la fecha fin es menor que hoy() ponemos la fecha fin, de lo contrario indicamos la fecha actual

Añadimos otra columna denominada “Tiempo Total” indicando el tiempo en días desde la fecha inicio hasta la fecha fin, con la siguiente fórmula:
=SIFECHA(B2;E2;"D")

En la columna de “Tiempo Consumido” introducimos la siguiente fórmula:
=SIFECHA(B2;F2;"D") que es la diferencia en días desde la fecha de inicio a la fecha de la columna “Control”, y nos devolverá los días que hemos consumido hasta hoy().

En la columna “Tiempo Restante” introducimos la siguiente fórmula:
=SI(C2>G2;"0";G2-C2) donde si el Tiempo Consumido es mayor que el Tiempo Total ponemos un cero (pero al tener formateada la columna aparecerá vacío), y si no lo es extraemos la diferencia en días entre el tiempo Consumido y el Tiempo Total, obteniendo el tiempo restante.

Puede parecer un poco complejo, pero el resultado es interesante, ya que ahora siempre tendremos actualizado el progreso de nuestro proyecto al día en el que nos encontramos. Si se producen modificaciones, tan solo tendríamos que modificar la fecha fin a mano y listo.

Si confeccionáis el proyecto a futuro, el gráfico os saldrá completamente en blanco (es obvio) ya que todavía no habéis empezado, y en el momento de finalizar estará completamente en azul.

Esta imagen dependiendo del momento en el que descarguéis el archivo no se corresponderá con lo que estáis viendo ahora, ya que los días han pasado y seguramente esté todo en azul.

Ahora os dejo la macro comentada, que debéis pegar en un módulo estándar:

Sub Gantt()
'Definimos variables
Dim Chart As Excel.ChartObject
Dim gGrafic As Chart
'Con la hoja activa
With ActiveSheet
'Si existe algún tipo de gráfico, lo borramos
For Each Chart In .ChartObjects
Chart.Delete
Next
'Guardamos longitud de datos
fin = Application.CountA(.Range("A:A"))
'insertamos gráfico
.Shapes.AddChart.Select
'Seleccionamos área de gráfico
ActiveChart.ChartArea.Select
With ActiveChart
'Configuramos gráfico con ChartWizard
.ChartWizard Source:=ActiveSheet.Range("A1:D" & fin), Gallery:=xlBar, Format:=3, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels:=1
'Indicamos posición del gráfico
.ChartArea.Left = Sheets(2).Cells(11, 1).Left
.ChartArea.Top = Sheets(2).Cells(11, 1).Top
'Indicamos el largo del gráfico
.ChartArea.Width = 800
'Indicamos el ancho
.ChartArea.Height = 200
'Ancho de intervalo que sea igual a cero
'de forma que las barras ocupen todo el ancho
.ChartGroups(1).GapWidth = 0
'La primera colección es la columna de las fechas de inicio
'debemos hacerlas invisibles en el gráfico, y lo configuramos así:
With .SeriesCollection(1)
With .Border
.LineStyle = xlNone
End With
.InvertIfNegative = True
.Interior.ColorIndex = xlNone
End With
'la segunda colección (tiempo consumido)
With .SeriesCollection(2)
'Damos color azul (dejo formato RGB para mayor elección de color)
.Interior.Color = RGB(0, 153, 255)
'Mostramos etiquetas
.ApplyDataLabels
End With
'la tercera colección (tiempo restante)
With .SeriesCollection(3)
'Damos color rojo (dejo formato RGB para mayor elección de color)
.Interior.Color = RGB(255, 0, 0)
'Mostramos etiquetas
.ApplyDataLabels
End With
'Eje de los valores
With .Axes(xlValue)
'Indicamos la fecha mínima
.MinimumScale = Application.WorksheetFunction.Min(ActiveSheet.Range("B2:B" & fin))
'Ocultamos las barras divisorias principales en vertical
.HasMajorGridlines = False
End With
'Eje de categorías
With .Axes(xlCategory)
'Invertimos el orden
.ReversePlotOrder = True
'Mostramos las barras divisorias principales en horizontal
.HasMajorGridlines = True
End With
End With
End With
End Sub

Además en el módulo de cada hoja que contenga los datos, debéis pegar lo siguiente, así con cada cambio que hagáis en la hoja, los datos se actualizarán.

Private Sub Worksheet_Change(ByVal Target As Range)
Call Gantt
End Sub

También podéis utilizar solo la macro principal y vincular a un botón en la hoja, pero debéis cambiar activesheet por la hoja en la que tengáis los datos.

Por supuesto, el gráfico lo podéis situar en el lugar de la hoja que mejor consideréis, la instrucción está en estas líneas de la macro, donde situamos el gráfico según la posición de las celdas, en este caso fila 11, columna 1.

.ChartArea.Left = Sheets(2).Cells(11, 1).Left
.ChartArea.Top = Sheets(2).Cells(11, 1).Top

La clave de todo este proceso consiste en ocultar (transparentes) las barras que hacen referencia a las fechas de inicio y dar color a las otras dos columnas (además de invertir posición y configurar el ancho).

Y aquí finalizamos este pequeño ejercicio. Sin duda una buena opción para controlar diariamente y de forma automática el progreso de nuestros proyectos.

Espero que os sea de utilidad 🙂

Descarga el archivo de ejemplo pulsando en: GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA