Feiertage automatisch farblich hervorheben
Für einige Planungen ist es notwendig, frühzeitig die kommenden Feiertage zu berücksichtigen. So zum Beispiel in einem Projekt, wenn die zur Verfügung stehenden Tage und Stunden zu planen sind. In einer Tagesplanung können Sie die Daten der Feiertage dann auch auffällig formatieren, damit sie sichtbar werden und gegenwärtig bleiben. In Tipp Nr. 157 hatten wir bereits Wochenenden farblich hervorgehoben – hier geht es an die Feiertage!
Als Beispiel nehmen wir jedoch der Einfachheit halber nur einen Jahreskalender mit der Darstellung aller Tage des Jahres. So könnte das fertige Kalenderblatt mit den dann gelb markierten Feiertagen aussehen, bei dem die Daten in den Zellen B7:Y37 stehen:
Bild 1: Kalenderblatt mit farbigen Feiertagen
Um in diesem Kalenderblatt alle Zellen, die ein Datum enthalten, welches einem Feiertag entspricht zu kennzeichnen, benötigen wir auch noch eine Liste aller Feiertage. Diese nehmen wir aus unserem Excel-Tool "Dynamischer Kalender", in dem alle Feiertage eines Jahres nach Bundesländern separiert aufgelistet sind. Die Tage selber stehen in dem Tabellenblatt "Feiertagsberechnung" im markierten Bereich F11:F55.
Bild 2: Liste aller Feiertage in F11 bis F55
In dem Kalenderblatt wählen wir als erstes alle Zellen aus, die ein Datum enthalten, auf das ggfs. ein Feiertag entfällt. Sollten dazwischen andere Spalten oder Zeilen mit anderen Werten stehen, ist das auch nicht tragisch. Wir lösen die Aufgabe nämlich mit der Bedingten Formatierung und einem SVERWEIS.
Wir gehen als nächstes also im Register Start auf Bedingte Formatierung und wählen Neue Regel ...
Bild 2: Bedingte Formatierung und neue Regel wählen
Für die Neue Formatierungsregel wählen Sie den Regeltyp „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ aus und geben in das Feld für die Regelbeschreibung folgende Funktion ein:
=SVERWEIS(B7;Feiertagsberechnung!$F$11:$F$55;1;0)
Bild 3: Regelbeschreibung mit SVERWEIS
Der SVERWEIS prüft, ob er eine Übereinstimmung zwischen den Daten im Kalenderblatt und den Daten in der Liste der Feiertage findet. Da wir im Kalenderblatt alle Zellen markiert hatten, gilt der SVERWEIS nicht nur in Zelle B7, sondern für den gesamten markierten Bereich. Hierfür ist es wichtig, das Suchkriterium B7 ohne einen absoluten Bezug ($-Zeichen) zu schreiben. Anders ist es bei der folgenden Verweismatrix: Diese benötigt die absoluten Bezüge ($F$11:$F$55). Der Spaltenindex lautet 1, da wir nur eine Spalte haben und die 0 führt zu genau übereinstimmenden Werten.
Jetzt können Sie noch ein Format festlegen: Button „Formatieren ...“ drücken und in dem sich dann öffnenden, bekannten Fenster „Zellen formatieren“ die gewünschte Farbe bzw. den Hintergrund wählen.
Das hier angewandte Excelbeispiel Dynamischer Kalender, der für jedes Jahr die Feiertage automatisch berechnet, finden Sie neben vielen anderen Kalenderarten in unseren Allg. Vorlagen.
In Kooperation mit ...
Seminartipps ...
Startseite > Excel-Tipps und -Tricks > Exceltipp 164
