Die Musterlösung beinhaltet die 4 Tabellen Daten 1, Basis 1, Focus 1 und Namensliste. Im Folgenden stellen wir Ihnen den Aufbau der Musterlösung vor.
2.1 Arbeitsblatt Daten 1
Das Arbeitsblatt Daten 1 ist der "Datenhalter" des Modells und dient daneben der Stammdatenpflege (Kontenbezeichnungen).
Besonders erwähnenswert ist hier die bedingte Formatierung (vgl. Abb. 3), mit der Maxima und Minima jeder Datenzeile im Bereich I9:P18 angezeigt werden.
Wenn Sie ein solches formelbasiertes Format für alle Zellen eines Bereichs gleichzeitig definieren wollen, müssen Sie zuerst den gesamten Bereich Ihrer Wahl markieren (hier $I9:$P18), dann über Start → Formatvorlagen → Bedingte Formatierung → Neue Regel das Dialogfeld öffnen, dort die Vorgabe Formel … verwenden auswählen und eine Formel eingeben, mit der Sie faktisch eine "Behauptung" aufstellen.
Mit =I9=MIN($I9:$P9) behaupten Sie, dass der Inhalt der Zelle I9 das Minimum der Werte im Zellbereich I9:P9 ist. Trifft dies zu, reagiert Excel darauf also mit dem logischen Resultat WAHR, dann kommt das definierte Format zur Anzeige (z. B. Färbung von Zelle und/oder Schrift), ansonsten nicht. Der erste Bezug in der Formel I9 ist relativ. Der zweite Bezug $I9:$P9 ist zwar spaltenabsolut, bezüglich seiner Zeilendefinition aber relativ.
Wenn Sie den Bereich komplett markiert haben und bei der Eingabe solcher Formeln die Unterschiede zwischen relativen und absoluten Bezügen konsequent beachten, können Sie die ganze Angelegenheit in einem einzigen Arbeitsgang erledigen. Im vorgestellten Fall müssen Sie also nicht zeilenweise formatieren oder Formate kopieren. Das für die Zeile 9 als zeilenrelativ definierte Format wird für alle Zeilen im markierten Bereich $I9:$P18 übernommen.
Abb. 3: Farbsignale für zeilenspezifische Maxima und Minima mittels bedingter Formatierung
2.2 Arbeitsblatt Basis 1
Durchaus komplizierter geht es im Arbeitsblatt Basis 1 zu, das die komplexe Datenquelle des Präsentationsdiagramms darstellt. Der Blattinhalt besteht mit wenigen Ausnahmen aus Formeln, deren Aufbau und Wirkung weiter unten beschrieben ist.
Zunächst aber einige Anmerkungen zur Struktur dieses Zentralelements. Die folgenden Hinweise beziehen sich auf die Kennziffern in Abbildung 4:
- Die Ziffern in den funktional überaus wichtigen Hilfsbereichen E9:P9 und D12:D23 sind Argumente für etliche der Formeln. Der zweitgenannte, vertikale Hilfsbereich besteht teilweise ebenfalls aus Formeln (Formeln liefern Argumente für andere Formeln).
- Hier werden die Konten-Textbezeichnungen aus dem Blatt Daten 1 übernommen.
Die Formeln im Bereich F13:P23 extrahieren aus Daten 1 die Werte der aktuell eingestellten Kostenstelle (vgl. Ziffer 10) und stellen, mit Ausnahme der Summenzelle F13, so viele identische Spalteninhalte nebeneinander, wie es Konten gibt. Im Ergebnis fließen alle diese Daten in das Diagramm, das aus 11 gestapelten Säulen besteht. Sichtbar gemacht werden dort aber nur jene Daten, die im Bereich F13:P23 mit einer Blaufärbung gekennzeichnet sind.
Abb. 4: Die Funktionalität der Diagrammbasis wird von einem komplexen Formelwerk gesteuert
- Zur Beschriftung der ersten Zeile der Rubrikenachse werden die Texte aus dem Bereich 2 übernommen.
- Zur Beschriftung der zweiten Zeile der Rubrikenachse werden aus Bereich 3 jene Werte übernommen, die im Diagramm tatsächlich visualisiert werden.
- Zur Beschriftung der dritten Zeile der Rubrikenachse werden Prozentwerte gebildet: Relative Anteile der Kostenarten an den Gesamtkosten.
- Hier erzeugt eine Formel per Übernahme aus Daten 1 den Namenstext der aktuell eingestellten Kostenstelle (vgl. Ziffer 10).
- Die Formeln im Bereich D14:D23 des vertikalen Hilfsbereichs sorgen dafür, dass es per Mausklick möglich ist, die Reihenfolge der Kostenarten im Diagramm umzukehren.
- Das Steuerelement Drehfeld befindet sich nur für Testzwecke in diesem Blatt. Es ist eine Kopie des Elements, das Sie im Blatt Focus 1 benutzen. Funktional ist es nicht mehr als ein Hin-und-Her-Schalter. Er gibt in der Zelle D25, die den Namen rB1.ReiheAusw hat, entweder eine 1 oder eine 2 aus. Diese Werte werden von den WENN-Formeln im Bereich D14:D23 (vgl. Ziffer 8) verarbeitet.
- Auch das Steuerelement Bildlaufleiste ist hier nur eine Arbeitskopie für Testzwecke. Sie gibt in der Zelle D7, die den Namen rB1.KstAusw hat, eine Zahl zwischen 1 und 9 aus. Diese Vorgabe beeinflusst dann alle kostenstellenspezifischen Kalkulationen des Blattes. Sie bestimmen also mit diesem Steuerelement, welche Kostenstelle mit ihren spezifischen Kostenverteilungen im Diagramm erscheinen soll.
- Die Formeln im Bereich G24:P24 produzieren den Wert 1, wenn der spaltengleiche Wert in Zeile 11 der größte des Bereichs G11:P11 ist.
2.3 Arbeitsblatt Focus 1
Die Inhalte und Darstellungsreihenfolgen im Blatt Focus 1 werden mit den beiden Steuerelementen bestimmt (vgl. oben bei den Ziffern 9 und 10), die hilfsweise auch im Blatt Basis 1 vorhanden sind.
In der verbundenen Zelle E5 befindet sich die Formel zur Herstellung einer dynamischen Diagrammüberschrift. Sie variiert in Abhängigkeit von der aktuellen Einstellung des Steuereleme...