Konsolidieren von Daten in mehreren Arbeitsblättern

Um Ergebnisse aus separaten Arbeitsblättern zusammenzufassen und zu melden, können Sie Daten aus den einzelnen Arbeitsblättern in einem Masterarbeitsblatt konsolidieren. Die Blätter können sich in derselben Arbeitsmappe wie das Masterarbeitsblatt oder in anderen Arbeitsmappen befinden. Wenn Sie Daten konsolidieren, stellen Sie Daten zusammen, sodass Sie bei Bedarf einfacher aktualisieren und aggregieren können.

Wenn Sie z. B. ein Arbeitsblatt mit den Ausgaben für jede Ihrer Regionalniederlassungen haben, können Sie eine Konsolidierung verwenden, um aus diesen Zahlen ein Masterarbeitsblatt mit Unternehmensausgaben zu erstellen. Dieses Masterarbeitsblatt kann auch Verkaufssummen und -mittelwerte, aktuelle Lagerbestände und die meistverkauften Produkte für das gesamte Unternehmen enthalten.

Tipp: Wenn Sie Häufig Daten konsolidieren, kann es hilfreich sein, neue Arbeitsblätter aus einer Arbeitsblattvorlage zu erstellen, die ein konsistentes Layout verwendet. Weitere Informationen zu Vorlagen finden Sie unter Erstellen einer Vorlage. Dies ist auch ein idealer Zeitpunkt, um Ihre Vorlage mit Excel-Tabellen einzurichten.

Es gibt zwei Möglichkeiten, Daten zu konsolidieren, entweder nach Position oder Kategorie.

Konsolidierung nach Position: Die Daten in den Quellbereichen haben die gleiche Reihenfolge und verwenden die gleichen Bezeichnungen. Verwenden Sie diese Methode zum Konsolidieren von Daten aus einer Reihe von Arbeitsblättern (z. B. aus Arbeitsblättern mit Budgets der Abteilungen), die anhand derselben Vorlage erstellt wurden.

Konsolidierung nach Kategorie: Wenn die Daten in den Quellbereichen nicht in derselben Reihenfolge angeordnet sind, aber die gleichen Datenbeschriftungen verwendet werden. Verwenden Sie diese Methode zum Konsolidieren von Daten aus einer Reihe von Arbeitsblättern, die unterschiedliche Layouts aufweisen, aber die gleichen Datenbeschriftungen enthalten.

Das Konsolidieren von Daten nach Kategorie ist mit dem Erstellen einer PivotTable vergleichbar. In einer PivotTable können Sie jedoch die Kategorien problemlos neu anordnen. Erwägen Sie das Erstellen einer PivotTable , wenn Sie eine flexiblere Konsolidierung nach Kategorie benötigen.

Hinweis: Die Beispiele in diesem Artikel wurden mit Excel 2016 erstellt. Ihre Ansicht kann sich zwar unterscheiden, wenn Sie eine andere Version von Excel verwenden, die Schritte sind jedoch identisch.

Führen Sie die folgenden Schritte aus, um mehrere Arbeitsblätter in einem Masterarbeitsblatt zu konsolidieren:

Falls noch nicht geschehen, richten Sie die Daten in jedem konstituierenden Blatt wie folgt ein:
- Stellen Sie sicher, dass jeder Datenbereich im Listenformat vorliegt. Jede Spalte muss eine Bezeichnung (Kopfzeile) in der ersten Zeile enthalten und ähnliche Daten enthalten. In der Liste dürfen keine leeren Zeilen oder Spalten vorhanden sein.
- Platzieren Sie jeden Bereich auf einem separaten Arbeitsblatt, aber geben Sie nichts in das Masterarbeitsblatt ein, in dem Sie die Daten konsolidieren möchten. Excel wird dies für Sie tun.
- Stellen Sie sicher, dass jeder Bereich das gleiche Layout hat.
Klicken Sie im Hauptarbeitsblatt auf die obere linke Zelle des Bereichs, in dem die konsolidierten Daten angezeigt werden sollen.

Hinweis: Um das Überschreiben vorhandener Daten im Masterarbeitsblatt zu vermeiden, stellen Sie sicher, dass Sie genügend Zellen rechts und unterhalb dieser Zelle für die konsolidierten Daten belassen.
Klicken Sie auf Daten>Konsolidieren (in der Gruppe Datentools ).
Klicken Sie im Feld Funktion auf die Zusammenfassungsfunktion, die in Excel für das Konsolidieren der Daten verwendet werden soll. Die Standardfunktion ist SUMME.

Hier sehen Sie ein Beispiel, in dem drei Arbeitsblattbereiche ausgewählt wurden:
Wählen Sie Ihre Daten aus.

Klicken Sie als Nächstes im Feld Verweis auf die Schaltfläche Reduzieren , um den Bereich zu verkleinern und die Daten im Arbeitsblatt auszuwählen.

Klicken Sie auf das Arbeitsblatt mit den zu konsolidierenden Daten, wählen Sie die Daten aus, und klicken Sie dann auf der rechten Seite auf die Schaltfläche Dialogfeld erweitern, um zum Dialogfeld Konsolidieren zurückzukehren.Wenn sich ein Arbeitsblatt mit daten, die Sie konsolidieren müssen, in einer anderen Arbeitsmappe befindet, klicken Sie auf Durchsuchen , um diese Arbeitsmappe zu suchen. Nach dem Suchen und Klicken auf OK gibt Excel den Dateipfad in das Feld Verweis ein und fügt diesem Pfad ein Ausrufezeichen an. Anschließend können Sie weitere Daten auswählen.

Hier sehen Sie ein Beispiel, in dem drei Arbeitsblattbereiche ausgewählt wurden:
Klicken Sie im Popupfenster Konsolidieren auf Hinzufügen. Wiederholen Sie diesen Vorgang, um alle Bereiche hinzuzufügen, die Sie konsolidieren.
Automatische und manuelle Aktualisierung: Wenn Excel Ihre Konsolidierungstabelle automatisch aktualisieren soll, wenn sich die Quelldaten ändern, aktivieren Sie einfach das Kontrollkästchen Verknüpfungen zu Quelldaten erstellen . Wenn dieses Kontrollkästchen deaktiviert bleibt, können Sie die Konsolidierung manuell aktualisieren.
Hinweise:
- Wenn sich die Quell- und Zielbereiche auf demselben Arbeitsblatt befinden, können Sie keine Verknüpfungen erstellen.
- Wenn Sie den Umfang eines Bereichs ändern oder einen Bereich ersetzen müssen, klicken Sie im Popup "Konsolidieren" auf den Bereich, und aktualisieren Sie ihn mithilfe der oben genannten Schritte. Dadurch wird ein neuer Bereichsverweis erstellt, sodass Sie den vorherigen bereichsverweis löschen müssen, bevor Sie sie erneut konsolidieren. Wählen Sie einfach den alten Verweis aus, und drücken Sie die ENTF-TASTE.
Klicken Sie auf OK, und Excel generiert die Konsolidierung für Sie. Optional können Sie Formatierungen anwenden. Die Formatierung ist nur einmal erforderlich, es sei denn, Sie führen die Konsolidierung erneut aus.
- Für Beschriftungen, die nicht den Beschriftungen in den anderen Quellbereichen entsprechen, werden in der Konsolidierung eigene Zeilen oder Spalten erstellt.
- Stellen Sie sicher, dass alle Kategorien, die Sie nicht konsolidieren möchten, über eindeutige Bezeichnungen verfügen, die nur in einem Quellbereich angezeigt werden.

Wenn sich die zu konsolidierenden Daten in verschiedenen Zellen auf verschiedenen Arbeitsblättern befinden:

Geben Sie für jedes einzelne Arbeitsblatt eine Formel mit Zellbezügen auf das entsprechende Arbeitsblatt ein. Wenn Sie beispielsweise Daten aus den Arbeitsblättern "Umsatz" (in Zelle B4), "HR" (in Zelle F5) und "Marketing" (in Zelle B9) in Zelle A2 des Hauptarbeitsblatts konsolidieren möchten, geben Sie Folgendes ein:

Excel-Formel mit Bezug auf mehrere Arbeitsblätter

Tipp: So geben Sie einen Zellbezug ein, z. B. Sales! B4: Geben Sie in einer Formel ohne Eingabe die Formel bis zu dem Punkt ein, an dem Sie den Verweis benötigen, klicken Sie dann auf die Registerkarte des Arbeitsblatts, und klicken Sie dann auf die Zelle. Excel vervollständigen den Blattnamen und die Zellenadresse für Sie. HINWEIS: Formeln können in solchen Fällen fehleranfällig sein, da es sehr einfach ist, versehentlich die falsche Zelle auszuwählen. Es kann auch schwierig sein, einen Fehler nach der Eingabe einer komplexen Formel zu erkennen.

Wenn sich die zu konsolidierenden Daten in den gleichen Zellen auf verschiedenen Arbeitsblättern befinden:

Geben Sie eine Formel mit einem 3D-Bezug ein, der einen Bezug auf einen Bereich von Arbeitsblattnamen verwendet. Zum Konsolidieren von Daten in Zellen A2 von "Sales through Marketing inclusive" geben Sie beispielsweise in Zelle E5 des Masterarbeitsblatts Folgendes ein:

Excel-Formel mit 3D-Bezug auf Arbeitsblätter