Gráficos tipo velocímetro en Excel – segunda nota

viernes, septiembre 15, 2006

En la entrada anterior explicamos las técnicas a emplear para crear un gráfico en Excel que simule un velocímetro. Si queremos crear un gráfico aún más parecido a un velocímetro, tendremos que diseñarlo con aguja (manecilla).
En esta nota mostraremos las técnicas y trucos necesarios para lograr este efecto.

Volvamos al ejemplo de la evaluación de avance de un proyecto






Nuestro objetivo es representar estos datos con el siguiente gráfico



Empezaremos por el final. Para crear la escala en el fondo, usamos la técnica que ya vimos en la nota anterior: un gráfico de tipo anillos al que dividimos en dos mitades. En una de las mitades representamos los intervalos de 10% en orden ascendiente de izquierda a derecha. La otra mitad la hacemos "desaparecer".

Los pasos son los siguientes:

1 – Armamos una tabla con una columna para los rótulos y una para los valores de los anillos, cuidándonos de dejar la celda del encabezamiento de los rótulos (A19) en blanco. De esta manera Excel utilizará los datos de la comuna A como rótulos y no como valores de un segundo anillo.




2 – Seleccionamos el rango (A19:B30) con los valores y los rótulos y en el asistente de gráficos elegimos el tipo anillos



Nos aseguramos que en el asistente de gráficos la opción sea "Series en columnas"



3 – En la pestaña "Títulos" borramos el título; en la pestaña "Leyenda" eliminamos la leyenda y en la pestaña "Rótulos de Datos" señalamos la opción "Nombre de la categoría"



Apretamos aceptar y este es el resultado



4 - Seleccionamos la serie de datos con un clic sobre alguna de las porciones del anillo y abrimos el menú de formato de serie de datos



En la pestaña "Tramas" elegimos un color conveniente (en nuestro ejemplo el amarillo claro)



En la pestaña "Opciones" llevamos el valor de la opción "ángulo del primer sector" a 270 grados, el tamaño del agujero del anillo a 20% y quitamos la marca de la opción "Variar colores de los sectores" de manera que todas las porciones tengan el mismo color.



5 – Para hacer desaparecer el anillo inferior, seleccionamos el sector (con un doble clic sobre la porción) y abrimos el menú de formato de puntos de datos.



En el diálogo señalamos para "Borde" y "Área" las opciones "ninguno"



6 – Arrastramos los rótulos a la posición deseada, lo que haremos manualmente. Elegimos los rótulos con un clic y con un segundo vamos eligiendo cada uno de los rótulos y llevándolo a la posición deseada



Presten atención que el rótulo de la porción inferior (100%) aparece a pesar de que no vemos el sector.

Con esto hemos terminado de preparar la escala de nuestro velocímetro. Ahora crearemos la aguja lo que hacemos usando un gráfico de tipo XY (Dispersión) con puntos de datos conectados por líneas.




Uno de los puntos de la aguja será siempre el cero; el otro punto deberá "moverse" a lo largo de una semicircunferencia que coincida con el borde exterior del anillo.
Esto significa que debemos transformar el dato que queremos representar (el porcentaje cumplido del proyecto) a radianes y establecer una fórmula que nos de los valores del punto. Hacemos esto usando las
funciones seno [=SENO()] y coseno [COS()] de Excel.

A nuestra tabla de datos del proyecto la agregamos una tabla para la transformación de datos




Los valores de las celdas A7 y B7 serán siempre cero.

El valor de la celda A8 está determinado por la fórmula

=-COS(B4*PI())/B10
Prestar atención al signo "-" al principio de la fórmula.

El valor de la celda B8 está determinado por la fórmula

=SENO(B4*PI())/B10

La función de la celda B10 es darnos control sobre el largo de la aguja. Podemos cambiar el valor en la celda B10 hasta llegar al largo deseado.

Ahora, con el rango A7:B8 seleccionado, elegimos el gráfico de tipo XY con líneas sin marcadores y nos aseguramos que la opción "series en" sea "columnas"




Quitamos las líneas de división y apretamos "Aceptar"



Como ven, tenemos todavía mucho trabajo por delante.


La primer tarea es adaptar los ejes a nuestras necesidades. Elegimos el eje de las X y abrimos el diálogo de formato del eje.




En la pestaña "Escala" fijamos el valor mínimo a -1 y el máximo a 1

Ahora seleccionamos el eje de las Y y apretamos F4 para copiar el formato del eje de las X. Este es el resultado




Ahora borramos el eje de la Y y los rótulos del eje de las X. Este es ahora nuestro gráfico




La línea que vemos une dos puntos: uno determinado por los valores (0,0) y el otro determinado por los valores (0.556, 0.962).

Ahora tenemos que combinar los dos gráficos. Excel permite presentar distintas series de datos en un mismo gráfico con distinto tipos de gráficos. Por lo tanto lo que haremos es copiar la serie de datos que define la aguja al gráfico de anillos y luego cambiar ciertas definiciones para lograr el efecto deseado.

Procedemos de esta manera:

1 – Seleccionamos el rango de los valores XY (A7:B8) y los copiamos (Ctrl+C). Luego seleccionamos el gráfico de anillos y en el menú de Excel apretamos "Edición--Pegado Especial". En el diálogo que se abre señalamos "agregar celdas como nueva serie" , "valores Y en columnas" y "Categorías (rótulos de X) en la primera columna"




El resultado es un tanto desalentador, pero enseguida nos ocuparemos de esto.



2 – Seleccionamos el anillo violeta (la nueva serie que acabamos de agregar) y abrimos el diálogo de tipo de gráfico.



Elegimos el tipo XY con líneas sin marcadores y apretamos "Aceptar"



3 – Ahora arreglaremos los ejes para que la aguja coincida con el gráfico de los anillos. Seleccionamos el eje de las Y y, como mostramos antes, definimos el valor mínimo a -1, el máximo a 1, y en "Eje de valores X cruza en" ponemos 0.



Seleccionamos el eje de las X y apretamos F4 para copiar el formato. Borramos el eje de la Y para lo cual en el menú Gráfico--Opciones de gráfico, en la pestaña Eje quitamos la marca del eje de las Y. Del eje de las X quitamos los rótulos



Como ven, casi hemos terminado.

En el extremo de la aguja aparece el rótulo con el valor del punto (0.556), cuando lo que queremos es que aparezca el valor original 66.7%.

El "truco" es el siguiente: seleccionamos el rótulo del punto (dos clic separados por un pequeño intervalo). Con el rótulo seleccionado creamos una referencia a la celda B4 en la barra de fórmulas (clic a la barra de fórmulas, escribimos "=" y hacemos un clic a la celda B4).



Los últimos toques son formar el fondo del gráfico, el rótulo del valor y la aguja, todo de acuerdo a los gustos personales de cada uno.



En una próxima nota veremos una interesante variante con dos series de datos (dos agujas) propuesta por
Jon Peltier en su sitio.

Categorías: Graficos_

Technorati Tags: ,

33 comments:

Anónimo,  09 noviembre, 2007 14:54  

Impresionante grafico de control, me ha servido de mucho .
Muchisimas Gracias
Santy

Anónimo,  01 julio, 2008 01:51  

un consulta cuando poco un % de 10, la aguja se mueve de su base cero. se me descuadra.

si me podrias ayudar??
jeanrodriguezt@hotmail.com

Jorge L. Dunkelman 01 julio, 2008 21:47  

Tendrías que enviarme el archivo con el problema.

Anónimo,  10 julio, 2008 08:51  

buenos dias. primero de todo felicitarte por el grafico es muy bueno.
y segundo: ¿como puedo hacer para realizarlo con valores negativos?. y ¿como puedo ahcer para que al poner el grafico en columnas no se borre la linea y queden solo los puntos?
muchas gracias.
mi mail: valdin-bushi@hotmail.com

Jorge L. Dunkelman 14 julio, 2008 21:18  

Puedes mandarme el archivo con los datos que quieres graficar?

Anónimo,  20 noviembre, 2008 20:05  

me encuentro dentro del proceso de inplantación de un sistema de gestión de calidad en mi empresa, y sus consejos y hints para realizar los tacómetros ha sido la diferencia en la realización fácil y clara de los apoyos visuales de gestión, muchisimas gracias

Deodato

Anónimo,  29 noviembre, 2008 03:37  

Estaba buscando algo exactamente como esto, pero tengo jun problema, las caracteriticas que presentas no son del excel 2007, me podrias ayudar viendo como le podria hacer con este excel, por que la verdad si estoy muy interesado

Jorge L. Dunkelman 29 noviembre, 2008 20:39  

Hola
existen varias diferencias en el funcionamiento de los gráficos entre Excel 2007 y las versiones anteriores.
Lo que te recomiendo hacer es crear el gráfico en XL2003 y luego abrir el archivo en la versión 2007. He probado con este gráfico en particular y veo que funciona.
También he corregido el elnace para descargar el archivo con el ejemplo.

Anónimo,  01 diciembre, 2008 22:00  

Muchas gracias, y de la misma forma que lo menciona ya le empece a trabajar en excel 2003, agradezco su tiempo y la forma de ayudarnos.

Anónimo,  08 diciembre, 2008 22:13  

Tengo una pregunta, que en realidad me puede salvar la vida, ¿este proceso puede ser ejecutado por una macro?

Jorge L. Dunkelman 08 diciembre, 2008 23:21  

Bueno, si es cuestión de vida o muerte puedes grabar todos los pasos en una macro y ejecutarla.

Anónimo,  09 diciembre, 2008 20:39  

Dime como le puedo hacer, la macro no se ejecuta me marca un error, me puedes ayudar con eso por favor?

Jorge L. Dunkelman 09 diciembre, 2008 22:34  

Ponte en contacto conmigo a través del mail.

Anónimo,  26 febrero, 2010 21:05  

Qué sucede si hay por ejemplo un 110% de cumplimiento ?. Atte., Marco Olivares H.

Jorge L. Dunkelman 27 febrero, 2010 00:05  

El concepto del gráfico es mostrar resultados entre 0% y 100%. Como el velocímetro de un auto, si superas la velocidad máxima, no lo verás reflejado en el instrumento.

Anónimo,  22 mayo, 2010 07:12  

Eres el mejor !!!

Anónimo,  22 febrero, 2011 22:43  

Excelente profesor, Gracias!!!

Anónimo,  23 febrero, 2011 06:48  

Buen dia, los graficos son excelentes y de mucha ayuda para el seguimiento...... actualmente estoy realizando un velocimetro que llegaa al 110% pero no he podido hacer que la aguja indique el valor correspondiente, siempre me queda mal........ me puedes ayudar con esto? Muchas Gracias.

Jorge L. Dunkelman 23 febrero, 2011 07:07  

Como pongo en mi comentaro del 27 de febrero del año pasado, el concepto es que la aguja se mueva entre 0% y 100%.
Existen otras, mejores, alternativas al gráfico tipo velocímetro. Estaré publicando algo sobre el tema en un futuro cercano.

Anónimo,  10 marzo, 2011 01:59  

He seguido todos los pasos, con el excell 2007, pero no he conseguido trasladar la aguja al gráfico de quesitos. Cómo lo puedo hacer? Cuando habro el pegado especial no aparece lo descrito anteriormente de"agregar celdas como nueva serie" , "valores Y en columnas" y "Categorías (rótulos de X) en la primera columna"
Un saludo

Jorge L. Dunkelman 10 marzo, 2011 07:03  

Fijate en mi comentario del 29/11/2008

Elena,  10 marzo, 2011 13:25  

Ya he creado el velocímetro, pero dependiendo de los datos que introduzca, hay veces que no me indica la cantidad en %, no sé por qué. Si se mueve la aguja.
Gracias

Anónimo,  10 marzo, 2011 14:29  

He conseguido hacer todo el velocímetro, pero cuando muevo las agujas dependiendo del dato me sale el porcentaje o no.
Un saludo

Javier Villanueva 29 abril, 2011 22:06  

Gracias!!!!!.... una ayuda increíble!...realmente es sorprendente todo lo que puedes ir descubriendo en el excel, y gracias a tí aprendo cada dia un poco mas!..

Anónimo,  27 marzo, 2012 20:33  

BUENISISISISMO

Fernando José Moreno Mora 31 agosto, 2012 21:13  

Hola a todos.

Desconocía este tipo de gráficos en Excel, gracias.

Saludos.

Elizabeth Mujica,  22 octubre, 2012 21:56  

Precioso post, justo lo que andaba buscando para un dashboard. ¡Muchas gracias!

Anónimo,  22 noviembre, 2012 01:34  

Excelente!! Muchas Gracias!!!

ireyeso 07 enero, 2013 19:58  

excelente, todo bien claro y funciona a la perfección

Anónimo,  20 marzo, 2013 19:07  

De verdad es un maravilla lo que se puede hacer con Excel. Mil Gracias de verdad me ha servido un monton

Anónimo,  17 octubre, 2013 02:30  

Muy bueno, muchas gracias.

Anónimo,  29 marzo, 2016 19:42  

Felicidades! Excelente trabajo!

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP