Consolidar datos de hojas Excel con MS Query

domingo, abril 20, 2008

Supongamos que tenemos una hoja en un cuaderno de Excel con los siguientes datos



En otra hoja tenemos estos datos



Nuestra tarea consiste en consolidar los datos de ambas hojas en una sola hoja, como esta



No podemos hacerlo con Datos—Consolidar o con Tablas Dinámicas. Una posibilidad es agregar el campo Cliente en la segunda hoja usando la función BUSCARV basándonos en el número de factura.
Este método se vuelve muy ineficiente cuando tenemos que manejar una gran cantidad de datos. No sólo que BUSCARV puede ser muy lenta cuando hacemos una búsqueda exacta sino que también tendríamos que estar actualizando las fórmulas cada vez que agregamos datos.

En esta nota mostraremos un método mucho más eficiente usando un programa adjunto de Excel, y poco conocido, el MS Query, sobre el cual ya hemos escrito en el pasado.

Supongamos que las hojas que hemos mostrada más arriba se encuentran en el cuaderno Datos.xls.

Empezamos por abrir un cuaderno en blanco y en este cuaderno abrimos el menú Datos-Obtener Datos externos-Nueva consulta de base de datos



En el diálogo que se abre elegimos la opción Excel Files



Ubicamos la carpeta donde guardamos el archivo (en nuestro caso Datos.xls) y elegimos el archivo



En el diálogo Elegir Columna veremos las dos hojas que contiene nuestro cuaderno



Para ver las tablas, que de hecho son las hojas del cuaderno, debemos asegurarnos que la opción Tablas del sistema esté seleccionada. Para hacer esto apretamos el botón Opciones



Nuestro objetivo es agregar los campos Fecha y Cliente a la hoja Ventas. Apretamos el signo + de Facturas para ver los campos, y elegimos Fecha y Cliente



Ahora señalamos la tabla Ventas y pasamos todos los campos apretando la flecha correspondiente



Al hacer esto veremos este mensaje



MsQuery nos pide que creemos un vínculo entre ambas tablas (unión). Haremos esto más adelante. Por ahora apretamos Aceptar y seguimos



Al apretar Aceptar se abre la interfaz del MS Query. En la parte superior vemos las tablas de datos que hemos importado al MS Query. En la zona de datos, vemos todos los datos de ambas tablas. Como podrán comprobar, en esta tabla hay 90 registros. Esto se debe a que no hemos creado ningún vínculo específico entre las dos tablas y por lo tanto se crean registros redundantes.
Existen distintos tipo de vínculos entre tablas. Quienes tengan conocimientos de SQL, podrán resolver el caso fácilmente. Para quien no conozca SQL, MS Query ofrece un asistente, que es lo que emplearemos en este ejemplo.
En el menú de MS Query elegimos Tablas-Uniones



En la ventana de diálogo del asistente nos aseguramos que en Izquierda y Derecha figure el campo Factura, en una ventanilla el campo de la tabla Facturas y en la otra él de Ventas. Elegimos la opción 3 de las opciones de unión (Left Outer Join, en SQL)



Apretamos Agregar y luego Cerrar. A continuación apretamos el botón de Ejecutar consulta ahora



El resultado es la tabla que estamos buscando. Nótese la forma gráfica en la que MS Query indica el tipo de unión que hemos creado entre las tablas



Todo lo que nos queda por hacer es transferir los datos al cuaderno de Excel que hemos abierto para el caso. Esto lo hacemos con el menú Archivo-Devolver datos a Excel



Excel activa la hoja de la cual hemos partido y abre un diálogo donde podemos elegir desde que celda importar los datos. Existen varias opciones que son visibles al apretar los botones Propiedades y Modificar consulta, y que invito a que investiguen por vuestra cuenta.



Al apretar Aceptar Excel importa los datos a la hoja.



Como ven, Excel también pone la barra de comandos Datos Externos. Esta barra nos permite renovar los datos, con el icono "!" y también editar la consulta. Una de las grandes ventajas de este método es que Excel guarda un vínculo con las tablas de datos lo que nos permite actualizar con comodidad y eficiencia la hoja de Excel cada vez que cambiemos o agreguemos datos en las tablas de origen.

Quien quiera practicar el ejercicio, puede descargar el archivo datos.xls aquí.


Technorati Tags:

19 comments:

Fernanda,  22 abril, 2008 01:00  

Hola!

Te molesto nuevamente...

He intentado realizar esto con mi libro. Pero cuando selecciono el libro, el que sea, pues lo intente primero con el que trabajo actualmente, luego con otro y finalmente descarge el mismo archivo con el que tu lo hiciste, pero aparece "El origen de datos no contiene tablas visibles"...

Podrias decirme porque me pasa esto?

Gracias!

Jorge L. Dunkelman 22 abril, 2008 22:46  

Como señalo en la nota, tienes que asegurarte que la opción Tablas del Sistema esté marcada.

Anónimo,  25 abril, 2008 10:27  

Don Jorge, excelente blog excelente trabajo, siempre reviso este blog por los ejemplos y utilidades, por ejemplo este ejercicio de consolidar con el ms query nunca imagine, yo esto lo habia utilizado para conectar a bases de access y uzar filtros simples, pero ahora se me abre mas el craneo, muy bien.

Me gustaria si es posible ver alguna sección referente a la estadistica con excel, ejemplos de proyecciones, pronostico, estimaciones, en este tema estoy Know Zero.

Gracias por JLD Excel Blog

Jorge L. Dunkelman 25 abril, 2008 16:03  

Hola
si tienes instaladao el Analysis ToolPak, Excel pone a tu disposición un arsenal de herramientas para el análisis estadístico. Como no soy un experto en Estadística, no estoy en condiciones de juzgar la precisión de estas herramientas. Pero para un uso a nivel no académico, son excelenetes.

Gus 03 julio, 2008 03:33  

Hola

Es posible utilizar este método con datos de otra hoja del mismo libro o siempre tiene que ser de otro archivo de excel?

Gracias y Saludos!

Jorge L. Dunkelman 03 julio, 2008 17:14  

Hola Gus

si, se puede. Cada hoja aparece como tabla de datos, lo mismo que si estuviera en otro cuaderno.

zapayita 11 agosto, 2008 03:09  

Hola!!!
quisiera saber còmo hacer para combinar la hoja 1 de varios libros de excel. Todas tienen el mismo encabezado pero los datos dentro de la tabla son diferentes (es un padròn de datos personales con unas 6 o 7 columnas). Varias personas cargan los datos (cada una en su libro) y tengo que armar un padròn general que contenga todos los datos.
Muchas gracias.

Silvia

Jorge L. Dunkelman 11 agosto, 2008 19:38  

Hola Silvia

hay varios caminos posibles.
Una posibilidad sería organizar todos los datos en un único cuaderno, lo que te permitiría consolidar los datos con facilidad usando Datos--Consolidar o Tablas Dinámicas.
Si los datos tienen que quedar separados en distintos cuadernos, el tema es más complicado y el marco de un comentario no es suficiente para la explicación.
En breve publicaré una nota sobre el tema.

Snakeraptor 28 septiembre, 2008 09:10  

Buenas noches Don Jorge:
Tengo una hoja de excel donde capturo todos los movimientos de los servicios que les ofrecemos a nuestro clientes, requiro hacer un query de cuantas veces ha visitado alguno de nuestros tecnicos al cliente, es decir solo requiero saber que al cliente 1 lo ha visitado el técnico 1, 3 veces, el técnico 2, 1 vez, etc, esto para cada uno de los clientes que tenemos. Quiciera saber si es posible en el ejemplo que dio sobre el MS Query y Excel hacer esto y cómo se hace?
Gracias por la ayuda!!!
FG

Jorge L. Dunkelman 28 septiembre, 2008 18:55  

Puedes crear una tabla de datos con Query, como muestro en la nota, y luego generar reportes con tablas dinámicas.
Si necesitas orientación puedes ponerte en contacto conmigo a través del mail.

Aquello que haces, te hace 23 julio, 2009 15:57  

Jorge, llevo solo unas pocas semanas leyendo sus valiosos aportes, he intentado buscar una ayuda que me permita consolidar los totales de varias tablas ubicadas en 7 hojas para totalizarlas en una ultima hoja independiente. quizá sea muy fácil, pero la verdad no soy muy habil con el excel. gracias por su ayuda

Jorge L. Dunkelman 23 julio, 2009 19:25  

Te sugiero que empieces por consolidar los datos en una única hoja. Excel 2007 te permite poner en una hoja más de un millón de líneas. Si usas una versión anterior, el límite será 65535 líneas (reservamos una para los títulos)

Marcelo Muñoz 19 diciembre, 2012 22:59  

Estimado Jorge, consulta: me sirve esta herramienta cuando tengo mas de dos hojas? Tengo en un libro 7 hojas donde repito un dato clave. Lo que busco es hacer un query extrayendo de varias de esas hojas datos siempre relacionados al mismo dato clave.
Desde ya muchas gracias, Slds,

Santandereano1970 13 enero, 2014 15:11  

Don Jorge, si las tablas a vincular están en archivos separados, es posible aplicar este procedimiento?

Jorge Dunkelman 15 enero, 2014 13:54  

Hola, si, se puede. El marco de un comentario es un poco estrecho para explicar el proedimiento. Publicaré una nota sobre el tema cuando vuelva "al terruño" (estoy de viaje por cuestiones de trabajo).

Giorgio 14 agosto, 2015 15:47  

Genial Jorge, como me tiene acostumbrado
GRACIAS!

JACKJONTER 01 julio, 2016 20:14  

COMO FUNCIONARIA PARA UNOS 5 ARCHIVOS PERO QUE TODOS TIENEN LAS MISMAS COLUMNAS DE DATOS

Jorge Dunkelman 02 julio, 2016 11:37  

Por suerte Exel ha avanzado desde que escribi este post y hoy en día contamos con la potencia del Power Query. Así que olvidate del MS Query y dale un vistazo a este post de cómo consolidar archivos con Power Query.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP