Alle anzeigen

Alle ausblenden

Zum Erstellen von Zusammenfassungen und Berichten mit Ergebnissen aus verschiedenen Arbeitsblättern können Sie Daten aus den einzelnen Arbeitsblättern in einem Hauptarbeitsblatt konsolidieren. Die Arbeitsblätter können sich hierbei in derselben Arbeitsmappe wie das Hauptarbeitsblatt oder in anderen Arbeitsmappen befinden. Wenn Sie Daten konsolidieren, werden die Daten zusammengetragen, wodurch Ihnen die regelmäßige oder gelegentliche Aktualisierung und Zusammenfassung erleichtert wird.

Wenn Sie beispielsweise über ein Arbeitsblatt mit den Ausgaben für jede Ihrer regionalen Niederlassungen verfügen, können Sie eine Konsolidierung verwenden, um mit diesen Zahlen ein Arbeitsblatt der gesamten Firmenausgaben zu erstellen. Dieses Hauptarbeitsblatt kann die Gesamtumsatzzahlen und Mittelwerte, den aktuellen Lagerbestand sowie die meistverkauften Produkte für das gesamte Unternehmen enthalten.

Verwenden Sie zum Konsolidieren von Daten den Befehl Konsolidieren auf der Registerkarte Daten in der Gruppe Datentools.

Was möchten Sie tun?

Konsolidieren nach Position

1. Richten Sie die Daten ein, die auf jedem einzelnen Arbeitsblatt konsolidiert werden sollen.

   Einrichten der Daten

   • Stellen Sie sicher, dass die einzelnen Datenbereiche in Form einer Liste (Liste: Eine Reihe von Zeilen, die zusammengehörende Daten oder eine Reihe von Zeilen enthalten, für die Sie mithilfe des Befehls "Liste erstellen" die Funktion als Datenblatt festlegen.) bereitgestellt werden, in der jede Spalte in der ersten Zeile eine Beschriftung hat, ähnliche Daten enthält und in der keine leeren Zeilen oder Spalten vorhanden sind.
   • Verwenden Sie für jeden Bereich ein eigenes Arbeitsblatt. Setzen Sie keine Bereiche auf das Arbeitsblatt, das für die Konsolidierung vorgesehen ist.
   • Stellen Sie sicher, dass jeder Bereich über dasselbe Layout verfügt.
   • Benennen Sie jeden Bereich: Wählen Sie den gesamten Bereich aus, und klicken Sie dann auf der Registerkarte Formeln in der Gruppe Benannte Zellen auf den Pfeil neben Bereich benennen. Geben Sie einen Namen für den Bereich in das Feld Name ein.
2. Klicken Sie auf die obere linke Zelle des Bereichs, in dem die konsolidierten Daten auf dem Hauptarbeitsblatt angezeigt werden sollen.

    Hinweis    Stellen Sie sicher, dass rechts und unter dieser Zelle ausreichend Zellen vorhanden sind, um die konsolidierten Daten aufzunehmen. Mit dem Befehl Konsolidieren wird der Bereich nach Bedarf aufgefüllt.

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

   

4. Klicken Sie im Feld Funktion auf die Zusammenfassungsfunktion (Zusammenfassungsfunktion: Eine Berechnungsart, die Quelldaten in einem PivotTable-Bericht oder in einer Konsolidierungstabelle bzw. beim Einfügen von automatischen Teilergebnissen in einer Liste oder Datenbank zusammenführt. Zu den Zusammenfassungsfunktionen gehören u.a. die Funktionen "Summe", "Anzahl" und "Mittelwert".), die in Microsoft Office Excel für das Konsolidieren von Daten verwendet werden soll.
5. Wenn sich das Arbeitsblatt in einer anderen Arbeitsmappe befindet, klicken Sie auf Durchsuchen, um nach der Datei zu suchen, und klicken Sie dann auf OK, um das Dialogfeld Durchsuchen zu schließen.

   Der Dateipfad wird in das Feld Bezug eingegeben, darauf folgt ein Ausrufezeichen.

6. Geben Sie den Namen ein, mit dem Sie den Bereich benannt haben, und klicken Sie dann auf Hinzufügen. Wiederholen Sie diesen Schritt für jeden Bereich.
7. Entscheiden Sie, wie die Konsolidierung aktualisiert werden soll. Führen Sie eine der folgenden Aktionen aus:

   • Aktivieren Sie das Kontrollkästchen Verknüpfungen mit Quelldaten, damit die Konsolidierung bei einer Änderung der Quelldaten automatisch aktualisiert wird.

      Wichtig   Sie können dieses Kontrollkästchen nur aktivieren, wenn sich das Arbeitsblatt in einer anderen Arbeitsmappe befindet. Sobald Sie das Kontrollkästchen aktiviert haben, können Sie die in der Konsolidierung enthaltenen Zellen und Bereiche nicht mehr ändern.

   • Deaktivieren Sie das Kontrollkästchen Verknüpfungen mit Quelldaten, damit Sie die Konsolidierung manuell aktualisieren können, indem Sie die enthaltenen Zellen und Bereiche ändern.
8. Lassen Sie die Felder unter Beschriftung aus leer. Excel kopiert nicht die Beschriftungen für Zeilen oder Spalten der Quellbereiche in die Konsolidierung. Wenn Sie Beschriftungen für die konsolidierten Daten wünschen, kopieren Sie diese aus einem der Quellbereiche, oder geben Sie diese manuell ein.

 Seitenanfang

Konsolidieren nach Kategorie

1. Richten Sie die Daten ein, die auf jedem einzelnen Arbeitsblatt konsolidiert werden sollen.

   Einrichten der Daten

   • Stellen Sie sicher, dass die einzelnen Datenbereiche in Form einer Liste (Liste: Eine Reihe von Zeilen, die zusammengehörende Daten oder eine Reihe von Zeilen enthalten, für die Sie mithilfe des Befehls "Liste erstellen" die Funktion als Datenblatt festlegen.) bereitgestellt werden, in der jede Spalte in der ersten Zeile eine Beschriftung besitzt, ähnliche Daten enthält und in der keine leeren Zeilen oder Spalten vorhanden sind.
   • Verwenden Sie für jeden Bereich ein eigenes Arbeitsblatt. Setzen Sie keine Bereiche auf das Arbeitsblatt, das für die Konsolidierung vorgesehen ist.
   • Stellen Sie sicher, dass die Beschriftungen für Spalten oder Zeilen, die Sie kombinieren möchten, über eine identische Schreibweise und Großschreibung verfügen. Die Beschriftungen "Jahresdurchschn." und "Jahresdurchschnitt" beispielsweise sind unterschiedlich und werden daher nicht konsolidiert.
   • Benennen Sie jeden Bereich: Wählen Sie den gesamten Bereich aus, und klicken Sie dann auf der Registerkarte Formeln in der Gruppe Benannte Zellen auf den Pfeil neben Bereich benennen. Geben Sie einen Namen für den Bereich in das Feld Name ein.
2. Klicken Sie auf die obere linke Zelle des Bereichs, in dem die konsolidierten Daten auf dem Hauptarbeitsblatt angezeigt werden sollen.

    Hinweis    Stellen Sie sicher, dass rechts und unter dieser Zelle ausreichend Zellen vorhanden sind, um die konsolidierten Daten aufzunehmen. Mit dem Befehl Konsolidieren wird der Bereich nach Bedarf aufgefüllt.

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

   

4. Klicken Sie im Feld Funktion auf die Zusammenfassungsfunktion (Zusammenfassungsfunktion: Eine Berechnungsart, die Quelldaten in einem PivotTable-Bericht oder in einer Konsolidierungstabelle bzw. beim Einfügen von automatischen Teilergebnissen in einer Liste oder Datenbank zusammenführt. Zu den Zusammenfassungsfunktionen gehören u.a. die Funktionen "Summe", "Anzahl" und "Mittelwert".), die in Excel für das Konsolidieren von Daten verwendet werden soll.
5. Wenn sich das Arbeitsblatt in einer anderen Arbeitsmappe befindet, klicken Sie auf Durchsuchen, um nach der Datei zu suchen, und klicken Sie dann auf OK, um das Dialogfeld Durchsuchen zu schließen.

   Der Dateipfad wird in das Feld Bezug eingegeben, darauf folgt ein Ausrufezeichen.

6. Geben Sie den Namen ein, mit dem Sie den Bereich benannt haben, und klicken Sie dann auf Hinzufügen. Wiederholen Sie diesen Schritt für jeden Bereich.
7. Entscheiden Sie, wie die Konsolidierung aktualisiert werden soll. Führen Sie eine der folgenden Aktionen aus:

   • Aktivieren Sie das Kontrollkästchen Verknüpfungen mit Quelldaten, damit die Konsolidierung bei einer Änderung der Quelldaten automatisch aktualisiert wird.

      Wichtig   Sie können dieses Kontrollkästchen nur aktivieren, wenn sich das Arbeitsblatt in einer anderen Arbeitsmappe befindet. Sobald Sie das Kontrollkästchen aktiviert haben, können Sie die in der Konsolidierung enthaltenen Zellen und Bereiche nicht mehr ändern.

   • Deaktivieren Sie das Kontrollkästchen Verknüpfungen mit Quelldaten, damit Sie die Konsolidierung manuell aktualisieren können, indem Sie die enthaltenen Zellen und Bereiche ändern.
8. Aktivieren Sie die Kontrollkästchen unter Beschriftung aus, mit denen angegeben wird, wo sich die Beschriftungen in den Quellbereichen befinden: Oberster Zeile, Linker Spalte oder beides.

  Hinweise  

• 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 Kategorien, die nicht konsolidiert werden sollen, über eindeutige Beschriftungen verfügen, die sich nur in einem Quellbereich befinden.

 Seitenanfang

Konsolidieren nach Formel

1. Kopieren Sie die Spalten- oder Zeilenbeschriftungen, die Sie für die konsolidierten Daten verwenden wollen, in das Hauptarbeitsblatt, oder geben Sie diese ein.
2. Klicken Sie auf die Zelle, die konsolidierte Daten aufnehmen soll.
3. Geben Sie eine Formel ein, die einen Zellbezug auf die Quellzellen auf jedem Arbeitsblatt oder einen 3D-Bezug mit Daten enthält, die Sie konsolidieren möchten. Führen Sie hinsichtlich der Zellbezüge eine der folgenden Aktionen aus:

   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 "Sales" (in Zelle B4), "HR" (in Zelle F5) und "Marketing" (in Zelle B9) in Zelle A2 des Hauptarbeitsblatts konsolidieren möchten, geben Sie Folgendes ein:

     

     Tipp  Zur Eingabe eines Zellbezugs, z. B. "Umsatz!B4", in eine Formel, ohne die gesamte Formel einzutippen, geben Sie die Formel bis zu der Stelle ein, an der Sie den Bezug benötigen, klicken Sie auf die Registerkarte des Arbeitsblatts, und klicken Sie dann auf die Zelle.

   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 der Zelle A2 der Arbeitsblätter "Umsatz" bis einschließlich "Marketing" konsolidieren möchten, geben Sie in Zelle A2 des Hauptarbeitsblatts Folgendes ein:

     

 Hinweis   Ist das Arbeitsblatt für die automatische Berechnung von Formeln eingerichtet, wird eine Konsolidierung mittels Formel stets automatisch aktualisiert, wenn die Daten in den einzelnen Arbeitsblättern geändert werden.

 Seitenanfang

Verwenden eines PivotTable-Berichts zur Konsolidierung von Daten

Sie können einen PivotTable-Bericht (PivotTable-Bericht: Ein interaktiver Kreuztabellenbericht in Excel, der Daten (beispielsweise Datensätze aus einer Datenbank) zusammenfasst und analysiert. Die Daten können aus unterschiedlichen Quellen stammen, einschließlich Quellen außerhalb von Excel.) aus mehreren Konsolidierungsbereichen erstellen. Diese Methode ist der Konsolidierung nach Kategorie vergleichbar, bietet für die Neuorganisation der Kategorien jedoch mehr Flexibilität. Weitere Informationen finden Sie unter Konsolidieren mehrerer Arbeitsblätter in einem PivotTable-Bericht

 Seitenanfang