Konsolidieren von Daten in mehreren Arbeitsblättern

Um Ergebnisse aus separaten Arbeitsblättern zusammenzufassen und in einem Bericht zu melden, können Sie Daten aus den einzelnen Arbeitsblättern in ein Master-Arbeitsblatt konsolidieren. Die Arbeitsblätter können sich in derselben Arbeitsmappe wie das Master-Arbeitsblatt oder in anderen Arbeitsmappen befinden. Beim Konsolidieren stellen Sie Daten zusammen, sodass Sie 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.

Möglichkeiten zum Konsolidieren von Daten

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

Konsolidierung nach Position: Die Daten in den Quellbereichen haben die gleiche Reihenfolge und verwenden die gleichen 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 die Erstellung einer PivotTable , wenn Sie eine flexiblere Konsolidierung nach Kategorien benötigen.

So konsolidieren Sie

Führen Sie die folgenden Schritte aus, um mehrere Arbeitsblätter in einem Master-Arbeitsblatt zusammenzufassen:

1. Falls noch nicht geschehen, richten Sie die Daten in den einzelnen Blättern wie folgt ein:

   • Stellen Sie sicher, dass jeder Datenbereich das Listenformat aufweist. Jede Spalte muss in der ersten Zeile eine Bezeichnung (Kopfzeile) aufweisen und ähnliche Daten enthalten. Die Liste darf keine leeren Zeilen oder Spalten enthalten.
   • Legen Sie jeden Bereich auf einem separaten Arbeitsblatt an, aber geben Sie im Master-Arbeitsblatt keine Elemente ein, in denen Sie die Daten konsolidieren möchten. Excel übernimmt diese Aufgaben für Sie.
   • Stellen Sie sicher, dass jeder Bereich dasselbe 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 Master-Arbeitsblatt überschrieben werden, stellen Sie sicher, dass Sie rechts und unterhalb dieser Zelle genügend Zellen für die konsolidierten Daten belassen.

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

4. Klicken Sie im Feld Funktion auf die Zusammenfassungsfunktion, die Excel zum Konsolidieren der Daten verwenden soll. Die Standardfunktion ist SUMME.
   Im Folgenden finden Sie ein Beispiel, in dem drei Arbeitsblattbereiche ausgewählt wurden:
   

5. Wählen Sie Ihre Daten aus.
   Klicken Sie anschließend im Feld Bezug auf die Schaltfläche Reduzieren , um das Bedienfeld zu verkleinern und die Daten im Arbeitsblatt auszuwählen.
   
   Klicken Sie auf das Arbeitsblatt mit den zu konsolidierenden Daten, markieren Sie die Daten und klicken Sie dann rechts auf die Schaltfläche Dialogfeld erweitern , um zum Dialogfeld "Konsolidieren" zurückzukehren.

   Wenn sich ein Arbeitsblatt mit Daten, die konsolidieren müssen, in einer anderen Arbeitsmappe befindet, klicken Sie auf Durchsuchen , um diese Arbeitsmappe zu suchen. Nachdem Sie auf "OK" geklickt haben, gibt Excel den Dateipfad in das Feld "Verweis " ein und fügt diesem Pfad ein Ausrufezeichen hinzu. Anschließend können Sie mit der Auswahl anderer Daten fortfahren.
   Hier ist ein Beispiel, in dem drei Arbeitsblattbereiche ausgewählt wurden:
   

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

7. Automatische Updates im Vergleich zu manuellen Updates: Wenn Sie möchten, dass Excel Ihre Konsolidierungstabelle automatisch aktualisiert, 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.

   Hinweis

   • Wenn sich die Quell- und Zielbereiche auf demselben Arbeitsblatt befinden, können Sie keine Verknüpfungen erstellen.
   • Wenn Sie die Ausdehnung 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 löschen müssen, bevor Sie erneut konsolidieren. Wählen Sie einfach die alte Referenz aus, 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. Sie müssen nur einmal 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 aufweisen, die nur in einem Quellbereich angezeigt werden.

Konsolidieren von Daten mithilfe einer Formel

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:

 

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 beispielsweise Daten in den Zellen A2 von "Vertrieb" bis einschließlich "Marketing" zu konsolidieren, geben Sie in Zelle E5 des Master-Arbeitsblatts Folgendes ein:

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen oder in Communitys Unterstützung erhalten.

Siehe auch

Übersicht über Formeln in Excel

Vermeiden defekter Formeln

Suchen und Beheben von Fehlern in Formeln

Excel-Tastenkombinationen und -Funktionstasten

Excel-Funktionen (alphabetisch)

Excel-Funktionen (nach Kategorie)