Kombinirajte 4 lista - Excel savjeti

Excel kombinira nekoliko radnih listova u jedan radni list. Svaki radni list može imati različit broj zapisa iz dana u dan, tako da formule nisu pravi put. Umjesto toga, malo poznati alat pod nazivom Power Query omogućit će vam jednostavno i brzo spajanje podataka.

Gledaj video

Doug: Kako kombinirati četiri lista gdje svaki ima različit broj redaka?
Koristite Power Query
Oblikujte svaki radni list kao tablicu pomoću Ctrl + T
Preimenujte tablice
Za svaku tablicu novi upit Iz tablice. Dodajte prilagođeni stupac za Regija
Umjesto Zatvori i učitaj, odaberite Zatvori i učitaj za … Samo izradi vezu
Koristite novi upit, kombinirajte upit, dodajte. 3 ili više tablica. Odaberite Tablice i Dodaj
Zatvori i učitaj i podaci se pojavljuju na novom radnom listu
Za jednu tablicu s dodatnim stupcem: podaci se prikazuju samo za zapise tog lista
Za jednu tablicu u kojoj su stupci bili u pogrešnom redoslijedu: Power Query je ispravno funkcionirao!
Lako se kasnije osvježiti

Automatski generirani prijepis

Naučite Excel iz epizode Podcast
2178 spajanje današnjih radnih listova
pitanje putem YouTubea od Douga ima ovo
situacija imamo četiri lista gdje svaki
list je regija s podacima o prodaji i
broj zapisa mijenja se mjesečno i
trenutno Doug pokušava koristiti formule
ali kad je to promijenio broj redaka
postaje noćna mora, pa sam rekla hej
Doug možemo li koristiti upit snage ako imate
Excel 2010 ili Excel 2013 besplatan je
preuzmite s Microsofta ili je izgrađen
u 2016. i Office 365 kaže da sve
točno, evo što imamo
četiri izvješća središnja regija istok
regija južna regija i zapad
regija i svaka ima drugačiju
broj zapisa kao ovdje na Jugu
Regija imamo 72 zapisa na istoku
regija 193 zapisa i ovo će
promijeni udesno
svaki put kad pokrenemo ovo izvješće
sada imam drugačiji broj zapisa
ovdje prvo napravio neke pretpostavke da
ne postoji kolona koja se naziva središnja i
tada ću također biti potpuno zao
ovdje i uzmi Južnu regiju koju želim
probaj zeznuti ja ću uzeti
stupac dobiti izrežite ga i zalijepite kako treba
preokrenuti ih i onda u redu pa mi
imaju jedan gdje su stupci obrnuti
a onda još jedan kamo ćemo
dodajte dodatni stupac postotaka bruto dobiti
pa će ovo biti dobit podijeljena sa
prihod u idealnom svijetu to su sve
oblikovana potpuno isto, ali kako sam naučila
nedavno sam održavao seminar dolje u
Sjeverna Karolina ako nisu sve u redu
netko je imao situaciju dobro znate
na pola godine stvari su se promijenile
i dodali su novi stupac ili potez
kolone oko nas bili smo zaista sretni
vidi da je upit snage mogao riješiti
s ovim sve u redu pa ćemo uzeti
svako od ovih izvješća i napravite ga
službeni format tablice kao stol
to je kontrolni čaj ili biste mogli koristiti a
raspon imena za mene kontrola t je
lakši put i što rade ovdje
da li će ovaj stol nazvati onim koji ću nazvati
preimenovati ovo da se zove centralno i
zatim idemo na Istočnu kontrolu T kliknite U redu
i ovo će se sada zvati Istok, hej
na ranijem podcastu pokazao sam kako ako
to su bile četiri zasebne datoteke
mogao upotrijebiti upit snage samo za
kombinirati datoteke, ali to ne radi kad
to su četiri odvojena ili četiri radna lista
u istoj knjizi tako dobro tamo mi
idi i onda
poput ove kontrole-t pomalo zamorne
postavi ovo prvi put, ali dječak
bit će super
svaki put kad ovo kasnije morate ažurirati
pa ćemo ići
odabrati ovaj prvi središnji stol
i ako ste u 2010 ili 2013 i
preuzeto napajanje upit ćete
imat će svoju karticu, ali za 16 in
Excel 2016 zapravo dobiva
transformirana što je druga skupina u
Office 365 sada se transformira
koja je prva grupa i tako smo
reći će da će ovo stvoriti
podaci iz tablice ili raspona u redu i
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
govoreći to pitanje dobro hvala
za svratak vidimo se sljedeći put
za još jedan neto ulog od

Preuzmi datoteku

Preuzmite uzorak datoteke ovdje: Podcast2178.xlsm

Zanimljivi članci...