Recientemente recibí una pregunta de un lector sobre la combinación de varias hojas de trabajo en el mismo libro de trabajo en una sola hoja de trabajo.
Le pedí que usara Power Query para combinar diferentes hojas, pero luego me di cuenta de 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 tabla usando Power Query.
Debajo de un video donde muestro cómo combinar datos de múltiples hojas / tablas usando Power Query:
A continuación, encontrará instrucciones escritas sobre cómo combinar varias hojas (en caso de que prefiera texto escrito en lugar de video).
Nota: Power Query se puede utilizar como un complemento en Excel 2010 y 2013, y es una función incorporada desde Excel 2016 en adelante. Según su versión, algunas imágenes pueden verse diferentes (las capturas de imágenes utilizadas en este tutorial son de Excel 2016).
Combinar datos de varias hojas de trabajo con Power Query
Al combinar datos de diferentes hojas con Power Query, es necesario tener los datos en una tabla de Excel (o al menos en rangos con nombre). Si los datos no están en una tabla de Excel, el método que se muestra aquí no funcionaría.
Suponga que tiene cuatro hojas diferentes: Este, Oeste, Norte y Sur.
Cada una de estas hojas de trabajo tiene los datos en una tabla de Excel y la estructura de la tabla es consistente (es decir, los encabezados son los mismos).
Haga clic aquí para descargar los datos y seguir adelante.
Este tipo de datos es extremadamente fácil de combinar usando Power Query (que funciona muy bien con datos en Excel Table).
Para que esta técnica funcione mejor, es mejor tener nombres para las tablas de Excel (también funciona sin ella, pero es más fácil de usar cuando se nombran las tablas).
Le he dado a las tablas los siguientes nombres: East_Data, West_Data, North_Data y South_Data.
Estos son los pasos para combinar varias hojas de trabajo con tablas de Excel usando Power Query:
- Vaya a la pestaña Datos.
- En el grupo Obtener y transformar datos, haga clic en la opción "Obtener datos".
- Vaya a la opción "De otras fuentes".
- Haga clic en la opción "Consulta en blanco". Esto abrirá el editor de Power Query.
- En el editor de consultas, escriba la siguiente fórmula 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 usar la fórmula exacta como se menciona (de lo contrario, obtendrá un error).
- Presiona la tecla Enter. Esto le mostrará todos los nombres de las tablas en todo el libro de trabajo (también le mostrará los rangos y / o conexiones nombrados en caso de que exista en el libro de trabajo).
- [Paso opcional] En este ejemplo, quiero combinar todas las tablas. Si solo desea combinar tablas específicas de Excel, puede hacer clic en el ícono desplegable en el encabezado del nombre y seleccionar las que desea combinar. De manera similar, si ha nombrado rangos o conexiones, y solo desea combinar tablas, también puede eliminar esos rangos nombrados.
- En la celda del encabezado Contenido, haga clic en la flecha de doble punta.
- Seleccione las columnas que desea combinar. Si desea combinar todas las columnas, asegúrese de que (Seleccionar todas las columnas) esté marcado.
- Desmarca la opción "Usar el nombre de la columna original como prefijo".
- Haga clic en Aceptar.
Los pasos anteriores combinarían los datos de todas las hojas de trabajo en una sola tabla.
Si observa de cerca, encontrará que la última columna (más a la derecha) tiene el nombre de las tablas de Excel (East_Data, West_Data, North_Data y South_Data). Este es un identificador que nos dice qué registro proviene de qué tabla de Excel. Esta es también la razón por la que dije que es mejor tener nombres descriptivos para las tablas de Excel.
A continuación, se muestran algunas modificaciones que puede realizar en los datos combinados en Power Query:
- Arrastre y coloque la columna Nombre al principio.
- Elimina "_Data" de la columna del nombre (de modo que te quedes con Este, Oeste, Norte y Sur en la columna del nombre). Para hacer esto, haga clic con el botón derecho en el encabezado Nombre y haga clic en Reemplazar valores. En el cuadro de diálogo Reemplazar valores, reemplace _Data con un espacio en blanco.
- Cambie la columna Datos para mostrar solo las fechas (y no la hora). Para hacer esto, haga clic en el encabezado de la columna Fecha, vaya a la pestaña "Transformar" y cambie el Tipo de datos a Fecha.
- 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 en Excel, como una nueva tabla en una nueva hoja de trabajo.
Para hacer esto. siga los pasos a continuación:
- Haga clic en la pestaña "Archivo".
- Haga clic en Cerrar y cargar en.
- En el cuadro de diálogo Importar datos, seleccione las opciones Tabla y Nueva hoja de trabajo.
- Haga clic en Aceptar.
Los pasos anteriores combinarían datos de todas las hojas de trabajo y le darían esos datos combinados en una nueva hoja de trabajo.
Un problema que debe resolver al usar este método
En caso de que haya utilizado el método anterior para combinar todas las tablas en el libro de trabajo, es probable que se enfrente a un problema.
Vea 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. Actualice de nuevo y cambiará a 3910.
Aqui esta el problema.
Cada vez que actualiza la consulta, agrega todos los registros de los datos originales a los datos combinados.
Nota: enfrentará este problema solo si ha utilizado Power Query para combinar TODAS LAS MESAS EXCEL en el libro de trabajo. En caso de que haya seleccionado tablas específicas para combinar, no enfrentará este problema.Entendamos la causa de este problema y cómo corregirlo.
Cuando actualiza una consulta, retrocede y sigue todos los pasos que tomamos para combinar los datos.
En el paso donde usamos la fórmula = Excel.CurrentWorkbook (), nos dio una lista de todas las tablas. Esto funcionó bien la primera vez ya que solo había cuatro mesas.
Pero cuando actualiza, hay cinco tablas en el libro de trabajo, incluida la nueva tabla que Power Query insertó donde tenemos los datos combinados.
Entonces, cada vez que actualiza la consulta, además de las cuatro tablas de Excel que queremos combinar, también agrega la tabla de consulta existente a los datos resultantes.
A esto se le llama recursividad.
A continuación se explica cómo solucionar este problema.
Una vez que inserte = Excel.CurrentWorkbook () en la barra de fórmulas de Power Query y presione enter, obtendrá una lista de tablas de Excel. Para asegurarse de que solo puede combinar las tablas de la hoja de trabajo, debe filtrar de alguna manera solo estas tablas que desea combinar y eliminar todo lo demás.
Estos son los pasos para asegurarse de que solo tenga las tablas requeridas:
- Haga clic en el menú desplegable y coloque el cursor sobre Filtros de texto.
- Haga clic en la opción Contiene.
- En el cuadro de diálogo Filtrar filas, ingrese _Data en el campo junto a la opción "contiene".
- Haga clic en Aceptar.
Es posible que no vea ningún cambio en los datos, pero hacerlo evitará que la tabla resultante se vuelva a agregar cuando se actualice la consulta.
Tenga en cuenta que en los pasos anteriores hemos utilizado "_Datos”Para filtrar como nombramos las tablas de esa manera. Pero, ¿qué pasa si sus tablas no se nombran de forma coherente? ¿Qué pasa si todos los nombres de las tablas son aleatorios y no tienen nada en común?
Esta es la forma de resolver esto: use el filtro "no es igual" e ingrese el nombre de la consulta (que sería ConsolidatedData en nuestro ejemplo). Esto asegurará que todo siga igual y que la tabla de consulta resultante que se crea se filtre.
Aparte del hecho de que Power Query hace que todo este proceso de combinar datos de diferentes hojas (o incluso de la misma hoja) sea bastante fácil, otro beneficio de usarlo es que lo hace dinámico. Si agrega más registros a cualquiera de las tablas y actualiza Power Query, automáticamente le proporcionará los datos combinados.Nota importante: en el ejemplo utilizado en este tutorial, los encabezados eran los mismos. En caso de que los encabezados sean diferentes, Power Query combinará y creará todas las columnas en la nueva tabla. Si los datos están disponibles para esa columna, se mostrarán, de lo contrario, se mostrarán nulos.
También le pueden gustar los siguientes tutoriales de Power Query:
- Combine datos de varios libros de trabajo en Excel (con Power Query).
- Cómo anular la dinámica de datos en Excel usando Power Query (también conocido como Get & Transform)
- Obtenga una lista de nombres de archivos de carpetas y subcarpetas (usando Power Query)
- Combinar tablas en Excel con Power Query.
- Cómo comparar dos hojas / archivos de Excel
