Skombinujte 4 hárky - tipy pre Excel

Excel kombinuje niekoľko pracovných hárkov do jedného pracovného hárka. Každý pracovný hárok môže mať zo dňa na deň iný počet záznamov, takže vzorce nie sú správnym spôsobom. Namiesto toho vám málo známy nástroj s názvom Power Query umožní jednoduché a rýchle zlúčenie údajov.

Pozeraj video

Doug: Ako skombinovať štyri hárky, kde každý má iný počet riadkov?
Použite Power Query
Naformátujte každý pracovný hárok ako tabuľku pomocou kombinácie klávesov Ctrl + T
Premenujte tabuľky
Pre každú tabuľku nový dopyt z tabuľky. Pridajte vlastný stĺpec pre región
Namiesto možnosti Zavrieť a načítať vyberte možnosť Zavrieť a načítať do … Vytvoriť iba pripojenie
Použite Nový dopyt, Spojiť dopyt, Pripojiť. 3 alebo viac tabuliek. Vyberte tabuľky a pridať
Zatvorte a načítajte a údaje sa zobrazia v novom pracovnom hárku
Pre jednu tabuľku so stĺpcom navyše: údaje sa zobrazia iba pre záznamy tohto hárka
Pre jednu tabuľku, kde boli stĺpce v nesprávnom poradí: Power Query fungoval správne!
Ľahko sa obnoví neskôr

Automaticky generovaný prepis

Naučte sa program Excel z epizódy podcastov
2178 sa dnes zlúči pre pracovné listy
Doug má prostredníctvom YouTube otázku
situácia má štyri listy, kde každý
hárok je región s údajmi o predaji a
- počet záznamov sa mení mesačne a -
práve teraz sa Doug pokúša používať vzorce
ale keď sa to zmenilo počtom riadkov
sa stáva nočnou morou, tak som povedal, že hej
Doug môžeme použiť power query, ak máš
Excel 2010 alebo Excel 2013 je to zadarmo
stiahnuť z Microsoftu alebo je postavený
do roku 2016 a Office 365 hovorí áno
presne tak, tu je to, čo máme, máme
štyri správy o strednom regióne na východe
región južný región a západ
regiónu a každý má iný
počet záznamov ako tu na juhu
Región máme na východe 72 záznamov
regiónu 193 záznamov a toto sa chystá
zmeniť právo
zakaždým, keď spustíme túto správu, bude mať
teraz mám iný počet záznamov
najskôr tu urobil niekoľko predpokladov
neexistuje stĺpec centrálny a
potom tiež budem úplne zlý
sem a vezmem si južný región, ktorý chcem
skús to pokaziť, vezmem
stĺpec zisk to vystrihnite a prilepte ako na to
zvrátiť tieto a potom v poriadku, takže my
mať taký, kde sú stĺpce obrátené
a potom ďalší, kam sa chystáme
pridať ďalší stĺpec percento hrubého zisku
takže toto bude zisk vydelený
príjmy v ideálnom svete, to sú všetko
tvaroval úplne rovnako, ale ako som sa dozvedel
nedávno som robil seminár dole v
Severná Karolína, ak nie sú v poriadku
niekto mal situáciu dobre, viete
v polovici roka sa veci zmenili
a pridali nový stĺpec alebo presunuli
kolóny okolo sme boli naozaj radi
vidieť, že moc dotaz bol schopný vyriešiť
s týmto v poriadku, takže si vezmeme
každej z týchto správ a urobte to
oficiálny formát tabuľky ako tabuľka tak
to je kontrolný čaj alebo môžete použiť a
rozsah mien pre kontrolu je t
ľahšia cesta a čo tu robia
hovoria tomuto stolu jeden?
premenujte toto na centrálne a
potom prejdeme na východnú kontrolu T a klikneme na OK
a toto sa teraz bude volať Východ
na staršom podcaste som ukázal, ako keby
to boli štyri samostatné súbory
mohol použiť power query iba na
skombinovať súbory, ale to nefunguje, keď
sú to štyri samostatné alebo štyri pracovné listy
v tej istej knihe tak dobre, že my
choď a potom
ako táto kontrola - je to trochu zdĺhavé
nastav to prvýkrát, ale chlapec
bude úžasné
zakaždým, keď to budete musieť neskôr aktualizovať
ďalej, takže urobíme, že ideme
zvoliť tento prvý centrálny stôl
regiónu a ak ste v rokoch 2010 alebo 2013 a
stiahnutý dotaz na napájanie, budete
bude mať vlastnú záložku, ale v 16 palcoch
Excel 2016 sa skutočne blíži
ktorá je druhou skupinou v
Office 365 sa teraz transformuje
čo je prvá skupina a tak sme
povedzme, že to vytvoria
- údaje z tabuľky alebo rozsahu v poriadku a -
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
povediac tu otazku dobre dakujem
za zastavenie sa uvidíme nabudúce
pre ďalšie obsadenie siete z

Stiahnuť súbor

Stiahnutie vzorového súboru nájdete tu: Podcast2178.xlsm

Zaujímavé články...