In den meisten Anwendungsfälle der bedingten Formatierung in Excel beziehen sich die Bedingung und die Formatierung auf die selbe Zelle. Wenn beides auseinander läuft, müssen Sie besondere Vorkehrungen treffen, was dieser Beitrag anhand eines Schichtplans erklärt.
Die Bedingte Formatierung mit einer eigenen Formel
Angenommen, Sie haben einen in Excel gebauten Plan für eine Einteilung von Mitarbeitern in verschiedene Schichten. Der soll nun so aufgewertet werden, dass die Felder unterhalb einer Zelle mit einem Datum rot werden, wenn das Datum darüber ein Sonntag ist. Dazu kann man keine vorgefertigte Funktion nutzen, sondern muss man eine selbst geschriebene Formel in der Bedingung verwenden. Hier eine mögliche Ausgangstabelle, die nur eine Grundstruktur darstellt:
Zuerst ist einmal wichtig, wie man überhaupt erkennt, dass ein Datum auf einen Sonntag fällt. Das geht einfach über die Funktion WOCHENTAG(), die für eine Zelle den Tag in numerischer Form ausspuckt. Für einen Sonntag liefert Sie dabei den Wert „1“ zurück.
So nutzen Sie WOCHENTAG() in einer Bedingten Formatierung
Markieren Sie den gesamten inneren Bereich der Tabelle. In unserem Beispiel wäre das B4 bis E6.
Klicken Sie im Startregister auf Bedingte Formatierung – Neue Regel – Formel zur Ermittlung….
Als Formel geben Sie ein: =WOCHENTAG(B$3)=1
Dann wählen Sie noch eine Formatierung, etwa das Füllen mit einem rötlichen Hintergrund.
Das Ergebnis sieht dann so aus:
Um auszuprobieren, ob das wirklich so klappt, könnten Sie als Datumswert über eine der noch weißen Spalten das Datum 11.6.2017 schreiben. Weil das auch ein Sonntag ist, müssen die Felder darunter rot werden, sobald Sie das neue Datum eingegeben haben.
Wollen Sie nicht nur den Sonntag farbig markieren, sondern auch den Samstag, dann ändern Sie die Formel so ab: =WOCHENTAG($B3;2) > 5. Möchten Sie wissen, wie diese Variante beide Tage erfassen kann, dann lesen Sie hier nach: Excel: Wochenende und Wochentage optisch unterscheiden.
Darum benötigt die Formel das Dollarzeichen
Wenn Sie einen Bereich für die Bedingte Formatierung markieren und in der Formel eine Referenzzelle angeben, wie hier B3, dann würde Excel diese Formel für die Zelle ganz links oben verwenden und bei jeder anderen Zelle den Bezug anpassen. In Zelle B5 etwa würde die Formatierung auf den Inhalt von B4 testen, wo gar kein Datumswert steht.
Mit dem $ erreichen Sie , dass Excel diese Anpassung in vertikaler Richtung sein lässt. In der Folge beziehen sich alle Formatierungen unterhalb von B3 also genau auf diese eine Zelle. Eine Spalte weiter rechts ändert Excel den Bezug automatisch auf C3, weil hier vor dem Adressteil kein Dollarzeichen steht. Das Ergebnis ist also genau das, was wir erreichen wollen.
Würden Sie beispielsweise als Inhalt der Formel schreiben =WOCHENTAG($B$3)=1, würde Excel alle markierten Felder nur im Bezug zu B3 formatieren. Das Dollarzeichen fixiert die entsprechende Koordinate quasi.
Wenn Sie noch mehr zum Thema lesen möchten, finden Sie hier eine Übersicht zur bedingten Formatierung mit Excel.
