Tener una visión cercana y global a temas que le estamos haciendo seguimiento es algo que requerimos constantemente. También es importante y útil comenzar a utilizar funciones de MS Excel ® como SI.ERROR o IFERROR , MAX, SI o IF y BUSCARV o VLOOKUP. indicando su escritura en español e inglés respectivamente.
En el siguiente apartado, se explicará en detalle:
1. Cómo de unos datos generales, tomar solo los últimos datos de forma automática y sin programación VBA®
3. Se realizará una gráfica de llenado de tanque o termómetro.
4. Al final se tiene un enlace donde se podrá bajar el archivo en MS Excel ® con el ejercicio realizado.
Es aconsejable tener conocimiento haciendo gráficas y saber como cambiarles el formato.
El resultado se muestra en la Figura Nro. 1.
En el siguiente apartado, se explicará en detalle:
1. Cómo de unos datos generales, tomar solo los últimos datos de forma automática y sin programación VBA®
3. Se realizará una gráfica de llenado de tanque o termómetro.
4. Al final se tiene un enlace donde se podrá bajar el archivo en MS Excel ® con el ejercicio realizado.
Es aconsejable tener conocimiento haciendo gráficas y saber como cambiarles el formato.
El resultado se muestra en la Figura Nro. 1.
 |
| Figura Nro. 1 Informe para el Jefe |
Desarrollo.
Para hacer seguimiento es común necesitar una vista gráfica global de cómo vamos con las tareas y una vista gráfica de la evolución en el último periodo. Cuando estamos haciendo seguimiento a tareas o a un proyecto es prácticamente inevitable ingresar datos como “Fecha” (“Date”) que es la fecha en la cual se hace seguimiento a las actividades y “Plan” (“Plan”) que es el porcentaje de avance que debería ir según lo planeado. Ahora con el fin de comparar lo planeado con lo que realmente llevamos, normalmente utilizamos una columna de información “Real” (“Actual”) que es el porcentaje de avance real del seguimiento o proyecto.
Ahora queremos ver gráficamente como ha sido el avance y compararlo con la realidad, probablemente para hacer ajustes. Entonces, hacemos dos gráficas, una que tiene toda la información y otra que nos muestra el detalle el cual puede ser el último periodo , ú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 que queremos ver en detalle. Ésto es lo que automatizaremos y en una vista podemos ver el detalle y la gráfica con toda la información. Adicionalmente veremos como hacer una gráfica de termómetro o de llenado de tanque.
Para hacer seguimiento es común necesitar una vista gráfica global de cómo vamos con las tareas y una vista gráfica de la evolución en el último periodo. Cuando estamos haciendo seguimiento a tareas o a un proyecto es prácticamente inevitable ingresar datos como “Fecha” (“Date”) que es la fecha en la cual se hace seguimiento a las actividades y “Plan” (“Plan”) que es el porcentaje de avance que debería ir según lo planeado. Ahora con el fin de comparar lo planeado con lo que realmente llevamos, normalmente utilizamos una columna de información “Real” (“Actual”) que es el porcentaje de avance real del seguimiento o proyecto.
Ahora queremos ver gráficamente como ha sido el avance y compararlo con la realidad, probablemente para hacer ajustes. Entonces, hacemos dos gráficas, una que tiene toda la información y otra que nos muestra el detalle el cual puede ser el último periodo , ú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 que queremos ver en detalle. Ésto es lo que automatizaremos y en una vista podemos ver el detalle y la gráfica con toda la información. Adicionalmente veremos como hacer una gráfica de termómetro o de llenado de tanque.
Arriba en la figura Nro. 1 se se muestra la tabla con el detalle de la informaición y tres gráficas resultado:
- Tanque o termómetro que se va llenando
- Toda la información.
- Último periodo que para el ejemplo son los últimos tres meses.
Cómo extraer la información del último periodo:
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 Nro. 2 Encabezados
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 Nro. 2 Encabezados
 |
Figura Nro. 2. Encabezados.
|
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,"")”
Para 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. 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 para una mejor visibilidad.
 |
| Figura Nro. 3. Hoja de datos. |
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 para una mejor visibilidad.
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 4 Fecha Máxima celda en amarillo).
Nota: Si deseas mas información sobre el uso de éstas funciones, dar click en los Hipervínculos “MAX” y “Y o AND” para MS EXCEL ® en inglés.
5. Como el informe está semanal, simplemente debemos restar 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
Nota: Si deseas mas información sobre el uso de éstas funciones, dar click en los Hipervínculos “MAX” y “Y o AND” para MS EXCEL ® en inglés.
5. Como el informe está semanal, simplemente debemos restar 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
“=SI.ERROR(BUSCARV(B17-7,FollowingPlan!$B$1:$B$200,1,0),"")”
MS EXCEL ® en Ingles
“=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.
Nota: Si deseas mas información sobre el uso de éstas funciones, dar click en los Hipervínculos “SI.ERROR o IFERROR”, “BUSCARV o VLOOKUP”.
6. 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
6. 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
“=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.
7. Arrastramos la formula hasta la C17 y nos trae de la hoja de datos los % acumulado según el plan.
8. 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.
9. Arrastramos la formula hasta la D17 y nos trae los % acumulado según lo real o actual.
10. Se hace la gráfica sobre ésta tabla calculada (B2:D17) y se tiene la información resiente.
9. Arrastramos la formula hasta la D17 y nos trae los % acumulado según lo real o actual.
 |
| Figura Nro. 4. Formula para traer porcentaje |
10. Se hace la gráfica sobre ésta tabla calculada (B2:D17) y se tiene la información resiente.
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”
j. Superposición de series se lleva al 100% y ancho del intervalo al 220% aceptar o OK.
Luego ya es jugar con el relleno y con los labels o leyenda.
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”
j. Superposición de series se lleva al 100% y ancho del intervalo al 220% aceptar o OK.
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 objetos y valores reales con Excel 2007 y 2010
- Bussiness Dashboard in Excel for beginners – A look at Bar, column, line and bullet charts
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.
Puedes bajar el archivo con el resultado en el siguiente enlace:
https://drive.google.com/file/d/0BzizAEkcinZ5aHZGNW9oZC00Smc/view?usp=sharing
No hay comentarios.:
Publicar un comentario
Gracias por participar en esta página. (Thank you for you participation on this page)