Konsolidieren von Daten in mehreren Arbeitsblättern

Konsolidieren von Daten in mehreren Arbeitsblättern

Um Ergebnisse aus separaten Arbeitsblättern in einem Bericht zusammenzufassen, können Sie Daten aus den einzelnen Arbeitsblättern in einem Masterarbeitsblatt konsolidieren. Die Arbeitsblätter können sich in derselben Arbeitsmappe wie das Masterarbeitsblatt oder in anderen Arbeitsmappen befinden. Beim Konsolidieren stellen Sie Daten zusammen, sodass Sie die Daten einfacher aktualisieren und nach Bedarf 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 Daten oft konsolidieren, kann es hilfreich sein, als Grundlage für die Arbeitsblätter eine Arbeitsblattvorlage mit einheitlichem Layout zu verwenden. 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 zum Konsolidieren von Daten: nach Kategorie oder nach Position.

Konsolidierung nach Position: Wenn die Daten in den Quellbereichen in derselben Reihenfolge angeordnet sind und die gleichen Datenbeschriftungen verwendet werden. 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. Wenn Sie eine flexiblere Konsolidierung nach Kategorie wünschen, sollten Sie stattdessen eine PivotTable erstellen.

Hinweis : Die in diesem Artikel verwendeten Beispiele wurden in Excel 2016 erstellt. Ihre Ansicht kann daher abhängig von der von Ihnen verwendeten Version anders aussehen. Die Schritte sind jedoch dieselben.

Konsolidierungsschritte

  1. Falls noch nicht geschehen, richten Sie in jedem Arbeitsblatt, das die zu konsolidierenden Daten enthält, die Daten durch Ausführen der folgenden Schritte ein:

    • Stellen Sie sicher, dass die einzelnen Datenbereiche in Form einer Liste bereitgestellt werden, in der jede Spalte in der ersten Zeile eine Beschriftung aufweist sowie ähnliche Daten enthält und in der keine leeren Zeilen oder Spalten vorhanden sind.

    • Verwenden Sie für jeden Bereich ein eigenes Arbeitsblatt, aber geben Sie nichts auf dem Masterarbeitsblatt ein, das für die Konsolidierung vorgesehen ist. Dieses Blatt wird von Excel ausgefüllt.

    • Stellen Sie sicher, dass jeder Bereich über dasselbe Layout verfügt.

  2. Klicken Sie im Hauptarbeitsblatt auf die obere linke Zelle des Bereichs, in dem die konsolidierten Daten angezeigt werden sollen.

    Hinweis : Stellen Sie sicher, dass rechts und unter dieser Zelle ausreichend Zellen für die konsolidierten Daten vorhanden sind, damit die im Zielarbeitsblatt vorhandenen Daten nicht durch die zu konsolidierenden Daten überschrieben werden.

  3. Klicken Sie auf der Registerkarte Daten in der Gruppe Datentools auf Konsolidieren.

    Gruppe "Datentools" auf der Registerkarte "Daten"

  4. Klicken Sie im Feld Funktion auf die Zusammenfassungsfunktion, die in Excel für das Konsolidieren der Daten verwendet werden soll. Die Standardfunktion ist SUMME.

    Nachfolgend sehen Sie ein Beispiel, in dem drei Arbeitsblattbereiche ausgewählt sind.

    Dialogfeld für die Datenkonsolidierung

  5. Auswählen Ihrer Daten

    • Wenn sich das Arbeitsblatt mit den zu konsolidierenden Daten in einer anderen Arbeitsmappe befindet, klicken Sie zuerst auf Durchsuchen, um nach der Arbeitsmappe zu suchen, und klicken Sie dann auf OK, um das Dialogfeld Durchsuchen zu schließen. Excel gibt im Feld Bezug den Dateipfad gefolgt von einem Ausrufezeichen ein, und Sie können mit dem Auswählen Ihrer Daten fortfahren.

    Klicken Sie als Nächstes im Feld Bezug auf die Schaltfläche Dialog reduzieren, um die Daten im Arbeitsblatt auszuwählen.

    Datenkonsolidierung – Dialog reduzieren

    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.

  6. Klicken Sie im Dialogfeld Konsolidieren auf Hinzufügen, und wiederholen Sie dann diesen Vorgang, um alle gewünschten Bereiche hinzuzufügen.

  7. Automatische und manuelle Aktualisierung: Wenn Excel die Konsolidierungstabelle bei einer Änderung der Quelldaten automatisch aktualisieren soll, aktivieren Sie das Kontrollkästchen Verknüpfungen mit Quelldaten. Wenn das Kontrollkästchen deaktiviert ist, 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.

    • Falls Sie Bereiche ändern müssen, nachdem Sie diese hinzugefügt haben, können Sie im Dialogfeld "Konsolidieren" auf die einzelnen Bereiche klicken und sie aktualisieren, wenn sie im Feld "Bezug" angezeigt werden. Klicken Sie anschließend auf "Hinzufügen". Dadurch wird ein neuer Bereichsbezug erstellt. Vor der erneuten Konsolidierung müssen Sie daher den vorherigen Bezug löschen. Wählen Sie einfach den alten Verweis aus, und drücken Sie ENTF.

  8. Klicken Sie auf "OK". Excel generiert dann die Konsolidierung für Sie. Sie ist unformatiert, sodass Sie die Formatierung vornehmen müssen, aber Sie müssen das nur einmal tun, sofern Sie die Konsolidierung nicht erneut ausführen.

    • Für Beschriftungen, die nicht den Beschriftungen in den anderen Quellbereichen entsprechen, werden in der Konsolidierung separate Zeilen oder Spalten erstellt.

    • Stellen Sie sicher, dass Kategorien, die nicht konsolidiert werden sollen, über eindeutige Beschriftungen verfügen, die sich nur in einem Quellbereich befinden.

Konsolidieren von Daten mithilfe einer Formel

  • Wenn sich die zu konsolidierenden Daten in verschiedenen Zellen auf unterschiedlichen 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 "Vertrieb" (in Zelle B4), "Personal" (in Zelle F5) und "Marketing" (in Zelle B9) in Zelle A2 des Masterarbeitsblatts konsolidieren möchten, geben Sie Folgendes ein:

    Excel-Formel mit Bezug auf mehrere Arbeitsblätter

    Tipp : Zur Eingabe eines Zellbezugs (z. B. "Vertrieb!B4") in eine Formel geben Sie die Formel bis zu der Stelle ein, an der Sie den Bezug benötigen, klicken Sie auf das Arbeitsblattregister, und klicken Sie dann auf die Zelle. Excel fügt den Blattnamen und die Zelladresse für Sie ein. Bedenken Sie, dass Formeln wie diese fehleranfällig sind, weil schnell mal versehentlich die falsche Zelle ausgewählt werden kann. Außerdem kann es schwierig sein, einen Fehler zu erkennen, nachdem eine Formel eingegeben wurde.

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

    Geben Sie eine Formel mit einem 3D-Bezug ein, der einen Bezug auf einen Bereich von Arbeitsblattnamen verwendet. Wenn Sie beispielsweise die Daten aus Zelle A2 der Arbeitsblätter "Vertrieb" bis einschließlich "Marketing" konsolidieren möchten, geben Sie in Zelle E5 des Masterarbeitsblatts Folgendes ein:

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

Wussten Sie schon?

Wenn Sie nicht über ein Office 365-Abonnement oder die neueste Office-Version verfügen, können Sie Office jetzt testen:

Testen Sie Office 365 oder die aktuelle Version von Excel

Haben Sie eine Frage zu einer bestimmten Funktion?

Posten Sie eine Frage im Excel-Communityforum.

Helfen Sie uns, Excel zu verbessern

Haben Sie Vorschläge, wie wir die kommende Version von Excel verbessern könnten? Wenn ja, lesen Sie die Themen unter Excel User Voice

Siehe auch

Übersicht über Formeln in Excel

Vermeiden defekter Formeln

Suchen und Korrigieren von Fehlern in Formeln

Excel-Tastenkombinationen und -Funktionstasten

Excel-Funktionen (alphabetisch)

Excel-Funktionen (nach Kategorie)

Ihre Fähigkeiten erweitern
Schulung erkunden
Neue Funktionen als Erster erhalten
An Office Insider teilnehmen

War diese Information hilfreich?

Vielen Dank für Ihr Feedback!

Vielen Dank für Ihr Feedback. Es klingt, als ob es hilfreich sein könnte, Sie mit einem unserer Office-Supportmitarbeiter zu verbinden.

×