Crear gráfico de mapa de calor en Excel

En Excel, un gráfico de mapa de calor se parece a una tabla que es una representación visual que muestra una vista comparativa de un conjunto de datos. Si hay un gran conjunto de datos en su hoja de cálculo, es realmente difícil identificar los valores más bajos o más altos de un vistazo, pero, en el mapa de calor, el valor de la celda se muestra en un patrón de color diferente para que podamos ver los datos más grandes o más pequeños rápidamente y fácilmente, como se muestra en la siguiente captura de pantalla.

• Crear un gráfico de mapa de calor simple con Formato Condicional
• Crear un gráfico de mapa de calor dinámico en Excel
• Ejemplo 1: Crear un mapa de calor dinámico usando una barra de desplazamiento
• Ejemplo 2: Crear un mapa de calor dinámico usando botones de opción
• Ejemplo 3: Crear un mapa de calor dinámico usando una casilla de verificación
• Descargar archivo de ejemplo de Gráfico de Mapa de Calor
• Video: Crear gráfico de mapa de calor en Excel

Crear un gráfico de mapa de calor simple con Formato Condicional

No hay una utilidad directa de gráfico de mapa de calor en Excel, pero, con la poderosa función de Formato Condicional, puede crear rápidamente un mapa de calor, por favor, siga los siguientes pasos:

1. Seleccione el rango de datos al que desea aplicar el Formato Condicional.

2. Luego haga clic en Inicio > Formato Condicional > Escalas de Color, y luego elija un estilo que necesite desde la lista desplegable expandida a la derecha (en este caso, seleccionaré Escala de Color Verde - Amarillo - Rojo), vea la captura de pantalla:

3. Ahora, el mapa de calor está creado, lo que resalta las celdas según sus valores; el color verde representa los valores más altos y el color rojo representa los valores más bajos, y los valores restantes muestran un color degradado entre verde y rojo. Vea la captura de pantalla:

4. Si desea ocultar los números y solo dejar los colores, seleccione el rango de datos y presione Ctrl + 1 para abrir el cuadro de diálogo Formato de Celdas.

5. En el cuadro de diálogo Formato de Celdas, bajo la pestaña Número, haga clic en la opción Personalizado en el cuadro de lista Categoría a la izquierda, y luego ingrese ;;; en el cuadro de texto Tipo, vea la captura de pantalla:

6. Luego, haga clic en el botón Aceptar, y todos los números están ocultos como se muestra en la siguiente captura de pantalla:

Nota: Para resaltar las celdas con otros colores que desee, seleccione el rango de datos, y luego haga clic en Inicio > Formato Condicional > Administrar Reglas para ir al cuadro de diálogo Administrador de Reglas de Formato Condicional.

Luego, haga doble clic en la regla existente para abrir el cuadro de diálogo Editar Regla de Formato, y luego reestablezca la regla según sus necesidades, vea la captura de pantalla:

Crear un gráfico de mapa de calor dinámico en Excel

Ejemplo 1: Crear un mapa de calor dinámico usando una barra de desplazamiento

Si hay múltiples columnas de datos en su hoja de cálculo, pero desea mostrarlas en un espacio limitado, en este caso, puede insertar una barra de desplazamiento en la hoja de cálculo para hacer que el mapa de calor sea dinámico, como se muestra en la siguiente demostración.

Para crear este tipo de gráfico de mapa de calor dinámico, siga los siguientes pasos:

1. Inserte una nueva hoja de cálculo, y luego copie la primera columna de meses de la hoja original a esta nueva hoja.

2. Luego, haga clic en Desarrollador > Insertar > Barra de Desplazamiento, vea la captura de pantalla:

3. Luego, arrastre el mouse para dibujar una barra de desplazamiento debajo de los datos copiados, y haga clic derecho en la barra de desplazamiento, y seleccione Formato de Control, vea la captura de pantalla:

4. En el cuadro de diálogo Formato de Objeto, bajo la pestaña Control, establezca el valor mínimo, valor máximo, cambio incremental, cambio de página y una celda vinculada basada en su rango de datos como se muestra en la siguiente captura de pantalla:

5. Luego haga clic en Aceptar para cerrar este cuadro de diálogo.

6. Ahora, en la celda B1 de esta nueva hoja, por favor ingrese la siguiente fórmula, y presione la tecla Entrar para obtener el primer resultado:

Nota: En la fórmula anterior, data1!$B$1:$I$13 es la hoja original con el rango de datos excluyendo el encabezado de fila (meses), $I$1 es la celda a la que la barra de desplazamiento está vinculada, $B$1:B1 es la celda donde se genera la fórmula.

7. Luego, arrastre esta celda de fórmula al resto de las celdas, si desea mostrar solo 3 años en la hoja de cálculo, arrastre la fórmula de B1 a D13, vea la captura de pantalla:

8. Y luego, aplique la Escala de Color de la característica de Formato Condicional al nuevo rango de datos para crear el mapa de calor, ahora, cuando arrastre la barra de desplazamiento, el mapa de calor se moverá dinámicamente, vea la captura de pantalla:

Ejemplo 2: Crear un mapa de calor dinámico usando Botones de Opción

También puede crear un mapa de calor dinámico usando botones de opción, seleccionar un botón de opción resaltará los n valores más grandes, y seleccionar otro botón de opción resaltará los n valores más pequeños como se muestra en la siguiente demostración:

Para terminar este tipo de mapa de calor dinámico, por favor haga lo siguiente:

1. Haga clic en Desarrollador > Insertar > Botón de Opción (Control de Formulario), luego, arrastre el mouse para dibujar dos botones de opción, y edite el texto según sus necesidades, vea la captura de pantalla:

2. Después de insertar los botones de opción, haga clic derecho en el primero, y seleccione Formato de Control, en el cuadro de diálogo Formato de Control, bajo la pestaña Control, seleccione una celda que esté vinculada al botón de opción, vea la captura de pantalla:

3. Haga clic en Aceptar para cerrar el cuadro de diálogo, y luego repita el paso anterior (paso 2) para vincular el segundo botón de opción a la misma celda (celda M1) también.

4. Y luego, debe aplicar el formato condicional para el rango de datos, seleccione el rango de datos, y haga clic en Inicio > Formato Condicional > Nueva Regla, vea la captura de pantalla:

5. En el cuadro de diálogo Nueva Regla de Formato, seleccione Usar una Fórmula para determinar qué celdas Formatear desde el cuadro de lista Seleccionar un Tipo de Regla, y luego ingrese esta fórmula: =IF($M$1=1,IF(B2>=LARGE($B$2:$I$13,15),TRUE,FALSE)) en el cuadro de texto Formatear valores donde esta fórmula es verdadera y luego haga clic en el botón Formato para seleccionar un color. Vea la captura de pantalla:

6. Haga clic en Aceptar, esto resaltará los 15 valores más grandes con color rojo cuando seleccione el primer botón de opción.

7. Para resaltar los 15 valores más pequeños, mantenga los datos seleccionados e ingrese al cuadro de diálogo Nueva Regla de Formato, y luego escriba esta fórmula: =IF($M$1=2,IF(B2<=SMALL($B$2:$I$13,15),TRUE,FALSE)) en el cuadro de texto Formatear valores donde esta fórmula es verdadera, y haga clic en el botón Formato para elegir otro color que necesite. Vea la captura de pantalla:

Nota: En las fórmulas anteriores, $M$1 es la celda vinculada a los botones de opción, $B$2:$I$13 es el rango de datos al que desea aplicar el formato condicional, B2 es la primera celda del rango de datos, el número 15 es el número específico que desea resaltar.

8. Haga clic en Aceptar para cerrar el cuadro de diálogo, ahora, al seleccionar el primer botón de opción, los 15 valores más grandes se resaltarán, y al seleccionar el segundo botón de opción, los 15 valores más pequeños se resaltarán como se muestra en la siguiente demostración:

Ejemplo 3: Crear un mapa de calor dinámico usando Casilla de Verificación

En esta sección, presentaré un gráfico de mapa de calor dinámico usando una casilla de verificación que puede ayudarlo a mostrar u ocultar el mapa de calor según sus necesidades. Si marca la casilla de verificación, el mapa de calor se mostrará; si la desmarca, se ocultará de inmediato, vea la siguiente demostración:

1. Primero, debe convertir su rango de datos a un formato de tabla, lo que puede ayudarlo a aplicar el formato condicional automáticamente al insertar una nueva fila de datos. Seleccione el rango de datos, y luego presione Ctrl + T juntas para abrir el cuadro de diálogo Crear Tabla, vea la captura de pantalla:

2. Haga clic en Aceptar para cerrar el cuadro de diálogo, y luego haga clic en Desarrollador > Insertar > Casilla de Verificación (Control de Formulario), luego, arrastre el mouse para dibujar una casilla de verificación y edite el texto según sus necesidades como se muestra en las siguientes capturas de pantalla:

3. Luego, haga clic derecho en la casilla de verificación, y seleccione Formato de Control, en el cuadro de diálogo Formato de Objeto, bajo la pestaña Control, seleccione una celda que esté vinculada a la casilla de verificación, vea la captura de pantalla:

4. Haga clic en Aceptar para cerrar el cuadro de diálogo, luego, seleccione el rango de datos que desea crear el mapa de calor, y haga clic en Inicio > Formato Condicional > Nueva Regla para ir al cuadro de diálogo Nueva Regla de Formato.

5. En el cuadro de diálogo Nueva Regla de Formato, realice las siguientes operaciones:

• Seleccione la opción Formatear todas las celdas según sus valores desde el cuadro de lista Seleccionar un Tipo de Regla;
• Elija Escala de 3 Colores desde la lista desplegable Estilo de Formato;
• Seleccione Fórmula en las casillas Tipo bajo las listas desplegables Mínimo, Punto Medio y Máximo respectivamente;
• Y luego, ingrese las siguientes fórmulas en las tres casillas Valor:
• Mínimo: =IF($M$1=TRUE,MIN($B$2:$I$13),FALSE)
• Punto Medio: =IF($M$1=TRUE,AVERAGE($B$2:$I$13),FALSE)
• Máximo: =IF($M$1=TRUE,MAX($B$2:$I$13),FALSE)
• Luego, especifique los colores de resaltado de la sección Color según sus necesidades.

Nota: En las fórmulas anteriores, $M$1 es la celda vinculada a la casilla de verificación, $B$2:$I$13 es el rango de datos al que desea aplicar el formato condicional.

6. Después de finalizar la configuración, haga clic en el botón Aceptar para cerrar el cuadro de diálogo, ahora, cuando marque la casilla de verificación, el mapa de calor se mostrará, de lo contrario, se ocultará. Vea la siguiente demostración:

Descargar archivo de ejemplo de Gráfico de Mapa de Calor

Video: Crear gráfico de mapa de calor en Excel