Consolidar datos de varios cuadernos Excel con MS Query

viernes, marzo 05, 2010

Ya hemos tratado anteriormente el tema de consolidación de datos de varios cuadernos u hojas Excel usando MS Query. Sin embargo el uso de esta herramienta presenta ciertos problemas y la ayuda en línea del MS Query es muy pobre.

En esta nota veremos cómo lograr esta consolidación para lo cual tendremos que editar y modificar la consulta SQL que genera el MS Query. Aclaremos que esta tarea es sencilla y no requiere conocimientos del lenguaje SQL.

En nuestro ejemplo suponemos que tenemos los datos de ventas de tres sucursales de una empresa en tres cuadernos distintos: ventasNorte.xls, ventasSur.xls y ventasOeste.xls. Las tablas de datos de cada cuaderno tienen la misma estructura, es decir, los mismos campos (columnas) en las mismas posiciones.

Empezamos por incluir cada tabla en un nombre. Por ejemplo, en ventasNorte.xls incluimos la tabla de datos en el nombre consNorte


También podemos crear el nombre usando el cuadro de nombres




El próximo paso es abrir un cuaderno en blanco donde consolidaremos los datos. En el nuevo cuaderno activamos la pestaña Datos y en Obtener Datos Externos usamos la opción Desde Microsoft Query




En Excel 2003 usamos




En Elegir origen de datos seleccionamos Excel Files




En el paso Select Workbook (seleccione cuaderno), seleccionamos uno de los cuaderno, por ejemplo, ventasNorte.xls




En el próximo paso seleccionamos el nombre del rango y pasamos todas las columnas a la consulta




Seguimos adelante en el proceso hasta llegar al último paso donde seleccionamos la opción Ver datos o modificar en Ms Query




Lo que veremos en la interfaz del MS Query que se abre es esto




Apretamos el botón SQL, lo que nos permite acceder a la sintaxis de la consulta.




En lugar de

SELECT rngNorte.Sucursal, rngNorte.Mes, rngNorte.Ventas, rngNorte.Clientes
FROM `D:\Ventas\ventasNorte`.rngNorte rngNorte

ponemos

SELECT * FROM `D:\Ventas\ventasNorte`.rngNorte
UNION ALL
SELECT * FROM `D:\Ventas\ventasSur`.rngSur
UNION ALL
SELECT * FROM `D:\Ventas\ventasOeste`.rngOeste




Al apretar Aceptar veremos




Apretamos Aceptar y todos los datos serán consolidados en la tabla del MS Query




En el menú del Query elegimos Archivo-Devolver los datos a Excel lo que abre el diálogo de Importar datos en Excel, donde podemos elegir la forma de ver los datos en Excel. Por lo general usaremos la opción Informe de tabla dinámica




En Excel 2003 la interfaz es algo distinta



32 comments:

Anónimo,  05 marzo, 2010 18:13  

Hola, muy buena la entrada (es de las que me gustan); como siempre hay algo que decir, unas cosillas:
1. Cuál es la ventaja de este método en contraposición de hacer algo "más cutre" utilizando el método de copiar y pegar de las distinas hojas...
2. A mi al darle al botón SQL, me sale como a ti pero me sale también la extensión del archivo (C:\Ventas\ventasNorte.xlsx`.rngNorte rngNorte)
3. Podías explicar sucintamente el cuadro de "Select*From", ya que no sé el símbolo "*", nio tampoco que le pones Select, pero no le especificas nada (por ejemplo, en la pantalla inicial te ponía lo que seleccionaba, es decir, SELECT rngNorte.Sucursal, rngNorte.Mes, rngNorte.Ventas, rngNorte.Clientes)

Un Saludo y sigue así

Jorge L. Dunkelman 05 marzo, 2010 20:31  

Veamos,
1 - si estás usando Excel Clásico tienes un límite de 65535 filas para datos. Con este método no tienes ese límite. Además cuando cambian los datos en los cuadernos de origen todo lo que hay que hacer es refrescar la consulta con el botón correspondiente.
2 - Tal vez se deba a que el archivo de origen es Excel 2007; yo use para ejemplo archivos de Excel 2003
3 - No explico ese pasoporque parto de la base que no todos los lectores conocen el lenguaje SQL. SELECT * es la instrucción para seleccionar todas las columnas (campos) de la tabla de origen. Esto reemplaza mencionar los campos explícitamente. En nuestro caso hay sólo cuatro, pero imaginate una tabla con 50 campos...

Anónimo,  05 marzo, 2010 21:01  

parece q se le puede sacar mucho partido a lo del Query, sabes si hay algún manual escrito o en internet (que sea en español), sobre esta aplicacion?, seria interesante.

Jorge L. Dunkelman 06 marzo, 2010 01:56  

No conozco ningún manual, tampoco en inglés. Pero me has dado una idea: escribir una guía sobre los usos de MS Query en castellano.

sus_80 06 marzo, 2010 18:41  

he visto este enlace y los anteriores sobre el tema, rizando el rizo se podría hacer algo como lo q sigue: en una hoja tengo los campos Nombre, Apellido, Nota numérica de un exámen; en otra hoja tengo como campo común Nota numérica de un exámen y al lado su correspondencia en letra (aprobado, notable, muy deficiente,...); pero era unsa Query (si se puede) de tal manera que por ejemplo si un alumno tiene un 5,3 ... pues que en la tabla de las calificaciones con letra viera que es un Aprobado...o sea, que en la hoja2 establecer un frecuencia de tal manera que pongas entre 5-7 aprobado...y eso te vale para todos los valores que pongas en la Hoja1 (5.1, 5.2, 5.3 ,.....)

sus_80 06 marzo, 2010 22:15  

he visto este enlace y los anteriores sobre el tema, rizando el rizo
se podría hacer algo como lo q sigue: en una hoja
tengo los campos Nombre, Apellido, Nota numérica de un exámen;
en otra hoja tengo como campo común Nota numérica de un exámen y al
lado su correspondencia en letra (aprobado, notable, muy deficiente,...);
pero era unsa Query (si se puede) de tal manera que por ejemplo si un alumno
tiene un 5,3 ... pues que en la tabla de las calificaciones con letra viera que
es un Aprobado...o sea, que en la hoja2 establecer un frecuencia de tal manera
que pongas entre 5-7 aprobado...y eso te vale para todos los valores que pongas
en la Hoja1 (5.1, 5.2, 5.3 ,.....)

Jorge L. Dunkelman 06 marzo, 2010 23:44  

¿Por qué con el Query? Se puede hacer facilmente con funciones (BUSCARV por ejemplo)

Anónimo,  09 marzo, 2010 02:09  

Hola Jorge,

Un saludo afectuoso desde Madrid.

Una pregunta: ¿qué quieres decir cuando en el comentario del 5 de marzo dices lo siguiente?

"1 - si estás usando Excel Clásico tienes un límite de 65535 filas para datos. Con este método no tienes ese límite."

Gracias

Jorge L. Dunkelman 09 marzo, 2010 06:45  

El límite de filas (registros) posibles en el MS Query está dado sólo por la cantidad de memoria disponible. En Excel Clásico (versiones 97-2003) el límite de filas posibles es 65536(en Excel 2007-10 algo más de un millón).
Si quisiera consolidar en una hoja datos de dos o más hojas que excedan este límite, no podrías hacerlo con Excel Clásico.

Anónimo,  09 marzo, 2010 19:06  

Hola Jorge,

El Excel Clásico que estoy usando es el 2003, junto con Windows XP.
La prueba que hice fue la siguiente:
Usé 3 hojas de 3 distintos workbooks con 25.000 filas cada una.
En el libro de consolidación me generó una consulta con el Microsoft Query de 65.536 filas, igual que el máximo habitual de filas de Excel 2003. Si le doy Registros>Ir a... el último que me muestra es el 65.536.
En la hoja de cálculo, al devolver datos, me generó también una lista con 65.536 filas. Sólo había una fila (registro) de diferencia porque en el Query no aparecía la fila de encabezamiento y en la hoja Excel sí, pero por lo demás todo era igual.
Sigo sin saber, cuando dices que con este método no tienes este límite de 65.536 filas, a qué te refieres exactamente.

Un saludo,
Sergio

Jorge L. Dunkelman 09 marzo, 2010 23:14  

Sergio,
el límite no existe en el Query, en Excel Clásico si existe. La idea no es exportar la consulta a una hoja de Excel, sino analizar los resultados con una tabla dinámica usando esa opción (fijate en la parte final de la nota).

Anónimo,  10 marzo, 2010 01:58  

Sigo tu blog desde hace mucho...sólo paso a felicitarte

Anónimo,  10 marzo, 2010 10:42  

Jorge:

Está claro que el que no quiere ver no ve. No me daba cuenta que en ese cuadro de diálogo de Excel 2003 había una opción de "Crear un informe de tabla dinámica...".

Efectivamente lo comprobé y ahí sí que incluye todos los datos parciales a pesar de sumar entre todos más de 65.536 filas y la consolidación la hace bien.

Gracias y un abrazo,
Sergio

Anónimo,  10 marzo, 2010 11:22  

Hola Jorge,

Soy Sergio otra vez.
Estuve dándole vueltas al tema de la creación de la tabla dinámica haciendo distintas pruebas. La principal conclusión es que en principio, si agrego alguna fila en los datos originales, la tabla dinámica no se actualiza.
Finalmente lo solucioné con la técnica (aprendida de tu Blog) de crear Listas allí donde estaban los nombres rngNorte, rngSur y rngOeste. Los nombres van cambiando dinámicamente a medida que añadimos filas, tal como tú nos enseñaste. Eso sí, hay que tener cuidado con el tema de las columnas porque ahí el nombre no se expande dinámicamente como en las filas. De todos modos se supone que las columnas de una tabla de datos deberíamos tenerlas claras cuando creamos una hoja de cálculo.
Antes lo había intentado con la función DesRef en la definición de cada nombre, pero en la Tabla Dinámica no me lo reconocía.

Un saludo,
Sergio

Anónimo,  11 marzo, 2010 00:25  

Hola Joge, me parece fantastico para mantener los datos actualizados ¿pero que pasa, si el rango definido en las hojas aumenta de filas?

Saludos

Jorge L. Dunkelman 11 marzo, 2010 13:22  

Si guardas la consulta en el MS Query, se la puede actualizar con facilidad y por lo tanto también la tabla dinámica.

Anónimo,  19 marzo, 2010 04:19  

Holq Jorge

Soy Jose Francisco

Mira, me da pena devolverme un poco y según recuerdo esta nota salio de una duda porque cuando uno iba a combinar dos hojas de libros distintos el cuadro de buscar archivo no se activaba....esto me sigue dando vueltas!!!
ESta solución esta muy chevere, y me gustaría tambien hacerlo por la otra via, como debo configurar el excell o que debo hacer para activar esta casilla?

Mil gracias

Rubencillo 19 marzo, 2010 17:18  

Hola Jorge.

Con Excel 2003, al seleccionar el archivo origen, me sale el mensaje "La tabla no tiene el formato esperado", ya no se que más hacerle, definí el nombre, borre el nombre, definí la lista, borre la lista, sin nombre y sin lista, nada, los datos son tabulares, con cabeceras (solo caracteres alfabéticos sin acento), los datos son numéricos, pero igual, no me permite seleccionar el archivo origen, puedes aconsejarme algo para resolver?

Rubencillo 19 marzo, 2010 17:27  

Estimado Jorge:

En mi desesperación combinada con frustración por no poder usar esta herramienta, di con la solución (empleando ensayo error) y funciona perfectamente con Excel 2003, la solución exige que definas el area de trabajo (la base de datos) como lista Y como nombre, preparada así, el query funciona sin problemas.

Un fuerte abrazo.

Jorge L. Dunkelman 19 marzo, 2010 18:21  

Felicitaciones y gracias por compartir. Y como decía mi abuelita, "la necesidad es la madre de todas la invenciones".

César,  24 marzo, 2010 20:00  

Recientemente he descubierto esta página, y por ente como sacarle más provecho a excel. Por lo que lo primero de todo es darte mi más sincera enhorabuena por este blog.

Ahora las dudas y sugerencias.

1. He montado la consolidación con excel 2007, y no he necesitado administrar nombre, directamente he cogido el nombre de la pestaña, ¿por qué sugieres hacerlo?

2. Cada vez que accedo a la consulta me aparece el siguiente mensaje "No se reconoce el formato de base de datos '[Ruta]\[Nombre fichero].xlsm' y tengo que volver a seleccionar el fichero excel ¿Hay alguna manera de mantener fijo el fichero y evitar que salga este mensaje ?

3. En mi caso, alguno de los nombres de los campos no son iguales, y no los puedo modificar en origen, es decir, por desgracia no puedo utilizar el * ¿o si?. Así que lo he solucionado modificando la query. Partiendo de tu ejemplo pongo mi solución, por si le puede servir a alguien más:

SELECT rngNorte.SucursalN AS Sucursal, rngNorte.Mes, rngNorte.Ventas, rngNorte.ClientesS AS Clientes
FROM `D:\Ventas\ventasNorte`.rngNorte rngNorte
UNION ALL
SELECT rngSur.SucursalS AS Sucursal, rngSur.Mes, rngSur.Ventas, rngSur.ClientesS AS Clientes
FROM `D:\Ventas\ventasSur`.rngSur rngSur

Un saludo.

Anónimo,  10 septiembre, 2010 00:56  

Hola buen dia Jorge mira yo trabaje con un archivo siguiendo los pasos indicados, solo que mis bases de datos se encuentan en el mismo archivo mediante hojas y donde pretendo que aparezca la consolidacion es en una misma hoja al final del archivo solo que despues de hacer los cambios donde se sustituye en ms Querry por Select * from y ruta de cada archivo me aparece un mensaje despues de darle aceptar en "Imposible presentar graficamente una consulta SQL que dice imposible agregar la tabla y la ruta del archivo, será que es un procedimiento diferente para el caso que yo pretendo hacer? agradezco tu respuesta y de antemano gracias por tu apoyo

Jorge L. Dunkelman 10 septiembre, 2010 12:02  

Puede deberse a varias razones y me resulta difícil hacerme una idea sin ver el archivo. Podés fijarte en esta nota que trata específicamente la consolidación de hojas de un mismo cuaderno.

Anónimo,  07 abril, 2011 09:27  

hola una consulta.

si tengo 19 tiendas y cada una con un archivo de ventas al mes. es decir: 19*12 = 228 archivos vinculados a un "archivo general anual de ventas": que pasa si los muevo de ubicación(a otra carpeta o a otra computadora) ya sea uno o varios archivos, o todos incluyendo el archivo general.

mi pregunta concreta es si deseo mover los 228 archivos (según como vayan ingresando mes a mes)a otra computadora y también obviamente mover el archivo general. como hago para que no se estropeen las formulas de vinculación???

Gracias y un saludo cordial.

Jorge L. Dunkelman 09 abril, 2011 09:33  

Si mantienes la estructura, es decir, la misma ubicación en la nueva computadora, no tendrías que tener problemas. También t sugiero, si no lo has hecho hasta ahora, hacer una búsqueda en la red. Podrás encontrar varias soluciones (el marco de un comentario no da para hacer un análisis de todas las posiblidades).
Pero me intirga por qué creas un archivo para cada mes y otro para totalizar. Te sugiero que crees un archivo único para todos los datos, que incluya uun campo (columna) para el mes/año y totalizar los resultados con una tabla dinámica. Sería mucho más eficiente y te ahorrará muchos dolores de cabeza (como el que estás teniendo en estos momentos).

CeliuxMax,  26 junio, 2011 07:06  

hola que tal, me ha sido super util esta pagina, la encontre como recomendacion en un libro que compre hoy... Estoy elaborando un libro en excel de cinco zonas (Norte, Sur, Pacifico, Golfo, Centro), y debo registrar sus reportes en el año pero debo compararlo contra dos sistemas (es decir dos folios o reportes diferentes, para verificar que sean la misma cantidad de reporte o la diferencia entre ellos por cada zona); el detalle es este, tengo 106 reporte en la zona norte, 15 en la zona sur, 2 en la zona centro.... como puedo hacer con microsoft query para se vaya actualizando cada vez que se registre un nuevo reporte o muestre solo las filas que contengan valor... o alguna sugerencia que me puedas dar para solucionar mi problema?...de ante mano muchas gracias :)

Jorge L. Dunkelman 26 junio, 2011 10:14  

CeliuxMax,

si guardas las definiciones del query, puedes acutlizarlo en cualquier momento. No me queda claro que significa "reportes" en tu consulta (registros?).
Te sugiero que sigas la consulta conmigo por mail privado(fijate en el enlace Ayuda, en la parte superior del blog).
Por curiosidad, ¿en que libro encontrate la mención a este blog?

Rodrigo 13 julio, 2011 00:43  

Estimado Jorge:

Muchas gracias por la ayuda que me brindó... me fue muy útil su referencia a este artículo para lo que debía hacer...

Albely 06 septiembre, 2011 02:11  

Estimado JOrge:

Quisiera hacer uso del MS Query, pero tengo una duda, ¿Si las bases de datos que quiero consolidar me van cambiando mes a mes porque le voy agrego mas filas, esta actualizacion queda registrada en el MS Query?
Muchas Gracias por tu ayuda.

Albely Santos

Jorge L. Dunkelman 06 septiembre, 2011 12:27  

Si, los nuevos datos se reflejan al actualizar.

Christian 05 enero, 2012 15:41  

Como dato: en algunos casos no funciona de esta forma,por que aparecen error en las tablas( desconozco por que?) pero solo se debe hacer un pequeño cambio, todo es igual pero en donde dice "..consulta desde MSQUERY.." haganlo desde el "...asistente para la conexion de datos.." y ningun problema(tampoco se por que de una manera si y la otra no) aparece la instruccion SQL tal cual lo menciona Jorge L. Dunkelman

Saludos

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP