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
Hola, muy buena la entrada (es de las que me gustan); como siempre hay algo que decir, unas cosillas:
ResponderBorrar1. 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í
Veamos,
ResponderBorrar1 - 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...
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.
ResponderBorrarNo 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.
ResponderBorrarhe 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 ,.....)
ResponderBorrarhe visto este enlace y los anteriores sobre el tema, rizando el rizo
ResponderBorrarse 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 ,.....)
¿Por qué con el Query? Se puede hacer facilmente con funciones (BUSCARV por ejemplo)
ResponderBorrarHola Jorge,
ResponderBorrarUn 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
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).
ResponderBorrarSi 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.
Hola Jorge,
ResponderBorrarEl 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
Sergio,
ResponderBorrarel 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).
Sigo tu blog desde hace mucho...sólo paso a felicitarte
ResponderBorrarJorge:
ResponderBorrarEstá 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
Hola Jorge,
ResponderBorrarSoy 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
Hola Joge, me parece fantastico para mantener los datos actualizados ¿pero que pasa, si el rango definido en las hojas aumenta de filas?
ResponderBorrarSaludos
Si guardas la consulta en el MS Query, se la puede actualizar con facilidad y por lo tanto también la tabla dinámica.
ResponderBorrarMuy buen aporte.
ResponderBorrarHolq Jorge
ResponderBorrarSoy 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
Hola Jorge.
ResponderBorrarCon 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?
Estimado Jorge:
ResponderBorrarEn 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.
Felicitaciones y gracias por compartir. Y como decía mi abuelita, "la necesidad es la madre de todas la invenciones".
ResponderBorrarRecientemente 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.
ResponderBorrarAhora 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.
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
ResponderBorrarPuede 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.
ResponderBorrarhola una consulta.
ResponderBorrarsi 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.
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).
ResponderBorrarPero 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).
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 :)
ResponderBorrarCeliuxMax,
ResponderBorrarsi 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?
Estimado Jorge:
ResponderBorrarMuchas gracias por la ayuda que me brindó... me fue muy útil su referencia a este artículo para lo que debía hacer...
Estimado JOrge:
ResponderBorrarQuisiera 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
Si, los nuevos datos se reflejan al actualizar.
ResponderBorrarComo 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
ResponderBorrarSaludos