Planificación de proyectos simple con diagrama de Gantt en Excel

Para proyectos de cierta envergadura se requiere de una herramienta potente de planificación de proyectos, pero para pequeños proyectos existen alternativas al alcance de cualquier usuario. Sin ir más lejos, es posible utilizar listados de tareas en Excel y crear un diagrama de Gantt para mostrar la secuencia temporal.

Ahora que estamos cercanos a la época de planificación presupuestaria, vamos a basar nuestro truco en un ejemplo simple que representará la planificación de tareas para la confección del presupuesto del próximo año. 

1. Listar las tareas de proyecto

En primer lugar, listaremos las tareas en una pestaña Excel. Para la primera tarea introduciremos la fecha inicial en la columna B, y los días estimados para cada tarea en la columna C.

La columna B la completaremos con la función DIA.LAB, que nos servirá para calcular la fecha inicial de cada tarea en función de la fecha inicial de la tarea anterior, más los días estimados de duración de la tarea anterior, considerando un listado de días festivos que generaremos en una pestaña de trabajo auxiliar. 

=DIA.LAB(B2;C2;festivos)

2. Crear el diagrama de Gantt 

Una vez tenemos nuestra lista de tareas detallada, empezaremos a crear un diagrama de Gantt. Para ello, seleccionaremos las celdas A1:C8 e insertaremos un gráfico de barras apiladas.

Seguidamente, eliminaremos el color de relleno de la serie Fecha inicial, cambiaremos el color de la serie duración del rojo al azul y borraremos la leyenda para ir convirtiendo el gráfico en un diagrama de Gantt.

A continuación, reordenaremos las tareas cambiando el formato del eje vertical como se muestra en la imagen de ejemplo. 

La fecha inicial del eje horizontal está calculada de forma automática, de modo que tendremos que ajustarla a las fechas de nuestro plan de proyecto. Al mismo tiempo, modificaremos las opciones de unidad mayor y menor del gráfico para disponer de referencias temporales semanal y diaria.

Para hacer las barras más altas, ajustaremos el ancho de las series tal y como se muestra en la imagen.

A continuación, añadiremos líneas de división secundarias para incorporar la referencia diaria y ajustaremos el formato de ambas líneas de división (semanal y diaria) para dar una referencia temporal.

Con el fin de mejorar el efecto visual de las líneas secundarias lo disminuiremos y le daremos un estilo de línea discontinua, a través de la opción Formato de líneas de división.

Una vez hayamos completado las tareas de formato, el diagrama de Gantt nos mostrará la secuencia temporal de las tareas de proyecto.

 

Ordenar campos de tabla dinámica con orden personalizado

Si queremos ordenar un campo de tabla dinamica que no sea alfabéticamente podemos crear una lista personalizada con el orden correcto. Posteriormente podremos ordenar automáticamente el campo. 

A continuación describimos en detalle como podemos hacerlo:

1. En opciones Excel clicaremos en el botón Modificar listas personalizadas

Clic para ampliar imagen2. A continuación, como se muestra en la imagen, seleccionaremos el rango dónde están los valores posibles ordenados:

Clic para ampliar imagen3. Finalmente, ahora sí, ordenaremos el campo alfabéticamente tal y como se muestra en la siguiente imagen:

Clic para ampliar imagen

Gráfico personalizado para cada dato

Dentro de un gráfico de Excel podemos llegar a personalizar la imagen que representa cada uno de los datos. Los pasos a seguir son los siguientes:

1. Insertar todas las imágenes que queremos incluir en nuestra Hoja de Cálculo XLS (Insertar > Imagen > Desde archivo...).

2. Repetiremos los pasos que describimos a continuación para cada columna:

a) Seleccionar y copiar la imagen que nos interese (CTRL+c).

b) Seleccionar el tipo de gráfico y clicar en la columna que corresponda la imagen copiada. Seleccionar justo una columna.

c) Pegar la imagen copiada (CTRL+v).

3. Una vez realizado en todas las columnas, realizaremos lo siguiente:

a) Doble click en una columna (no la serie entera).

b) En el cuadro de dialogo [Formato de datos], seleccionar la ficha [patrones] y a continuación, hacer click en el botón: [Efectos de relleno].

  c) En el cuadro de diálogo [Efectos de relleno], seleccionar la ficha [Imagen]. Hacia la parte inferior izquierda hay tres opciones de formato: Estirar, apilar o aplicar y escalar a X unidades/ imagen.

Este es un ejemplo de resultado:

Comparar y trabajar con hojas de cálculo en la misma ventana

¿Tiene dos libros de Excel abiertos y necesita comparar datos?

Con los libros abiertos acceda al menú superior Ventana - Comparar en paralelo con... y seleccione el otro libro.

La ventana quedará tal como se muestra en la siguiente imagen. De esta manera se puede comparar fácilmente el contenido entre ficheros. Cuando se desplace el cursor hacia abajo en la hoja de la parte superior, también se desplazará la hoja inferior.

Gráficos de barras en la propia celda

A continuación se describen los pasos, a través de un ejemplo,  para hacer un gráfico de barras en una celda contigua a las celdas que contienen los datos.

1. Supongamos cinco filiales de una empresa con la cifra de ventas de cada una de ellas:

 

2. Crearemos una columna que nos indique el % de ventas sobre el total. Podemos llamar la columna  % sobre el total. Para obtener el resultado en tanto por ciento, se divide cada celda entre la suma de todas ellas y multiplicando por 100.

Para esto escribimos  en la celda C2 la siguiente fórmula =(B2/$B$7)*100 y arrastraremos hacia abajo:

 

3. En la cuarta columna, cambiaremos el formato de tamaño a 8 y en la celda D2 escribiremos la fórmula =REPETIR(“|”;C2). Arrastraremos hacia abajo para copiarla en el resto de celdas.

Como vemos, estos pasos han generado una barra en la celda contigua a la cifra de ventas muy útil para ver, de una forma más visual, la contribución en las ventas totales de cada una de las filiales. Las barras más largas representarán las filiales que han contribuido más en la cifra de ventas total y las más cortas las que menos. 

Cómo suavizar una gráfica de tipo linea

En el caso de gráficas de tipo línea, Excel nos permite suavizar los picos para mejorar el aspecto.

Para ello, basta que pongamos el mouse sobre la línea a suavizar, pulsar el botón derecho y seleccionar la opción de Formato de la Serie de Datos. En Excel 2003, marcar directamente la opción de suavizar la línea. En Excel 2007 (ver imagen), seleccionar “Estilo de línea” en el formulario que se abre, y marcar la opción de suavizar.

Escribir en varias hojas Excel a la vez

Para poner el mismo texto en muchas hojas de trabajo, una forma rápida de copiarlo a la vez es la siguiente:

1. Abrir en Excel un libro nuevo y vacío.

2. Pulsar la tecla Control y mientras se mantiene presionada hacer clic sobre las hojas en las que deben aparecer los mismos datos. Por ejemplo, sobre la Hoja1, 2 y 3.

3. A continuación, hacer clic en la celda A1 de la Hoja1 y escribir el texto.

4. Posteriormente, hacer clic en la Hoja2 para comprobar que el texto escrito en la Hoja1 también aparece en la celda A1 de la Hoja2 y de la Hoja3...

Categorización de fechas en tablas dinámicas en Excel

Dado un conjunto de datos (por ejemplo la siguiente tabla)

Podemos crear una tabla dinámica con ellos, de manera que en las filas ponemos la fecha y en los datos mostramos el importe (ver imagen)

Para crear y analizar los datos agrupando por años, meses, etc. debemos poner el cursor sobre el campo Date (Celda E2 en la imagen) y ejecutar la opción de menú: Datos à Agrupar y Esquema à Agrupar

Y en la ventana emergente seleccionar el tipo de agrupación, por ejemplo meses y trimestres

Automáticamente en la tabla dinámica se añadirán las dimensiones que hayamos creado

Pudiendo filtrar la información por cualquiera de dichos conceptos.

Gráfico de real vs objetivo con formatos condicionales

Supongamos un gráfico en el que se muestra un valor real comparado con un objetivo para cada período con formato de columnas, es decir, se tienen configuradas dos series (una para el valor real y otra para el objetivo), cada una con un color.

Lo que se pretende es poder colorear el valor real en base al cumplimiento (en verde si se supera o igual el objetivo) y en rojo si es menor.

Para ello se crearan dos columnas adicionales, de real, y creamos un gráfico con 3 series (las dos de real y el objetivo:

Ahora, sólo falta cambiar las propiedades de las series.

Las otras dos series se trazaran en el eje secundario (vigilar la escala de los dos ejes).

El gráfico final queda de la siguiente manera:

Cambiar el número de hojas por defecto al abrir

Cuando se abre un nuevo libro, Excel muestra 3 hojas por defecto. En la mayoría de casos sólo necesitamos una o dos, y, el resto las eliminamos. Para evitar esto, podemos indicar a Excel que sólo cree una hoja por defecto, y añadir más si las necesitamos. En Menu -> Herramientas --> Opciones -->General cambiar la opción “Número de hojas en nuevo libro” de 3 a 1.