Vor Kurzem (hier) habe ich Dir die rS1.Methode vorgestellt und erläutert, wie der Erfinder, Reinhold Scheck, den einheitlichen Aufbau der Arbeitsmappen und die einheitliche Benennung der standardmäßig enthaltenen sechs Arbeitsblätter konzipiert hat.
In diesem Beitrag erfährst Du etwas über die Namenskonventionen der rS1.Methode und die Arbeit mit der Methode an einem Beispiel. Dies ist übrigens mein erster Beitrag mit über 1500 Wörtern.
Es wird zunächst nach Namen für Bereiche oder nach Namen für Objekte unterschieden.
Bereiche sind zusammenhängende Zellen, z.B.
B3:B12
A1:D14
C12:G12
Oder auch einzelne Zellen, wie
E16
Objekte sind Diagramme, Steuerelemente, Bilder oder auch UserForms in der VBA-Programmierung.
VBA-Programmierung ist jedoch gerade das, was durch die Anwendung der rS1.Methode vermieden werden soll. Namen für Objekte spielen deshalb eine untergeordnete Rolle und werden nur wichtig, wenn später doch programmiert werden soll oder muss.
Namen für Bereiche
Der Namen für einen Bereich besteht aus Präfix, Trennzeichen und Text.
rD1.Plan2015Werte
rD1 Präfix
. Trennzeichen (Punkt)
Plan2015Werte sinntragender Text
Präfix
Das kleine „r“ zu Beginn steht für Bereich (engl.: range).
„D1“ steht für das Arbeitsblatt, in dem sich der namentlich zu benennende Bereich befindet.
Du erinnerst Dich?
Das dritte Arbeitsblatt in Arbeitsmappenvorlage war „Daten 1“, „D1“ weist also auf „Daten1“ hin.
Bereiche im Blatt Focus 1 würden danach mit rF1, Bereiche im Blatt Listen 1 mit rL1 usw. beginnen.
Der Wortteil „Werte“ gibt Dir einen Hinweis darauf, dass der benannte Bereich Zahlen, eben Werte, und keine Auswahl (Ausw) enthält.
Welchen Nutzen bringt Dir das?
Ob Du einen Namen in der Namensliste oder in einer Formel liest, Du weißt sofort, in welchem Arbeitsblatt Du ggf. nachsehen musst.
Bereiche mit mehreren Zellen werden in der Regel in Formeln, z.B. INDEX als Argument für die Matrix oder in der VERGLEICH-Funktion als Suchmatrix gebraucht. Einzellige Bereiche werden z.B. in der VERGLEICH-Funktion als Suchkriterium oder in der INDEX-Funktion für die Bestimmung der Zeile und/oder Spalte benötigt.
Ein einzelliger Bereich kann aber auch Ausgangspunkt für das Argument Bezug in der Funktion BEREICH.VERSCHIEBEN sein.
Trennzeichen
Als Trennzeichen wird bei Bereichsnamen grundsätzlich der “.“ (Punkt) verwendet. Mehr ist dazu nicht zu sagen.
Sinntragender Text
Der sinntragende Text ist in den Namen außerordentlich wichtig. Im anschließenden Beispiel wirst Du sehen, wie der Name, den Du dann in Formeln verwenden willst, dynamisch durch Verkettung erzeugt werden kann.
Jedes neue Wort im Text beginnt mit einem Großbuchstaben. Auch hierzu wirst Du im Beispiel erkennen, dass das durchaus Sinn macht.
Und schließlich sollte der Text schon einen inhaltlichen Hinweis geben.
Namen für Objekte
Der Namen für einen Bereich besteht aus Präfix, Trennzeichen und Text.
oF1_ListBoxMonatAusw
oF1 Präfix
_ Trennzeichen (Unterstrich)
ListBoxMonatAusw sinntragender Text
Präfix
Das kleine „o“ zu Beginn steht für Objekt (engl.: object).
„F1“ steht für das Arbeitsblatt, in dem sich der namentlich zu benennende Bereich befindet.
Trennzeichen
Als Trennzeichen wird bei Objektnamen grundsätzlich der “_“ (Unterstrich) verwendet.
Sinntragender Text
Der sinntragende Text ist in den Namen auch von Objekten außerordentlich wichtig. Das Beispiel oben zeigt, dass es um das Steuerelement ListBox /ActiveX-Steuerelement) geht, mit dem der Berichtsmonat ausgewählt wird. Der Textteil „Ausw“ wird hier und bei Bereichsnamen gleichermaßen immer für Zellen oder ein Steuerelemente verwendet, die eine Auswahl enthalten oder ermöglichen. Das macht durchaus Sinn.
Jedes neue Wort im Text beginnt mit einem Großbuchstaben.
Der Text soll einen inhaltlichen Hinweis geben.
Die rS1.Methode in der Praxis
Um Dir das bisher Dargelegte zu verdeutlichen, habe ich mir ein praktisches Beispiel überlegt, an dem Du das Eine oder Andere sehen und nachvollziehen kannst.
Für die Beispieldatei habe ich die Vorlage, wie im Artikel vom 13.10.2015 beschrieben, aufgerufen und sie unter dem Namen „Excel_rS1 Beispiel.xlsx“ gespeichert.
Sieh Dir bitte diese Datei in folgender Reihenfolge an:
Blatt „Daten 1 Plan 2012“
Beginnend ab Zelle K11 (davor bleiben 10 Leerspalten, darüber 10 Leerzeilen) habe ich Daten gemäß folgender auszugsweisen Abbildung vorgenommen, Zeile 14 geht natürlich bis Monat Dez weiter.
Die Tabelle enthält die Kosten für drei Kostenstellen des Bereiches Zerspanung in einem Maschinenbauunternehmen. Die Plan-Werte habe ich für diesen Zweck auf einen monatlich gleichen Betrag festgeschrieben.
Den Bereich L15:W17 habe ich markiert und dann über Formeln / Namen definieren / Neuer Name den Namen „rD1.Plan2012Werte“ vergeben.
Letztlich habe ich noch den Namen des Arbeitsblattes von ursprünglich „Daten 1“ in „Daten 1 Plan 2012“ geändert. Damit wird der Inhalt des Blattes für den Nutzer sichtbarer.
Blatt „Daten 2 Ist 2012“
Das Arbeitsblatt wird neu eingefügt, nach rS1-Regeln formatiert und zunächst mit dem Namen „Daten 2“ versehen.
Die Tabelle ist ähnlich der in Blatt „Daten 1 Plan 2012“ aufgebaut.
Hier allerdings müssen Ist-Werte erfasst werden. Diese trägst Du entweder monatlich manuell ein, kopierst sie aus anderen Dateien hinein oder holst sie über einen Bezug aus anderen Quellen.
Ich habe die Werte über die Funktion ZUFALLSBEREICH ermittelt und über Kopieren / Inhalte einfügen-Werte festgeschrieben. Es handelt sich also um Spieldaten.
Den Bereich L15:W17 habe ich auch hier markiert und dann über Formeln / Namen definieren / Neuer Name den Namen „rD2.Ist2012Werte“ vergeben.
Das Arbeitsblatt benenne ich um, von „Daten 2“ in „Daten 2 Ist 2012“.
Blatt „Focus 1“
Das Blatt „Focus 1“ soll die Berichtsdaten enthalten, also die Daten, die Du einem Empfängerkreis zugänglich machen willst.
Die Berichtstabelle könnte z.B. so aussehen:
Die Zellen L14 und L15 sollen der Auswahl von Argumenten dienen. Du kannst wählen, ob Du die Daten bei jeder Abfrage manuell eingeben, die Auswahl über ein Drop-Down-Liste oder eines der Steuerelemente Listenfeld bzw. ListBox oder Kombinationsfeld bzw. ComboBox treffen möchtest.
Ich habe mich im Beispiel für eine Drop-Down-Liste entschieden.
In jedem Fall werden Listen benötigt, die eine Auswahl ermöglichen. Dazu gehst Du jetzt ins Blatt „Listen 1“, das für das Anlegen von Listen vorgesehen ist.
Blatt „Listen 1“
Du benötigst jeweils eine Liste für die Auswahl des Berichtsjahres und die Auswahl des Berichtsmonats.
Lege die Listen im Blatt „Listen 1“ an und vergib für den Bereich K12:K15 den Namen „rL1.JahreListe“ und den Bereich L12:L23 den Namen „rL1.MonateListe“.
Jetzt gehst Du zurück ins Blatt „Focus 1“.
Blatt „Focus 1“
Aktiviere Zelle L14, gehe auf Daten / Datenüberprüfung. Wähle unter Zulassen „Liste“ aus und trage als Quelle „=rL1.JahreListe“ ein. Klicke auf OK.
Aktiviere Zelle L15, gehe auf Daten / Datenüberprüfung. Wähle unter Zulassen „Liste“ aus und trage als Quelle „=rL1.MonateListe“ ein. Klicke auf OK.
Beide Zellen enthalten beim Aktivieren jetzt rechts den bekannten Pfeil, nun kann ich wählen. Als Jahr brauche ich 2012, als Monat den Mai.
Schließlich versehe ich L14 und L15 noch mit Namen:
L14 wird rF1.JahrAusw,
L15 wird rF1.MonatAusw benannt.
Was jetzt noch fehlt, sind die Werte zu Deiner Abfrage. Dazu gebe ich in die Zellen L18:O21 Formeln ein.
Damit diese nicht zu lang werden und weil die Tabelle hinsichtlich der Zeilen- und Spaltenanzahl konstant ist, nutze ich die Hilfsspalte I und trage dort, so wie in der Abbildung, 1, 2 und 3 ein.
Ich markiere den Bereich L18:L20, gebe die Formel
=INDEX(INDIREKT(“rD1.”&$L$17&rF1.JahrAusw&”Werte”);$I18;VERGLEICH(rF1.MonatAusw;rL1.MonateListe;0))
ein. Sieht kompliziert aus? Ist es aber nicht.
Ich verwende hier die INDEX-Funktion in der Matrixversion (dazu schrieb ich auch hier), d.h. die Syntax ist
=INDEX(Matrix;Zeile;Spalte).
Durch Verkettung
“rD1.”&$L$17&rF1.JahrAusw&”Werte”
wird der Name der Matrix erzeugt: rD1.Plan2012Werte
(Hier siehst Du, dass es Sinn macht, dass jedes neue Wort im Namenstext mit einem Großbuchstaben beginnt)
Da der Name nicht direkt in INDEX eingegeben, sondern indirekt erzeugt wird, muss der Ausdruck noch mit der INDIREKT-Funktion umgeben werden:
INDIREKT(“rD1.”&$L$17&rF1.JahrAusw&”Werte”)
Die Zeile wird durch $I18, $I19 und $I20 bestimmt, das sind die Werte in der Hilfsspalte.
Das Spaltenargument gewinne ich mit Hilfe der VERGLEICH-Funktion:
VERGLEICH(rF1.MonatAusw;rL1.MonateListe;0)
Dabei vergleiche ich die Auswahl rF1.MonatAusw mit meiner Liste rL1.MonateListe.
In „Reinschrift“ würde die Formel in L18 nun lauten:
=INDEX(rD1.Plan2012Werte;1;5)
Ich habe L18:L20 markieret, die Formel eingegeben und schließe nun mit Strg + Enter ab.
Nun gebe ich noch die restlichen Formeln ein:
M18:M20
=INDEX(INDIREKT(“rD2.”&$M$17&rF1.JahrAusw&”Werte”);$I18;VERGLEICH(rF1.MonatAusw;rL1.MonateListe;0))
N18:N20
=M18-L18
O18:O21
=N18/L18
L21:N21
=SUMME(L18:L20)
Das Blatt „Focus 1“ ist jetzt fertig.
Blatt „Grafik 1“
Die Plan- und Istwerte der drei Kostenstellen will ich noch vergleichend in einem Diagramm darstellen. Das Diagramm soll im Blatt „Grafik 1“ abgelegt werden.
In „Focus 1“ markiere ich K18:M20, 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$17;’Focus 1′!$K$18:$K$20;’Focus 1’!$L$18:$L$20;1)
Ist =DATENREIHE(‘Focus 1′!$M$17;’Focus 1′!$K$18:$K$20;’Focus 1’!$M$18:$M$20;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.MonatAusw&” “&rF1.JahrAusw
In K9 steht jetzt: „Plan – Ist – Vergleich Mai 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:L15.
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 erste Beispiel zur Anwendung der rS1.Methode ist damit abgeschlossen. Beachte bitte, dass Du im Blatt „Focus 1“ in L14:L15 nur Auswahlen treffen kannst, die in Listen hinterlegt sind.
Das Modell enthält bisher nur Daten eines Jahres, ausgewählt werden können nur die Werte einzelner Monate. So sieht der Bericht aus:
Im nächsten Beitrag zu dieser Thematik stelle ich ein Modell vor, mit dem auch kumulierte Abfragen möglich sind.
Außerdem gebe ich allen, die Gefallen an der Methode finden, eine Übungsaufgabe mit auf den Weg.
4 Gedanken zu „Arbeiten mit der rS1.Methode – Namenskonventionen und Beispiel“