Combina 4 fogli - Suggerimenti per Excel

Excel combina diversi fogli di lavoro in un unico foglio di lavoro. Ogni foglio di lavoro potrebbe avere un numero diverso di record da un giorno all'altro, quindi le formule non sono la strada da percorrere. Invece, uno strumento poco conosciuto chiamato Power Query ti consentirà di unire i dati in modo semplice e rapido.

Guarda un video

Doug: come combinare quattro fogli in cui ognuno ha un diverso numero di righe?
Usa Power Query
Formatta ogni foglio di lavoro come una tabella con Ctrl + T
Rinomina le tabelle
Per ogni tabella, nuova query dalla tabella. Aggiungi una colonna personalizzata per Regione
Invece di Chiudi e carica, scegli Chiudi e carica per … Crea solo una connessione
Usa nuova query, Combina query, Aggiungi. 3 o più tabelle. Scegli le tabelle e aggiungi
Chiudi e carica ei dati vengono visualizzati in un nuovo foglio di lavoro
Per la tabella con una colonna aggiuntiva: i dati vengono visualizzati solo per i record di quel foglio
Per l'unica tabella in cui le colonne erano nell'ordine sbagliato: Power Query ha funzionato correttamente!
Facile da aggiornare in seguito

Trascrizione generata automaticamente

Impara Excel da Podcast Episode
2178 unione per i fogli di lavoro di oggi
domanda tramite YouTube da Doug ha questo
situazione abbiamo quattro fogli in cui ciascuno
foglio è una regione con dati di vendita e
il numero di record cambia mensilmente e
in questo momento Doug sta cercando di usare le formule
ma quando il numero di righe lo ha cambiato
diventa un incubo proprio così ho detto hey
Doug possiamo usare la query di alimentazione se lo hai
Excel 2010 o Excel 2013 è gratuito
scarica da Microsoft o è costruito
nel 2016 e in Office 365 si dice sì a tutti
giusto quindi ecco cosa abbiamo che abbiamo
quattro riporta la regione centrale ad est
regione la regione del sud e l'ovest
regione e ognuna ha una diversa
numero di record come qui nel sud
Regione abbiamo 72 record nell'est
regione 193 record e questo sta per
cambia a destra
ogni volta che eseguiamo questo rapporto avrà
un numero diverso di record ora ho
fatto alcune ipotesi qui prima che
non c'è una colonna chiamata centrale e
allora anche io sarò completamente malvagio
qui e prendo la regione sud che voglio
prova a fare un casino, prenderò il
colonna profitto taglialo e incollalo come fare
invertirli e poi va bene così noi
averne uno in cui le colonne sono invertite
e poi un altro in cui andremo
aggiungere una colonna percentuale di profitto lordo extra
quindi questo sarà il profitto diviso per
entrate in un mondo ideale, queste sono tutte
forma esattamente la stessa ma come ho imparato
recentemente stavo facendo un seminario giù in
North Carolina se non stanno bene
qualcuno ha avuto una situazione, beh sai
a metà anno le cose sono cambiate
e hanno aggiunto una nuova colonna o spostamento
colonne intorno a cui siamo stati davvero felici
vedere che la query di alimentazione è stata in grado di affrontare
con questo tutto bene quindi andremo a prendere
ciascuno di questi rapporti e trasformalo in
un formato tabella ufficiale come tabella così
questo è il tè di controllo oppure potresti usare un file
intervallo di nomi per me control t è il
modo più semplice per andare e cosa fanno qui
chiamerò questo tavolo quello che vado
rinominalo per chiamarlo centrale e
quindi andiamo al controllo est T clic su OK
e questo ora si chiamerà East hey
in un podcast precedente ho mostrato come se
questi sono stati quattro file separati che abbiamo
potrebbe aver usato power query solo per
combinare file ma non funziona quando
sono quattro fogli di lavoro separati o quattro
nello stesso libro così bene ci siamo
vai e poi
come questo controllo un po 'noioso
impostare questo la prima volta, ma ragazzo
ci sarà fantastico
ogni volta che devi aggiornarlo in seguito
quindi faremo è andare
per scegliere questo primo tavolo centrale
regione e se sei nel 2010 o 2013 e
la query di alimentazione scaricata lo farai
avrà la sua scheda ma in 16 pollici
Excel 2016 sta effettivamente ottenendo
trasformato che è il secondo gruppo in
Office 365 si sta ora trasformando
che è il primo gruppo e quindi lo siamo
dirò che lo avrebbero creato
dati da una tabella o un intervallo, va bene e
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
dicendo quella domanda bene grazie
per passare ci vediamo la prossima volta
per un altro cast netto da

Download file

Scarica il file di esempio qui: Podcast2178.xlsm

Articoli interessanti...