Daten in Excel konsolidieren

Ein kleines Unternehmen soll drei Standorte mit einer unterschiedlichen Anzahl an Mitarbeitern haben. Zum Teil arbeiten Mitarbeiter auch wechselnd an verschiedenen Standorten.

Die Zentrale bekommt monatlich die geleisteten Arbeitsstunden mit gleichartig aufgebauten Arbeitsmappen gemeldet. Die Daten sind in den Arbeitsmappen “Ort1.xlsx”, “Ort2.xlsx” und “Ort3.xlsx” enthalten.

Die Arbeitsmappen enthalten jeweils alle Stunden des bis zum Berichtsmonat aufgelaufenen Jahres.

Aufgabe ist es nun, die Einzelberichte zu einem Bericht zusammenzufassen.

Du könntest dir nun eine Tabelle nach diesem Muster aufbauen und die Stunden der Mitarbeiter addieren. Das kann bei einer großen Anzahl an Mitarbeitern schwierig werden, da nicht alle Mitarbeiter in allen Standorten arbeiten und die Einzelberichte nicht sortiert sein müssen.

Hier hilft dir ein Datentool, nämlich “Konsolidieren”.

Was ist unter “Konsolidieren zu verstehen?

Einfach gesagt werden beim Konsolidieren mehrere Einzelgrößen zu einer Gesamtgröße zusammengefasst bzw. verdichtet.

Wie musst du dazu vorgehen?

Schau dir die Meldungen der drei Standorte an. Sie haben eine unterschiedliche Zeilenzahl und die Namen der Mitarbeiter sind nicht identisch.

Gehe zuerst in die Arbeitsmappe, die die verdichteten Informationen aufnehmen soll. Klicke auf die künftige linke obere Zelle (z.B. B5), gehe ins Menü Daten/Datentools und rufe “Konsolidieren” auf. Es erscheint diese Maske:

Unter Funktion findest du alle möglichen Funktionen, entscheide dich hier für Summe.

Klicke rechts neben Verweis auf den Pfeil nach oben Markiere in der Mappe “Ort1.xlsx” den Bereich B4:E9 und klicke dann auf hinzufügen. Markiere anschließend die gleichen Bereiche in “Ort2.xlsx” und “Ort3.xlsx”. Die markierten Bereiche müssen in allen Datenmappen gleich groß sein.

Setze abschließend noch die Häkchen bei “Oberster Zeile”, “Linker Spalte” und “Verknüpfungen mit Quelldaten”. Mit “OK” schließt du den Prozess ab und hast die konsolidierten Daten als Tabelle vorliegen.

Du siehst am linken Tabellenrand, dass gleichzeitig auch noch eine Gruppierung erfolgt ist. Dadurch wird es dir möglich, dir Zwischenergebnisse anzeigen zu lassen. Klicke dazu auf die Gliederungsebene 2 (links oben in der Ecke). Nun sieht die Tabelle so aus:

Darin sind soweit alle Daten, die mit den einzelnen Berichtsmappen gemeldet wurden. Du siehst, dass z.B. Susanne Wilke an zwei Orten gearbeitet hat. Du siehst, wie viele Stunden jeweils auf die einzelnen Monate entfallen. Du hast Summen je Ort und Monat und die gesamten Stunden je Monat. Damit ist das Ziele, drei Tabellen zu konsolidieren, bereits erreicht.

Aber, die Tabelle gefällt dir so nicht. Formatiere sie deshalb über das Menü Start/Formatvorlagen als Tabelle. Vergiss nicht, den Haken bei “Tabelle hat Überschriften” zu setzen. So sieht es dann aus:

Gliederungsebene 1

Gliederungsebene 2

Nützlich wären jetzt noch Zeilensummen. Klicke dazu in die Märzspalte und rufe mit der rechten Maustaste “Zeilen/Spalten einfügen / Tabellenspalte nach rechts” auf. Ändere die Überschrift auf “Summe”. Schreibe in Gliederungsansicht 2 jetzt in die erste Summenzelle z.B. die Formel:

 =SUMME(Tabelle1[@[Jan]:[Mrz]])

Mit dem Klick auf Enter steht diese Formel in allen Zeilen.

Überschreibe noch “Spalte 1” mit “Mitarbeiter”, “Spalte 2” mit “Ort” und passe die Spaltenbreiten an. Formatiere die Ergebniszeile evtl. noch fett und fülle sie mit einer anderen Farbe. Das Ergebnis könnte dann so aussehen:

Bei Änderungen in den Meldemappen (“Ort1.xlsx” usw.) ändern sich auch die Daten in der konsolidierten Tabelle.