![]( "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 >>
Informieren Sie sich regelmäßig über neue Excel-Tipps und Excel-Tools
in unserem monatlich erscheinenden >>Newsletter<<.
letzte Änderung Alexander Wildt
am 09.09.2019
|
Autor:in
Bleiben Sie auf dem Laufenden mit unserem Newsletter
Tragen Sie sich für den
kostenfreien und unverbindlichen
Newsletter von Controlling-Portal.de ein und erhalten Sie jeden Monat aktuelle Neuigkeiten für Controller. Wir informieren Sie über neue Fachartikel, über wichtige News, aktuelle Stellenangebote, interessante Tagungen und Seminare. Wir empfehlen Ihnen spannende Bücher und geben Ihnen nützliche Excel-Tipps. Verpassen Sie nie mehr wichtige Diskussionen im Forum und stöbern Sie in Software-Angeboten, die Ihnen den Arbeitsalltag erleichtern.
Beispiel-Newsletter >>
Jetzt Newsletter gratis erhalten
Eigenen Fachbeitrag veröffentlichen?
Sie sind Autor einer Fachpublikation oder Entwickler einer Excel-Vorlage? Gern können Sie sich an der Gestaltung der Inhalte unserer Fachportale beteiligen! Wir bieten die Möglichkeit Ihre Fachpublikation (Fachbeitrag, eBook, Diplomarbeit, Checkliste, Studie, Berichtsvorlage ...) bzw. Excel-Vorlage auf unseren Fachportalen zu veröffentlichen bzw. ggf. auch zu vermarkten.
Mehr Infos >>
Über 200 Kennzahlen aus Finanzen, Personal, Logistik, Produktion, Einkauf, Vertrieb, eCommerce und IT.
Wie erstelle ich ein Tacho- oder Ampel-Diagramm? Wie kann ich Abweichungen in Tabellen ansprechend visualisieren? Das wird Ihnen hier anschaulich erklärt. 
Viel ist zum Berichtswesen oder Reporting schon geschrieben worden. Dennoch zeigen Umfragen, dass rund 50 Prozent der Empfänger von Berichten mit dem Reporting nicht zufrieden sind. Jörgen Erichsen erklärt in diesem Buch die Bedeutung und die Handhabung des Berichtswesens speziell für kleinere Betriebe. Mit zahlreichen Beschreibungen, Beispielen und Checklisten.
Wir setzen für Sie den Rotstift an, sparen Sie mit unsrem RS Toolpaket - Controlling über 30% im Vergleich zum Einzelkauf. Die wichtigsten Controlling-Vorlagen in einem Paket (Planung, Bilanzanalyse, Investitionsrechnung, ...). Das Controllingpaket umfasst 6 Excel-Tools für Ihre Arbeit! 
Rollierende Liquiditätsplanung auf Wochenbasis. Mit der Excel-Vorlage „Liquiditätstool“ erstellen Sie schnell und einfach ein Bild ihrer operativen Liquiditätslage für die nächsten (bis zu 52) Wochen.. 
