Ziel dieses Workshops ist es einen Kalender zu erstellen, der nur durch Eingabe der Jahreszahl automatisch ein Kalenderblatt erzeugt, in dem die Wochenenden und die gesetzlichen Feiertage markiert und die Anzahl der Arbeitstage angezeigt werden. Der Kalender soll für die Nutzung der Design-Features der neueren Excel Versionen vorbereitet sein. In diesem 3. Teil des Workshops meistern Sie die “Bedingte Formatierung”.
Sie könnten die beiden ersten Teile der Workshops “Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 1 — Grundlagen” und “Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 2 – Programmierung” nacharbeiten und Ihre eigene Kalenderdatei erstellen. Oder Sie laden sich eine der beiden Dateien herunter und starten mit dem Workshop Ihrer Wahl:
Die Dateien des Workshop Teil 3 finden Sie wie immer am Ende dieses Blog Post. Klicken Sie auf ein Bild für eine vergrößerte Darstellung.
Was noch fehlte
Im Teil 2 dieser Workshop Reihe fehlte noch das Formatieren der Zellen in denen ein Feiertag oder möglicherweise die Kalenderwoche steht. die Berechnung hatten Sie schon programmiert. Markieren Sie bitte die Zellen A4:F4 und füllen Sie sie bitte bis zur Zeile 9 nach unten aus. Wenn Sie alles richtig gemacht haben, dann sollten Sie beim 1. Januar linksbündig “Neujahr” stehen haben und beim 3. Januar und 7. Februar eine 1 bzw. 6, das sind die Kalenderwochen. Wir möchten im Feld aber 1. KW bzw. 6. KW stehen haben, also bemühen Sie die benutzerdefinierte Zellformatierung. Markieren Sie bitte die Zellen C3:C9 und F3:F9 (<STRG> Taste), klicken mit der rechten Maus und wählen “Zellen formatieren”. Geben Sie als Typ [0”. KW”] ohne die eckigen Klammern ein. Also Null Anführungszeichen, Punkt, Leerzeichen, KW und dann Anführungszeichen.
![Geben Sie als Typ [0”. KW”] ohne die eckigen Klammern ein.](http://kuettner.it/wp-content/uploads/2011/03/kal302.jpg "Geben Sie als Typ [0”. KW”] ohne die eckigen Klammern ein.")
Schön erkenn Sie, wir haben zwar für alle Zellen eine Zahlenformatierung definiert, diese gilt aber nur für die Zahlen, hier die Kalenderwochen. Wenn Sie einmal die Zellen markiert haben, ordnen Sie den Inhalt bitte linksbündig an.
Die Bedingte Formatierung
Folgende Formatierungen für Feiertage, Wochenenden und Montage wollen wir vornehmen:
- der Tag soll eine mittlere Füllfarbe und weiße Schrift bekommen,
- der Wochentag bekommt eine mittlere Füllfarbe und
- das Feld Feiertag/KW bekommt ebenfalls eine mittlere Füllfarbe.
- Das Feld Feiertag/KW bekommt eine mittlere Schriftfarbe, sofern es ein Montag ist, der kein Feiertag ist.
Wochenende: WOCHENTAG(Datum;2)>5
Ein Datum fällt auf ein Wochenende, wenn der Wochentag größer als 5 ist, nämlich Samstag oder Sonntag
NICHT( ISTFEHLER( FINDEN(Datum;Feiertagsliste) ) )
Ein Feiertag ist, wenn beim Suchen in der Feiertagsliste kein Fehler auftritt. Datum ist ein Feiertag, dann liefern die Funktion FINDEN keinen Fehler, damit liefert die Funktion ISTFEHLER den Wert FALSCH, die Funktion NICHT kehrt den Wert zu WAHR um. Damit hier die Fehler der Nichtfeiertage abgefangen werden können, wird diese Konstruktion benötigt.
UND( ISTFEHLER( FINDEN(Datum;Feiertagsliste));
WOCHENTAG(A3;2)=1)
Die Kalenderwoche wird nur eingetragen, wenn der Montag kein Feiertag ist. Also nur wenn Wochentag=1 (Montag) UND beim Suchen in der Feiertagsliste ein Fehler auftritt, das Datum also kein Feiertag ist.
Mit den folgenden zwei Formeln lösen Sie die Bedingte Formatierung für die oben aufgestellten Regeln. ACHTUNG! Bezugszelle ist hier A3.
=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5) =UND(ISTFEHLER(FINDEN(A3;Feiertagsliste));WOCHENTAG(A3;2)=1)
Markieren Sie bitte die Zellen A3:A9, beginnen Sie unbedingt mit Zelle A3, da Sie als Bezugszelle für die Formatierung der anderen Zellen benutzt wird. Würden Sie von unten her markieren, dann würde oben im Namenfeld nicht A3 sondern A9 stehen und die Formel müsste sich anstelle von A3 auf A9 beziehen. Also machen Sie es bitte wie vorgeschlagen.
Klicken Sie dann im Menüband “Start”, Gruppe “Formatvorlagen” auf “Bedingte Formatierung” und wählen “Neue Regel ..”.
Es öffnet sich das Dialogfenster “Neue Formatierungsregel”, wählen Sie hier “Formel zur Ermittlung der zu formatierenden Zellen verwenden” und geben dann im Feld “Werte formatieren, für die diese Formel wahr ist:” (rot) die Formel “=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)” ohne Anführungszeichen ein. Klicken Sie hiernach auf den Knopf Formatieren (gelb).
Im Dialogfenster Zellen formatieren wählen Sie für die Schrift ein Design-Weiß (grün) und für Ausfüllen ein mittleres Design-Blau (blau) aus. Nachdem Sie alle Dialoge mit OK geschlossen haben könnte Ihr Kalender wie abgebildet aussehen.
Nun formatieren Sie die Wochentage, diese sollen nur mit einem Füllmuster versehen werden, die Schriftfarbe bleibt unverändert. Markieren Sie bitte die Zellen B3:B9, beginnen Sie unbedingt mit Zelle B3!
Klicken Sie dann im Menüband “Start”, Gruppe “Formatvorlagen” auf “Bedingte Formatierung” und wählen “Neue Regel ..”.
Wählen Sie “Formel zur Ermittlung der zu formatierenden Zellen verwenden” und geben dann im Feld “Werte formatieren, für die diese Formel wahr ist:” (rot) die Formel “=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)” ohne Anführungszeichen ein. Als Füllmuster wählen Sie das gleiche mittlere Design-Blau wie bei den Tagen. Wenn alles funktioniert hat, dann sieht Ihr Kalender nun so aus.
Nun Formatieren Sie die Feiertage/KW. Die Formatierungsregel für die Feiertage kennen Sie schon, es ist die schon für die Tage und Wochentage verwendete. Markieren Sie die Zellen C3:C9. Erzeugen Sie eine neue formelbasierte bedingte Formatierung und verwenden Sie die folgende Formel und wählen ein mittleres Design-Blau als Füllmuster:
=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)
Jetzt müssen Sie die Formatierung für die Kalenderwoche festlegen, hier kein Füllmuster aber Schriftfarbe in mittleren Design-Blau.
Nachdem Sie die erste Formatierungsregel definiert haben, sind noch immer die Zellen C3:C9 markiert. Klicken Sie auf “Bedingte Formatierung” und wählen Sie nun “Regeln verwalten ..”. Im Dialogfenster “Manager für Regeln zur bedingten Formatierung” wählen Sie bitte “Neue Regel ..”.
Der weitere Ablauf ist analog zu den schon durchgeführten bedingten Formatierungen. Wählen Sie eine formelbasierte bedingte Formatierung, wählen Sie als Schriftfarbe ein mittleres Design-Blau. Geben Sie die folgende Formel ein:
=UND(ISTFEHLER(FINDEN(A3;Feiertagsliste));WOCHENTAG(A3;2)=1)
Nach dem Bestätigen (mehrere Male), sollten Sie die hellblaue 1. KW sehen können.
Nun haben Sie es fast geschafft, Sie müssen nur noch die bedingten Formatierungen für Tage, Wochentage und Feiertag/KW vom Januar auf den Februar übertragen. Das Erledigen Sie kinderleicht mit Format-Übertragen (-Pinsel).
Markieren Sie hierzu die Zellen A3:A9, also vom 01. bis 07. Januar, und klicken dann auf im Menüband “Start”, Gruppe “Zwischenablage” auf “Format-Übertragen”. Danach markieren Sie den Bereich D3:D9 in einem Zug und die Formatierung wird übertragen. Das wiederholen Sie bitte für die Wochentage und die Feiertage/KW. Wenn alles funktioniert hat, dann sollte Ihr Kalender wie im folgenden Bild aussehen.
Das war der 3. Teil des Workshops. Im 4. Teil werden Sie die restlichen Monate anlegen und die Zeilen nach unten ausfüllen. Dann kommt noch das Ermitteln der Arbeitstage und das Einstellen des Druckbereiches und Sie sind fertig mit Ihrem Kalender.
Viel Erfolg und Spaß beim Nachmachen.