Erweiterte Datenanalyse - Datenkonsolidierung

Möglicherweise sind Sie auf verschiedene Situationen gestoßen, in denen Sie konsolidierte Daten präsentieren müssen. Die Datenquelle kann von einem Ort oder von mehreren Orten stammen. Eine weitere Herausforderung könnte darin bestehen, dass die Daten von Zeit zu Zeit von anderen Personen aktualisiert werden.

Sie müssen wissen, wie Sie ein zusammenfassendes Arbeitsblatt einrichten können, das die Daten aus den von Ihnen eingerichteten Quellen konsolidiert, wann immer Sie möchten. In Excel können Sie diese Aufgabe mit dem in wenigen Schritten ausführenData Tool – Consolidate.

Daten für die Konsolidierung vorbereiten

Stellen Sie vor der Konsolidierung der Daten sicher, dass die Datenquellen konsistent sind. Dies bedeutet, dass die Daten wie folgt angeordnet sind:

Jeder Datenbereich befindet sich in einem separaten Arbeitsblatt.
Jeder Datenbereich ist im Listenformat mit Beschriftungen in der ersten Zeile.
Darüber hinaus können Sie in der ersten Spalte gegebenenfalls Beschriftungen für die Kategorien einfügen.
Alle Datenbereiche haben das gleiche Layout.
Alle Datenbereiche enthalten ähnliche Fakten.
In jedem Bereich gibt es keine leeren Zeilen oder Spalten.

Wenn die Datenquellen extern sind, stellen Sie sicher, dass ein vordefiniertes Layout in Form einer Excel-Vorlage verwendet wird.

Angenommen, Sie haben die Verkaufsdaten verschiedener Waren aus jeder der Regionen - Ost, Nord, Süd und West. Möglicherweise müssen Sie diese Daten konsolidieren und von Zeit zu Zeit eine produktbezogene Zusammenfassung der Verkäufe vorlegen. Die Vorbereitung umfasst Folgendes:

Ein Arbeitsblatt pro Region - dh vier Arbeitsblätter mit den Namen Ost, Nord, Süd und West. Diese können sich in derselben Arbeitsmappe oder in verschiedenen Arbeitsmappen befinden.
Jedes Arbeitsblatt hat das gleiche Layout, das die Details des Produkts, die Anzahl der Einheiten und die Menge darstellt.
Sie müssen die Daten produktbezogen konsolidieren. Stellen Sie daher sicher, dass die Spalte mit der Bezeichnung Produkt die erste Spalte ist und die Produktbezeichnungen enthält.

Konsolidieren von Daten in derselben Arbeitsmappe

Wenn Sie alle Daten, die Sie konsolidieren müssen, in derselben Arbeitsmappe haben, gehen Sie wie folgt vor:

Step 1 - Stellen Sie sicher, dass sich die Daten jeder Region auf einem separaten Arbeitsblatt befinden.

Step 2 - Fügen Sie ein neues Arbeitsblatt hinzu und nennen Sie es Zusammenfassung.

Step 3 - Klicken Sie auf das Arbeitsblatt Zusammenfassung.

Step 4 - Klicken Sie auf die Zelle, in der Sie die Zusammenfassungsergebnisse platzieren möchten.

Step 5 - Klicken Sie auf DATA Registerkarte auf der Multifunktionsleiste.

Step 6 - Klicken Sie auf Consolidate Schaltfläche in der Data Tools Gruppe.

Das Consolidate Das Dialogfeld wird angezeigt.

Step 7 - Wählen Sie Sum aus der Dropdown-Liste unter Function.

Step 8 - Wählen Sie die Daten aus jedem Arbeitsblatt wie folgt aus.

Klicken Sie auf das Symbol im Feld unter Referenz.
Wählen Sie das Arbeitsblatt - Ost.
Wählen Sie den Datenbereich.
Klicken Sie erneut auf das Symbol im Feld unter Referenz.

Der ausgewählte Bereich wird im Referenzfeld angezeigt -

Step 9 - Klicken Sie auf AddSchaltfläche rechts neben dem Feld. Der ausgewählte Datenbereich wird im Feld unter angezeigtAll References.

Step 10- Wiederholen Sie die Schritte 1 bis 5 für die restlichen Datenarbeitsblätter - Nord, Süd und West. Das Dialogfeld Konsolidieren sieht folgendermaßen aus.

Sie können sehen, dass die Datenbereiche in alphabetischer Reihenfolge arbeitsblattweise im Feld unter angezeigt werden All references.

Step 11 - Aktivieren Sie die Kontrollkästchen Top row und Left column unter Use labels in. OK klicken.

Ihre Daten werden produktbezogen für die Regionen Ost, Nord, Süd und West zusammengefasst.

Sie können die oben angegebenen Schritte wiederholen, um Ihre Zusammenfassungsergebnisse manuell zu aktualisieren, wann immer Sie sie benötigen.

Daten automatisch konsolidieren

Angenommen, Sie möchten, dass Ihr Zusammenfassungsblatt automatisch aktualisiert wird, wenn sich die Daten ändern. Um dies zu erreichen, benötigen Sie Links zu den Quelldaten.

Step 1 - Aktivieren Sie das Kontrollkästchen - Create links to source data Klicken Sie im Dialogfeld Konsolidieren auf OK.

Ihre zusammenfassenden Ergebnisse werden wie folgt angezeigt:

Sie werden feststellen, dass rechts neben der Spalte Produkt eine neue Spalte eingefügt wird.

Step 2- Klicken Sie auf das Pluszeichen in der Gliederung in der Zeile mit dem Produktwert Seife. Sie können sehen, dass die neue Spalte den konsolidierten Wert für jeden Satz von Produktwerten in Bezug auf die Region enthält.

Konsolidieren von Daten aus verschiedenen Arbeitsmappen

Im vorherigen Beispiel befinden sich alle Daten, die Sie zusammenfassen müssen, in derselben Arbeitsmappe. Es ist jedoch wahrscheinlich, dass die Daten für jede Region separat verwaltet und regional aktualisiert werden. In einem solchen Fall können Sie die Daten wie folgt konsolidieren:

Step 1 - Öffnen Sie die Arbeitsmappen mit den Daten, z. B. Arbeitsmappen - Ost-Vertrieb, Nord-Vertrieb, Süd-Vertrieb und West-Vertrieb.

Step 2 - Öffnen Sie eine neue Arbeitsmappe.

Step 3 - Klicken Sie in einem neuen Arbeitsblatt auf eine Zelle, in der die Zusammenfassung angezeigt werden soll.

Step 4 - Klicken Sie auf der Multifunktionsleiste auf die Registerkarte DATEN.

Step 5 - Klicken Sie im Feld Datentools auf Konsolidieren.

EIN ConsolidateDas Dialogfeld wird angezeigt. Im Dialogfeld Konsolidieren -

Wählen Sie Summe aus der Dropdown-Liste im Feld unter Funktion.
Klicken Sie auf das Symbol im Feld unter Reference.
Wählen Sie die Arbeitsmappe - East-Sales.xlsx.
Wählen Sie den Datenbereich.
Klicken Sie erneut auf das Symbol im Feld unter Referenz.
Drücke den Add Taste rechts.

Das Dialogfeld Konsolidieren sieht folgendermaßen aus:

Klicken Sie auf das Symbol rechts neben dem Feld unter Referenzen.
Wählen Sie die Arbeitsmappe - North-Sales.xlsx.
Wählen Sie den Datenbereich.
Klicken Sie erneut auf das Symbol rechts neben dem Feld unter Referenzen.
Klicken Sie auf Hinzufügen.

Step 6 - Wiederholen Sie die Schritte 1 bis 6, um die Datenbereiche aus den Arbeitsmappen South-Sales.xlsx und West-Sales.xlsx hinzuzufügen.

Step 7 - Unter Use labels inAktivieren Sie die folgenden Kontrollkästchen.

Oberste Reihe.
Linke Spalte.

Step 8 - Aktivieren Sie das Kontrollkästchen Create links to source data.

Das Dialogfeld "Konsolidieren" sieht folgendermaßen aus:

Ihre Daten sind in Ihrer Arbeitsmappe zusammengefasst.