Combinar datos de varias hojas de trabajo en una sola hoja de trabajo en Excel

Hace poco recibí una pregunta de un lector sobre la combinación de varias hojas de trabajo del mismo libro en una sola hoja de trabajo.

Le pedí que usara Power Query para combinar diferentes hojas, pero luego me di cuenta que para alguien nuevo en Power Query hacer esto puede ser difícil.

Así que decidí escribir este tutorial y mostrar los pasos exactos para combinar varias hojas en una sola mesa mediante Power Query.

A continuación, un vídeo donde muestro como combinar datos de varias hojas / tablas mediante Power Query:

A continuación se muestran las instrucciones escritas sobre cómo combinar varias hojas (en caso de que prefiere el texto escrito sobre el vídeo).

Nota: Power Query se puede utilizar como complemento a Excel 2010 y 2013, y es una función incorporada a partir de Excel 2016. Según su versión, algunas imágenes pueden tener un aspecto diferente (las capturas de imágenes que se utilizan en este tutorial son de Excel 2016).

Combine datos de varias hojas de trabajo mediante Power Query

Cuando se combinan datos de diferentes hojas mediante Power Query, hay que tener los datos en una tabla de Excel (o al menos en intervalos con nombre). Si los datos no se encuentran en una tabla de Excel, el método que se muestra aquí no funcionaría.

Supongamos que tiene cuatro hojas diferentes: este, oeste, norte y sur.

Cada uno de estas hojas de trabajo tiene los datos en una tabla de Excel y la estructura de la tabla es coherente (es decir, las cabeceras son las mismas).

Este tipo de datos es extremadamente fácil de combinar mediante Power Query (que funciona muy bien con los datos de la tabla Excel).

Para que esta técnica funcione mejor, es mejor tener nombres para las tablas de Excel (funcionáis sin ella también, pero es más fácil de usar cuando se llaman las tablas).

He dado en las tablas los nombres siguientes: Dades_Est, Dades_Oest, Dades_Nord y Dades_Sud.

Estos son los pasos para combinar varias hojas de trabajo con tablas de Excel mediante Power Query:

  1. Ve a la pestaña Datos.
  2. En el grupo Obtener y transforma datos, haga clic en la opción “Obtener datos”.
  3. Vaya a la opción “Otros fuentes”.
  4. Haga clic en la opción “Consulta en blanco”. Esto abrirá el editor de Power Query.
  5. En el editor de consultas, escriba la fórmula siguiente en la barra de fórmulas: = Excel.Libro de trabajo actual(). Tenga en cuenta que las fórmulas de Power Query distinguen entre mayúsculas y minúsculas, por lo que debe utilizar la fórmula exacta tal como se ha mencionado (en caso contrario, obtendrá un error)
  6. Pulse la tecla Intro. Esto le mostrará todos los nombres de las tablas de todo el libro (también le mostrará los intervalos y / o las conexiones llamados en caso de que exista en el libro).
  7. En este ejemplo, quiero combinar todas las tablas. Si sólo desea combinar tablas de Excel específicas, puede hacer clic en el icono desplegable de la cabecera del nombre y seleccionar las que desea combinar. Del mismo modo, si ha llamado intervalos o conexiones y sólo desea combinar tablas, también puede eliminar estos intervalos.
  8. En la celda de cabecera de contenido, haga clic en la flecha de doble punto.
  9. Seleccione las columnas que desea combinar. Si desea combinar todas las columnas, asegúrese de que la opción Seleccionar todas las columnas esté marcada.
  10. Desmarque la opción “Usar el nombre de la columna original como prefijo”.
  11. Haga clic en Aceptar.

Los pasos anteriores combinarían los datos de todas las hojas de trabajo en una sola mesa.

Si os fijáis bien, encontrará que la última columna (más a la derecha) tiene el nombre de las tablas Excel (Dades_Est, Dades_Oest, Dades_Nord y Dades_Sud). Este es un identificador que nos indica qué registro provino de qué tabla de Excel. Esta es también la razón por la que he dicho que es mejor tener nombres descriptivos para las tablas de Excel.

A continuación, se muestran algunas modificaciones que puede hacer a los datos combinados en la propia Power Query:

  1. Arrastre y coloque la columna Nombre al principio.
  2. Eliminar la “_Datos” de la columna del nombre (de forma que le queden al este, al oeste, al norte y al sur en la columna del nombre). Para ello, haga clic con el botón derecho del ratón en la cabecera Nombre y haga clic en Reemplazar los valores. En el cuadro de diálogo Reemplazar los valores, sustituya _Datos por un blanco.
  3. Cambie la columna Datos para mostrar sólo las fechas (y no la hora). Para ello, haga clic en la cabecera de la columna Fecha, vaya a la pestaña “Transformar” y cambie el tipo de datos en Data.
  4. Cambie el nombre de la consulta a ConsolidatedData.

Ahora que tiene los datos combinados de todas las hojas de trabajo en Power Query, puede cargarlos a Excel, como tabla nueva en una hoja de cálculo nueva.

Hacer esto. siga los siguientes pasos:

  1. Haga clic en la pestaña “Archivo”.
  2. Haga clic en Cerrar y carga a.
  3. En el cuadro de diálogo Importar datos, seleccione Tabla y opciones de hoja de cálculo nueva.
  4. Haga clic en Aceptar.

Los pasos anteriores combinarían datos de todas las hojas de trabajo y le proporcionarían estos datos combinados en una hoja de cálculo nueva.

Un problema que debe resolver al utilizar este método

En caso de haber utilizado el método anterior para combinar todas las tablas del libro, es probable que tenga algún problema.

Véase el número de filas de los datos combinados: 1304 (que es correcto).

Ahora, si actualizo la consulta, el número de filas cambia a 2607. Actualizar de nuevo y cambiará a 3910.

 

He aquí el problema.

Cada vez que actualice la consulta, añade todos los registros de los datos originales a los datos combinados.

Nota: sólo afrontar este problema si ha utilizado Power Query para combinarlas TODAS LAS TABLAS EXCEL el libro de trabajo. En caso de que seleccione tablas específicas para combinar, no os enfrentaréis a este problema.

Comprendamos la causa de este problema y cómo se corrige.

Cuando actualice una consulta, se retrocede y sigue todos los pasos que hicimos para combinar los datos.

Al paso donde hemos utilizado la fórmula = Excel.CurrentWorkbook (), Nos dio una lista de todas las tablas. La primera vez funcionó bien, ya que sólo había cuatro mesas.

Pero cuando actualice, hay cinco mesas en el libro, incluida la nueva tabla que Power Query insertó donde tenemos los datos combinados.

Por lo tanto, cada vez que actualice la consulta, aparte de las cuatro tablas de Excel que queremos combinar, también añade la tabla de consultas existente en los datos resultantes.

Esto se llama recursividad.

A continuación se explica cómo solucionar este problema.

Una vez inserte = Excel.CurrentWorkbook () en la barra de fórmulas de Power Query y pulse Intro, obtendrá una lista de tablas de Excel. Para asegurarse de que sólo puede combinar las tablas de la hoja de cálculo, debe filtrar de alguna manera sólo estas tablas que desea combinar y eliminar todo lo demás.

Estos son los pasos para asegurarse de que sólo se dispone de las tablas necesarias:

  1. Haga clic en el menú desplegable y pasar el cursor a Filtros de texto.
  2. Haga clic en la opción Contiene.
  3. En el cuadro de diálogo Filtro filtros, introduzca _Datos el campo situado junto a la opción “contiene”.
  4. Haga clic en Aceptar.

Es posible que no vea ningún cambio en los datos, pero si hace esto evitará que la tabla resultante no se añada de nuevo cuando se actualice la consulta.

Tenga en cuenta que en los pasos anteriores hemos utilizado “_Datos“Para filtrar a medida que designàvem las tablas de esta manera. Pero, ¿qué ocurre si sus tablas no se nombran de manera coherente. ¿Qué pasa si todos los nombres de las tablas son aleatorios y no tienen nada en común.

He aquí la manera de solucionarlo: utilice el filtro “no es igual” e introduzca el nombre de la consulta (que sería ConsolidatedData en nuestro ejemplo). De este modo, se asegurará de que todo siga igual y que se filtre la tabla de consultas resultante que se crea.

Aparte del hecho de que Power Query facilita todo este proceso de combinación de datos de diferentes hojas (o incluso de la misma hoja), otra ventaja de utilizarla es que la hace dinámica. Si añade más registros a alguna de las tablas y actualice la consulta de energía, proporciona automáticamente los datos combinados.

Nota importante: al ejemplo utilizado en este tutorial, las cabeceras eran las mismas. En caso de que las cabeceras sean diferentes, Power Query combinará y creará todas las columnas de la nueva tabla. Si los datos están disponibles para esta columna, se mostrarán, de lo contrario se mostrarán nulas.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *