Auf den Punkt gebracht: Daten in Excel konsolidieren 28

Die Konsolidieren-Funktion: Daten aus unterschiedlichen Arbeitsblättern oder Arbeitsmappen verdichtet darstellen
 

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

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

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: Summe

Konsolidieren: Verschiedene Funktionen

Wir belassen es bei der Summenfunktion.

Nun klicken wir auf das kleine Symbol rechts nebem dem Feld „Verweis“:

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

Ersten Bereich auswählen

Über den Knopf „Hinzufügen“ übernehmen wir den zuvor markierten Bereich in die Liste der Verweise:

Vorhandene 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

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

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

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

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

Angepasste Verweise

Excel bereitet die konsolidierte Tabelle dann entsprechend anders auf:

Die neue konsolidierte Tabelle

Die neue konsolidierte Tabelle

Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

28 Gedanken zu “Auf den Punkt gebracht: Daten in Excel konsolidieren

  • Avatar-Foto
    Tom

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Josef Hart

    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!

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Kerstin

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Maria Karwinsky

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Ines

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Jess

    Hallo

    Super Tipp, aber wie kann ich die konsolidierten Daten in eine neue Tabelle kopieren (ohne Einzelwerte etc.)?

    Danke

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Jess

        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.

  • Avatar-Foto
    Gerhard Duscha

    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?

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Eberhardt, Konrad

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Sandra Geisler

    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?

    • Avatar-Foto
      Sandra Geisler

      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.

  • Avatar-Foto
    Axel Goede

    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?

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Dounia

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Stephan Rothmund

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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 😉

    • Avatar-Foto
      Martin Weiß

      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