Dashboard para seguimiento de Proyectos en excel + Descarga gratis

Realizar seguimiento a nuestros proyectos, es una parte fundamental para asegurar su éxito; es por ello que es imprescindible contar con alguna herramienta que nos permita visualizar de forma sencilla y rápida los principales indicadores de programación y avance. En ese contexto, continuando con nuestra serie de entradas sobre gestión de proyectos, te explicaremos cómo elaborar un dashboard para seguimiento de proyectos en excel, que incluye un diagrama de Gantt e información adicional, que te ayudará revisar más a detalle cada una de sus actividades.

 

 

Antes de iniciar, te recordamos que puedes revisar todas nuestras sobre gestión de proyectos en nuestra sección de Producción y Operaciones de nuestro blog.

Así mismo, para hacer seguimiento a tus proyectos en excel, también puedes visitar nuestra sección de plantillas de diagramas de Gantt.

El resultado final que obtendremos será el siguiente:

 

 

En la imagen podemos ver que nuestro tablero está filtrado para las actividades asignadas al “Líder 1”. Podemos hacer filtros por fecha de inicio, fecha final, responsable y actividad

 

Ejemplo

 

En la siguiente imagen presentamos los datos a utilizar para la explicación:

 

 

A continuación detallaremos los componentes:

• Actividad: Indica las actividades que componen el proyecto.
• Fecha de Inicio: Fecha donde inicia la actividad.
• Duración: Días necesarios para realizar la actividad.
• Fecha de finalización: Fecha donde termina la actividad. Se obtiene en función de la fecha de inicio y duración de la actividad con la siguiente fórmula: “FECHA DE INICIO+DURACIÓN-1”.
• %Completado a hoy: Indica el porcentaje de avance real a la fecha actual.
• Asignado a: Corresponde a la persona asignada para liderar la actividad.
• Presupuesto Programado: Presupuesto que se estimó para la actividad antes de iniciarla.
• Presupuesto Ejecutado: Indica la ejecución presupuestal de la actividad en la fecha actual.
• Avance en días (según %): Esta celda nos servirá para elaborar nuestro dashboard y representa el equivalente en días del porcentaje de avance real. Se obtiene mediante la multiplicación de: “DURACIÓN*%COMPLETADO A HOY”.
• Avance en días (según fecha actual): Esta celda nos servirá para elaborar nuestro dashboard y representa el número de días que han transcurrido desde que inició la actividad hasta la fecha actual. Se calcula con la siguiente fórmula: “=SI((FECHA ACTUAL-FECHA DE INICIO+1)<=0;0;FECHA ACTUAL-FECHA DE INICIO+1).  La interpretación de la fórmula sería, mientras la actividad no inicie, este valor será cero, si y inició, devolverá los días transcurridos desde que inició la actividad hasta la fecha actual.

También tenemos dos celdas para indicar el nombre de nuestro proyecto (B1) y la fecha actual (B2).

 

Creación del Dashboard:

 

Para elaborar nuestro dashboard de seguimiento de proyectos en excel, crearemos 5 tablas dinámicas. Antes de iniciar, vamos a definir nuestros datos como una tabla; para ello realizaremos lo siguiente:

• Seleccionaremos cualquiera de las celdas donde se encuentren los datos de nuestro proyecto (cualquier celda entre A5 y J18).
• Presionaremos Ctrl+T.
• Nos aparecerá una nueva ventana, verificamos que el check de la tabla tiene encabezados esté marcado y damos click en aceptar.
• Nuestros datos estarán con un nuevo formato de colores, lo que indica que ya se generó nuestra tabla.

 

 

Ahora procederemos a explicar cómo crear nuestras tablas y gráficos dinámicos para nuestro dashboard.

Nota: Trataremos de explicar los pasos, considerando que ya tienen nociones básicas sobre la creación de tablas y gráficos dinámicos. Si deseas aprender más sobre estos temas, te recomendamos revisar los tutoriales de la web de Microsoft: Tabla Dinámica y Gráfico Dinámico.

 

Tabla Dinámica 1 – Diagrama de Gantt:

 

En primer lugar, crearemos nuestro gráfico dinámico de Gantt, para ello seleccionaremos cualquier celda de nuestra tabla y daremos click en insertar, luego en insertar tabla dinámica:

 

 

Seleccionamos los datos según la imagen mostrada. Luego, en los campos de la tabla seleccionaremos lo siguiente:

 

 

Ahora, agregaremos un campo calculado en la sección Analizar, luego en Campos,elementos y conjuntos y finalmente en campo calculado, según la siguiente imagen:

 

 

Con los datos de esta tabla, crearemos nuestra gráfica dinámica de Gantt insertando un gráfico de barras apilada. Obtendremos el siguiente resultado inicial:

 

 

Luego quitamos los botones del gráfico: pestaña Analizar, luego en Botones de Campo y finalmente en Ocultar todos:

 

 

Los siguientes pasos consisten en dar formato al gráfico como quitar el relleno de la barra azul, darle color a las otras barras a tu gusto y  colocar las categorías en orden inversa. Esos pasos están detallados en nuestro primer tutorial sobre diagramas de Gantt.

Finalmente, crearemos una nueva hoja donde colocaremos nuestro diagrama. Cortamos el diagrama creado y lo pegamos en esta nueva hoja.

 

Tabla dinámica 2 – Tarjetas del Dashboard:

 

En la parte superior de nuestro dashboard tenemos tres tarjetas. La primera indica la fecha de inicio de todo el proyecto, la segunda la duración total y la tercera la fecha final del proyecto. Estas tarjetas se actualizan conforme actualizamos los filtros.

Para elaborar estas tarjetas, crearemos una nueva tabla dinámica, en la misma hoja donde se creó la tabla anterior, con los siguientes datos:

 

 

Dos celdas debajo, buscaremos obtener el valor mínimo de las fechas de inicio y el valor máximo de las fechas de fin. Luego obtendremos la duración con esos dos valores. Las fórmulas a utilizar serán las siguientes:

 

 

En nuestra nueva hoja, donde ya tenemos nuestro diagrama de Gantt, crearemos los cuadros de texto que hagan referencia a las celdas anteriores. Así mismo, dentro de una forma cuadrada colocaremos los encabezados:

 

 

Repetimos el proceso para las otras dos tarjetas.

Más información sobre cómo referenciar celdas en cuadros de texto la encontrarás aquí.

 

Tabla dinámica 3 – Evaluación de Presupuesto:

 

Crearemos una nueva tabla dinámica para evaluar el presupuesto con los siguientes datos:

 

 

Con estos datos, crearemos un gráfico de columnas y lo copiaremos en nuestra hoja de gráficos:

 

 

Más abajo, utilizaremos 2 celdas para hacer referencia a los resultados de la tabla dinámica. Con estas nuevas celdas realizaremos la evaluación si el presupuesto es normal o si se excedió:

 

 

Nota: Puedes aplicar la fórmula para evaluar el presupuesto directamente a los resultados de la tabla dinámica.

A la celda de evaluación, le aplicaremos formato condicional de la siguiente forma:

 

 

Nuestra celda quedaría así:

 

 

Para agregar esta celda en nuestra hoja de gráficos, copiaremos la celda y la pegaremos como imagen vinculada con la siguiente opción:

 

 

Tabla Dinámica 4 – Porcentaje de Avance:

 

Crearemos una nueva tabla dinámica con los siguientes datos:

 

 

Nuevamente utilizaremos dos celdas para generar el porcentaje de avance total, utilizando los resultados de la tabla dinámica:

 

 

Con los porcentajes obtenidos, realizaremos nuestro gráfico de anillos. Posteriormente le dimos un formato especial para hacerlo más vistoso:

 

 

Puedes aprender cómo darle ese formato aquí.

 

Tabla Dinámica 5 – Comparación de Avance:

 

Nuestra última tabla dinámica será la siguiente:

 

 

Como en la tabla 3, referenciamos esos valores a dos celdas, y con estas celdas, utilizamos la fórmula “si conjunto” para analizar los datos:

 

 

Lo que estamos comparando son los valores del avance esperado según la fecha actual, con el avance real. Si el avance esperado es mayor que el avance real, quiere decir que la actividad está “Retrasada”; si se da al contrario, la actividad está “Adelantada”. Además, si la actividad ya se completó, el resultado será “Finalizada”. También hemos considerado, que si  la diferencia entre el avance esperado y el avance real es menor al 10% del avance esperado, la actividad estará “A tiempo”.

En esta celda también aplicaremos formato condicional para cambiar el color según el resultado:

 

 

Finalmente pegamos la celda en nuestra hoja de gráficos como imagen vinculada.

 

Agregando los filtros:

 

Una vez ordenados nuestros gráficos en la hoja, vamos a agregar los filtro con la segmentación de datos y la escala de tiempo:

 

 

En la escala de tiempo seleccionaremos las dos opciones disponibles y en la segmentación de datos solamente “Actividad” y “Asignado a”:

 

 

 

Estos filtros deben enlazarse a nuestras tablas dinámicas, para lo cual seleccionamos el filtro, luego en la pestaña Opciones, damos click en Conexiones de Informes. Ahí seleccionamos las 5 tablas dinámicas creadas. 

 

 

Repetimos el proceso para los otros filtros.

Finalmente, colocamos los filtros alrededor de nuestros gráficos y lo coloreamos a nuestro gusto.

Con todo lo visto anteriormente, tendríamos el siguiente resultado final:

 

 

Puedes descargar el archivo gratis en el siguiente link:

Reflexión Final

 

No cabe duda que nuestro Dashboard para seguimiento de Proyectos en excel, logra mostrar los principales indicadores que te ayudarán a controlar de forma más fácil el avance de tu proyecto; así también, nos demuestra que excel, a pesar de no ser una herramienta especializada en la gestión de proyectos, nos puede ser de mucha utilidad. No te pierdas nuestro próximo y último tutorial sobre proyectos en excel, donde te enseñaremos como elaborar un diagrama de Gantt nivel avanzado.

Si tienes alguna duda o recomendación, o simplemente dejar tu agradecimiento; puedes realizarlo en la sección de comentarios. Además te invitamos a seguirnos en Facebook y suscribirte a nuestro canal de Youtube. Somos Plan de Mejora, el mejor lugar para aprender, crecer y mejorar.