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 2. Teil des Workshops programmieren Sie die Formeln im Tabellenblatt mit dem Jahreskalender und bereiten das Tabellenblatt für die Benutzung der Designfeatures des Office 2010 vor.
Sie können den Teil 1 des Workshops “Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 1 — Grundlagen” nacharbeiten und Ihre eigene Kalenderdatei erstellen, oder Sie laden sich die Datei “Kalender Teil 1 für Excel 2010” herunter und machen gleich mit Teil 2 des Workshops weiter.
Die Datein des Workshop Teil 2 finden Sie wie immer am Ende dieses Textes.
Design mit Office 2010/2007
Wenn Sie mit Office ab 2007 die automatische Umschaltung bei Auswahl eines anderen Design nutzen möchten, dann verabschieden Sie sich bitte von der manuellen Wahl von Standardfarben oder Standardschriften.
Sie müssen sich bei der Farb- bzw. Fontwahl auf die Designschriften bzw. Designfarben beschränken. Dann kann Excel, aber auch die anderen Office Anwendungen, bei Auswahl eines anderen Designs, die zugehörigen Schriften und Farben aktivieren.
Standardmäßig ist in meinem System das Design “Larissa” vorgewählt, als Beispiel habe ich das Design “Austin” gegenüber gestellt. Larissa ist ein blaues Thema, Austin ein eher grünes Thema.
Bei “Larissa” wird für Überschriften die Designschriftart “Cambria” eingesetzt und für Mengen- oder Fließtext die Designschriftart “Calibri”. Bei “Austin” werden Überschriften und Mengentext mit “Century Gothic” gesetzt.
Wollen Sie designorientiert die Schrift auswählen, so entscheiden Sie sich nur noch für Überschrift oder Mengentext. Aktivieren Sie ein neues Design, dann werden die zugehörigen Designschriften aktiviert.
Wählen Sie bei Farben auch nur die Designfarben, diese sind je nach Design harmonisch um eine Grundfarbe herum gruppiert. Prüfen Sie ggf. ob das gewählte Design hinsichtlich seiner Grauumsetzung auf Schwarz/Weiß Druckern erkennbar ist. Die neuen Designs orientieren auf Ausdrucke mit Farbdruckern.
Die Designs in Office 2010/2007 gestatten Ihnen ein schnelles Umschalten zwischen verschiedenen Varianten bei der Gestaltung Ihres Dokumentes. Weitere Designs zum Herunterladen finden Sie bei Microsoft Office.com unter Office-Designs. I.d.R. sind die Designs für alle Office-Anwendungen einsetzbar.
Probieren Sie ruhig mal ein neues Design aus, durch überfahren wird das neue Design mittels der Livevorschau angezeigt, aber erst durch bewusstes Anklicken für Ihr Dokument gewählt.
Kalender programmieren
Nun geht es mit unserem Kalender in die zweite Runde. Wir müssen nur einen kleinen Teil händisch programmieren, der Rest wird durch Ausfüllen übertragen. Wir programmieren die ersten 2 Tage im Januar und Februar.
Als erstes Formatieren wir die Überschrift in Zelle A1, dort steht z.Z. nur die Zahl 2011, wir möchten dort jedoch “Kalender für das Jahr 2011” stehen haben und wir können keinen Text hineinschreiben, da unser Kalender das Kalenderjahr aus der Zelle A1 bezieht.
Sie formatieren die Zelle benutzerdefiniert. Dazu klicken Sie mit der rechten Maustaste auf die Zelle A1 und wählen “Zellen formatieren”. Wählen Sie nun das Registerblatt “Zahlen” und dort die Kategorie “Benutzerdefiniert”. Unter Typ tragen Sie inkl. Anführungszeichen “Kalender für das Jahr” 0 ein. Der Text in Anführungszeichen wird als Text interpretiert, das folgende Leerzeichen ist für den abstand zuständig und die Null ist ein Platzhalter für eine ganze Zahl ohne Nachkommastelle. Möchten Sie zwei Kommastellen, so würden Sie 0,00 eingeben. Sehr schön können Sie die Auswirkung Ihrer Eingabe im Feld Beispiel erkennen. Sollte dort ggf. Unfug stehen, dann haben sie vieleicht ein Anführungszeichen vergessen. Klicken sie nach der Eingabe auf Ok.
Bei Ihnen steht nicht die gewünschte Überschrift sondern #######, kein Problem. Der Platz im Feld A1 ist zu schmal um den gesamten Text anzuzeigen, Abhilfe könnte sein die Zellen A1 bis AJ1 zu verknüpfen, so breit wird unser Kalender werden.
Für jeden Monat werden Sie drei Spalten benötigen:
- Spalte: Der Tag 1 .. 31
- Spalte: Der Wochentag Mo .. So
- Spalte: Der Feiertag oder ggf. die Kalenderwoche
Über diesen 3 Spalten befindet sich der Monat: Januar .. Dezember.
Verbinden Sie die Zellen A1 bis C1 und ordnen Sie den Inhalt linksbündig an. Schreiben Sie nun in Zelle A1 =Datum(Jahr;1;1), das bewirkt den 01.01. des Jahres, hier 2011. Noch schnell das Datum so formatieren, dass nur der Monat dort steht. Rechte Maus auf Zelle klicken und “Zellen formatieren” wählen, Registerkarte “Zahlen”, Kategorie “Benutzerdefiniert”, Typ MMMM.
Für die Formatierung des Monates gibt es 4 Möglichkeiten:
- M: in Monaten von Januar bis September steht nur 1. bis 9., ab Oktober steht 10.,
- MM: in Monaten von Januar bis September gibt es eine führende Null,
- MMM: der Monat wird in Textschreibweise mit drei Zeichen abgekürzt, also bspw. Jan,
- MMMM: der Monat wird ausgeschrieben, bspw. Januar.
Schreiben Sie nun in die Zelle A3 ein Gleichheitszeichen und klicken Sie die Zelle A2 an, alternativ könnten Sie auch händisch =A2 hineinschreiben, aber die Gefahr des Vertippen besteht und könnte zu unerwünschten Ergebnissen führen. Formatieren Sie die Zelle A3 benutzerdefiniert mit der Zeichenfolge TT.
Für die Formatierung des Tages gibt es ebenfalls 4 Möglichkeiten:
- T: stellt den Tag ohne führende Null dar,
- TT: an Tagen von 01. bis 09. gibt es eine führende Null,
- TTT: der Tag wird in Textschreibweise mit zwei Zeichen abgekürzt, also bspw. Mo, Di, Mi,
- TTTT: der Monat wird ausgeschrieben, bspw. Montag, Dienstag, Mittwoch.
In die Zelle B3 schreiben Sie wiederum ein Gleichheitszeichen und klicken dann auf die Zelle A3, hier möchten wir den Wochentag, also Mo .. So stehen haben. Wählen Sie die benutzerdefinierte Formatierung mit TTT.
Jetzt wird es interessant, Sie wollen in Zelle C3:
- an Feiertagen den Feiertag eintragen
- an Montagen, die kein Feiertag sind, soll die Kalenderwoche stehen,
- in allen anderen Fällen soll nichts eingetragen werden.
Tragen Sie folgenden “Code” ein.
=WENN(ISTFEHLER(FINDEN(A3;Feiertagsliste)); WENN(WOCHENTAG(A3;2)=1;KÜRZEN((A3-WOCHENTAG(A3;2)-DATUM(JAHR(A3+4-WOCHENTAG(A3;2));1;-10))/7); "" ); SVERWEIS(A3;Berechnen_der_Feiertage;2;WAHR) )
Was macht nun diese Code-Zeile?
Es wird abgefragt ob der Tag in Zelle A3 kein Feiertag ist, dann wird sofern der Wochentag ein Montag ist die Kalenderwoche ausgegeben, ansonsten wird der Feiertag eingetragen. Ganz einfach der Reihe nach.
WENN(Prüfung; [Dann_Wert]; [Sonst_Wert])
Die Wenn Anweisung sollten Sie kennen, WENN(Prüfung; [Dann_Wert]; [Sonst_Wert]), d.h ist die Prüfung wahr, dann wird der Dann_Wert ausgeführt, sonst der Sonst_Wert. Der Sonst_Wert ist optional, wenn Sie ihn nicht festlegen, dann wird ggf. “FALSCH” ausgegeben.
FINDEN(A3;Feiertagsliste)
Diese Funktion sucht das Datum in Zelle A3 in der Feiertagsliste, wenn es nicht in der Liste steht, gibt es eine Fehlermeldung, die Sie mit der nächsten Funktion abfangen können.
ISTFEHLER(Wert)
Wert kann eine Funktion sein, die einen Fehler erzeugt, bspw. ISTFEHLER(5/0) liefert Wahr, da eine Division durch Null einen Fehler hervorruft. Sie benutzen es um den Fehler der Finden Funktion abzufangen.
WENN(WOCHENTAG(A3;2)=1;KÜRZEN((A3-WOCHENTAG(A3;2)-DATUM(JAHR(A3+4‑WOCHENTAG(A3;2));1;-10))/7); “” )
Ist das Datum in Zelle A3 ein Montag, dann wird die Kalenderwoche als Zahl eingetragen, sonst wird nichts, also “”, eine leere Zeichenkette eingetragen.
WOCHENTAG(A3;2)=1
liefert WAHR, wenn der Tag in Zelle A3 ein Montag ist, also den Wert 1 hat. Montag = 1, Sonntag = 7.
KÜRZEN((A3-WOCHENTAG(A3;2)-DATUM(JAHR(A3+4‑WOCHENTAG(A3;2));1;-10))/7)
Leider ist die Formel für die Kalenderwoche in Excel die amerikanische Berechnungsvariante, die meist einen anderen Wert liefert als die KW nach DIN. Das ist die korrigierte Berechnung nach DIN.
SVERWEIS(A3;Berechnen_der_Feiertage;2;WAHR))
Gibt aus der Liste “Berechnen_der_Feiertage” den Namen des Feiertages aus Spalte 2 zurück. Fehler brauchen nicht abgefangen werden, da die Funktion nur aufgerufen wird, wenn ein Feiertag vorliegt.
Wenn ich derartige Formeln aufbaue, dann mache ich das schrittweise und baue sie aus den Einzelformeln zusammen.
Bei Ihnen sollte für den ersten Januar der Feiertag Neujahr erscheinen.
Jetzt wird es wieder ein wenig einfacher. Schreiben Sie in Zelle A4 ein Gleichheitszeichen, klicken auf die Zelle A3 und geben dann noch ein Pluszeichen gefolgt von einer 1 ein. D.h. Sie addieren zum 1. Januar einen Tag hinzu und erhalten den 2. Januar. Hier als 02 erkennbar.
Markieren Sei nun die Zellen B3 bis C3, bewegen den Cursor an die rechte untere Ecke und warten bis aus dem fetten weißen Plus ein weniger fettes schwarzes Plus wird. Dann halten Sie die linke Maustaste gedrückt und ziehen den Cursor bis zur Zeile 4. In der Zelle B4 sollte “So” erscheinen, die Zelle C scheint leer. Ein sich zeigender Smarttag würde ihnen eine Auswahl bieten hinsichtlich “Zellen Kopieren”, “Nur Formate ausfüllen” oder “Ohne Formatierung ausfüllen”. Der Standardwert “Zellen Kopieren” ist für diesen Fall die richtige Wahl.
Als Schrift für die gesamte Tabelle habe ich die Designschrift “Calibri” verwendet. Die Überschrift hat eine Größe von 36pt, der Monat, die Tage und der abgekürzte Wochentag ist mit 22pt formatiert und die Feiertage haben eine Schriftgröße von 11pt. Die Breite der Spalte A und B habe ich an den Inhalt angepasst, hier 50pt und die Spalte C habe ich so breit gemacht, dass der Feiertag “Tag der deutschen Einheit” hineinpassen würde. Wird später nochmals angepasst.
Dann habe ich die Zellen A2 bis C4 markiert und den Rahmen (Zellen formatieren, Register Rahmen) mit einer fetten Linie in einer hellen Designfarbe außen und innen nur waagerecht formatiert. Sonst würde Excel zwischen den Feldern für den Tag, den Wochentag und den Feiertag/KW ebenfalls eine Linie ziehen, das möchte ich aber nicht.
Nun legen wir das Feld für den Februar in der Überschrift an. Dazu markieren Sie den Januar begeben sich mit dem Cursor an den rechten unteren Rand der Zelle, bis aus dem fetten weißen Plus ein weniger fettes Plus wird, und ziehen bei gedrückter linker Maustaste um drei Spalten nach rechts. Dabei wird automatisch die Formel aus Zelle A2 in die Zelle D2 übertragen, diese entsteht durch verbinden der Zellen D2 bis F2. Noch steht fehlerhaft dort die Formel “=DATUM(Jahr;1;1)” die Sie durch “=DATUM(Jahr;MONAT(A2)+1;1)” ersetzen. Nun sollte nach Bestätigen mit der <ENTER>-Taste dort Februar stehen. Sie berechnen aus dem Datum im Feld Januar ein neues Datum in dem Sie zum Monat einen Monat hinzufügen.
Nun werden Sie die Formeln in den Feldern A3 bis C4 in die entsprechenden Felder des Monats Februar kopieren. Markieren Sie dazu zuerst den Bereich D3 bis D4, halten die <STRG>-Taste gedrückt und wählen nun den Bereich A3 bis A4 aus. Hiernach klicken Sie im Menüband Start, Gruppe Bearbeiten auf Füllbereich und wählen “Rechts”. Nun sollten die Formeln und Formate in die Zellen D3 bis D4 übernommen worden sein. Verfahren Sie bitte genauso für die Bereiche
- B3 bis B4 und E3 bis E4 und
- C3 bis C4 und F3 bis F4.
Sie erhalten eine Tabelle wie auf dem letzten Bild dargestellt.
In diesem Workshop haben Sie die Formeln für den Kalender programmieret. Jetzt fehlt nur noch die bedingte Formatierung um die Hervorhebungen für Feiertage und Wochenenden hervor zunehmen. Wie das geht, erfahren Sie im nächsten Teil des Workshops.