Daten summieren mit einem 3D-Bezug

MS Excel bietet dem Controller verschiedene Möglichkeiten an, Daten zu konsolidieren.  Zum einen kann er mit der Funktion "Summe" einen ganzen Zahlenblock addieren oder mit "Summewenn" nur bestimmte Werte. In diesem Beispiel soll vorgestellt werden, wie es möglich ist, eine Summe über mehrere Tabellen zu erstellen. Diese Verknüpfung von Tabellen wird 3D-Bezug genannt.

In der Praxis kann ein Controller mit Hilfe des 3D-Bezugs aus unterschiedlichen Tabellen mit gleichem Aufbau die benötigten Werte zusammenrechnen. In dem hier verwendeten Beispiel liegt eine Ergebnisrechnung für jeden Monat in einem separaten Tabellenblatt vor. Um die Quartalsergebnisse zu berechnen, addiert der Controller die benötigten Zellen direkt über Tabellenblätter, da der Aufbau der Monatsberichte identisch ist. 
Schritt 1 – Berichtsvorlagen:

In der Beispieldatei wurde der folgende Aufbau für den jeweiligen Monatsbericht verwendet. Zusätzlich wurde für jeden Monat ein eigenes Tabellenblatt angelegt.


Abbildung 1 Vorlage - Monatsbericht

Der Quartalsbericht enthält alle vier Quartale des Jahres. Hierbei wurde ebenfalls die Gliederung der Monatsberichte übernommen.


Abbildung 2 Vorlage – Quartalsbericht

In beiden Berichten sind bereits einfache Summen-Formeln enthalten, welche die einzelnen Positionen zusammenfassen.

Formeln:

Zelle B10: =Summe(B6:B9)
Zelle B17: =Summe(B14:B16)
Zelle B28: =Summe(B20:B27)
Zelle B30: =B10-B17-B28

Für den Quartalsbericht wurden diese Formeln für die Spalten C bis E kopiert.

Schritt 2 – Monatswerte erfassen:

In der Praxis werden die Monatsdaten aus anderen Quellen wie zum Beispiel Datenbanken, anderen Excel-Dateien oder aus einer Controlling-Software entnommen. Da in diesem Beispiel keine Originaldatengrundlage gegeben ist, werden hier manuell fiktive Daten eingegeben für den jeweiligen Monat.

Schritt 3 – Auswertungsbericht erstellen:

Nachdem alle Werte für die jeweiligen Monate erfasst sind, müssen die Formeln für die einzelnen Quartale erstellt werden.

Formeln:

Zelle B6: =Summe(Januar:März!B6)
Zelle C6: =Summe(April:Juni!B6)
Zelle D6: =Summe(Juli:September!B6)
Zelle E6: =Summe(Oktober:Dezember!B6)

Diese Formeln wurden für die verbleibenden darunter liegenden Positionen kopiert. Somit lauten die Formeln für Zeile 7 zum Beispiel wie folgt:

Zelle B7: =Summe(Januar:März!B7)
Zelle C7: =Summe(April:Juni!B7)
Zelle D7: =Summe(Juli:September!B7)
Zelle E7: =Summe(Oktober:Dezember!B7)

Der fertige Bericht wird in folgender Abbildung dargestellt.


Abbildung 3 fertig gestellter Bericht

Hinweise:

Wenn Daten mit einem 3D-Bezug summiert werden, müssen einige Einschränkungen bzw. Regeln beachtet werden.

Zum einen kann ein 3D-Bezug in keiner Matrix- bzw. Array-Formel verwendet werden. Ebenfalls nicht verwendbar sind 3D-Bezüge mit Operatoren für Schnittmengen.

Bei der Verwendung von 3D-Bezügen muss beachtet werden, dass Sie alle Tabellen mit einbeziehen, die zwischen den beiden in der Formel angegebenen Tabellen liegen. Wenn zum Beispiel in der hier verwendeten Beispieldatei das Tabellenblatt Mai zwischen Februar und März angeordnet ist, so wird auch der Mai mit zum 1. Quartal gerechnet. Daher ist die Anordnung von Tabellen sehr wichtig bei der Verwendung eines 3D-Bezugs.

Außerdem unterstützen nur folgende Funktionen die Verwendung des 3D-Bezugs:

MITTELWERT		MAX		ANZAHL
MITTELWERTA		MAXA		ANZAHL2
VARIANZ		MIN		STDABW
VARIANZA		MINA		STDABWA
VARIANZEN		PRODUKT		STDABWN
VARIANZENA		SUMME		STDABWNA

Hierbei handelt es sich um ein Beispiel. Selbstverständlich können weitere Berichte, andere Stellgrößen oder Schematas etc. genutzt werden.

Die Beispieldatei Szenarien können Sie hier herunterladen >>

 

 

letzte Änderung Alexander Wildt am 17.08.2024

Autor:in

weitere Fachbeiträge des Autors | Forenbeiträge