Wie schon in der Einleitung erwähnt, entstehen Komfort und Variationsbreite der hier exemplarisch gezeigten Beispiele durch die Verwendung spezifischer Excel-Funktionen, die im Zusammenwirken mit Steuerelementen eine hohe Flexibilität des Datenverkehrs zwischen verschiedenen Arbeitsblättern (und auch Arbeitsmappen!) ermöglichen.
5.1 Verwendung der Funktion INDEX
Formeln des Typs INDEX finden Sie in den Arbeitsblättern Focus 1, Daten 1, Listen 1 und Listen 2. Sie sind einfach zu konstruieren und besonders in statischen Strukturen von großem Nutzen.
Die Funktion INDEX in der Syntax =INDEX(Matrix;Zeile;Spalte) ermittelt einen Wert aus einer durch Bezug (Namensbezug oder Zellbezug) vorgegebenen Matrix. Die Position des Werts innerhalb dieser Matrix wird durch die Angabe von Zeile und Spalte bestimmt.
Am Beispiel zweier Formeln im Blatt Focus 1 (vgl. Abb. 7):
Abb. 7: INDEX-Zugriffe auf unterschiedliche Quellen mit variabler Zeilenvorgabe und konstanter Spaltenvorgabe
Die Formel =INDEX(rD1.Datenbereich;rL1.Konten01Ausw;C24) in Zelle C22 – exemplarisch für den Zellbereich C22:N22 – als Excel-Sprachanweisung: "Ermittle einen Wert in der Matrix, die den Namen rD1.Datenbereich hat (der Bereich E8:P24 im Blatt Daten 1). Benutze als Zeilenposition den (variablen) Wert, der aktuell in der Zelle rL1.Konten01Ausw vorgegeben ist (diese Zelle ist mit dem Steuerelement zur Kontenauswahl verknüpft). Benutze als Spaltenposition den (konstanten) Wert, der als Vorgabe in Zelle C24 hinterlegt ist."
In Abbildung 7 wurde das Steuerelement zur Kontenauswahl zur Seite geschoben, damit die dahinter befindliche Formel und deren Ergebnis gezeigt werden kann. Im Steuerelement ist aktuell ein abgekürzter Text zu sehen, in Zelle B22 und somit im Diagrammtitel dessen Langform. Die Formel als Anweisung: "Ermittle einen Wert in der Matrix, die den Namen rP1.Kontentexte hat (der Bereich C5:D21 im Blatt Parameter 1). Benutze als Zeilenposition die (variable) Vorgabe aus rL1.Konten01Ausw und als Spaltenposition die 2."
5.2 Verwendung der Funktion BEREICH.VERSCHIEBEN
Formeln des Typs BEREICH.VERSCHIEBEN finden Sie in den Arbeitsblättern Focus 2 und Listen 2. Sie sind in ihrem Effekt den INDEX-Formeln sehr ähnlich, besitzen aber einen größeren Leistungsumfang und vor allem eine sehr viel höhere Flexibilität, da ihr "Zielgebiet" nicht durch eine zumeist statische Matrix zu definieren ist, sondern sie ihre Zugriffe von einer einzigen Zelle aus in beliebige Richtung und Distanz richten können. Der entsprechende Ausgangs- bzw. Ankerpunkt ist in der hier beschriebenen Konstruktionsart der schon im Kapitel 3.3 erwähnte "Knoten".
Die Funktion BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;Höhe;Breite) liefert als Ergebnis einen Wert (Zellinhalt), der gegenüber dem angegebenen Argument Bezug um eine bestimmte, anzugebende Anzahl von Zeilen und Spalten versetzt ist. Das Argument Bezug definiert also den Ausgangspunkt für einen Zugriff auf eine andere Zelle oder einen Zellbereich. Die Argumente Höhe und Breite sind optional und werden hier nicht benutzt.
Am Beispiel zweier Formeln im Blatt Focus 2 und im Zusammenhang mit Abbildung 8:
Abb. 8: Die Formel im Bereich F5:M5 liefern der Wert #NV, wenn die Datenquelle leer ist
Die Formel
=BEREICH.VERSCHIEBEN(rD2.Knoten;rL2.KundeAusw02;D$25)
in Zelle D5 – exemplarisch für den Zellbereich C5:E5 –, als Anweisung: "Ermittle einen Wert. Gehe dazu von der Zelle mit dem Namen rD2.Knoten (Zelle B7 im Blatt Daten 2) so viele Zeilen nach unten, wie es der (variablen) Zahlenvorgabe in der Zelle mit dem Namen rL2.KundeAusw02 entspricht (hier wird nach Auswahl des gewünschten Kunden die aktuell benötigte Zeilenposition ausgewiesen, vgl. Abb. 9), und so viele Spalten nach rechts, wie es der als Konstante hinterlegten Zahlenvorgabe in Zelle D25 entspricht."
Die gleichartige, aber erheblich komplexere Variante in I5, exemplarisch für F5:M5,
=WENN(BEREICH.VERSCHIEBEN(rD2.Knoten;rL2.KundenAusw02;I$25)=0;#NV;
BEREICH.VERSCHIEBEN(rD2.Knoten;rL2.KundenAusw02;I$25))
führt eine Fehlerpufferung durch. Sie prüft zunächst, ob die bezogene Datenquelle (Zelle) leer ist (es liegt kein Umsatz vor), also den Wert Null übergibt. Wenn das so ist, wird statt der Null der Wert #NV in die Zelle geschrieben. Ansonsten wird – zweiter Teil der WENN-Formel – der in der Datenquelle vorhandene Wert übernommen.
Hinweis: Im Liniendiagramm sollen nur tatsächliche Umsatzwerte erscheinen. Deswegen hier die Verwendung des Fehlerwerts #NV, der im Diagramm das Zeichnen eines Datenpunkts definitiv unterdrückt. Damit dieser Fehlerwert in der Zelle jedoch unsichtbar bleibt, wird seine Anzeige durch eine bedingte Formatierung verhindert: Schriftfarbe gleich Hintergrundfarbe. Die dazu in der Formatierungsanweisung verwendete Formel ist =ISTNV(Bezug). Untersuchen Sie diesbezüglich die Zellen F5:M5.
5.3 Verwendung der Funktion VERGLEICH
Bei statischer Berichtsstruktur und gleichzeitig nicht statischer Abrufstruktur sind die bislang beschriebenen Formeln zwar anwendbar, bedürfen aber einer Hilfestellung. Im Beispiel 2 ist das aus dem Klick in ein Steuerelement resultierende Zeilenargument nicht eindeutig, weil von...