Mapas de calor en Excel

En esta publicación, recrearemos un mapa de calor presentado en Information Dashboard Design, segunda edición de Stephen Few, utilizando una tabla dinámica de Excel y un poco de formato condicional.

Objetivo

Antes de llegar demasiado lejos, echamos un vistazo a nuestro objetivo. Estamos intentando crear un mapa de calor que utiliza la variación en la intensidad del color para representar rangos de valores. La siguiente imagen es un mapa de calor parcial que muestra el consumo de alcohol por país durante el año 2010, y hoy descargué los datos del sitio web de la Organización Mundial de la Salud (OMS) utilizando el enlace que se proporciona a continuación.

Hay 3 pasos principales para crear este mapa de calor en Excel.

• Crear la tabla dinámica básica
• Aplicar formato condicional
• Formatear la tabla dinámica

Veámoslos uno por uno. Los siguientes pasos se demuestran utilizando Excel 2013 para Windows.

Crear la tabla dinámica básica

Antes de crear la tabla dinámica, echamos un vistazo rápido a los datos que descargamos del sitio web de la OMS. Las columnas que necesitaremos para nuestro mapa de calorías son País (Columna F), Tipos de bebidas (Columna G) y Valor numérico (Columna I).

Para crear la tabla dinámica, seleccione una celda dentro del rango de datos y haga clic en el icono Insertar Cinta de tabla dinámica. Coloque la tabla dinámica en una hoja de trabajo nueva o existente según lo desee. Luego, inserte el campo País en el área Filas, el campo Valor numérico en el área Valores y el campo Tipos de bebidas en el área Columnas. Luego, si Excel utiliza de forma predeterminada la función Contar para el campo Valor numérico, cambie la función a Suma haciendo clic derecho en cualquier celda del campo de valor y seleccionando Resumir valores por… Suma. Todos estos pasos se ilustran a continuación.

Ahora que existe la tabla dinámica básica, debemos realizar un par de modificaciones sencillas en la estructura.

Primero, eliminamos los totales del informe seleccionando Herramientas de tabla dinámica Diseño Totales generales Desactivado para filas y columnas.

Luego filtramos los países a los que les faltan datos haciendo clic en el menú desplegable de etiquetas de fila y seleccionando Filtros de valor Mayor que. En el cuadro de diálogo Filtro de valor resultante, le decimos a Excel que muestre los elementos para los cuales la suma del campo Valor numérico es mayor que 0, como se muestra en la captura de pantalla siguiente.

Colocamos las columnas en el orden deseado haciendo clic en las etiquetas de las columnas y arrastrándolas a su posición, como se muestra a continuación.

Actualizamos el orden de clasificación haciendo clic en una celda en la columna Bebidas espirituosas y haciendo clic en el icono Datos Cinta ZA.

Cambiamos la etiqueta de la columna “Otras bebidas alcohólicas” a “Otras” escribiendo “Otras” en la celda de la etiqueta de la columna.

El informe de tabla dinámica resultante se muestra a continuación.

Ahora que nuestra estructura básica de informe se ve bien, aplicamos los colores del mapa de calor con formato condicional.

Aplicar formato condicional

La función de formato condicional da formato a una celda según su valor. En nuestro caso, nos gustaría aplicar colores de relleno de celda según los siguientes rangos:

Para ello repetimos el siguiente paso 5 veces, una por cada rango. Seleccione cualquier celda de valor en la tabla dinámica y seleccione Inicio Formato condicional Nueva regla. En el cuadro de diálogo Nueva regla de formato resultante, le decimos a Excel que aplica la regla a todas las celdas que muestran valores de “Suma de valores numéricos” y que dé formato solo a las celdas que contienen un valor de celda entre los valores. del rango deseado. Hacemos clic en el botón Formato para establecer el relleno de la celda en el color deseado y para crear un borde de contorno de celda blanco.

Repetimos este paso hasta que tengamos cinco reglas de formato condicional individual, como se ilustra en el cuadro de diálogo Administrador de reglas de formato condicional que se muestra a continuación.

En este punto nos sentimos bastante bien porque podemos ver el mapa de calor tomando forma, como se muestra en la tabla dinámica resultante a continuación.

Ya sólo queda aplicar algunos retoques cosméticos.

Formatear la tabla dinámica

Lo limpiamos completando las siguientes tareas de formato.

• Desmarque la casilla de verificación Herramientas de tabla dinámica Diseño Encabezados de columna.
• Cambie el estilo haciendo clic en Herramientas de tabla dinámica Diseño Luz de estilo dinámico 15 (u otro estilo preferido)
• Desactive Herramientas de tabla dinámica Analizar Encabezados de campo
• Reemplace la etiqueta del informe “Suma de valor numérico” con un espacio
• Centrar y formatear las celdas del encabezado de la columna con fuente en negrita.
• Dígale a Excel que deje de cambiar el tamaño de los anchos de nuestras columnas desmarcando la casilla Opciones de tabla dinámica Ajustar automáticamente anchos de columna al actualizar (haga clic en el ícono Analizar de tabla dinámica Opciones para abrir el cuadro de diálogo Opciones de tabla dinámica )
• Establezca los anchos de las columnas de la hoja de trabajo como desee.
• Para ocultar la visualización de los valores de celda, haga clic con el botón derecho en cualquier celda de valor y seleccione Formato de número (no Formato de celdas)… y en el cuadro de diálogo resultante establezca el formato de número en Tipo personalizado ;; ; (mostrado a continuación)

Excelente… logramos nuestro objetivo y el informe resultante es exactamente lo que queríamos.

¡Gracias a Stephen Few por escribir Information Dashboard Design y por otorgar permiso para recrear este mapa de calor!

Otras consideraciones

• Dado que el mapa de calor se creó con una tabla dinámica, es fácil de actualizar en cualquier momento. Simplemente exportamos datos actualizados y los pegamos en la hoja de trabajo de Datos. Activamos la tabla dinámica y seleccionamos el ícono Herramientas de tabla dinámica Cambiar fuente de datos para asegurarnos de que todas las filas de datos estén incluidas en el informe.
• Tenga en cuenta que, en la práctica, sería una buena idea almacenar los datos subyacentes en una tabla (Insertar Tabla) y luego crear la tabla dinámica usando el nombre de la tabla para garantizar que las nuevas filas de datos se incluyan automáticamente. Actualice la tabla dinámica para recuperar los valores actualizados de la tabla, incluidas las filas nuevas.
• En la práctica, sería una buena idea almacenar los valores del rango en celdas de la hoja de cálculo en lugar de ingresarlos en la regla de formato condicional. Esto facilitaría el cambio rápido de las gamas de colores sin necesidad de actualizar las reglas de formato condicional.

Recursos adicionales

• Archivo de muestra: HeatMap
• Diseño de panel de información: http://www.amazon.com/dp/1938377001
• Sitio web de la Organización Mundial de la Salud (OMS): http://apps.who.int/en/
• Página web de datos descargados de la OMS: http://apps.who.int/gho/data/node.main.A1023?lang=en