Siempre que veo imágenes promocionales de un costoso software BI Dashboard, siempre muestran lo mismo: un mapa con países o regiones resaltados en diferentes colores. Envía el sutil mensaje de que los mapas son el pináculo de la inteligencia empresarial (si lo son o no es un asunto completamente diferente). Hasta Excel 2016, estos gráficos de mapas no estaban disponibles de forma nativa en Excel. Como resultado, el software BI Dashboard se destacó como algo más avanzado, tenía el factor "WOW".

Como verás en este post, con unas cuantas imágenes descargadas de internet y unas líneas de VBA podemos crear nuestros propios Maps Charts personalizados en Excel. Nuestros paneles de Excel ahora también pueden tener ese factor "WOW". Esta solución debería funcionar en cualquier versión de Excel que pueda ejecutar macros.

Incluso si tiene Excel 2016 con gráficos de mapas integrados, es posible que desee seguir este artículo. Los gráficos de mapas integrados son sorprendentes, pero al mismo tiempo tienen varias limitaciones, por lo que este enfoque puede satisfacer mejor sus necesidades.

En esta publicación, encontrará la plantilla de mapa mundial, que está disponible para descargar de forma gratuita, pero también instrucciones sobre cómo crear sus propios mapas. Mi objetivo para esta publicación es brindarle las herramientas, habilidades y conocimientos para construir sus propios gráficos de mapas personalizados.

Presentamos la plantilla de mapa mundial

Quiero compartir con ustedes el archivo de plantilla de mapa mundial. Esta plantilla incluye un mapa mundial, en el que los países cambian de color cuando cambian los valores asociados con esos países. La funcionalidad de esta plantilla se puede aplicar a cualquier mapa o cualquier forma. Ya sea que su mapa sea de estados, distritos escolares o por factores geográficos, esta plantilla se puede adaptar a cualquier escenario.

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0167 Plantilla de mapa.xlsm

Tener acceso

Usando la plantilla de mapa mundial

Debo agradecer a www.freeworldmaps.com por permitirme usar y distribuir sus imágenes del mapa mundial dentro de la plantilla del mapa mundial. Este también es un gran sitio para conseguir otros mapas.

Pruebe la plantilla usted mismo. Los países en el mapa tienen el formato de acuerdo con los nombres y valores de las columnas Q y R. El formato aplicado se basa en el color de relleno y el color del borde izquierdo en las celdas T2 – T8.

Cambiar los valores en la columna Q o cambiar las bandas de colores en las celdas T2 – T8 cambiará el formato de los países en el mapa. La función ColorToShape (que creé usando VBA) en la Columna S es el disparador que impulsa la funcionalidad.

Para que la funcionalidad funcione, los valores de la columna Q deben ser idénticos a los nombres de las formas en el mapa. Si la función ColorToShape se aplica a un nombre que no es también el nombre de una forma, no habrá ningún efecto. Pero, si el nombre de la forma existe, su color y borde cambiarán.

Al hacer clic en uno de los países se revelará el nombre de la forma.

Como algunos países, como Indonesia, están construidos con múltiples formas, cada forma se denomina "Indonesia". No es necesario que cada forma tenga un nombre único. Nombrar las formas de esta manera garantiza que el formato se aplique a todas las formas asociadas con ese país.

Creando tu propio gráfico de mapa

Esta sección cubre los pasos para crear su propio gráfico de mapa, que funcionará exactamente de la misma manera que la plantilla de mapa mundial. Se proporciona el código VBA específico para cada paso.

Descripción general

Los pasos básicos son:

• Obtenga un mapa con cada país/región como una forma separada.
• Enumera los nombres de todas las formas.
• Cambie el nombre de todas las formas a nombres utilizables.
• Obtenga una lista final de todas las formas (con nombres utilizables) y el valor relacionado con cada país/región.
• Defina las bandas de colores para formatear cada país/región.
• Utilice la función definida por el usuario ColorToShape para dar formato a las formas.

Obtenga un mapa en el formato correcto

La forma más sencilla de obtener mapas nuevos es buscar en Internet mapas integrados en archivos de PowerPoint. Necesita encontrar mapas donde cada país/región tenga una forma diferente. Simplemente copie el mapa (es decir, todas las formas) de PowerPoint a Excel. Un buen lugar para comenzar es www.freeworldmaps.net , aquí es donde obtuve las imágenes para esta publicación.

Podrías dibujar las formas tú mismo directamente en Excel, llevaría bastante tiempo, pero de todos modos es una opción.

Enumere todos los nombres de formas

Para obtener una lista de todos los nombres de formas, seleccione una celda y ejecute la macro ListAllCountries. Esta macro enumerará los nombres de todas las formas en la hoja de trabajo activa, comenzando con la celda seleccionada.

Sub ListAllCountries()Dim countryName As ShapeDim i As Long'Recorra cada forma y enumere su nombrePara cada paísName In ActiveSheet.Shapes Selection.Offset(i, 0).Value = countryName.Name i = i + 1Next countryNameEnd Sub

Cambiar el nombre de las formas

En la mayoría de los mapas que se obtienen en Internet, los nombres de las formas no son particularmente útiles; por ejemplo, la forma puede llamarse “Freeform 102” en lugar de “Australia”. En estas circunstancias, es necesario cambiar el nombre de las formas a algo utilizable. Esta puede ser la parte del proceso más dolorosa y que requiere más tiempo.

La forma más sencilla de cambiar el nombre de una forma es seleccionar la forma, cambiar el nombre en el cuadro de nombre del elemento y luego presionar Enter para confirmar el cambio.

Sin embargo, al utilizar este método, no es posible dar el mismo nombre a varias formas, lo cual está bien para algunas circunstancias pero no para otras (como el ejemplo de Indonesia mencionado anteriormente).

Otra opción es cambiar el nombre de las formas usando una función definida por el usuario que creé llamada NameCountries (consulte el código a continuación). Esta función toma la siguiente forma:

=NombrePaíses(nombreantiguo, nombre nuevo, celda de hoja de destino)

La función NameCountries toma 3 argumentos;

• oldName = el nombre actual de la forma; puede ser una referencia de una sola celda o una cadena de texto
• newName = el nombre con el que desea que se llame la forma; puede ser una referencia de una sola celda o una cadena de texto
• targetSheetCell = la referencia a cualquier celda de la hoja que contiene el mapa

Al utilizar la función definida por el usuario NameCountires, es posible dar el mismo nombre a varias formas.

El código VBA para la función NameCountries está aquí (Nota: este código debe ingresarse en un Módulo).

Función pública NameCountries(oldName As String, newName As String, _targetSheetCell As Range)Dim countryName As ShapeDim targetSheetName As String'Obtenga el nombre de la hoja que contiene el mapatargetSheetName = targetSheetCell.Parent.Name'Recorra cada forma en la hoja que contiene the mapFor Each countryName In Sheets(targetSheetName).Shapes 'Cambia el nombre de su nombre existente a un nuevo nombre If oldName = countryName.Name Then countryName.Name = newName End IfNext countryNameEnd Function

Enumere todos los nombres de las formas. . . otra vez (con los valores)

Continúe ejecutando la macro ListAllCountries y la función NameCountries hasta que esté satisfecho con todos los nombres.

A continuación, asigne a cada forma un valor basado en los datos que desea representar (por ejemplo, temperatura, ingresos, esperanza de vida, calificaciones escolares o cualquier otra cosa para la que desee utilizar el mapa).

Crea las bandas de colores y los valores.

A continuación, cree los rangos que se aplicarán a los países. El color de relleno y el color del borde izquierdo se aplicarán a cada una de las formas.

Para los fines de la función definida por el usuario ColorToShape (ver más abajo), es necesario que las bandas vayan desde el valor más pequeño en la parte superior de la lista hasta el valor más grande en la parte inferior de la lista.

Las formas del mapa no tienen un 'color predeterminado', solo cambiarán cuando la función ColorToShape las obligue (ver más abajo). Para garantizar que el formato sea correcto, los valores de cada país deben estar entre el valor más bajo y el más alto en el rango de bandas de colores.

Función ColorToShape

Las macros y las funciones definidas por el usuario hasta ahora han sido únicamente para configurar la plantilla del mapa de la manera correcta. Es la función ColorToShape la que controla la plantilla de trabajo.

La función ColorToShape toma 4 argumentos:

=ColorToShape(nombre del país, valor de búsqueda, rango de búsqueda, celda de hoja de destino)

• countryName = el nombre de la forma, puede ser una cadena o una referencia de celda única a una cadena.
• lookupValue = el valor asociado con el país/región/forma, puede ser un valor o una referencia de celda única a un valor.
• lookupRange = el rango de celdas que contienen los valores y las bandas de colores que se aplicarán a los países/regiones/formas.
• targetSheetCell = la referencia a cualquier celda de la hoja que contiene el mapa

Cuando se vuelve a calcular esta función, cambiará el color de las formas según lookupValue y lookupRange.

El código VBA para la función ColorToShape es: (Nota: este código debe ingresarse en un Módulo):

Función pública ColorToShape(countryName As String, lookupValue As Double, _lookupRange As Range, targetSheetCell as Range)'Forzar recálculoApplication.VolatileDim countryShape As ShapeDim lookupCell As RangeDim targetSheetName As StringtargetSheetName = targetSheetCell.Parent.Name'Establecer el valor predeterminado de la función en "No Found"ColorToShape = "Not Found"'Recorre cada forma en la hoja de trabajo que contiene el mapaPara cada paísForma en hojas (targetSheetName).Shapes 'Si la forma y el nombre del país tienen el mismo nombre, entonces Si countryShape.Name = countryName Entonces 'Recorre cada uno celda en el rango de bandas de color Para cada celda de búsqueda en rango de búsqueda 'Si el valor es menor o igual que las bandas de color, entonces Si lookupCell.Value = lookupValue Entonces 'Aplica el color de relleno de las bandas de color countryShape.Fill.ForeColor.RGB = _ RGB(Color2RGB(lookupCell.Interior.Color, "R"), _ Color2RGB(lookupCell.Interior.Color, "G"), _ Color2RGB(lookupCell.Interior.Color, "B")) 'Aplica el borde izquierdo del bandas coloreadas countryShape.Line.ForeColor.RGB = _ RGB(Color2RGB(lookupCell.Borders(xlEdgeLeft).Color, "R"), _ Color2RGB(lookupCell.Borders(xlEdgeLeft).Color, "G"), _ Color2RGB(lookupCell .Borders(xlEdgeLeft).Color, "B")) 'Cambie el resultado de la UDF a "Formatted" ColorToShape = "Formatted" 'Formato aplicado, deje de recorrer Exit For End If Next lookupCell End IfNext countryShapeEnd Function

La función ColorToShape llama a otra función para obtener la referencia de color RGB correcta. Incluya el siguiente código dentro del mismo módulo que la función ColorToShape.

Función privada Color2RGB(Color tan largo, ColorSelect como cadena) Tan largoDim Rojo Tan largoDim Verde Tan largoDim Azul Como largoRojo = Color Mod 256Verde = Color 256 Mod 256Azul = Color 65536 Mod 256Seleccionar caso ColorSeleccionar caso "R" Color2RGB = Rojo Caso "G" Color2RGB = Verde Caso "B" Color2RGB = Azul Final Seleccionar Función Final

Conclusión

Con suerte, podrá ver el sutil mensaje de que Excel no es una buena herramienta de Business Intelligence y tiene fallas. Un poco de truco de Excel/VBA es todo lo que se necesita para convertir Excel en una herramienta que pueda crear gráficos de mapas. Las costosas herramientas de BI serán mucho más fáciles de configurar, pero sus opciones serán limitadas. Con este método, Excel se puede convertir en cualquier cosa que desees; por ejemplo, si quieres un mapa de la Tierra Media del Señor de los Anillos, puedes lograrlo con Excel.