msquery: cunsulta de datos externos sobre otro libro de excel

Excel es, sin lugar a dudas, una de las mejores herramientas para el análisis de datos que existe. Pero tiene una limitación: si poseemos grandes cantidades de datos en un libro, este se tornará, generalmente, muy lento.
[+/-] Ver el resto / Ocultar

Si, por ejemplo, tengo una tabla de 500.000 registros y 15 campos, el uso de autofiltros u otras opciones no será lo ideal al momento de efectuar consultas, a menos que poseamos un ordenador superpoderoso.

En auxilio de esto, y otras cosas de mayor relevancia, tenemos a MsQuery, que son consultas sobre bases de datos externas, sean estas archivos de texto, libros de excel, access, etc, etc.

Yo lo denominaría un matrimonio perfecto. Utilizamos formatos externos para almacenar grandes volúmenes de datos y con MsQuery generamos las consultas, las cuales luego migramos a Excel. En la mayoría de los casos, las consultas devuelven solo una parte del total general de datos.

Este es un tema por demás de extenso e imposible de tratar en un solo post, por cuanto comenzaré por las cuestiones mas sencillas. En futuras entradas ampliaremos conceptos y conoceremos mas a fondo todo el potencial de esta herramienta de consulta.

Basta de teoría y arranquemos con la práctica. Tengo en un libro las ventas anuales de mi empresa, la cual vemos a continuación. La hoja de se llama "mayor".

notarán que vendedor, cliente y producto son códigos. luego veremos el por qué.

Así las cosas, este año me fué bien, y tengo mas de 80.000 registros, o sea, ventas. Si quisiera saber las ventas que le hice al cliente "a" del producto "p1", podría utilizar filtros u ordenamiento, pero tenemos el problema de que, a grandes cantidades de datos, excel se nos pondría bastante "pesado".

Entonces veamos como utilizar este libro como "base de datos", leyendo y efectuando consultas desde otro libro.

1) Abrimos un archivo nuevo y vamos a la pestaña "datos" y seleccionamos la opcion de la imagen(en excel 2003 y anteriores, menú datos / obtener datos externos / nueva consulta)

2) Como los datos a leer están en otro libro de Excel, elegimos la siguiente opción:

3) Ahora buscamos el archivo de origen de datos en nuestro disco duro y lo seleccionamos:

4) Nos aparecerá en pantalla un form mostrando todas las hojas que tengamos en el libro, con sus respectivas tablas. Como arrancamos "sencillito", recordemos que nuestro archivo tiene una sola hoja, llamada "mayor". Noten como el nodo se expande y, a su vez, nos muestra todos los campos de la tabla (columnas). 

los botones entre las ventanas nos permitirán incluir toda la tabla o algunos campos.

5) En este caso, hacemos click sobre el nodo "mayor" y enviamos todos los campos a la ventana de la derecha:

ya tenemos todos los campos que incluiremos en nuestra consulta

6) Antes de presionar "siguiente", damos click en "opciones" y verificamos que todo se encuentre tildado como en la siguiente imagen:

aceptamos y, ahora sí, presionamos "siguiente"

7) Ahora... a prestar atención por que empieza la consulta "en sí". El form de la pantalla nos permite ahora ingresar los parámetros de nuestra consulta. Vean la imagen:

le digo "traeme de la tabla "mayor" aquellos registros cuyos productos sean iguales a p1". Noten que, aparte, tenemos dos condicionales mas. Si desplegamos la primer lista, podremos seleccionar varias opciones: mayor a, menor a, menor o igual a, etc. etc.

Vamos ahora a seleccionar el campo "vendedor" y configurarlo de la siguiente forma:

Si unimos los criterios de las dos últimas imágenes, la consulta nos queda: "seleccionar todos los registros de la tabla "mayor", cuyos productos sean igual a p1 y vendedero igual a 1.

Hacemos click en "siguiente".

8) Otra opcion mas: si deseamos que MsQuery nos devuelva la consulta ordenada por alguno de los campos:

lo ordeno por el campo "fecha"

9) Luego de presionar siguiente, nos pide el lugar donde colocaremos los datos:

por ahora, los devolvemos a Excel.

10) El siguiente form aparecerá en pantalla, varias opciones:

a) llevar los datos a una tabla común.

b) llevarlos a una tabla dinámica.

c) gráfico y tabla dinámica.

d) seleccionar a partir de que rango se copiarán los datos

e) por si acaso, podemos enviarlo a una nueva hoja de cálculos.

Como vamos a lo simple, dejamos "tabla", "=$A$1" y "hoja de cálculo existente"

11) listo !!!!!! nuestra consulta, con los parámetros ingresados, en Excel, lista para su análisis:

tal cual lo indicamos en los distintos pasos del asistente, tengo a mi alcance todas las ventas del p1, realizadas por el vendedor 1

Desde la pestaña "datos" tendremos algunas opciones interesantes:

con este menú podremos mantener nuestros datos actualizados.

Si, eso ultimo que mencioné, quizás sea lo mas interesante de todo: cada vez que se efectúen cambios en el libro original, tabla "mayor", los datos se actualizarán en nuestra consulta.

En "propiedades de conexion" tenemos lo siguiente:

habilitamos las actualizaciones en segundo plano y también le podemos indicar que lo haga cada una determinada cantidad de minutos. La ultima casilla nos permite actualizar cada vez que abramos el archivo. En la ventana superior podremos personalizar el nombre de la query.

Este ejemplo fué muy sencillo e introductorio, como "para que practiquen un rato".

En futuras entradas veremos como modificar manualmente los parámetros de la consulta y obtener varias tablas de un mismo libro o de varios libros de Excel. Se pueden llegar a realizar trabajos realmente complejos, pero la principal ventaja radica en que lo hacemos una sola vez y la consulta generada es actualizable.