Gráficos de mapas en Excel (I): Los mapas 2D

Si bien algunos años Microsoft dio la opción de usar mapas en Excel (Excel 97 y 2000, con la herramienta Microsoft Data Map), dicha herramienta fue dejada de lado por un tiempo hasta que hace algunos años, en versiones más modernas de Excel, Microsoft ha incluido la posibilidad de usar mapas (a través de complementos) mucho más complejos que antaño entre las opciones de gráficos. Además, desde la versión 2016 tenemos la opción de usar dichos mapas como gráficos para nuestros datos. Ah, eso sí, necesariamente se tiene que tener conexión a internet para que, a través de Bing Maps, los gráficos nos ofrezcan los mapas de los países, regiones, localidades, etc., a los que hacemos referencia. Si bien existe incluso la posibilidad de usar mapas 3D, en esta ocasión vamos a centrarnos en los gráficos que son del tipo 2D.

Lo primero que debemos tener es una columna que tenga los nombres de los lugares que deseamos estén en nuestro gráfico de mapa. En este caso yo usaré regiones/departamentos de mi querido Perú y en una segunda columna escribiré la población que tienen. Ah, tendré todo tipo Tabla para que si agrego datos se actualice el gráfico con ellos. .

Ahora coloquen el cursor en cualquier celda del rango de la tabla y una vez hecho eso vayan a la pestaña Insertar, ahí busquen el grupo Gráficos y en él verán el botón Mapas, denle clic y en el menú que se despliega elijan “Mapa coroplético”.

Si todo salió bien, si los nombres de los lugares que usaron son correctos y están en Bing Maps, pues el resultado será similar a este:

Obviamente pueden usar las herramientas de gráficos para cambiar los colores, colocar título, poner etiquetas de datos, etc.

Vamos con un ejemplo más para que nos quede más claro aún. Probemos con países de Sudamérica y su Producto Bruto Interno (PBI) en dólares de EE.UU.:

En algunas ocasiones quizás el mapa tarde en cargar un poco y/o no aparezca un mensaje como el siguiente:

De ser el primer caso, solo tengamos paciencia y de ser el segundo, basta darle clic al botón “Acepto”. Después de hacer algunos cambios de formato, en mi caso tendré este mapa como resultado:

Como ven, el uso de estos mapas es bastante simple, pero a pesar de ello aún su uso no ha sido tan difundido. Espero les guste y le sepan dar utilidad a esta herramienta. Hasta la próxima.

Abraham Valencia

Descarga el ejemplo aquí

Conectar Excel con Excel OnLine en OneDrive (Personal) a través de Power Query

Hace algunas semanas comentábamos cómo conectar Excel con una Hoja de Cálculo de Google a través de Power Query (Enlace) y hace un par de días, en uno de los foros en los cuales participo, un usuario hacia una pregunta que me llevó a decidir escribir algo parecido, pero en esta ocasión usando un archivo de Excel en OneDrive (personal). Entonces, vamos a lo nuestro.

Supongamos que tenemos un archivo con datos en nuestro OneDrive al que acceden muchas personas y lo van actualizando con datos nuevos (incluso puede ser un archivo de Excel Online creado a través de Microsoft Forms, pero en OneDrive) y nosotros necesitamos esos datos, pero en nuestra PC de escritorio.

Entonces, para mi ejemplo tengo esto en mi OneDrive:

Si desde un libro de Excel queremos usar Power Query (pestaña “Datos”, grupo “Obtener y transforma datos”, algunas de sus opciones) para conectarnos al archivo ubicado en OneDrive e intentamos usar su propio enlace (el que se ve en la barra de navegación) o el enlace que nos brinda la herramienta Compartir de Excel Online o de OneDrive, pues no podremos obtener los datos y Power Query nos devolverá como resultado solo etiquetas sin utilidad o sin dato alguno; entonces ¿cómo hacemos?

En el OneDrive ubiquemos nuestro archivo y démosles clic derecho y en el menú que se despliega elijamos la opción “Insertar”.

En las opciones que se despliegan, dar clic al botón “Generar”.

En el resultado obtenido lo que debemos hacer es copiar el código html que se nos brinda.

Podemos pegar dicho código en Word o el Bloc de Notas o en donde deseemos. Es importante eso pues lo vamos a usar. Este es el mío:

<iframe src="https://onedrive.live.com/embed?cid=003809D6D9821399&resid=3809D6D9821399%212695&authkey=AJz9IGXj5klBpVc&em=2" width="402" height="346" frameborder="0" scrolling="no"></iframe>

Ahora, en el enlace que dejo aquí abajo, miren lo resaltado en negrita:

https://onedrive.live.com/download?resid=ABCDXYZ&authkey=ABCDEF&em=x&app=Excel

Lo que nos interesa es lo correspondiente a “resid” y a “authkey” y lo que haremos será reemplazar las partes en negrita de dicho enlace con los valores correspondiente del código html obtenido, de tal modo que este será el nuevo enlace:

https://onedrive.live.com/download?resid=3809D6D9821399%212695&authkey=AJz9IGXj5klBpVc&em=2&app=Excel

¿Notan qué es lo que se reemplazó? Por si quedan dudas, para el caso del “resid” es la parte que está entre el signo igual (=) y el ampersand (&) y para el caso del “authkey” igual, pero para ambos casos después de la palabra respectiva.

Ahora sí, volvamos a nuestro libro de Excel y en la hoja en donde queramos los datos vamos a la pestaña “Datos” y en el grupo “Obtener y transforma datos” vamos a darle clic al botón “Desde la web” y en el cuadro de dialogo que se abrirá, pegaremos el enlace que hemos creado y le damos clic al botón “aceptar” (asegúrense de que en el enlace creado no hay espacios entre los caracteres).

Si todo salió bien se nos mostrará un cuadro como el que muestro a continuación, elegimos, a la izquierda, el nombre de la hoja en la que tenemos los datos, a la derecha se nos mostrarán algunos de ellos y hecho eso le damos clic al botón “Cargar” (si deseas cambios usa “Transformar datos”).

Si todo salió bien, en nuestra hoja tendremos algo así:

Para finalizar, en la pestaña “Datos” vayan al grupo “Consultas y conexiones” y ahí en “Actualizar todo” elijan “Propiedades de conexión…” y activen, si lo desean, “Actualizar cada” y coloquen la cantidad de minutos deseados; activen la opción “Actualizar al abrir el archivo” y verifiquen que la opción “Actualizar esta conexión en Actualizar Todo” esté activada. Por último, activar “Habilitar la carga de datos”. Listo, ahora nuestra hoja de Excel se actualizará con los datos que se cambien/agreguen/eliminen en el archivo de OneDrive. Espero les sea útil. Hasta la próxima.

Abraham Valencia

Filtros avanzados (II): El uso de filtros con fechas

Hace algunos días vimos como usar los filtros avanzados para enviar los datos obtenido a otro rango u otra hoja (Enlace), ahora lo que haremos es aprender a usar dichos filtros con fechas, incluyendo cómo usar rangos de fechas.

Lo primero es que debemos tener los datos como mostraré a continuación, y en la parte superior de ellos tendremos dos celdas que tendrán el mismo encabezado que nuestro campo de fecha.

Luego, si queremos filtrar solo por una fecha, pondremos así:

No daré los detalles de cómo usar los filtros avanzados pues están en el artículo anterior (mirar el enlace indicado líneas arriba), pero si hemos seguido adecuadamente los pasos, tendremos un resultado similar al de aquí:

¿Y que pasa si lo que queremos son los datos entre dos fechas? Pues, ahora sí usaremos los dos campos que pusimos para las fechas. Vamos a suponer que queremos los datos entre los días 15/01/2017 y el 17/03/2017. Lo que debemos hacer es usar los signos “mayor” (>) y “menor” (<), junto con las fechas, del siguiente modo:

Una vez que apliquemos el filtro avanzado tendremos como resultado lo siguiente:

Pero ¿qué pasa si en los registros tenemos fechas exactamente iguales a las usadas para filtrar? Al usar mayor y menor no toma en cuenta esas fechas, solo las superiores e inferiores, respectivamente, entonces ¿cómo solucionamos eso? Pues vamos a agregar el signo “igual” (=) del siguiente modo:

Al aplicar el filtro el resultado incluirá las fechas usadas en los campos:

Y esa es la forma en que podemos usar fechas con los filtros avanzados. Hasta la próxima semana.

Abraham Valencia