EXCEL msquery: cunsulta de datos externos sobre otro libro de excel – II

Publicado: 3 de agosto de 2012 en Actualidad

msquery: cunsulta de datos externos sobre otro libro de excel – II

En mi anterior post vimos los parámetros básicos de una consulta externa sobre otro archivo de Excel. Vamos a complicar y mejorar un poco mas las cosas. =)
[+/-] Ver el resto / Ocultar

El ejemplo en cuestión tenía una sola tabla, que estaba ubicada dentro del archivo "base_consulta_1.xls", el cual poseía una única hoja, llamada "mayor".
Ahora modificaremos dicho archivo e incorporaremos tres hojas mas: "clientes", "vendedores" y "productos".
Deberán quedar de la siguiente forma:

hoja "clientes"
msquery16.JPG

hoja "vendedores"

msquery17.JPG

hoja "productos"
msquery18.JPG
los colores y demás son solo descriptivos, para diferenciarlas bien.

Demos un vistazo a la tabla que ya teníamos en el archivo, "mayor", que es donde contabilizo las ventas diarias:
msquery1.JPG
supuestamente, x que no tengo muchas ganas de escribir, tiene miles de registros.

vendedor, cliente y producto tienen códigos en la tabla "mayor", que están directamente relacionados a los respectivos "id" de las demás tablas. Verán como los datos son del mismo tipo (numéricos, alfanuméricos, etc) en cada caso.
Este es el "alma" del modelo que utilizamos: base de datos relacional. En la tabla principal solo almaceno un código (lo que ocupa muy poco espacio), mientras en otras guardo los detalles respectivos a cada código.
No me explayaré sobre el particular, no es el objetivo de este post, pero es bueno saberlo y, si tienen consultas, las levantan aquí mismo.

Ahora entenderemos bien el concepto con un ejemplo.
Vamos a realizar una consulta sobre las 4 tablas, relacionando sus campos y devolviendo los resultados a Excel. A diferencia del post anterior sobre el tema, en este caso vamos a "meter un poco de mano", en lugar de hacer todo mediante el asistente.

Nos dirigimos a datos / obtener datos externos / de otras fuentes / desde microsoft query.
del formulario seleccionamos Excel Files y luego buscamos el archivo en nuestro disco duro, en el cual tenemos las 4 hojas con sus respectivas tablas. (todas las imagenes estan en mi anterior post)

Bueno, ya empezaron las diferencias. Vemos como ahora el form me muestra 4 tablas. Excelente:

msquery25.JPG

tal como lo hiciéramos en el ejemplo del post anterior, volcamos todas las tablas a la ventana de la derecha:
msquery26.JPG
directamente traspasamos todas las tablas, luego en excel quitaremos los campos innecesarios

Ahora, cuidado. Un mensaje nos alertará que no podemos seguir adelante, salvo que luego vinculemos los campos manualmente. Presionamos Aceptar:

msquery27.JPG

Hemos ingresado a la interfaz gráfica de MsQuery, la cual guarda mucha similitud con la estructura de las consultas de Access: tenemos las cuatro tablas y, debajo de ellas y mal listados, los registros. Aclaro "mal listados", por que aun nos resta vincular las 4 tablas con sus campos en común:
msquery28.JPG

Tenemos un montón de datos, casi "sin sentido". Recordemos lo mencionado sobre el modelo relacional y unamos los campos que comparten las mismas características. Esto se puede hacer con el cursor del mouse, arrantrando y soltando. Debe quedar de esta forma:
msquery19.JPG
cada una de las tres tablas "satélites" tiene un campo en común con la tabla "mayor".

noten como la grilla de datos muestra todos los campos, tando el "id" como el respectivo nombre:
msquery20.JPG
Llevaremos a Excel todas las ventas realizadas por Walter Leiva y Esteban Flores. Vamos al menú "Criterios / Agregar" y nos aparecerá el siguiente form:
msquery21.JPG
selecciono el campo "vendedor" de la tabla mayor y en valor pongo el 1 (walter leiva) y presiono "agregar"

En pocas palabras: MsQuery filtrará aquellas ventas realizadas por el vendedor cuyo código es igual a 1 en la tabla "mayor", y me presentará el siguiente resultado:

msquery30.JPG
la grilla tiene todas las ventas del vendedor 1. en la parte superior (campos de criterios) podemos seguir agregando campos para refinar la consulta, como se muestra a continuación:

msquery31.JPG
así iremos agregando campos de acuerdo a nuestras necesidades. por el momento solo utilizaremos "vendedor"

Note que debajo de "vendedor" está el valor que ingresamos en el formulario de criterios. Aqui es factible modicarlo manualmente (por ejemplo por 2 ó 3, siempre que ese código exista) y también aprovechar la opcion "o", debajo de Valor. Aqui ingresaré un 2:
msquery32.JPG
es sencillo: le estoy diciendo "todas las ventas del vendedor 1 o 2, de la tabla mayor"

Nos vamos a "archivo / devolver datos a excel" y listo, tendremos nuestra consulta terminada. Aqui le hice unos "retoques", eliminando las columnas (campos) que consideré innecesarias para mi tarea:
msquery33.JPG
dentro de excel podemos modificar los formatos numéricos a nuestro gusto.

Llegamos al fin. Moraleja: en una sola tabla (mayor) almaceno miles y miles de registros, todos codificados (clientes, productos y vendedores) a los fines de que esa tabla posea datos de poca longitud y, por ende, no ocupe mucho espacio en disco, lo que la ralentizaría.
En otras tres tablas almaceno los códigos y descripciones, para luego vincularlos mediante MsQuery.

Ejecuto la consulta y puedo modificarla manualmente, que no es poca cosa y nos dá una herramienta muy poderosa para nuestro trabajo. Cada vez que modifique algo en la tabla original se actualizará en la consulta que he creado. Es la solución definitiva al manejo de grandes volúmenes de datos.
Espero que lo practiquen convenientemente, ya que los ayudará mucho en las labores diarias.

Otra cosa: antes de exportar los datos a excel, vamos a "archivo / guardar consulta", lo que nos generará un archivo de extensión .dqy (database query). Ponemos algún nombre descriptivo y guardamos.
Si la consulta es muy compleja, no tenemos ganas de volver a realizarla o queremos modificarla y "guardarla como…" esta es la solución ideal, ya que nos evita realizar nuevamente los pasos de conexión, selección de tablas y campos, criterios, etc, etc. Todo ha quedado debidamente guardado y la podemos ejecutar y modificar a gusto:

esta es la consulta guardada (miren el tamaño !!!)
msquery34.JPG

y aqui está luego de clickearla, totalmente ejecutada:

msquery35.JPG

un lujo. que lo disfruten.
archivo excel con las tablas
archivo dqy

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s