Nach dem prinzipiellen Aufbau der Musterlösung stellen wir Ihnen nun die verwendeten Formeln und Steuerelemente vor.
3.1 Steuerelemente
Die beiden zur Dynamisierung des Modells benutzen Steuerelemente gehören zur Gruppe der Formularsteuerelemente; die Funktionalisierung können Sie Abbildung 5 entnehmen.
Die rS1.Methode
Excel-Lösungen, die mit Steuerelementen dynamisiert werden, sind vielgestaltig erweiter- und ausbaubar. Ihre hier gezeigten Strukturen und Funktionalisierungen folgen den Vorgaben der "rS1.Methode", eines vom Autor erfundenen und schon seit vielen Jahren eingesetzten Systems zur Strukturierung und Systematisierung von komplexen Kalkulationsmodellen.
Beachten Sie, dass für die Zellverknüpfungen (D25 und D7 im Blatt Basis 1) der Steuerelemente die Bereichsnamen dieser Zellen, also rB1.ReiheAusw und rB1.KstAusw benutzt wurden. Solche standardisierten Namen (die Suffix-Ergänzung Ausw steht bei der hier benutzten rS1.Methode immer für eine Anwenderauswahl) erleichtern das Verständnis von Formeln sehr, wie weiter unten erkennbar wird.
Steuerelemente ab Excel 2010
Der Zugriff auf die Steuerelemente erfolgt über Registerkarte Entwicklertools → Steuerelemente → Einfügen. Die Verknüpfung des Formularsteuerelements ist eine Formateigenschaft des Objekts (Steuerung → Zellverknüpfung).
Die Registerkarte Entwicklertools muss unter Excel 2010 und neuer nach Standardinstallation vom Benutzer zunächst im Menüband verfügbar gemacht werden: Datei → Optionen → Menüband anpassen → bei Hauptregisterkarten die Entwicklertools aktivieren.
WICHTIG: Erzeugen Sie bei der Konstruktion Ihrer Lösung zuerst die Steuerelemente und ihre Funktionalisierung, bevor Sie mit dem Aufbau des damit beeinflussbaren Formelwerks beginnen.
Abb. 5: Die Festlegungen für die beiden Steuerelemente
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 We...