Združite 4 liste - Excel Nasveti

Excel združi več delovnih listov v en sam delovni list. Vsak delovni list ima lahko različno število zapisov iz dneva v dan, zato formule niso prava pot. Namesto tega vam bo malo znano orodje Power Query omogočilo preprosto in hitro združitev podatkov.

Oglejte si video

Doug: Kako združiti štiri liste, kjer ima vsak različno število vrstic?
Uporabite Power Query
Vsak delovni list oblikujte kot tabelo s tipkama Ctrl + T
Preimenujte tabele
Za vsako tabelo novo poizvedbo Iz tabele. Dodajte stolpec po meri za Regija
Namesto Zapri in naloži izberite Zapri in naloži v … Ustvari samo povezavo
Uporabite novo poizvedbo, kombiniraj poizvedbo, dodaj. 3 ali več tabel. Izberite Tabele in Dodaj
Zapri in naloži in podatki se prikažejo na novem delovnem listu
Za eno tabelo z dodatnim stolpcem: podatki se prikažejo samo za zapise tega lista
Za eno tabelo, v kateri so bili stolpci v napačnem vrstnem redu: Power Query je deloval pravilno!
Enostavno kasneje osvežiti

Samodejno ustvarjen prepis

Naučite se Excel iz epizode Podcast
2178 spajanje delovnih listov danes
vprašanje prek YouTuba od Douga ima to
imamo štiri liste, kjer je vsak
list je regija s podatki o prodaji in
število zapisov se mesečno spreminja in
trenutno Doug poskuša uporabiti formule
ko pa se je število vrstic spremenilo
postane nočna mora, zato sem rekel hej
Doug, če lahko uporabimo napajalno poizvedbo
Excel 2010 ali Excel 2013 je brezplačen
prenesite iz Microsofta ali je že zgrajen
v leto 2016 in Office 365 piše, da vsi
tako, tukaj je tisto, kar imamo
štiri poročila o osrednji regiji vzhodu
regija južna regija in zahod
vsaka ima drugačno regijo
število zapisov, kot tukaj na jugu
Regija imamo 72 zapisov na vzhodu
regija 193 zapisov in to se bo
spremenite desno
vsakič, ko zaženemo to poročilo
zdaj imam drugo število zapisov
tu najprej naredil nekaj predpostavk, da
ni stolpca z imenom osrednji in
potem bom tudi popolnoma hudoben
tukaj in vzemite Južno regijo, ki jo želim
poskusi zajebati bom vzel
dobiček stolpec izrežite in prilepite, kako naj
obrnite jih in potem v redu, tako da bomo
imajo enega, kjer so stolpci obrnjeni
in nato še enega, kamor bomo šli
dodajte dodaten stolpec odstotkov bruto dobička
tako da bo to dobiček, deljen z
prihodki v idealnem svetu so to vse
oblikovan popolnoma enako, ampak kot sem se naučil
pred kratkim sem imel seminar v Ljubljani
Severna Karolina, če niso v redu
nekdo je imel situacijo dobro veste
na polovici leta so se stvari spremenile
in dodali so nov stolpec ali premik
stolpcev okrog smo bili res veseli
poglejte, da je moč poizvedba lahko obravnavala
s tem v redu, da bomo vzeli
vsakega od teh poročil in ga vključite
uradna oblika tabele kot tabela
to je kontrolni čaj ali pa lahko uporabite a
obseg imen za mene nadzor t je
lažja pot in kaj počnejo tukaj
ali temu mizo rečejo ena, ki jo bom jaz
preimenujte v centralno in
nato nadaljujemo do vzhodne kontrole T kliknite V redu
in to se bo zdaj imenovalo vzhod hej
na prejšnjem podcastu sem pokazal, kako če
to so bile štiri ločene datoteke
bi lahko uporabil poizvedbo moči samo za
združiti datoteke, vendar to ne deluje, ko
to so štirje ločeni ali štirje delovni listi
v isti knjigi tako dobro tam
pojdi in potem
kot je ta nadzor-t malo dolgočasen
nastavite to prvič, ampak fant
tam bo super
vsakič, ko boste to morali posodobiti pozneje
tako da bomo naredili to, da gremo
izbrati to osrednjo tabelo
in če ste v letih 2010 ali 2013 in
preneseno poizvedbo moči, ki jo boš
imel svoj zavihek, vendar čez 16 v
Excel 2016 dejansko dobiva
druga skupina v
Office 365 se zdaj preoblikuje
ki je prva skupina in tako smo
bodo rekli, da bodo to ustvarili
podatki iz tabele ali obsega v redu in
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
če rečem, da je vprašanje, hvala
za ustavljanje se vidimo naslednjič
za še eno mrežno zasedbo iz

Prenesite datoteko

Prenesite vzorčno datoteko tukaj: Podcast2178.xlsm

Zanimive Članki...