Arbeiten mit der rS1.Methode – ein zweites Beispiel

Im letzten Beitrag habe ich Dir die Namenskonventionen der rS1.Methode vorgestellt und ein erstes Beispiel ausführlich erläutert. Darin konntest Du die Werte nur jeweils eines Monats abrufen.
Im heutigen Beispiel gehe ich einen Schritt weiter und will zeigen, dass Du natürlich auch Werte für einen Zeitraum abfragen kannst.

Für die Beispieldatei habe ich wieder die Vorlage, wie im Artikel vom 13.10.2015 beschrieben, aufgerufen und sie unter dem Namen „Excel_rS1 Beispiel 2.xlsx“ gespeichert.

Blatt „Focus 1“

Die Datei sieht grundsätzlich nicht anders aus als im ersten Beispiel. Etwas anders allerdings ist das Blatt „Focus 1“ aufgebaut. Du siehst, dass hier nicht nur zwei Auswahlfelder vorhanden sind, nämlich Jahr und Monat, sondern ein drittes Feld dazu gekommen ist.
Das bisherige Feld Monat entfällt, dafür richtest Du das Feld vonMonat und das Feld bisMonat ein, so habe ich sie genannt. Du kannst aber auch andere Bezeichnungen wählen.

Für die Zellen L15 und L16 legst Du über Daten / Datenüberprüfung die Gültigkeit fest. Bei Zulassen wählst Du „Liste“, als Quelle bei beiden Zellen „rL1.MonateListe“.

Beiden Zellen gibst Du jetzt wieder einen Namen:
L15 wird rF1.VonMonatAusw
L16 wird rF1.BisMonatAusw
Du siehst in der Abbildung noch etwas Neues. In Zeile 19 stehen unter „Plan“ und „Ist“ Zeichenfolgen. Das sind die Datenbereiche, auf die Du zugreifen willst. Die Bereiche sind in den Arbeitsblättern „Daten 1“ und „Daten 2“ vorhanden und mit Namen versehen.
Natürlich gibt es neben dieser Variante, die Matrixnamen offen zu zeigen, noch andere Möglichkeiten, den Datenabruf dynamisch zu gestalten. Das würde erstens die Formeln weiter verlängern und zweitens dem noch nicht so sattelfesten rS1-Anwender den Überblick vielleicht erschweren.
Warum habe ich diese Zeichenfolgen dort eingefügt? Bisher hattest Du es nur mit einem Jahr zu tun, nämlich 2012. Da sind „rD1.Plan2012Werte“ und rD2.Ist2012Werte“ ausreichend.
Kommen aber nun weitere Jahre hinzu, musst Du, getreu dem Grundsatz, dass jeder neue Sachverhalt ein neues Arbeitsblatt erhält, neue Blätter anlegen, „Daten 3“, „Daten 4“ usw. Die Ziffer nach „rD“ muss also variabel sein. Das hast Du durch diese Aktion erreicht.
In L19 und M19 werden die Zeichenfolgen durch Verkettung so erzeugt:
L19
=“rD“&BEREICH.VERSCHIEBEN(rL1.DatenKnoten;VERGLEICH(L$18&rF1.JahrAusw;rL1.DatenListe;0);1)&“.“&L$18&rF1.JahrAusw&“Werte“
M19
=“rD“&BEREICH.VERSCHIEBEN(rL1.DatenKnoten;VERGLEICH(M$18&rF1.JahrAusw;rL1.DatenListe;0);1)&“.“&M$18&rF1.JahrAusw&“Werte“
Damit diese Formeln auch ein Ergebnis liefern, musst Du vorher im Blatt „Listen 1“ noch diese Liste anlegen. M12:M19 erhalten den Namen „rL1.DatenListe“, M11 erhält den Namen „rL1.DatenKnoten“.

Wenn das neue Jahr nicht in der Liste vorhanden ist, erweitere die Listen und passe im Namensmanager für „rL1.DatenListe“ den Quellbereich an.
Was jetzt noch fehlt, sind die Werte zu Deiner Abfrage. Dazu gebe ich in die Zellen L20:O23 Formeln ein. Ich habe wieder die Hilfsspalte I mit den Werten 1, 2 und 3 genutzt.
Ich markiere den Bereich L20:L22, gebe die Formel
=SUMME(INDEX(INDIREKT($L$19);$I20;VERGLEICH(rF1.VonMonatAusw;rL1.MonateListe;0)):
INDEX(INDIREKT($L$19);$I20;VERGLEICH(rF1.BisMonatAusw;rL1.MonateListe;0)))

ein und schließe mit Strg + Enter ab. Du siehst, dass INDIREKT jetzt auf die durch Verkettung erzeugten Zeichenfolgen      (= Matrixnamen) zugreift.
Die weiteren Formeln sind:
M20:M22
=SUMME(INDEX(INDIREKT($M$19);$I20;VERGLEICH(rF1.VonMonatAusw;rL1.MonateListe;0)):
INDEX(INDIREKT($M$19);$I20;VERGLEICH(rF1.BisMonatAusw;rL1.MonateListe;0)))

N20:N22 =M20-L20
O20:O23 =N20/L20
L23:N23 =SUMME(L20:L22)

Das Blatt „Focus 1“ ist jetzt fertig. Probiere jetzt bitte aus, ob alles funktioniert, bei langen Formeln schleichen sich schnell Fehler ein, schon ein Leerzeichen kann zu viel sein.

Blatt „Grafik 1“

Die Plan- und Istwerte der drei Kostenstellen will ich wieder vergleichend in einem Diagramm darstellen. Das Diagramm soll im Blatt „Grafik 1“ abgelegt werden.

Da sich Blatt „Focus 1“ ein wenig verändert hat, hier die angepasste Anleitung für das Diagramm:

In „Focus 1“ markiere ich K20:M22, gehe auf Einfügen/Diagramme/Säule und wähle Gruppierte Säule. Ich klicke in den Diagrammbereich, wähle mit der rechten Maustaste Ausschneiden, gehe ins Blatt „Grafik 1“ und wähle Einfügen. Dort positioniere ich das Diagramm.

Klicke zuerst die Plan-Säulen, dann die Ist-Säulen an und verändere die Beschriftung in der Bearbeitungszeile wie folgt:

Plan =DATENREIHE(‚Focus 1′!$L$18;’Focus 1′!$K$20:$K$22;’Focus 1‘!$L$20:$L$22;1)
Ist =DATENREIHE(‚Focus 1′!$M$18;’Focus 1′!$K$20:$K$22;’Focus 1‘!$M$20:$M$22;2)

Über Layout/Beschriftungen will ich noch einen Diagrammtitel einfügen, der sich dynamisch verändern können muss.

Dazu schreibe ich in K9, hier nutze ich eine Hilfszeile, diese Formel:

=’Focus 1′!$K$12&“ „&rF1.VonMonatAusw&“ bis „&rF1.BisMonatAusw&“ „&rF1.JahrAusw

In K9 steht jetzt: „Plan – Ist – Vergleich April bis Oktober 2012“. Das soll der Diagrammtitel werden.

Ins Diagramm füge ich jetzt einen Diagrammtitel ein, das Wort „Diagrammtitel“ muss einen geschlossenen Rahmen aufweisen, und schreibe in der Bearbeitungszeile die Formel:

=’Grafik 1′!$K$9

Jetzt ist das Diagramm dynamisch und reagiert auf Veränderungen im Blatt „Focus 1“ L14:L16.

Blatt „Parameter 1“

Parameter wurden in diesem Modell nicht benötigt, das Blatt ist leer.

Blatt „Namensliste“

Jetzt sind alle Namen für Bereiche in der Arbeitsmappe vergeben. Bleibt noch, die Namensliste zu füllen.

Ich klicke im Blatt auf K11, gehe über Formeln/Definierte Namen/In Formel verwenden/Namen einfügen, klicke auf Liste einfügen und auf OK.

Das Blatt enthält jetzt eine Aufstellung über alle vergebenen Namen mit dem jeweiligen Bereich.

Zusammenfassung

Das zweite Beispiel zur Anwendung der rS1.Methode ist damit abgeschlossen. Beachte bitte, dass Du im Blatt „Focus 1“ in L14:L16 nur Auswahlen treffen kannst, die in Listen hinterlegt sind.

Das Modell enthält bisher nur Daten eines Jahres, abgefragt werden können die Werte von Zeiträumen innerhalb eines Jahres.

So sieht der Bericht fertig aus:

Download Datei: