Wie der Name schon sagt, mit dem Konsolidierungswerkzeug lassen sich Daten in Excel schnell und sauber konsolidieren.

Grundsätzlich gibt es zwei Möglichkeiten mit dem Tool zu konsolidieren:

1. Position bezogen: Diese Variante konsolidiert, wie der Name sagt, nach Position. Die Datenquellen müssen in derselben Reihenfolge angeordnet sein und dieselben Zeilen- und Spaltenbeschriftungen müssen verwendet werden.
2. Kategorie bezogen: In dieser Variante wird nach Kategorienamen konsolidiert.

Der Unterschied zwischen den beiden Varianten erläutere ich kurz im Text weiter unten. Hier sei erstmals einfach vermerkt dass wir uns in diesem Artikel hauptsächlich auf die Kategorie bezogene Variante fokussieren. Wer aber die zweite Variante kennt wird mit der ersten keine Mühe haben.

Das Konsolidierungswerkzeug benutzen

In unserem Beispiel möchten wir drei verschiedene Datenquellen konsolidieren. Alle Datenbereiche sind gleich strukturiert und haben übereinstimmende Überschriften. Wer selber mitmachen möchte kann sich die Datei mit dem  hier herunterladen.

Hier ein Bereich zur Konsolidierung als Beispiel:

Das Konsolidierungswerkzeug befindet sich im Daten Menü bei den Datentools mit dem Namen Konsolidieren.

Bevor wir das Konsoliderungswerkzeug aufrufen muss der Zellencursor in einem Bereich platziert werden in dem das Resultat der Konsolidierung ausgegeben werden kann. Mit anderen Worten: Stellt sicher dass nicht in Eurer Arbeitsmappe überschrieben wird!

Klicken und weiter geht es.

Es öffnet sich das Konsolidieren Fenster.

Zuerst legen wir fest welche Funktion zur Datenkonsolidierung benutzt werden soll. Abgesehen von Summieren (Voreinstellung) können auch noch andere Rechnungsoperationen vorgenommen werden. Hier die Liste mit der Auswahl:

Um die Bereich zu konsolidieren müssen wir nun im Werkzeug die zu konsolidierende Bereiche eingeben. Dies tun wir in dem wir im Feld Verweis den Bereich aussuchen und im Anschluss Hinzufügen drücken. Dies machen wir für alle drei Bereiche im Beispiel.

Unter der Auswahl Beschriftung aus legen wir fest dass wir sowohl nach Oberster Zeile wie auch Linker Spalte konsolidieren möchten.

Position bezogen Konsolidieren

Wenn die Beschriftung aus leer gelassen wird, dann konsolidiert Excel nach der Position bezogenen Variante 1 welche ich anfangs erläutert habe. Diese Variante eignet sich nur dann wenn der Aufbau der zu konsolidierenden Daten genau gleich strukturiert sind!

Nachdem wir OK drücken haben wir unser Resultat!

Fertig!

Konsolidieren von ungleichen Bereichen

Im ersten Beispiel waren die Daten alle gleich strukturiert und benannt. In der Praxis ist dies selten der Fall, weshalb ich auch die Kategorie bezogene konsolidierungs Variante in diesem Artikel vorstellen wollte.

In der Kategorie bezogenen Konsolidierung ist darauf zu achten dass jeweils die erste Spalte in den Bereichen die Kategorie darstellt (also Konten, Bereiche, Themen oder Namen nach denen konsolidiert wird).

Beispiel 2: Konsolidieren von Daten die nur in einem Bereich vorhanden sind

In diesem Beispiel wird in der konsolidierten Tabelle (I1:L6) alle Spaltenüberschriften aufgeführt, jedoch werden nur die Summen wirklich konsolidiert (da nur diese nummerische Werte enthalten) welche in beiden Bereichen vorhanden sind. Rubriken welche Text enthalten werden in der Konsolidierten Tabelle aufgeführt, jedoch ohne Werte.

 

Beispiel 3: Mehrere Spalten konsolidieren

Im Beispiel 3 haben wir mehrere Spalten mit nummerischen Werten welche alle konsolidiert werden. Diese werden jedoch nur auf die erste Spalte konsolidiert (Namen), und zwar selbst dann wenn beide Tabellen weitere Rubriken enthalten nach denen sortiert werden könnte. Hier wäre eine Pivot-Tabelle dann wiederum flexibler.

Beispiel 4: Mehrere Spalten aber ungleiche Reihenfolge

Werden Bereich konsolidiert in denen die Daten nicht in der selben Reihenfolge (Spalten) erscheinen, so muss man acht geben dass zumindest die erste Spalte in allen Bereichen die selbe ist. Ansonsten bekommt man eine Auswertung die “Nonsens” ist. Alle weiteren zu konsolidierenden Spalten können jedoch nach beliebiger Reihenfolge erscheinen!

Konsolidieren vs. Pivot-Tabelle

Das Konsolidierungswerkzeug hat eine ähnliche Funktion wie die Pivot-Tabelle. Es aggregiert (oder konsolidiert) Daten. Beide Werkzeuge sind zum Aggregieren und Auswertung von Daten in Excel sehr nützlich, wobei Pivot-Tabellen ganz klar flexibler sind, und deshalb auch öfters benutzt werden.

Das Konsolidierungswerkzeug hat jedoch einen Vorteil gegenüber der Pivot Tabelle: Das Konsolidierungstool kann verschiedensten Datenquellen zusammenziehen!

Daten für eine Pivot Tabelle müssen immer bereits zusammengestellt sein. Entweder in einer Tabelle oder einer anderen Datenquelle.

Fazit

Oft wird viel Arbeit in genau dieses zusammenstellen einer Datenbasis gesteckt, damit anschließend eine Pivot-Tabelle erstellt werden kann. Dies ist jedoch eine sehr Zeitraubende Aufgabe. Solange es die Analyse oder der Bericht zulässt ist das Konsolidierungswerkzeug ein fleißiger kleiner Helfer um Minuten und Stunden zu sparen!

Für welche Aufgaben benutzt Ihr das Konsolidierungswerkzeug? Kommentare herzlich willkommen!

Mehrere Dateien Zusammenführen

Wenn Euch dieser Beitrag noch nicht ans Ziel gebracht hat, könnt Ihr Euch auch das Multi File Konsolidierungs Tool von ExcelNova anschauen. Damit könnt Ihr Werte aus duzenden von Dateien automatisiert herauskopieren lassen!