Si utiliza Excel con frecuencia, probablemente se haya encontrado con estos problemas: necesita combinar varias hojas de trabajo de su departamento de ventas en una hoja maestra para simplificar el análisis. También es posible que desee combinar varias hojas de trabajo de ventas en un único libro. La función de copiar y pegar es eficaz para conjuntos de datos pequeños con estructuras idénticas. Pero para conjuntos de datos grandes, los errores son inevitables. Entonces, ¿qué herramientas o comandos puedes utilizar para combinar fácilmente archivos de Excel? Lee esta guía para averiguarlo.
Cómo fusionar hojas de Excel
¿Cómo combinarías las tres hojas Excel (Sheet 1, Sheet 2, y Sheet 3) que aparecen a continuación?
Excel no tiene una función integrada para combinar hojas. Puedes copiar y pegar manualmente las hojas o combinarlas utilizando Coupler.io. Se trata de una solución de automatización de informes que puede importar y combinar datos de más de 60 fuentes de datos de forma automática y programada.
Veamos cómo fusionar los datos de las tres hojas anteriores utilizando Coupler.io.
Paso 1: Recoger los datos
Seleccionamos el origen y el destino como Microsoft Excel en el siguiente formulario. Haga clic en Proceed para empezar.
En la configuración de origen, conecte su cuenta Microsoft, seleccione el archivo Excel y las 3 hojas que desea fusionar.
Paso 2: Transformar los datos
En este paso, verá una vista previa de los datos fusionados de las 3 hojas. Además, también puedes organizar y transformar los datos utilizando las siguientes opciones.
- Renombrar, reorganizar y ocultar columnas innecesarias
- Filtra y ordena tus datos
- Creación de nuevas columnas mediante fórmulas y cálculos personalizados
Una vez que los datos estén listos, vaya a la configuración de destino. Seleccione el archivo Excel y la hoja a la que desea exportar estos datos combinados.
También puedes enviar estos datos fusionados a otros destinos como BigQuery, Looker Studio, Google Sheets, etc. para su posterior análisis y visualización. Consulta más integraciones con Excel.
Paso 3: Programar la transferencia
Active el botón Automatic data refresh. Especifique los detalles de la transferencia de datos: intervalo, días de la semana, preferencias horarias y zona horaria.
Los datos combinados de las tres hojas tienen este aspecto.
Del mismo modo, también puedes combinar varias hojas de cálculo de Google en una sola.
Fusionar hojas de Excel sin duplicados
Excel no proporciona una función integrada para combinar hojas y eliminar duplicados en un solo clic. Sin embargo, puede copiar y pegar hojas o combinarlas mediante Coupler.io y, a continuación, utilizar el comando Remove Duplicate para eliminar las entradas duplicadas.
Por ejemplo, eliminemos las entradas duplicadas (es decir, las celdas rojas y azules) de la hoja Data I que aparece a continuación.
- Para eliminar los duplicados en los datos fusionados, vaya a la pestaña Data tab => Data Tools => Remove Duplicates.
- En la casilla Remove Duplicates, desmarque la opción My data has headers para eliminar los encabezados duplicados. A continuación, utilice la sección Columns para marcar o desmarcar las columnas que contienen valores duplicados (en nuestro caso, hemos marcado todas las columnas). Haga clic en OK.
- Haga clic en OK en el cuadro que le notifica el número de duplicados eliminados. Como puede ver en la imagen siguiente, se han eliminado 3 valores duplicados y ahora nos quedan valores únicos.
Cómo fusionar dos hojas Excel basándose en una columna
Puede hacerlo con dos métodos.
- Uso de Coupler.io
- Uso de Power Query
Veamos cómo funcionan estos métodos y cuál es el mejor para usted.
Método 1: Coupler.io
Supongamos que tiene dos hojas Excel en el mismo archivo – Finance Table y Sales Table. Desea fusionar los datos de estas dos tablas basándose en la columna ‘Product ID‘.
El origen y el destino están preseleccionados en el formulario. Haga clic en Proceed para proseguir.
En la configuración de origen, conecte su cuenta Microsoft, seleccione el archivo Excel y la hoja que desea combinar. En nuestro caso, elegimos la hoja ‘Finance Table’.
Haga clic en Connect one more source. Seleccione la aplicación de origen como Excel, conecte su cuenta y seleccione la segunda hoja “Sales Table” de su archivo Excel.
Después de conectar las hojas de Excel, pasa al siguiente paso. Aquí, verás dos opciones.
- Append – Combina datos de múltiples fuentes con estructuras similares donde las columnas coinciden por nombre.
- Join – Combina datos de múltiples fuentes con diferentes estructuras utilizando una columna de identificador único.
Seleccione unir y especifique la columna clave como ‘Product ID’ en ambas hojas.
Una vez que proceda, verá una vista previa de los datos unidos de las dos hojas de Excel basados en la columna clave “Product ID”.
Especifique dónde cargar los datos en la configuración de destino. Una vez que ejecute el importador, podrá ver los datos fusionados de dos hojas (Finance table y Sales table) basados en la columna ‘Product ID’.
Método 2: Power Query
Puede utilizar Power Query para combinar hojas de Excel basadas en una columna común. Power Query está disponible como complemento gratuito en Excel 2010 y 2013, mientras que es una función integrada a partir de Excel 2016.
Vamos a fusionar dos hojas (Finance Table y Sales Table) con una columna coincidente (“Product ID”) como se ve en las capturas de pantalla de abajo:
Paso 1: Crear conexiones Power Query
- Antes de fusionar nuestras hojas, necesitamos crear conexiones Power Query separadas para nuestras hojas Finance Table y Sales Table. Comencemos con la hoja “Sales Table“. Seleccione la celda que desea combinar. En su pestaña Data, vaya a la sección Get & Transform y haga clic en From Table.
- En el cuadro Create Table posterior, marca o desmarca My table has headers. Para este tutorial, lo dejaremos marcado porque nuestra Tabla tiene encabezados. A continuación, haz clic en Aceptar.
- En el Power Query Editor resultante, haga clic en Close and Load. Seleccione Close and Load To… en la lista desplegable.
- En el cuadro de diálogo Load to, seleccione Only Create Connection. Haga clic en Load.
- Se abrirá un cuadro de Workbook Queries en la parte derecha de su libro de trabajo con el nombre de su tabla/rango. Repita el mismo proceso para otras tablas (en este caso, “Finance Table”).
- Después de crear una conexión separada para la hoja “Finance Table”, el cuadro Workbook Queries debe contener ambas conexiones como se muestra a continuación:
Paso 2: Fusionar las hojas
Seleccione una hoja, vaya a Data => New Query => Combined Queries => Merge como se muestra en la siguiente captura de pantalla:
- En el cuadro Fusionar, añada Finance Table y Sales Table a la primera y segunda listas desplegables, respectivamente. En ambas tablas, seleccione la columna coincidente (en este caso, “Product ID”). Una vez seleccionada la columna coincidente, se volverá verde.
- En la Join kind box, deje la opción por defecto como está. Haga clic en OK.
Después de completar los pasos de Merge, el Power Query Editor añadirá una columna extra (“Sales Table”). Pero la columna extra sólo contiene “Table”. En los próximos pasos, le mostraremos cómo solucionarlo.
Paso 3: Rellenar la tabla de ventas
- Haga clic en la flecha de dos caras situada junto a la Sales Table. En el cuadro de diálogo subsiguiente, seleccione la(s) columna(s) que desea copiar de su segunda tabla (en este caso, Sales Table).
- En el cuadro de diálogo que se muestra a continuación, mantenga marcada la casilla Expand. Seleccione la(s) columna(s) que desea añadir de la segunda tabla. En nuestro caso, seleccionamos “Items” e “Amount ($)”. Desmarque la casilla Use original column name as prefix si no desea que la columna lleve como prefijo el nombre de la tabla de la que procede. Haz clic en OK.
Tras la fusión, la nueva tabla contendrá registros de ambas tablas.
Paso 4: Importar los datos combinados a Excel
- En el cuadro Power Query Editor, seleccione Close and Load To… de la lista desplegable Close and Load. En el cuadro Load To, haga clic en Table y elija si desea guardar la tabla como una New worksheet o como una Existing worksheet (nosotros guardamos la tabla como una hoja de cálculo nueva). A continuación, haga clic en Load.
En Excel, nuestra tabla combinada aparecerá así:
Combinar hojas de Excel de diferentes archivos utilizando VBA
Fusionar hojas de diferentes archivos puede ser bastante complicado, especialmente cuando se trata de grandes conjuntos de datos. Aquí es donde entran en juego las macros de Excel. Las macros son códigos que se utilizan para automatizar tareas en Excel. Visual Basic for Applications (VBA) es una forma de generar macros de Excel. Por ejemplo, vamos a utilizar el código VBA para combinar hojas de diferentes archivos de Excel.
Para facilitar el acceso, le aconsejamos que compile sus archivos Excel en la misma carpeta. A continuación se muestran instantáneas de los archivos Excel que vamos a combinar.
Paso 1: Insertar macro en el libro de trabajo
- En su nuevo libro de trabajo, vaya a la pestaña Developer tab => Code => Visual Basic para abrir el Visual Basic Editor (VBE). Alternativamente, puede utilizar Alt + F11 en Windows (o Fn + Opt + F11 si utiliza Mac), para abrir el VBE.
- Una vez que se abra el cuadro VBE, haga clic con el botón derecho del ratón en la hoja de cálculo que desea combinar e inserte el siguiente código:
Sub mergeFiles()
'Merges all files in a folder to a main file.
'Define variables:
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet
Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
'Allow the user to select multiple workbooks
tempFileDialog.AllowMultiSelect = True
numberOfFilesChosen = tempFileDialog.Show
'Loop through all selected workbooks
For i = 1 To tempFileDialog.SelectedItems.Count
'Open each workbook
Workbooks.Open tempFileDialog.SelectedItems(i)
Set sourceWorkbook = ActiveWorkbook
'Copy each worksheet to the end of the main workbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet
'Close the source workbook
sourceWorkbook.Close
Next i
End Sub
- Para ejecutar su macro, seleccione la Run/Sub Userform (F5) en la ventana de código como se muestra en la imagen de abajo. Alternativamente, pulse F5.
- Desde el explorador del escritorio, seleccione los archivos Excel desde los que desea combinar sus hojas. Para este tutorial, decidimos compilar nuestros archivos Excel (Finance Table y Sales Table) en la misma carpeta (Personal Library). A continuación, haz clic en Open.
Como puede ver en la imagen de abajo, hemos fusionado con éxito hojas Excel de diferentes archivos Excel (Finance Table y Sales Table) en un nuevo libro.
El uso de VBA para combinar dos hojas de Excel no es un paseo por el parque. Por lo tanto, siempre se puede utilizar Coupler.io para hacer esto. Le permite combinar datos de diferentes archivos sin codificación y organizar los datos sobre la marcha.
¿Cuál es la mejor opción?
Las macros de Excel son perfectas para combinar hojas de Excel de distintos archivos para usuarios que saben programar. Todo lo que hay que hacer es escribir el código adecuado y ejecutarlo cuando se solicite. Power Query también es muy útil para combinar hojas de Excel con columnas comunes.
Pero si escribir código no es lo tuyo y no quieres hacer malabares entre diferentes herramientas para combinar hojas de Excel, entonces Coupler.io es la solución todo-en-uno sin código para ti. Combina múltiples hojas de Excel y archivos de forma automática en un horario personalizado.
Por último, antes de elegir una opción de fusión en Excel, asegúrate de tener en cuenta la estructura, el contenido y el volumen de tus datos. Y ahora, ¡a Excel! ?