Dynamische Basisdatenauswahl 7

Mehr Dynamik!
Wie man mit ein paar cleveren Excel-Funktionen seine Berichtsdaten dynamisch auswählen kann.
 

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

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

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

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

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

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

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

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.

Über den Autor
Ich heiße Gerhard Pundt, habe Betriebswirtschaft studiert und arbeite seit 15 Jahren bei einem Wasserversorger in M-V als Controller.

Zu Excel kam ich 1993, bedingt durch die Arbeit. Mein heutiges Wissen in Excel und VBA habe ich mir autodidaktisch durch Lesen, Probieren und Üben angeeignet.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

7 Gedanken zu “Dynamische Basisdatenauswahl

  • Avatar-Foto
    Ernst

    Hallo Martin!
    In Variante2 hätte ich einen Fehler entdeckt:
    Wenn du z.B. Monat 5 bis 5 auswählst, wird mit Bereich.Verschieben als letzter Parameter die Breite, also die Summe über 5 Spalten ausgewertet, was ja nicht das Ziel ist.
    Meiner Meinung nach muss die Formel in c14 =SUMME(BEREICH.VERSCHIEBEN(INDIREKT($C$13);VERGLEICH($B14;L1.Produkt;0);$C$8;1;$C$9-$C$8+1))
    lauten. Dann passt auch das Ergebnis.
    LG
    Ernst

    • Avatar-Foto
      Martin Weiß

      Hallo Ernst,

      du bist offensichtlich der Erste, dem der Fehler aufgefallen ist! Vielen Dank für den Hinweis und die korrigierte Formel, so passt es.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Robyn

    Hallo Martin,

    hat sich bei der Logik von „Bereich.Verschieben“ in Excel365 etwas kürzlich geändert?

    Ich wollte heute einen dynamischen Wverweis bzw. Sverweis einstellen, so wie ich das bisher immer gemacht habe.
    Mit einem über „bereich.verschieben“ dynamisch benannten Bereich.
    (=OFFSET(Tabelle1!$E$13;;;COUNTA(Tabelle1!$E:$E);COUNTA(Tabelle1!$13:$13)-1) – Mein Excel ist auf EN eingestellt)
    Heute hatte ich zum ersten mal das Phänomen, dass der Name nicht als Bereich gesehen wird, sondern die gesamte Spalte E und Zeile 13 markiert werden, und das Ergebnis 0 ist.

    Wir nutzen an sehr vielen Stellen die dynamischen Namen. U.a. auch für Diagramme.
    Ist das nun nicht mehr möglich? Und wenn nicht, gibt es eine einfache Alternative?

    VG
    Robyn

    • Avatar-Foto
      Martin Weiß

      Hallo Robyn,

      was sich mit Excel 365 geändert hat, ist, dass viele Formeln jetzt automatisch dynamisch reagieren, wenn mehr als nur 1 Wert zurückgeliefert wird. Wo man also vor Excel 365 mit der Tastenkombination Strg+Alt+Eingabe eine Matrixformel erzeugen musste, ist das mit Excel 365 nicht mehr notwendig. Die Formel wird einfach in die erste Zelle eingegeben und läuft automatisch in soviele Zeilen/Spalten, wie notwendig.

      Bei OFFSET bzw. BEREICH.VERSCHIEBEN bedeutet das, dass abhängig von den beiden Parametern für Höhe und Breite sich die Ergebniszellen entsprechend ausdehnen. Wenn Du hingegen weiterhin mit Strg+Alt+Eingabe arbeitest, kann die Formel nicht dynamisch arbeiten. Das könnte bei dir die Ursache dafür sein, dass du nur den Wert 0 erhältst.

      Mit Excel 365 ist also deutlich mehr Dynamik möglich, als das vorher der Fall war.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Robyn

        Hallo Martin,
        danke dir für die Rückmeldung!
        Ich habe hier immer mit dem Standard-Sverweis gearbeitet. Ohne Matrixformel.
        Dh. ich habe im Sverweis statt der Matrix, den über Offset definierten Namen als Matrix verwendet.
        Diesen löst Excel jetzt aber als Zeile und Spalte auf, statt als Matrix. 🙁

        Ich bin jetzt den Umweg über eine formatierte Tabelle gegangen. Dafür musste ich jetzt die Überschrift doppelt in der Tabelle habe, weil die Überschrift nicht Teil der Matrix ist und ich somit mit dem WVerweis nicht nach dem Wert in der Überschrift suchen konnte, aber es funktioniert. Wenn auch umständlicher als vorher.

        Ich habe das allerdings in vielen älteren Dateien. Wenn ich das alles auf ähnliche Weise umstellen müsste, dann ist das echt viel Arbeit.

        • Avatar-Foto
          Martin Weiß

          Hallo Robyn,

          grundsätzlich sehe ich keinen Grund, warum die Lösung mit M365 komplizierter sein sollte als mit den Vorgängerversionen. Aber für mich ist das aus der Ferne schwer zu beurteilen, ohne die Datei und die genauen Formeln vor mir zu haben.

          Schöne Grüße,
          Martin