Konsolidieren von Daten in mehreren Arbeitsblättern

Konsolidieren von Daten in mehreren Arbeitsblättern

Um Ergebnisse aus getrennten Arbeitsblättern zusammenzufassen und zu melden, können Sie Daten aus jedem Blatt 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, damit Sie die Aktualisierung und Aggregatfunktion 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 Daten häufig konsolidieren, kann es hilfreich sein, neue Arbeitsblätter aus einer Arbeitsblattvorlage zu erstellen, die ein einheitliches 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 haben dieselbe Reihenfolge 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. 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. 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:

  1. Wenn Sie dies noch nicht tun, richten Sie die Daten in jedem einzelnen Arbeitsblatt ein, indem Sie die folgenden Schritte tun:

    • Stellen Sie sicher, dass jeder Datenbereich im Listenformat angezeigt wird. Jede Spalte muss eine Bezeichnung (Kopfzeile) in der ersten Zeile und ähnliche Daten enthalten. In der Liste dürfen keine leeren Zeilen oder Spalten vorhanden sein.

    • Legen Sie jeden Bereich auf einem separaten Arbeitsblatt fest, geben Sie aber nichts in das Hauptarbeitsblatt 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.

  2. 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 hinterlassen.

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

    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.

    Hier ist ein Beispiel, in dem drei Arbeitsblattbereiche ausgewählt wurden:

    Dialogfeld für die Datenkonsolidierung

  5. Wählen Sie Ihre Daten aus.

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

    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üssen, in einer anderen Arbeitsmappe befindet, klicken Sie auf Durchsuchen, um die Arbeitsmappe zu suchen. Nach dem Suchen und Klicken auf OKExcel der Dateipfad in das Feld Referenz ein, und fügen Sie ein Ausrufezeichen an diesen Pfad an. Sie können dann mit der Auswahl weiterer Daten fortfahren.

    Hier ist ein Beispiel, in dem drei Arbeitsblattbereiche ausgewählt wurden:

    Dialogfeld für die Datenkonsolidierung

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

  7. Automatische und manuelle Aktualisierung: Wenn Sie Excel Konsolidierungstabelle automatisch aktualisieren möchten, wenn sich die Quelldaten ändern, aktivieren Sie einfach das Kontrollkästchen Verknüpfungen mit 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 mit den oben genannten Schritten. Dadurch wird ein neuer Bereichsverweis erstellt, sodass Sie den vorherigen Bereich löschen müssen, bevor Sie ihn erneut konsolidieren. Wählen Sie einfach den alten Verweis aus, und drücken Sie die DELETE-TASTE.

  8. Klicken Sie auf OK,und Excel wird die Konsolidierung für Sie generiert. Optional können Sie Formatierungen anwenden. Es ist nur einmal erforderlich, die Konsolidierung zu formatieren, 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, eindeutige Bezeichnungen haben, 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. Umsatz! 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 Arbeitsblattregisterkarte, und klicken Sie dann auf die Zelle. Excel wird der Blattname und die Zellenadresse für Sie abgeschlossen. 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, nach der Eingabe einer komplexen Formel einen Fehler zu erkennen.

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

Geben Sie eine Formel mit einem 3D-Bezug ein, der einen Bezug auf einen Bereich von Arbeitsblattnamen verwendet. Um z. B. Daten in den Zellen A2 von Vertrieb bis Einschließlich Marketing zu konsolidieren, geben Sie in Zelle E5 des Masterarbeitsblatts 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.

×