Consolidar datos de varios libros de Excel con MS Query

Podemos consolidar datos de varios libros Excel utilizando la herramienta Microsoft Querypor medio de una sola consulta y a final de cuentas tener una sola hoja con todos los datos. Para este ejemplo tendremos  dos archivos de Excel, el primero con nombres de empleados y el segundo con los salarios de cada uno de ellos.
Es importante que los datos tengan una columna de identificación que nos permitirá encontrar las coincidencias entre los registros de ambos archivos. En este ejemplo, la columna ID será la que nos permitirá relacionar correctamente la información.
Consolidar varios archivos Excel con Microsoft Query

Establecer un nombre de rango

El primer paso es establecer un nombre de rango para los datos en cada uno de los archivos. Para hacerlo debes seleccionar las celdas correspondientes y en el cuadro de nombres especificar un nombre descriptivo. En el primero de los archivos nombraré al rango comoEmpleados y en el segundo archivo el rango se llamará Salarios.
Asignar nombre a rango de celdas
Una vez asignados los nombres en cada archivo debes guardar los cambios. Debido a la técnica que utilizaremos es muy importante que los archivos estén guardados en la misma carpeta.

Consolidar datos en un libro nuevo

Abre un nuevo libro de Excel y ve a la ficha Datos y pulsa el botón De otras fuentes y selecciona la opción Desde Microsoft Query. Se mostrará el cuadro de diálogo Elegir origen de datos y debemos seleccionar la opción Excel Files.
Elegir origen de datos
Pulsa el botón Aceptar y se abrirá el cuadro de diálogo Seleccionar libro el cual nos permitirá abrir el primero de nuestros libros. Para continuar con el ejemplo seleccionaré el Libro1 el cual contiene los nombres de los empleados.

El Asistente para consultas

La siguiente pantalla del Asistente para consultas nos permitirá elegir las columnas que deseamos incluir.
Elegir columnas en el Asistente para consultas
Una vez seleccionadas las columnas y hacer clic en el botón Siguiente, pasaremos al segundo paso del Asistente que nos permitirá filtrar los datos si así lo deseamos. El tercer paso nos permitirá ordenar los registros por alguna columna específica y al hacer clic en el botón Siguiente llegaremos al último de los pasos.
Elegir Ver datos o modificar consulta en Microsoft Query
En este último paso debemos seleccionar la opción Ver datos o modificar consulta en Microsoft Query. Al hacer clic en Finalizar se mostrará Microsoft Query con los datos del primer archivo.
Datos mostrados en Microsoft Query
Observa que los registros corresponden a los del primer archivo de Excel. Para consolidar los datos del segundo libro debemos seguir algunos pasos adicionales.

Agregar tablas adicionales

Dentro de Microsoft Query, haz clic en el menú Tabla y selección la opción Agregar tablas. Se mostrará el siguiente cuadro de diálogo:
Agregar nueva tabla a consulta
En la lista llamada Libro, que se encuentra en la parte inferior, selecciona el segundo archivo de Excel y de inmediato se mostrará el nombre del rango previamente configurado para el archivo. Haz clic en el botón Agregar y posteriormente en el botón Cerrar. Observarás una nueva tabla dentro del panel de Microsoft Query.
Consulta de Microsoft Query a dos tablas de datos
Este proceso lo puedes repetir en caso de que tengas más archivos de Excel de manera que termines con todos los datos que serán consolidados dentro de Microsoft Query.

Crear una unión de tablas

El siguiente paso es crear una unión de tablas utilizando la columna de identificación (llave). En este ejemplo me refiero a la columna ID que en la primera tabla identifica el nombre del empleado y en la segunda tabla su salario correspondiente. Haz clic en la opción de menú Tabla y selecciona la opción Uniones. Se mostrará el siguiente cuadro de diálogo.
Crear unión entre dos tablas en Microsft Query
Debemos asegurarnos que la unión se realice utilizando las columnas ID de ambas tablas y que el operador es el símbolo igual (=). Al pulsar el botón Agregar se creará una nueva unión y al cerrar el cuadro de diálogo podrás observar que el panel inferior de la ventana solo muestra los registros de los empleados del primer archivo de Excel. También podrás ver la relación entre ambas tablas de manera de manera gráfica a través de una conector que une ambas tablas.
Consulta a dos tablas unidas por un campo

Agregar una columna a los resultados

Solo nos resta agregar la columna de Salario a los resultados. Para ello debes hacer clic en el encabezado de la columna vacía que se muestra a la derecha de la última columna y seleccionar el nombre del campo deseado. Para nuestro ejemplo es el campo Salarios.Salario
Agregar columna de resultados a una consulta de Microsoft Query
Al seleccionar el campo se mostrarán los datos correspondientes para cada registro.
Datos consolidados de diferente archivos Excel
Con este último paso hemos terminado de consolidar datos de varios libros de Excel con MS Query. Solo nos resta llevar la información a nuestra hoja de Excel.

Devolver datos a Excel

El último paso es seleccionar el menú Archivo y seleccionar la opción Devolver datos a Microsoft Excel. Se mostrará el cuadro de diálogo Importar datos preguntando la ubicación a donde se colocará la información y al hacer clic en Aceptar se insertará una tabla en la hoja de Excel con los datos consolidados.
Datos consolidados y mostrados en una hoja de Excel
Microsoft Query nos permite consolidar datos procedentes de varias fuentes de información ahorrándonos tiempo y esfuerzo. Considera esta alternativa la próxima vez que necesites consolidar datos de varios libros de Excel.

Si te gustó, suscríbete al Feed RSS de Siempre Excel y recibe nuestras actualizaciones

Publicar un comentario

0 Comentarios