Gráfico de Termómetro en Excel – Descarga Gratuita y Cómo Crearlo

Este tutorial demostrará cómo crear un gráfico de termómetro en todas las versiones de Excel: 2007, 2010, 2013, 2016 y 2019.

Un gráfico de termómetro es una modificación especial de un gráfico de columnas apiladas. Las columnas se colocan unas encima de otras y visualmente se asemejan a un termómetro, que se va llenando a medida que avanzas hacia tu objetivo.

Siempre que necesite hacer un seguimiento del progreso hacia un objetivo -ya sea financiación, ventas, ingresos u otras métricas relevantes- este gráfico proporciona una forma sencilla y eficaz de visualizar esos datos.

Pero un simple termómetro ya no es suficiente (¡vamos, estamos en 2020!).

En este tutorial detallado paso a paso, aprenderás a trazar un gráfico de objetivos termométrico extraordinariamente versátil con una etiqueta elegante que funciona incluso cuando tus resultados superan un objetivo establecido.

Compruébalo:

 

Como verá en breve, crear un gráfico de termómetro impresionante requiere mucho esfuerzo. Por eso hemos diseñado el Complemento Creador de Gráficos, una herramienta para crear gráficos Excel avanzados en unos pocos clics.

Así que, vayamos al grano.

Cómo Empezar

A modo de ilustración, imagina que lanzaste una tienda de comercio electrónico como un hustle lateral y quieres construir un gráfico de termómetro para realizar un seguimiento del rendimiento de la tienda en comparación con tu objetivo de ingresos establecido. En 2019, tuviste una buena racha.

La tienda generó $ 21,734 en ingresos, superando ligeramente tu objetivo inicial (puedes descorchar una botella de champán más tarde).

Dicho esto, echa un vistazo al conjunto de datos del gráfico:

 

Antes de pasar a construir el gráfico, tenemos que desglosar cada punto de datos para que puedas volver sobre tus pasos fácilmente utilizando tus datos.

El conjunto de datos tiene dos columnas:

• Datos Originales: Esta columna representa sus datos reales, cualesquiera que sean. Déjela como está.
• Entradas del Gráfico: Aquí es donde ocurre toda la magia. La tabla contiene todos los valores especiales que utilizará para construir el gráfico. Además, una nota importante: ¡las categorías deben estar ordenadas exactamente de la misma manera que se muestra en la captura de pantalla anterior!

Quizá se pregunte de dónde proceden todos los valores de la segunda tabla. Pues bien, en realidad tendrá que calcularlos usted mismo utilizando las fórmulas especiales de Excel que se indican a continuación (sorpresa, sorpresa).

Desafortunadamente, no hay otra forma de hacerlo, pero las instrucciones que aparecen a continuación deberían hacerlo bastante fácil de seguir.

Así pues, ¡empieza el viaje!

Paso #1: Configurar la Tabla Auxiliar.

Como el eje que mantiene todo unido, cada uno de los elementos de la segunda tabla tiene su propósito. Veamos a estos chicos malos con más detalle.

Ingresos objetivo: Este punto de datos debe coincidir con el valor objetivo(B7). Así de sencillo.

Fórmula de Ingresos Totales: Esta Función SI evita que los marcadores de datos se apilen unos sobre otros de forma incorrecta en caso de que el resultado supere el objetivo:

=SI(B6<=B7; B6; B7)

He aquí la versión descodificada basada en los datos originales:

=SI({Total}<={Goal}; {Total}; {Goal})

Traducción al lenguaje humano básico: si el resultado (B6) es inferior o igual al objetivo (B7), la fórmula devuelve el valor del resultado (B6); en nuestro caso, todo lo que esté por debajo de la marca de 20.000 dólares. Por el contrario, si el resultado (B6) supera el objetivo (B7), la fórmula devuelve el valor objetivo(B7).

Consejo rápido: haga clic en el botón “Formato de números contables” (Pestaña Inicio > Grupo Números) para mostrar la salida de la función como moneda.

 

Superado: Esta Función SI calcula si se ha superado o no el objetivo. Cuando esto ocurre, la fórmula resta el objetivo(B7) del resultado(B6) y devuelve lo que sobra. En caso contrario, la fórmula simplemente muestra cero:

=SI(B7<=B6; B6-B7; 0)

La versión descodificada:

=SI({Goal}<={Total}; {Total}-{Goal}; 0)

Progreso: Esto se explica por sí mismo. Necesitamos medir el progreso como un porcentaje del objetivo para nuestra etiqueta ultra elegante. Simplemente divide el resultado(B6) por el objetivo(B7) y formatea el resultado como un porcentaje: haz clic en el botón “Estilo de porcentaje” (Pestaña Inicio > Grupo Números).

=B6/B7

Versión descodificada:

={Total}/{Goal}

Etiqueta: La guinda del pastel. Combina los valores numéricos y porcentuales que representan el progreso hacia el objetivo y les da un formato ordenado. Para ello, recurriremos a la función TEXTO (novatos en Excel, prepárense para el impacto):

=CONCATENAR(TEXTO(B6; "$#0.00"); " ("; TEXTO(E5; "#0%"); ")")

Y la versión descodificada:

=CONCATENAR(TEXTO({Total}; "{Formatear el Valor como Moneda}");" (";TEXTO({Progreso}, "{Formatear el Valor como Porcentaje}");")")

Si te parece desalentador, echa un vistazo al artículo sobre la función que aparece en el enlace anterior.

Así es como se ve en la práctica:

 

Lo peor ya ha pasado. Para resumirlo todo, echa un segundo vistazo a las fórmulas:

Ajustes del Gráfico	Fórmula
Fórmula de ingresos totales	=SI(B6<=B7; B6; B7)
Superado	=SI(B7<=B6; B6-B7; 0)
Progreso	=B6/B7
Etiqueta	=CONCATENAR(TEXTO(B6; “$#0.00”); “ (”; TEXTO(E5; “#0%”); “)”)

Una vez allí, pasar al siguiente paso.

 

Paso #2: Crear un Gráfico de Columnas Apiladas.

1. Resalte el rango de celdas E2:E4 en el Valor
2. Selecciona Insertar
3. Haz clic en el icono “Insertar gráfico de columnas o barras”.
4. Elige “Columna apilada”

 

Paso #3: Apilar las Series de Datos Una Sobre Otra.

Una vez que aparezca el gráfico, reorganice las series de datos:

1. Haga clic en el gráfico.
2. Cambie al Diseño de Gráfico
3. Elija “Cambiar Fila/Columna”

 

Paso #4: Cambiar los Colores de los Marcadores de Datos.

Para convertir el gráfico en algo parecido a un termómetro, comience por cambiar el color de los marcadores de datos. Primero, haga transparente el marcador de datos para la Serie 1 “Ingresos Objetivo”(E2) y coloque líneas de borde a su alrededor.

Haga clic con el botón derecho en el marcador de datos que representa los “Ingresos objetivo” E2 (sección inferior del gráfico) y seleccione “Formatear serie de datos”

 

En el panel de tareas que aparece, haga lo siguiente:

1. Vaya a Relleno y línea
2. En “Relleno”, seleccione “Sin relleno”
3. En “Borde”, seleccione “Línea sólida”
4. En “Borde”, haz clic en el icono Color de relleno para abrir la paleta de colores y selecciona rojo.
5. Ajusta el valor de Ancho a 2 pt.

 

Colorea los otros marcadores de datos de la siguiente manera:

1. Para la serie 2 “Fórmula de ingresos totales”(E3):
   1. Seleccione el marcador de datos (E3) con el panel de tareas Formato de Series de Datos aún abierto.
   2. En “Relleno”, seleccione “Relleno sólido”
   3. Haga clic en el icono Color de relleno y seleccione rojo.
2. Para la Serie 3 “Superado” (E4):
   1. Seleccione el marcador de datos(E4) con el panel de tareas Formato de Series de Datos aún abierto.
   2. En “Relleno”, seleccione “Relleno sólido”
   3. Haga clic en el icono Color de relleno y seleccione Verde claro.

 

Paso #5: Añadir la Etiqueta de Datos al Gráfico.

Ahora es el momento de insertar la encantadora etiqueta de datos (E6) que tanta sangre, sudor y lágrimas nos costó crear.

Haga clic con el botón derecho en la Serie 2 “Fórmula de Ingresos Totales” (la que tiene un relleno rojo) y elija “Agregar Etiquetas de Datos”

 

Paso #6: Cambiar el Valor de la Etiqueta de Datos.

Vincule la etiqueta al valor de la segunda tabla.

1. Haga doble clic en la etiqueta.
2. Escriba “=” en la Barra de Fórmula.
3. Seleccione el valor de la etiqueta(E6).

 

Ahora tenemos que arreglarlo un poco. Ponga la etiqueta en negrita, póngale color blanco y cambie el tamaño de la fuente para mejorar estéticamente el elemento (Pestaña Inicio > Grupo Fuente).

Paso #7: Reposicionar la Etiqueta de Datos.

Una vez que haya colocado la etiqueta, empújela hasta el extremo superior del marcador de datos relacionado. Haga clic con el botón derecho en la etiqueta de datos y elija “Formato de Etiquetas de Datos”

 

Luego, en la pestaña Opciones de etiqueta, en Posición de etiqueta, elija “Extremo interno”

 

Paso #8: Mover la Serie 1 “Ingresos Objetivo” al Eje Secundario.

Para moldear el gráfico en forma de termómetro, necesita colocar la Serie 1 “Ingresos Objetivo” (E2) en la posición correcta.

1. Haga clic con el botón derecho en la Series 1 “Ingresos Objetivo” (la transparente en la parte inferior).
2. Elija “Formatear Serie de Datos”

 

En el panel de tareas que aparece, bajo Trazar Serie en, elija “Eje Secundario”

 

Paso #9: Remover el Eje Secundario.

El eje secundario hizo su trabajo, así que ya no lo necesitamos.

Haga clic con el botón derecho en el eje secundario (la columna de números de la derecha) y seleccione “Eliminar”

 

Paso #10: Modificar los Rangos de Escala del Eje Primario.

Como habrá notado, la escala del eje primario luce un poco desalineada. Cambiemos eso.

Haga clic derecho en la escala del eje primario y elija “Dar formato al eje…”

 

Establezca el valor de Límites mínimos en -10000. ¿Por qué? Si lo hace, liberará espacio para el bulbo del termómetro.

Este valor varía en función de los datos de su gráfico. Una buena regla general es utilizar su número objetivo y dividirlo por dos.

Por ejemplo, supongamos que su objetivo de ingresos es de 100.000 dólares. Entonces su valor de Límites Mínimos sería -50000.

 

Paso #11: Cambiar el Formato Numérico de la Escala del Eje Primario y Añada Marcas de Graduación.

Esos números negativos no nos ayudan aquí. Afortunadamente, podemos deshacernos de ellos fácilmente usando el poder de los formatos numéricos personalizados.

Con el panel de tareas Formato de Eje aún abierto, en Opciones de Eje, navegue hasta la sección Números y haga lo siguiente:

1. Para “Categoría”,seleccione ””
2. Escriba “$#.##0,00;;$0” en el campo Código de Formato y haga clic en Añadir.

Además, en Marcas de graduación, seleccione “Dentro” en el menú desplegable “Tipo principal”.

 

Ahora, cambie a la pestaña Relleno y Línea. Cambia el color de las marcas de graduación a negro y ajusta el valor de Ancho a 1,5 pt.

 

Paso #12: Eliminar el Título del Gráfico, las Líneas de la Cuadrícula y el Eje Horizontal.

Elimine los elementos del gráfico que no tienen ningún valor práctico: el título del gráfico, las líneas de la cuadrícula y el eje horizontal. Haga clic derecho en cada elemento y seleccione “Eliminar”

 

Paso #13: Cambiar el Ancho de la Separación de las Series de Datos en el Eje Secundario.

Mientras le da los toques finales a su gráfico, adelgace el tubo del termómetro. Comience con la serie colocada en el eje secundario (Serie 1 “Ingresos Objetivo”).

Haga clic con el botón derecho del ratón en la Serie 1 “Ingresos previstos”(E2), abra el panel de tareas Formato de serie de datos y establezca el valor de Ancho de separación en 250%. Aumente el porcentaje si desea reducirlo más.

 

Paso #14: Cambiar el Ancho del Espacio de la Serie de Datos en el Eje Primario.

Después de eso, haga clic en el borde del tubo rojo una vez más para seleccionar la Serie 2 “Fórmula de Ingresos Totales” (E3), colocada en el eje primario. Establezca el mismo valor de Ancho de Brecha (250%).

 

Paso #15: Agregar el Bulbo del Termómetro al Gráfico.

Como ajuste final, cree y agregue el bulbo del termómetro al gráfico.

1. Cambie a Insertar.
2. Elija el botón “Ilustraciones”.
3. Elija el botón “Formas”.
4. Escoja “Elipse”

 

Mantén pulsada la tecla Mayús mientras dibujas para crear un círculo perfecto. A continuación, haz clic con el botón derecho en el círculo y, en el menú que aparece, cambia los colores Contorno de forma y Relleno de forma a rojo.

 

Una última cosa. Copia el círculo seleccionándolo y pulsando Ctrl + C. A continuación, selecciona el gráfico y pulsa Ctrl + V para pegar el círculo en el gráfico del termómetro. Por último, cambia el tamaño y ajusta la posición de la bombilla para que encaje en la imagen.

 

Ahí lo tienes. Ya está listo para crear impresionantes gráficos de termómetros en Excel