Generar Agenda de Tareas (Gráficos Gantt)

Durante esta última semana he tenido varias consultas relativas a la generación de gráficos para el control de agendas de proyectos, o lo que es lo mismo, gráficos Gantt. Excel no presenta este tipo de gráficos por defecto pero resulta bastante sencillo conseguirlo siguiendo unos pasos...

Partimos del siguiente ejemplo donde podemos ver en la columna B la tarea; en la columna C la fecha de inicio de dicha tarea; y en la columna D la duración en días desde la fecha de inicio de cada tarea:

1. Seleccionamos el rango B2:D8 y procedemos a insertar gráfico tipo barras horizontales; subtipo barras apiladas:

2. Pulsamos directamente Finalizar. El gráfico obtenido será el siguiente:

3. Ya tenemos la base para empezar a trabajar... Lo que debemos hacer a continuación es un clic con el botón derecho del ratón sobre el eje de las Y (eje vertical) y seleccionar Formato de ejes...

4. En la pestaña Escala marcamos la opción Categorías en orden inverso. De esta manera excel colocará las tareas en el orden correcto (no como las presentaba en el gráfico anterior) y las fechas en la parte superior del gráfico. Hacemos clic encima de la leyenda y suprimimos:

5. Hacemos doble clic encima de la serie de datos de color azul, que se refiere a las fechas de inicio, y le quitamos el relleno y el borde:

6. Hacemos doble clic encima de las fechas que se encuentran en la parte superior del gráfico, para darles formato, y seleccionamos en la ventana que se abre la pestaña Escala. Excel no reconoce aquí los formatos de fecha, por lo que debe utilizar el formato general. Para hacer esto debe volver a la hoja y situarse, por ejemplo en la celda F3. En esta celda copie la fecha inicial de la primera tarea y vaya a Formato/Celda/Número y seleccione la opción General. Póngase en F4 y copie la fecha de inicio del último proyecto más el número de días de duración (en nuestro ejemplo la última fecha es el 7/8/2010 y la duración de esta última tarea es de 7 días, por lo que la fecha final será el 14/8/2010) y haga lo mismo que en el anterior caso. De esta manera obtendremos los números de serie que corresponden a estas dos fechas que nos interesan (en concreto el 40369 y el 40404 ¿Para qué? Pues volviendo a nuestra ventana de Formato de ejes en la pestaña Escala debemos escribir como Mínimo el 40369 (que es la fecha de inicio de la primera tarea) y como Máximo el 40404 (que es la fecha de finalización de la última tarea):

Y con algunas mejoras de formato (color de fuentes, color de lineas de división, etc) conseguimos un sencillo gráfico Gantt como el mostrado a continuación:

Evitar Valores Cero y Blancos en un Gráfico

"Tengo que realizar un gráfico y tengo algunas celdas que contienen el valor cero y otras están en blanco ¿es posible que no aparezcan dichos valores en el gráfico?"

Sí es posible. Lo solucionaremos por medio de la función NOD. El problema planteado es el siguiente:

Como se puede comprobar existen dos celdas con el valor cero y una celda en blanco. Si realizamos un gráfico de esta tabla el resultado obtenido será:

Lo que pretendemos conseguir es que excel "ignore" estos valores y realice una gráfica continua (interpole). Para ello hacemos lo siguiente:

1. Nos situamos en la celda D3 y escribimos la siguiente fórmula:

=SI(O(C3=0;C3="");NOD();C3)

La función NOD() no tiene argumentos. Simplemente devuelve el error #N/A. Aunque pueda parecer inútil, será precisamente la solución a nuestro problema, ya que excel interpola automáticamente este tipo de valor en los gráficos.

2. Copiamos la fórmula de D3 hasta D14:

3. Realizamos nuestro gráfico utilizando los rangos B3:B14 y D3:D14. El resultado obtenido será el deseado:

Datos Dinámicos en los Títulos de Gráficos

"Tengo un gráfico con las ventas de cada mes y me gustaría añadir como título del gráfico la cifra de ventas acumulada a final de año (pero no como texto fijo)".

Sin problema... Supongamos que partimos del siguiente ejemplo:

Y realizamos el siguiente gráfico de columnas (gráfico completamente básico al que simplemente le he cambiado los colores predeterminados y borrado el título y leyenda):

Lo que queremos conseguir es que por un lado aparezca un título fijo con el texto "Ventas Mensuales" y, por otro lado, que aparezca un título dinámico del tipo "Ventas Anuales Acumuladas" y a continuación la cifra en cuestión (lógicamente esta cifra debe ser dinámica, es decir, que si realizamos cualquier cambio en las ventas mensuales que también cambie dicho total). Para conseguir este objetivo debemos seguir los siguientes pasos:

1. Pulsamos el botón derecho del ratón encima del área de trazado del gráfico y seleccionamos Opciones de gráfico. En el apartado título del gráfico escribimos "Ventas Mensuales" y aceptamos.

2. Nos situamos en la celda B17 y escribimos la siguiente fórmula:

="Ventas Acumuladas: "&TEXTO(C15;"#.###")

3. Hacemos un clic encima del área de trazado de nuestro gráfico y a continuación escribimos un igual en nuestra barra de fórmulas y hacemos clic en B17. Pulsamos enter.

De esta manera aparecerá en el medio del gráfico el texto Ventas Acumuladas seguido de la suma total de nuestras ventas. Sólo nos resta colocar dicho cuadro en la parte del gráfico que deseemos:

Pruebe a modificar las cifra de ventas de cualquier mes y comprobará que la cifra total acumulada mostrada en el gráfico también cambiará.

Distintos Colores en Valores de una Serie de Datos de un Gráfico

"Me interesaría dibujar un gráfico que muestre los valores de la serie y que dichos valores tomen distintos colores en función de que sean mayores o menores que cero".

La solución es bastante sencilla utilizando el formato personalizado de número. Lo que deseamos obtener es lo siguiente:

Como se puede observar en e gráfico, cuando los valores son positivos los muestra en color azul y en color rojo cuando éstos son negativos. Lo mismo ocurre con los valores del eje Y.

Para conseguir este resultado debemos seguir los siguientes pasos (comienzo mostrando los datos del ejemplo):

1. Seleccionamos el rango A2:B13 y pulsamos el icono de insertar gráfico. Seleccionamos el tipo que queramos utilizar (en nuestro ejemplo tipo lineas y subtipo lineas con marcador en cada valor) y pulsamos finalizar. El resultado será:

2. Hacemos doble clic encima de la serie de datos; seleccionamos la pestaña Rótulos de datos; activamos Valor y aceptamos.

3. Tras eliminar las lineas de división principales, cambiar el color de la linea y añadir título (cuestiones básicas que entiendo no necesitan ser explicadas) el gráfico quedaría así:

4. Hacemos doble clic encima de los valores de la serie.

5. Seleccionamos la pestaña Número, marcamos la opción Personalizada y dentro del cuadro Tipo escribimos el siguiente formato:

[Rojo][<0]-#.###;[azul][>0]#.###;Estándar

6. Tras aceptar obtendremos el resultado deseado (hemos realizado la misma operación en el formato de número del eje Y):

Modificando Datos desde los Gráficos

Descargar el Archivo

Aviso para navegantes: el contenido de este artículo es aplicable sólo en versiones anteriores a Excel 2007. Seguro que Microsoft tendrá una explicación pintoresca para explicar por qué han omitido está funcionalidad...

Cualquier usuario "respetable" de Excel sabe que cuando generamos un gráfico y posteriormente modificamos los datos de los que depende dicho gráfico éste se actualiza automáticamente. Pero pocos, muy pocos, usuarios saben que esta misma labor la podemos realizar directamente desde los gráficos. Dicho de otra forma: al igual que podemos modificar los datos de entrada de un modelo para ver qué resultado obtenemos, podemos realizar la misma tarea directamente desde los gráficos sin necesidad de modificar celda alguna. Supongamos que tenemos la siguiente cuenta de resultados provisional con el desarrollo de fórmulas que se muestra en la segunda figura (las celdas en naranja son las fórmulas que hay que crear y que se pueden copiar):

Realizamos ahora un gráfico de columnas en 2D del, por ejemplo, beneficio bruto:

El gráfico creado es un gráfico de "salida de datos", ya que el origen de sus datos son fórmulas que calculan el beneficio bruto estimado para cuatro años. A continuación vamos a crear dos gráficos de entrada de datos. Por un lado un gráfico que contemple el porcentaje de Incremento de Ventas y el porcentaje de Coste Directo y, por otro lado, otro gráfico que contemple las Ventas del Año1 y los Gastos Fijos :

(Realizamos dos gráficos de entrada de datos por no complicarlo innecesariamente teniendo que crear un gráfico en dos ejes, ya que manejamos cifras en euros y porcentajes)

Si ahora quisiéramos comprobar cómo quedaría el beneficio bruto si modificáramos, por ejemplo, los Gastos Fijos, el 95% de usuarios irían a la celda que contiene dicho dato (los Gastos Fijos) y procederían a cambiar la cifra. Pero debe saber que esto puede hacerlo directamente desde el gráfico de entrada de datos. Para ello sólo tiene que hacer lo siguiente:

1. Haga un clic encima de la columna de Gastos Fijos en el gráfico. Le aparecerá seleccionada la serie, es decir, tanto la columna de ventas como la de gastos fijos. Para seleccionar sólo la de Gastos Fijos vuelva a hacer otro clic encima de dicha columna. Quedará de la siguiente forma:

2. Ahora estire el rectángulo hacia arriba hasta alcanzar la cifra deseada que se irá mostrando en un pequeño recuadro amarillo. Pruebe con estirar hasta los 2.250€ y suelte:

Al soltar el gráfico del beneficio bruto se habrá actualizado automáticamente para los nuevos valores. 

Si vuelve a la hoja y comprueba la celda de entrada de datos correspondiente a los Gastos Fijos verá que también ha cambiado tomando el valor 2.250€.

Modificar el gráfico predeterminado

Para modificar el gráfico predeterminado de Excel debemos seguir los siguientes pasos:

1. Creamos el gráfico que queramos establecer posteriormente como predeterminado. Para ello modificamos a nuestro gusto el tipo de gráfico, los colores, formatos, etcétera que estimemos oportuno (por ejemplo como el que se ve en la imagen).

2. Ponemos el puntero encima de este gráfico y pulsando el botón derecho del ratón seleccionamos del menú emergente la opción Tipo de Gráfico.

3. Automáticamente se abrirá la ventana mostrada a continuación, donde debemos seleccionar la pestaña Tipos Personalizados

4. Al pie de esta ventana existe la opción Establecer como predeterminado. Al pulsar este botón nos pedirá confirmación y pulsaremos Sí, con lo que aparecerá la siguiente ventana:

5. Tras darle un nombre y una descripción, si lo deseamos, pulsamos aceptar y ya está.

Añadir datos a un gráfico existente

Para añadir datos a un gráfico que ya tenemos creado tan sólo tenemos que "arrastrar" dichos nuevos datos dentro del gráfico (para versiones Excel 97-2003).

Basándonos en el ejemplo mostrado en la figura, tenemos representadas las ventas de la delegación de La Coruña. Si queremos agregar las ventas de la delegación de Orense tendremos que:

1. Seleccionar el rango C5:C17

2. Con el rango seleccionado nos colocamos encima del contorno de dicho rango (como si fuesemos a desplazar el rango a otra parte de la hoja), hacemos clic con el botón izquierdo del ratón y "arrastramos" dicho rango dentro del gráfico.

En la versión 2007 no podemos utilizar este sencillo y práctico método (quizás por esto lo han quitado...) pero podemos hacerlo de una manera alternativa que también resulta bastante sencilla. A saber: Seleccionamos el rango C5:C17 y pulsamos Copiar. Nos situamos dentro del gráfico y pulsando el botón derecho del ratón seleccionamos Pegar.

Gráfico instantáneo

Generar un gráfico de manera instantánea en Excel (versiones 2000-2007) es tan sencillo como seleccionar los datos que queremos representar y, una vez seleccionado dicho rango, pulsar la tecla F11. Veámoslo con un ejemplo.

Supongamos que en el rango A5:A17 tenemos los meses de un año y en el rango B5:B17 las cifras de ventas correspondientes a dichos meses. Para proceder a realizar un gráfico:

1. Seleccionamos desde A5 hasta B17

2. Pulsamos la tecla F11

Automáticamente Excel inserta una nueva hoja en nuestro libro con el gráfico de los datos seleccionados...