Professionelles Berichtswesen: Variable Daten mit dynami ... / 2.3 Vorgaben aus der Gültigkeitsliste in Formatierungsregeln

Im Blatt Tabellen 2020 werden die variablen Formatierungsvorgaben mit Formeln erzeugt. Für Tabelle 1 wurden keine solchen Festlegungen getroffen. Der Zugriff auf das entsprechende Dialogfeld (am besten bereits vorher den zu untersuchenden/zu ändernden Bereich markieren) gelingt mit dem Befehlsweg Start → Bedingte Formatierung → Regeln verwalten → oben im Dialogfeld ggf. Bereich auswählen → gewünschte Regel auswählen → Schaltfläche Regel bearbeiten → dann ggf. weiter mit Regeländerung (Änderung der Formel) oder zugehörige Formatierungsänderung (Klick auf Schaltfläche Formatieren).

Hinweis

Bedingte Formatierung

Seit Excel 2007 hat die bedingte Formatierung einen enormen Zuwachs an Möglichkeiten erfahren, nicht alle übrigens von Vorteil. Die Handhabung des seitdem leider auch viel zu kompliziert gestalteten Werkzeugs hier auch nur halbwegs deutlich zu beschreiben, ist leider unmöglich. Entsprechende Vorkenntnisse müssen also vorausgesetzt werden.

Regeln für die bedingte Formatierung der Tabelle 2

Die verschiedenen Regeln für die Zahlenformate im Bereich $S$7:$X$19 der Tabelle 2 lauten dann wie folgt:

=LINKS($Z$9;1)="B" mit benutzerdefiniertem Tausender-Zahlenformat #.##0,0. "T"

=LINKS($Z$9;1)="C" mit benutzerdefiniertem Tausender-Zahlenformat #.##0,00. "T"

=LINKS($Z$9;1)="D" mit Standard-Zahlenformat #.##0,00

Wenn das jeweilige Formelresultat WAHR ist, wird das zugeordnete Format angewandt. Die Zahlenformate sind im Arbeitsblatt wie auch nachstehend mit ihren relevanten Excel-Codierungen angegeben. Die Stammformatierung des Bereichs ist #.##0 und wird angezeigt, wenn in der Liste der Eintrag A gewählt wurde, also keine der vorhandenen Formatierungsregeln greift.

Regeln für die bedingte Formatierung der Tabelle 3

Im Unterschied zu Tabelle 2 wird nun bei Tabelle 3 die bedingte Formatierung für verschiedene Zellenfärbungen im Bereich =$G$25:$K$37 eingesetzt:

Zunächst wird in Zelle A27 mit der Formel =RECHTS($B$27;1) das letzte Zeichen des Textes in Zelle B27 ausgelesen. Das Formelergebnis kann also x, n, s oder e sein. Es ist per Schriftfärbung unsichtbar gemacht. Die sich darauf beziehenden Formeln der Formatierungsregeln erzeugen in horizontaler Richtung (Verteilung nach Orten) Rot- und/oder Grünfärbungen einzelner Zellen. Bei einer ökonomischen Festlegung – pro Färbung nur eine Formel für den gesamten Bereich G25:K37 – spielen die Bezugsarten der Formel eine entscheidende Rolle.

Zunächst wurde der gesamte Bereich markiert, die aktive Zelle ist oben links G25. Dann öffnen Sie das Dialogfeld und geben die folgende Regel (Formel) ein:

=UND(ODER($A$27="n";$A$27="s");G25=MIN($G25:$K25))

Diese Formel ist exemplarisch für alle Zellen im Bereich, verbunden mit der Rotfärbung einer Zelle, wenn erstens in Zelle A27 entweder ein n ODER ein s steht UND zweitens gleichzeitig der Wert in der Zelle der kleinste in seiner Zeile ist.

Die zeilenspezifische, gleichzeitig und gleichartig aber für alle Zeilen des Bereichs geltende Wirkung wird mit dem Formelteil G25=MIN($G25:$K25) erreicht. G25 als relativer Bezug (gültig in allen Zellen) und $G25:$K25 als spaltenabsoluter Bezug, bewirken Zeile für Zeile, dass deren jeweils kleinste Zahl rot hinterlegt wird, wenn in der Gültigkeitsliste entweder der Eintrag nur Min oder der Eintrag beides gewählt wurde.

Sinngemäß gilt das Gleiche natürlich für jene Regel, die zeilenspezifisch auf das Maximum reagiert. Die Regel

=UND(ODER($A$27="x";$A$27="s") ; G25=MAX($G25:$K25))

ist exemplarisch für alle Zellen im Bereich verbunden mit der Grünfärbung einer Zelle, wenn erstens in Zelle A27 entweder ein x ODER ein s steht UND zweitens gleichzeitig der Wert in der Zelle der größte in seiner Zeile ist. Dies geschieht also, wenn in Gültigkeitsliste entweder der Eintrag nur Max oder der Eintrag beides gewählt wurde.

Wenn die Vorgabe beides gewählt wurde (in A27 steht also das Zeichen s) greifen beide Regeln, in jeder Zeile ist jeweils eine der Zellen rot und eine andere grün.

Wenn die Vorgabe keine gewählt wurde (in A27 steht also das Zeichen e) trifft keine der Regeln zu. Für diesen Fall zeigt der Bereich seine ungefärbte Stammformatierung.

Regeln für die bedingte Formatierung der Tabelle 4

Die verschiedenen Regeln für die Zellenfärbungen im Bereich $S$25:$X$36$:der Tabelle 4 lauten wie folgt. Die Regel

=UND(ODER($AA$27="n";$Y$27="s");S25=MIN(S$25:S$36))

mit Rotfärbung sowie die Regel

=UND(ODER($AA$27="x";$Y$27="s");S25=MAX(S$25:S$36))

mit Grünfärbung entsprechen nach Aufbau und Wirkungsart nahezu vollständig den zuvor für Tabelle 3 beschriebenen Erläuterungen. Der einzige, aber wesentliche Konstruktionsunterschied: Die Färbungen reagieren spaltenspezifisch (also Minimum und Maximum nach Produkten). Ursache hierfür ist der zeilenabsolute Bezug im Formelteil MIN(S$25:S$36) bzw. MAX(S$25:S$36).