Konsolidieren von Daten in mehreren Arbeitsblättern

Konsolidieren von Daten in mehreren Arbeitsblättern

Zum zusammenfassen und Berichten von Ergebnissen aus separaten Arbeitsblättern können Sie Daten aus jedem Blatt in einem Masterarbeitsblatt konsolidieren. Die Blätter können sich in der gleichen 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 zum Konsolidieren von Daten: entweder nach Position oder Kategorie.

Konsolidierung nach Position: die Daten in den Quellbereichen weisen dieselbe Reihenfolge auf und verwenden dieselben Beschriftungen. 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. Sie sollten eine PivotTable erstellen , wenn Sie eine flexiblere Konsolidierung nach Kategorie benötigen.

Hinweis: Die Beispiele in diesem Artikel wurden mit Excel 2016 erstellt. Obwohl ihre Ansicht unterschiedlich sein kann, wenn Sie eine andere Version von Excel verwenden, sind die Schritte identisch.

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

  1. Wenn Sie dies noch nicht getan haben, richten Sie die Daten in jedem konstituierenden Arbeitsbogen wie folgt ein:

    • Stellen Sie sicher, dass jeder Datenbereich im Listenformat angezeigt wird. Jede Spalte muss über eine Beschriftung (Kopfzeile) in der ersten Zeile verfügen und ähnliche Daten enthalten. Es dürfen keine leeren Zeilen oder Spalten an einer beliebigen Stelle in der Liste vorhanden sein.

    • Setzen Sie jeden Bereich auf ein separates Arbeitsblatt, geben Sie jedoch nichts in das Masterarbeitsblatt ein, in dem Sie die Daten konsolidieren möchten. Excel wird dies für Sie ausführen.

    • Stellen Sie sicher, dass jeder Bereich das gleiche Layout aufweist.

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

    Hinweis: Um zu vermeiden, dass vorhandene Daten im Masterarbeitsblatt überschrieben werden, stellen Sie sicher, dass Sie genügend Zellen auf der rechten Seite und unter dieser Zelle für die konsolidierten Daten beibehalten.

  3. Klicken Sie auf Daten>konsolidieren (in der Gruppe Daten Tools ).

    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.

    Im folgenden finden Sie ein Beispiel für die Auswahl von drei Arbeitsblattbereichen:

    Dialogfeld für die Datenkonsolidierung

  5. Wählen Sie Ihre Daten aus.

    Klicken Sie als nächstes im Feld Bezug auf die Schaltfläche reduzieren , um den Bereich zu verkleinern und 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.

    Wenn sich ein Arbeitsblatt, das Daten enthält, die Sie konsolidieren möchten, in einer anderen Arbeitsmappe befindet, klicken Sie auf Durchsuchen , um nach dieser Arbeitsmappe zu suchen. Nachdem Sie nach dem Auffinden und klicken auf OKgeklickt haben, gibt Excel den Dateipfad in das Feld Verweis ein und fügt ein Ausrufezeichen an diesen Pfad an. Sie können dann fortfahren, weitere Daten auszuwählen.

    Im folgenden finden Sie ein Beispiel für die Auswahl von drei Arbeitsblattbereichen:

    Dialogfeld für die Datenkonsolidierung

  6. Klicken Sie im Popupfenster konsolidieren auf Hinzufügen. Wiederholen Sie diesen Vorgang, um alle Bereiche, die Sie konsolidieren, hinzuzufügen.

  7. Automatische und manuelle Aktualisierung: Wenn Excel Ihre Konsolidierungstabelle automatisch aktualisieren soll, wenn sich die Quelldaten ändern, aktivieren Sie einfach das Kontrollkästchen Verknüpfungen mit Quelldaten erstellen . Wenn dieses Kontrollkästchen nicht aktiviert 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.

    • Wenn Sie den Umfang eines Bereichs ändern oder einen Bereich ersetzen möchten, klicken Sie im Popupfenster konsolidieren auf den Bereich, und aktualisieren Sie ihn mit den obigen Schritten. Dadurch wird ein neuer Bereichsbezug erstellt, sodass Sie das vorherige löschen müssen, bevor Sie es erneut konsolidieren. Wählen Sie einfach die alte Referenz und drücken Sie die ENTF-Taste.

  8. Klicken Sie auf OK, und Excel generiert die Konsolidierung für Sie. Optional können Sie Formatierungen anwenden. Es muss nur einmal formatiert werden, es sei denn, Sie führen eine erneute Konsolidierung durch.

    • 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, eindeutige Beschriftungen aufweisen, die nur in einem Quellbereich angezeigt werden.

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 "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 – beispielsweise Sales! B4 – geben Sie in einer Formel ohne Eingabe die Formel bis zu dem Punkt ein, an dem Sie den Bezug benötigen, klicken Sie dann auf die Registerkarte Arbeitsblatt, und klicken Sie dann auf die Zelle. Excel vervollständigen den Arbeitsplan Namen und die Zelladresse für Sie. Hinweis: Formeln in solchen Fällen können 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 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 Daten in den Zellen a2 aus Verkäufen durch Marketing inklusive konsolidieren möchten, geben Sie in Zelle E5 des Master Arbeitsblatts Folgendes ein:

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

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen, Unterstützung in der Answers Community erhalten oder aber ein neues Feature oder eine Verbesserung auf Excel User Voice vorschlagen.

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)

Benötigen Sie weitere Hilfe?

Ihre Office-Fähigkeiten erweitern
Schulungen erkunden
Neue Funktionen als Erster erhalten
Office Insider werden

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.

×