Primer Truco. Gráfica global y Gráfica del último periodo que se actualiza automáticamente.

Se verá cómo utilizar  las funciones SI.ERROR o IFERROR (Versión de MS EXCEL en inglés), MAX, SI o IF y BUSCARV o VLOOKUP. Se debe tener conocimiento haciendo gráficas y cambiarles el formato.

Se anexa el resultado de lo que queremos hacer para presentar a nuestro jefe:

Figura 1. Informe

En gerencia de proyectos es común necesitar una vista gráfica global de cómo va el proyecto y una vista gráfica de la evolución en el último periodo. Los datos que normalmente se necesitan son “Fecha” (“Date”), “Plan” (“Plan”) que es el porcentaje de avance que debería ir según el plan, y “Real” (“Actual”) que es el porcentaje de avance real del proyecto. Lo que normalmente se hace, son dos gráficas, una donde se selecciona toda la tabla y otra donde se selecciona el último periodo que puede ser, último mes, último trimestre, etc. Pero cada vez que se entre información en la tabla, se requiere modificar manualmente la gráfica para que tome el último periodo.

En la figura 1 se encuentra la tabla con la información y tres gráficas:

1. 1.       Tanque que se va llenando
2. 2.      La que muestra la información de todo el proyecto
3. 3.      La que queremos automatizar que solo muestra el último periodo que para el ejemplo son los últimos tres meses.

Parte uno. Conseguir los datos del último periodo automáticamente.

Para automatizar la gráfica tres se procede de la siguiente forma:

1. 1.       En la misma hoja o en otra hoja que llamaremos hoja de cálculos se crea otra lista con los encabezados “Date” “Plan” y “Actual”. Por facilidad y orden, el ejercicio se realizará utilizando otra hoja donde colocamos los encabezados en las celdas B2 a D2. Ver figura 2.

Figure 2 Resultado de Cálculos para Automatizar la gráfica

1. 2.      Ahora debemos calcular la mayor fecha que tiene datos. Para ello verificamos cuales  celdas de las columnas C y D de la hoja datos tienen datos. Para el ejemplo la formula sería: MS EXCEL en Español “=SI(Y(FollowingPlan!C2<>"",FollowingPlan!D2<>""),FollowingPlan!B2,"")”  
2. MS EXCEL en Ingles “=IF(AND(FollowingPlan!C2<>"",FollowingPlan!D2<>""),FollowingPlan!B2,"")” y se colocaría en la celda F3 de la hoja de cálculos. La fórmula verifica que ninguna de las dos celdas estén vacías.
3. 3.      La fórmula se copia en la columna F hasta un valor que incluya todos los valores de la hoja de datos.  En el ejemplo se incluyó hasta la fila 200 (F200) y se marca en gris por facilidad.
4. 4.      Ahora en la columna B, bajar 15 espacios luego del encabezado (pues se quiere ver las 15 entradas anteriores) y en esa celda coloco la formula “=MAX(F3:F200)”  en nuestro caso sería la celda B17 (Ver Figure 2 Resultado de Cálculos para Automatizar la gráfica celda en amarillo). Dar click para tener explicación del funcionamiento de la función “MAX” y “Y o AND” para MS EXCEL en inglés.
5. 5.      Como el informe está semanal, simplemente debemos quitar 7 días a esta fecha y copiar hacia arriba hasta la celda B3, pero es importante que coloque en vacío si la fecha no existe, pues al principio solo se tendrán pocas entradas, entonces la formula a colocar es: MS EXCEL en Español
6. “=SI.ERROR(BUSCARV(B17-7,FollowingPlan!$B$1:$B$200,1,0),"")”
7. MS EXCEL en Ingles
8. “=IFERROR(VLOOKUP(B17-7,FollowingPlan!$B$1:$B$200,1,0),"")”.  Donde B17-7 es la fecha anterior y la busca en la tabla de datos, si no la encuentra coloca vacío.
9. Dar click para tener explicación de la función  “SI.ERROR o IFERROR”, “BUSCARV o VLOOKUP”
10. 6.      La Fórmula se copia hasta la celda B3.
11. 7.      Ahora pasamos a la  Celda C3 de nuestra hoja de cálculos para buscar el dato del porcentaje en la tabla de la hoja de seguimiento.  Entonces colocamos la fórmula MS EXCEL en Español “=SI(B3="","",(B3,FollowingPlan!$B$1:$C$200,2,0))”  o
12. “=IF(B3="","",VLOOKUP(B3,FollowingPlan!$B$1:$C$200,2,0))” si es MS EXCEL en Ingles.  Dar click para tener explicación de la función SI o IF.
13. 8.      Arrastramos la formula hasta la C17 y nos trae de la hoja de datos los % acumulado según el plan.
14. 9.      Hacemos lo mismo en la celda D3 colocando la fórmula MS EXCEL en Español “=SI(B3="","",BUSCARV(B3,FollowingPlan!$B$1:$D$200,3,0))”  o  “=IF(B3="","",VLOOKUP(B3,FollowingPlan!$B$1:$D$200,3,0))”  si es MS EXCEL en Ingles.
15. 10.   Arrastramos la formula hasta la D17 y nos trae los % acumulado según lo real o actual.
16. 11.    Se hace la gráfica sobre ésta tabla calculada y se tiene la información resiente. 
17. 12.   Para probar pueden borrar algunos datos en la hoja de datos.

Parte dos. Realización de las gráficas.

Gráfica de Columna que se va llenando

1. En la hoja de datos en la celda G22 se coloca el máximo valor del porcentaje del plan, lo cual se logra con la fórmula “=MAX(C2:C35)” y en la celda G23 se coloca el máximo valor del porcentaje actual, lo cual se logra con la fórmula “=MAX(D2:D35)”.
2. Se inserta una gráfica de 2D Gráfico en Columnas (Clustered Column) en MS EXCEL en inglés. Y se adicionan los datos:

a.      Click derecho del mouse y seleccionar “Select Data”

b.      Se da click en el botón para adicionar Serie.

c.       Se selecciona la celda C1 como el nombre de la serie y G22 como el dato. Aceptar

d.      Se da click en el botón para adicionar otra Serie.

e.      Se selecciona la celda D1 como el nombre de la serie y G23 como el dato. Aceptar

f.        Luego click para editar la parte Horizontal y se entra la información “% de Avance”

g.      OK y OK para salir.

h.      Se da click derecho sobre la serie dos y se selecciona “Dar formato a serie de datos”

i.        Superposición de series se lleva al 100% y ancho del intervalo al 220%  aceptar o OK.

j.        Luego ya es jugar con el relleno y con los labels o leyenda.

Más detalle en los Links:

           

·       Cómo hacer un gráfico de termómetro para comparar objetivos y valores reales con Excel 2007 y 2010

·         Business Dashboards in excel for beginners - A look at Bar, column, line and bullet charts

Gráfica de línea y Área.

1. Lo primero es seleccionar los datos que están en la hoja de datos desde B1 hasta D35.
2. Se selecciona la Gráfica 2D Línea (Line). Y se crea la gráfica automáticamente.
3. Se selecciona la serie que será Área, click derecho y se selecciona cambiar el tipo de gráfica.
4. Para MS EXCEL 2013 se selecciona en la parte de abajo la serie y se indica el nuevo tipo de gráfica que es en Área.
5. Se juega con formato, relleno, gradiente, leyendas.
6. Igual se hace para la gráfica de últimos avances.