Combineer 4 bladen - Excel-tips

Excel combineert verschillende werkbladen tot één werkblad. Elk werkblad kan van dag tot dag een ander aantal records hebben, dus formules zijn niet de juiste keuze. In plaats daarvan kunt u met een weinig bekende tool genaamd Power Query de gegevens eenvoudig en snel samenvoegen.

Bekijk video

Doug: Hoe vier vellen combineren waarvan elk een ander aantal rijen heeft?
Gebruik Power Query
Maak elk werkblad op als een tabel met Ctrl + T
Hernoem de tabellen
Voor elke tabel een nieuwe query uit tabel. Voeg een aangepaste kolom toe voor Regio
In plaats van Sluiten en laden, kiest u Sluiten en laden om alleen een verbinding te maken
Nieuwe zoekopdracht gebruiken, zoekopdracht combineren, toevoegen. 3 of meer tabellen. Kies de tabellen en voeg toe
Sluit en laad en de gegevens verschijnen op een nieuw werkblad
Voor de ene tabel met extra kolom: de gegevens worden alleen weergegeven voor de records van dat blad
Voor de ene tabel waarin de kolommen in de verkeerde volgorde stonden: Power Query werkte correct!
Gemakkelijk om later te vernieuwen

Automatisch gegenereerd transcript

Leer Excel van Podcast Episode
2178 samenvoegen voor werkbladen van vandaag
vraag via YouTube van Doug heeft dit
situatie hebben we vier bladen waar elk
sheet is een regio met verkoopgegevens en
het aantal records verandert maandelijks en
op dit moment probeert Doug formules te gebruiken
maar toen het aantal rijen dat veranderde
wordt toch een nachtmerrie, dus ik zei hey
Doug kunnen we powerquery gebruiken als je die hebt
Excel 2010 of Excel 2013 is gratis
downloaden van Microsoft of het is gebouwd
in 2016 en Office 365 staat er ja allemaal
dus hier is wat we hebben we hebben
vier meldt de centrale regio het oosten
regio de zuidelijke regio en het westen
regio en elk heeft een andere
aantal records zoals hier in het zuiden
Regio hebben we 72 records in het oosten
regio 193 records en dit gaat
rechts veranderen
elke keer dat we dit rapport uitvoeren, hebben we
een ander aantal records heb ik nu
maakte hier eerst een aantal aannames dat
er is geen kolom met de naam centraal en
dan zal ik ook helemaal slecht zijn
hier en neem de zuidelijke regio die ik wil
probeer het maar te verpesten. Ik neem de
winstkolom knip het en plak het hoe
draai die om en dan in orde, dus we
heb er een waar de kolommen zijn omgekeerd
en dan nog een waar we heen gaan
voeg een extra kolom brutowinstpercentage toe
dus dit wordt de winst gedeeld door
inkomsten in een ideale wereld dat zijn ze allemaal
precies hetzelfde gevormd, maar zoals ik heb geleerd
onlangs deed ik een seminar in
North Carolina als ze niet in orde zijn
iemand had een situatie, weet je
halverwege het jaar veranderden de zaken
en ze hebben een nieuwe kolom of verplaatsing toegevoegd
kolommen rondom waren we erg blij mee
zie dat de machtsvraag kon omgaan
met dit in orde, dus we gaan het nemen
elk van deze rapporten en maken het in
een officieel tafelformaat als tabel dus
dat is controlethee of je zou een
naambereik voor mij controle t is de
gemakkelijkere manier om te gaan en wat ze hier doen
noemen ze deze tafel. Ik ga
hernoem dit naar centraal en
dan gaan we naar Oost-controle T klik op OK
en dit gaat nu Oost heten
op een eerdere podcast heb ik laten zien hoe als
dit zijn vier afzonderlijke bestanden geweest die we
had power-query alleen kunnen gebruiken om
combineer bestanden, maar dat werkt niet wanneer
het zijn vier aparte of vier werkbladen
in hetzelfde boek zo goed daar we
ga en dan
zoals deze controle-t een beetje vervelend
stel dit de eerste keer op, maar jongen
er wordt geweldig
elke keer dat u dit later moet bijwerken
dus we gaan doen, we gaan
om deze eerste tafel centraal te kiezen
regio en als u zich in 2010 of 2013 bevindt en
gedownloade powerquery ga je het zijn
krijgt zijn eigen tabblad maar in 16 in
Excel 2016 krijgt eigenlijk
getransformeerd dat is de tweede groep in
office 365 krijgt nu een transformatie
dat is de eerste groep en dus zijn wij
ga zeggen dat ze dit gingen maken
gegevens uit een tabel of bereik in orde en
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
zeggende die vraag het goed dank u
om langs te komen, we zien je de volgende keer
voor een ander net cast van

Download bestand

Download het voorbeeldbestand hier: Podcast2178.xlsm

Interessante artikelen...