Kombiner 4 ark - Excel-tip

Indholdsfortegnelse

Se video

Excel kombinerer flere regneark til et enkelt regneark. Hvert regneark kan have et andet antal poster fra dag til dag, så formler er ikke vejen at gå. I stedet giver et lille kendt værktøj kaldet Power Query dig mulighed for at flette dataene enkelt og hurtigt.

Se video

Doug: Hvordan kombineres fire ark, hvor hver har et forskelligt antal rækker?
Brug Power Query
Formater hvert regneark som en tabel med Ctrl + T
Omdøb tabellerne
For hver tabel ny forespørgsel fra tabel. Tilføj en tilpasset kolonne til Region
I stedet for at lukke og indlæse skal du vælge Luk og indlæs til … Opret kun en forbindelse
Brug ny forespørgsel, kombiner forespørgsel, tilføj. 3 eller flere tabeller. Vælg tabellerne og tilføj
Luk og indlæs, og dataene vises på et nyt regneark
For den ene tabel med ekstra kolonne: dataene vises kun for arkets optegnelser
For den ene tabel, hvor kolonnerne var i den forkerte rækkefølge: Power Query fungerede korrekt!
Let at opdatere senere

Autogenereret udskrift

Lær Excel fra Podcast Episode
2178 fusionere til regneark i dag
spørgsmål via YouTube fra Doug har dette
situation har vi fire ark hvor hver
ark er en region med salgsdata og
antallet af poster ændres månedligt og
lige nu forsøger Doug at bruge formler
men da antallet af rækker ændrede det
bliver et mareridt lige så jeg sagde hej
Doug kan vi bruge strømforespørgsel, hvis du har
Excel 2010 eller Excel 2013 er gratis
download fra Microsoft, eller det er bygget
i 2016 og office 365 siger det ja alle
lige så her er hvad vi har, vi har
fire rapporter om den centrale region øst
region den sydlige region og den vestlige
region og hver har forskellige
antal poster som her i syd
Region vi har 72 poster i øst
region 193 registrerer, og dette vil
skift til højre
hver gang vi kører denne rapport vil have
et andet antal poster nu har jeg
lavede nogle antagelser her først det
der er ikke en søjle kaldet central og
så bliver jeg også fuldstændig ond
her og tag den sydlige region, jeg vil
prøv at skrue det op, jeg tager den
profit kolonne klippe det og indsætte det, hvordan man
vende dem og så okay så vi
har en, hvor kolonnerne vendes
og så en anden, hvor vi skal
tilføj en ekstra kolonne bruttofortjeneste procent
så dette bliver fortjeneste divideret med
indtægter i en ideel verden disse er alle
formet nøjagtigt det samme, men som jeg lærte
for nylig lavede jeg et seminar nede i
North Carolina, hvis de ikke er i orden
nogen havde en situation, du kender
halvvejs året ændrede ting sig
og de tilføjede en ny kolonne eller flytte
søjler omkring vi var virkelig glade for
se, at strømforespørgsel var i stand til at håndtere
med dette okay, så tager vi det
hver af disse rapporter og gøre det til
et officielt tabelformat som tabel så
det er kontrol te, eller du kan bruge en
Navneområde for mig er kontrol t det
lettere vej at gå, og hvad de gør her
kalder de dette bord, det skal jeg
omdøbe dette for at blive kaldt centralt og
så går vi videre til østkontrol T klik på OK
og dette kaldes øst nu hej
på en tidligere podcast viste jeg, hvordan hvis
det har været fire separate filer, vi
kunne have brugt strømforespørgsel bare til
kombinere filer, men det virker ikke når
de er fire separate eller fire regneark
i den samme bog så godt der vi
gå og derefter
som denne kontrol-t lidt kedelig for
sætte dette op første gang, men dreng
der bliver fantastisk
hver gang du skal opdatere dette senere
så vi skal gøre, er at vi skal
at vælge dette første bord centralt
region, og hvis du er i 2010 eller 2013 og
downloadet strømforespørgsel, du skal det
skal have sin egen fane, men i 16 i
Excel 2016 bliver faktisk
transformeret, som er den anden gruppe i
office 365 bliver nu transformeret
som er den første gruppe, og så er vi
vil sige, de skulle skabe dette
data fra en tabel eller rækkevidde okay og
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
siger det spørgsmål det godt tak
for at komme forbi ses vi næste gang
til endnu et net cast fra

Download fil

Download eksempelfilen her: Podcast2178.xlsm

Interessante artikler...