Excel combina varias hojas de trabajo en una sola hoja de trabajo. Cada hoja de trabajo puede tener un número diferente de registros de un día a otro, por lo que las fórmulas no son el camino a seguir. En cambio, una herramienta poco conocida llamada Power Query le permitirá fusionar los datos de manera simple y rápida.
Ver video
- Doug: ¿Cómo combinar cuatro hojas donde cada una tiene un número diferente de filas?
- Usar Power Query
- Formatee cada hoja de trabajo como una tabla con Ctrl + T
- Cambiar el nombre de las tablas
- Para cada tabla, nueva consulta De tabla. Agregar una columna personalizada para la región
- En lugar de Cerrar y cargar, elija Cerrar y cargar a… Solo crear una conexión
- Usar nueva consulta, combinar consulta, agregar. 3 o más tablas. Elija las tablas y agregue
- Cierre y cargue y los datos aparecen en una nueva hoja de trabajo
- Para la única tabla con columna adicional: los datos se muestran solo para los registros de esa hoja
- Para la única tabla en la que las columnas estaban en el orden incorrecto: ¡Power Query funcionó correctamente!
- Fácil de actualizar más tarde
Transcripción generada automáticamente
- Aprenda Excel del episodio de podcast
- 2178 fusionar para las hojas de trabajo de hoy
- pregunta a través de YouTube de Doug tiene esto
- situación tenemos cuatro hojas donde cada
- La hoja es una región con datos de ventas y
- el número de registros cambia mensualmente y
- ahora mismo Doug está tratando de usar fórmulas
- pero cuando el número de filas cambió eso
- se convierte en una pesadilla, así que dije hey
- Doug, ¿podemos usar Power query si tienes
- Excel 2010 o Excel 2013 es gratis
- descargar de Microsoft o está construido
- en 2016 y Office 365 dice que sí a todos
- bien, esto es lo que tenemos, tenemos
- cuatro informes la región central el este
- región la región sur y el oeste
- región y cada una tiene una
- número de registros como aquí en el sur
- Región tenemos 72 registros en el este
- región 193 registros y esto va a
- cambiar a la derecha
- cada vez que ejecutemos este informe tendremos
- un número diferente de registros ahora que tengo
- hizo algunas suposiciones aquí primero que
- no hay una columna llamada central y
- entonces también voy a ser completamente malvado
- aquí y tomar la Región Sur que quiero
- Intenta arruinarlo, voy a tomar el
- columna de ganancias córtelo y péguelo cómo
- revertir esos y luego está bien, así que
- tener uno donde las columnas estén invertidas
- y luego otro donde vamos
- agregue un porcentaje de ganancia bruta de columna adicional
- así que esto será la ganancia dividida por
- ingresos en un mundo ideal estos son todos
- forma exactamente la misma pero como aprendí
- Recientemente estuve haciendo un seminario en
- Carolina del Norte si no están bien
- alguien tuvo una situación bien sabes
- a mitad de año las cosas cambiaron
- y agregaron una nueva columna o se movieron
- columnas alrededor nos alegramos mucho de
- ver que la consulta de energía pudo hacer frente
- con esto bien, así que vamos a tomar
- cada uno de estos informes y convertirlo en
- un formato de tabla oficial como tabla para
- eso es té de control o podrías usar un
- rango de nombres para mí control t es el
- forma más fácil de ir y lo que hacen aquí
- ¿Llaman a esta mesa?
- cambiar el nombre de esto para que se llame central y
- luego pasamos al control Este T haga clic en Aceptar
- y esto se llamará este ahora, oye
- en un podcast anterior mostré cómo si
- estos han sido cuatro archivos separados que
- podría haber usado Power query solo para
- combinar archivos pero eso no funciona cuando
- son cuatro hojas de trabajo separadas o cuatro
- en el mismo libro tan bien ahí estamos
- ve y luego
- como este control-t un poco tedioso para
- configuré esto la primera vez pero chico
- va a ser genial
- cada vez que tengas que actualizar esto más tarde
- así que vamos a hacer es ir
- para elegir esta primera mesa central
- región y si estás en 2010 o 2013 y
- consulta de energía descargada lo vas a hacer
- tendrá su propia pestaña, pero en 16 pulgadas
- Excel 2016 está obteniendo
- transformado que es el segundo grupo en
- Office 365 ahora se está transformando
- que es el primer grupo y entonces estamos
- diré que iban a crear esto
- datos de una tabla o rango bien y
- there is our data now we don't have a
- region field and the combined files
- would have added the region field so in
- this case I'm just gonna add a column a
- new custom call the headings gonna be
- region and this one is going to be what
- was this central right like that
- click OK alright now here's the
- important part when we're done this with
- this we're gonna go home not choose
- close and load we're gonna open the
- drop-down close and load to only create
- a connection click OK
- perfect we have our connection only now
- the next thing we have to do is repeat
- these steps for the next three regions
- and now that would be really a bit
- boring to you so let's just speed up the
- video to 10x for this
- alright there we are for connections set
- up now here's where we're going to do
- the magic I'm gonna insert a new blank
- worksheet and I'm gonna say get data
- combine queries and I want to append two
- queries from this workbook and I'm gonna
- say three or more tables and the
- available tables are Central through
- West click Add BAM click OK and then we
- can close and load and what we have here
- is we have a superset of all of the
- records in all of the tables all right
- and where we tried to screw it up where
- I purposely tried to screw up by
- reversing cost of goods sold and profit
- down in what was that that was Central
- East South in the South Region I'll just
- go check those right and it looks like
- yeah generally feels right they used the
- heading to figure it out because the
- profit is always higher than cost of
- goods sold and so that worked and then
- down here in the West where we added
- gross profit percent we actually get
- that data for the tables that had it and
- for the tables that didn't have it we
- just get null which is perfect alright
- now duck
- here's what you're gonna do so the next
- time that you have some more data and
- I'll just let's create some some extra
- records here we'll just add some ABC
- with a date of today and all retail and
- it's called Doug's new records and just
- some garbage out here let's just put in
- a hundred all the way across in the
- interest of time okay so now because
- this is a table the table automatically
- expands to the new records which is
- beautiful had they been named range I
- would have had a redefine that's why I
- really like the table instead of the
- name range but we come back here to the
- resulting workbook with 563 rows loaded
- and I click refresh
- and bam now I have 572 Rose loaded
- including let's see if we can find them
- in here
- Doug's new records right there at the
- end of the South Region
- isn't that just an awesome awesome way
- to go yes it definitely takes longer to
- set up the first day we're up to seven
- minutes already if I hadn't sped that up
- to 10x but once it's set up now life is
- gonna be super super easy from here on
- out way this is where I usually promote
- my own book but no this time let's talk
- about this awesome book Emma's for data
- monkey by Ken polls in Miguel Escobar
- everything I learned about power query I
- learned from this book look at the eye
- on the top right hand corner for more
- information about that book all right
- wrap up topics in this episode Doug how
- to combine four sheets where each sheet
- has a different number of rows we can
- use power query make sure to format each
- worksheet as a table with ctrl T or use
- named ranges but I prefer ctrl T rename
- the tables from each table choose new
- query from table add a custom column for
- a region and then instead of close and
- load choose close and load to only
- create a connection do that for all four
- queries and then new query combined
- query append choose three or more tables
- choose the tables and click Add
- now some older versions of power query
- you couldn't do three or more tables you
- have to do two and then do another query
- to add the third one and then do another
- query to add the fourth one either way
- it would be more hassle that way I'm
- glad that they added the three or more
- tables close and load this time close
- and load to the worksheet and and then
- later on if you add more data to any of
- the four tables just go back to your
- query and click refresh and you're good
- to go
- power query and amazing new feature from
- Microsoft I love it I thank Doug for
- diciendo esa pregunta bien gracias
- por pasarte nos vemos la próxima vez
- para otra red lanzada desde
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2178.xlsm
