Traducir el blog

Desempleo de la UE con banderas

Posted on junio 24, 2020 by Excel Pedro Wave

Tasas de desempleo de la UE con banderas

Aviso para navegantes: Esta entrada es bastante extensa por lo que, si sólo quieres descargarte la plantilla, ve al final de esta entrada del blog pero, si quieres aprender técnicas nuevas en Excel, tendrás que leer y estudiar todo el artículo y analizar las fórmulas, nombres definidos, formas y macros VBA de la plantilla descargada.

 

Introducción

Esta entrada del blog es la continuación de la que publiqué hace 2 semanas en el siguiente enlace: Tasas de desempleo de la UE basándome en el tablero que Robert Mundigl publicó en su excelente blog clearlyandsimply.com - Highlighting on Excel Dashboards.
Ahora intento mejorar este cuadro de mando, incluyendo 3 mejoras. A mí se me ocurrió la primera y Robert hizo comentarios (traducidos del inglés con el traductor de Google) sugiriendo las otras dos mejoras:

Mejora 1) Información emergente (tooltip) con banderas
 
Cuando pasa el ratón por encima (evento MouseMove) de un país del mapa de la UE, se muestra su nombre, su tasa de desempleo para el año seleccionado y su bandera. Esta es la principal mejora y es la que más me ha costado desarrollar.

Comentario de Robert:
Según tu segunda sugerencia: Me interesaría mucho cómo proporcionarías una descripción con el nombre del país, la tasa de desempleo y la bandera al pasar el ratón sobre el mapa. No sé cómo lograr esto, así que estaría muy interesado en ver cómo harías esto.

Mejora 2) Cambiar país con el gráfico de líneas

Poder seleccionar un país haciendo clic en la serie del gráfico de líneas.

Comentario de Robert:
Finalmente, reemplazaste la imagen vinculada por dos gráficos y usaste el código VBA para hacerlos visibles o invisibles. Esta es una buena idea, pero solo si aprovecha el hecho de tener el gráfico en su tablero, es decir, para seleccionar un país haciendo clic o desplazándose sobre las líneas con el mouse. Echa un vistazo a esta publicación, donde se explica la selección de una serie de datos al pasar el mouse sobre:

Mejora 3) Nuevos botones de animación

El reloj de arena ha sido sustituido por los botones de animación: Play, Pause, Stop.

Comentario de Robert:
Animar la vista es una gran idea. No lo he hecho en mi artículo, porque quería centrarme en resaltar todas las vistas de un tablero. Pero definitivamente es una buena idea. Dicho esto, aún preferiría los botones de reproducción que utilicé en mis libros de animación, porque creo que son más intuitivos que un icono y una barra de desplazamiento. Si el usuario ve los botones de reproducción en un tablero, comprende inmediatamente para qué sirven. Este no es el caso con un icono de reloj de arena y una barra de desplazamiento. Echa un vistazo al libro de trabajo que viene con esta publicación, por ejemplo:

Mejora 1) Información emergente (tooltip) con banderas

La idea de mostrar información emergente con el nombre del país y la tasa de desempleo me pareció demasiado sencilla, por lo que añadí el reto de mostrar la bandera. Eso ya no es tan fácil, como Robert Mundigl dijo en sus comentarios en una de mis publicaciones en Linkedin:

 
Proporcionar una tooltip sobre mapas de formas en hojas de cálculo de Excel no es tan fácil como puede sonar. Por lo que sé, sólo hay dos soluciones para hacer esto y ninguna de ellas funcionará realmente para este panel:
 
La opción 1 es la brillante técnica de mi amigo Jordan Goldmeier (hipervínculos que desencadenan una función definida por el usuario VBA), como se describe aquí:
https://optionexplicitvba.blogspot.com/2012/09/the-excel-rollover-mini-faq.html
Esto funciona muy bien, pero requiere una cuadrícula de celdas minimizada debajo de las formas (alturas de fila muy estrechas y anchos de columna) y esto se convierte en un problema con el diseño del panel, si está utilizando otros rangos de celdas para mostrar, por ejemplo, la tabla desplazable o las etiquetas de eje del gráfico de barras.
 
La opción 2 usa la agregación de hipervínculos a las formas del mapa. Los hipervínculos están "muertos" y hacer clic en ellos no hace nada, pero se puede usar VBA para aprovechar las sugerencias de pantalla del hipervínculo para mostrar el nombre y la tasa de desempleo del país (pero no la bandera...). Echa un vistazo a este post de 2010, donde esta técnica se utiliza en un mapa de Argentina:
https://www.clearlyandsimply.com/clearly_and_simply /2010/02/spice-up-your-choropleth-maps-with-excel.html
Importante desventaja de esta 2ª opción: ya no se puede hacer clic en la forma para seleccionar un país. Para mí, este es un no-go para el tablero, porque el clic y la selección es más importante que la tooltip en mi humilde opinión.
 
De todos modos, no dejes que te desanime. Si tienes una idea de cómo mostrar una tooltip para las formas en los gráficos de Excel, me encantaría verla.

Tengo que decir que estas dos opciones ya las conocía yo. La segunda opción no permite mostrar la bandera, por lo que se tiene que descartar. La primera opción se basa en la increible técnica de Jordan Goldmeier, con el excelente truco con el efecto rollover que he estado usando durante 7 años, desde que conocí a nuestro amigo común Jordan. Ver mi ejercicio con el efecto rollover en este enlace: pedrowave.blogspot.com - Como pintar con Excel

El problema con esta opción es crear una retícula con gran cantidad de filas y columnas, lo que sólo se puede hacer si el mapa es lo único que hay en la hoja y éste no es el caso.

Hay una tercera opción que permite mostrar los datos del país y su bandera en un cuadro con información emergente (tooltip) que voy a exponer a continuación.


Este vídeo está grabado con una versión preliminar que después ha sido mejorada para que la información del país y su bandera persiga al ratón, que estará centrado en el cuadro emergente (tooltip). También se ha añadido la posibilidad de fijar la tooltip y que cambie cuando se mueva el ratón por encima de los países del mapa y la posibilidad de ocultar la tooltip. Por supuesto que, haciendo clic encima de la tooltip, queda seleccionado el país sobre el que está el ratón.

Técnicas empleadas para mostrar la tooltip con datos del país y su bandera

A) Las formas de la hoja 'dashboard' se ordenan en varias capas de información:

1) Capa superior con los controles: Combobox; Scrollbar; DropDown; Option Button; Charts.

2) Capa de etiquetas invisibles (control ActiveX): Cada etiqueta se nombra con "L_" y la abreviatura del país con 3 letras. Este es el truco principal pues las etiquetas activan el evento MouseMove cuando el usuario mueve el ratón, lo que permite modificar la información de la tooltip.

3) Capa de la Tooltip con una única imagen obtenida con la cámara de Excel. Al situar esta capa entre la capa de etiquetas y la capa de formas con los contornos de los países, es posible que la tooltip siga los movimientos del ratón de manera continua. 

4) Capa de formas de los países. Son las formas originales con las fronteras de los países y sus áreas coloreadas para formar el mapa cloropético. Cada forma se nombra con "S_" y la abreviatura del país con 3 letras. 

5) Capa inferior con el resto de formas: Países fuera de la UE; Map Icon; Play Button Circle; Image_Copyright. 

El ordenamiento de estas capas se ha hecho desde el menú: Inicio | Buscar y seleccionar... | Panel de selección..., con las formas de la hoja.

B) De estas capas, nos interesan sólo dos: la Tooltip y las etiquetas invisibles.

1) Capa con la Tooltip:
 
Denominada TooltipMap en la hoja 'dashboard', está generada con la cámara de Excel y con la siguiente fórmula: =tooltip!$B$3:$D$7
 
En la hoja 'tooltip' se obtiene el nombre del país, su tasa de desempleo para el año seleccionado y su bandera, a partir del valor de la celda G8 con el nombre: Tooltip_Country
 
En las celdas C4 y H8 se obtiene la bandera a partir del nombre definido: myFlag con la siguiente fórmula:

=INDICE(TableTooltipCountry[Flag];COINCIDIR(Tooltip_Country;TableTooltipCountry[Shape];0);1)
 
La tooltip no se mueve cuando no está chequeada la casilla de verificación "Move", en cuyo caso se muestra en una posición fija y con una línea alrededor, con el color azul de la bandera de Europa, RGB(0,51,153):

  

Se han dispuesto dos casillas de verificación (checkbox) para actuar sobre la Tooltip mediante la macro ChangeTooltipStatus:
Visible: para mostrar u ocultar la Tooltip.
Move: para permitir el movimiento de la Tooltip o dejarla inamovible en una posición fija.
Estos dos checkbox se han simulado en dos celdas de la hoja y llaman a la macro ChangeCountryTooltip
 
El formato de la Tooltip es fácilmente modificable a gusto del usuario con sólo editarlo en la hoja 'tooltip' y modificar la tabla TableTooltipCountry con los datos a informar.



2) Capa de etiquetas invisibles:
 
Esta es la nueva capa añadida al efecto para poder mover la Tooltip con la bandera a través de las áreas de los distintos países de la UE siguiendo el movimiento del ratón, por lo que esta mejora es la principal aportación de este mapa.
 
a) Lo primero que he hecho ha sido crear las nuevas formas como etiquetas control ActiveX desde el módulo VBA modChangeTooltip con las macros:
 
AddLabels: Recorre todas las formas con los mapas de cada país, denominadas "S_(3 letras del país)" y llama a la macro AddLabel.
 
AddLabel: Crea una etiqueta invisible denominada "L_(3 letras del país)" un 40% más pequeña que la forma del tipo "S_" y situada por encima de ella. Sus propiedades más interesantes son:
  • BackStyle = fmBackStyleTransparent
  • BorderStyle = fmBorderStyleNone
  • ShapeRange.ZOrder msoBringToFront
  • ShapeRange.Fill.Visible = msoFalse
DeleteLabels: Borra todas las etiquetas invisibles denominadas "L_(3 letras del país). Esta macro sirve para hacer pruebas y reintentar crear de nuevo las etiquetas hasta conseguir el resultado esperado.
 
No es necesario ejecutar de nuevo ninguna de estas 3 macros para usar la plantilla. Así quedaría el mapa si se hubieran creado las etiquetas con la propiedad visible:
 
b) Lo segundo que he hecho ha sido ajustar manualmente la posición y el tamaño de algunas de las etiquetas.
 
Para las etiquetas creadas anteriormente, que se pueden ver en el mapa de arriba, su área no se correspondía con la zona donde mover el cursor sobre alguno de los países. Esta labor manual ya no se debe hacer si el resultado es el deseado, si no es del agrado del usuario habrá que ajustar manualmente algunas de las etiquetas.  

Algunos países ha habido que retocarlos más que otros, por ejemplo para Dinamarca se puede ver la etiqueta L_DNK que no se ajustaba al contorno del país. También se ha intentado que las etiquetas no se solapen unas con otras para poder mostrar el país correcto en la Tooltip cuando se mueva el ratón por el mapa.
 
El caso de España es especial pues en el mapa de la UE no se muestran las Islas Canarias, localizadas en el Océano Atlántico, por cuestiones de espacio en la versión original de este mapa cloropético.
 
 
Será una nueva mejora a considerar para una futura entrada en el blog.

c) Lo tercero que he hecho ha sido crear un módulo de clase en VBA llamado 
CActiveXLabel para no tener que crear un par de eventos por cada una de las 28 etiquetas invisibles creadas en los pasos anteriores.
 
La clase ClassLabel definida como MSForms.Label permite atender estos dos eventos:
  • ClassLabel_MouseMove: lanza el evento MouseMove cuando el ratón pasa por encima de una etiqueta invisible. Es el evento más importante del truco para mostrar la Tooltip.
  • ClassLabel_Click: lanza el evento Click cuando se hace clic en una etiqueta invisible para cambiar el país seleccionado. 
El código del módulo es el siguiente:

d) Lo cuarto ha sido crear, en el módulo modChangeTooltip, una Colección (Public LabelColl As Collection) que contiene todas las clases del tipo ClassLabel con la macro FillLabelCollection que rellena la colección con las clases de cada una de las 28 etiquetas del tipo "L_".
 
Con lo que tenemos 3 escenarios posibles para cambiar el país seleccionado: 
1. Clic sobre la tooltip cuando se mueve la tooltip por el mapa.
2. Clic sobre la etiqueta de un país cuando la tooltip no se mueve.
3. Clic sobre una forma de un país cuando la tooltip es invisible.
 
e) Lo quinto ha sido no modificar la macro UpdateChoroplethMap, que cambia los colores de relleno de las formas del mapa cloropético, sino modificar únicamente los colores de las formas del tipo "S_", con las imágenes de cada país, para que no se intenten modificar las formas del tipo "L_", con las etiquetas invisibles de cada país.
 
En el evento Workbook_Open se llama a la macro SetCountryShapesLayer (módulo modChoroplethMap) para que actualice los valores de la matriz myShapeIndex, de la hoja 'calcs', para que los índices apunten únicamente a la capa de formas de los países del tipo "S_", que son las formas que cambian de color en el mapa cloropético.
 

Mejora 2) Cambiar el país con el gráfico de líneas

Una validación de datos del tipo Lista en la celda B7 (myChartImage) de la hoja 'dashboard' permite seleccionar dos maneras de presentar los gráficos con la macro ChangeChart, lanzada en el evento Worksheet_Change:
 
a) Image: es el modo original de Robert de mostrar los gráficos de bandas y de líneas, con una imagen fotográfica tomada con la cámara de Excel de los gráficos de la hoja 'charts'.
 
b) Chart: es el nuevo modo de mostrar esos dos gráficos en la hoja 'dashboard' para poder interactuar con ellos y que se vean las tooltips cuando se pase el cursor por encima de una serie, un punto o cualquier otra parte del gráfico.
 
Cuando se selecciona Chart en el desplegable y se marca Line Chart, se ha añadido la posibilidad de cambiar de país haciendo clic en una de las series del gráfico de líneas.
 
 
Para ello hace falta que al hacer clic en una serie del gráfico de línes se lance el evento Select, lo que obliga a crear un nuevo módulo de clase CChartEvent que gestione los eventos del gráfico.
 
Se define el gráfico con eventos:
Public WithEvents EventChart As Chart
 
Y se crea la macro que escucha al evento Select: 
Private Sub EventChart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
 
que llama a la macro: UpdateTableCountryClick para cambiar de país.
 
Para crear eventos en gráficos es bueno informarse en la excelente página:
 
Chart Events in Microsoft Excel
October 31, 2014 by Jon Peltier
 
Para que no afecten otros eventos se han definido dos rutinas:
  • EventChart_BeforeDoubleClick: no actua doble clic. 
  • EventChart_BeforeRightClick: no actua el botón derecho del ratón.
Ahora también es posible hacer clic en un punto de la línea de una serie para que cambie el año además del país. Lee los comentarios de Robert y míos para obtener una explicación de esta nueva mejora.
 

    Mejora 3) Nuevos botones de animación

    Ahora los botones de animación son: Play, Pause, Stop.


     
    Se han definido en la celda W2 (myAnimation) de la hoja 'dashboard', mediante una fuente de texto del tipo Webdings:
    • Play es el carácter: 4
    • Pause es el carácter: ; 
    • Stop es el carácter: <
    Durante la animación se va alternando entre Pause y Stop para saber que la animación está en modo Play.
     
    Robert si has leído hasta aquí, ¿queda claro y simple?
     
    Tengo que darte las gracias porque he aprendido a elegir banderas en tu magnífica y muy actual entrada del blog, que recomiendo leer encarecidamente a todos los apasionados de Excel:
     
     


    Descarga del Cuadro de Mando

    Descarga la plantilla totalmente gratuita, con las macros visibles y las hojas protegidas sin contraseña, de este cuadro de mando con las tasas de desempleo en la UE durante los 30 últimos años, con las banderas de los países, desde este enlace y habilita las macros para usarlo.

    Actualización del 29 de junio de 2020:
    He modificado la plantilla con dos de las sugerencias que indicó Robert Mundigl en los comentarios de abajo. Como hago pruebas únicamente en una versión de Excel 2010, en la que no ocurría que la etiqueta ActiveX apareciera como un rectángulo blanco después de hacer clic en el mapa, tampoco se si ese efecto indeseado ha desaparecido. Espero vuestros comentarios.

    Descarga desde Google (con "Excel Download") o desde Microsoft OneDrive:
    Descarga y ejecuta el fichero para ver en movimiento el efecto MouseMove con el que se muestra información emergente (tooltip) con el nombre, la tasa de desempleo y la bandera del país dentro del mapa de la UE.

    Espero que esta técnica para mostrar información emergente (tooltip) sea de valor para añadir más interactividad a los mapas en Excel.

    7 Response to "Desempleo de la UE con banderas"

    .
    gravatar
    Robert Says....

    Pedro,

    thank you very much for this post. Very interesting enhancements of the dashboard. Great job.

    Here are my comments:

    1. The line chart: I especially like the additional option of selecting a country by clicking on the line chart, because this fits very well to the entire concept of the original dashboard of letting the user select a country and year on every view of the dashboard. Great idea, congratulations.

    Suggestion: It would be even better, if not only the country selection would change, but also the selected year, depending on which point of the line is clicked. You can do this by taking advantage of the argument Arg2 which returns the index of the data point currently clicked on.

    2. The Map Tooltips: Using a set of ActiveX Labels on top of the map is an interesting idea. Just like any another workaround, it comes with some disadvantages, because the rectangular Label controls do not fully and exactly cover the shapes of the countries. Therefore, if you hover over certain parts of a country (mainly the edges), the tooltip will not update correctly, e.g. the North-Western part of Spain or the Eastern part of Germany. Furthermore, because of the necessity of manual adjustments, the approach will only be viable for maps with a limited number of regions. You will still be ok for a map of Spain by its 50 provinces, but you will definitely have a hard time if you want to use this on a map of Spain by municipalities (~8,100 if I remember that correctly).

    That being said, it is a good idea and it works well for the map of Europe used on this dashboard. Kudos.

    Suggestion: in the workbook you posted for download, the ActiveX Label appears as a white rectangle after clicking on the map and will only disappear again if the mouse is moved away from it. This is a known bug of the ActiveX label controls. You can easily overcome this by making the label invisible in the MouseUp event sub of the ClassLabel. If you then also turn off the screen updating during the execution of the Click event, you can even avoid the short flickering of the label when clicking.

    3. The animation: I am afraid to say, but we will have to agree that we disagree on this one. I still prefer my approach of showing all animation buttons. From my point of view, they are more intuitive (users immediately understand which options they have) and they provide more functionality (forward and backward, fast forward and fast backward, jump to the start and the end). Your solution is working well, but I will stick to my approach. A matter of taste, I guess.

    Again, congratulations. Great work.

    .
    gravatar
    Excel Pedro Wave Says....

    Traducción del comentario de Robert para mis lectores en lengua española:

    Pedro, muchas gracias por esta publicación. Mejoras muy interesantes del tablero original. Gran trabajo.

    Estos son mis comentarios:

    1. El gráfico de líneas: Me gusta especialmente la opción adicional de seleccionar un país haciendo clic en el gráfico de líneas, ya que esto se adapta muy bien al concepto completo del tablero original de permitir que el usuario seleccione un país y un año en cada vista del tablero. Buena idea, felicidades.

    Sugerencia: Sería aún mejor si no solo cambiara la selección del país, sino también el año seleccionado, según el punto de la línea en el que se haga clic. Puede hacer esto aprovechando el argumento Arg2 que devuelve el índice del punto de datos en el que se hace clic actualmente.

    2. La información sobre herramientas del mapa: es una idea interesante usar un conjunto de etiquetas ActiveX en la parte superior del mapa. Al igual que cualquier otra solución alternativa, presenta algunas desventajas, ya que los controles de etiqueta rectangulares no cubren de forma completa y exacta las formas de los países. Por lo tanto, si pasa el mouse sobre ciertas partes de un país (principalmente los bordes), la información sobre herramientas no se actualizará correctamente, por ejemplo, la parte noroeste de España o la parte oriental de Alemania. Además, debido a la necesidad de ajustes manuales, el enfoque solo será viable para mapas con un número limitado de regiones. Todavía estará bien para un mapa de España por sus 50 provincias, pero definitivamente tendrá dificultades si desea usar esto en un mapa de España por municipios (~ 8,100 si no recuerdo mal).

    Dicho esto, es una buena idea y funciona bien para el mapa de Europa utilizado en este tablero. Elogios.

    Sugerencia: en el libro de trabajo que publicó para descargar, la etiqueta ActiveX aparece como un rectángulo blanco después de hacer clic en el mapa y solo desaparecerá nuevamente si el mouse se aleja de él. Este es un error conocido de los controles de etiqueta ActiveX. Puede superar esto fácilmente haciendo que la etiqueta sea invisible en el sub evento MouseUp del ClassLabel. Si también deja de refrescar la pantalla durante la ejecución del evento Click, incluso puede evitar el breve parpadeo de la etiqueta al hacer clic.

    3. La animación: me da miedo decirlo, pero tendremos que aceptar que no estamos de acuerdo con esto. Todavía prefiero mi enfoque de mostrar todos los botones de animación. Desde mi punto de vista, son más intuitivos (los usuarios entienden de inmediato qué opciones tienen) y brindan más funcionalidad (avance y retroceso, avance rápido y retroceso rápido, salto al inicio y al final). Su solución está funcionando bien, pero seguiré con mi enfoque. Una cuestión de gustos, supongo.

    De nuevo, felicidades. Buen trabajo.

    .
    gravatar
    Excel Pedro Wave Says....

    Robert, estoy encantado de recibir tus comentarios, pues son muy acertados y muy didácticos y es un honor aprender de Excel del propio autor del blog:

    Clearly and Simply
    Intelligent Data Analysis, Modeling, Simulation and Visualization
    https://www.clearlyandsimply.com

    Espero que este lunes pueda contestar a tu comentario.

    Mientras tanto deseo que pases un buen fin de semana.
    Un cordial saludo de
    Pedro.

    .
    gravatar
    Excel Pedro Wave Says....

    Hola Robert,

    Atendiendo tus sugerencias he actualizado lo siguiente:

    Sugerencia 1: En el gráfico de líneas sería aún mejor si no solo cambiara la selección del país, sino también el año seleccionado, según el punto de la línea en el que se haga clic. Puede hacer esto aprovechando el argumento Arg2 que devuelve el índice del punto de datos en el que se hace clic actualmente.
    Seguro que hay alguna manera de hacer clic fácilmente en un punto determinado de la línea, porque lo habitual es que haga clic en la línea de la serie y no en un punto determinado de la línea.

    Sugerencia 2: la etiqueta ActiveX aparece como un rectángulo blanco después de hacer clic en el mapa y solo desaparecerá nuevamente si el mouse se aleja de él. Este es un error conocido de los controles de etiqueta ActiveX. Puede superar esto fácilmente haciendo que la etiqueta sea invisible en el sub evento MouseUp del ClassLabel. Si también deja de refrescar la pantalla durante la ejecución del evento Click, incluso puede evitar el breve parpadeo de la etiqueta al hacer clic.

    No lo he podido hacer en el evento ClassLabel_MouseUp pues la etiqueta debe quedar en estado visible al hacer clic en ella, por lo que lo he hecho en el evento ClassLabel_Click, lo que no se si dará resultado. Lo pruebas y me cuentas.

    Tengo que decir que lo estoy probando en una versión de Excel 2010, ya que es la única que tengo con macros pues la empresa me retiró hace un mes el portátil que llevaba usando estos últimos años.

    Sugerencia 3 para mostrar todos los botones de animación, como dices: es una cuestión de gustos. Sólo tengo que decir que Play y Pause son los únicos botones que aparecen en YouTube, por ejemplo.

    Gracias por tu tiempo y tu feedback.
    Disculpa si no traduzco este comentario al inglés.

    .
    gravatar
    Robert Says....

    Pedro,
    I just checked. The label does not stay visible anymore, but you still see the label for a few milliseconds when you click on it.

    .
    gravatar
    Excel Pedro Wave Says....

    Robert, thank you very much for your feedback.

    As I've mentioned, with Excel 2010 version I can't reproduce that unwanted effect, so I leave it that way if the behavior is what you are telling me and the tooltip continues showing the country and its flag in any case.

    I encourage you to publish this post as a new post in your blog, or to update your original post, because all the credit is yours, I have only added some improvements to your excellent blog post.

    .
    gravatar
    Excel Pedro Wave Says....

    He corregido algunos errores por lo que recomiendo descargar de nuevo el archivo desde los enlaces de descarga.

    Leave A Reply

    Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.

    Mi lista de blogs