David de Florida hace la pregunta de hoy:
Tengo dos libros de trabajo. Ambos tienen los mismos datos en la columna A, pero las columnas restantes son diferentes. ¿Cómo puedo fusionar esos dos libros de trabajo?
Le pregunté a David si es posible que un libro de trabajo tenga más registros que el otro. Y la respuesta es sí. Le pregunté a David si el campo clave solo aparece una vez en cada archivo. La respuesta también es sí. Hoy, resolveré esto con Power Query. Las herramientas de Power Query se encuentran en las versiones de Windows de Excel 2016+ en la sección Obtener y transformar de la pestaña Datos. Si tiene versiones de Windows de Excel 2010 o Excel 2013, puede descargar el complemento de Power Query para esas versiones.
Aquí está el libro de trabajo de David 1. Tiene Producto y luego tres columnas de datos.
Aquí está el libro de trabajo de David 2. Tiene Código de producto y luego otras columnas. En este ejemplo, hay productos adicionales en el libro2, pero las soluciones funcionarán si cualquiera de los libros tiene columnas adicionales.
Estos son los pasos:
-
Seleccione datos, obtener datos, de archivo, de libro de trabajo:
Cargar datos de un archivo - Busque el primer libro de trabajo y haga clic en Aceptar
- En el cuadro de diálogo Navegador, elija la hoja de trabajo de la izquierda. (Incluso si solo hay una hoja de trabajo, debe seleccionarla). Verá los datos a la derecha.
- En el cuadro de diálogo Navegador, abra el menú desplegable Cargar y elija Cargar en…
- Elija Solo crear una conexión y presione Aceptar.
-
Repita los pasos del 1 al 5 para el segundo libro.
Crea una conexión con el libro de trabajo Si ha realizado ambos libros de trabajo, debería ver dos conexiones en el Panel de consultas y conexiones a la derecha de la pantalla de Excel.
Conexiones a ambos libros de trabajo Continúe con los pasos para fusionar los libros de trabajo:
-
Datos, obtener datos, combinar consultas, fusionar.
Fusionar dos consultas con columnas diferentes - En el menú desplegable superior del cuadro de diálogo Combinar, elija la primera consulta.
- En el segundo menú desplegable del cuadro de diálogo Combinar, elija la segunda consulta.
- Haga clic en el encabezado Producto en la vista previa superior (este es el campo clave. Tenga en cuenta que puede realizar una selección múltiple de dos o más campos clave presionando Ctrl + clic)
- Haga clic en el encabezado Código de producto en la segunda vista previa.
-
Abra el Tipo de unión y elija Completo externo (todas las filas de ambos)
Pasos 8-12 ilustrados aquí -
Haga clic en Aceptar. La vista previa de datos no muestra las filas adicionales y solo muestra "Tabla" repetidamente en la última columna.
Esto no parece prometedor - Observe que hay un icono "Expandir" en el encabezado de DavidTwo. Haga clic en ese icono.
-
Opcional, pero siempre anulo la selección de "Usar nombre de columna original como prefijo". Haga clic en Aceptar.
Expanda los campos del libro de trabajo 2 Los resultados se muestran en esta vista previa:
Todos los registros de cualquier libro - En Power Query, use Inicio, Cerrar y Cargar.
Aquí está la característica hermosa: si los datos subyacentes en cualquiera de los libros de trabajo cambian, puede hacer clic en el ícono Actualizar para incorporar nuevos datos al libro de resultados.
Nota
El icono de Actualizar suele estar oculto. Arrastre el borde izquierdo del panel Consultas y conexiones hacia la izquierda para revelar el icono.
Ver video
Transcripción del video
Aprenda Excel del Podcast, Episodio 2216: Combine dos libros de trabajo basados en una columna común.
Oye, bienvenido de nuevo a Netcast, soy Bill Jelen. La pregunta de hoy es de David, quien estuvo en mi seminario en Melbourne, Florida, para el Capítulo de la Costa Espacial del IIA.
David tiene dos libros de trabajo diferentes donde la Columna A es común entre ambos. Entonces, aquí está el Libro de trabajo 1, aquí el Libro de trabajo 2: ambos tienen código de producto. Este tiene elementos que el primero no tiene, o viceversa, y David quiere combinar todas las columnas. Entonces, tenemos tres columnas aquí y cuatro columnas aquí. Puse ambos en el mismo libro de trabajo, en caso de que esté descargando el libro de trabajo para continuar. Tome cada uno de estos, muévalo a su propio libro de trabajo y guárdelo.
Muy bien, para combinar estos archivos, usaremos Power Query. Power Query está integrado en Excel 2016. Si tiene la versión de Windows 10 o 13, puede ir a Microsoft y descargar Power Query. Puede comenzar desde un nuevo libro de trabajo en blanco con una hoja de trabajo en blanco. Va a guardar este archivo: guardar como, ya sabe, tal vez Libro de trabajo, para mostrar los resultados de los archivos combinados .xlsx. ¿Bien? Y lo que vamos a hacer es hacer dos consultas. Vamos a ir a Datos, Obtener datos, Desde archivo, Desde libro de trabajo, y luego elegiremos el primer archivo. En una vista previa, seleccione la hoja que tiene sus datos y no tenemos que hacer nada con estos datos. Así que simplemente abra el cuadro de carga y elija Cargar en, Solo crear conexión, haga clic en Aceptar. Perfecto. Ahora, vamos a repetir eso para el segundo elemento: Datos, Desde archivo,Desde un libro de trabajo, elija DavidTwo, elija el nombre de la hoja y luego abra la carga, Cargar en, Solo crear una conexión. Verá aquí en este panel, tenemos ambas conexiones presentes. Bien.
Ahora el trabajo real: Datos, Obtener datos, Combinar consultas, Combinar, y luego, en el cuadro de diálogo Combinar, elija DavidOne, DavidTwo, y este siguiente paso es completamente intuitivo. Tienes que hacer esto. Elija la columna o columnas en común, es decir, Producto y Producto. Bien. Y luego, tenga mucho cuidado aquí con el tipo de combinación. Quiero todas las filas de ambos porque una puede tener una fila adicional y necesito ver eso, y luego hacemos clic en Aceptar. Bien. Y aquí está el resultado inicial. No parece que haya funcionado; no parece que haya agregado los elementos adicionales que estaban en el archivo 2. Y tenemos esta columna 5, ahora es nula. Voy a hacer clic derecho en la columna 5 y decir, Eliminar esa columna. Así que abra este icono de expansión y desmarque esta casilla para Usar el nombre de la columna original como prefijo y ¡BAM! funciona. Entonces, los elementos adicionales que estaban en el Archivo 2, que no están en el Archivo 1,aparecen.
Bien. Ahora, en el archivo de hoy, parece que esta columna Código de producto es mejor que esta columna Producto, porque tiene filas adicionales. Pero podría haber un día en el futuro en el que el Libro de trabajo 1 tenga cosas que el Libro de trabajo 2 no tiene. Así que los dejaré a los dos allí, y no me voy a deshacer de ningún valor nulo porque, como, aunque esta fila en la parte inferior parece ser completamente nula, podría haber en el futuro una situación en la que tenemos algunos nulos aquí porque falta algo. ¿Bien? Entonces, finalmente, cierre y cargue, y tenemos nuestras dieciséis filas.
Ahora, en el futuro, digamos que algo cambia. Muy bien, volveremos a uno de esos dos archivos y cambiaré la clase de Apple a 99, e incluso insertemos algo nuevo y guardemos este libro. Bien. Y luego, si queremos que nuestro archivo de combinación se actualice, ven aquí, ahora, cuidado, cuando haces esto por primera vez, no puedes ver el ícono Actualizar, debes tomar esta barra y arrastrarla . Y haremos Refresh, y 17 filas cargadas, aparece la sandía, la manzana cambia a 99, es algo hermoso. Ahora, oye, ¿quieres aprender sobre Power Query? Compre este libro de Ken Puls y Miguel Escobar, M es para (DATA) MONKEY. Te pondré al día.
Resumen de hoy: David de Florida tiene dos libros de trabajo que quiere combinar; ambos tienen los mismos campos en la Columna A, pero las otras columnas son todas diferentes; un libro de trabajo puede tener elementos adicionales que no están en el otro y David los quiere; no hay duplicados en ninguno de los archivos; vamos a usar Power query para resolver esto, así que comience con un nuevo libro en blanco en una hoja de trabajo en blanco; va a realizar tres consultas, primero una: Datos, Desde archivo, Libro de trabajo y luego Cargar solo en la conexión creada; lo mismo para el segundo libro de trabajo, y luego Datos, Obtener datos, Fusionar, seleccionar las dos conexiones, seleccionar la columna que es común en ambos, en mi caso, Producto, y luego desde el Tipo de unión, desea unirse por completo todo desde el Archivo 1, todo desde el Archivo 2. Y lo bueno es que si los datos subyacentes cambian,simplemente puede actualizar la consulta.
Para descargar el libro de trabajo del video de hoy, visite la URL en la descripción de YouTube.
Bueno, oye, quiero que me guste David por aparecer en mi seminario, quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo de Excel
Para descargar el archivo de Excel: combine-based-on-common-column.xlsx
Power Query es una herramienta asombrosa en Excel.
Pensamiento del día de Excel
Les he pedido a mis amigos de Excel Master sus consejos sobre Excel. Pensamiento de hoy para reflexionar:
"Siempre presione F4 cuando lea rango o matriz en una función"
Tanja Kuhn
