Beitrag aus SmartTools Excel Weekly
Bedingte Formatierung mit mehreren Alternativkriterien, Teil 2
Excel 365 2024 2021 2019 2016 2013
Bedingte Formatierungen sind praktisch, wenn Sie Zellen in Abhängigkeit von einem Kriterium hervorheben wollen. Manchmal darf es aber auch ein bisschen mehr sein. Stellen Sie sich zum Beispiel vor, Sie wollten Zellen hervorheben, wenn sie einen bestimmten Text enthalten, - das aber nur, wenn in einer anderen Zelle ebenfalls ein bestimmter Wert steht. Auch eine solche Abhängigkeit von mehreren Kriterien lässt sich mit Excels bedingter Formatierung realisieren. Wie das geht, verraten wir Ihnen in diesem Tipp.
Im ersten Teil hatten Sie bereits erfahren, wie Sie eine einzelne Zelle aufgrund verschiedener Alternativkriterien hervorheben können. Als Beispiel diente eine Mitarbeitertabelle für Stundenkontingente, in der Abwesenheitskürzel in Abhängigkeit vom jeweiligen Wochentag eingefärbt werden sollten.
Die Überprüfung der Wochentage erfolgte über einen Vergleich mit den Tagesbezeichnungen "Sa" und "So". Hier noch einmal die Formel für die bedingte Formatierung von Zelle B7:
=UND(ODER(B7="U";B7="K");ODER(B5="Sa";B5="So"))
Das funktioniert, wenn Sie das Wochentagskürzel entweder manuell in Zelle B5 eingeben oder wenn Sie es mit einer TEXT-Funktion aus dem Datumswert in Zelle B4 berechnen lassen:
=TEXT(B4;"TTT")
Eine Ausgabe des Wochentags wäre aber auch möglich, indem Sie in Zelle B5 das Datum aus Zelle B4 wiederholen:
=B4
... und Zelle B5 dann nach Drücken von Strg + 1 das benutzerdefinierte Zahlenformat "TTT" zuweisen.
Jetzt sehen Sie in Zelle B5 zwar auch Wochentagsnamen wie "Mo", "Di" oder "Sa". Eigentlich steht in der Zelle aber ein Datumswert, weshalb ein Textvergleich in der Formel für die bedingte Formatierung fehlschlägt.
Sie umgehen das Problem, indem Sie in der Formel nicht mit Textvergleichen, sondern mit der Funktion WOCHENTAG arbeiten. In der einfachsten Variante liefert "WOCHENTAG(<Datumswert>)" Ziffern von 1 bis 7 mit 1 für Sonntag, 2 für Montag bis 7 für Samstag.
Somit können Sie die Formel für die bedingte Formatierung von Zelle B7 wie folgt abwandeln, damit sie auch bei formatierten Datumswerten und ohne Textvergleich die gewünschten Resultate liefert:
=UND(ODER(B7="U";B7="K");ODER(WOCHENTAG(B5)=7;
WOCHENTAG(B5)=1))
Sie werden eine bedingte Formatierung aber nur selten einer einzigen Zelle zuweisen. Meist wenden Sie bedingte Formatierungen gleich auf einen größeren Zellbereich an. Im Prinzip können Sie dann einfach die Zelle, der Sie die bedingte Formatierung zugewiesen haben, in andere Zellen kopieren. Ob sie danach noch korrekt funktionieren, hängt aber von den Bezugsarten ab, die Sie in der Formel verwendet haben.
Im bisherigen Beispiel haben wir nur relative Bezüge verwendet. So ist die bedingte Formatierung immer von der aktuellen Zelle und der Zelle zwei Zeilen darüber abhängig. Wenn Sie Zelle B7 in derselben Zeile nach rechts kopieren, bleibt die bedingte Formatierung somit intakt.
Wenn Sie Zelle B7 jedoch nach unten kopieren, funktioniert die Formatierung nicht mehr, weil in der zweiten ODER-Funktion ja nicht die Zelle zwei Zeilen darüber geprüft werden soll, sondern prinzipiell der Inhalt in Zeile 5.
Um Kopiervorgänge flexibler zu gestalten, setzen Sie in der Bedingungsformel einen gemischten Bezug ein: mit relativer Spaltenangabe und absoluter Zeilenangabe. Absolute Bezüge kennzeichnen Sie mit einem $-Zeichen.
Bearbeiten Sie also die bedingte Formatierung in Zelle B7 und ändern Sie sie in:
=UND(ODER(B7="U";B7="K");ODER(B$5="Sa";B$5="So"))
bzw.
=UND(ODER(B7="U";B7="K");ODER(WOCHENTAG(B$5)=7;
WOCHENTAG(B$5)=1))
Beachten Sie, dass der Bezug auf die aktuelle Zelle weiterhin komplett relativ ist. Nur so kann Excel diesen Bezug immer automatisch an die Zielzelle eines Kopiervorgangs anpassen. Dagegen passt Excel in der zweiten ODER-Funktion nur jeweils den Spaltenbezug an. Die Prüfung bezieht sich aber immer auf Zeile 5. So können Sie Zelle B7 sowohl nach rechts als auch nach unten kopieren, ohne dass die bedingte Formatierung verloren geht.