Gantt con actividades repetidas en Excel

En el pasado he tocado el tema de construir diagramas Gantt con Excel. Podemos usar, básicamente, dos técnicas: gráficas de barras apiladas o formato condicional (también pueden consultar mi nota sobre diagrama Gantt dinámico).

Las técnicas que muestro en la nota no contemplan el caso en que una tarea se repite a lo largo del proyecto. En esta nota mostraré una técnica sencilla para crear ese tipo de diagrama de Gantt.

Supongamos este ejemplo sencillo

Nuestro proyecto comprende cinco tareas, algunas de las cuales se repiten en la segunda etapa.
Esto es lo que queremos obtener:

Empezamos por construir nuestro diagrama que mostrará la primera etapa del proyecto, seleccionando el rango A2:C7 en nuestro ejemplo e insertando un gráfico de tipo barra apilada

En Excel 2010 obtenemos este resultado, así que tendremos que hacer algunas modificaciones:

Abrimos el asistente Seleccionar origen de datos y:

Editamos el eje de las categorías para que incluya sólo los valores del rango A3:A7

Agregamos la serie Comienzo (el rango B3:B7) y la movemos al primer lugar (usando la flecha indicada en la imagen)

Ocultamos la serie “Comienzo” marcando “Sin relleno” en el asistente de formato de la serie y “Sin línea” en la opción Borde

También el eje de los valores (horizontal) requiere nuestra atención. Cambiamos el valor mínimo del eje de manera que coincida con la primer fecha del proyecto

Finalmente, eliminamos la leyenda y si es necesario cambiamos la orientación de los ejes de manera que la primer tarea quede en la parte superior del diagrama. Para esto seleccionamos el eje de las categorías (el vertical) y cambiamos las definiciones

El resultado es el siguiente

Para poder representar la segunda etapa de la tarea tendremos que crear una columna auxiliar. Podemos incluirla en la tabla, pero mi inclinación personal es ocultarla ya que no aporta ninguna información útil al usuario.

Insertamos una columna a la izquierda de la tabla (o mejor aún dos, Ay B) y creamos la columna auxiliar “Intervalo”

Intervalo cuenta los días transcurridos entre el final de la primer etapa de la tarea y el comienzo de la segunda etapa.

Ahora agregamos dos nueva series a la gráfica: Intervalo y Duración (de la etapa 2)

Dado que vamos a ocultar la columna A, tenemos que cambiar la forma en que Excel representa los datos de columna ocultas apretando el botón “Celdas ocultas y vacías” y marcando “Mostrar datos en filas…”

Ahora tenemos que volver la serie Intervalos invisible, tal como hicimos con la serie Comienzo, cambiando las definiciones de relleno y borde

El último detalle que nos queda son los valores 00/01/1900 (rango I5:I7), Esto se debe a que los valores son ceros, pero el formato es de fecha. Lo que hacemos es cambiar levemente el formato usando formato personalizado “dd/mm/aa;;” (agregamos dos veces punto y coma)

El resultado final

El archivo con el ejemplo se puede descargar aquí (versiones Excel 2003 y Excel 2010).

Formato Condicional en Excel 2010 – Cambios y Mejoras

En la nota sobre formato condicional personalizado con iconos pasé por alto las diferencias y mejoras introducidas en la versión 2010 de Excel (gracias a Carola por llamar mi atención sobre el error). Si bien los cambios y mejoras son menos dramáticos de los introducidos en Excel 2007, vale la pena pasar revista a las diferencias entre Excel 2007 y Excel 2010 en lo que a formato condicional se refiere.

En esta nota veremos los cambios en el formato condicional con conjuntos de iconos. Esta funcionalidad fue introducida en Excel 2007

La limitación en Excel 2007 es que no podemos combinar entre iconos que pertenecen a distintos conjuntos. En Excel 2010 podemos cambiar el icono de cada una de las condiciones

Esta funcionalidad incluye la posibilidad de determinar que bajo determinada condición no aparezca ningún icono

Esto es útil cuando queremos que aparezca un icono sólo cuando se cumple una determinada condición, por ejemplo los tres mejores meses de ventas

Otro cambio es la posibilidad de elegir el conjunto de iconos visualmente desde una lista desplegable

Otra herramienta introducida en Excel 2007 y mejorada en Excel 2010 son las barras de datos.

En este ejemplo aplicamos formato condicional con barra de datos en la versión Excel 2007

Podemos ver que las proporciones entre las barras del mejor mes (Agosto) y el peor (Setiembre) no concuerdan con la diferencia entre los números. Las barras de datos en Excel 2007 no sirven para comparar valores. Este problema fue solucionado en Excel 2010. Los mismos datos en Excel 2010 se ven así

Otras mejoras en formato condicional con barras de datos son:

• Posibilidad de elegir entre relleno degradado y relleno sólido (en Excel 2007 sólo degradado)
• Posibilidad de poner borde a la barra

• Posibilidad de representar valores negativos en forma efectiva

Formato condicional personalizado con iconos en Excel 2007/10

Entre las funcionalidades cuyas sus posibilidades más se han extendido en Excel 2007/10 se cuenta sin dudas el formato condicional. Ya he tratado el tema en varias notas, en general y el uso de iconos en particular.

Un lector me consulta:

Quiero utilizar conjuntos de iconos de la siguiente manera: 1.Valores entre 0 y 4: flecha verde. 2.Valores entre 4 y 10: flecha naranja 3. Resto de valores, bien por debajo de 0 o por encima de 10: flecha roja ¿¿Se puede hacer?? Es que no encuentro ninguna fórmula

En realidad no necesitamos ninguna fórmula (recordemos que estamos usando Excel 2007 o Excel 2010). Podemos usar el conjunto de iconos, pero tendremos que modificar las opciones por defecto que Excel nos ofrece.

En el caso de mi lector, queremos usar tres iconos/flechas diferentes pero para cuatro posibilidades. Vamos a mostrar el proceso de construir la regla.

Supongamos que esta es la lista de valores a la que queremos aplicar el formato condicional (que por comodidad he ordenado de menor a mayor sin que esto sea necesario para aplicar las reglas)

Empezamos por seleccionar el rango de la lista y elegir la opción “conjunto de iconos-4 flechas de color”

Ahora debemos cambiar el formato por defecto que aplica Excel, para lo cual usamos el menú Formato Condicional-Administrar reglas-Editar reglas

Cambiamos la opción Tipo de “porcentual” a “número”

En la primer regla ponemos “>10” y cambiamos el icono usando el botón de iconos (Aclaración: esta opción existe sólo en Excel 2010. Gracias a Carola por llamar mi atención sobre este punto)

Al hacer esto los valores en la segunda regla cambian automáticamente, determinando el límite inferior de la regla

Ponemos como límite superior “4” para esta regla y cambiamos el icono. Esto determina el límite inferior de la tercera regla

La cuarta regla queda definida por defecto. Apretamos “Aceptar” y..voila!

Diferenciar fechas de números en Excel

Ya hemos explicado en este blog que en Excel, las fechas son números. Por ejemplo, la fecha de hoy, 15/07/2011, es para Excel el número 40739 que es el número de días transcurridos desde el 01/01/1900.

Cuando introducimos en una celda un valor que Excel pueda reconocer como fecha, el formato de fecha es aplicado automáticamente. Pero, por debajo del formato en pantalla, Excel sigue “viendo” el número de serie que representa la fecha. Esta captura de pantalla demuestra el proceso

Esta característica puede crearnos dificultades si tenemos que diferenciar en una fórmula entre fechas y números.

Excel cuenta con varias funciones que nos permiten evaluar el tipo de datos que contiene una celda; por ejemplo: ES.PAR, ESTEXTO, ESNUMERO, etc. (todas bajo la categoría Información en el asistente de funciones). Pero no tiene una función “ESFECHA”, que nos permita establecer si la celda evaluada contiene una fecha.

La única alternativa con funciones es usar la función CELDA con el argumento “formato”. Si el resultado de la fórmula es D1, D2, D3, D4 o D5, entonces la celda evaluada contiene una fecha

Por ejemplo

Pero esta función no reconoce todos los formatos legítimos de fechas (aparentemente debido a los formatos regionales)

Una solución más práctica es crear una UDF (función definida por el usuario) aprovechando que en Vba si hay una función para reconocer fechas: IsDate

Creamos la UDF poniendo este código en un módulo común del editor (si lo guardamos en el Personal, la función estará disponible para todos los cuadernos activos)

Function contiene_fecha(rngCell As Range) As Boolean

    contiene_fecha = IsDate(rngCell)
   
End Function

Al aplicar la función obtenemos el resultado “VERDADERO” o “FALSO” de acuerdo al contenido de la celda

Análisis Pareto en Excel 2003

En la nota anterior sobre análisis de datos según el principio de Pareto mostramos cómo crear el cuadro de resultados y el gráfico con Excel. La solución con tablas dinámicas que mostré en esa nota sólo se aplica a Excel 2010.

Entre las mejoras introducidas en Excel 2010 se cuentan seis funcionalidades en la opción “Mostrar como…” de las tablas dinámicas. Entre ellas “% del Total en…”, que calcula el total acumulado como porcentaje del total. Esta funcionalidad no está disponible en las versiones anteriores de Excel.

En esta nota mostraré un rodeo para dar la misma solución que Excel 2003
.
En la tabla de datos agregamos un campo (columna): “Pct del Total” (porcentaje del total)

La fórmula para calcular los valores del campo es

=C2/SUMA($C$2:$C$287)

que sencillamente calcula el porcentaje de cada valor en relación al total. Como hemos definido nuestra tabla como “lista” no debemos preocuparnos por actualizar las fórmulas cuando agreguemos o quitemos valores. Si usamos versiones anteriores a Excel 2003 tendremos que usar rangos dinámicos.

Una vez creado el campo auxiliar, creamos la tabla dinámica con el campo Categoría en el área de las filas y el campo Ventas en el área de datos. Luego ordenamos la tabla de mayor a menor según el campo de Ventas.

En el próximo paso agregamos el campo “Pct del Total” al área de ventas y definimos “Mostrar datos como..” con la opción “Total en…” y “Categoría” como capo de base

Todo el proceso puede verse en este video



Finalmente creamos el gráfico dinámico, donde deberemos hacer algunos cambios para obtener algo humanamente legible. Empezamos por crear el gráfico (un clic al icono de gráficos en la barra de herramientas de las tablas dinámicas). Por defecto Excel crea el gráfico en una hoja separada de manera que si queremos ubicarlo en la misma hoja de la tabla dinámica tendremos que cambiar la ubicación.

El segundo paso es cambiar las proporciones del gráfico (por defecto, estos gráficos dinámicos sufren de enanismo agudo al cambiarlos de ubicación).

Para que la serie “Pct del Total” sea visible tendremos que definirla en un eje Y secundario. La forma más sencilla es usar la barra de herramientas de los gráficos para seleccionar la serie y el menú Formato para cambiar las definiciones

Asegurándonos que la series esté seleccionada cambiamos a “eje secundario” y el tipo de gráfico a “Línea”. Después de hacer otros cambios obtenemos