lunes, 18 de mayo de 2015

Gráfico para mostrar Grado de Avance en Excel.

Hace unos días solicitaba un lector ayuda para elaborar un gráfico que mostrara el grado de avance de ciertos datos
.generar una gráfica en la que pueda representar el avance de construcción de las cuadrillas en relación a un kilometraje proyectado.

Necesito representar como los kilómetros construidos semana a semana van llegando a lo proyectado y que porcentaje de la totalidad de kilómetros representa el avance cada semana hasta llegar al 100% de lo proyectado...

y adjuntaba un gráfico de cómo podría ser el resultado final:

Gráfico para mostrar Grado de Avance en Excel.



Lo que tenemos de especial en este gráfico es el Multinivel en la etiquetas del eje horizontal, y sobre todo, como siempre, fundamental contar con un estructura de datos en el origen adecuada...
No era el caso de nuestro lector, por lo que partiré mostrando cómo debería ser dicha distribución:

Gráfico para mostrar Grado de Avance en Excel.



Fijémonos que al necesitar un multinivel de la etiqueta del eje horizontal, necesitaremos dos columnas: 'Semana' y 'Fibra', y obviamente las dos series de datos Proyectado y Avance.

El trabajo para llegar a nuestra meta, como casi siempre, consiste en crear diferentes Series Auxiliares; en este ejercicio crearemos seis series.
Tres de ellas que llamaré:
F12%, F24% y F96%
representaré individualmente los valores de Avance (columna C) de cada tipo de Fibra.
Por otra parte, las tres restantes series auxiliares me servirán para agregar a las etiquetas de datos los valores porcentuales de avance.



Las fórmulas necesarias aparecen en la imagen.
para F12%: =SI($B12="Fibra 12";$C12;NOD())
para F24%: =SI($B12="Fibra 24";$C12;NOD())
y para F96%: =SI($B12="Fibra 96";$C12;NOD())

Y para las series necesarias para las etiquetas de valores:
=SI($B4="Fibra 12";$C4/$D4;"")
=SI($B4="Fibra 24";$C4/$D4;"")
=SI($B4="Fibra 96";$C4/$D4;"")


El resultado será:



La forma de construir el gráfico en nuestra hoja de cálculo es sencilla, seleccionamos el rango A3:G12 (dejamos fuera las tres series asociadas a las etiquetas de datos).
Insertamos un gráfico de columnas agrupadas, y a continuación presionamos el botón de Cambiar tipo de gráfico:

Gráfico para mostrar Grado de Avance en Excel.



En el siguiente paso seleccionamos una de las series de datos y ajustamos los parámetros de:
Superposición de series: 100%
Ancho del intervalo: 10%

Gráfico para mostrar Grado de Avance en Excel.



Otro paso es hacer visible la serie de datos de Avance/Fibra, ya que ahora ha quedado oculta 'detrás' de la serie de 'proyectado' con un mayor valor... Para ello presionamos Seleccionar datos... y colocamos la Serie 'Proyectado' encima de la 'Avance'(o Fibra):

Gráfico para mostrar Grado de Avance en Excel.



Llegamos al final. A las tres series auxiliares F12%, F24% y F96% les agregamos Etiquetas de datos, y las configuramos para que muestren los valores de las celdas H4:H12, I4:I12 y J4:J12 respectivamente...


Repetiríamos esta última acción para cada serie...

El resultado final es el que ya hemos visto:

Gráfico para mostrar Grado de Avance en Excel.

6 comentarios:

  1. Me marca error en las fórmulas de los datos auxiliares,cuando introduzco "Fibra 12" me sale una ventana de error y ya no me deja seguir capturando los datos

    ResponderEliminar
    Respuestas
    1. Hola que tal?
      un gusto saludarte igualmente.

      Qué tipo de mensaje de error?.
      Si has seguido las indicaciones tal cual se exponen no debes tener ningún problema...

      En todo caso asegúrate que los separadores de argumentos en tu equipo son los mismos (; y no sólo ,)

      Un cordial saludo

      Eliminar
    2. Si era cuestión de los separadores de argumentos, solo debo usar (,)

      Mil gracias por tu ayuda

      Eliminar
  2. Hola Ismael, una duda nunca había utilizado la función "Nod ()", ¿que uso o aplicación le das?.
    Como es costumbre, muy interesante tu publicación de hoy.

    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola Ciro,
      como ves devuelve un error de No disponible #N/A..
      habitualmente se emplea en gráficos para evitar un valor representado

      Obviamente se puede emplear en cualquier otro caso, siempre que necesitemos retornar un error.

      Saludos cordiales

      Eliminar