Formulario de contacto

Nombre

Correo electrónico *

Mensaje *

miércoles, 15 de abril de 2015

Cómo hacer un mapa de calor (o Heatmap) en Excel

Hoy quiero mostrar cómo realizar un gráfico de mapa de calor o Heatmap. El proceso básicamente utilizará formato condicional. A su vez, incorporaremos una lista desplegable para hacerlo más interactivo.


Qué es un Heatmap?

Un Heatmap (o ‘mapa de calor’) es un gráfico en el que se resaltan mediante un código de colores zonas concretas de una web en base a criterios determinados.

Todos hemos visto Heatmaps, ya sea en el mapa del mundo, o una imagen del cuerpo humano, o en estadísticas sobre fútbol. Hoy vamos a usar el último ejemplo. Vamos a realizar un heatmap de un jugador de fútbol ficticio sobre un campo de fútbol.







Bueno pues ahora que sabemos qué es un Heatmap vamos a ver cómo se realiza.




Proceso para realizar un Heatmap


1. Necesitamos los... datos! 


Bueno, pues está claro que necesitamos las estadísticas de un jugador. Yo, como no las tenía me las he inventado, por eso el mapa ha salido tan distribuido. Yo no creo que ningún jugador de fútbol pudiera estar por todo el campo durante un partido de fútbol. Ya no quedan Di Estefanos.


Lo que he hecho es diseñar mi campo de juego de 20 filas x 30 columnas y ubicarlo en el rango G3:AJ22, al cual he llamado Campo.



Con eso en mente, he creado un tabla, con intervalos de cada 20 segundos hasta los 90 minutos o 5400 segundos. En la tabla a cada intervalo le di un valor aleatorio para la ubicación en el terreno de juego.


Las fórmulas a utilizar fueron:


=ALEATORIO.ENTRE(1,30) para las columnas

=ALEATORIO.ENTRE(1,20) para las filas

También identifique si correspondían a la primera o segunda parte.


De todas maneras estas estadísticas estarían dadas, pero como yo nos la tengo ni he podido encontrar me las he tenido que inventar.






2. Creamos una lista desplegable

Esta lista tendrá 3 valores, 1a parte, 2a parte y partido entero. Para insertar una lista desplegable (si aún no sabes cómo) ve al siguiente enlace.




La celda vinculada a la lista desplegable será AO1, y la hemos llamado "Sel_" en el Administrador de nombres.


3. Definimos las columnas y filas dependiendo de la selección en la lista desplegable

Si se ha elegido el partido entero necesitaremos todos los datos, pero si se ha elegido una parte sóla necesitaremos los datos correspondientes a esa parte.

Para ello definimos en el Administrador de nombres, el rango a utilizar para las columnas y para las filas. Lo vamos a realizar utilizando la función ELEGIR.

Para ello es conveniente entender cómo funciona esta función, para lo cual recomiendo leer el siguiente enlace.


De tal manera, definimos 

Cols=ELEGIR(Sel_,Heatmap!$D$2:$D$272,Heatmap!$D$2:$D$136,Heatmap!$D$137:$D$272)

Fil=ELEGIR(Sel_,Heatmap!$E$2:$E$272,Heatmap!$E$2:$E$136,Heatmap!$E$137:$E$272)


Si la variable Sel_ (que está vinculada a la lista desplegable) indica 1 el rango a utilizar será el primero indicado, Heatmap!$D$2:$D$272 para las columnas.



4. Comprobamos en que parte del campo los datos dicen que el jugador ha estado

Para realizar este paso, que es bastante importante, nos debemos de familiarizar con la función SUMAPRODUCTO. Para ello recomiendo estos 2 enlaces.


Usos prácticos de la función SUMAPRODUCTO


Cómo contar el número de valores únicos en un rango



Para ello cada celda de nuestro terreno de juego (rango G3:AJ22) deberá tener la siguiente fórmula.


=SUMAPRODUCTO((Cols=COLUMNA()-6)*(Fil=FILA()-2))


La razón por la que se le resta 6 y 2 es porque mi rango empieza en la columna 7 (columna G) y la fila 3.



Al hacer esto lo que se obtiene es la frecuencia de cada celda (fila x columna) en nuestros datos.






Bueno pues ya casi está.


5. Aplicar formato condicional

El formato condicional que ha de ser aplicado a nuestro terreno de juego ha de ser el de escalas de color. Mira el siguiente enlace si aún no sabes cómo hacerlo.


Formato condicional - escalas de color


En nuestro caso, se utiliza el de 2 colores como se ve en la imagen.





6. Ocultamos los números

Obviamente no queremos mostrar los números en el terreno de juego así que los ocultamos. Para ello lo que hacemos es seleccionamos nuestro rango (G3:AJ22) y le damos al botón derecho del mouse > Formato de celdas > Personalizada y ahí introducimos ";;;;".



Y ya está! Ubica la lista desplegable cerca del campo y disfruta de tu Heatmap!






Te ha gustado el artículo?


No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:

1. Puedes dejar una donación pinchando en la siguiente imagen.

PayPal. La forma rápida y segura de pagar en Internet


2. Puedes hacer clic en los anuncios mostrados en la página

3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.

Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.


Descargarse el archivo


Para descargarse el archivo con el ejemplo ve al siguiente enlace.




Aprende a hacer los mejores dashboards 

Pincha aquí para ver los mejores ejemplos.



Subscríbete y recibe todos las entradas por email


Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales