Kombinieren Sie 4 Blätter - Excel-Tipps

Excel kombiniert mehrere Arbeitsblätter zu einem Arbeitsblatt. Jedes Arbeitsblatt kann von Tag zu Tag eine andere Anzahl von Datensätzen enthalten, daher sind Formeln nicht der richtige Weg. Stattdessen können Sie mit einem wenig bekannten Tool namens Power Query die Daten einfach und schnell zusammenführen.

Schau Video

• Doug: Wie kombiniert man vier Blätter, bei denen jedes eine andere Anzahl von Zeilen hat?
• Verwenden Sie Power Query
• Formatieren Sie jedes Arbeitsblatt als Tabelle mit Strg + T.
• Benennen Sie die Tabellen um
• Für jede Tabelle eine neue Abfrage aus Tabelle. Fügen Sie eine benutzerdefinierte Spalte für Region hinzu
• Wählen Sie anstelle von Schließen und Laden die Option Schließen und Laden, um… Nur eine Verbindung zu erstellen
• Neue Abfrage verwenden, Abfrage kombinieren, Anhängen. 3 oder mehr Tabellen. Wählen Sie die Tabellen und Hinzufügen
• Schließen und laden und die Daten werden in einem neuen Arbeitsblatt angezeigt
• Für die eine Tabelle mit zusätzlicher Spalte: Die Daten werden nur für die Datensätze dieses Blattes angezeigt
• Für die eine Tabelle, in der die Spalten in der falschen Reihenfolge waren: Power Query hat korrekt funktioniert!
• Einfach später zu aktualisieren

Automatisch generiertes Transkript

• Lerne Excel aus der Podcast-Episode
• 2178 Zusammenführung für Arbeitsblätter von heute
• Frage über YouTube von Doug hat dies
• Situation haben wir vier Blätter, wo jeweils
• Blatt ist eine Region mit Verkaufsdaten und
• Die Anzahl der Datensätze ändert sich monatlich und
• Im Moment versucht Doug, Formeln zu verwenden
• aber als die Anzahl der Zeilen das änderte
• wird ein Albtraum richtig, also sagte ich hey
• Doug, können wir Power Query verwenden, wenn Sie haben
• Excel 2010 oder Excel 2013 ist kostenlos
• Download von Microsoft oder es ist gebaut
• in 2016 und Office 365 heißt es ja alles
• Genau, hier ist was wir haben, was wir haben
• Vier Berichte über die Zentralregion im Osten
• Region die südliche Region und der Westen
• Region und jeder hat eine andere
• Anzahl der Rekorde wie hier im Süden
• Region haben wir 72 Datensätze im Osten
• Region 193 Aufzeichnungen und das wird
• rechts ändern
• Jedes Mal, wenn wir diesen Bericht ausführen, wird
• eine andere Anzahl von Datensätzen habe ich jetzt
• machte hier zunächst einige Annahmen, dass
• Es gibt keine Spalte namens Central und
• dann werde ich auch ganz böse sein
• hier und nimm die Südregion, die ich will
• versuche es zu vermasseln Ich nehme das
• Profit-Spalte ausschneiden und einfügen, wie es geht
• kehre diese um und dann alles in Ordnung damit wir
• haben eine, wo die Spalten umgekehrt sind
• und dann noch eine, wo wir hingehen werden
• Fügen Sie eine zusätzliche Spalte Bruttogewinn in Prozent hinzu
• Das wird also Gewinn geteilt durch
• Einnahmen in einer idealen Welt das sind alles
• genau gleich geformt aber wie ich gelernt habe
• Vor kurzem habe ich ein Seminar in gemacht
• North Carolina, wenn es ihnen nicht gut geht
• Jemand hatte eine Situation, die Sie kennen
• Mitte des Jahres änderten sich die Dinge
• und sie fügten eine neue Spalte hinzu oder bewegten sich
• Kolumnen um uns herum waren sehr glücklich
• sehen, dass Power Query in der Lage war zu behandeln
• Wenn das in Ordnung ist, werden wir es nehmen
• jeder dieser Berichte und machen es in
• ein offizielles Tabellenformat als Tabelle also
• das ist Kontrolltee oder du könntest einen verwenden
• Namensbereich für mich Kontrolle t ist die
• einfacher Weg zu gehen und was sie hier tun
• Nennen sie diesen Tisch einen, den ich werde?
• Benennen Sie dies in zentral und um
• Dann fahren wir mit der Oststeuerung fort. T Klicken Sie auf OK
• und das wird jetzt Ost heißen, hey
• In einem früheren Podcast habe ich gezeigt, wie wenn
• Dies waren vier separate Dateien, die wir
• hätte Power Query nur dazu verwenden können
• Dateien kombinieren, aber das funktioniert nicht, wenn
• Es sind vier separate oder vier Arbeitsblätter
• im selben Buch so gut da wir
• geh und dann
• wie diese Kontrolle-t ein wenig langweilig zu
• Stellen Sie dies das erste Mal ein, aber Junge
• Es wird großartig
• jedes Mal, wenn Sie dies später aktualisieren müssen
• Also werden wir tun, wir werden gehen
• um diese erste Tabelle zentral zu wählen
• Region und wenn Sie in 2010 oder 2013 und sind
• Heruntergeladene Power-Abfrage, du wirst es
• Ich werde einen eigenen Tab haben, aber in 16 in
• Excel 2016 wird tatsächlich
• transformiert, das ist die zweite Gruppe in
• Office 365 wird jetzt transformiert
• Das ist die erste Gruppe und so sind wir
• Ich werde sagen, sie würden das schaffen
• Daten aus einer Tabelle oder einem Bereich in Ordnung und
• 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
• Sag diese Frage gut, danke
• Für einen Besuch sehen wir uns beim nächsten Mal
• für ein anderes Netz gegossen von

Download-Datei

Laden Sie die Beispieldatei hier herunter: Podcast2178.xlsm