Workshop Profi-Berichte: Dynamische Präsentationen mit " ... / 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.