Konsolidieren Sie Daten in mehreren Arbeitsblättern
Um Ergebnisse aus separaten Arbeitsblättern zusammenzufassen und zu melden, können Sie Daten aus jedem Blatt in einem Hauptarbeitsblatt 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 sie bei Bedarf einfacher aktualisieren und aggregieren können.
Wenn Sie beispielsweise ein Spesen-Arbeitsblatt für jede Ihrer regionalen Niederlassungen haben, können Sie die Konsolidierung verwenden, um diese Zahlen in ein Haupt-Unternehmens-Spesen-Arbeitsblatt zu überführen. Dieses Hauptarbeitsblatt kann auch Verkaufssummen und Durchschnittswerte, 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, Daten zu konsolidieren, entweder nach Position oder nach Kategorie.
Konsolidierung nach Position : Die Daten in den Quellbereichen haben die gleiche Reihenfolge und verwenden die gleichen Bezeichnungen. Verwenden Sie diese Methode, um Daten aus einer Reihe von Arbeitsblättern zu konsolidieren, z. B. Abteilungsbudget-Arbeitsblätter, die aus derselben Vorlage erstellt wurden.
Konsolidierung nach Kategorie : Wenn die Daten in den Quellbereichen nicht in derselben Reihenfolge angeordnet sind, aber dieselben Labels verwenden. Verwenden Sie diese Methode, um Daten aus einer Reihe von Arbeitsblättern mit unterschiedlichen Layouts, aber denselben Datenbeschriftungen zu konsolidieren.
Das Konsolidieren von Daten nach Kategorie ähnelt dem Erstellen einer PivotTable. Mit einer PivotTable können Sie die Kategorien jedoch leicht neu organisieren. 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 erstellt. Wenn Sie eine andere Version von Excel verwenden, kann Ihre Ansicht zwar abweichen, die Schritte sind jedoch dieselben.
Befolgen Sie diese Schritte, um mehrere Arbeitsblätter in einem Hauptarbeitsblatt zu konsolidieren:
Falls Sie dies noch nicht getan haben, richten Sie die Daten in jedem konstituierenden Blatt ein, indem Sie wie folgt vorgehen:
Stellen Sie sicher, dass jeder Datenbereich im Listenformat vorliegt. Jede Spalte muss in der ersten Zeile eine Beschriftung (Überschrift) haben und ähnliche Daten enthalten. Die Liste darf nirgendwo leere Zeilen oder Spalten enthalten.
Legen Sie jeden Bereich auf ein separates Arbeitsblatt, aber geben Sie nichts in das Hauptarbeitsblatt ein, in dem Sie die Daten konsolidieren möchten. Excel übernimmt dies für Sie.
Stellen Sie sicher, dass jeder Bereich das gleiche Layout hat.
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 Hauptarbeitsblatt überschrieben werden, stellen Sie sicher, dass Sie genügend Zellen rechts und unterhalb dieser Zelle für die konsolidierten Daten lassen.
Klicken Sie auf Daten > Konsolidieren (in der Gruppe Datentools ).
Klicken Sie im Feld Funktion auf die Zusammenfassungsfunktion, die Excel zum Konsolidieren der Daten verwenden soll. Die Standardfunktion ist SUM .
Hier ist ein Beispiel, in dem drei Arbeitsblattbereiche ausgewählt wurden:
Wählen Sie Ihre Daten aus.
Klicken Sie anschließend im Feld „ Referenz " auf die Schaltfläche „Reduzieren", um den Bereich zu verkleinern und die Daten im Arbeitsblatt auszuwählen.
Klicken Sie auf das Arbeitsblatt, das die Daten enthält, die Sie konsolidieren möchten, wählen Sie die Daten aus und klicken Sie dann rechts auf die Schaltfläche „ Dialog erweitern ", um zum Dialogfeld „ Konsolidieren " zurückzukehren.
Wenn sich ein Arbeitsblatt mit Daten, die Sie konsolidieren müssen, in einer anderen Arbeitsmappe befindet, klicken Sie auf Durchsuchen , um diese Arbeitsmappe zu suchen. Nachdem Sie die Datei gefunden und auf OK geklickt haben, gibt Excel den Dateipfad in das Feld Referenz ein und hängt ein Ausrufezeichen an diesen Pfad an. Sie können dann weitere Daten auswählen.Hier ist ein Beispiel, in dem drei Arbeitsblattbereiche ausgewählt wurden:
Klicken Sie im Popup-Fenster Konsolidieren auf Hinzufügen . Wiederholen Sie dies, um alle Bereiche hinzuzufügen, die Sie konsolidieren.
Automatische vs. manuelle Aktualisierungen: 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.
Anmerkungen:
Sie können keine Verknüpfungen erstellen, wenn sich Quell- und Zielbereiche auf demselben Blatt befinden.
Wenn Sie die Ausdehnung eines Bereichs ändern oder einen Bereich ersetzen müssen, klicken Sie im Popup-Fenster „Konsolidieren" auf den Bereich und aktualisieren Sie ihn mit den oben beschriebenen Schritten. Dadurch wird eine neue Bereichsreferenz erstellt, sodass Sie die vorherige löschen müssen, bevor Sie erneut konsolidieren. Wählen Sie einfach die alte Referenz aus und drücken Sie die Entf-Taste.
Klicken Sie auf OK , und Excel generiert die Konsolidierung für Sie. Optional können Sie eine Formatierung anwenden. Eine Formatierung ist nur einmal erforderlich, es sei denn, Sie führen die Konsolidierung erneut aus.
Alle Beschriftungen, die nicht mit Beschriftungen in den anderen Quellbereichen übereinstimmen, führen zu separaten Zeilen oder Spalten in der Konsolidierung.
Stellen Sie sicher, dass alle Kategorien, die Sie nicht konsolidieren möchten, eindeutige Labels haben, die nur in einem Quellbereich erscheinen.
Wenn sich die zu konsolidierenden Daten in verschiedenen Zellen auf verschiedenen Arbeitsblättern befinden:
Geben Sie eine Formel mit Zellverweisen auf die anderen Arbeitsblätter ein, eine für jedes einzelne Arbeitsblatt. Um beispielsweise Daten aus Arbeitsblättern mit den Namen Sales (in Zelle B4), HR (in Zelle F5) und Marketing (in Zelle B9) zu konsolidieren, würden Sie in Zelle A2 des Hauptarbeitsblatts Folgendes eingeben:
Tipp: Um eine Zellreferenz – wie z. B. Sales!B4 – in eine Formel einzugeben, ohne sie einzutippen, geben Sie die Formel bis zu dem Punkt ein, an dem Sie die Referenz benötigen, klicken Sie dann auf die Registerkarte des Arbeitsblatts und dann auf die Zelle. Excel vervollständigt den Blattnamen und die Zellenadresse für Sie. HINWEIS: Formeln können in solchen Fällen fehleranfällig sein, da es sehr leicht 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 in denselben Zellen auf verschiedenen Arbeitsblättern befinden:
Geben Sie eine Formel mit einer 3D-Referenz ein, die eine Referenz auf eine Reihe von Arbeitsblattnamen verwendet. Um beispielsweise Daten in den Zellen A2 von Vertrieb bis einschließlich Marketing zu konsolidieren, würden Sie in Zelle E5 des Hauptarbeitsblatts Folgendes eingeben:
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in der Answers-Community erhalten.
Siehe auch
Übersicht über Formeln in Excel
So vermeiden Sie fehlerhafte Formeln
Finden und korrigieren Sie Fehler in Formeln
Excel-Tastaturkürzel und Funktionstasten
No comments:
Post a Comment