Conectar Excel con una Hoja de Cálculo de Google a través de Power Query

Hoy en día en que el trabajo compartido a través de internet se masifica más y más, no es raro que compartamos y/o se trabaje en archivos “on line”. Una de las aplicaciones cuyo uso se está difundiendo mucho es la “Hoja de Cálculo de Google” e incluso muchas personas creen que es lo mismo que Excel. No es extraño que mientras en nuestra portátil o nuestra PC de escritorio usamos Excel, nosotros mismos y/o nuestros compañeros/as estén trabajando en la aplicación de Google y que necesitamos usar esos mismos datos. Por supuesto que una alternativa es entrar a la hoja de Google en internet y descargar en formato Excel, pero habría que hacer eso cada vez, lo que puede resultar algo tedioso. Entonces ¿qué otra alternativa tenemos?

En esta ocasión vamos a conectar una hoja de cálculo de Google con un libro de Excel. Para eso vamos a comenzar suponiendo que tenemos datos dispuesto, por ejemplo, así:

Ahora, lo primero que haremos es ir al menú “Archivo” y elegir la opción “Publicar en la Web…”

En el cuadro de dialogo que saldrá, vamos a elegir, en la primera lista, el nombre de la hoja cuyos datos vamos a compartir y en la segunda lista vamos a elegir la opción “Valores separados por tabuladores (.tsv)”.

Luego de ello, Google nos brindará un enlace que debemos copiar. No olvidar eso o no podremos realizar la conexión.

Ahora sí, vamos a nuestro querido Excel. En el libro y hoja en donde queramos los datos vamos a la pestaña “Datos” y en el grupo “Obtener y transforma datos” vamos a darle click al botón “Desde el texto/CSV” y en el cuadro de dialogo que se abrirá, pegaremos el enlace de Google en la parte de “Nombre de archivo:”

Veremos cómo se abre el editor de Power Query y ahí le daremos doble click a la imagen en donde dice “docs.google.com”.

El resultado debe ser similar a esto:

Si al igual que yo usan tildes y algunos datos se ven como en la imagen anterior, no se preocupen, hay formas de arreglarlo. En el editor de Power Query vamos a la pestaña “Inicio” y en el grupo “Consulta” elige el botón “Editor avanzado” y al abrirse buscaremos los siguiente:

¿Ven el número 1252? Pues reemplácenlo por el 65001 que es el correspondiente al formato UTF-8, denle click al botón “Listo” y ahora los datos se verán así:

Ahora elijan la pestaña “Transformar” y en el grupo “Tabla” elige “Usar la primera fila como encabezado” y en la lista que se despliega elige la opción del mismo nombre. En el grupo “Columna de texto” elige “Dividir columna” y ahí “Por delimitador”, en el cuadro que tendrán a la vista, verifiquen que estas opciones estén activadas y denle click a “Aceptar”:

Ahora sí tendremos los datos casi listos:

En la pestaña “Archivo” elijan “Cerrar y cargar” para enviar los datos a nuestra hoja de Excel. En la pestaña “Datos” de la hoja, 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 la Hoja de Cálculo de Google. Espero les sea útil. Hasta la próxima.

Abraham Valencia

Informes con segmentación de datos

Constantemente los usuarios/as de Excel están en busca de cómo mostrar sus datos en informes/reportes que, además de ser visualmente agradables a la vista, permita mostrar resúmenes de datos o similares. Hay varias formas de lograr hacerlo (Fórmulas, VBA, Tablas Dinámicas, etc.) pero en esta ocasión vamos a usar la “Segmentación de datos”.

Para comenzar, vamos a suponer que tenemos datos dispuestos de esta manera:

Entonces lo primero que haremos será convertir esos datos en “Tabla” (Enlace).

Si no situamos en cualquier celda de la tabla podremos usar las “Herramientas de tabla” y agregaremos la “Fila de totales” de tal modo que podamos usarlos a la hora de hacer nuestros informes.

Como también queremos datos por meses (en este caso todos los datos son de un solo año) y las tablas no permiten agrupar por meses, agregaremos una columna usando la siguiente fórmula:

Es suficiente que coloquemos la fórmula en la primera celda de la nueva columna y como se habrán dado cuenta se agregará en todas las otras celdas de manera automática (ventaja de las tablas).

Ahora lo que haremos será insertar unas diez filas encima de nuestra tabla, luego colocaremos el cursor en cualquier celda de la tabla y en las “Herramientas de tabla” elegiremos el botón “Insertar segmentación de datos”:

Para nuestro caso vamos a elegir tres opciones: Vendedor, producto y mes.

-------

Los tres cuadros que obtendremos podemos cambiarles el tamaño y colocarlos en la parte en que hemos insertado las filas en blanco, además podemos cambiarles el formato si seleccionamos cada uno y en las “Herramientas de segmentación de datos” cambiamos su estilo de tal modo que podríamos tener, finalmente, algo así:

Y listo, ya podemos elegir/filtrar las opciones que deseamos para nuestros informes:

Hasta la próxima.

Abraham Valencia

Descargar el ejemplo aquí

El correcto uso de INDIRECTO (II): Listas desplegables dependientes

Como ya hemos comentado, la función “Indirecto” tiene varias características que no necesariamente son conocidas. En esta ocasión vamos a sacar provecho de ellas y mostraremos cómo hacer listas dependientes usando dicha función.

Para comenzar, en una hoja escribamos datos de este modo:

Luego, para asegurarnos de que si agregamos más datos estos sigan siendo parte de nuestras listas, vamos a convertir cada columna a “Tabla” (Enlace).

Ah, por favor, colocar que los datos tienen encabezados:

Luego, para asegurar de que funcionen nuestras listas, debemos cambiar el nombre de cada tabla y colocarle el mismo que el encabezado:

A esa hoja vamos a nombrarla “Datos”. Ahora vayamos a otra hoja y en la celda, por ejemplo, “B1” usando “Validación de datos” y la opción “lista” crearemos una lisa desplegable con los nombres de los cuatro continentes que hemos usado.

Como ya tenemos la primera lista crearemos, ahora, la que será dependiente. Vamos a la celda “B2” y nuevamente usaremos “Validación de datos” y la opción “lista”, pero en esta ocasión ingresaremos una fórmula con “Indirecto” del siguiente modo:

=INDIRECTO(B1)

El resultado que tendremos es el siguiente:

Cada vez que cambiemos el valor de “B1”, la lista de “B2” será con los países correspondiente. Ah, y si agregamos más países en la hoja “Datos”, al ser tablas serán automáticamente parte de cada conjunto de países y se visualizarán sin dilema en la hoja de las listas. Y eso amigos/as es otra buena idea para usar “Indirecto”. Hasta la próxima.

Abraham Valencia

Excel y MySQL (II)

Hace poco hablamos de cómo conectar MySQL con Excel (Enlace) aunque básicamente se hizo referencia a conectarse a un servidor y extraer sus datos. En esta ocasión lo que vamos a hacer es enviar datos de Excel hacia una base de datos MySQL.

Vamos a hacer algo corto y práctico porque la idea es que se entienda más que hacer algo complicado. Entonces, comenzaremos creando una tabla con cuatro campos en alguna base de datos MySQl que tengamos. Mi base de datos se llama “Pruebas” y llamaré a mi tabla “TablaPruebas”. Los campos serán los siguientes:

Nombre del campo	Tipo	Extras
Id	Int	Primary – Auto_Increment
Nombre	Text
País	Text
Fecha	Date

En nuestra hoja de Excel usaremos tres columnas A, B y C (La de “Id” no, ya que es de numeración automática) y pondremos encabezados en la fila 1. Llenaremos datos que queramos enviar a MySQL:

Como son varios datos usaremos un bucle para enviar los datos, aunque la clave de todo será usar SQL y en específico “Insert Into”. Dado que usaremos el bucle mencionado, debemos asegurar que al construir nuestra cadena SQL, se tomen los datos adecuados por lo que dicha cadena debe quedar de la siguiente manera:

sql = "Insert Into tablapruebas (Nombre, País, Fecha) Values ('" & Range("A" & x).Value & "','" _         
  & Range("B" & x).Value & "','" & Format(Range("C" & x).Value, "yyyy-mm-dd") & "')" 

Al ser los campos del tipo Text y Date, deben necesariamente ir entre comillas simples en la cadena. Para terminar, y basándonos en todo lo mencionado en el artículo anterior, nuestra macro debería quedar así, tomando en cuenta que usaré MySQL en el “LocalHost”:.

Sub DatosaMySQL()

Dim cnn As ADODB.Connection 
Dim rst As ADODB.Recordset 
Dim sql$, BD$, Servidor$, User$, Clave$ 
Dim UltimaFila As Long, x As Long 

Set cnn = New ADODB.Connection 
Let Servidor = "localhost": Let BD = "pruebas" 
Let User = "root": Let Clave = "" 
Let UltimaFila = Cells(Rows.Count, 1).End(xlUp).Row 

cnn.ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};" _
      & "SERVER=" & Servidor & ";DATABASE=" & BD & ";" _
          & "UID=" & User & ";PWD=" & Clave & ";PORT=3306;OPTION=131072" 
cnn.Open 

For x = 2 To UltimaFila

  Set rst = New ADODB.Recordset
         Let sql = "Insert Into tablapruebas (Nombre, País, Fecha) Values ('" & Range("A" & x).Value & "','" _
           & Range("B" & x).Value & "','" & Format(Range("C" & x).Value, "yyyy-mm-dd") & "')"     

  With rst
      .CursorLocation = adUseClient         
   .CursorType = adOpenKeyset         
   .LockType = adLockOptimistic
         .Open sql, cnn, , , adCmdText
     End With 

  Set rst = Nothing 

Next x 

cnn.Close 
Set cnn = Nothing 
MsgBox "Todo listo" 

End Sub

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

Espero les sea útil. Hasta la próxima.

Abraham Valencia