Auf den Punkt gebracht: Daten in Excel konsolidieren 8

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.



Kommentar erstellen

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

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

  • 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

    • Martin Weiß Beitragsautor

      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

  • 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!

    • Martin Weiß Beitragsautor

      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

  • 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

    • Martin Weiß Beitragsautor

      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

  • 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

    • Martin Weiß Beitragsautor

      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