Desempleo de la UE con banderas

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:

https://www.clearlyandsimply.com/clearly_and_simply/2020/06/how-to-handle-line-charts-with-many-data-series.html

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:

https://www.clearlyandsimply.com/clearly_and_simply/2014/09/gatminder-replica-in-microsoft-excel.html

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:

Comentarios de Robert en Linkedin: Tasas de desempleo de la UE

 

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:

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    EU Unemployment Rates - Highlight item across the dashboard
'   Class Module:   CActiveXLabel
'   Author:         Pedro Wave
'   Copyright:      © 2020 by Pedro Wave for Excel Guys, https://pedrowave.blogspot.com
'   Last edit:      16-Jun-2020
'   Purpose:        Country labels class events: MouseMove & Click
' ----------------------------------------------------------------------------------------------------------------------------------

Option Explicit

Public WithEvents ClassLabel As MSForms.Label

Private Sub ClassLabel_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
'
' Class Label MouseMove event
'
    Dim sCountry As String
    
    On Error Resume Next
    
    ' Change country label by country shape
    sCountry = Replace$(Me.ClassLabel.Name, "L_", "S_")
    
    Sheets(C_TOOLTIP_SHEET_NUMBER).Range("Tooltip_Country").Value2 = sCountry
    
    ' TooltipMap position
    If Sheets(C_MAP_SHEET_NUMBER).Range("myTooltipMove").Value2 = "a" Then
        With Sheets(C_MAP_SHEET_NUMBER).Shapes("TooltipMap")
            .Left = ClassLabel.Left + x - .Width / 2
            .Top = ClassLabel.Top + y - .Height / 2
        End With
    End If
    
End Sub

Private Sub ClassLabel_Click()
'
' Class Label Click event
'
    Dim sCountry As String
    
    ' Label lost focus
    Sheets(C_MAP_SHEET_NUMBER).Select
    Range("myChartImage").Select
    
    ' Chane country label by country shape
    sCountry = Replace$(Me.ClassLabel.Name, "L_", "S_")
    
    ' Update after user selected another country by clicking on a map label
    UpdateShape sCountry

End Sub

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

https://peltiertech.com/chart-events-microsoft-excel/

 

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:

 

https://www.clearlyandsimply.com/clearly_and_simply/2020/04/my-2-cents-on-the-covid-19-dashboard-by-jhu.html

 

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:

EU_Unemployment_Rates_PW2.xlsm 

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.

Tasas de desempleo de la UE

Tasas de desempleo de la UE

Actualización: En el siguiente enlace puedes descargar la segunda versión que muestra las banderas de los países de la Unión Europea:

https://pedrowave.blogspot.com/2020/06/desempleo-de-la-ue-con-banderas.html

Desde que el Covid-19 está entre nosotros se ha disparado salvajemente el número de desempleados en todo el mundo por lo que, a partir de ahora, habrá que analizar y controlar las tasas de desempleo desde marzo de 2020 en España o en cualquier otro país del mundo, aunque esta tarea la dejo para un próximo artículo si os gusta esta entrada del blog que es la primera después de más de 2 años sin publicar nada.

En este artículo pretendo mostrar las tasas de desempleo de los países de la UE desde 1990 hasta 2019, para poderlas comparar en un futuro con las tasas de 2020. El último dato del que dispongo es de marzo de 2020.

Esta plantilla ha sido diseñada totalmente en Excel, para que pueda llegar a muchos más usuarios que si se usan herramientas como Power Maps, Power View o Power BI, que no tienen instaladas ni conocen muchos de los usuarios de Excel.

Agradecimientos

Reconozco que es la primera vez que publico a partir de una plantilla que no he creado yo mismo.

Como la versión original está descargada de un blog escrito en inglés, la plantilla modificada también está en inglés, sin traducir al español, lo que espero que no sea un impedimento para mis lectores.

En esta ocasión he modificado y actualizado una plantilla del fenomenal analista y visualizador de datos Robert Mundigl, autor del blog Clearly and Simply, con las tasas de desempleo de la UE desde 1990 hasta 2014, que se puede leer y descargar en el siguiente enlace:

clearlyandsimply.com - Highlighting on Excel Dashboards

He aprendido mucho de este artículo, por lo que recomiendo encarecidamente su lectura a quienes quieran superarse en Excel.

Leyendo este artículo he aprendido cómo:

• Crear un mapa coroplético: mapa temático en el que las áreas se sombrean de distintos colores, como puede ser la densidad de población o la renta per cápita, en este caso: las tasas de desempleo.
• Crear una tabla parcial de datos seleccionada con barras de desplazamiento.
• Seleccionar un país o un año en la tabla parcial desplazable.
• Seleccionar un país en el gráfico de barras.
• Hacer interactivo un mapa, una tabla, un gráfico...
• Seleccionar e interactuar con distintos grupos de edad.

Todo esto está muy bien explicado en la entrada del blog original, por lo que vuelvo a recomendar su lectura en español a quienes se les resista el inglés, gracias al traductor de Google:

clearlyandsimply.com - Destacar en paneles de Excel

Origen de las tasas de desempleo de la UE

El indicador presenta tasas de desempleo para diferentes grupos de edad. La tasa de desempleo representa a las personas desempleadas como un porcentaje de la fuerza laboral. La fuerza laboral es el número total de personas empleadas y desempleadas. El indicador se basa en la Encuesta de población activa de la UE. Se pueden descargar las tasas de desempleo de 2005 a 2019 para los países de la UE, según varios rangos de edades, desde el siguiente enlace:

eurostat - Unemployment rate by age

Para el año 2020 se han usado las tasas mensuales de desempleo, descargadas del siguiente enlace:

ILOSTAT - Unemployment rate by sex and age (%) - Monthly

Actualización de las tasas de desempleo de la UE por edad

En la versión original del blog Highlighting on Excel Dashboards se muestran datos desde 1990 hasta 2014. En la plantilla que publico ahora se han actualizado las tasas de desempleo de 1995 a 2019 y se ha añadido la tasa de desempleo de marzo de 2020, justo antes de que esta maldita pandemia afectara al empleo de los trabajadores de todo el mundo. Los grupos de edad originales eran dos: de 26 a 74 años y menores de 25 años. Originalmente no hay datos en el rango de edad de 15 a 74 años, por lo que hay una laguna para ese rango en el período de 1990 a 1994.

En la plantilla actual se han incluido 3 grupos de edad:

• 15 a 74 años para toda la fuerza laboral, o sea toda la población activa.
• 15 a 24 años para los jóvenes.
• 25 a 74 años para los adultos.

En la hoja 'data' se encuentran las 3 tablas de datos por edad. Se han modificado las hojas 'control'; 'calcs' y 'charts' para incluir los años 2015 a 2020. Estas hojas están suficientemente explicadas en el blog original que he compartido mas arriba.

 

Cambios en la plantilla original

No soy quien para modificar la excelente plantilla que publicó Robert Mundigl, al que le he solicitado permiso antes de modificarla, autor del magnífico blog Clearly and Simply. Lo único que puedo decir como desagravio es que no he mejorado la interactividad original, pues es una labor de gigantes en Excel, lo que seguro que necesitó de un extraordinario esfuerzo por parte de su autor.

Lo que si que me he atrevido a mejorar es la entrada de datos de la cabecera, que permite seleccionar el país, el año y el grupo de edad, y he añadido una animación interactiva a la plantilla para darle más vida.

La interfaz gráfica de usuario en la plantilla original tenía este aspecto:

 

En ella hay tres Controles de Formulario para elegir país, año y grupo de edad. Estos Controles de Formulario se pueden ampliar pero el texto no cambia de tamaño, siendo difícil de ver para los que somos cortos de vista como yo.

     















La nueva interfaz gráfica tiene este otro aspecto:

 

Los Controles de Formulario se han sustituido por Controles ActiveX del tipo cuadro combinado (ComboBox) que si que permiten modificar el tamaño de la fuente de texto, siendo mucho mas legibles:

       

Además se ha añadido una barra de desplazamiento (ScrollBar) para poder seleccionar más cómodamente los años, de uno en uno o de cinco en cinco, sin tener que usar el desplegable de años, pudiendo ir adelante o atrás:

 

Otra mejora ha sido adaptar la tabla parcial de 10 países x 10 años para que se desplace automáticamente al país o al año seleccionado, pero sin cambiar la vista parcial si ya se ve el país o el año seleccionados.

También he incrustado los gráficos de bandas y de líneas en la hoja 'dashboard' para poder analizar sus series fácilmente, pues originalmente había que hacerlo desde la hoja 'charts'.

 

Nuevas macros

Para que los nuevos Controles ActiveX funcionen se han añadido varias macros en la hoja 'dashboard':

• ComboBox1_Change: para cambiar de país con el desplegable.
• ComboBox2_Change: para cambiar de año con el desplegable.
• ScrollBar1_Change: para cambiar de año con la barra de deslizamiento.
• ComboBox3_Change: para cambiar de grupo de edad con el desplegable.
• Función ScrollTable: para cambiar la vista de la tabla parcial deslizante.

En el objeto 'DieseArbeitsmappe' (originalmente en alemán), o sea en el objeto 'ThisWorkBook', se han incluido dos macros para los eventos:

• Workbook_Open: para proteger algunas hojas y detectar el idioma de Excel.
• Workbook_BeforeSave: para proteger algunas hojas.

Módulo modChangeChart:

• ChangeChart: Cambia la visibilidad del gráfico de bandas o de líneas. Ya no se muestran como gráficos seleccionados con la Cámara de Excel, sino que están incrustados en la hoja 'dashboard' para poder analizar sus series interactivamente, para lo que se han renombrado las series con los nombres de los países en el gráfico de líneas. La hoja 'charts' se ha mantenido para respetar la plantilla original, pero podría ser eliminada sin problemas.

Módulo modChangeProtect:

• ProtectSheets: protege todas las hojas (excepto la hoja 'charts', pendiente de analizar por qué no se pueden proteger) de la acción de los usuarios pero no de las macros, gracias al argumento de la función Protect: UserInterfaceOnly:=True
• UnprotectSheets: desprotege todas las hojas si se ejecuta manualmente.

Módulo modChangeLanguage:

• ChangeLanguageFormat: Detecta el idioma de Excel y manda cambiar el formato de años en los gráficos.
• ChangeYearFormat: Cambia el formato de dos dígitos de los años en el eje horizontal de los gráficos de bandas y de líneas, según el idioma:
• Inglés: yy
• Español: aa

Módulo modChangeYear:

• InitializeYear: Arranca o para el temporizador de n segundos (n = 0 a 10 sec delay).
• LoopYear: Bucle desde el primer año al último año. Este bucle no refresca los controles ActiveX: ScrollBar1 y ComboBox2 hasta que no acaba el bucle, ¿alguna sugerencia?
• ChangeYear: Cambia el año de modo temporizado por el número seleccionado de segundos, desde el año en curso y cuando llega a 2020 continua en 1990.
• StartTimer: Arranca el temporizador con la función: Application.OnTime
• StopTimer: Para el temporizador.
• ColorTimer: Pone color rojo cuando el temporizador está corriendo.

El módulo anterior se usa en la nueva interacción animada que se consigue con este icono de un reloj de arena:

Cuando se hace clic en el reloj arranca el temporizador, cambiando al siguiente año cuando transcurre el número de segundos de retraso elegidos (retraso de 1 a 10 segundos), poniéndose en color de fondo rojo para indicar que la animación está en marcha. Si se eligen 0 segundos, se inicia un único bucle desde el primer al último año. En cualquier momento se puede volver a hacer clic en el reloj de arena para detener la animación.

Esta animación es muy rudimentaria. Si se quiere aprender más sobre animaciones de mapas en Excel no hay nada mejor que leer esta interesante entrada al blog del mismo autor:

 

clearlyandsimply.com - Animated Maps in Microsoft Excel

Descarga de la plantilla

Descarga el archivo desde el icono (Google Drive) o desde el enlace (Microsoft OneDrive)

EU_Unemployment_Rates_PW1.xlsm


Esta plantilla está escrita en inglés  y es compatible con versiones a partir de Microsoft Excel 2007.

 
Contiene macros, que hay que permitir para activarlas, y se debe hacer clic en el botón "Habilitar edición" si aparece un mensaje informativo de "Vista protegida" para poder usar esta plantilla, siempre que confíes en el autor de este blog, Pedro Wave.

Si eres curioso como yo, puedes desproteger las hojas para estudiarlas, ya que están protegidas sin contraseña.

Espero haber conseguido mejorar la interactividad de la plantilla original y que los datos actualizados sean útiles para estudiar las tasas de desempleo en la UE.

 

Copyright

Yo, Pedro Wave, estoy publicando bajo una licencia Creative Commons License:

Attribution-NonCommercial-ShareAlike 3.0 Unported (CC BY-NC-SA 3.0)

https://creativecommons.org/licenses/by-nc-sa/3.0/

Los términos de la licencia son: 

• Atribución: Otorgue el crédito apropiado, especialmente mantenga mi nombre y el nombre de mi blog en el libro de trabajo y el código.
• Compartir igual: Regalarlo gratis. Lo estoy regalando gratis, así que también tiene que hacerlo igual.
• No comercial: No use mi plantilla y código para crear una plantilla o libro de trabajo para la venta comercial.

Siempre y cuando sigas estos términos, se te permite transformar y cambiar la plantilla publicada y compartirla en tu blog o distribuirla de otras maneras.

Lo importante es que puedas aprender técnicas nuevas con esta plantilla y mejorar tu experiencia en Excel, con lo que habré conseguido el objetivo que me he propuesto desde que creé este blog hace más de 10 años.

Desempleo activo

Llevaba más de dos años sin publicar una entrada nueva en este blog, que tantas horas de satisfacción y aprendizaje me ha deparado, y ahora vuelvo a escribir pues el coronavirus me ha dejado profesionalmente parado durante unas semanas y engrosando las estadísticas del desempleo desde este mes.

 
Voy a intentar dedicar este tiempo a seguir en activo, publicando más entradas en el blog, para lo que quisiera que los lectores me deis ideas diciéndome que es lo que os gustaría que publicara a partir de ahora, para lo que os animo a escribir un comentario en esta entrada.

 
Tengo que dar las gracias a los visitantes del blog por haber dedicado su tiempo a leer hasta aquí.

Nueva Agenda Calendario de Fases Lunares

Nueva Agenda Calendario de Fases Lunares

Actualización 2022/01/03: He publicado una nueva versión de esta Agenda Calendario Lunar aquí:

Agenda Calendario Lunar con fórmulas | #ExcelPedroWave

He creado y acabo de publicar una nueva agenda calendario de fases lunares, con la que poder guardar todos los recuerdos del siglo XX y todos los eventos que nos aguardan en el siglo XXI, y que espero que te guste pues esta plantilla la he hecho con mucha ilusión.

En esta primera versión he cargado las fases lunares de dos años, del 2018 y del 2019, pero la plantilla está preparada para cargar las fases lunares desde el año 1901, en que comenzó el siglo XX, hasta el año 2100 en que finalizará el siglo XXI, o sea, desde el último siglo del II milenio hasta el primer siglo del III milenio.

Calendario

En la hoja 'Calendar' se muestra un mes seleccionable por medio de un desplegable de año y de mes, o por controles de incremento y decremento, con los que cambiar rápidamente de mes y de año.

Los días vienen marcados en azul de lunes a viernes o en rojo si son sábados o domingos.  Si son festivos se marcan en color verde, por ejemplo el día 5 está señalado de vacaciones.

Se puede elegir si la semana comienza en lunes o en domingo, que es en lo poco que coinciden América del Norte y los Estados Islámicos.

Para mostrar las 4 fases lunares he usado los siguientes caracteres del tipo Wingdings 2: ☽ (creciente) 🌕 (llena) ☾ (menguante) 🌑 (nueva).

El día y la hora en que ocurren las fases lunares se muestran en el calendario y dependen de la zona horaria del observador, denominada huso horario. Por eso se puede elegir la zona horaria (Time Zone) en el calendario, además de poder seleccionar mediante una casilla de verificación si la zona horaria es del este (East) o del oeste (West), siendo la hora positiva o negativa respectivamente. El valor de la zona horaria se puede editar con un decimal para poder contemplar  medias horas, por ejemplo en el caso de la India que se debe introducir como 5,5 pues le corresponde el huso horario UTC+05:30.

También se visualiza el día actual con un hiperenlace a la agenda. Cada uno de los días contiene un hiperenlace marcado con 3 puntos suspensivos "..." debajo del número de día, que lo redirige al día y la hora de la agenda en la que se guarda el texto del evento, por ejemplo el día 21 hay que renovar un permiso pues es lo que está escrito en la agenda. Si en la agenda se escriben para un mismo día varios eventos en dos horas diferentes, el hiperenlace lleva a la hora del primer evento, o a la segunda columna si es un día festivo.

Agenda

En la hoja 'Planner' está la agenda completa con los días en la columna A, filtrada por defecto para este año 2018.

La agenda contiene todos los días de 201 años, desde 1900 hasta 2100, lo que hace un total de 73.414 días, uno en cada fila, aunque  en la hoja 'Calendar' no se ha incluido el año 1900 por ser del siglo XIX y porque Microsoft Excel lo trata como bisiesto aunque no lo es, como se explica en este enlace: Excel incorrectly assumes that the year 1900 is a leap year

La columna B se usa para indicar el motivo de un día festivo (Holiday) que quedará marcado en color de fondo verde, tanto en la agenda como en el calendario. Este motivo editado en la agenda aparece en el calendario debajo del número de día de fiesta, con un hiperenlace a la agenda, como ya se ha explicado anteriormente.

Las columnas C a Z son para cada una de las 24 horas del día, desde las 0:00 horas hasta las 23:00 horas, pudiendo editar las celdas de las 24 horas de cada día en la agenda, lo que sirve como perfecto recordatorio de todos los eventos que se guarden en la agenda.

Fases Lunares

En la hoja 'Moon Phases' están las 4 fases lunares de los años 2018 y 2019, con sus fechas y horas según el Horario Universal (UT - Universal Time).

Estos tiempos se ajustan para que en el calendario aparezcan convertidos a la Zona Horaria seleccionada.

Las fases de la luna se han descargado de:
Phases of the Moon
by The United States Naval Observatory (USNO)

Ayuda

En la hoja 'Help' está la ayuda que es conveniente leer alguna vez para saber más acerca de esta Nueva Agenda Calendario de Fases Lunares.

Descarga de la Agenda Calendario de Fases Lunares

Descarga el archivo desde el icono (Google Drive) o desde el enlace (Microsoft OneDrive)

New_Moon_Phases_Calendar_Planner_PW1.xlsx

Esta agenda-calendario no contiene macros y es compatible a partir de Microsoft Excel 2007.

Se debe hacer clic en el botón "Habilitar edición" si aparece un mensaje informativo de "Vista protegida" para poder usar esta plantilla, siempre que confíes en el autor de este blog, Pedro Wave.

Si esta agenda tiene éxito, en un próximo articulo explicaré cómo incluir las fases lunares de otros años. Si no, igual pasa un año hasta que publique otra vez.

Si eres curioso como yo, puedes desproteger las hojas con la contraseña: Lunar

En el siguiente enlace tienes otros muchos calendarios que he publicado hasta la fecha:

calendario | #ExcelPedroWave

Buscador de libros en Excel

Buscador de libros en Excel

Para comenzar bien el año 2017 nada como tener a mano un buen Buscador de libros en Excel, en formato pdf, ppt, doc o xls.

En la hoja "Navegador" se selecciona el tipo de archivo, el idioma, el número de resultados y los términos de búsqueda y se hace clic en el botón "Buscar", con lo que se mostrarán los resultados de la búsqueda, como enlaces a los libros.

Para guardar los enlaces, se hace clic en el botón "Cargar archivos en la biblioteca".

Biblioteca

La hoja "Biblioteca" contiene los libros encontrados, con su descripción y enlace, tipo, idioma, término de búsqueda y se pueden añadir comentarios y valorar con 5 estrellas cada uno de los libros.

Se puede descargar un libro seleccionándolo y haciendo clic en el botón "Descargar ahora".

Se puede leer el libro descargado, o abrir uno en el navegador, haciendo clic en el botón "Read More".

Descarga del Buscador de libros

Descarga el archivo desde el icono (Google Drive) o desde el enlace (Microsoft OneDrive)

Libros Excel - PW10.xlsm


A poco que uses este buscador crecerá tu biblioteca de libros en Excel.
¿Por qué no pruebas a buscar enlaces sin tipo de archivo?

Te agradeceré que escribas un comentario diciendo cuáles son los mejores libros que has encontrado en Excel y adjunta su enlace para compartirlo.

Acerca del autor del blog

Enlace a mi perfil como autor del blog:

Blogger: Excel Pedro Wave

Introducción

Mi carrera profesional me permite estar en la ola de las nuevas tecnologías, habiendo trabajado 30 años en departamentos de I+D y, en los últimos años, como ingeniero de desarrollo y pruebas de sistemas informáticos y como ingeniero de datos. Empresas en las que he prestado mis servicios: Balay, BSH, Computer International, Comercial Sistemas Informáticos - COSISA, SFS, Electrónica Aragonesa, Elasa, Amper, Siemens, Saica, Rade Technologies, Indra Software Labs, everis, Ibertech Servicios Profesionales TIC, ALTEN SPAIN, Experis IT, Indra Sistemas, IBM, BBVA y la Web.

Intereses

Blogs, datos, tecnologías Web, software, programación, TIC, IT, GUI, IGU, Excel, Power Query, Power Pivot, Power Maps, Power BI y Big Data.

Desde que me he autojubilado escribo en el blog más a menudo que antes, por lo que pienso seguir compartiendo con mis seguidores los retos y desafíos que voy superando en Excel, para sacarle todo el potencial a esta excelente herramienta multiusos, tan usada y a la vez tan incomprendida. Así, los seguidores del blog y yo mismo, mejoraremos y ampliaremos nuestros conocimientos del enorme poder de cálculo que tiene Excel, descargando multitud de ejemplos del blog.

También puedes seguirme suscribiéndote a mi canal donde puedes ver mi lista de vídeos:

YouTube - Excel Pedro Wave

Calendario Excel 2016

Calendario Perpetuo en Excel

Para comenzar bien el año 2016 nada como tener a mano un calendario anual completo y otro mensual escritos en Excel, con fotos propias de fondo que se pueden imprimir.

Las fotos las tomé en el río Ebro a su paso por Zaragoza durante la Expo del Agua del año 2008.

Para cambiar el mes o el año (del 1900 al 9999) se tienen que editar las celdas de la hoja "Calendario".

Para quienes quieran un calendario anglosajón también se puede cambiar el día de comienzo de la semana.

Descarga del Calendario Excel 2016

Descarga el archivo desde el icono (Google Drive) o desde el enlace (Microsoft OneDrive)

Calendario_PW2.xlsx


Este es el artículo más corto que he escrito hasta ahora, pero espero que sirva para seguir aumentando el número de visitas al blog, que ya pasa de medio millón. Muchas gracias a todos y

¡¡¡ Feliz Año Nuevo en Excel 2016 !!!

How to make a board game - Cómo hacer un tablero de juego

Cómo hacer un tablero de juego en Excel

Read the English translation below, towards the middle of this blog post.

He creado tres tableros de juego con un gráfico de columnas apiladas en Excel (ver cómo hacerlo aquí).

El primero es el más sencillo, con 64 cuadros coloreados alternadamente, para lo que ha hecho falta crear 16 series de datos de 8 elementos cada una, para que se muestre cada columna apilada verticalmente como 4 cuadros de color claro y otros 4 cuadros de color oscuro. Puedes verlo en la hoja "BoardGame1".

El rango de datos del gráfico es =BoardGame1!$M$4:$AC$12

El eje X va numerado del 1 al 8 en notación numérica o como letras "a-h" en notación algebraica, lo que se puede seleccionar con un desplegable en la celda M15.
El eje Y va numerado del 1 al 8, siendo visibles los valores de las series con valor 1.

El nombre de las series es "SerieYZ", siendo el valor de:
Y = la posición que ocupa la serie en el eje Y.
Z = el tipo de serie: 0 - color claro; 2 - color oscuro.

Cada par de series acabadas con un mismo valor Y ocupa esa posición en el eje Y, por lo que una serie tiene que tener 4 ceros y 4 unos alternados para formar el tablero.

Para comprender mejor cómo está construido, descarga el fichero desde los enlaces de más abajo y desprotege la hoja, que está guardada sin contraseña.

Experimenta con los valores de las series, cambiando un 0 por un 1 o por cualquier valor entre 0 y 8.
Para aprender más deberías pinchar en un cuadro del gráfico y cambia su color de relleno o lo que quieras de cada serie para ver el resultado.

Observa que para que este tablero sirva como tablero de ajedrez, el cuadro inferior derecho debe ser de color claro.

Descarga del tablero de juegos

Descarga el archivo desde el icono (Google Drive) o desde el enlace (Microsoft OneDrive)

BoardGame_PW10.xlsx


Antes de descargar estos tableros puedes verlos directamente un poco más abajo en el blog, aunque no tengas instalado Excel, donde puedes modificar las siguientes celdas de las 3 hojas con los ejemplos de tablero de juegos:

BoardGame1: M15 para cambiar la notación numérica o algebraica del eje X.

BoardGame2: M15 y M26 para cambiar el tipo de tablero: 1 - Clásica o 2 - Moderno.

BoardGame3: M15, M26, Q15 para ver las series de último movimiento, S15 y U15 para introducir las casillas de inicio y fin del último movimiento de una pieza (piezas de ajedrez no representadas).





En la hoja "BoardGame2" hacen falta 24 series nombradas "SerieYZ" siendo:
Z = el tipo de serie: 0 - color claro; 1 - color oscuro clásico y 2 - color oscuro moderno.
El tipo de tablero clásico o moderno se selecciona en la celda M26.

En la hoja "BoardGame3" hacen falta 40 series nombradas "SerieYZ" con los valores de Z anteriores mas los siguientes valores:
Z = a : series de color azul claro con la posición inicial del último movimiento de una pieza.
Z = b : series de color azul oscuro con la posición final del último movimiento de una pieza.

Para que no aparezcan estas dos series con el último movimiento, el valor de la celda Q15 = 0.
Si Q15 = 1, se colorean los cuadros de los últimos movimientos introducidos en las celdas S15 y U15, siendo su valor de dos dígitos, del tipo: AB, donde A y B valen 1 a 8.

Puedes ver cómo ha servido este tablero para ver partidas de ajedrez en el siguiente enlace:
Excel Chess Board PGN Viewer - Visor PGN Tablero de Ajedrez Excel

How to graph a board game with an Excel chart.

I have created three boards game with a stacked column chart in Excel (see how here).

The first is the simplest, with 64 squares colored alternately, for which it has been necessary to create 16 data series of 8 elements each, to be displayed each column vertically stacked like 4 squares of light colored and 4 other squares dark color. You can see it on the "BoardGame1" sheet.

The data range of the graph is =BoardGame1!$M$4:$AC$12

The X axis is numbered from 1 to 8 in numeric notation or as letters "ah" in algebraic notation, which can be selected with a dropdown in cell M15.
The Y axis is numbered from 1 to 8, the series are visibles with value 1.

The name of the series is "SerieYZ", being the value of:
Y = the position of the series on the Y axis.
Z = the type of series: 0 - light color; 2 - dark color.

Each pair of series, finished with the same Y value, occupies that position in the Y axis, so that a serie must have 4 zeroes and 4 ones to form a staggered board.

To better understand how it is built, download the file from the links below and unprotects the sheet, which is saved without a password.

You can experiment with the values of the series, changing a 0 to a 1 or any value between 0 and 8.
To learn more you should click on a square of the graph and change its fill color or whatever you want of each series to see the result.

Note that for this board serves as a chessboard, the lower right square should be light in color.

Download the board game

Download the file from the (Google Drive) icon or from the links (Microsoft OneDrive)

BoardGame_PW10.xlsx


Before download these boards you can see them directly further down in this blog, even if you have not installed Excel, where you can modify the following cells of the 3 sheets with examples of board games:

BoardGame1: M15 to change the numeric or algebraic notation from the X axis.

BoardGame2: M15 and M26 to change the board type: 1 - Classic or 2 - Modern.

BoardGame3: M15, M26, Q15 to see the last movement series. S15 and U15 to enter the start and end square with the last movement of a piece (chess pieces not shown).





In the "BoardGame2" sheet it takes 24 named series "SerieYZ" being:
Z = the serie type: 0 - light color; 1 - dark classic color; 2 - dark modern color.
The type of classic or modern board is selected in the M26 cell.

In the sheet "BoardGame3" are needed 40 series named "SerieYZ" with Z values above plus the following values:
Z = a: light blue series with the starting position of the last movement of a piece.
Z = b: dark blue series with the final position of the last movement of a piece.

To not appear these two series with the last move, the value of the cell Q15 = 0.
If Q15 = 1, the pictures of the latest moves introduced in the S15 and U15 cells are colored, and its value of two digits as AB, where A and B are worth 1-8.

You can see how it has used this board for chess games in the following link:
Excel Chess Board PGN Viewer - Visor PGN Tablero de Ajedrez Excel

Excel Chess Board PGN Viewer - Visor PGN Tablero de Ajedrez Excel

Chess Board PGN Viewer | Visor PGN Tablero de Ajedrez

Chess Board PGN Viewer is an Excel file which allows you to either view PGN format games. This viewer should currently be considered Beta quality, made entirely with a graphic chart to represent both the board and the chess pieces.

Visor PGN Tablero de Ajedrez es un archivo Excel que permite ver juegos de ajedrez en formato PGN. Este visor actualmente se debe considerar de calidad Beta, y está hecho enteramente con un único gráfico para representar tanto el tablero como las piezas del ajedrez.

Viewer Download | Descarga del Visor

Download this file viewer chess PGN made entirely with formulas and a graph in Excel. It does not contain macros and it is protected without password.

Descarga este visor de ficheros PGN de ajedrez hecho totalmente con fórmulas y un gráfico en Excel. No contiene macros y está protegido sin contraseña.

• Microsoft OneDrive: ChessBoard_PW018.xlsx 
• Sites Google Drive: ChessBoard_PW018.xlsx

(2015-11-09) Version 016:
If Q2 = 0 it is shown the edited game in algebraic format in the R36:S135 range.
Si Q2=0 se muestra la partida editada en formato algebraico en el rango R36:S135.

(2015-11-15) Version 017:
You can edit a game in numeric format in the R36:S135 range, or a mix algebraic vs numeric.
Se puede editar una partida en formato numérico en el rango R36:S135, o una mezcla de algebraico y numérico.

(2021-10-18) Version 018:

Contains 589 Capablanca chess games and, in the R36:S59 range, The Evergreen Game - Anderssen vs. Dufresne, 1852.

Contiene 589 partidas de ajedrez de Capablanca y, en el rango R36:S59, The Evergreen Game - Anderssen vs. Dufresne, 1852.

Video Viewer | Vídeo del Visor

This viewer is compatible with Excel 2007 and later.
This is the aspect of the viewer in this video that explains how to use it:

Este visor es compatible con Excel 2007 y posteriores versiones.
Este es el aspecto del visor en este vídeo que explica cómo usarlo:

Next blog post | Nuevas entradas del blog

In the next few blog post I'll write about:
En las próximas entradas del blog escribiré sobre:

1. How to graph a board game with an Excel chart.
   Cómo hacer un tablero de juego con un gráfico Excel.
2. How to display chess pieces in an Excel chart.
   Cómo mostrar piezas de ajedrez en un gráfico Excel.
3. How to convert PGN files to chess games.
   Cómo convertir ficheros en formato PGN en juegos de ajedrez.
4. How to convert algebraic to numeric chess notation.
   Cómo convertir la notación de ajedrez algebraica a numérica.

Related links | Enlaces relacionados

Based on the Excel craft by Daniel Ferry:
Excel 2007 Chess Game Viewer | Excel Hero Blog

SVG chess pieces/Maurizio Monge
Chess | Pedro Wave for Excel Guys
Wikipedia - Portable Game Notation (PGN)

Ajedrez | Pedro Wave for Excel Guys
Wikipedia - Notación portable de juego (PGN)
AyudaExcel.com - Visor de ajedrez de partidas pgn

A new chess viewer by Robert Gascon released 2021-10-17:
Excel Chess Games Viewer 2.0