So konsolidieren Sie Daten aus mehreren Blättern mithilfe von 3D-Referenzen in Excel
Excel 3D Reference ist eine kurze und flexible Formel, mit der Sie Berechnungen durchführen können, indem Sie auf dieselbe Zelle oder einen Zellbereich auf mehreren Blättern verweisen. Durch die Verwendung von 3D-Formeln muss nicht mehr auf jede Zelle verwiesen werden, was die Berechnungen vereinfacht. Wenn Sie die 3D-Formel einmal anwenden, enthält sie Daten aus Blättern, die Sie später hinzufügen.
In diesem Artikel wird erläutert, wie die Konsolidierung von Daten in einem Blatt dazu beitragen kann, die Zeit für die Berechnung von Daten über mehrere Blätter hinweg zu verkürzen. Das Verwalten großer Informationsmengen in einem einzigen Arbeitsblatt kann einfach und effizient sein und bei minimalen Anforderungen zu genauen Ergebnissen führen.
Verschiedene Ansätze zum Summieren von Daten in Excel
In Excel besteht die herkömmliche Methode zum Summieren von Informationen über mehrere Blätter darin, jede Zelle mithilfe der Summenfunktion manuell zu addieren. Im Gegensatz dazu kann das manuelle Hinzufügen immer problematischer werden, wenn Sie über einen längeren Zeitraum einen großen Datensatz mit monatlichen Verkäufen haben.
Dies ist auch keine dynamische Methode, daher müssen Sie die Formeln erneut ändern, wenn Sie ein zusätzliches Blatt hinzufügen. Schauen wir uns kurz an, wie die Konsolidierung von Daten über mehrere Blätter hinweg zu einem einzigen Blatt mithilfe von Excel 3D Reference dazu beiträgt, die Daten schnell und präzise zusammenzufassen.
Syntax der Excel 3D-Referenz
Sehen Sie sich die Syntax einer 3D-Referenzierungsformel an, die die Werte in Zellen B4 für vier Blätter mit den Namen Januar, Februar, März und April summiert.
=SUM(January:April!B4)Wie oben gezeigt, gibt es zwei Hauptelemente in der Excel 3D-Formel. Das erste bezieht sich auf die Namen des ersten und des letzten Blattes, die durch ein Semikolon getrennt sind. Im zweiten Teil wird auf alle vier Blätter auf dieselbe Zelle oder denselben Zellbereich verwiesen.
In diesem Fall wird SUM verwendet. Sie können während der Berechnung auch eine andere Formel verwenden. Die Syntax für die 3D-Referenz bleibt jedoch gleich. Um die 3D-Referenz von Excel verwenden zu können, müssen die Daten auf allen vier Blättern konsistent sein.
Excel 3D Referenzbeispiel
Lassen Sie uns nun die Gründe für die Excel 3D-Funktion verstehen. Um dies zu beurteilen, setzen wir es anhand eines Datensatzes in die Praxis um.
Schauen Sie sich die Beispieldaten an, die Verkäufe für die vier Monate, Bestandsnummer, verkaufte Bestände, gekaufte Bestände und Schlussbestände am Ende eines jeden Monats enthalten.
Januar Daten:
Februar Daten:
März Daten:
April Daten:
Bewerten Sie im ersten Schritt die auf vier Blättern verfügbaren Daten, um festzustellen, ob sie sequentiell sind. Nur dann können 3D-Referenzen implementiert werden.
Implementieren der 3D-Referenz
Um den Gesamtumsatz für vier Monate zu berechnen, wählen Sie die Zelle aus, in der die Abbildung angezeigt wird.
In diesem Beispiel befindet sich der Verkaufswert für alle vier Blätter in Zelle B3. Die Referenzdaten auf vier Blättern befinden sich in derselben Zelle. Der Bereich wird von Januar bis April angezeigt, da beide festgelegte Namen für Start- bzw. Endblätter sind.
1. Fügen Sie Zelle B3 als Referenzzelle hinzu .
2. Wählen Sie von Januar bis April den zu verwendenden Bereich.
3. Drücken Sie die Eingabetaste , um die Formel auszuführen.
Um sicherzustellen, dass die Formel korrekt angewendet wurde, können Sie die Werte für ein bestimmtes Element im Datensatz manuell summieren. Sobald Sie wissen, dass es korrekt ist, können Sie erwarten, dass der Rest der Berechnungen korrekt ist, solange die Werte konsistent sind.
Der gleiche Ansatz zur Berechnung des Gesamtverkaufswertes für vier Monate gilt für Aktien, gekaufte, verkaufte und schließende Aktien am Ende eines jeden Monats.
Der Bereich bleibt vier Monate lang gleich, während sich die referenzierte Zelle entsprechend ihrer Position in verschiedenen Blättern ändert. Fassen wir die Bestandsdaten für vier Monate zusammen.
1. Fügen Sie Zelle B4 als Referenzzelle hinzu .
2. Wählen Sie den Bereich ab Januar: April .
Ziehen Sie es, um die 3D-Summenformel für andere Zellen in der Zeile mithilfe der automatischen Füllsequenz zu implementieren.
Auf die gleiche Weise können Sie 3D-Referenzen auf eine Reihe von Zellen in verschiedenen Arbeitsblättern anwenden. In diesem Fall bleibt die Auswahl der Blätter gleich.
Sie müssen jedoch den gesamten Bereich anstelle einer Zelle als Referenz auswählen. Stellen Sie außerdem sicher, dass die Daten über alle Blätter hinweg konsistent sind.
Hinzufügen eines neuen Blattes in der Arbeitsmappe
Die Excel 3D-Referenz aktualisiert die Daten automatisch, während ein oder mehrere neue Blätter hinzugefügt werden, solange diese Blätter zwischen den in der 3D-Formel ausgewählten Bereichen liegen. Mal sehen, wie es funktioniert.
Wie unten gezeigt, beträgt der Gesamtumsatz für vier Monate 250000 für die Monate Januar bis April.
Fügen wir ein neues Blatt hinzu, das die Daten für den Monat Mitte Februar enthält.
Berücksichtigen Sie den Verkaufswert des Blattes Mitte Februar. Die Daten werden automatisch im Hauptblatt der viermonatigen Analyse angezeigt.
Wie oben gezeigt, steigt der Gesamtumsatz für alle Monate um 10.000. Ohne die Formel zu ändern, wurde das Ergebnis automatisch aktualisiert.
Diese Flexibilität ist praktisch, wenn Sie den Verkaufsrekord des ganzen Jahres über umfangreiche Datensätze hinweg verfolgen. Fügen wir dem Blatt Mitte Februar Lagerbestände, verkaufte Mengen, gekaufte Mengen und Endbestandsdaten hinzu.
Sehen Sie sich an, wie sich der obige Zusatz auf die Daten in einem viermonatigen Analyseblatt auswirkt.
Daher hat die Referenzformel alle Daten im Hauptblatt erfolgreich aktualisiert.
Machen Sie Daten konsistent, um 3D-Referenzen anzuwenden
Wenn Sie Daten aus mehreren Blättern in einem zusammenfassen möchten, stellen Sie sicher, dass die Zellennummer konsistent ist. Befindet sich beispielsweise der gekaufte Bestand in einer bestimmten Zelle auf Blatt 1, muss er auf allen anderen Blättern in derselben Zelle abgelegt werden. Geben Sie beim Hinzufügen eines neuen Blattes die Daten in den Zellen korrekt ein.
Vereinfachen Sie Berechnungen mit Excel 3D-Referenzierung
Das Implementieren von 3D-Formeln scheint schwierig zu sein, lässt sich jedoch mühelos warten, sobald Sie sie richtig ausführen. Sie können jeden Monat neue Daten hinzufügen, ohne die Berechnungen von Anfang an durchführen zu müssen.
Die 3D-Formel eliminiert auch die Wahrscheinlichkeit von Doppelarbeit oder Auslassung von Verkäufen für einen bestimmten Monat. Diese Funktionalität ist am vorteilhaftesten für Buchhalter, die jeden Monat Datenbündel aktualisieren.