PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT

Hola a todos!

En el post de hoy voy a tratar de ayudar y dar respuesta a un lector que me comentaba la necesidad de usar SmartArt para generar un diagrama de Gantt personalizado.

(*Antes de entrar en materia, si estáis usando Excel 2010 debéis leer el post completo dado que tendréis que realizar una pequeña modificación en el código).

En un principio, y teniendo en cuenta la naturaleza y objetivo de un diagrama de Gantt, creo que la mejor solución es utilizar una estructura de jerarquía horizontal, para ello me voy a basar en este objeto de SmartArt para realizar el ejemplo (Jerarquía multinivel en horizontal)

PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT

Teniendo en cuanta esto, antes de comenzar a programar, debemos diseñar nuestro cuadro de mando para introducir todos los ítems que necesitamos para confeccionar nuestro diagrama, para este ejemplo utilizaré estas tareas:

PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT1

Aunque se podría programar para obtener automáticamente el porcentaje de consecución de objetivos de acuerdo con la fecha, para este ejemplo he decidido que solo se calculan de forma automática los días entre fechas, el resto es a criterio del usuario el llevar el seguimiento y controlar el %.

Como podéis observar, estoy utilizando diferentes niveles de tareas, dado que algunas conforman otras de entidad superior, por ejemplo las diferentes fases de diseño o de desarrollo. Esto implicará que en nuestra estructura de jerarquía se muestren esas dependencias y ofrezca mayor información visual.

En realidad este diseño puede personalizarse de muchas formas, tantas como le gusten al usuario.

Pues bien, una vez que tenemos esta información en la hoja “DATOS”, ya podemos utilizar el siguiente código para generar nuestro peculiar diagrama de Gantt:

Sub DIAGRAMA_GANTT_SMARTART()
'Declaramos variables
Dim Diseño As SmartArtLayout
Dim Shape As Excel.Shape
Dim oNodos As SmartArtNodes
Dim inserta As Shape
Dim i As Integer, Fin As Integer
Dim j As Integer, valor As Double
Dim NPorcent As String, Per_2 As Long, Per_1 As Long
With Sheets("ESTRUCTURA")
.Select
'Eliminamos TODOS objetos en la hoja "ESTRUCTURA"
For Each Shape In .Shapes
Shape.Delete
Next
'Insertamos objeto SmartArt, en este caso "Jerarquía Multinivel"
Set Diseño = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2008/layout/HorizontalMultiLevelHierarchy")
Set inserta = .Shapes.AddSmartArt(Diseño)
Set oNodos = inserta.SmartArt.AllNodes
'Verificamos número de nodos necesarios contando los ítems de la página "DATOS"
Fin = Application.CountA(Sheets("DATOS").Range("A:A"))
'Creamos nodos
Do While oNodos.Count < Fin
oNodos.Add.Promote
Loop
'Eliminamos nodos sobrantes y los nombramos con la información de la hoja "DATOS"
For i = 2 To Fin
Do While oNodos(i - 1).Level < Sheets("DATOS").Range("B" & i).Value
oNodos(i - 1).Demote
Loop
Next
'Eliminamos último nodo (estará vacío al tener encabezado la hoja "DATOS")
oNodos(Fin).Delete
'aplicamos estilos
For Each Shape In .Shapes
'Colores
Shape.SmartArt.Color = Application.SmartArtColors("urn:microsoft.com/office/officeart/2005/8/colors/accent2_1")
'Estilos rápidos
Shape.SmartArt.QuickStyle = Application.SmartArtQuickStyles("urn:microsoft.com/office/officeart/2005/8/quickstyle/simple2")
'Iniciamos loop para recorrer el diagrama desde el último item al primero
For j = Fin To 2 Step -1
'Si el % está vacío, asignamos un valor 0 a la variable valor
If Sheets("DATOS").Range("F" & j).Value = Empty Then
valor = 0
ElseIf Sheets("DATOS").Range("F" & j).Value > 1 Then
valor = 1
Else
valor = Sheets("DATOS").Range("F" & j).Value
End If
'expresamos en color el porcentaje de cumplimiento de objetivos
With oNodos(j - 1).Shapes.Fill
.TwoColorGradient Style:=msoGradientVertical, Variant:=1
.GradientStops(2).Color = vbWhite
.GradientStops(1).Position = valor
.GradientStops(2).Position = valor
.GradientStops(1).Color.RGB = RGB(204, 204, 255)
End With
'Adicionalmente añadimos el porcentaje en número al diagrama y lo coloreamos en azul
With oNodos(j - 1)
.TextFrame2.TextRange.Text = Sheets("DATOS").Range("A" & j) & " " & Format(valor, "Percent")
NPorcent = Sheets("DATOS").Range("A" & j) & " " & Format(valor, "Percent")
Per_1 = UBound(Split(NPorcent)) + 1
Per_2 = UBound(Split(NPorcent)) + 2
.TextFrame2.TextRange.Words(Per_1).Font.Fill.ForeColor.RGB = vbBlue
.TextFrame2.TextRange.Words(Per_2).Font.Fill.ForeColor.RGB = vbBlue
End With
Next j
'Dimensionamos la imagen
With .Shapes(1)
.Height = 581.25 'Alto del objeto
.Width = 600.5 'Ancho del objeto
.Top = 100 ' Altura en la hoja
.Left = 14.25 ' A la izquierda de la hoja
End With
Next
End With
End Sub

Una vez aplicado el código generamos el siguiente diagrama:

PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT2

Efectivamente, logramos nuestro propósito, el % de consecución se equipara al color azul, que se va desplazando a la derecha a medida que vamos cumpliendo los plazos. Este efecto lo conseguimos con esta parte de la macro:

With oNodos(j - 1).Shapes.Fill
.TwoColorGradient Style:=msoGradientVertical, Variant:=1
.GradientStops(2).Color = vbWhite
.GradientStops(1).Position = valor
.GradientStops(2).Position = valor
.GradientStops(1).Color.RGB = RGB(204, 204, 255)
End With

Al superponer los dos gradientes conseguimos evitar el degradado de los dos colores y que aparezca una línea delimitadora entre el azul y el blanco.

Importante, si estáis programando con Excel 2010 debéis añadir un factor de corrección en la siguiente línea de código, y es añadir un -0.01 al final:

.GradientStops(1).Position = valor - 0.01

Esto se corrige en las versiones 2013 y 2016 (comprobado), pero en 2010 es necesario rebajar en un 0.01 puntos el gradiente nº 1 para que no se muestren los colores difuminados. El ejemplo lo dejo sin el 0.01 dado que estoy programando en Excel 2016 y no es necesario.

También es muy interesante la forma en la que podemos colorear parte de un texto, dado que lo que hacemos es detectar las dos últimas palabras (el número y el símbolo del porcentaje) y aplicarle un color del sistema (azul):

.TextFrame2.TextRange.Words(Per_1).Font.Fill.ForeColor.RGB = vbBlue
.TextFrame2.TextRange.Words(Per_2).Font.Fill.ForeColor.RGB = vbBlue

Aunque podríamos seguir introduciendo elementos en cada nodo, creo que con este ejemplo se consigue, con una simple visual, hacer una idea del nivel de desarrollo en el que se encuentra nuestro proyecto.

En cuanto a la información de la hoja “DATOS”, usamos toda la información excepto las fechas y los días. Los datos obligatorios para generar el diagrama son las tareas, la jerarquía y los porcentajes (que van desde el 0% al 100%).

Y esto esto, espero que os resulte de interés : ) Os dejo dos archivos, uno para 2010 y el otro para versiones superiores.

Descarga el archivo de ejemplo pulsando en: PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT

Descarga el archivo de ejemplo pulsando en: PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT_v2010

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Anuncios

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):

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA1

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:

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA

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:

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA2

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.

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA3

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

 

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í:

CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA

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:

CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA1

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 🙂

CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA2

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í