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

Filtros avanzados (I): Copiar los datos a otro rango u otra hoja

El uso de los filtros (antes autofiltros) para poder ver en una hoja solamente aquellas filas, dentro de un rango de celdas, que cumplen ciertas condiciones, es relativamente conocido y difundido por los usuarios/as de Excel. Claro, el efecto se da en la misma hoja y dentro del mismo rango (se “ocultan” filas), pero ¿qué ocurre cuando queremos que el resultado del rango filtrado se vea en otras celdas (otro rango) o incluso en otra hoja? En esos casos usaremos los “Filtros avanzados” (ahora “Filtro – Avanzadas”).

Para este ejemplo lo primero que haremos es insertar tres filas vacías encima de nuestros datos.

En la primera fila repetiremos los nombres de los campos (títulos de las columnas) que deseamos filtrar. No es necesario que sean todos y no olvidemos que nunca deben ser más que la cantidad del número de columnas de nuestros datos (recordemos eso sobre todo en el caso de que queramos filtrar por rango de fechas).

Vamos a suponer que deseamos filtrar las ventas solo de la tienda “San Borja” y que queremos los datos a partir de la celda “G4”, entonces colocaremos la palabra “San Borja” en la celda “C2” y luego en la pestaña “Datos” vamos al grupo “Ordenar y filtrar” y le damos clic al botón “Avanzadas”. En el cuadro de diálogo que sale a continuación primero elegiremos la opción “Copiar a otro lugar”, elegiremos el rango de nuestros datos (“Rango de la lista”), también nuestro “Rango de criterios” y en “Copiar a” vamos a elegir la celda “G4” en nuestro caso.

Si hicimos todo bien tendremos como resultado algo así:

Vamos ahora a suponer que queremos esos mismos resultados, pero en otra hoja. Seguiremos los mismos pasos, pero en el cuadro de diálogo elegiremos (o escribiremos) la celda de otra hoja.

Cuando le demos clic a “Aceptar” Excel nos dirá lo siguiente:

Entonces ¿es imposible que se copien los datos en otra hoja? Pues no es imposible y ahora veremos cómo hacerlo.

Vamos a supone que en “Hoja2” queremos los resultados y que en la hoja “Datos” está nuestra base, entonces vamos a la “Hoja2” y desde ahí activaremos la función de “Avanzadas”. Deberíamos tener algo así:

Y una vez que damos clic a “Aceptar”, y si todo salió bien nuevamente, tendremos lo siguiente en la “Hoja2”:

Y así, amigos y amigas, hemos aprendido a filtrar los datos en otra hoja y si usar macros. Hasta la próxima.

Abraham Valencia

Nota: Para más formas de filtrar ver el siguiente enlace

"Dashboard" en Excel: Creando tu propio tablero de control

Cada día es más habitual encontrar libros de Excel en los cuales se ha agregado “Tableros de Control” más conocidos simplemente como “Dashboards”. Dichos tableros facilitan la presentación y/o análisis de datos que en muchas ocasiones necesitamos por lo que en esta ocasión veremos cómo hacer uno que incluso nos permita visualizar los datos a través de gráficos.

Para comenzar, la disposición de nuestros datos para el ejemplo será de este modo:

Para que el ejercicio resulte mucho más visible, he agregado varios registros con datos de tres años.  Para que no haya problema cuando vamos agregando más datos, yo siempre recomiendo convertir en “Tabla” dichos rangos. Además de usar el ya “clásico” ejemplo de ventas para estos casos, también necesitaremos, como siempre se recomienda, tener tres hojas en nuestro libro: Una para los datos, otras para las tablas dinámicas que vamos a necesitar y una para el “Dashboard”.

En este ejemplo lo que vamos a necesitar, para nuestro “Dashboard”, son los cruces de ventas por vendedor y de ventas por tienda, para eso vamos a necesitar dos tablas dinámicas. Obviamente cada quien debe decidir lo que desea mostrar. Para eso, entonces, crearemos dos tablas dinámicas que colocaremos en la hoja que yo he llamado “TD” (estoy asumiendo que todos/as saben cómo crearlas) de tal modo que tengamos esto como resultado:

Sugiero dar formato a los números para que después en los gráficos que usemos se refleje dicho formato. En mi caso usaré el formato de moneda, tal cual está en la tabla de datos.

Ahora lo que haremos será insertar un gráfico dinámico para cada una de las tablas dinámicas. Por si se han olvidado en dónde está la opción para crearlas, se sitúan en cualquier celda de la tabla dinámica respectiva y en la pestaña “Analizar” la veremos así:

Podemos elegir el tipo de gráfico que deseemos, en mi caso yo he elegido barras 3D agrupadas (horizontales).

No olviden que es un gráfico por cada tabla dinámica que usemos.

Luego lo que debemos hacer es cortar y pegar cada gráfico en la hoja llamada “Dashboard”. Por supuesto que podemos cambiar el diseño de cada gráfico con las opciones, justamente, de la pestaña “Diseño”. Además de eso, sugiero usar las opciones de cada gráfico dinámico para ocultar los botones de campos.

Una vez hecho eso, seleccionamos cualquiera de nuestros gráficos dinámico y vamos a la pestaña “Analizar” y en el grupo “Filtrar” usaremos el botón “Insertar escala de tiempo”.

En el menú que veremos elegimos, en mi caso, el campo “Día” y aceptamos.

Nuestra escala de tiempo incluirá todos los meses ya años que existan en nuestros datos.

Podemos cambiar el diseño a dicha escala y, además, una de las ventajas que tiene es que nos permitirá elegir días, meses, trimestres o años.

Lo que toca hacer ahora es relacionar nuestra escala de tiempo con las tablas dinámicas, para ello seleccionaremos nuestra escala y en la pestaña “Opciones”, en el grupo “Escala de tiempo” vamos a usar el botón “Conexiones de informes”.

Y activaremos la conexión a todas las tablas dinámicas que estamos usando.

Si bien ya tenemos como ir filtrando por tiempo, ahora lo que haremos es agregar algo que nos permita también hacerlo por vendedor o tienda, para eso seleccionamos cualquiera de los gráficos y en la pestaña “Analizar”, en el grupo “Filtrar”, esta vez vamos a elegir “Insertar segmentación de datos” y elegiremos “Vendedor” y “Tienda”.

Con los “slicer” que tenemos ahora, lo que debemos hacer es usar “Conexiones de informes” y relacionarlos a nuestras tablas dinámicas, tal y como hicimos con la escala de tiempo. Por supuesto también podemos cambiarle el diseño a dichos “slicer”.

Para ver como seguir agregando datos que nos pueden ser útiles, en una celda obtendremos el total de ventas y haremos que dependa de lo que vamos a ir eligiendo en la escala de tiempo y/o los “slicer”. Para ello vamos a usar el total de general de una de las tablas dinámicas y vincular dicho resultado a la celda deseada. En este caso he usado el total de la tabla dinámica con los resultados de las “Tiendas” .

Como las tablas dinámicas trabajan sobre los mismos datos, vamos a usar la de “Vendedores” y en el campo de totales vamos cambiar para que nos dé como resultado el promedio.

Vinculamos otra celda con dicho resultado del promedio, y con ello tendremos el promedio de venta de cada vendedor en base a lo que vayamos filtrando en la hoja “Dashboard”.

Para finalizar podemos colocar cada elemento de los obtenidos en la parte de la hoja que deseemos y tendremos algo como esto:

Y listo, ya podemos comenzar a “jugar” con los datos. Espero les sea útil. Hasta la próxima.

Abraham Valencia

Descarga el ejemplo aquí: Enlace