viernes, 29 de noviembre de 2013

Gráficos de velocímetro en Excel (4): Creación desde cero


Para aquellos que desean un nivel de personalización total y quieren aventurarse en el ámbito de un manejo avanzado de Excel, cerramos esta serie de artículos que comenzamos hace bastantes meses, con la construcción desde cero de un gráfico de velocímetro.




Vamos a dividir el proceso en 3 partes:
  1. Creación de la escala
  2. Creación de la aguja
  3. Últimos retoques
1ª PARTE: Creación de la aguja
  • Debemos preparar los siguientes datos en alguna hoja de Excel:


Donde el Valor alcanzado es el valor que tenemos que introducir (o enlazar a una celda que tenga ese valor) y el Valor objetivo es el valor de referencia que tenemos que conseguir. Hay que recordar que un gráfico de velocímetro tiene como objetivo representar el grado (%) de cumplimiento de un objetivo.

En tercer lugar, debemos introducir una fórmula que divida los anteriores datos y en cuarto lugar debemos introducir el valor 2 (dos). Estas dos celdas, deben tener formato porcentaje.

Para finalizar debemos hacer una pequeña tabla como la que vemos en la parte inferior la imagen de arriba, donde hay dos valores fijos en la fila con el título "Centro" (dos ceros) y también dos fórmulas en las celdas de abajo (la fila con el título "Punto") que son el resultado de las funciones trigonométricas SENO y COS (que podemos ver en color azul para poder copiarlas).
  • Sin tener ningún dato seleccionado (en una celda vacía, por ejemplo) insertamos un gráfico de dispersión desde la ficha Insertar / Gráficos / 

  • Con el botón derecho del ratón, hacemos clic en cualquier sitio del gráfico en blanco que ha aparecido en el paso anterior y del menú contextual elegimos "Seleccionar datos".
  • Hacemos clic en "Agregar" y después introducimos en el cuadro que aparece, lo siguiente:

... que no es otra cosa que el resultado de seleccionar:


  • A continuación, debemos cambiar la escala del eje vertical. Para ello, hacemos clic en cualquier número de dicha escala con el botón derecho del ratón y seleccionamos "Dar formato al eje..". y cambiamos las opciones de esta forma:


  • Después, debemos hacer lo mismo con la escala del eje horizontal. Para ello, también hacemos clic en cualquier número de dicha escala con el botón derecho del ratón y seleccionamos "Dar formato al eje..". 


y cambiamos las opciones de la misma forma y con los mismos valores que utilizamos para el eje vertical.

Si hemos hecho todo bien, debemos tener esto:

  • Ahora debemos eliminar todos los elementos de este gráfico, excepto los dos puntos azules (seleccionando previamente con un clic cada elemento con el botón izquierdo del ratón y presionando la tecla "Supr"):


  • Es el momento de hacer clic con el botón izquierdo en cualquiera de los puntos y desde la ficha "Formato", seleccionamos un color de "Contorno de forma" negro (por ejemplo). Veremos como aparece la aguja.



             Publicidad (vídeo en HD de software para Plan de viabilidad económica):
http://goo.gl/Oh7FHI


2ª PARTE: Creación de la escala
  • Con el botón derecho del ratón, hacemos clic en cualquier sitio del gráfico (excepto en la aguja) y en el menú contextual elegimos "Seleccionar datos".
  • Hacemos clic en "Agregar" y después introducimos en el cuadro que aparece, lo siguiente:


        Aceptar...
  • En el gráfico que tenemos, ha aparecido un punto arriba en el centro. Debemos seleccionarlo con el botón derecho del ratón y elegir "Cambiar tipo de gráfico de series..." y elegimos un gráfico de anillos:


  • Llegados aquí, debemos quitar el relleno a las secciones de la mitad inferior del anillo, seleccionando una a una cada sección y desde la ficha "Formato" elegir "Sin relleno". Debemos recordar que para seleccionar una a una las secciones, hay que hacer un clic separado de otro clic en cada sección a seleccionar. Posteriormente, cambiamos el color de las secciones de la mitad superior según nuestra conveniencia:


3ª PARTE: Últimos retoques
    • Quitar los marcadores (rombos) de los extremos de la aguja (con el botón derecho sobre la aguja, seleccionamos "Dar formato a serie de datos..." y en dentro del menú "Opciones de marcador" elegimos "Ninguno".
    • Cambiar la aguja a forma de flecha (botón izquierdo sobre la aguja y en a ficha "Formato", seleccionar "Contorno de forma" / Flechas / elegir un tipo de flecha que nos guste y también un grosor adecuado).
    • Quitar el color del fondo al gráfico (Relleno de forma = "Sin relleno") para que deje ver las celdas que hay detrás y también el color del borde (Contorno de forma = "Sin contorno")..
    • Poner en las celdas de atrás los valores de referencia.
... y voila!!!

...aunque también hay otras opciones de personalización...

...y más refinado...

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:


 

59 comentarios:

  1. Llevo años buscando esto,...muchas gracias!!!!!
    Una pequeña errata,....para que funcione como debe, la multiplicación de pi debe estar dentro del paréntesis de la división que se hace.

    ResponderEliminar
  2. Me pasa que cuando algo tiene cumplimiento del 75% la aguja no marca ese 75%,...ayuda!!!

    ResponderEliminar
  3. Buenos días.Quería consultarte sobre un problema que tengo con el eje X del gráfico de Excel. Es una serie diaria. Quiero dejar sólo los días que a mí me interesan. Probe poniendo datos como texto en el eje y borrando aquellos que no me interesan, pero nada. Alguna idea respecto a este tema? cualquier idea será bien recibida.
    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. En el cuadro "Opciones de eje..." (se abre con botón derecho encima de una de las fechas y "Dar formato al eje...") tienes varias opciones para personalizar. Pero por lo que dices, si necesitas que las fechas sean elegidas por tí de forma arbitraria, deberías construirte una columna (o fila) a tal efecto con la fechas deseadas. Después puedes asignar esa columna desde "Seleccionar datos..." (botón derecho en el gráfico) y después "Editar" las "Etiquetas del eje horizontal". Una vez ahí selecciona en "Rango de rótulos del eje" el rango donde tengas tus fechas.
      Saludos

      Eliminar
  4. Buenas tardes, Muchas gracias por la información, ha sido de gran ayuda, adicional tengo una duda, es posible ligar directamente este tipo de gráficos a una tabla dinámica, para que por ejemplo se pueda usar segmentación de datos sobre el?

    Muchas gracias

    ResponderEliminar
    Respuestas
    1. El único dato de entrada o dato-origen de este gráfico es el "valor alcanzado" (que puedes ver al principio de la 1ª PARTE del artículo). Ese dato puede venir enlazado desde cualquier sitio, incluido una TD.

      Si utilizas Excel 2013, debes saber que en esta versión se puede hacer una segmentación de datos en Tablas de Excel, además de con las Tablas Dinámicas.
      Saludos

      Eliminar
  5. oculto columnas y filas y se pierde la flecha del velocímetro que hago?

    ResponderEliminar
    Respuestas
    1. Los gráficos en Excel no pueden representar datos en filas o columnas ocultas

      Eliminar
    2. Puedes dejar los datos visibles pero el color de la letra del mismo color del fondo para que se disimule... o ponerlos detrás del gráfico para que te lo tape. Son las opciones que yo utilizo

      Eliminar
  6. EXCELENTE!!! muy valioso!! y muy claro!!

    ResponderEliminar
  7. Buenos días! Una información muy útil, gracias! Sin embargo, tengo una duda que no se como solucionar. Estoy en el 2º paso: Tengo la flecha del velocímetro y ya he seleccionado la nueva serie de datos pero si le doy a cambiar tipo de gráfico y selecciono el de anillos, me desaparece la flecha!

    Muchas gracias

    ResponderEliminar
  8. Hola Gracias por el artículo, tengo un problema cuando le doy cambiar formato al gráfico se me pierde la aguja. Gracias Excel 2013

    ResponderEliminar
  9. Excelente...!!! Muchas gracias por compartir... Trabajos como el que hacen acá ayuda al día a día. Son cosas que difícilmente aprenderás en un curso.

    ResponderEliminar
  10. Excelente artículo, me ha sido de mucha utilidad. Tengo una consulta: como se agregan los valores porcentuales a la gráfica (0,25,50,75,100, etc.).

    ResponderEliminar
  11. consulta, al ingresar valores menores a 100 la aguja se da vuelta.. que puede estar ocurriendo?

    ResponderEliminar
  12. Buen dia

    Se pierde la aguja al cambiar al gráfico de anillo como dice la parte 2 del tutorial que hago?

    ResponderEliminar
    Respuestas
    1. Dependiendo de la versión de Excel, es posible que tenga un comportamiento distinto en alguno de los pasos.
      Intentad hacerlo con Excel 2010 que fue el programa con el que se hizo el tutorial.
      Saludos.

      Eliminar
  13. Hola. Muchas Gracias por el Tutorial.
    Para los que lo intentan hacer en Excel 2013 y se pierde la aguja. Se resuelve así:
    Paso 2: elegir "Cambiar tipo de gráfico de series..."
    Aparece la ventana con opciones al lado izquierdo..AHI NO ES. En el centro aparecen seie 1 y serie 2 y se puede escoger el gráfico para cada serie. La serie 1 se deja igual (la flecha). La serie 2 si se puede cambiar en anillos.

    ResponderEliminar
    Respuestas
    1. Mil Gracias benefactor anónimo!!!

      Eliminar
    2. Gracias!!, estuve mucho rato buscando la solución a este paso.

      Eliminar
  14. EXCELENTE! hora mis dashboards seran mucho mas visuales!! muchas gracias

    ResponderEliminar
  15. Gracias Carlos por tu acertada aportación y nos alegra Juan Rafael que cumplamos nuestro objetivo de contribuir en la mejora de vuestro trabajo.

    ResponderEliminar
  16. Excelente material; pero ahora como sería para ir llenando una botella segun cumplimientos?

    ResponderEliminar
  17. Solo respondemos dudas relacionadas directamente con el artículo, siempre y cuando la pregunta está formulada de forma inequívoca.
    Ofrecemos formación y consultoría para otros servicios que necesiten, previo presupuesto
    Saludos y gracias por leernos.

    ResponderEliminar
  18. Impresionante! se agradece y se implementa.

    ResponderEliminar
  19. Me queda el valor de x negativo -0.707106781. y probe poniendo PI dentro de la division y tampoco me resulto...Pude hacerlo igual pero al finalizar y cargar los valores objetivo y alcanzado la aguja no se mueve... que puede ocurrir?

    ResponderEliminar
    Respuestas
    1. Es complicado, para este ejercicio, saber cuál puede ser el problema, puesto que pueden ser múltiples y en cualquier paso del proceso.
      También he de decir que el valor cumplido (división de D2/D3) debe resultar <= 1 para que la escala represente desde 0% hasta 100%, de lo contrario el resultado de la aguja no representará fielmente lo deseado.
      Si la división la convertimos en D2/(D3*2) entonces podrán resultar valores <=2 y podremos representar hasta un 200%
      Saludos.

      Eliminar
  20. Excelente!, solo es cuestión de hacer los ajustes a la necesidad y se puede representar!

    ResponderEliminar
  21. Es muy claro y me salio perfecto!!!! ahora, le consulto lo siguiente, si quiero que en ese medio circulo tome los valores negativos y flecha no se dirija a lo que puse "sin relleno" es posible?
    muchas gracias

    ResponderEliminar
  22. DONDE MARCA 75% DEBERÍA PONER 50%
    LA FLECHA CON VALORES INFERIORES A 100% SE INVIERTE, SITUÁNDOSE EL PUNTERO EN EL CENTRO DE LA TARTA
    POR LO DEMÁS GENIAL

    ResponderEliminar
  23. Muy bueno pero como hago el degradado de la imagen final?

    ResponderEliminar
    Respuestas
    1. Seleccionando cada sector del gráfico y desde la ficha formato, elegir Relleno de forma / Degradado

      Eliminar
    2. Si lo hago asi pero no queda de la manera que sale ahi sino que los colores se ven separados, queda un degrades por color, no degradado en el cambio de color, sera la version de Office?

      Eliminar
    3. No es por la versión. Por aquí es dificil explicar la personalización de degradados. En Excel 2007 se hace de una forma y en el resto de Excel se hace de otra. En resumen, hay que peronalizar el degradado, no hay que aplicar degradados que ya están preestablecidos.

      Eliminar
  24. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  25. Excelente me ayudó muchísimo. . . .!!!

    ResponderEliminar
  26. Muy bien explicado. Como hiciste el degradé de colores del modelo final?

    ResponderEliminar
  27. muchísimas gracias desde Venezuela un abrazo

    ResponderEliminar
  28. Buenas tardes, cómo haces para colocar los porcentajes de manera dinámica en el velocímetro/tacómetro?? o lo hiciste de manera manual?? gracias, saludos,

    ResponderEliminar
    Respuestas
    1. Aunque se puede hacer por otros métodos, de forma manual es lo más sencillo (mediante cuadros de texto que puedes mover libremente).
      Saludos.

      Eliminar
  29. Hola, excelente articulo, hay un pequeño detalle me gustaria consultar al colocar valores inferiores al 100% la flecha se desconfigura... nos puedes ayudar porfis

    ResponderEliminar
    Respuestas
    1. Es cierto que estos gráficos no pueden representar todos los valores posibles, pero intenta modificar las fórmulas de las celdas C4 y C5 que se ven en el artículo del blog en la 1ª PARTE para variar las proporciones que se representan. Es cuestión de probar la combinación mejor para el tipo de datos que necesitas.

      Eliminar
    2. Lo que puedes hacer, es generar una tercera serie con unos datos iguales para x e y (copialos más abajo e indica que esos son para valores menores a 100%), y tendras una segunda flecha, esa la modificas para que apunte a donde debería, para los valores menores a 100% que es donde se desconfigura. para que solo te aparezca la flecha que apunta a donde quieres tendras que hacer que alguno de los 4 valores de la serie errores desaparezca mientras el %alcanzado no entre en el rango donde la fecha muestra la orientación correcta, yo use esta formula =si(%alcanzado<1,"",0) esta formula la puse sobre la columna de la x donde va el cero, al quedar ese valor como vacio desaparece la flecha.
      Espero me allá podido explicar

      Eliminar
    3. Lo que puedes hacer, es generar una tercera serie con unos datos iguales para x e y (cópialos más abajo e indica que esos son para valores menores a 100%), y tendrás una segunda flecha, esa la modificas para que apunte a donde debería, para los valores menores a 100% que es donde se desconfigura. Para que solo te aparezca la flecha que apunta a dónde quieres, tendrás que hacer que alguno de los 4 valores de la serie errores desaparezca mientras él %alcanzado no entre en el rango donde la fecha muestra la orientación correcta, yo use esta fórmula =si(%alcanzado<1,"",0) esto lo puse sobre la columna de la x donde va el cero, al quedar ese valor como vacío desaparece la flecha, la formula tendrá que modificarse en el otro caso con >=1 y así podrás ver todos los valores

      Eliminar
  30. Me pasa que cuando algo tiene cumplimiento del 75% la aguja no marca ese 75%,...ayuda!!!

    ResponderEliminar
  31. Me pasa que cuando algo tiene cumplimiento del 75% la aguja no marca ese 75%,...ayuda!!!

    ResponderEliminar
    Respuestas
    1. Es cierto que estos gráficos no pueden representar todos los valores posibles, pero intenta modificar las fórmulas de las celdas C4 y C5 que se ven en el artículo del blog en la 1ª PARTE para variar las proporciones que se representan. Es cuestión de probar la combinación mejor para el tipo de datos que necesitas.

      Eliminar
  32. Excelente su aporte muchas gracias, no es verdad que no represente todos los valores, en donde están las formulas de seno y coseno colocar un condicional que evalúe si es mayor a 200% entonces que el valor del eje "y" es cero y lo mismo para el eje "x" que sea igual a 1 para que cuando el cumplimiento sea superior a 200% solo apunte a la derecha lo mismo si es inferior a 100%. los valores se colocan insertando una auto forma; como el gráfico está transparente los objetos se colocan detrás del gráfico.

    ResponderEliminar
  33. GRACIAS, EXCELENTE EXPLICACIÓN, SALUDOS DESDE NICARAGUA

    ResponderEliminar
  34. estimados, cuando los numero son inferiores a 100% la flecha queda al revés

    ResponderEliminar
    Respuestas
    1. En este enlace se puede descargar un archivo donde hay cantidades menores de 100% y e gráfico funciona bien.
      No se qué le puede ocurrir, pero invluso podría ser un problema de la versión de Excel que utiliza.
      Saludos.

      Eliminar
  35. y si quiero graficar números negativos como -200

    ResponderEliminar
    Respuestas
    1. Solo pueden representarse valores positivos con gráficos de anillo.
      Los gráficos de velocímetro no tienen como objetivo mostrar el valor de un número, sino la relación de un valor "conseguido" respecto de otro valor total-objetivo.
      Busca la forma de convertir a positivo el numero que muestra "lo conseguido" y también busca la forma de convertir a positivo "el objetivo". Una vez que lo tengas, solo tienes que hacer la división y el resultado es el valor que "alimenta" el gráfico.
      Si no encuentras las forma, quizá un gráfico de velocímetro no es ideal en esta ocasión.

      Eliminar
  36. Cuando modifico el Valor alcanzado (De 0 a 60 y de 86 al 100), la flecha se mueve por todo el gráfico.

    ResponderEliminar
  37. Cuando actualizo el Valor alcanzado (En mi ejemplo del 0 al 60 y del 86 al 100) la flecha se mueve por todo el grafico.

    ResponderEliminar