Tabla de termómetros de Excel: descarga gratuita y cómo crear

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

Un gráfico de termómetro es una modificación especial de un gráfico de columnas apiladas. Las columnas se colocan una encima de la otra y visualmente se asemejan a un termómetro, que se llena a medida que avanza hacia su objetivo.

Siempre que necesite realizar 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 gráfico de termómetro ya no corta la mostaza (¡vamos, esto es 2022!).

En este tutorial detallado paso a paso, aprenderá a trazar un gráfico de objetivos de termómetro notablemente versátil con una etiqueta elegante que funciona incluso cuando sus resultados superan un objetivo establecido.

Solo compruébalo:

Como verá en breve, construir una tabla de termómetro impresionante requiere un gran esfuerzo. Es por eso que diseñamos el complemento Chart Creator, una herramienta para crear gráficos avanzados de Excel con solo unos pocos clics.

Entonces, vayamos al meollo de la cuestión.

Empezando

Con fines ilustrativos, imagine que lanzó una tienda de comercio electrónico como una actividad secundaria y desea crear un gráfico de termómetro para realizar un seguimiento del rendimiento de la tienda en relación con su objetivo de ingresos establecido. En 2022, tuvo una buena racha.

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

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

Antes de continuar con la creación del gráfico, necesitamos desglosar cada punto de datos para que pueda volver sobre los pasos fácilmente utilizando sus datos.

El conjunto de datos tiene dos columnas:

• Datos originales: Esta columna representa sus datos reales, sean los que sean. Déjalo como está.
• Entradas de gráfico: Aquí es donde ocurre toda la magia. La tabla contiene todos los valores especiales que utilizará para crear el gráfico. Además, una nota importante: Las categorías deben ordenarse exactamente de la misma manera que se muestra en la captura de pantalla anterior.

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

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

¡Así comienza el viaje!

Paso # 1: Configure la mesa de ayuda.

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

Ingresos objetivo: Este punto de datos debe coincidir con el valor del objetivo (B7). Tan simple como eso.

Fórmula de ingresos totales: Esta función SI evita que los marcadores de datos se apilen uno encima del otro de forma incorrecta en caso de que el resultado supere el objetivo:

1	= SI (B6 <= B7, B6, B7)

Aquí está la versión decodificada basada en los datos originales:

1	= SI ({Total} <= {Objetivo}, {Total}, {Objetivo})

Traducción al lenguaje humano básico: si el resultado (B6) es menor o igual que el objetivo (B7), la fórmula devuelve el valor del resultado (B6) -en nuestro caso, cualquier cosa que caiga por debajo de la marca de $ 20K. Por el contrario, si el resultado (B6) supera la meta (B7), la fórmula devuelve el valor del objetivo (B7).

Consejo rápido: haga clic en "Formato de número de contabilidad " botón (Pestaña de inicio > Grupo de números) para mostrar la salida de la función como moneda.

Sobrealimentado: Esta Función SI calcula si su objetivo se ha superado o no. Siempre que eso suceda, la fórmula resta el objetivo (B7) del resultado (B6) y devuelve lo que sobra. De lo contrario, la fórmula simplemente muestra cero:

1	= SI (B7 <= B6, B6-B7, 0)

La versión decodificada:

1	= SI ({Objetivo} <= {Total}, {Total} - {Objetivo}, 0)

Progreso: Este se explica por sí mismo. Necesitamos medir el progreso como un porcentaje del objetivo de nuestra etiqueta ultra elegante. Simplemente divide el resultado (B6) por la meta (B7) y formatee la salida como un porcentaje; haga clic en el botón "Estilo de porcentaje " botón (Pestaña de inicio > Grupo de números).

1

= B6 / B7

Versión decodificada:

1	= {Total} / {Objetivo}

Etiqueta: La guinda del pastel. Éste combina los valores numéricos y porcentuales que representan el progreso hacia su objetivo y los formatea cuidadosamente. Para llevarlo a cabo, pasaremos a la función TEXTO (principiantes de Excel, prepárense para el impacto):

1	= TEXTO (B6, "$ # 0.00") & "(" & TEXTO (E5, "## 0%") & ")"

Y la versión decodificada:

1	= TEXTO ({Total}, "{Dar formato al valor como moneda}") & "(" & TEXTO ({Progreso}, "{Dar formato al valor como porcentajes}") & ")"

Si eso parece desalentador, consulte el artículo vinculado anteriormente que cubre la función.

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

Vaya, lo peor ha quedado atrás. Para resumir todo, eche un segundo vistazo a las fórmulas:

Configuración de 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	= TEXTO (B6, “$ # 0.00 ″) &” (”& TEXTO (E5,“ ## 0% ”) &“) ”

Una vez allí, continúe con el siguiente paso.

Paso 2: cree un gráfico de columnas apiladas.

1. Resalta el rango de celdas E2: E4 en el Valor
2. Selecciona el Insertar
3. Haga clic en el "Insertar gráfico de columnas o barras”Icono.
4. Escoger "Columna apilada.”

Paso 3: apile las series de datos una encima de la otra.

Una vez que aparezca el gráfico, reorganice la serie de datos:

1. Haga clic en el gráfico.
2. Cambie al Diseño
3. Escoger "Cambiar fila / columna.”

Paso # 4: Cambie los colores de los marcadores de datos.

Para convertir el gráfico en algo parecido a un termómetro, comience cambiando el color de los marcadores de datos. Primero, cree el marcador de datos para la Serie 1 "Ingresos objetivo" (E2) transparente y poner 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-la sección inferior del gráfico) y seleccione "Dar formato a la serie de datos.”

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

1. Navega al Rellenar línea
2. Debajo "Llenar," Seleccione "Sin relleno.”
3. Debajo "Frontera," escoger "Línea sólida.”
4. Debajo "Frontera," haga clic en el Color de relleno icono para abrir la paleta de colores y seleccionar rojo.
5. Selecciona el Ancho valor para 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 Dar formato a la serie de datos el panel de tareas aún está abierto.
   2. Debajo "Llenar," escoger "Relleno sólido.”
   3. Haga clic en el Color de relleno icono y seleccione rojo.
2. Para la Serie 3 "Excedido" (E4):
   1. Seleccione el marcador de datos (E4) con el Dar formato a la serie de datos el panel de tareas aún está abierto.
   2. Debajo "Llenar," escoger "Relleno sólido.”
   3. Haga clic en el Color de relleno icono y seleccione verde claro.

Paso # 5: agregue la etiqueta de datos al gráfico.

Ahora es el momento de insertar la hermosa etiqueta de datos (E6) que nos tomó tanta sangre, sudor y lágrimas para armar.

Haga clic con el botón derecho en la Serie 2 "Fórmula de ingresos totales" (el que tiene un relleno rojo) y elige "Agregar etiquetas de datos.”

Paso # 6: Cambie el valor de la etiqueta de datos.

Vincula la etiqueta al valor de la segunda tabla.

1. Haz doble clic en la etiqueta.
2. Escribe “=” en el Barra de formulas.
3. Seleccione el valor de la etiqueta (E6).

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

Paso # 7: Vuelva a colocar la etiqueta de datos.

Una vez que haya configurado la etiqueta, empújela hacia el extremo superior del marcador de datos relacionado. Haga clic con el botón derecho en la etiqueta de datos y elija "Dar formato a las etiquetas de datos.”

Entonces, en el Opciones de etiqueta pestaña, debajo Posición de la etiqueta, escoger "Extremo interior.”

Paso # 8: Mueva la Serie 1 “Ingresos objetivo” al eje secundario.

Para moldear la tabla en forma de termómetro, debe obtener Serie 1 "Ingresos objetivo" (E2) en la posición correcta.

1. Haga clic derecho en Serie 1 "Ingresos objetivo" (el transparente en la parte inferior).
2. Elegir "Dar formato a la serie de datos.”

En el panel de tareas que aparece, debajo de Trazar serie en, escoger "Eje secundario.”

Paso # 9: Retire el eje secundario.

El eje secundario hizo su trabajo, por lo 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 "Borrar.”

Paso # 10: Modifique los rangos de escala del eje primario.

Como habrás notado, la escala del eje primario parece un poco fuera de lugar. Cambiemos eso.

Haga clic con el botón derecho en la escala del eje principal y seleccione "Eje de formato.”

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

Este valor difiere según los datos de su gráfico. Una buena regla general es usa tu número de objetivo y divídelo por dos.

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

Paso # 11: Cambie el formato numérico de la escala del eje primario y agregue marcas de graduación.

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

Con el Eje de formato el panel de tareas aún está abierto, debajo Opciones de eje, navega hasta el Números sección y haga lo siguiente:

1. Para "Categoría," Seleccione ""
2. Escribe "$#,##0.00;;$0" en el Código de formato campo y haga clic en Agregar.

Además, bajo Marcas de graduación, escoger "Dentro" desde el "Tipo mayor" Menú desplegable.

Ahora, cambie al Rellenar línea pestaña. Cambie el color de las marcas de graduación a negro y establezca el Ancho valor para 1,5 pt.

Paso # 12: Elimina el título del gráfico, las líneas de 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 cuadrícula y el eje horizontal. Haga clic con el botón derecho en cada elemento y seleccione "Borrar.”

Paso # 13: Cambie el ancho del espacio de la serie 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 en la Serie 1 "Ingresos objetivo" (E2), abre el Dar formato a la serie de datos panel de tareas y configure el Anchura de rendija valor para 250%. Aumente el porcentaje si desea adelgazar más.

Paso # 14: Cambie 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 Serie 2 "Fórmula de ingresos totales” (E3), colocado en el eje primario. Establecer lo mismo Anchura de rendija valor (250%).

Paso # 15: Agregue la bombilla del termómetro a la tabla.

Como ajuste final, cree y agregue el bulbo del termómetro a la tabla.

1. Cambie al Insertar
2. Elegir el "Formas" botón.
3. Elegir "Oval.”

Sujetar Cambio mientras dibuja para crear un círculo perfecto. Luego haga clic con el botón derecho en el círculo y, en el menú que aparece, cambie el Contorno de forma y Relleno de forma colores a rojo.

Solo una última cosa. Copia el círculo seleccionándolo y presionando Ctrl + C. Luego seleccione el gráfico y presione Ctrl + V para pegar el círculo en la tabla del termómetro. Finalmente, cambie el tamaño y ajuste la posición de la bombilla para que encaje en la imagen.

Así que ahí lo tienes. ¡Ahora está listo para crear gráficos de termómetros impresionantes en Excel!