Las macros de los archivos descargados están bloqueadas por defecto. Para
desbloquear las macros debes modificar las Propiedades del archivo
siguiendo estas instrucciones:
Abre el archivo y presiona el botón: Habilitar edición cuando
aparezca el aviso de VISTA PROTEGIDA.
Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA
DE SEGURIDAD Las macros se han deshabilitado o se deshabilitó parte del
contenido activo.
Las hojas están protegidas sin contraseña, y no está protegido el proyecto
VBA, por lo que puedes estudiar y analizar el código de las macros.
ATENCIÓN:
Se pueden modificar estos libros de Excel respetando esta licencia:
🔝Select the language of this blog post in the Google box at the top
left.
Actualización 2023-12-22:Nueva calculadora Excel para bajarse la lista definitiva de premios de la Lotería Nacional de Navidad española del 22
de diciembre de 2023, y comprobar si nos tocaron los números que jugábamos.
Actualiza los premios con el menú: Datos | Actualizar todo (Control +
Alt + F5)
NOTA: Este artículo lo publiqué por primera vez el 22 de diciembre de
2014 y cada año lo actualizo con el nuevo sorteo de Navidad. La próxima
actualización será el próximo viernes 22 de diciembre de 2024.
¿Y si nos ha tocado la lotería? ¡Cosa estadísticamente muy poco
probable!
¿Y si nos toca pagar la lotería? ¡Cosa segura!
O acaso hay alguien tan boyante económicamente que pueda pagarla en metálico
o en "negro".
Si lo normal es ¡tener que pedir un crédito! para poder comprar los décimos
de la lotería...
ATENCIÓN:
He ampliado a 20 los números a comprobar si están premiados.
Calculadora de visitas a este blog
Adelanto que a mí tampoco me ha tocado la lotería, pero voy camino de acabar
el año con cerca del millón ciento cincuenta mil visitas a este blog, gracias a lectores como tú que se interesan por los artículos que voy
escribiendo sobre Excel, siempre que puedo.
Mi lotería
No esperes que adivine los números premiados en la lotería, pues la lotería
es un asunto cuyo resultado depende de la suerte o la casualidad.
Para comprobar los números de la
Lotería de Navidad con Excel ¡como a mí me gusta! y ¡saber si aún
debemos pagar 21 € por un café!, si nos pasa como al del anuncio de hace
unos años, que no tenía ni metálico para comprarla, aquí va mi propuesta:
En lugar de bajar a la cafetería a que nos entreguen un sobre cerrado, que
no sabemos que contiene y darnos una sorpresa, podemos bajarnos la
Calculadora de Premios de la Lotería de Navidad en Excel (descárgala un poco más abajo).
Si alguno sigue sin querer bajar a la cafetería, que busque en Internet:
A mí, como a la mayoría, me toca pagar todos los años la lotería a los
afortunados a los que les ha tocado el Gordo de Navidad y a
¡Hacienda somos todos!
Actualiza los premios con el menú: Datos | Actualizar todo (Control +
Alt + F5)
Esta plantilla tiene la primera hoja protegida sin contraseña y no contiene
macros, únicamente tiene una conexión a las tablas de la Lotería de Navidad
española, y todo el cálculo se realiza ¡sólo con fórmulas!
INSTRUCCIONES:
0) Hacer clic en "Habilitar edición" en respuesta a:
Este archivo procede de Internet y podría no ser seguro
1) Hacer clic en "Habilitar contenido" en respuesta a la
Advertencia de seguridad:
Se han deshabilitado las conexiones de datos externos
2) Actualizar la tabla de la hoja 'Alambres' con el menú:
Datos | Actualizar todo (Control + Alt + F5)
3) Actualizar la tabla dinámica de la hoja 'Números' con el menú:
Datos | Actualizar (Alt + F5)
Videotutorial de la Calculadora de Premios
Problemas con la descarga de la lista oficial
Con este aporte se pueden descargar los números de la página oficial, pues
desde el archivo pdf, que publica Loterías y Apuestas del Estado, no se
permite la copia de los números premiados.
La descarga del archivo pdf se hace desde este enlace:
La imagen con los Permisos del archivo muestra las opciones de
seguridad del archivo pdf, descargado con la lista completa de los números
de la lotería, comprobando que no se permite nada excepto Imprimir
(en "Alta resolución"). No se permite la copia de contenido y ni
siquiera está activada la accesibilidad al contenido, por lo que no ayuda a
obtener la lista de números premiados en Excel.
Cómo funciona la Calculadora de Premios
Con mi plantilla se establece una conexión a la web de
Loterías y Apuestas del Estado:
En la consulta web es importante hacer clic en cada uno de los 10 alambres
de la Tabla 1, para que queden marcados todos los alambres. Las 9 tablas
restantes se importan automáticamente.
Y se consigue un listado de hasta 20 números premiados en cada uno de los 10
alambres de cada una de las 10 tablas de extracciones, generando
automáticamente y pudiendo consultar todos los números premiados el 22 de
diciembre. Los reintegros de la última cifra del premio "GORDO" se
calculan con una fórmula adicional.
Por supuesto que mi plantilla no contiene ninguno de los números de la
lotería pues, si así fuera, tendría que pagar al Estado "royalties"
(en la lengua española serían regalías) y cada cual es libre de
conectar y descargar los números premiados.
🔝To translate this blog post to your language, select it in the top left
Google box.
Volando por el mundo
Como resulta demasiado fácil volar de un país a otro del mundo,
será cuestión de visitar los países del mundo de una forma distinta.
¡Con una hoja de cálculo!
Volar por el mundo con Excel no contamina tanto como volar en avión.
Se llega a cualquier país del mundo con solo mover el ratón.
Se conocen los datos y las banderas de cada país que se sobrevuela.
Se aprenden los trucos con los que ha sido diseñado este mapa.
Comparte este mapa con todo el mundo para conocer todos los países
del mundo.
Volando por el mundo puede ser muy didáctico en la enseñanza en las
escuelas, cursos de geografía, y en las empresas cuyo objetivo sea tener
clientes en todo el mundo, con solo adaptarlo al negocio o a la educación.
Mapa interactivo del mundo
Este mapa del mundo es el más interactivo que he hecho en Excel hasta ahora:
Interactúa con las formas de 240 países dibujadas en el mapa del mundo.
Interactúa con los países sobre los que pasa el puntero del ratón.
Interactúa con el nuevo Control de Usuario con información de cada país.
Interactúa con el modo de vuelo del puntero con el nombre, capital y
bandera de un país.
Interactúa con el modo chincheta sin puntero.
Interactúa con el buscador de países con el control de cuadro combinado.
Interactúa con el tamaño de la información de un país.
Interactúa con la voz que pronuncia el nombre del país.
Interactúa con las etiquetas con los nombres de los países.
Interactúa con el reinicio y la actualización del mapa.
Interactúa con el zoom del mapa.
Interactúa con el desplazamiento horizontal o vertical del mapa.
Interactúa con la Wikipedia para obtener más información de los países,
sus capitales, idiomas, monedas y código ISO 3166-1 de cada país.
Interactúa con este artículo del blog pulsando en el símbolo de
información que está arriba a la derecha del Control de Usuario.
¡No encontrarás ninguno igual!
El nuevo Control de Usuario permite controlar todos los países del
mapa del mundo.
Lista de Controles de Usuario (identificados con números dentro de
círculos):
(1) Nombre del país 🗺️, con enlace a datos del país en la Wikipedia.
(2) Enlace a este artículo del blog con esta información.
(3) Nombre oficial del país.
(4) Capital del país con enlace a la Wikipedia.
(5) Población actualizada y superficie del país.
(6) Idiomas oficiales con enlace a la Wikipedia para el primer
idioma.
(7) Monedas del país con enlace a la Wikipedia.
(8) Código ISO 3166-1 con las 3 letras que identifican al país o
texto alternativo.
(9) Tecla de voz 🗣️, para pronunciar el nombre del país cuando está
en rojo.
(10) Tecla de aumento o disminución del tamaño del Control de
Usuario.
(11) Tecla para mostrar el Control de Usuario a la derecha ▶️ o a la
izquierda ◀️ del mapa.
(12) Tecla de modo Avión 🛩️, con un puntero y el nombre del país que
se sobrevuela.
(13) Tecla de modo Casa 🏡, con un puntero sin información.
(14) Tecla de modo Mundo 🌍, con un puntero con el nombre, la capital
y la bandera del país que se sobrevuela.
(15) Tecla de modo Chincheta 📌, sin puntero aunque con un cuadro de
búsqueda de países.
(16) Buscador de países en una lista desplegable de un cuadro
combinado. Escribir caracteres comodín: asterisco * (para cualquier
carácter) o interrogante ? (para un carácter). Si es pequeña la superficie
del país se remarca con un cuadrado.
(17) Lista desplegable de países para seleccionar con el ratón o con
las teclas de flechas o de avance y retroceso de página.
(18) Teclas de control del zoom del mapa:
ZM: Zoom Máximo del 400%
Zm: Zoom mínimo del 75%
Z0: Zoom al 100%
Z+: Zoom + 50
Z-: Zoom - 50
(19) Teclas con 4 flechas de desplazamiento del mapa: arriba⬆️,
abajo⬇️, derecha➡️ e izquierda⬅️
(20) Tecla de etiqueta 💬 para mostrar el nombre del país en el mapa
si está en rojo.
(21) Tecla 🔁 de reinicio del mapa y de actualización de la población
de cada país.
(22) Seleccionar cualquier país del mapa haciendo clic sobre una de
las 240 formas de países.
Descarga Volando por el mundo
Este mapa del mundo es compatible con todas las versiones de escritorio,
desde Excel 2010 hasta Excel para Microsoft 365.
Descarga la versión 1.0 desde uno de estos enlaces:
Las macros del archivo descargado están bloqueadas por defecto. Para
desbloquear las macros debes modificar las Propiedades del archivo
siguiendo estas instrucciones:
Abre el archivo y presiona el botón: Habilitar edición cuando
aparezca el aviso de VISTA PROTEGIDA.
Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA
DE SEGURIDAD Las macros se han deshabilitado o se deshabilitó parte del
contenido activo.
Las hojas están protegidas sin contraseña y no está protegido el proyecto
VBA, por lo que puedes estudiar y analizar el código de las macros.
ATENCIÓN:
Se puede modificar este libro de Excel respetando esta licencia:
Con este vídeo aprenderás a volar por el mundo sin salir de Excel y
visitarás cualquier país, obteniendo información de su población actual y de
más datos del país mediante enlaces a la Wikipedia.
Todos mis mapas del mundo
Esta es la última entrega de los 5 artículos sobre mapas del mundo que he
estado publicando desde hace dos meses.
En los cuatro artículos anteriores he explicado cómo he ido desarrollando
este proyecto, por si te interesa estudiar cómo lo he hecho:
🔝To translate this blog post to your language, select it in the top left
Google box.
Buscador de países en un mapa mundial
Para encontrar un país en un mapa del mundo hace falta un
Buscador de países con el que sea fácil encontrar cualquiera de
los 240 países dentro del mapa hecho en Excel con formas (shapes).
Con este Buscador de países se escriben las iniciales del país, por
ejemplo las letras: es y se encuentra rápidamente el país
buscado, en este caso: España entre los cinco que empiezan por
esas dos letras.
También se pueden introducir caracteres comodín como asteriscos (*) o
interrogantes (?), por ejemplo *pa?a obteniendo una lista de 4
países, entre ellos: España.
Y se puede elegir un país en el mapa haciendo clic sobre el mismo, con lo
que aparece el nombre del país en el cuadro combinado.
Con este buscador de 240 países es fácil estudiar y aprender todos los
países del mundo sin salir de Excel.
Problema del Buscador de países solucionado
La primera versión que hice del Buscador de países se cerraba
intempestivamente sin dar ningún aviso cuando presionaba las teclas de
avance y retroceso de página, <Av Pág> y <Re Pág>, o las
teclas de flecha arriba y abajo.
El cierre de Excel se producía en la versión más reciente que tengo que es
Excel para Microsoft 365, pues en Excel 2010 no ocurría.¡Misterios de la ciencia y la técnica moderna!
Excel para MS365 se abría de nuevo avisándome que reparara el libro:
Como se por experiencia que es más dañino reparar un libro, porque se
pierden objetos y datos por el camino, mientras se me ocurría como reparar
las macros, cree este tema en el foro para ver si algún experto en Excel me
podía ayudar:
Mi amigo Macro Antonio vino en mi ayuda indicándome cuál era la macro
que generaba el cierre de Excel y a partir de ahí creo que conseguí resolver
el problema.
A ese hilo del
foro TodoExcel.com subí las 4 versiones del Buscador de países que hice para
resolver el problema, por si quieres investigar por tu cuenta, que de los
fallos es desde donde más se aprende... 🤔
Descarga el Buscador de países
Descarga la versión 4.0 desde uno de estos enlaces:
Las macros del archivo descargado están bloqueadas por defecto. Para
desbloquear las macros debes modificar las Propiedades del archivo
siguiendo estas instrucciones:
Abre el archivo y presiona el botón: Habilitar edición cuando
aparezca el aviso de VISTA PROTEGIDA.
Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA
DE SEGURIDAD Las macros se han deshabilitado o se deshabilitó parte del
contenido activo.
Las hojas están protegidas sin contraseña y no está protegido el proyecto
VBA, por lo que puedes estudiar y analizar el código de las macros.
ATENCIÓN:
Se puede modificar este libro de Excel respetando esta licencia:
En este vídeo explico cómo usar el Buscador de países y cómo lo he hecho
para que sea compatible con todas las versiones desde Excel 2010 hasta Excel
para Microsoft 365.
Para conocer más detalles sigue leyendo.
Cómo insertar un cuadro combinado
Para insertar un cuadro combinado del tipo control ActiveX en una hoja de
cálculo lee estas instrucciones:
Selecciona el de abajo: Cuadro combinado (control ActiveX).
Haciendo clic con el botón derecho del ratón sobre el cuadro combinado hay
que editar algunas de sus Propiedades por categorías:
Apariencia: se modifica BackColor con el valor: &H80000018&
para darle un color de fondo amarillo claro.
Comportamiento: se cambia MatchEntry a: 2-fmMatchEntryNone,
para poder escribir cualquier cosa en la caja del cuadro combinado.
Dato: se cambia ListRows por ejemplo a 8 o 10, con el
número de elementos a desplegar.
Varias: cambiar el nombre (Name): ComboBoxBuscador
Varias: editar LinkedCell con: Buscador!$K$1, la
celda donde escribirá el cuadro combinado.
Varias: editar ListFillRange con: miListaPaíses,
que es un nombre definido con la lista de países que se mostrará en el
desplegable del cuadro combinado.
El resto de propiedades se dejan con su valor por defecto.
Hoja 'Buscador'
En la hoja 'Buscador' está la inteligencia natural con la que consigo
obtener la lista de países que se mostrará en el desplegable del cuadro
combinado.
Se podría hacer fácilmente con la inteligencia artificial de las nuevas
funciones de Excel, pero entonces este Buscador no sería compatible con
todas las versiones de Excel, desde la antigua versión de Excel 2010 hasta
la más reciente versión de Excel para Microsoft 365.
La celda K1, denominada miBúsqueda, está vinculada al cuadro
combinado y es el país o el texto que se muestra en él.
La celda K3, denominada miDesplegable, es el texto con el que se
obtiene la lista desplegable de países y puede incluir caracteres comodín (*
o ?) .
En la columna A está la lista completa de países ordenados alfabéticamente
dentro de la tabla: TablaBuscador
En la columna C se obtienen todos los países buscados repetidos que
comienzan por el texto de miDesplegable, dentro de la
tabla: TablaPaísesBuscados
Esta búsqueda se hace con la fórmula de la celda C2 que se arrastra
hacia abajo hasta la fila 244:
Como en el rango $A2:$A$244 la celda inicial es relativa, la fórmula busca
en el rango de celdas de su fila hasta la fila final, que es una referencia
absoluta.
En la columna E se obtiene
la lista de países buscados y no repetidos con esta fórmula
matricial:
Esta fórmula se debe introducir en Excel 2010 con la combinación de teclas:
Control + Mayús + Intro.
En las versiones recientes de Excel se introduce tal cual, pues la
interpreta como una fórmula de matriz dinámica que se explica en el
siguiente artículo:
El problema es que sigue siendo una lista con 244 filas.
En la columna I se obtiene la lista de países que se desplegarán
en el cuadro combinado, en un rango de filas con el número de países
encontrados, gracias a esta fórmula matricial:
Con esta última fórmula se crea en el Administrador de nombres el
nombre definido: miListaPaíses, con el que se ha editado la propiedad
del cuadro combinado: ListFillRange
Con este nombre definido se consigue desplegar dinámicamente la lista de
países buscados en el desplegable del cuadro combinado de la hoja
'Mapa'.
Cómo busca los países en el mapa
Para buscar los países hacen falta macros que relacionen el nombre del país
con la forma (shape) del país en el mapa.
Lo primero es detectar el evento de foco en el cuadro combinado de la hoja
'Mapa':
Este evento llama a la macro que despliega la lista de 240 países en el
cuadro combinado:
La escritura de cualquier texto en el cuadro combinado se escucha con el
evento:
Este evento llama a la macro que busca los países:
Si no hay nada escrito, borra los colores de los países en el mapa.
Si se escribe un texto con o sin comodines que no sea el nombre de un país,
despliega la lista de países buscados.
Si se escribe el nombre de un país, o se selecciona con el ratón o con las
teclas de flechas o de avance o retroceso de página, se colorea el país en
el mapa.
Si se hace clic sobre un país en el mapa, se escribe su nombre en el cuadro
combinado, con la macro que tienen asignada todas las formas (shapes) de los
países:
Colección de mapas del mundo
Esta es la cuarta entrega de un mapa del mundo que pronto podrás descargar y
probar, y que incorporará todas las funciones y características que voy
publicando estas últimas semanas aquí:
🔝To translate this blog post to your language, select it in the top left
Google box.
Nuevo Control de Usuario personalizable
Para controlar el mapa del mundo, que estoy desarrollando y que pronto
publicaré, he diseñado un nuevo Control de Usuario personalizable,
con el que no hay necesidad de saber programar Formularios de Usuario
(UserForm) en VBA.
Lo he creado con dos formas (shapes), una con una captura de un rango de
celdas con la
cámara de Excel, y otra con un control de etiqueta (control ActiveX) con el que detecto
los clics con el ratón sobre la forma del nuevo control.
Con este nuevo Control de Usuario puedo mostrar datos de cualquier
país y personalizar cualquier acción sobre el mapa: zoom, scroll, voz,
etiquetas, reinicio, modo de vuelo, tamaño del control, enlaces externos,
etc.
En esta imagen animada está situado arriba a la derecha con los datos de
España.
Si quieres saber cómo lo he hecho, o si quieres incorporarlo en tus
proyectos, ¡sigue leyendo!
Diseño del nuevo Control de Usuario
El aspecto del nuevo Control de Usuario se ha diseñado en la hoja
'Información', con fórmulas, formatos condicionales e insertando
símbolos para las teclas.
El nombre del país está en el rango B2:G3 con hipervínculos creados en una
fórmula para visitar el país en la Wikipedia.
El nombre oficial está en el rango B4:G5 y su capital en el rango B6:G6.
La población en el rango B7:G7 y la superficie en el rango B8:G8.
Los idiomas en el rango B9:G10, las monedas en el rango B11:G12 y el Código
ISO 3166-1 del país con su abreviatura en 3 letras en el rango B13:G14.
Lo interesante viene en el rango B15:G17 con 18 celdas, en 3 filas y 6
columnas, que serán las teclas virtuales de este nuevo Control de Usuario.
Para situarse en este control se definen el eje X con 6 columnas y el eje Y
con 16 filas.
La última tecla pulsada se indica en la celda F20 para el valor de X y en la
celda H20 para el valor de Y.
El modo de vuelo seleccionado queda guardado en la celda B20. En este
ejemplo solamente puede ser la chincheta por simplificación, pues la
selección del país no se hace con el "puntero" que se comentó en artículos
anteriores, sino que se hace directamente pinchando en las formas de los
países.
He insertado símbolos de la fuente de caracteres Webdings para crear las
teclas y caracteres Wingdings para crear las flechas.
El color de las teclas por defecto es azul y cambia a color rojo o a un
color de fondo más oscuro con dos formatos condicionales en la hoja
'Información'. Ver la siguiente imagen.
Si usas este nuevo Control de Usuario ya no tendrás que programar
Formularios de Usuario tan rígidos sino que podrás diseñar el control dentro
de una hoja de Excel con las ventajas de personalización del diseño y de los
formatos que se pueden representar dentro de las celdas.
Visualización del nuevo Control de Usuario
En la hoja 'Mapa' se visualiza arriba a la derecha el nuevo Control
de Usuario.
Se visualiza con la forma (shape) de la imagen capturada por la
cámara de Excel, denominada F_Información
=Información!$B$2:$G$17
con la fórmula apuntando al rango de celdas de la hoja 'Información'
que aparecen en el cuadro de control.
Se controla su uso con un control de etiqueta (control ActiveX) denominado:
F_Etiqueta_Info.
Este control de etiqueta está por encima de la imagen, para obtener la
posición del cursor sobre el control cuando se hace clic sobre él, con lo
que se determina qué tecla actúa. Se puede hacer clic en cualquier parte del
control.
Programación del Control de Usuario
En la hoja 'Mapa' del proyecto VBA he codificado el evento
F_Etiqueta_Info_MouseDown, con el que se obtienen las coordenadas X e
Y al hacer clic con el ratón sobre el Control de Usuario.
Este evento llama a la macro: ControlMapa, pasándole la posición X e
Y relativa a la anchura y a la altura del control, respectivamente.
Lo importante está en estas dos instrucciones que obtienen la posición
relativa en el rango de celdas Información!B2:G17 y escriben X e Y en las
celdas F20 y H20 respectivamente:
Las macros del archivo descargado están bloqueadas por defecto. Para
desbloquear las macros debes modificar las Propiedades del archivo
siguiendo estas instrucciones:
Abre el archivo y presiona el botón: Habilitar edición cuando
aparezca el aviso de VISTA PROTEGIDA.
Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA
DE SEGURIDAD Las macros se han deshabilitado o se deshabilitó parte del
contenido activo.
Las hojas están protegidas sin contraseña y no está protegido el proyecto
VBA, por lo que puedes estudiar y analizar el código de las macros.
ATENCIÓN:
Se puede modificar este libro de Excel respetando esta licencia:
Este ejercicio de diseño y desarrollo lo hago en plan educativo, en primer
lugar como reto a mis neuronas para que no envejezcan demasiado pronto, y en
segundo lugar como ideas educativas para los maestros que enseñan Excel en
sus clases, sin olvidar enseñar a quienes no hayan visto aún un mapa en su
vida como usuarios de Excel que ¡sí, se puede!
Pronto publicaré un mapa completo del mundo con todas las funciones y
características que voy publicando estas últimas semanas aquí:
🔝To translate this blog post to your language, select it in the top left
Google box.
Mientras diseño un mapa del mundo me he encontrado con la tarea de calcular
la localización de cada país en el mapa mundial.
Cada vez que cambia el zoom o el scroll del mapa tengo que recalcular la
posición de los países, por lo que el algoritmo de cálculo debe ser
eficiente y con un rendimiento máximo para que ese cálculo no interfiera en
el manejo del mapa.
Explicaré por qué utilizar una matriz para recopilar datos de las formas de
lo países y escribir en la matriz en lugar de escribir directamente en un
rango de celdas de la hoja de trabajo.
En estos artículos encontrarás más información de las
matrices (array) y de las formas (shapes):
La transferencia de datos entre celdas de la hoja de cálculo y variables
de VBA es una operación costosa en tiempo de ejecución, por lo que debe
reducirse lo más posible. Puede aumentar considerablemente el rendimiento
de su aplicación Excel pasando matrices de datos a la hoja de cálculo, y
viceversa, en una sola operación en lugar de una celda cada vez. Si
necesita realizar cálculos extensos sobre datos en VBA, debe transferir
todos los valores de la hoja de trabajo a una matriz, hacer los cálculos
en la matriz y luego escribir la matriz nuevamente en la hoja de trabajo.
Esto mantiene al mínimo la cantidad de veces que se transfieren datos
entre la hoja de trabajo y VBA. Es mucho más eficaz transferir en una
única instrucción una matriz de 100 valores a la hoja de cálculo que
transferir cada uno de los 100 elementos separadamente en una celda
diferente.
Con esta técnica de cargar la matriz y escribirla en las celdas una sola
vez, he conseguido tiempos de ejecución de menos de medio segundo, cuando un
bucle para escribir separadamente en las celdas no baja de 60 segundos.
Como se aprende practicando, os dejo un ejemplo con las dos macros, la lenta
y la rápida.
Cómo guardar las formas de los países en una tabla
El problema es que en la hoja 'Mapa' hay formas (shapes) de 240
países que hay que guardar en una tabla de la hoja 'Fronteras', para
lo que hace falta una macro que escriba en la tabla algunas de las
propiedades de las 240 formas, lo que se hace con un bucle de dos maneras
diferentes.
Las propiedades de las formas se guardan en la tabla "TablaFronteras" en las
5 primeras columnas, el resto de columnas se calculan con fórmulas que hay
que mantener.
Normalmente se programa la macro lenta si no se conoce la técnica de la
macro rápida, que mejora el rendimiento al usar matrices (arrays). A
continuación explicaré la diferencia principal entre estas dos macros.
Macro lenta
Se escribe cada celda dentro de un bucle a la vez que se leen las
propiedades de cada una de las formas (shapes) de cada país del mapa. Este
método es ineficiente pues consume mucho tiempo escribir celdas
individualmente, pues las macros VBA y Excel son dos mundos separados y el
interfaz de conexión entre ellos no está optimizado internamente.
Macro rápida
Con un bucle se escriben las propiedades de cada forma (shape) de los países
en una matriz (array) bidimensional, que se copia en el rango de celdas de
la tabla con una única instrucción, lo que mejora su rendimiento pues es el
método más eficiente.
La única instrucción que copia la matriz en el rango está optimizada
internamente para pasar valores entre VBA y la hoja de cálculo.
Vídeo para mejorar el rendimiento
En este vídeo explico cómo usar las dos macros y calcular su rendimiento.
Descarga el archivo con las macros
Descarga la versión 2.0 desde uno de estos enlaces:
Las macros del archivo descargado están bloqueadas por defecto. Para
desbloquear las macros debes modificar las Propiedades del archivo
siguiendo estas instrucciones:
Abre el archivo y presiona el botón: Habilitar edición cuando
aparezca el aviso de VISTA PROTEGIDA.
Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA
DE SEGURIDAD Las macros se han deshabilitado o se deshabilitó parte del
contenido activo.
Las hojas no están protegidas, y no está protegido el proyecto VBA, por lo
que puedes estudiar y analizar el código de las macros.
ATENCIÓN:
Se puede modificar este libro de Excel respetando esta licencia:
Con el archivo descargado los tiempos de ejecución son mucho más rápidos que
en el vídeo pues es un ejemplo reducido.
Macro lenta: >0,6 segundos
Macro rápida: <0,05 segundos
El rendimiento es de más de 1 a 10 con matrices.
Los tiempos en el vídeo son con el archivo que estoy diseñando ¡es el caso
real!:
Macro lenta: >60 segundos
Macro rápida: <0,5 segundos
El rendimiento es de más de 1 a 100 con matrices.
La macro lenta se ejecuta en 0,6 segundos en un archivo reducido, pero ese
tiempo es de 60 segundos en el archivo real del vídeo. El escalado empeora
el tiempo en un factor de 100.
La macro rápida se ejecuta en 0,05 segundos en un archivo reducido, y en 0,5
segundos en el archivo real del vídeo. El escalado solo empeora el tiempo en
un factor de 10, siendo razonables los 0,5 segundos que es mejor tiempo para
la macro rápida que el mejor tiempo de la macro lenta en un archivo
reducido.
A veces no tenemos en cuenta que el rendimiento de los algoritmos no
optimizados empeora con el escalado de las aplicaciones.
Una macro que parece rápida y eficaz se vuelve lenta y torpe cuando las
hojas de cálculo crecen, pues no están optimizadas para el escalado y el
rendimiento óptimo, que hay que tener en cuenta desde la primera versión del
algoritmo si no queremos encontrarnos sorpresas desagradables cuando el
proyecto crezca.
Para la aplicación que estoy desarrollando de un
Mapa del mundo es importante que funcione en todo tipo de
máquinas, también en las lentas con versiones antiguas de Excel.
Los tiempos de la macro rápida en mi viejo portátil con Excel 2010 corriendo
en Windows 7 son similares a los de mi nuevo portátil con Excel para
Microsoft 365 en Windows 11. La macro lenta tiene un rendimiento un 100%
inferior en el viejo portátil.
Actualización de las macros
En la versión 2.0 he incluido varias macros más de este hilo:
🔝To translate this blog post to your language, select it in the top left
Google box.
Cuando diseñé el
Juego de mapas en Excel
del artículo anterior, me quedé con las ganas de pasar el ratón por encima
del mapa e ir viendo qué país estaba debajo del cursor, sin necesidad de
hacer clic en el mapa con el botón izquierdo del ratón.
En esta imagen animada puedes comprobar que ya lo he conseguido, gracias a
la ayuda recibida en este foro de Excel al que recomiendo que te registres
(no cobro comisión):
Cuando descargues este mapa podrás estudiar los países de África pasando el
"puntero" del ratón por encima del mapa.
🕸️ Aprenderás datos de los países africanos obtenidos con Power Query.
🐭 Dale con el ratón a la celda D4 y cambiarás el modo de visualización de
los datos en el mapa:
🌍 En modo "globo" los datos siguen al "puntero".
🛩️ En modo "avión" el nombre del país sigue al "puntero".
🏚 En modo "casa" los datos no siguen al "puntero".
📌 En modo "chincheta" los datos están fijos y no se usa el "puntero".
🗣️ Dale a la cabeza parlante en la celda D5 para escuchar el nombre de los
países.
♻️ Si algo no funciona reinicia el mapa dando a la celda E19, con lo que
también se actualizarán los datos de África con Power Query y se maximizará
el mapa mediante un zoom automático.
Puntero al mapa
He creado un "puntero" virtual que sigue al puntero del ratón en
todos los movimientos sobre la hoja de cálculo 'Mapa'.
El "puntero" es un círculo pequeño creado desde la cinta de opciones:
Insertar > Formas > Formas básicas > Elipse, a la que se le ha dado
la misma altura que anchura de 0,2 cm para crear el círculo. A esta forma
del "puntero" la denomino:
F_Puntero
Al "puntero" virtual le sigue, como un perrito faldero, una "imagen
dinámica" de un rango de celdas capturada con
la cámara de Excel (información aquí), con lo que se consigue mostrar dinámicamente el contenido del rango de
celdas en un "Objeto Volante Identificado - OVI", por lo que no se puede
clasificar como OVNI. A esta "imagen dinámica" la denomino:
F_País =$C$2:$C$5
En el rango C2:C5 debe estar el texto y la bandera.
Con el "texto dinámico" se muestra el nombre del país y opcionalmente su
población actual, con un estilo de WordArt para que el texto destaque en el
mapa. A este "texto dinámico" lo denomino: F_Texto
La "bandera" es una imagen dinámica capturada con la cámara de Excel. A esta
"bandera" la denomino: F_Bandera =miBandera, que muestra la
bandera de un país a partir de la columna I de la hoja 'Fronteras'.
ATENCIÓN: La imagen de arriba es de una versión anterior en la que esas dos
formas estaban en la hoja 'Mapa'. En la nueva versión se han movido a
la hoja 'Fronteras' el "texto dinámico" (F_Texto) y la
"bandera dinámica" (F_Bandera), con el texto sin solapar con la
bandera.
IMPORTANTE:
El orden de las formas es muy importante para conseguir el efecto de capas
de dibujo en el mapa, y que las formas (shapes) sean activadas
adecuadamente.
El "puntero" (F_Puntero) debe estar al frente del resto de formas y
así pasará por encima de las demás capas del mapa.
La "etiqueta" (F_Etiqueta_Mapa) es la segunda capa, situada en la
esquina superior izquierda de la hoja, con lo que se obtienen las
coordenadas X e Y en el mapa, escuchando el evento:
F_Etiqueta_Mapa_MouseMove
La "imagen dinámica" (F_País) es la tercera capa.
Después de la imagen F_Copyright está ordenada cada "forma
de país", intentado que los países pequeños estén por encima de los grandes,
y así poder seleccionarlos con seguridad.
Países debajo del Puntero
Para identificar cada país he tenido que renombrar cada "forma de país", por
ejemplo Zimbabue, detectando las fronteras de cada país. Es un paso
importante que hay que hacer al menos una vez. Es tedioso pero es
determinante para conseguir el objetivo del mapa, que es identificar el país
que está debajo del "puntero" del ratón.
El cálculo de la distancia a la forma (shape) de un país al "puntero
virtual" se hace con la fórmula de la columna "Distancia" de la tabla en la
hoja 'Fronteras', solamente si el puntero está dentro de la forma
rectangular.
En la imagen solamente aparece la Distancia del "puntero virtual" a
Argelia porque es el país que más cerca está en el momento de la
captura de la imagen de la hoja.
NOTA:
Si hay varios países cerca, se calcula el valor mínimo de la Distancia.
Para detectar las fronteras de un país, el método del "puntero virtual" no
es tan preciso como el puntero del ratón sobre una forma (shape), ya que
Excel nativamente es capaz de detectar el contorno de la forma del país.
Con el cálculo de la Distancia empeora la precisión, pues es una forma
rectangular en la que caben los contornos de otros países, y hasta sus aguas
jurisdiccionales, por lo que al pasar el "puntero virtual" por los mares y
océanos se puede llegar a detectar qué país es el más cercano...
Para obtener correctamente las coordenadas he incluido un Zoom automático,
con el que se ve todo el continente de África. La macro:
ZoomRangoMapa se ejecuta si el mapa es demasiado grande y se
mueve el puntero por el mapa, o cuando el mapa es muy pequeño y se reinicia
el mapa haciendo clic en la celda E19, con lo que se maximiza al tamaño de
la hoja 'Mapa'.
Truco para alinear los punteros virtual y real
Cuando me propuse pasar el ratón por encima del mapa, el puntero del ratón
se desviaba hacia la izquierda del "puntero virtual", aunque éste se
veía centrado horizontalmente con el puntero del ratón. 🙀
Investigué en Google y no encontré nada. 😿
¡Hasta que se me ocurrió la solución! 😻
La desviación se producía porque en la etiqueta que permite escuchar eventos
de movimiento del ratón, y que está por encima del mapa, llamada:
F_Etiqueta_Mapa, la propiedad MousePointer valía:
0-fmMousePointerDefault
El puntero del ratón por defecto es una flecha apuntando hacia arriba a la
izquierda pero, cuando se pasa por encima del "puntero virtual" que tiene
asignada la macro: ColorearPaís, el puntero es una mano con el dedo
índice hacia arriba, como si fuera a hacer clic sobre el "puntero virtual".
Observa en la imagen de arriba ¡que la punta de la flecha y la punta del
dedo índice no coinciden horizontalmente!
¡Son unos pocos pixeles pero suficientes para tocar la moral del
apuntador! 😾
Aquí está el TRUCO: La solución era usar siempre el mismo puntero del
ratón: ¡la mano! 👆
O sea, en F_Etiqueta_Mapa la propiedad MousePointer con valor:
3 - fmMousePointerIBeam
¡Y asunto resuelto! 👏👏👏
Ese valor del MousePointer es la misma mano que se usa para mostrar dónde
está el cursor cuando se hace clic con el ratón en un objeto con macro
asignada, por lo que ahora ya coinciden las coordenadas de la mano que se
mueve por la etiqueta.
Y ahora, cuando saco a pasear al ratón,
¡me sigue como un perrito faldero! 🐕
Consultar datos de África con Power Query
En la hoja 'Datos' se cargan los datos de África, una vez
transformados con Power Query.
Los datos de la capital del país; moneda; idioma; forma de gobierno,
superficie, población y PIB per cápita se extraen con la consulta a dos
páginas Web, la primera para obtener la
población actual en tiempo real, y la segunda para obtener el resto
de datos:
El problema principal con el que me he topado ha sido que los nombres de los
países no son iguales en esas páginas, siendo peor en Wikipedia, pues detrás
de los nombres hay caracteres ocultos, por lo que no se pueden combinar las
dos consultas a no ser que los nombres sean iguales.
En la columna A de esta imagen Egipto tiene un LARGO de 8 con los caracteres
ocultos, cuando su LARGO es 6.
Los caracteres sobrantes son: Unicode 8203 (hexadecimal: 200B)
Este carácter Unicode detrás de los nombres de los países impedía combinar
las dos consultas, una a la Wikipedia y otra a CountryMeters, pues no
coinciden los nombres de los países con los espacios ocultos de ancho cero.
Este es el código para quitar ese carácter Unicode en el último PASO
APLICADO a la consulta en Power Query: Datos África
Esto demuestra que los caracteres sin importancia son los que más guerra
dan, pues son difíciles de limpiar. La limpieza proporcionada por defecto
por Power Query no elimina ciertos caracteres Unicode.
En versiones antiguas de Excel el mapa funciona aunque no actualiza los
datos si no está instalado el complemento de Power Query, que se puede
descargar aquí:
Este videotutorial lo hice con una versión anterior, que aún no cargaba
datos de los países con Power Query, pero creo que seguirá sirviendo para
explicar su funcionamiento.
Selecciona los subtítulos en tu idioma para entender el vídeo.
Descarga el mapa de África
Este mapa ha sido probado en versiones de Excel 2010, 2016, 2021 y Excel
para Microsoft 365. Si me das feedback con los bugs que encuentres, y que
seguro que se me han escapado, igual mejora el mapa.
Descarga la versión 9.6 desde uno de estos enlaces:
Las macros del archivo descargado están bloqueadas por defecto. Para
desbloquear las macros debes modificar las Propiedades del archivo
siguiendo estas instrucciones:
Abre el archivo y presiona el botón: Habilitar edición cuando
aparezca el aviso de VISTA PROTEGIDA.
Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA
DE SEGURIDAD Las macros se han deshabilitado o se deshabilitó parte del
contenido activo.
Si aparece una ventana para elegir el tipo de acceso, selecciona el modo
Anónimo.
Las hojas están protegidas sin contraseña y no está protegido el proyecto
VBA, por lo que puedes estudiar y analizar el código de las macros.
AVISO:
Si se desprotege la hoja 'Mapa'
¡no se debe volver a proteger manualmente!, ya que este mapa se ha diseñado pensando en protegerlo totalmente del
usuario, sin estar protegido de las macros, con esta instrucción:
Este último parámetro, que protege la hoja únicamente del usuario, es
imposible de poner si se protegen las hojas manualmente.
Se ha protegido el mapa así para que las imágenes (shapes) de los países no
puedan ser modificadas ni movidas por el usuario final y tampoco puedan ser
cambiadas las formas del "puntero virtual" ni las formas que comienzan por
F_ (abreviatura de Formas).
ATENCIÓN:
Se puede modificar este libro de Excel respetando esta licencia:
A partir de la versión 9.5 he introducido una interesante mejora para ver el
detalle del mapa haciendo Zoom.
Con el zoom puedo aumentar el detalle del mapa y seguir pasando el "puntero"
por encima de los países, sin necesidad de maximizar el mapa, y permitiendo
el desplazamiento (scroll) horizontal y vertical del mapa.
Para hacer zoom en Excel con el ratón usa la rueda del ratón. Presiona la
tecla Control mientras giras la rueda hacia adelante o hacia atrás
para ampliar o disminuir el zoom respectivamente.
Pronto publicaré un mapa completo del mundo con todas las funciones y
características que voy publicando estas últimas semanas aquí:
32-Bit vs 64-Bit Excel: Which Is Better?
-
32-bit vs 64-bit Excel—which is better? It all comes down to the need for
memory, data size, and compatibility. Not sure which version of Microsoft
Excel 3...
Tablas Dinámicas en Excel y Google Sheets
-
Las tablas dinámicas son una de las herramientas más poderosas en una hoja
de cálculo, esenciales para cualquier usuario que […]
The post Tablas Dinámica...
Debra’s Excel News–November 2024
-
Fix pivot table errors, show images in cells, and more, in this month’s
Excel news. Visit my Excel website for more tips, tutorials and videos, and
check t...
Accountex 2024 Excel a Power BI
-
En Accountex 2024 el jueves 7 noviembre presenté: «BI transición de Excel a
Power BI» (Sesión coordinada por el Consejo General de Economistas)Esta
sesió...
NADA tras la DANA
-
🔝*To translate this blog post to your language, select it in the top left
Google box. *
NADA a favor de la DANA
Justo después de las inundaciones d...
Installing 3DFrame-py
-
As mentioned in the previous post, the installation process for the
3DFrame-py spreadsheet has changed with the new version. Also there have
been some sign...
Divisor propio mayor que la raíz cuadrada
-
Explorando por OEIS, encontré un tipo de números en https://oeis.org/A332269
y me ha apetecido desarrollar el tema mediante nuestras funciones en hoja ...
How To Predict Bearing Life With Excel
-
When you work in mechanical engineering, understanding the reliability and
performance of bearings under various conditions is crucial. Bearings are
the co...
TikTok’s search evolution
-
2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are
more likely to rely on TikTok than Google as a search engine. More than
half of...
Interés compuesto con Excel
-
Este es un mapa mental de las distintas funciones Excel para calcular el
valor del dinero en el tiempo aplicando la ley de capitalización compuesta.
En ...
Unblocking and Enabling Macros
-
When Windows detects that a file has come from a computer other than the
one you're using, it marks the file as coming from the web, and blocks the
file....
-
Aprendiendo Unreal Engine 5. ¿Por dónde empiezo?
Desde la presentación de la Comunidad Oficial de desarrolladores de Unreal
Engine de Málaga, he recibido m...
Navigating Outlook Favorites
-
I have these four favorites defined in Outlook: From the inbox, I could hit
Shift+F6 to get into the Favorites area but sometimes I would end up in no
man’...
Office Scripts: Trabajando con Tablas
-
[image: Office Scripts: Trabajando con Tablas]
Me he dado cuenta que últimamente solo escribo de lenguaje M (es mi pequeño
vicio)... pero hay que liberar l...
Progress on the Block Protocol
-
Since the 1990s, the web has been a publishing place for human-readable
documents. Documents published on the web are in HTML. HTML has a little
bit of… Re...
London Excel Meetup Workbooks
-
The workbooks used in my presentation on “Analytical and Interactive
Dashboards in Excel” at the London Excel Meetup, September 3, 2020
Mis metas son seguir superando nuevos retos en Excel y compartirlos en mi blog, para sacarle todo el poder a esta excelente herramienta multiusos, tan usada y a la vez tan incomprendida, para así poder mejorar nuestros conocimientos de Excel.
