Kostenanalyse mit dynamischem Wasserfalldiagramm / 3.2 Formeln im Blatt Basis 1

Zunächst einige Basisinformationen zu den hier hauptsächlich benutzten Funktionen:

• Die Funktion =INDEX(Matrix;Zeile;Spalte) ermittelt einen Wert aus einer vorgegebenen Matrix, dessen Position durch die Angabe von Zeile und Spalte bestimmt ist.
• 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 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 in dieser Beispiellösung nicht benutzt.
• Die Funktion =WENN(Prüfung;Dann_Wert;Sonst_Wert) prüft eine Behauptung und reagiert auf das Prüfergebnis. Im Argument Prüfung wird praktisch eine Behauptung aufgestellt. Trifft diese zu, ist das Ergebnis der Prüfung der logische Wert WAHR und die Formel reagiert mit dem Dann_Wert. Trifft sie nicht zu, liefert das Prüfungsergebnis den Wert FALSCH und die Formel reagiert mit dem Sonst_Wert.

Nun zu den Formeln im Arbeitsblatt Basis 1 und zu ihrer Bedeutung für das Modell. Beachten Sie besonders die Verwendung der beiden benannten Zellen, die mit den Steuerelementen verknüpft sind sowie die Nutzung der Argument-Vorgabewerte in den Hilfsbereichen E9:P9 und D14:D23.

Die nachstehenden Informationen folgen der Systematik des Kapitelabschnitts 2.2 (s. Abb. 4). Dabei wird für jeden Bereich gleichartiger Formeln eine dieser Formeln exemplarisch beschrieben. Dies geschieht in Form einer umgangssprachlichen Anweisung, so als würde Excel ein mündlicher Auftrag erteilt. Soweit erforderlich sind der Anweisung erläuternde Bemerkungen hinzugefügt:

• Die Formel BEREICH.VERSCHIEBEN(rD1.Knoten;$D13;E$9) in Zelle E13, exemplarisch für den Bereich E13:E23, als Anweisung: "Ermittle den Wert einer Zelle, ausgehend von der Zelle, die rD1.Knoten heißt (das ist die Zelle G7 im Arbeitsblatt Daten 1), so viele Zeilen nach unten, wie in Zelle D13 steht, und so viele Spalten nach rechts, wie in Zelle E9 steht."

  Die Vorgaben der Zeilenargumente in der Bezugsspalte D sind von D14 bis D23 variabel, deswegen ist die Reihenfolge der Kostenarten umschaltbar.

• Die Formel =BEREICH.VERSCHIEBEN(rD1.Knoten;$D13;rB1.KstAusw) in Zelle F13 exemplarisch für den Bereich F13:P23: Wie vorstehend, aber mit dem Spaltenargument aus Zelle D7, die den Namen rB1.KstAusw hat. (Nur das Zeilenargument ist variabel, somit entstehen im Bereich G14:P23 spaltenidentische Werte.)
• Die Formel =INDEX(rB1.Konten;F$9;1) in Zelle F12, exemplarisch für den Bereich F12:P12, als Anweisung: "Ermittle den Wert einer Zelle, die sich in der Matrix mit dem Namen rB1.Konten (E13:E23) befindet. Wähle innerhalb der Matrix die Zeilenposition, die in Zelle F9 steht, und benutze die Spalte 1 der Matrix (die hier die einzige ist)."
• Auf diese Weise entsteht eine Zeile mit den Kontenbezeichnungen, die im Diagramm als erste Zeile der 3-zeiligen Rubrikachsenbeschriftung erscheinen. Beachten Sie bitte, dass die Werte in der Hilfszeile 9 ab Zelle G9 von 11 nach 2 absteigend sind.

• Die Formel =INDEX(F$13:F$23;F$9;1) in Zelle F11, exemplarisch für den Bereich F11:P11, als Anweisung: "Ermittle den Wert einer Zelle, die sich in der Matrix F$13:F$23 befindet. Wähle innerhalb der Matrix die Zeilenposition, die in Zelle F9 steht, und benutze die Spalte 1 der Matrix (die hier die einzige ist)."

  Auf diese Weise entsteht eine Zeile mit jenen Eurowerten, die im Diagramm den sichtbaren Säulensegmenten entsprechen und die zweite Zeile der dreizeiligen Rubrikenachsenbeschriftung bilden.

• Die Formel =F$11/$F$13% in Zelle F10, exemplarisch für den Bereich F10:P10, als Anweisung: "Dividiere den Wert in Zelle F11 durch ein Hundertstel des Wertes in Zelle F13."

  Auf diese Weise entsteht eine Zeile mit jenen Prozentwerten, die im Diagramm den sichtbaren Säulensegmenten entsprechen und die dritte Zeile der 3-zeiligen Rubrikachsenbeschriftung bilden. Wichtig: Der Bezug $F$11 ist absolut. Das benutzerdefinierte Zahlenformat des diese Formeln enthaltenden Bereichs ist 0 "%".

• Die Formel =BEREICH.VERSCHIEBEN(rD1.Knoten;$D12;rB1.KstAusw) in Zelle E12 als Anweisung: "Ermittle den Wert einer Zelle, ausgehend von der Zelle, die rD1.Knoten heißt, so viele Zeilen nach unten, wie in Zelle D12 steht, und so viele Spalten nach rechts, wie in der Zelle rB1.KstAusw (Zelle D7) steht."

  So wird die Textbezeichnung der aktuell per Bildlaufleiste eingestellten Kostenstelle aus dem Blatt Daten 1 übernommen.

• Die Formel =WENN(rB1.ReiheAusw=1;11;2) in Zelle D14 als Anweisung: "Wenn der Wert in der Zelle mit dem Namen rB1.ReiheAusw (Zelle D25) 1 ist, dann schreibe eine 11, ansonsten eine 2."

  So wird der Ausgangswert für eine aufsteigende oder absteigende Zahlenfolge gebildet.

• Die Formel =WENN(rB1.ReiheAusw=1;D14-1;D14+1) in Zelle D15, exemplarisch für den Bereich D15:D23, als Anweisung: "Wenn der Wert in der Zelle mit dem Namen rB1.ReiheAusw gleich 1 ist, dann subtrahiere 1 von dem Wert in D14, an...