Un mapa de calor en Excel es una representación visual que le muestra rápidamente una vista comparativa de un conjunto de datos.
Por ejemplo, en el conjunto de datos a continuación, puedo identificar fácilmente cuáles son los meses en los que las ventas fueron bajas (resaltadas en rojo) en comparación con otros meses.
En el conjunto de datos anterior, los colores se asignan según el valor de la celda. La escala de colores es de verde a amarillo a rojo con valores altos obteniendo el color verde y valores bajos obteniendo el color rojo.
Crear un mapa de calor en Excel
Si bien puede crear un mapa de calor en Excel codificando manualmente las celdas con colores. Sin embargo, tendrá que rehacerlo cuando cambien los valores.
En lugar del trabajo manual, puede usar formato condicional para resaltar celdas según el valor. De esta manera, en caso de que cambie los valores en las celdas, el color / formato de la celda actualizará automáticamente el mapa de calor según las reglas preestablecidas en formato condicional.
En este tutorial, aprenderá a:
- Cree rápidamente un mapa de calor en Excel utilizando formato condicional.
- Crea un mapa de calor dinámico en Excel.
- Cree un mapa de calor en tablas dinámicas de Excel.
¡Empecemos!
Crear un mapa de calor en Excel usando formato condicional
Si tiene un conjunto de datos en Excel, puede resaltar manualmente los puntos de datos y crear un mapa de calor.
Sin embargo, eso sería un mapa de calor estático, ya que el color no cambiaría cuando modificara el valor en una celda.
Por lo tanto, el formato condicional es el camino correcto a seguir, ya que hace que el color de una celda cambie cuando cambia el valor en ella.
Suponga que tiene un conjunto de datos como se muestra a continuación:
Estos son los pasos para crear un mapa de calor con estos datos:
- Seleccione el conjunto de datos. En este ejemplo, sería B2: D13.
- Vaya a Inicio -> Formato condicional -> Escalas de color. Muestra varias combinaciones de colores que se pueden utilizar para resaltar los datos. La escala de colores más común es la primera en la que las celdas con valores altos se resaltan en verde y las bajas en rojo. Tenga en cuenta que al pasar el mouse sobre estas escalas de color, puede ver la vista previa en vivo en el conjunto de datos.
Esto le dará un mapa de calor como se muestra a continuación:
De forma predeterminada, Excel asigna el color rojo al valor más bajo y el color verde al valor más alto, y todos los valores restantes obtienen un color basado en el valor. Entonces, hay un degradado con diferentes tonos de los tres colores según el valor.
Ahora, ¿qué pasa si no quiere un degradado y solo quiere mostrar rojo, amarillo y verde? Por ejemplo, desea resaltar todos los valores inferiores a, digamos, 700 en rojo, independientemente del valor. Por lo tanto, 500 y 650 obtienen el mismo color rojo, ya que es menos de 700.
Para hacer esto:
- Vaya a Inicio -> Formato condicional -> Escalas de color -> Más opciones.
- En el cuadro de diálogo Nueva regla de formato, seleccione "Escala de 3 colores" en el menú desplegable Estilo de formato.
- Ahora puede especificar el valor mínimo, el punto medio y el máximo y asignarle el color. Como queremos resaltar todas las celdas con un valor por debajo de 700 en rojo, cambie el tipo a Número y el valor a 700.
- Haga clic en Aceptar.
Ahora obtendrá el resultado como se muestra a continuación. Tenga en cuenta que todos los valores por debajo de 700 obtienen el mismo tono de color rojo.
Una palabra de precaución: Si bien el formato condicional es una herramienta maravillosa, desafortunadamente, es volátil. Esto significa que siempre que hay algún cambio en la hoja de trabajo, el formato condicional se vuelve a calcular. Si bien el impacto puede ser insignificante en conjuntos de datos pequeños, puede conducir a un libro de Excel lento cuando se trabaja con conjuntos de datos grandes.
Crear un mapa de calor dinámico en Excel
Dado que el formato condicional depende del valor en una celda, tan pronto como cambie el valor, el formato condicional se recalcula y cambia.
Esto permite hacer un mapa de calor dinámico.
Veamos dos ejemplos de creación de mapas de calor utilizando controles interactivos en Excel.
Ejemplo 1: mapa de calor con barra de desplazamiento
Aquí hay un ejemplo donde el mapa de calor cambia tan pronto como usa la barra de desplazamiento para cambiar el año.
Este tipo de mapas de calor dinámicos se puede utilizar en paneles en los que tiene limitaciones de espacio pero aún desea que el usuario acceda a todo el conjunto de datos.
Haga clic aquí para descargar la plantilla Mapa de calor
¿Cómo crear este mapa de calor dinámico?
Aquí está el conjunto de datos completo que se utiliza para crear este mapa de calor dinámico.
Estos son los pasos:
- En una hoja nueva (o en la misma hoja), ingrese los nombres de los meses (simplemente cópielos y péguelos de los datos originales).
- Vaya a Desarrollador -> Controles -> Insertar -> Barra de desplazamiento. Ahora haga clic en cualquier parte de la hoja de trabajo e insertará una barra de desplazamiento. (haga clic aquí si no puede encontrar la pestaña de desarrollador).
- Haga clic con el botón derecho en la barra de desplazamiento y haga clic en Control de formato.
- En el cuadro de diálogo Control de formato, realice los siguientes cambios:
- Valor mínimo: 1
- Valor máximo 5
- Enlace de celda: Hoja1! $ J $ 1 (Puede hacer clic en el icono de la derecha y luego seleccionar manualmente la celda que desea vincular a la barra de desplazamiento).
- Haga clic en Aceptar.
- En la celda B1, ingrese la fórmula: = ÍNDICE (Hoja1! $ B $ 1: $ H $ 13, FILA (), Hoja1! $ J $ 1 + COLUMNAS (Hoja2! $ B $ 1: B1) -1)
- Cambie el tamaño y coloque la barra de desplazamiento en la parte inferior del conjunto de datos.
Ahora, cuando cambie la barra de desplazamiento, el valor en Sheet1! $ J $ 1 cambiaría, y dado que las fórmulas están vinculadas a esta celda, se actualizaría para mostrar los valores correctos.
Además, dado que el formato condicional es volátil, tan pronto como cambia el valor, también se actualiza.
Ver video - Mapa de calor dinámico en Excel
Ejemplo 2: Creación de un mapa de calor dinámico en Excel usando botones de opción
Aquí hay otro ejemplo en el que puede cambiar el mapa de calor haciendo una selección de botón de opción:
En este ejemplo, puede resaltar los 10 valores superiores / inferiores según la selección del botón de opción / radio.
Haga clic aquí para descargar la plantilla Mapa de calor
Crear un mapa de calor en una tabla dinámica de Excel
El formato condicional en las tablas dinámicas funciona de la misma manera que con cualquier dato normal.
Pero hay algo importante que debes saber.
Déjame darte un ejemplo y mostrarte.
Suponga que tiene una tabla dinámica como se muestra a continuación:
Para crear un mapa de calor en esta tabla dinámica de Excel:
- Seleccione las celdas (B5: D14).
- Vaya a Inicio -> Formato condicional -> Escalas de color y seleccione la escala de color que desea aplicar.
Esto crearía instantáneamente el mapa de calor en la tabla dinámica.
El problema con este método es que si agrega nuevos datos en el backend y actualiza esta tabla dinámica, el formato condicional no se aplicaría a los nuevos datos.
Por ejemplo, cuando agregué nuevos datos en el back-end, ajusté los datos de origen y actualicé la tabla dinámica, puede ver que el formato condicional no se le aplica.
Esto sucede cuando aplicamos el formato condicional solo a las celdas B5: D14.
Si desea que este mapa de calor sea dinámico de modo que se actualice cuando se agreguen nuevos datos, estos son los pasos:
- Seleccione las celdas (B5: D14).
- Vaya a Inicio -> Formato condicional -> Escalas de color y seleccione la escala de color que desea aplicar.
- Vaya nuevamente a Inicio -> Formato condicional -> Administrar reglas.
- En el Administrador de reglas de formato condicional, haga clic en el botón Editar.
- En el cuadro de diálogo Editar regla de formato, seleccione la tercera opción: Todas las celdas que muestran los valores de "Ventas" para "Fecha" y "Cliente".
Ahora, el formato condicional se actualizará cuando cambie los datos del backend.
Nota: El formato condicional desaparece si cambia los campos de fila / columna. Por ejemplo, si elimina el campo Fecha y lo vuelve a aplicar, se perderá el formato condicional.
