Consolidar en Excel – Cómo combinar varias hojas

Altavoz inteligente Echo Dot

Como consolidar en excel? Si busca maneras de conjuntar hojas, consolidar datos de múltiples hojas o sencillamente conjuntar múltiples hojas en una, ¡entonces vino al sitio acertado!

Entre los inconvenientes muy recurrentes que combaten los clientes de Excel es la oportunidad de fusionar múltiples hojas en solo una sin copiar y pegar.
En el presente artículo, va a ver 2 de los niveles más habituales: consolidación de datos numéricos (suma, recuento, promedio, etcétera.) y hojas combinadas (esto es, copia de datos de múltiples hojas en solo una). Consulte las distintas opciones ahora y vea cuál puede agradar sus pretensiones.

De qué manera consolidar datos de múltiples hojas en solo una hoja

seguramente la manera mucho más fácil y rápida de consolidar datos en Excel (que está en un libro de trabajo o en múltiples libros de trabajo) es a través de la función Consolidar dentro en Excel.

Consideremos el próximo ejemplo. Suponga que tiene múltiples reportes de las áreas de trabajo regionales de su compañía y quiere consolidar estos números en una hoja de cálculo profesora para tener un informe resumido con los totales de ventas de todos y cada uno de los modelos.

Como puede ver en la imagen ahora, las tres hojas que se consolidarán tienen una composición de datos afín, pero distintas números de filas y columnas:

consolidar en excel

Entonces, para consolidar los datos en solo una hoja de trabajo, realice los próximos pasos:

1. Organice adecuadamente los datos de origen. A fin de que la función de consolidación de Excel ande adecuadamente. Para esto, asegúrese de que:

  • Cada rango (grupo de datos) que quiere consolidar está en una hoja de trabajo separada.
  • No hay datos en el papel de trabajo donde tiene planeado hacer los datos afianzados.
  • Cada hoja de trabajo debe tener exactamente el mismo diseño y cada columna debe tener un encabezado que tenga dentro datos afines.
  • No hay filas o columnas en blanco en ninguna lista.

2. En su hoja profesora (donde se consolidarán los datos), realice click en la celda superior izquierda donde quiere que aparezcan los datos afianzados. entrar a la guía Datos > Consolidar.

herramienta de consolidación

Consejo agregada: Se aconseja consolidar los datos en una hoja de trabajo en blanco. Si su hoja profesora ahora tiene ciertos datos, asegúrese de que haya bastante espacio (filas y columnas en blanco) para contener los datos combinados.

3. Siguiendo los pasos precedentes, Excel va a abrir el cuadro de diálogo Consolidar. Configure los próximos cambios de consolidación:

  • En el campo Ocupación, elija entre las funcionalidades de resumen que quiere emplear para consolidar sus datos (recuento, promedio, máximo, mínimo, etcétera.). En este caso, elegimos Suma.
  • En el campo Referenciarealice click en el icono del cuadro de diálogo Contraer y escoja el rango en la primera hoja.
  • Entonces realice click en el botón Añadir a fin de que este rango se añada a Todas y cada una de las referencias. Repita este paso para todos y cada uno de los rangos que quiera consolidar.

Si una o ciertas hojas están en otro libro, lleve a cabo click en el botón Buscar para encontrar el libro esperado.

consolidacion de datos en excel

4. Todavía en el cuadro de diálogo Consolidar, configure los cambios de actualización eligiendo alguno de las próximas opciones:

  • Mira las cajas línea superior y/o columna izquierda si quiere que las etiquetas de fila y/o columna de los rangos de origen se copien para la consolidación.
  • elige la casilla Hacer links a datos de origen si quiere que los datos afianzados se actualicen de forma automática toda vez que cambien los datos de origen. En un caso así, Excel va a crear links a sus hojas de origen, tal como un esquema como se expone en la imagen ahora.

unir datos en excel

Al expandir un conjunto preciso (cliqueando en el símbolo mucho más) y después cliqueando en la celda con un valor preciso, aparecerá un link a los datos de origen en la barra de fórmulas.

Consolidar hojas de cálculo en Excel

Como puede ver, la función Consolidar de Excel es realmente útil para catalogar datos de múltiples hojas de trabajo. No obstante, tiene ciertas restricciones. Particularmente, solo marcha para valores numéricos y siempre y en todo momento sintetiza esos valores de una manera u otra (suma, cuenta, promedio, etcétera.)

Por otra parte, si quiere fusionar hojas de trabajo en Excel copiando sus datos, la opción de consolidación no es la opción mejor. Para conjuntar solo unas escasas hojas, posiblemente no necesite solamente que copiar y pegar a la vieja. Pero si quiere fusionar docenas de hojas de trabajo, los fallos al copiar y pegar de forma manual tienen la posibilidad de ser ineludibles. En un caso así, puede usar una de las próximas opciones para hacer de manera automática la consolidación de datos.

De qué manera fusionar múltiples hojas de Excel en solo una

Por norma general, hay otras 2 maneras de fusionar hojas de Excel en solo una, sin copiar y pegar de forma manual los datos. Son ellas:

  • Emplear una macro en VBA
  • Empleo de PowerQuery

Consolidar en Excel empleando un código VBA

Si es un usuario adelantado de Excel y se siente cómodo con las macros y VBA, puede conjuntar múltiples hojas de Excel en solo una empleando algún script de VBA.

Por poner un ejemplo, suponga que tiene tres hojas de trabajo que poseen datos que quiere consolidar en solo una hoja de trabajo, como se expone en la imagen ahora.

Consolidar datos en Excel con VBA

Una alternativa sería desarrollar una macro en VBA con la capacidad de copiar datos de todas y cada una de las hojas y pegarlos en solo una hoja, sosteniendo solo un encabezado.

Hay distintas maneras de realizar este trabajo utilizando VBA. Ahora se expone un código que puede amoldar a sus pretensiones.

Consejo: Si aún no sabe de qué manera trabajar con macros en VBA, consulte el tutorial: De qué manera grabar macros en Excel: una guía punto por punto

Sub Consolidar_Dados()

'Copia les dados da primeira Planilha
Plan1.Select
Range("A1").CurrentRegion.Select
Selection.Copy

'Cola les dados na Planilha Matriz a partir da célula A1
Plan4.Select
Range("A1").Select
ActiveSheet.Paste

'Copia les dados da segunda Planilha a partir da célula A2 para ignorar o cabeçalho
Plan2.Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Identifica a primeira linha vazia na Planilha Matriz y también cola les dados copiados da Segunda Planilha
Plan4.Select
linha = Sheets("Plan4").Range("A1048576").End(xlUp).Row + 1
Range("A" & linha).Select
ActiveSheet.Paste

'Copia les dados da Terceira Planilha a partir da célula A2 para ignorar o cabeçalho
Plan3.Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Identifica a primeira linha vazia na Planilha Matriz y también cola les dados copiados da Terceira Planilha
Plan4.Select
linha = Sheets("Plan4").Range("A1048576").End(xlUp).Row + 1
Range("A" & linha).Select
ActiveSheet.Paste

'Desenvolvido por exceleasy.com.br
End Sub

Cómo combinar varias hojas en Excel

Recuerde que a fin de que el código VBA ande adecuadamente, todas y cada una de las hojas de origen tienen que tener exactamente la misma composición, exactamente los mismos encabezados de columna y exactamente el mismo orden de columna.

Te puede interesar:  WhatsApp puede perder la copia de seguridad ilimitada de mensajes en Google Drive

Consolidar en Excel utilizando Power Query

Power Query es una tecnología muy vigorosa para conjuntar y refinar datos en Excel.

Esencial: Power Query se puede emplear como un complemento en Excel 2010 y 2013 y es una función dentro desde Excel 2016. Según su versión, ciertas imágenes tienen la posibilidad de tener un aspecto diferente (las imágenes usadas en este tutorial son de Excel 2019).

Para conjuntar datos de distintas hojas de trabajo empleando Power Query, debe tener los datos en formato de tabla de Excel (o por lo menos rangos con nombre). Si los datos no están en una tabla de Excel, el procedimiento que se expone ahora no va a funcionar.

Imaginemos que tiene 4 hojas de trabajo distintas: enero, febrero, marzo y abril.

Todas estas hojas de trabajo tiene dentro los datos estructurados en formato de tabla de forma consistente (o sea, los encabezados son exactamente los mismos).

unir hojas de trabajo en excel

En este momento prosiga los pasos ahora para conjuntar múltiples hojas de trabajo con tablas de Excel empleando Power Query:

1. entrar a la guía Datos > conjunto Conseguir y editar datos > Conseguir datos.

Power Query - Cómo unir datos

2. seleccionar opción De otras fuentes > Solicitud en blanco.

Power Query - cómo fusionar hojas

3. En el editor de consultas, ingrese la próxima fórmula en la barra de fórmulas: =Excel.LibroActual().

Consolidación de datos en Power Query

Tenga presente que las fórmulas de Power Query distinguen entre mayúsculas y minúsculas, con lo que debe utilizar el fórmula precisa como se mentó (en caso contrario conseguirá un fallo).

4. presione la tecla entrar. Esto mostrará todos y cada uno de los nombres de las tablas en todo el libro de trabajo (asimismo mostrará los rangos con nombre y/o las conexiones si hay en el libro de trabajo).

unir hojas de trabajo en Power Query

Opcional: En este caso de ejemplo, deseamos conjuntar todas y cada una de las tablas. Si solo quiere conjuntar tablas concretas, sencillamente lleve a cabo click en el ícono desplegable en el encabezado del nombre y escoja las que quiere conjuntar. De la misma forma, si ha nombrado rangos o conexiones, y solo quiere conjuntar tablas, asimismo puede remover esos rangos con nombre.

5. En la celda de encabezado Contenidorealice click en la flecha de 2 puntas como se expone en la próxima imagen:

Cómo fusionar hojas en Power Query

6. Escoja las columnas que quiere conjuntar. Si quiere conjuntar todas y cada una de las columnas, asegúrese de que (Elegir todas y cada una de las columnas) esté marcado.

7. Desmarque ‘Utilizar el nombre de la columna original como prefijo’. Para acabar, realice click en OK.

Consolidar en Excel con Power Query

Como resultado, va a tener las tablas combinadas en solo una tabla.

Combinar varias hojas en una sola en Excel

Tenga presente que la última columna (mucho más a la derecha) transporta el nombre de las tablas de Excel (Tabla 1, Tabla 2, Tabla 3, Tabla 4). Este es un identificador que nos comunica qué registro procede de qué tabla de Excel. Si ha nombrado sus tablas con un nombre personalizado, esos nombres aparecerán en este campo respectivo.

Exportación de datos de Power Query a una hoja

En este momento que tiene los datos combinados de todas y cada una de las hojas de trabajo en Power Query, puede cargarlos en Excel, como una exclusiva tabla en una exclusiva hoja de trabajo.

Para llevar a cabo esto, sencillamente prosiga los pasos ahora:

1. Pantalla de diálogo de Power Query, vaya a la pestañita Archivar y elegir:

  • Cerrar y cargar: Si elige utilizar exactamente el mismo libro de trabajo
  • Cerrar y cargar a: Si elige transladar los datos afianzados a otro libro de trabajo.

cómo fusionar varias hojas en una

Para este caso, escogemos emplear la opción cerrar y cargar. Tenga presente que Excel trasfiere los datos afianzados a una exclusiva hoja de cálculo en exactamente el mismo Libro de trabajo.

hoja de trabajo consolidada

Como se señaló previamente, Power Query es una herramienta muy vigorosa para conjuntar y refinar datos en Excel. Como resultado, es bastante complejo y necesita una extendida curva de estudio.

Si te resulta interesante reforzar en el tema, te aconsejamos leer el tutorial: Conjuntar datos de múltiples fuentes de datos (Power Query).

Consulte asimismo los próximos consejos de Excel:

Y tú, ¿conoces alguna otra forma de consolidar datos en Excel? ¡Deje su comentario ahora y prosiga siguiendo mucho más consejos de Excel en nuestro portal web!

Tommy Banks
Estaremos encantados de escuchar lo que piensas

Deje una respuesta

TecnoBreak | Ofertas y Reviews
Logo
Enable registration in settings - general
Shopping cart