Cómo hacer en Excel un moderno gráfico de anillo en 3 pasos

 Artículo actualizado el 06 / Dic. / 2017 
(Ver al final:  Gráfico de anillo que cambia de color, según los valores de origen)

No hay dashboard (cuadro de mando) que se precie hoy en día sin gráfico de anillo. Es una realidad y, sinceramente, creo que está justificada si pensamos que es un tipo de gráfico que expresa de forma práctica, limpia y rotunda un porcentaje.

Este tipo de gráficos es adecuado para expresar lo "alcanzado" comparado con lo que nos falta por alcanzar. Esto, como es entendible, es aplicable a multitud de situaciones en el mundo económico - financiero y también en otras.

Vamos a aprender a hacer un gráfico de este tipo intentando agregar alguna particularidad que lo haga más atractivo, si cabe. Tomaremos como ejemplo, el realizado para la imagen anterior.

PASO 1 (creación del gráfico de anillo)

Partimos de unos datos, que son los que proporcionan el valor a representar, de los cuales seleccionamos las dos cantidades que vemos a continuación:

 

Activamos la ficha INSERTAR y después elegimos "Gráfico de anillo"

PASO 2 (personalización del gráfico de anillo)

Hacemos clic con el botón derecho del ratón en cualquier parte del anillo del gráfico y seleccionamos la opción "Dar formato a serie de datos". Al hacerlo obtenemos un panel a la derecha (si trabajamos con Excel 2013) o un cuadro de diálogo (si trabajamos con Excel 2007/10) en el que podemos ajustar el "Tamaño del agujero del anillo", así como el ángulo de giro y la separación de secciones:

Hasta ahora tenemos...

... pero nenesitamos deshacernos del título y de la leyenda haciendo clic con el botón izquierdo del ratón en cada uno de esos elementos (para seleccionarlos) y después presionando la tecla "Supr".

También sería conveniente seleccionar desde el borde todo el gráfico y eliminar el relleno (que es blanco hasta ahora) y el propio borde que viene por defecto. Para cambiar estas dos características, debemos tener el gráfico seleccionado y activar la ficha FORMATO para después utilizar las herramientas de "Relleno de forma " y "Contorno de forma" eligiendo, respectivamente las opciones "Sin relleno" y "Sin contorno":

Has aquí tendremos esto...

... pero sería más estético si elimináramos la cuadricula de Excel desde la ficha VISTA (NO teniendo seleccionado el gráfico y después desactivando la casilla de verificación "Líneas de cuadrícula") y también si redujéramos el tamalo del gráfico desde cualquier manija de las esquinas.

También podemos personalizar el color de las secciones del gráfico. Basta con seleccionar cada una de ellas con dos clics separados (para que quede seleccionada una sección sola) y elegir el color desde la herramienta "Relleno de forma" que conocemos de pasos anteriores.

PASO 3 (título y círculo central del gráfico)

Para el título, elegimos una celda que esté situada en la parte inferior del lugar que elijamos para el gráfico y simplemente lo escribimos y aplicamos el formato deseado.

Para el círculo central, debemos activar la ficha INSERTAR, después elegir "Elipse" y a continuación extender el cursor en diagonal PRESIONANDO AL MISMO TIEMPO LA TECLA "MAYÚS" para que la elipse sea un círculo perfecto.

Una vez hecho esto, y asegurándonos de que aún está seleccionado el círculo, ponemos el cursor en la barra de fórumulas y escribimos una fórmula que haga referencia a la celda donde tenemos el porcentaje (INTRO cuando ya esté escrita la fórmula):

Ahora, con el círculo seleccionado aún, y desde la ficha INICIO, podemos:

• Utilizar las herramientas de tamaño y color de fuente para personalizar el porcentaje
• Eliminar el borde del círculo (teniéndolo seleccionado, desde la ficha FORMATO / Relleno de formato)
• Alinear el porcentaje correctamente (centrado vertical y horizontalmente) desde los botones siguientes de la ficha INICIO:

Una vez hecho lo anterior, solo queda centrar el círculo sobre el gráfico y también colocar todo encima de una celda que contenga el título deseado:

El espacio de Mac

En cuanto a las diferencias en Mac respecto de todo lo dicho hasta ahora, diremos que:

• Para crear el gráfico debemos ir al siguiente menú (eligiendo la opción "Anillos"):

• Para crear el círculo central, podemos ir al botón (que vemos a la izquierda de la imagen) que activa el cuadro de diálogo "Multimedia"...

... el resultado es, por supuesto, similar:

Actualización 6/12/2017

Gráfico de anillo que cambia de color, según valores de origen

A petición de un lector en un comentario de este artículo, lo he actualizado para mostrar cómo es posible obtener el efecto de un gráfico que cambia de color cuando los valores superan o no alcanzan una cantidad.

Para conseguirlo, debemos realizar, en principio, todo lo que se muesrta en la imagen de abajo...

... son dos gráficos de anillos exactamente iguales (especialmente en tamaño), excepto tres cosas:

• Uno tiene como origen de datos las celdas D2:D3 y el otro, las celdas D19:D20
• El otro tiene la porción de anillo protagonista de color naranja - rojo y el otro la tiene azul
• Un gráfico no tiene color de fondo (está sin relleno)

Las celdas de origen de datos tienen unas fórmulas que, dependiendo de los valores que hay que evaluar (celdas B19:B20) no devuelven un valor de error o el valor que se debe mostrar. Puesto que cuando los valores son errores, el gráfico no puede mostrarlos... ese gráfico se convierte en transparente y deja que se vea el otro gráfico... que sí tiene valores.

Finalmente, tan solo queda poner un gráfico (el transparente) exactamente sobre el otro para que el efecto sea la visualización de los datos para uno de los casos....

y la visualización de los datos.... para otro de los casos...

José Manuel Pomares Medrano

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):

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:

 

Cómo añadir una serie de datos a un gráfico en 3 segundos

Muchos de mis alumnos se sorprenden en los cursos que imparto, de un pequeño truco que explico en el módulo de "Gráficos y presentación de datos". Éste no es otro, que añadir a un gráfico (que ya tenemos hecho) una serie más de datos, tan sólo mediante copiar-pegar (Sólo para Excel 2007 y 2010... no en Excel 2013). Así de sencillo.

Tenemos el siguiente gráfico, que representa sólo la primera de las series de la tabla (Línea 1):

Si queremos agregar la serie de la "Línea 2", tan sólo tenemos que:

PASO 1:

Seleccionar y Copiar con CTRL-C el rango de celdas siguiente:

PASO 2:

Seleccionar el gráfico y hacer CTRL-V

Si además queremos que en vez de columnas, sea una línea lo que represente a la Línea 2, debemos:

• Hacer click con el botón derecho en cualquier columna de la Línea 2
• Seleccionar la opción "Cambiar tipo de gráfico de series..."
• Seleccionar uno de los gráficos de la galería de gráficos de línea y ACEPTAR.

Obtendremos algo así:

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:

Gráficos de Gantt en Excel para gestión de proyectos

Artículo actualizaro el 2 Mar de 2017

A la hora de hacer un gráfico de Gantt para la gestión de proyectos, una de las posibilidades es hacerlo mediante un gráfico de barras horizontales apiladas.

Explicaremos cómo hacerlo, partiendo de una tabla con datos de ejemplo en la que se registra la evolución de unos cuantos proyectos, los cuales tienen una fecha de inicio y una fecha final prevista. Ambas fechas se relacionan con la fecha del día actual para calcular los días que se han trabajado en el proyecto y los días que quedan por trabajar (solo se tienen en cuenta los laborales y se pueden especificar los días festivos del país, dentro de cada proyecto):

El sistema para hacerlo consiste en aprovecharnos de que para Excel, las fechas son realmente números a los que les da un formato especial. Es decir, la fecha 18 de Mayo de 2012, realmente es el número 41.047 el cual es convertido por Excel en fecha cuando asignamos un formato como tal. De la misma forma, el día 1 de Enero de 1900, corresponde al número 1.

Es decir, para los "ojos" de Excel, la anterior tabla sería así:

Si nos damos cuenta, por ejemplo, podríamos "apilar" los números 41014, 33,43 del proyecto 9 en un gráfico de barras horizontales apiladas

 y tendríamos algo así:

El límite izquierdo de la serie naranja de la barra representa el día nº 1 (1 de Enero de 1900), el límite derecho de la misma zona naranja, representa el nº 41014 (Fecha de inicio o 15 de Abril de 2012), la zona verde oscuro representa los días trabajados (33) y la zona verde claro representa los días que faltan para el final (43).

Si representáramos varios proyectos en esta situación, quedaría todo así:

Esto empieza a parecerse a una gestión de proyectos, pero prosigamos para perfeccionar algo más nuestro gráfico.

Observamos que Excel ha asignado una escala automática en la que la primera fecha es el 31-Jul-11 (y no el 1 de Enero de 1900 como podría pensarse en un principio):

Aun con estas "buenas intenciones" de Excel, esto provoca que haya demasiada (e innecesaria) zona naranja en las barras. Lo podemos solucionar obligando a que la escala sea "fija" (y no "Automárica) y comience con una fecha más cercana que bien podría ser el 1-01-12 (el proyecto 1 que comenzó en primer lugar). Lo haríamos así:

Y quedaría así:

Nos queda un detalles más: mejorar el aspecto haciendo que la serie naranja sea transparente. 

Esto se hace haciendo click con el borón derecho en la serie y eligiendo "sin relleno" en el menú contextual, quedando así:

Observamos que hemos dado un formato "personalizado" a las etiquetas de las series:

Esto se puede hacer con coble click en una de las etiquetas y seleccionando "Formato de etiquetas de datos...". Después "construimos" un formato personalizado y lo agregamos:

Vemos con más detalle el resultado final:

Podemos descargar el archivo del ejemplo que nos ha acompañado en este artículo, desde aquí:

  José Manuel Pomares Medrano