Mit der rS1.Methode nur Daten für ausgewählten Zeitraum anzeigen

Berichte, die ein Controller für das Management erstellt, werden in der Regel so aussehen, wie die Empfänger es wünschen. Dagegen ist gar nichts zu sagen, denn das Management muss Istzustände schnell und richtig erfassen, daraus Schlussfolgerungen ziehen und Entscheidungen treffen.
Dieser Beitrag erläutert kurz meinen bisherigen Ansatz auf Basis der rS1.Methode und stellt eine weitere Variante der Berichtserstellung vor.

1. Der bisherige Berichtsansatz

In bisherigen Beiträgen, z.B. „Arbeiten mit der rS1.Methode – ein zweites Beispiel und eine Aufgabe“ habe ich erläutert, wie Daten dynamisch in Wertetabellen gesucht, verdichtet und ausgegeben werden können.

Die Aufgabe war stets: Suche die Daten für die ausgewählten Zeiträume, summiere sie und schreibe sie in die Berichtstabelle an die richtige Stelle.

Die nachfolgende Grafik stellt einen Bericht dar (siehe oben erwähnter Beitrag), in dem für drei Kostenstellen die Planwerte und die Istwerte für den Zeitraum von April bis Oktober 2012 darzustellen waren. Die Berechnung der absoluten und relativen Abweichungen sowie die Summenbildungen erfolgten im Bericht selbst.

2. Ein neuer Berichtsansatz

Im heutigen Beitrag möchte ich eine weitere Variante der Darstellung von Zahlenmaterial vorstellen.

Der Bericht umfasst in der Kopfzeile alle 12 Monate sowie eine Summenspalte und vier Produkte eines fiktiven Möbelbauers. Je Produkt sollen die Planwerte, die Istwerte sowie das prozentuale Verhältnis von Ist zu Plan ausgewiesen werden.

Gezeigt werden sollen aber nur die Monate mit Werten, die sich im ausgewählten Zeitraum befinden. Im Beispiel sind dies die Monate April bis Juli. Alle anderen Monatszellen sollen leer bleiben.

Wie gehe ich dazu vor?
Zuerst erstelle ich eine Datei entsprechend der rS1.Methode (siehe o.g. Beitrag). Für die Basisdaten habe ich schon mal das Blatt Daten1 zur Verfügung. Da ich drei Jahre abbilden können möchte, erweitere ich um die Blätter Daten2 und Daten3.
Daten1 wird Plan- und Istwerte für das Jahr 2013, Daten2 die Werte für 2014 und Daten3 die Werte für 2015 enthalten. Die Daten trage ich manuell ein, kopiere sie hinein oder hole sie über eine Verknüpfung aus anderen Dateien. Das ist beispielhaft hier an einem Auszug aus dem Blatt „Daten1-2013“ zu sehen:

Den Datenbereichen weise ich Namen zu, das ist eine ganze Menge. Hier der Auszug aus der Namensliste:

Für meinen Bericht im Blatt Focus1 benötige ich Auswahllisten für die Zellen M13:M15.
Die Listen lege ich im Blatt Listen1 an und vergebe Namen nach der rS1.Methode:
rL1.JahrListe, rL1.MonatListe

Für die Formeln zur Datenabfrage benötige ich noch eine Produktliste und vergebe den Namen rL1.ProduktListe.

3. Die Gestaltung der Abfrage

Möglich ist, die Werte für alle Monate in den Bericht einzutragen. Das Ziel ist es aber, nur die Monate sichtbar mit Werten zu füllen, die zum ausgewählten Zeitraum gehören. Das Bild oben zeigt die gewünschte Auswahl für die Monate April bis Juli. Für alle anderen Monate sollen die Zellen leer bleiben.

Zu diesem Zweck füge ich in die Zelle M3, die außerhalb des zu druckenden Bereiches liegen, folgende Formel als Hilfskonstruktion ein:

=WENN(UND(M$18>=$M$14;M$18<=$M$15);1;0)

und ziehe sie mit dem Ausfüllkästchen bis X3 durch.

Was erreiche ich damit?
M14 zeigt die Information „von Monat“, M15 zeigt „bis Monat“. M18:X18 sind die Monate im Kopf der Berichtstabelle.

Wenn also M18:X18 größer oder gleich „von Monat“ und M18:X18 kleiner oder gleich „bis Monat“ ist, schreibe 1, wenn nicht, schreibe 0.
Damit habe ich nun eine Bedingung für die eigentliche Datenabfrage in M19:X30 formuliert.

In M19 gebe ich nun folgende Formel ein:
Die Syntax der WENN-Funktion lautet:

=WENN(Prüfung;Dann_Wert;Sonst_Wert)

=WENN(M$3=1;INDEX(INDIREKT(„rD“&VERGLEICH($M$13;rL1.JahrListe;0)&“.Mengen“
&$L19&$M$13);VERGLEICH($K19;rL1.ProduktListe;0);M$18);““)

Die Syntax der INDEX-Funktion (in der Matrixversion) lautet:

=INDEX(Matrix;Zeile;Spalte)

Die Formelteile im Einzelnen:

Prüfung:

WENN(M$3=1
Hier beziehe ich mich auf die in M3:X3 gesetzte Bedingung, wenn =1, dann …

Dann_Wert:

INDEX(INDIREKT(„rD“&VERGLEICH($M$13;rL1.JahrListe;0)&“.Mengen“&$L19&$M$13)

INDEX verlangt als Erstes eine Matrix. Die bestimme ich über eine Verkettung von Zeichenfolgen in der Funktion INDIREKT.

VERGLEICH($M$13;rL1.JahrListe;0) gibt mir die Position des ausgewählten Jahres, 2013, in der Liste rL1.JahrListe zurück. Das ist die 1. „rD“ in Verbindung mit 1 gibt: rD1
Dazu füge ich &“.Mengen“ an: rD1.Mengen
Mit &$L19 erhalte ich das Wort „Plan“: rD1.MengenPlan
&$M$13) schließlich gibt mir noch das Jahr. Damit heißt die Matrix, die ich mit INDIREKT bestimme:
„rD1.MengenPlan2013“.

VERGLEICH($K19;rL1.ProduktListe;0)
Hiermit suche ich die Position des Eintrags in K19, das ist „Stuhl“, in der Liste „rL1.ProduktListe und erhalte die 1. Damit habe ich das Zeilenargument für die INDEX-Formel.

M$18)
Damit erhalte ich das Spaltenargument für die Formel in der Zelle M19, die 1.

Die INDEX-Formel vereinfacht dargestellt:
=INDEX(rD1.MengenPlan2013;1;1)

Bleibt noch der Sonst_Wert:
„“)
Das heißt, wenn in M3:X3 keine 1, sondern eine 0 steht, lasse die Zelle leer.

Die weiteren Formeln trage ich analog in die übrigen Zellen ein. Die Formeln sind in der Beispieldatei zu finden.

Der Bericht soll abschließend auch noch eine dynamische Überschrift erhalten. Dazu schreibe ich in K11 diese Verkettungsformel:

=“Produktionsmengen für den Zeitraum von   „&BEREICH.VERSCHIEBEN(rL1.MonatKnoten;$M$14;1)
&“ bis „&BEREICH.VERSCHIEBEN(rL1.MonatKnoten;$M$15;1)&“ „&$M$13

Im Beispiel erhalte ich so den Text:
Produktionsmengen für den Zeitraum von April bis Juli 2013

Und so sieht der Bericht fertig aus:

4. Die Abfragemöglichkeiten

Was kann ich nun mit diesem Modell alles tun?
– Es stehen drei Jahre zur Verfügung.
– Es können alle Monate des Jahres abgefragt werden.
– Es können einzelne Monate abgefragt werden.
– Es können mehrere zusammenhängende Monate abgefragt werden, z.B. Quartale oder    Halbjahre.
– Im Blatt Grafik1 und weiteren können Diagramme erstellt werden.
– Es können weitere Datenblätter, z.B. mit den Umsätzen angelegt werden. Dazu müsste im Berichtskopf noch eine weitere Auswahlliste eingerichtet werden, z.B. in M16, mit der von Mengen auf Umsätze oder umgekehrt umgeschaltet werden könnte. In den Formeln würde dann statt „Mengen“ z.B. M16 stehen.
– Statt dem Verhältnis Ist / Plan können absolute und relative Abweichungen berechnet werden.
– Statt Produkte können auch Kostenstellen, Kostenarten, Filialen, Regionen oder Länder als Kriterium dienen, der Fantasie sind da keine Grenzen gesetzt.
– usw.

Hier findest du die Beispieldatei zum Herunterladen: