Combiner 4 feuilles - Conseils Excel

Excel combine plusieurs feuilles de calcul dans une seule feuille de calcul. Chaque feuille de calcul peut avoir un nombre d'enregistrements différent d'un jour à l'autre, les formules ne sont donc pas la solution. Au lieu de cela, un outil peu connu appelé Power Query vous permettra de fusionner les données simplement et rapidement.

Regarder la vidéo

Doug: Comment combiner quatre feuilles où chacune a un nombre de lignes différent?
Utiliser Power Query
Mettre en forme chaque feuille de calcul sous forme de tableau avec Ctrl + T
Renommez les tables
Pour chaque table, nouvelle requête From Table. Ajouter une colonne personnalisée pour la région
Au lieu de Fermer et charger, choisissez Fermer et charger pour… Créer uniquement une connexion
Utiliser une nouvelle requête, combiner une requête, ajouter. 3 tableaux ou plus. Choisissez les tables et ajoutez
Fermer et charger et les données apparaissent sur une nouvelle feuille de calcul
Pour la table avec colonne supplémentaire: les données ne s'affichent que pour les enregistrements de cette feuille
Pour la table où les colonnes étaient dans le mauvais ordre: Power Query fonctionnait correctement!
Facile à actualiser plus tard

Transcription générée automatiquement

Apprenez Excel à partir de l'épisode de podcast
2178 fusionner pour les feuilles de calcul d'aujourd'hui
question via YouTube de Doug a ceci
situation nous avons quatre feuilles où chacune
feuille est une région avec des données de vente et
le nombre d'enregistrements change mensuellement et
en ce moment, Doug essaie d'utiliser des formules
mais quand le nombre de lignes a changé cela
devient un vrai cauchemar alors j'ai dit hey
Doug pouvons-nous utiliser Power Query si vous avez
Excel 2010 ou Excel 2013 c'est gratuit
télécharger à partir de Microsoft ou c'est construit
en 2016 et Office 365 il dit oui tout
bon alors voici ce que nous avons, nous avons
quatre rapports la région centrale de l'est
région la région sud et l'ouest
région et chacun a un
nombre de disques comme ici dans le sud
Région où nous avons 72 enregistrements dans l'est
région 193 records et cela va
changer à droite
chaque fois que nous exécutons ce rapport,
un nombre différent d'enregistrements maintenant j'ai
fait quelques hypothèses ici d'abord que
il n'y a pas de colonne appelée central et
alors aussi je vais être complètement mauvais
ici et prendre la région sud que je veux
essaie de tout foutre, je vais prendre le
colonne de profit couper et coller comment faire
inverser ceux-ci et puis très bien alors nous
en avoir un où les colonnes sont inversées
et puis un autre où nous allons
ajouter une colonne supplémentaire pour cent de marge brute
donc ce sera le profit divisé par
revenus dans un monde idéal, ce sont tous
forme exactement la même mais comme j'ai appris
récemment, je faisais un séminaire en
Caroline du Nord s'ils ne vont pas bien
quelqu'un a eu une situation bien tu sais
à la moitié de l'année, les choses ont changé
et ils ont ajouté une nouvelle colonne ou déplacer
colonnes autour de nous étions vraiment heureux
voir que Power Query a pu traiter
avec tout ça, alors on va prendre
chacun de ces rapports et en faire
un format de table officiel comme table afin
c'est du thé de contrôle ou vous pouvez utiliser un
plage de noms pour moi contrôle t est le
moyen plus simple et ce qu'ils font ici
est-ce qu'ils appellent cette table une je vais
renommez-le pour qu'il soit appelé central et
puis nous passons au contrôle Est T cliquez sur OK
Et ça va s'appeler East maintenant hey
sur un podcast précédent, j'ai montré comment si
ce sont quatre fichiers séparés que nous
aurait pu utiliser la requête d'alimentation juste pour
combiner des fichiers mais cela ne fonctionne pas quand
ce sont quatre ou quatre feuilles de calcul
dans le même livre si bien là nous
aller et puis
comme ce contrôle-t un peu fastidieux à
mis en place la première fois mais garçon
il va être génial
chaque fois que vous devez le mettre à jour plus tard
sur alors nous allons faire est nous allons
de choisir cette première table centrale
région et si vous êtes en 2010 ou 2013 et
requête d'alimentation téléchargée, vous allez c'est
va avoir son propre onglet mais en 16
Excel 2016 obtient en fait
transformé qui est le deuxième groupe dans
Office 365 se transforme maintenant
qui est le premier groupe et nous sommes donc
va dire qu'ils allaient créer ça
les données d'une table ou d'une plage sont correctes et
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
en disant cette question bien merci
pour s'arrêter, on se voit la prochaine fois
pour un autre filet de

Télécharger un fichier

Téléchargez l'exemple de fichier ici: Podcast2178.xlsm

Articles intéressants...