Diagrama Gantt con tablas dinámicas

Reconozco que no hay nada novedoso en crear diagramas Gantt con Excel. Este blog aloja varias notas sobre el tema. Las técnicas más comunes para crear un diagrama de Gantt en Excel se basan en formato condicional o en gráficos de barras.

Todos los modelos que he visto hasta ahora, incluyendo los míos, tienen dos inconvenientes:

• por lo general tienen un solo nivel de actividad, es decir, no se puede descomponer una actividad central en varias sub-tareas;

• falta de flexibilidad a la hora de agregar o quitar alguna actividad.

El modelo sencillo que muestro en esta nota soluciona estos dos problemas usando tablas dinámicas.

Supongamos que queremos crear un diagrama de Gantt para un proyecto que se compone de 3 etapas. Cada etapa comprende varias actividades.
Empezamos por organizar los datos en una matriz de datos que convertimos en Tabla. Por ejemplo

A partir de esta tabla creamos esta tabla dinámica

Como puede apreciarse los campos de valores están resumidos con distintas funciones (Suma, Max.). Dado que cada registro aparece una única vez en la tabla de valores (existe una única línea para Etapa 1 - Actividad 1), podemos usar cualquier función que resuma valores excepto Contar.

También establecemos formato de fecha para el campo de fechas (Principio).

La ventaja de usar tablas dinámicas es que no necesitamos guardar ningún orden preestablecido al agregar o quitar filas de la tabla de datos. La tabla dinámica siempre estará agrupada jerárquicamente y también tenemos control total del orden.

Ahora tenemos que crear un gráfico dinámico. Los gráficos dinámicos reflejan la tabla dinámica en la cual se basan, no la base de datos de la tabla dinámica

Eligimos el tipo Barra Apilada y obtenemos este resultado

en el cual introduciremos, obviamente, algunas modificaciones. Empezamos por modificar las definiciones de la serie Principio para volverla "invisible" (ver mi prehistórica nota sobre el tema). Luego quitamos los botones y la leyenda. Este video muestra el proceso




A esta altura de los acontecimientos tenemos que corregir dos aspectos del gráfico: el orden de los valores en el eje de las X y el rango de los valores en el eje de las Y (recordemos que estamos usando un gráfico de barras de manera que el eje de las X es el eje vertical y el de las Y es el horizontal)

Si a pesar de las definiciones de Microsoft, el mínimo del eje de valores no es el mínimo del cuadro de valores, tendremos que hacer la corrección manualmente

En cuanto al eje de las categorías, usamos el menú de formato del eje

El último detalle es actualizar la tabla dinámica cada vez que introduzcamos un cambio en la tabla de datos (el gráfico dinámico que la refleja se actualiza junto con la tabla dinámica).
Si queremos evitar tener que hacer la actualización manualmente con cada cambio, podemos crear un evento usando la técnica que muestro en esta nota.

Copiar suma de varias celdas a una única celda como valor

Cada post que publico lleva por lo menos una etiqueta que identifica el contenido. A lo largo del tiempo he creado muchas etiquetas, como puede verse en la nube de etiquetas en la columna derecha del blog (o izquierda, si es que se me da por cambiar la plantilla).
Para este post debiera haber creado una nueva etiqueta, algo así como "técnicas complicadas para resolver problemas no tan graves existiendo otras técnicas más sencillas". Pero como resulta un poco largo seguramente terminaré endosándole la lacónica etiqueta "Macros".

La situación es la siguiente: en una hoja de Excel hay datos numéricos "desparramados" en varias celdas; la misión es sumar todos los valores y poner el resultado en una única celda. Esta celda debe contener un valor constante, no una fórmula. A los efectos del ejemplo uso una única hoja; el problema real comprendía varias hojas.

¿Cuáles son las opciones?

1. Tomar un papel y un lápiz, hacer la cuenta y teclear el resultado.
2. Totalizar los datos en una celda, copiar el resultado y pegarlo en la celda indicada como valor.
3. Usar una macro para agilizar el proceso.

La primera opción es una broma. El problema con la segunda opción es lo tedioso del proceso.

Nos queda así la opción de la macro, que es la que utilice al enfrentarme con el problema.

El código de la macro es la siguiente

Sub copy_and_sum()
    Dim rngCell As Range, dblTemp As Double
    Dim dblTotSum As New DataObject

    For Each rngCell In Selection
        If IsNumeric(rngCell) Then
            dblTemp = dblTemp + rngCell
        End If
    Next rngCell


    With dblTotSum
        .SetText dblTemp
        .PutInClipboard
    End With

End Sub

Para utilizar la macro comenzamos por seleccionar las celdas que queremos totalizar (clic a la primer celda y Ctrl-Clic a las restantes); luego activamos la macro (con ALT-F8 o un atajo de teclado); la macro suma los valores numéricos de las celdas y copia el total al Clipboard. Finalmente usamos Ctrl-V o Pegar para pegar el valor en la celda correspondiente.

Esta macro usa el objeto DataObject por lo que hay que activar la referencia al Microsoft Forms 2.0 Object Library.

A pesar de que este objeto está definido para copiar texto al Clipboard, podemos ver que también lo hará con valores numéricos y lo que es más importante, nos permitirá ir sumándolos.

En la primera parte del código usamos un loop For Each Next para asignar los valores numéricos a la variable dblTemp e ir sumándolos. En la segunda parte del código empezamos por asignar el valor de dblTemp al objeto dblTotSum para luego copiarlo al Clipboard con el método PutinClipboard.

Seleccionar celdas combinadas en una hoja de Excel

En más de un post me he explayado sobre ese mal hábito de usar "combinar y centrar" en las hojas de Excel. También he expuesto más de una vez las ventajas de usar "centrar en la selección" en su lugar.

No siempre podemos evitar el uso de "combinar y centrar", ya sea porque el rango a unificar es vertical (no exste la posibilidad de "centrar en la selección" verticalmente) o porque estamos lidiando con un cuaderno que recibimos de algún colega.

Si queremos "descombinar" todos los rangos combinados en una hoja, podemos seleccionar todas las celdas de la hoja (Ctrl-E o clic en el cuadrdado a la izquierda y arriba de A1) y pulsar el botón "Combinar y centrar". Si queremos reemplazar el "combinar y centrar" por el "centrar en la selección", podemos usar la macro que aparece en el post mencionado.

Si queremos marcar los rangos de celdas combinadas en una hoja de Excel, podemos usar esta macro. Las áreas combinadas aparecerán con fondo rojo; por supuesto, podemos adaptar el código para dar cualquier otro formato (por ejemplo, borde)

Sub mark_merged_cells()

    Dim rngSearchArea As Range, rngCell As Range

    Set rngSearchArea = Range(Cells(1, 1), _
                        Range(Cells.SpecialCells(xlCellTypeLastCell).Address))

    For Each rngCell In rngSearchArea
        If rngCell.MergeCells = True Then
            rngCell.Interior.Color = 190
        End If
    Next rngCell

End Sub
Sub select_merged_cells()

Si queremos sólo seleccionar las áreas combinadas, usamos esta macro

Sub select_merged_cells()
 
    Dim rngSearchArea As Range, rngCell As Range
    Dim rngTemp As Range
 
    Set rngSearchArea = Range(Cells(1, 1), _
                        Cells.SpecialCells(xlCellTypeLastCell).Address)
 
    For Each rngCell In rngSearchArea
        If rngCell.MergeCells = True Then
            If rngTemp Is Nothing Then
                Set rngTemp = rngCell
            Else
                Set rngTemp = Union(rngTemp, rngCell)
            End If
        End If
    Next rngCell
 
    rngTemp.Select
 
End Sub

En ambas macros definimos el área de búsqueda desde la celda A1 hasta la última celda usada en la hoja (para evitar que la macro corra por toda la eternidad) con 

Set rngSearchArea = Range(Cells(1, 1), Range(Cells.SpecialCells(xlCellTypeLastCell).Address))

Para seleccionar simultáneamente los distintos rangos en la segunda macro, usamos el método Union.