Cree un gráfico de mapa de calor en Excel

En Excel, un gráfico de mapa de calor tiene forma de tabla y ofrece una representación visual que muestra una vista comparativa de un conjunto de datos. Si su hoja de cálculo contiene una gran cantidad de datos, le resultará muy difícil identificar rápidamente los valores más altos o más bajos; sin embargo, en un mapa de calor, las celdas se muestran con distintos matices de color, lo que permite detectar de forma inmediata y sencilla los valores máximos y mínimos, tal como se aprecia en la siguiente captura de pantalla.

• Cree un gráfico sencillo de mapa de calor con Usar formato condicional
• Cree un gráfico dinámico de mapa de calor en Excel
• Ejemplo 1: Cree un mapa de calor dinámico mediante la barra de desplazamiento
• Ejemplo 2: Cree un mapa de calor dinámico mediante Botón de Opción
• Ejemplo 3: Cree un mapa de calor dinámico mediante Casilla de Verificación
• Descargue el archivo de ejemplo del gráfico de mapa de calor
• Vídeo: Cree un gráfico de mapa de calor en Excel

Cree un gráfico sencillo de mapa de calor con Usar formato condicional

Excel no dispone de una herramienta directa para crear mapas de calor, pero con la potente función Usar formato condicional, podrá crear rápidamente un mapa de calor siguiendo estos pasos:

1. Seleccione el rango de datos al que desea aplicar el uso de formato condicional.

2. A continuación, haga clic en Inicio > Usar formato condicional > Escalas de colory elija el estilo que necesite en el menú desplegable expandido a la derecha (en este caso, seleccionaré la)escala de color verde – amarillo – rojo). Vea la captura de pantalla:

3.Ya se ha creado el mapa de calor, que resalta las celdas según sus valores: el verde indica los valores más altos, el rojo los más bajos y los intermedios se muestran con un degradado entre ambos colores. Vea la captura de pantalla:

4. Si desea ocultar los números y mostrar únicamente los colores, seleccione el rango de datos y pulse las teclas Ctrl + 1 para abrir el cuadro de diálogo Establecer formato de celda.

5. En el cuadro de diálogo Establecer formato de celda, en la pestaña Número, haga clic en la opción Personalizada del cuadro Categoría de la izquierda y, a continuación, introduzca ;;; en el cuadro Tipo. Vea la captura de pantalla:

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

Nota: Para resaltar las celdas con otros colores de su preferencia, seleccione el rango Inicio>Usar formato condicional>Administrar reglas para acceder al cuadro de diálogo Usar formato condicional Administrar reglas.

A continuación, haga doble clic en la regla existente para abrir el cuadro de diálogo Editar regla de formato y reconfigure la regla según sus necesidades. Vea la captura de pantalla:

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

Ejemplo 1: Cree un mapa de calor dinámico mediante la barra de desplazamiento

Si su hoja de cálculo incluye varias columnas de datos pero necesita mostrarlas en un espacio reducido, puede insertar una barra de desplazamiento para que el mapa de calor sea dinámico, tal como se muestra en la siguiente demostración.

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

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

2. A continuación, haga clic en Programador > Insertar > Barra de desplazamiento. Vea la captura de pantalla:

3. A continuación, arrastre el cursor para dibujar una barra de desplazamiento debajo de los datos copiados, haga clic con el botón derecho en la barra y seleccione Formato del control. Vea la captura de pantalla:

4. En el cuadro de diálogo Formato de objeto, en la pestaña Control, configure el valor mínimo, el valor máximo, el incremento, el cambio de página y la celda vinculada según su rango de datos, tal como se muestra en la siguiente captura de pantalla:

5. A continuación, haga clic en Aceptar para cerrar este cuadro de diálogo.

6. Ahora, en la celda B1 de esta nueva hoja, introduzca la siguiente fórmula y pulse la tecla Intro para obtener el primer resultado:

Nota: En la fórmula anterior, data1!$B$1:$I$13 es el rango de datos original en la hoja, excluyendo la cabecera de filas (meses); $I$1 es la celda vinculada a la barra de desplazamiento, y $B$1:B1 es la celda donde introduce la fórmula.

7. A continuación, arrastre esta celda con la fórmula al resto de las celdas. Si desea mostrar únicamente 3 años en la hoja de cálculo, arrastre la fórmula desde B1 hasta D13. Vea la captura de pantalla:

8. A continuación, aplique la función Escala de color de la herramienta Usar formato condicional al nuevo rango de datos para crear el mapa de calor. Al arrastrar la barra de desplazamiento, ¡el mapa de calor se actualizará dinámicamente! Vea la captura de pantalla:

Ejemplo 2: Cree un mapa de calor dinámico mediante Botón de Opción

También puede crear un mapa de calor dinámico mediante un botón de opción. Al seleccionar un botón de opción, se resaltarán los *n* valores más altos; al seleccionar otro, se resaltarán los *n* valores más bajos, tal como se muestra en la siguiente demostración:

Para completar este tipo de mapa de calor dinámico, proceda de la siguiente manera:

1. Haga clic en Programador > Insertar > Botón de opción (control de formulario) y, a continuación, arrastre el cursor para dibujar dos botones de opción y edite el texto según sus necesidades. Vea la captura de pantalla:

2. Tras insertar el Botón de Opción, haga clic con el botón derecho en el primero y seleccione Formato de control. En el cuadro de diálogo Formato de control, en la pestaña Control, seleccione una celda vinculada al Botón de Opción. Vea la captura de pantalla:

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

4. A continuación, debe aplicar el formato condicional al 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 que determine qué celdas se deben dar formato en el cuadro de lista Seleccionar un tipo de regla y, a continuación, introduzca esta fórmula: =SI($M$1=1;SI(B2>=K.ÉSIMO.MAYOR($B$2:$I$13;15);VERDADERO;FALSO)) en el cuadro de texto Dar formato a los valores para los que la fórmula es verdadera y, después, haga clic en el botón Formato para seleccionar un color. Vea la captura de pantalla:

6. Haga clic en el botón Aceptar. Al seleccionar la primera opción, se resaltarán automáticamente en rojo los 15 valores más altos.

7. Para resaltar los 15 valores más bajos, mantenga los datos seleccionados y vaya al cuadro de diálogo Nueva regla de formato. A continuación, escriba esta fórmula: =SI($M$1=2;SI(B2<=K.ÉSIMO.MENOR($B$2:$I$13;15);VERDADERO;FALSO)) en el cuadro de texto Dar formato a los valores para los que la fórmula es verdadera y haga clic en el botón Formato para elegir otro color según sus necesidades. Vea la captura de pantalla:

Nota: En las fórmulas anteriores, $M$1 es la celda vinculada al Botón 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 y el número 15 es la cantidad específica 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, se resaltarán los 15 valores más altos; y al seleccionar el segundo Botón de Opción, se resaltarán los 15 valores más bajos, tal como se muestra en la siguiente demostración:

Ejemplo 3: Cree un mapa de calor dinámico mediante Casilla de Verificación

En esta sección, presento un gráfico de mapa de calor dinámico controlado mediante una casilla de verificación, que le permite mostrar u ocultar el mapa de calor según sus necesidades: al marcarla, el mapa de calor aparece; al desmarcarla, desaparece al instante. Vea la siguiente demostración:

2. En primer lugar, convierta su rango de datos en una tabla. Así, al insertar nuevas filas, el formato condicional se aplicará automáticamente. Seleccione el rango de datos y pulse simultáneamente las teclas Ctrl + T 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, a continuación, haga clic en Desarrollador > Insertar > Casilla de verificación (Control de formulario). Luego, arrastre el ratón para dibujar una casilla de verificación y edite el texto según sus necesidades, tal como se muestra en las siguientes capturas de pantalla:

3. A continuación, haga clic con el botón derecho en la casilla de verificación y seleccione Formato de control. En el cuadro de diálogo Formato de objeto, en la pestaña Control, seleccione una celda vinculada a la casilla de verificación. Vea la captura de pantalla:

4. Haga clic en Aceptar para cerrar el cuadro de diálogo. A continuación, seleccione el rango de datos con el que desea crear el mapa de calor y haga clic en Inicio > Usar formato condicional > Nueva regla para abrir el cuadro de diálogo Nueva regla de formato.

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

• Seleccione Dar formato a todas las celdas según sus valoresen el cuadro Seleccionar un tipo de reglalista;
• Elija Escala de 3 coloresen la lista Estilo de formatodesplegable;
• Seleccione Fórmulaen los cuadros Tiposituados bajo las listas Mínimo,Punto medioy Máximodesplegables por separado;
• A continuación, introduzca las siguientes fórmulas en los tres cuadros ValorCuadro de texto:
• Mínimo:=SI($M$1=VERDADERO;MIN($B$2:$I$13);FALSO)
• Punto medio:=SI($M$1=VERDADERO;PROMEDIO($B$2:$I$13);FALSO)
• Máximo:=SI($M$1=VERDADERO;MAX($B$2:$I$13);FALSO)
• Después, especifique los colores de resaltado en 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 y $B$2:$I$13 es el rango de datos al que desea aplicar el formato condicional.

6. Una vez finalizada la configuración, haga clic en el botón Aceptar para cerrar el cuadro de diálogo. A partir de ahora, si marca la casilla de verificación, se mostrará el mapa de calor; de lo contrario, permanecerá oculto. Vea la siguiente demostración:

Descargue el archivo de ejemplo del gráfico de mapa de calor

Vídeo: Crear un gráfico de mapa de calor en Excel