Cómo combinar datos de varios libros de Excel. Digamos que tiene una carpeta llena de archivos de Excel. Cada uno tiene una hoja de trabajo con un número diferente de filas. Puede importar todos los archivos de la carpeta a un solo libro de Excel.
Ver video
- Revisando el podcast Clean Data with Power Query # 2037
- Power Query ahora puede combinar todos los archivos de Excel en una carpeta.
- Mejorado: eliminan automáticamente los encabezados de todos menos del primer archivo.
- Usted elige qué archivo usar como archivo de muestra.
- Elija qué hoja de trabajo, tabla, rango con nombre importar
- Utilice el editor de consultas para realizar cualquier transformación.
- Cerrar y cargar para combinar todos los archivos
- Más tarde, actualice la consulta para que se actualice
Transcripción del video
Aprenda Excel de Podcast, Episodio 2077: Combinar libros de trabajo
Oye, bienvenido de nuevo al netcast, soy Bill Jelen. Hace solo 40 episodios, hablando de limpiar datos con Power Query, cómo tomar un montón de archivos CSV y traerlos a un solo archivo de Excel. Bueno, esta característica acaba de mejorar dramáticamente.
Así que hoy tengo aquí una carpeta y en esta carpeta hay una serie completa de archivos Excel mensuales, 22 de ellos durante casi dos años. Necesito combinar todos estos archivos. Ahora, todos estos tienen el mismo diseño de columna, ¿de acuerdo? Entonces todos tienen 9 columnas, todos tienen los mismos encabezados y así sucesivamente, está bien. Entonces vamos a combinar todos estos sin una macro.
Si está en Excel 2016, irá a la pestaña Datos para obtener y transformar datos. Si está en Excel 2010 o 2013, saldrá y descargará un gran producto de Microsoft llamado Power Query. Puedes descargarlo todo e instalarlo en menos de un minuto, de acuerdo.
Entonces, vamos a venir aquí para obtener datos, de un archivo, pero no de un solo libro de trabajo. Queremos obtenerlo de una carpeta como esta. Muy bien, aquí están. Le están pidiendo que busque la carpeta, pero solo voy a hacer clic allí y pegar Ctrl + V porque pegué el directorio, haga clic en Aceptar. Entonces me mostraron una vista previa de los datos que tengo. Abro este pequeño menú desplegable Combinar aquí y elijo Combinar y editar. De acuerdo, y luego el paso aquí es que vamos a elegir un archivo que sea un buen archivo representativo, de acuerdo, y ellos simplemente eligen el primero que está alfabéticamente. No importa cuál elijas. Y luego esta Consulta de muestra que estamos construyendo desde el primer archivo que elegimos, en este caso, quiero Sheet1.Si tiene varias hojas, puede decir Hoja2 o la hoja llamada Declaración de ingresos o el Rango de nombres o una tabla. Y me mostraron cómo se ve y hago clic en Aceptar.
Ahora, están sucediendo muchas cosas en esta pantalla. Lo que realmente han hecho aquí es que crearon una Consulta de muestra, ahh Consulta de muestra, y luego la crearon como una función y ejecutaron esa función en todo lo que hay en la carpeta. Añaden una nueva columna que me dice de dónde vino el archivo y, en este caso particular, ya sé de dónde vino porque tenemos una fecha completada aquí. Así que no necesito eso, voy a hacer clic derecho y Eliminar eso. Y tal vez haya algunos datos allí que no necesito. Tal vez estos datos vayan a un cliente, así que no quiero mostrar el beneficio para poder eliminar esa columna e incluso eliminar la columna Costo, y tal vez haya algunas cosas molestas como, ya sabes, usaron encabezados incorrectos y yo desea cambiar el nombre de estos encabezados. Así que haga clic derecho y cambie el nombre, lo llamaremos CANTIDAD de Cantidad.Tal vez las cosas estén en la secuencia incorrecta, tal vez quiera tomar este artículo y moverlo a otro lugar, ¿de acuerdo? Solo todas estas pequeñas cosas molestas que posiblemente tenga y, por supuesto, aquí, están haciendo todos los pasos aplicados y luego puede continuar haciendo cualquier cosa de Power Query que necesite hacer. Pero en este caso, es simple. Solo quiero ingresar los datos. Así que voy a Cerrar y Cargar, pasan unos segundos y ahí está. Tengo mi final. Ordenemos esto aquí por fecha; ya ves, vamos desde enero de 2018 hasta octubre de 2019. Qué genial es eso.Está haciendo todos los pasos aplicados y luego puede continuar haciendo cualquier cosa de Power Query que necesite hacer. Pero en este caso, es simple. Solo quiero ingresar los datos. Así que voy a Cerrar y Cargar, pasan unos segundos y ahí está. Tengo mi final. Ordenemos esto aquí por fecha; ya ves, vamos desde enero de 2018 hasta octubre de 2019. Qué genial es eso.Está haciendo todos los pasos aplicados y luego puede continuar haciendo cualquier cosa de Power Query que necesite hacer. Pero en este caso, es simple. Solo quiero ingresar los datos. Así que voy a Cerrar y Cargar, pasan unos segundos y ahí está. Tengo mi final. Ordenemos esto aquí por fecha; ya ves, vamos desde enero de 2018 hasta octubre de 2019. Qué genial es eso.
Ahora, aquí está lo asombroso, asombroso de esto. Digamos que algo cambia en nuestra carpeta. Así que de vuelta aquí en los archivos del mes, obtengo dos archivos nuevos, Pegar, y ahora hay dos registros más aquí, dos archivos de Excel más que tengo que combinar. Simplemente regrese a mi archivo de Excel que actualmente tiene 516 filas, busque MonthFiles y haga clic en el botón Actualizar. Y que rápido ahora tengo los nuevos registros de noviembre y diciembre. Power Query es una nueva y sorprendente herramienta gratuita para Excel 2010, gratis para Excel 2013. Fue hermoso cuando podían combinar archivos CSV pero ahora que pueden combinar archivos Excel también, simplemente fenomenal.
Entonces, actualicé mi libro, Power Excel con, la edición 2017, incluye Power Query, Power BI y todas las nuevas herramientas. Echale un vistazo. Haga clic en esa "i" en la esquina superior derecha.
Muy bien, resumen de este episodio: estamos revisando los datos limpios con el podcast de Power Query # 2037, donde combinamos varios archivos CSV. Ahora podemos usar esa misma herramienta, una versión mejorada de esa herramienta, para combinar todos los archivos de Excel en una carpeta. Otra mejora, el cambio de 2037, eliminan automáticamente los encabezados de todos los archivos excepto el primero y promueven esos encabezados en el primer archivo. Usted elige qué archivo usar como archivo de muestra. En mi caso, usé enero. Trabajo: elija qué hoja de trabajo, tabla o rango con nombre importar y luego realice cualquier transformación en el editor de consultas. Hacemos clic en Cerrar y Cargar, combinará todos los archivos. Y luego, si se colocan más archivos en la carpeta o se quitan algunos archivos o se modifican algunos archivos, simplemente actualice la consulta y volverá a realizar todos esos pasos. Power Query,una herramienta absolutamente asombrosa.
Quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2077.xlsm
