Im Geschäftsleben hat man es häufig mit einer Vielzahl an gleichartigen Excel-Arbeitsmappen zu tun. So liefert z.B. jede Verkaufsniederlassung ihre Umsatzzahlen in einer eigenen Arbeitsmappe an. Und das Controlling hat jetzt das Vergnügen, die Daten aus den vielen einzelnen Dateien mit möglichst wenig Aufwand zusammenzufassen.
Kein großes Problem, wenn man die Konsolidieren-Funktion kennt!
Und so geht’s:
Als kleines Übungsbeispiel habe ich 4 Arbeitsmappen erstellt, die jeweils die Umsätze der Regionen Nord, Süd, Ost und West enthalten. Die einzelnen Umsatztabellen sind zwar gleich aufgebaut, enthalten aber nicht für jede Region die gleichen Produkte:
Umsätze in vier Regionen
Diese Umsätze möchte ich nun in einer neuen Datei zusammengefasst darstellen.
Konsolidierung
Dazu öffne ich eine neue Arbeitsmappe und starte die Funktion „Konsolidieren“, die sich im Register „Daten“ unter den Datentools befindet:
Datentools: Konsolidieren
Im sich nun öffnenden Fenster lassen sich für das Zusammenfassen der Daten neben der Summenfunktion verschiedene andere mathematische Operationen auswählen:
Konsolidieren: Verschiedene Funktionen
Wir belassen es bei der Summenfunktion.
Nun klicken wir auf das kleine Symbol rechts nebem dem Feld „Verweis“:
Verweis
Das Fenster verkleinert sich bis auf das Eingabefeld. Nun können wir die Umsatztabelle unserer ersten Region markieren, und zwar einschließlich der Spalten und Zeilenbezeichnungen und der Ergebniszeile:
Ersten Bereich auswählen
Über den Knopf „Hinzufügen“ übernehmen wir den zuvor markierten Bereich in die Liste der Verweise:
Vorhandene Verweise
Anschließend verfahren wir nach dem gleichen Schema für die anderen drei Regionen:
- Auf das Symbol rechts neben dem Verweis-Feld klicken
- Tabellenbereich markieren und mit der Eingabe-Taste bestätigen
- Mit dem Hinzufügen-Knopf in die Liste der Verweise übernehmen
Zu guter Letzt setzen wir noch die drei Häkchen, mit denen wir die Zeilen- und Spaltenbeschriftung übernehmen und die Ergebnisse mit den Quelldaten verknüpfen. Das Eingabefenster sollte nun so aussehen:
Konsolidieren: Fast fertig
Zur Erklärung:
Wenn wir das Häkchen „Verknüpfungen mit Quelldaten“ nicht setzen, werden die Ergebnisse berechnet und als statische Zahlen in die neue Tabelle eingetragen. Mit einer Verknüpfung hingegen bleibt ein Verweis auf die Originaldaten bestehen. Das heißt, wenn sich an den Quelldaten etwas ändert, wird auch die konsolidierte Tabelle automatisch angepasst.
Nach einem Klick auf OK bekomme ich die konsolidierten Ergebnisse:
Konsolidierte Tabelle
Wie man am linken Rand erkennen kann, hat Excel die Daten gleich noch gruppiert. So kann man sich nach Belieben zu den Zwischenergebnissen auch die Einzelwerte anzeigen lassen. Wenn man eine der Wertezellen auswählt, sieht in der Eingabezeile auch, dass eine Verknüpfung zu den Originaldaten besteht:
Gruppierte Ergebnisse
Tipp
Die Ausgangsdaten müssen nicht zwingend gleich aufgebaut sein. So könnte in meinem Beispiel eine der vier Regionen die Umsätze für eine andere Periode geliefert haben (statt Jan – März z.B. Apr – Juni) und obendrein für mehr als drei Produkte:
Ein anderer Tabellenaufbau
Auch das wäre überhaupt kein Problem. Ich muss nur im Konsolidieren-Fenster den alten Verweis der Region West löschen und den neuen Tabellenbereich hinzufügen:
Angepasste Verweise
Excel bereitet die konsolidierte Tabelle dann entsprechend anders auf:
Die neue konsolidierte Tabelle
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo,
super Artikel, genau nach so einer Lösung habe ich gesucht, vielen Dank…
allerdings hätte ich da noch eine Frage dazu:
Funktioniert das auch wenn die Quelldateien auf OneDrive liegen und freigegeben
sind, so dass die externen Mitarbeiter nicht mit eigenen Dateien hantieren müssen?
Und was passiert wenn eine oder mehrere Quelldateien gerade geöffnet sind und
bearbeitet werden, kommt Excel damit klar?
Vielen Dank schon im Voraus,
Tom
Hallo Tom,
zu freigegebenen Dateien auf OneDrive kann ich leider nichts sagen, ebenso beim Thema mit den geöffneten Quelldateien. Bei Letzterem würde ich vermuten, dass es kein Problem ist, da Excel normalerweise auf die gespeicherten Daten zugreift. Aber ich bin mir nicht sicher, hier hilft vermutlich nur ausprobieren.
Schöne Grüße,
Martin
Dieses Verfahren funktioniert bei wenigen Tabellen ganz gut.
Ist aber letztendlich nur ein manuelles zusammenkopieren von Tabellen.
Ich muss immer noch einzelne Tabelleninhalte markieren, kopieren und einfügen.
Bei mehr als 10 Tabellen kommt man dann leicht ins schleudern.
In alten MS-DOS Zeiten gab es den Befehl:
copy tabelle1.txt + tabelle2.txt + tabelle3.txt > zusammen.txt
und das hat funktioniert!
Hallo Josef,
wenn es nur um das Untereinanderhängen von mehreren Tabellen, gebe ich Dir Recht. Die Konsolidieren-Funktion bietet hier aber deutlich mehr: Schließlich werden die Tabellen ja nicht nur untereinander kopiert, sondern auch abhängig von deren Aufbau ein eine einheitliche Struktur gebracht. Und was noch wichtiger ist: Die Möglichkeit, zu den Quellinhalten zu verküpfen, so dass sich auch Änderungen in der konsolidierten Tabelle widerspiegeln.
Dazu war auch der gute alte copy-Befehl nicht in der Lage ;-=
Schöne Grüße,
Martin
Guten Tag,
der Tipp ist super – genauso habe ich es auch in einem anderen Portal gelesen und befolgt.
Ich will eine Serienbriefdatei in Excel, bestehend aus vier Tabellenblättern für jeweils verschiedene Branchen, in einer Tabelle (5. Datenblatt) konsolidieren, damit Word alle Datensätze berücksichtigt (Word kann nicht aus mehreren Tabellenblättern Datensätze verarbeiten, sondern immer nur aus einer Tabelle).
Bei mir ist nun ein Problem aufgetreten:
Ich habe die vier Tabellenblätter mit identischen Spalten nach der Erklärung konsolidiert (je vier Spalten beinhalten Text, je eine Spalte eine PLZ, also Zahl).
Nun sind zwar alle Datensätze sind in der konsolidierten Tabelle vorhanden. Aber aus irgendeinem unerfindlichen Grund sind von den fünf Spalten nur zwei ausgefüllt:
Spalte A -> es fehlt die Tabellkopfbezeichnung „Firma“
Spalte B -> „Straße“ als Tabellenkopf ist ausgefüllt, aber keine Inhalte in den Zellen darunter
Spalte C -> Tabellenkopf „PLZ“ und alle Zahlen darunter sind vollständig
Spalten D + E -> „Ort“ und „Funktion“ wie Spalte B: die Tabellenkopfbezeichnung ist vorhanden aber keine Werte darunter
Ich habe das mehrfach probiert. Auch die Variante den Tabellenkopf händisch einzukopieren und nur die Inhalte zu konsolidieren, hat nicht funktioniert.
Was mache ich falsch? Ich will die Datensätze nicht händisch kopieren, weil ja dann keine Änderungen übernommen werden.
Für eine Idee bzw. Lösung bin ich echt dankbar.
Viele Grüße Kerstin
Hallo Kerstin,
ich fürchte, dass du für deine Zwecke mit der Konsolidieren-Funktion auf keinen grünen Zweig kommst. Wenn ich es richtig verstanden habe, befinden sich in allen vier Tabellen Adressen, die zwecks Serienbrief-Erstellung in eine Tabelle zusammengeführt werden sollen. Dafür ist die Konsolidieren-Funktion nicht gedacht: Hier werden Werte nach verschiedenen Kriterien verdichtet (also z.B. summiert, gezählt etc.), aber nicht einfach der Reihe nach aufgelistet. Daher bleibt (ohne Makros) nur das manuelle Zusammenkopieren aus den unterschiedlichen Tabellen.
Grüße,
Martin
Hallo, ich finde die Tipps hier alle sehr hilfreich, habe aber ein besonderes Problem. wir nutzen Excel als „Access-Ersatz“, d.h. wir verwalten Adressen mit Excel. Nun haben wir 4 Tabellenblätter der einzelnen Teams – die wir (ohne copy-paste) in einem Tabellenblatt zusammenführen wollen. Verändert sich etwas in Team 1, soll es sich automatisch in der Zusammenfassung ebenfalls ändern.
Irgendwie komme ich nicht auf die Lösung….
Könnt ihr mir auf die Sprünge helfen?#Danke und liebe Grüße Maria
Hallo Maria,
wenn ich die richtig verstanden habe, sollen die Adressenlisten alle auf einem einzigen Blatt zusammengeführt werden. Ich hinterfrage hier nicht die Sinnhaftigkeit verschiedener Tabellenblätter für diese Zwecke, denn hier sind Probleme eigentlich vorprogrammiert.
Ich persönlich würde die Zusammenführung über Power Query umsetzen. Das ist ein Excel-Add-In von Microsoft, das es ab Excel 2010 zum kostenlosen Download gibt (https://www.microsoft.com/de-DE/download/details.aspx?id=39379). Damit lassen sich Daten aus unterschiedlichsten Quellen auch in extrem großen Mengen zusammenfassen. Allerdings setzt es eine gewisse Einarbeitung voraus, eine Beschreibung würde hier den Rahmen sprengen.
Aber das wäre definitiv eine Lösung, die ich empfehlen kann. Ansonsten sehe ich nur den manuellen Weg über Copy&Paste (den Du ja ausgeschlossen hast) oder ein kleines VBA-Makro.
Schöne Grüße,
Martin
Super, danke für dies schöne Zusammenfassung – klappt super!
Eine Frage habe ich noch. Gibt es einen besonderen Trick, wie man die Namen der Arbeitsmappen (hier Nord, Ost etc.) bei der automatischen Gruppierung angezeigt bekommt. Bei mir ist es zwar gruppiert, aber die Namen werden nicht angezeigt (leere Zelle).
Herzlichen Dank
Hallo Ines,
der „Trick“ ist, dass ich die anderen Arbeitsmappen unter diesen Dateinamen abgespeichert habe (Nord.xls, Ost.xls usw). Und dieser Name wird in der Gruppierung verwendet.
Schöne Grüße,
Martin
Hallo
Super Tipp, aber wie kann ich die konsolidierten Daten in eine neue Tabelle kopieren (ohne Einzelwerte etc.)?
Danke
Hallo Jess,
was genau meinst Du mit „ohne Einzelwerte“?
Grundsätzlich kann die konsolidierte Tabelle genauso kopiert und woanders eingefügt werden, wie jede andere normale Tabelle. Ich stehe gerade etwas auf dem Schlauch…
Schöne Grüße,
Martin
Meine Ergebnistabelle hat mir meine Daten konsolidiert und ich sehe die Zusammenfassung und möchte nur diese kopieren (und nicht die ganzen Einzeldaten). Leider kann ich kein Bild hochladen um das Problem zu verdeutlichen.
Soweit so gut. Ich habe den Dateinamen West für den 2. Step geändert. Dann muss man in der Spalte „Region“ nacharbeiten. Aber so erkennt man besser, dass die Bezeichnung aus dem Dateinamen genommen wird.
Problem: Die Funktion rechnet die ausgewählte Funktion auf alle „numerischen“ Spalten. Wenn man, wie ja im Pers.-Wesen oft, die Personal-Nr. in einer Spalte hat, wird die brav mit summiert. Außer mit einem SVERWEIS die wieder hinzubiegen, ist mir keine Lösung geglückt. Gibt es bessere Vorschläge?
Hallo Gerhard,
wenn die Personalnummer nicht die einzige Spalte ist, die für die Gliederung herangezogen werden soll, wird es hier vermutlich keine richtig gute Lösung geben.
Schöne Grüße,
Martin
Hallo Martin,
Deine Erklärungen sind sehr gut.
Leider bekomme ich die Daten nicht komplett oder falsch in die zweite Tabelle geliefert, habe keine Lösung ??
Bin so vorgegangen, wie Du beschrieben hast.
Mit freundlichen Grüßen
Hallo Konrad (oder doch Eberhardt?),
das ist jetzt aus der Ferne sehr schwierig zu beantworten. Entweder passt etwas mit den markierten Bereichen in den Quelldateien nicht oder die Tabellen sind doch zu unterschiedlich aufgebaut, so dass man sie vielleicht tatsächlich nicht konsolidieren kann. Ich habe es noch nicht ausprobiert, aber vielleicht sind auch gefilterte oder ausgeblendete Zeilen oder Spalten in den Quelldaten ein Problem.
Schöne Grüße,
Martin
Hallo,
ich habe jetzt schon mehrfach das Konsolidieren ausprobiert, aber gerade wenn es um die Veränderlichkeit der konsolidierten Tabelle geht, wird diese nie angepasst. Die Verknüpfung zur Quelldatei ist gesetzt, dennoch werden keine Änderungen übernommen. Ich habe Ihre Übung exakt nachgebaut und auch hier bleibt die Tabelle über Jan/Feb/Mrz bestehen. Geändert hat sich allerdings die Anzahl der Zeilen in der Gruppierung links am Tabellenrand: Hier tauchen jetzt jeweils 7 leere Zeilen auf, wenn man die Gruppierung öffnet.
Haben Sie eine Idee?
Hallo,
ich habe meine Fehler jetzt selbst gefunden: Ich hatte in den ersten 4 Tabelle nur die Produkte 1/2/3 angegeben nicht aber 4 und 5, die in der ausgetauschten Tabelle auch genannt werden. Wenn alle Produkte im Vorfeld genannt werden, passt sich die konsolidierte Tabelle an.
Genau das ist aber mein Problem: Ich benötige eine Form der Addition, die auch das Einfügen weiterer Produkte im späteren Verlauf zulässt und in der konsolidierten Tabelle einfügt.
Hierfür kann ich keine Lösung finden.
Hallo Sandra,
das Problem sind vermutlich die Verweise zu den Quelldateien. Dort wird ja ein fester Tabellenbereich angegeben und damit werden neue Daten ignoriert, wenn diese nicht im definierten Tabellenbereich enthalten sind. Ein Versuch wäre, den Tabellenbereich entsprechend großzügiger zu dimensionieren, so dass auch neu Produkte umschlossen werden (das habe ich allerdings selbst noch nicht getestet).
Ansonsten empfehle ich, Ihnen Power Query bzw. Daten abrufen und transformieren etwas näher anzusehen. Damit sind weitaus dynamischere Konsolidierungen möglich. Allerdings ist ein gewisser Einarbeitungsaufwand in Kauf zu nehmen. Sie finden hier auf dem Blog ein paar Einsteiger-Artikel dazu:
https://www.tabellenexperte.de/einfuehrung-in-power-query-teil-1/
https://www.tabellenexperte.de/einfuehrung-in-power-query-teil-2/
Und dann auch noch diesen hier:
https://www.tabellenexperte.de/massen-import-mit-powerquery/
Schöne Grüße,
Martin
Habe zurzeit ein großes Problem. Es müssen 2 Tabellen konsolidiert werden. Beide identisch aufgebaut.
1. Spalte stehen Namen aller Mitarbeiter, alle sind mehrfach vorhanden.
Folgende Spalten bestehen einige aus Zahlen und einige aus Text.
Konsolidierung schlägt immer fehl “ keine Daten konsolidiert“.
Frage: muss ich die Spalten mit Text weglassen?
Hallo Axel,
Textspalten dürfen grundsätzlich schon enthalten sein, aber sie werden dann einfach leer übertragen. Konsolidiert werden nur Wertespalten.
Schöne Grüße,
Martin
Hallo Martin,
Vielen Dank für die immer wertvollen Tipps. Ich habe gegenwärtig das Problem, dass ich zwar Tabellen konsolidieren kann, allerdings das Problem habe, dass mein Produkt aus „zwei Spalten“ besteht, d.h, einer Materialnummer und der Produktbezeichnung. In der konsolidierten Tabelle erscheint entweder das eine oder andere. Was mir nur bedingt hilft, da ich bei Materialnummer nicht weiß, worum es geht, mit der Produktbezeichnung aber nicht in unserer MaWi arbeiten kann. Gibt es eine Möglichkeit, beides in der konsolidierten Tabelle zu haben?
Vielen Dank und liebe Grüße
Dounia
Hallo Dounia,
ich fürchte, das wird mit der Konsolidieren-Funktion nicht klappen. Soweit ich es sehen kann, ist nur eine Spalte zulässig.
Heute würde ich ohnehin für diese Zwecke Power Query benutzen. Dort kannst du alle Quelltabellen importieren und dann hinterher über Pivot-Tabellen so auswerten, wie du es brauchst.
Schöne Grüße,
Martin
Hallo Martin
Ich möchte verknüpft konsolidieren, aber ohne die Gliederung zu erhalten. Geht das direkt?
Besten Dank für Deinen Hinweis.
LG Stephan
Hallo Stephan,
du kannst ja hinterher jederzeit die Gliederung wieder entfernen: Menü „Daten | Gruppierung aufheben“.
Hinweis:
Der Blogartikel stammt noch aus dem Jahr 2014. Heute würde ich die verschiedenen Tabellen dynamisch per Power Query zusammenfassen und über eine Pivot-Tabelle auswerten.
Schöne Grüße,
Martin
Lieber Martin
Einziger, erkennbarer Nachteil: keine vorhandene Dynamisierung.
In heutigen [MS 365-]Zeiten wäre die – zum Beispiel – Summierung sämtlicher Arrays der als Tabellen formatierten Quell-Bereiche flexibler und mächtiger als die oben aufgezeigte Konsolidierung.
Herzlichst
[Ebenfalls-] Martin 😉
Hallo Martin,
ja, Excel aus Microsoft 365 bietet mit den dynamischen Array-Funktionen (HSTAPELN, VSTAPELN etc.) tolle neue Möglichkeiten.
Danke für den ergänzenden Hinweis.
Schöne Grüße,
Martin