Dynamische Basisdatenauswahl 7

Excel ist sicherlich eines der wichtigsten Werkzeuge im (Berufs-)Leben vieler Controller und Analysten. Auswertungen, Planungen, Reportings – all das eben, wofür Excel geschaffen ist.

Um aus den oftmals ermüdenden zugrunde liegenden Zahlenfriedhöfen möglichst flexibel und auf Knopfdruck die entscheidenden Informationen ziehen zu können, bedarf es einiger Vorbereitungen.

Was man tun muss, um in seinen Berichten die Daten dynamisch auswählen zu können, verrät dir im heutigen Artikel ein Mann aus der Praxis: Controller und Gastautor Gerhard Pundt.

Viel Spaß beim Lesen und Ausprobieren!

Die für einen Bericht erforderlichen Basisdaten müssen in einer einheitlichen Tabellenstruktur zur Verfügung stehen, damit sie mit Hilfe des hier vorgestellten Modells weiter verarbeitet werden können. Die Erstellung der Basistabellen ist nicht Gegenstand dieses Beitrags (die Excel-Datei mit allen vorgestellten Beispielen kann hier heruntergeladen werden.) Wir wollen den Zahlenteil des Berichts aus den aufbereiteten Daten quasi per Knopfdruck erstellen.

In meinem Beispiel will ich anhand eines einfachen Kostenberichts zeigen, wie das mit dem Knopfdruck gemeint ist. Der fertige Bericht soll etwa so aussehen:

Beispiel: Kostenbericht

Auf den ersten Blick sieht das sehr einfach und übersichtlich aus. Doch dem Leser mit kaufmännischem Hintergrundwissen wird klar sein, dass hinter einer Zahl doch mehr steht als eben nur diese eine Zahl.

Für diesen Beitrag habe ich ein fiktives Unternehmen gewählt, zu dem zwei Werke gehören. Die Plan- bzw. Ist-Werte können jeweils für zwei Jahre ausgewählt werden. Die Produktpalette soll für beide Werke identisch sein. Der abgebildete Bericht befindet sich im Arbeitsblatt „Focus1“.

Im Blatt „Listen1“ habe ich Auswahllisten für das Jahr, das Werk, die Datenart, die Produkte und den Monat angelegt und über „Formel / Namen definieren“ mit Namen versehen:

Auswahllisten

Eine vollständige Liste der vergebenen Namen befindet sich ebenfalls im Blatt „Listen1“. Dem aufmerksamen Leser wird auffallen, dass alle Namen für Listen mit dem Päfix „L1“ und einem Punkt beginnen.

L1 deshalb, weil die Listen im Blatt „Listen1“ zu finden sind. Für das Beispiel benötige ich acht Basisdatenbereiche. Wie kommt die Anzahl der Bereiche zustande? Ganz einfach: 2 Datenarten x 2 Jahre x 2 Werke = 8 Bereiche. D.h., kommt ein Aspekt in einer Liste dazu, benötigst Du bereits 4 neue Tabellen mit Basisdaten, also 1 x 2 x 2 = 4. So geschieht es z.B., wenn ein neues Jahr hinzu kommt. Wie Du in der Beispieldatei in den Basistabellen sehen kannst, enthalten die Zeilenüberschriften die Produkte und die Spaltenüberschriften die Monate.

Vorab will ich noch bemerken, dass ich Dir für den Abruf der Zahlenwerte zwei Varianten vorschlage. Die erste Variante nutzt die Funktionen SUMME, INDEX, INDIREKT und VERGLEICH. In der zweiten Variante werden die Funktionen SUMME, BEREICH.VERSCHIEBEN, INDIREKT und VERGLEICH eingesetzt.

Variante 1

Im Blatt „Focus1“ habe ich mit Daten / Datenüberprüfung Auswahlmöglichkeiten in C5 für das Jahr, in C6 für das Werk, in C8 und C9 für den Monat zugelassen. Ein Klick auf den Pfeil öffnet die Liste:

Dropdown-Listen

Die dynamischen Elemente in diesem Modell sind die durch Verkettung erzeugten Bezüge auf die Basistabellen. Wie funktioniert das?

Im Arbeitsblatt „Focus1“ rufe ich die Werte mit der INDEX-Funktion ab. INDEX verlangt in der Syntax als erstes die Angabe einer Matrix. Die Matrix kann in der Formel hart eingetragen werden, dann ist das Modell nicht dynamisch. Die Matrix kann mit Hilfe von INDIREKT auch über einen Bezug abgerufen werden. Dieser Bezug soll dynamisch sein.

Die Dynamik erreiche ich dadurch, dass ich den Namen der Matrix in Abhängigkeit von den Einstellungen durch VERKETTEN erzeuge.

Im Blatt „Focus1“ findest Du in Zelle C13 den Text „D.Plan2013Flensburg“. Die Formel dahinter lautet:
="D."&C12&C5&C6

„D.“ gibt den Hinweis auf einen Datenbereich, eine Matrix, wie sie in der INDEX-Funktion benötigt wird. In C12 steht „Plan“, in C5 das Jahr „2013“ und in C6 das Werk „Flensburg“.

Änderst Du nun die Auswahl in C5 oder C6, ändert sich auch das Ergebnis der Verkettung.

Nun habe ich zwar den Namen des Bereiches, aus dem die Daten gezogen werden sollen, aber noch nicht den Bereich selbst. Ich lege mir dazu ein Arbeitsblatt mit Namen „Daten Plan 2013“ an. Darin enthalten sind die Plandaten des Jahres 2013 für die Werke Flensburg und Wismar. Für Flensburg sieht das etwa so aus:

Plandaten

Den Bereich B7:M11 markiere ich, gehe über Formel zu Namen definieren und vergebe den Namen „D.Plan2013Flensburg“.

Ich lege analog die Arbeitsblätter „Daten Ist 2013“, „Daten Plan 2014“ und „Daten Ist 2014“ an, erstelle die Basistabellen und vergebe wie bei „D.Plan2013Flensburg“ die Namen.

Jetzt stehen auf vier Arbeitsblättern die acht möglichen Basisdatenbereiche zur Verfügung.

In Focus1!C14 schreibe ich die Formel, die ich bis C18 herunter ziehe:
=SUMME(INDEX(INDIREKT($C$13);VERGLEICH($B14;L1.Produkt;0);$C$8):INDEX(INDIREKT($C$13);VERGLEICH($B14;L1.Produkt;0);$C$9))

In Focus1!D14 schreibe ich so und ziehe bis D18 herunter:
=SUMME(INDEX(INDIREKT($D$13);VERGLEICH($B14;L1.Produkt;0);$C$8):INDEX(INDIREKT($D$13);VERGLEICH($B14;L1.Produkt;0);$C$9))

Dynamische Bereichsauswahl: Variante 1

Durch den Einbau von VERGLEICH in die Zeilenangabe in der INDEX-Formel kann ich auf die manuelle Änderung des Zeilenargumentes je Zeile verzichten. VERGLEICH im Spaltenargument reagiert variabel auf die Auswahlen in C8 und C9.

In „Focus1“ kann ich nun die Auswahlen verändern und werde sofort die zugehörigen Daten in der Auswertung sehen.

Variante 2

Im Arbeitsblatt „Focus2“ rufe ich die Werte mit der BEREICH.VERSCHIEBEN-Funktion ab. BEREICH.VERSCHIEBEN verlangt in der Syntax als erstes die Angabe eines Bezuges. Der Bezug kann in der Formel hart eingetragen werden, dann ist das Modell nicht dynamisch. Der Bezug kann mit Hilfe von INDIREKT auch über einen anderen Bezug abgerufen werden. Dieser Bezug soll dynamisch sein.

Die Dynamik erreiche ich dadurch, dass ich den Namen des Bezuges in Abhängigkeit von den Einstellungen durch VERKETTEN erzeuge. Der Bezug selbst ist eine einzige Zelle.

Im Blatt „Focus2“ findest Du in Zelle C13 den Text „K.Plan2013Flensburg“. Die Formel dahinter lautet:
="K."&C12&C5&C6

„K.“ bedeutet hier nicht Datenbereich, sondern Knoten. Der Knoten ist der Ausgangs-, der Startpunkt für die Verschiebeaktion. In den Basistabellen vergebe ich nun für die linke obere Zelle Namen.

Namen vergeben

A6 heißt: „K.Plan2013Flensburg“

So bin ich in allen Basistabellen vorgegangen.

In Focus2!C14 schreibe ich die Formel, die ich bis C18 herunter ziehe:
=SUMME(BEREICH.VERSCHIEBEN(INDIREKT($C$13);VERGLEICH($B14;L1.Produkt;0);$C$8;1;$C$9))

Dynamische Bereichsauswahl: Variante 2

In Focus2!D14 schreibe ich so und ziehe bis D18 herunter:
=SUMME(BEREICH.VERSCHIEBEN(INDIREKT($D$13);VERGLEICH($B14;L1.Produkt;0);$C$8;1;$C$9))

Für alles weitere gilt das zur INDEX-Methode Gesagte.

Jetzt hast Du, wohlgemerkt einmalig, etwas Aufwand gehabt, um den Bericht auszugestalten. Als Folgearbeiten sind danach monatlich die Basistabellen für das Ist mit Daten zu füllen und jährlich die Tabellen und Namen für ein neues Jahr hinzuzufügen. Aber auch leere Tabellen können schon angelegt und namentlich definiert werden. Auch die Jahresliste kann schon bis 2020 oder später angelegt sein.

Das könnte dich auch interessieren:

Wenig(er) bekannte Datumsfunktionen

Wieviele sind das eigentlich?

Das Schweizer Taschenmesser in Excel: SVERWEIS!

Links, rechts, vor, zurück…